Question

It is required to develop the normalization from the Relational Model for the clothing store: Modarte S.A. de C.V.

-Step 1: Entity-Relationship Model

The clothing store Modarte S.A. de C.V. It has various products for women, men, boys and girls, which requires generating an Entity-Relationship Model for inventory control and sales management.

It is required to develop 3 tables: products, employees and customers.

In the Products table the following fields must be considered: product code, product name, type, size, purchase date, sale date and quantity in stock.

In the Employees table, the following fields must be considered: Employee ID, Employee Name, Address, Telephone and CURP.

In the Clients table, the following fields must be considered: client number, Client name, address, e-mail, payment method, and RFC for billing.

In the Sales table the following fields must be considered: sales code, sale date, payment method and total to pay.

The Products-Sale Relationship contains the following fields: product code and sales code

- Step 2: First normal form.

- Step 3: Second normal form.

- Step 4: Third normal form.

Answer 1

Table with fields

Products – productCode(PK),productName,type,size,purchaseDate,saleDate,quantity

Employees – employeeId(PK),employeeName,address,phone,curp

Clients – clientNumber(PK),clientName,address,email,payMthod,rfc

Sales – salesCode(PK),saleDate,payMethod,totalPay

Entity-Relationship Model

First Normal Form [1NF]

A database is in first normal form if it satisfies the following conditions:

• Contains only atomic values
• There are no repeating groups

In the Employee table address, phone are multi valued attributes. If the table should be like this:

employeeId	employeeName	Address	Phone	Curp
101	James	First street plot no 4	9845673489, 9645677832	J237801
102	Peter	Park avenue flat no 123	945239876	P25678

According to 1NF the above table must be like this:

employeeId	employeeName	Address	Phone	Curp
101	James	First street plot no 4	9845673489	J237801
101	James	First street plot no 4	9645677832	J237801
102	Peter	Park avenue flat no 123	945239876	P25678

In the client table email attribute is multi-valued attribute. So that should be also split like above table.

Second Normal Form

· In the 2NF, relational must be in 1NF.

· In the second normal form, all non-key attributes are fully functional dependent on the primary key

productCode	productName	Type	Size	purchaseDate	saleDate	quantity
1001	Shirt	Cotton	40	12/10/2020	10/11/2020	5000
1001	Shirt	Cotton	40	12/10/2020	12/11/2020	3000
1001	Shirt	Cotton	40	12/10/2020	20/11/2020	2500

The above table can be split like below:

productCode	productName	Type	Size	purchaseDate	Quantity
1001	Shirt	Cotton	40	12/10/2020	5000
1001	Shirt	Cotton	40	12/10/2020	3000
1001	Shirt	Cotton	40	12/10/2020	2500

productCode	productName	Type	Size	saleDate	quantity
1001	Shirt	Cotton	40	10/11/2020	5000
1001	Shirt	Cotton	40	12/11/2020	3000
1001	Shirt	Cotton	40	20/11/2020	2500

Third Normal Form

• A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency.
• 3NF is used to reduce the data duplication. It is also used to achieve the data integrity.
• If there is no transitive dependency for non-prime attributes, then the relation must be in third normal form.

A relation is in third normal form if it holds atleast one of the following conditions for every non-trivial function dependency X → Y.

1. X is a super key.
2. Y is a prime attribute, i.e., each element of Y is part of some candidate key.

employeeId	employeeName	Address	Phone	Curp
101	Sherin	Third cross no 12	9654329874	S78654
102	James	First street plot no 4	9645677832	J237801
103	Peter	Park avenue flat no 123	945239876	P25678

FD set:

{employeeId -> employeeName, employeeId -> phone, employeeId -> curp, employeeId -> address}

Candidate Key:

{employeeId}