Database Systems: Relational Data Structures, Schema and Normalization

This report delves into the core concepts of relational database design. It begins by outlining various entities, including Employees, Departments, Customers, Complaints, Products, and Stores, detailing their attributes and relationships. The report meticulously analyzes each entity, assessing its compliance with the first, second, and third normal forms (1NF, 2NF, and 3NF) to ensure data integrity and minimize redundancy. It also addresses many-to-many relationships, proposing the creation of linking tables like Store_Has_Product to resolve them. Furthermore, the report translates the relational data structures into a detailed relational database schema, specifying column names, data types, and primary/foreign keys for each table. Finally, the report provides a comprehensive bibliography of relevant resources.

Running head: Introduction to Database
INTRODUCTION TO DATABASE
Name of the Student
Name of the University
Author Note

1Introduction to Database
Table of Contents
1. Relational data structures....................................................................................................2
A relational database schema that translates your relational data structures:............................8
Bibliography:............................................................................................................................13

2Introduction to Database
1. Relational data structures
Employees Entity:
Employees (EmployeeID,  EmpName, Phonenumber, departmentID, StoreNumber)
1nf:
When a relation contains multi-valued or composite attribute, it will violate the first
normal form. But employee table does not contain any composite or multi-valued attribute.
2nf:
A relation must be in first 1nf and it must not contain partial dependency. Employee
tables does not contain any partial dependency.
3nf:
There is no transitive dependency for non-prime attributes are present in the employee
table thus, this table is in third normal form.
Department Entity:
Department (DepartmentID,  DepartmentName)
1nf:
When a relation contains multi-valued or composite attribute, it will violate the first
normal form. But department table does not contain any composite or multi-valued attribute.
2nf:
A relation must be in first 1nf and it must not contain partial dependency. Department
tables does not contain any partial dependency.
3nf:

3Introduction to Database
There is no transitive dependency for non-prime attributes are present in the
department table thus, this table is in third normal form.
Customer Entity:
Customer (CustomerID,  Firstname, lastName, StreetAddress, Town, State, PostCode,
workNumber, HomeNumber)
1nf:
When a relation contains multi-valued or composite attribute, it will violate the first
normal form. But customer table does not contain any composite or multi-valued attribute.
2nf:
A relation must be in first 1nf and it must not contain partial dependency. Customer
tables does not contain any partial dependency.
3nf:
There is no transitive dependency for non-prime attributes are present in the customer
table thus, this table is in third normal form.
Complaint Entity:
Complaint (ComplaintNumber  ComplaintDate, ComplaintType, status, EmployeeID,
CustomerID)
1nf:
When a relation contains multi-valued or composite attribute, it will violate the first
normal form. But complaint table does not contain any composite or multi-valued attribute.
2nf:

4Introduction to Database
A relation must be in first 1nf and it must not contain partial dependency. Complaint
tables does not contain any partial dependency.
3nf:
There is no transitive dependency for non-prime attributes are present in the
complaint table thus, this table is in third normal form.
ProductComplain Entity:
ComplainNumber (ComplainNumber,  shortdescription, numberofitems, productid)
1nf:
When a relation contains multi-valued or composite attribute, it will violate the first
normal form. But ProductComplain table does not contain any composite or multi-valued
attribute.
2nf:
A relation must be in first 1nf and it must not contain partial dependency.
ProductComplain tables does not contain any partial dependency.
3nf:
There is no transitive dependency for non-prime attributes are present in the
ProductComplain table thus, this table is in third normal form.
Stores Entity:
Stores (StoreNumber  name, street, city, manager, state, postcode, telephonenumber)
1nf:

5Introduction to Database
When a relation contains multi-valued or composite attribute, it will violate the first
normal form. But store table does not contain any composite or multi-valued attribute.
2nf:
A relation must be in first 1nf and it must not contain partial dependency. Store tables
does not contain any partial dependency.
3nf:
There is no transitive dependency for non-prime attributes are present in the store
table thus, this table is in third normal form. But store and product table contain many to
many relations that is the reason a new table is need to be created name Store_Has_Product.
This table can hold only storenumber and productid. This table resolve the many to many
relation and satisfy the 3nf form.
Product Entity:
Product (productid,  productName, productDescription, productunitcost)
1nf:
When a relation contains multi-valued or composite attribute, it will violate the first
normal form. But product table does not contain any composite or multi-valued attribute.
2nf:
A relation must be in first 1nf and it must not contain partial dependency. Product
tables does not contain any partial dependency.
3nf:

6Introduction to Database
There is no transitive dependency for non-prime attributes are present in the product
table thus, this table is in third normal form. But product and store contain many to many
relations that is the reason a new table is need to be created name Store_Has_Product. This
table can hold only storenumber and productid. This table resolve the many to many relation
and satisfy the 3nf form.
StoreComplain Entity:
StoreComplain ( ComplaintNumber  shortdescription, Storenumber)
1nf:
When a relation contains multi-valued or composite attribute, it will violate the first
normal form. But StoreComplain table does not contain any composite or multi-valued
attribute.
2nf:
A relation must be in first 1nf and it must not contain partial dependency.
StoreComplain tables does not contain any partial dependency.
3nf:
There is no transitive dependency for non-prime attributes are present in the
StoreComplain table thus, this table is in third normal form.
EmployeeComplain Entity:
EmployeeComplain ( ComplainNumber  shortdescription, EmployeeID)
1nf:

7Introduction to Database
When a relation contains multi-valued or composite attribute, it will violate the first
normal form. But EmployeeComplain does not contain any composite or multi-valued
attribute.
2nf:
A relation must be in first 1nf and it must not contain partial dependency.
EmployeeComplain does not contain any partial dependency.
3nf:
There is no transitive dependency for non-prime attributes are present in the
EmployeeComplain thus, this table is in third normal form.
Contract Entity:
ContractEntity (EmployeeID, ComplaintNumber,  date, time, typeofcontract,
shortdescription)
1nf:
When a relation contains multi-valued or composite attribute, it will violate the first
normal form. But ContractEntity does not contain any composite or multi-valued attribute.
2nf:
A relation must be in first 1nf and it must not contain partial dependency.
ContractEntity does not contain any partial dependency.
3nf:
There is no transitive dependency for non-prime attributes are present in the
ContractEntity thus, this table is in third normal form.

8Introduction to Database
A relational database schema that translates your relational data
structures:
Employees Table:
COLUMNNAME DATATYPE KEYS
EmployeeID Int Primary key.
EmpName Varchar (50)
PhoneNumber Int
DepartmentID Varchar (20) Foreign key.
StoreNumber Varchar (20) Foreign key.
Department Table:
COLUMNNAME DATATYPE KEYS
DepartmentID Varchar (20) Primary key.
DepartmentName Varchar (50)
Customers Table:
COLUMNNAME DATATYPE KEYS
CustomerID Int Primary key.
FirstName Varchar (50)
LastName Varchar (50)

9Introduction to Database
StreetAddress Varchar (50)
Town Varchar (20)
State Varchar (20)
PostCode Int
WorkNumber Int
HomeNumber Int
Complaint Table:
COLUMNNAME DATATYPE KEYS
ComplaintNumber Int Primary key.
ComplaintDate Date
ComplaintType Varchar (20)
Status Varchar (10)
EmployeeID Int Foreign key.
CustomerID Int Foreign key
productComplain
COLUMNNAME DATATYPE KEYS
ComplainNumber Int Primary key, foreign key
ShortDescription Varchar (50)
Numberofitems Int
ProductID Varchar (10) Foreign key.

10Introduction to Database
Product Table:
COLUMNNAME DATATYPE KEYS
ProductID Varchar (10) Primary key.
ProductName Varchar (30)
ProductDescription Varchar (50)
ProductUnitCost Int
Store Table:
COLUMNNAME DATATYPE KEYS
StoreNumber Varchar (20) Primary key.
Name Varchar (50)
Street Varchar (20)
City Varchar (20)
Manager Varchar (20)
State Varchar (20)
Postcode Int
telephoneNumber Int
Store_Has_Products Table

11Introduction to Database
COLUMNNAME DATATYPE KEYS
StoreNumber Varchar (20) Primary key. Foreign key
ProductID Varchar (10) Primary key. Foreign key
Store Complain Table:
COLUMNNAME DATATYPE KEYS
ComplaintNumber Int Primary key. Foreign key
ShortDescription Varchar (50)
StoreNumber Varchar (10) Foreign key.
EmployeeComplain Table:
COLUMNNAME DATATYPE KEYS
ComplaintNumber Int Primary key. Foreign key
ShortDescription Varchar (50)
EmployeeID Int Foreign key
Contact Table:
COLUMNNAME DATATYPE KEYS
EmployeeID Int Primary key. Foreign key
ComplaintNumber Int Primary key. Foreign key