Question

Normalisation

The table below has basic information about Sales staff and their sales for a small appliance sales chain. It shows the employee, the items they sell as well as which store they work in.

SALES(Cost, Emp_Name, Emp_ID , Make, Grade , Price, Serial_No, Commission, Store _Address, Date, Store _Phone, Model)

The following functional dependencies apply and there are no redundancies present:

Store_Address -> Store_Phone
Grade -> Commission
Emp_ID -> Store _Address
Serial_No -> Model, Make
Emp_ID , Serial_No -> Date, Retail_Price Serial_No ->Wholesale_Price

Emp_ID -> Grade Emp_ID -> Emp_Name

3.1​ ​Find the Primary Key of the SALES relation.
3.2​ Decompose the SALES relation into 3NF. Show the final schema in full with all primary keys and

foreign keys mark appropriately.3.3​ The following FD also holds true:

Store_Phone-> Store_Address

Which relation in your decomposition would it map to? Test that relation to show that it is still in 3NF. Show your working.

Answer 1

SALES(Cost, Emp_Name, Emp_ID , Make, Grade , Price, Serial_No, Commission, Store_Address, Date, Store _Phone, Model)

Given FDs:

Store_Address -> Store_Phone

Grade -> Commission

Emp_ID -> Store _Address

Serial_No -> Model, Make

Emp_ID , Serial_No -> Date, Price

Serial_No ->Cost

Emp_ID -> Grade

Emp_ID -> Emp_Name

The primary key of SALES is combination of Emp_ID and Serial_No : (Emp_ID, Serial_No)

FDs :

Emp_ID -> Emp_Name, Store_Address, Grade

Serial_No -> Model, Make, Cost

(Emp_ID, Serial_No) -> Date, Price

Store_Address -> Store_Phone

Grade -> Commission

First Normal Form (1NF) - 1NF disallows multivalued attributes, composite attributes and their combinations. The domain of attributes must include only atomic values hence it disallows sets of values, a tuple of values or a combination of both as an attribute for a single tuple.

The Schema is in 1NF. Since combination of Emp_ID and Serial_No is primary key of the table which means these contain atomic values and Emp_ID -> Emp_Name, Store_Address, Grade which means Emp_Name, Store_Address , Grade also contain only atomic values and Serial_No -> Model, Make, Cost which means Model, Make and Cost contain only atomic values. Similarly, (Emp_ID, Serial_No) -> Date, Price => Date and Price has atomic values

Store_Address -> Store_Phone , Since Store_Address has atomic values => Store_Phone will also have only atomic values

Grade -> Commission => Commission also has atomic values.

After 1 NF:

SALES(Cost, Emp_Name, Emp_ID , Make, Grade , Price, Serial_No, Commission, Store_Address, Date, Store _Phone, Model)

where (Emp_ID, Serial_No) is the primary key of Sales table with FDs:

Emp_ID -> Emp_Name, Store_Address, Grade

Serial_No -> Model, Make, Cost

(Emp_ID, Serial_No) -> Date, Price

Store_Address -> Store_Phone

Grade -> Commission

Second Normal Form (2NF) - A Relational Schema, R, is in 2NF if every non-prime attribute , A in R is fully functionally dependent on the primary key of R.

• A prime attribute is a part of key whereas a non-prime attribute doesn't form part of any key of R.
• A functional dependency X -> Y is a fully functional dependency if removal of any attribute A from X means that the dependency doesn't hold anymore.
• A functional dependency X->Y is a partial dependency if some attribute A E X can be removed and the depenedency still holds.

For the given Schema:

Emp_Name, Store_Address and Grade are partially dependent on the primary key of Sales (dependent on Emp_ID)

Similarly, Model, Make, Cost are partially dependent on the primary key of Sales (dependent on Serial_No)

To remove the partial dependency from Sales, we remove Emp_ID, Emp_Name, Store_Address, Grade, Store_Phone and Commission from Sales and create another table called Emp and make Emp_ID the primary key of Emp table which in Sales table is a foreign key of Emp table.

Similarly, we remove Serial_No, Model, Make, Cost from Sales table and create another table called Item where Serial_No is the primary key of Item table and foreign key of Item table in Sales table.

After 2NF:

Emp (Emp_ID, Emp_Name, Store_Address, Grade, Store_Phone, Commission)

where Emp_ID is the primary key of Emp with FDs:

Emp_ID -> Emp_Name, Store_Address, Grade

Store_Address -> Store_Phone

Grade -> Commission

Item (Serial_No, Model, Make, Cost)

where Serial_No is the primary key of Item

Serial_No -> Model, Make, Cost

SALES( Emp_ID , Price, Serial_No, Date)

where Emp_ID is the foreign key of Emp table and Serial_No is the foreign key of Item table

and (Emp_ID, Serial_No) is the primary key of Sales table with FDs:

(Emp_ID, Serial_No) -> Date, Price

Third Normal form (3NF) - A Relational Schema, R, is in 3NF if it is in 2NF and no non-prime attribute of R is transitively dependent on the primary key. A Relational Schema, R, is in 3NF if whenever a functional dependency X->A holds in R either:

• X is the super key of R
• A is the prime attribute of R

A functional dependency , X->Y in a relational schema, R, is a transitive dependency if there is a set of attributes Z that is not part of any key of R and both X->Z and Z->Y holds.

For the given schema:

In Emp table , Store_Phone and Commission are transitively dependent on Emp_Id

In order to remove this transitive dependency , we remove and create another table called Store with attributes Store_Address and Store_Phone where Store_Address is the primary key of Store and forms foreign key in Emp table

Similarly, we remove and create another table called Comm with fields Grade and Commission where Grade is the primary key of Comm table and forms foreign key in Emp table

After 3NF:

Store(Store_Address, Store_Phone)

where Store_Address is the primary key with FD:

Store_Address -> Store_Phone

Comm(Grade, Commission)

where Grade is the primary key of Comm with FD:

Grade -> Commission

Emp (Emp_ID, Emp_Name, Store_Address, Grade, Commission)

where Store_Address is the foreign key of Store and Grade is the foreign key of Comm

and Emp_ID is the primary key of Emp with FDs:

Emp_ID -> Emp_Name, Store_Address, Grade

Item (Serial_No, Model, Make, Cost)

where Serial_No is the primary key of Item

Serial_No -> Model, Make, Cost

SALES( Emp_ID , Price, Serial_No, Date)

where Emp_ID is the foreign key of Emp table and Serial_No is the foreign key of Item table

and (Emp_ID, Serial_No) is the primary key of Sales table with FDs:

(Emp_ID, Serial_No) -> Date, Price