Question

For this question, normalize the table to 3NF and draw the relational schema showing cardinalities.

UCLA just purchased several servers that are to be used for enterprise resource planning (ERP). The individual servers were purchased from different suppliers and these servers are running different software applications. UCLA also trained several of its staff to use these servers and each trained staff has an authentication code that allows the staff to use a specific software application on a specific server for a specific time period. Software license is for specific servers and a server could have multiple software applications at a time. Every supplier assigns a tech person with ID to support UCLA in case of any challenges. Each tech person provides a mobile phone number to be used to reach him. Servers have a purchase price.

Attributes are: ServerNo, SupplierID, SupplierName, SupplierPhone, SupplierTechID, SupplierTechName, SupplierTechPhone, SoftwareApplicationID, SoftwareApplicationName, SoftwareApplicationVendor, SoftwareApplicationLicenseExpires, SoftwareApplicationLicensePrice, StaffID, StaffName, StaffAuthenticationStarts, StaffAuthenticationEnds, StaffAuthenticationCode, PurchasePrice

Answer 1

Explaination:

Normalization:

1. Normalization is the process of organizing the data in the database.
2. Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate the undesirable characteristics like Insertion, Update and Deletion Anomalies.
3. Normalization divides the larger table into the smaller table and links them using relationship.
4. The normal form is used to reduce redundancy from the database table.

The database normalization process is further categorized into the following types:

1. First Normal Form (1 NF)
2. Second Normal Form (2 NF)
3. Third Normal Form (3 NF)
4. Boyce Codd Normal Form or Fourth Normal Form ( BCNF or 4 NF)
5. Fifth Normal Form (5 NF)
6. Sixth Normal Form (6 NF)

We have to decompose the given relation into 3 NF . So the relation have to be in 1NF and 2NF

1NF : A relation is in 1NF if it contains an atomic value.

2NF: A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key.

3NF : A relation will be in 3NF if it is in 2NF and no transition dependency exists.

We have following functional dependencies for Relations decomposed in 3 NF:

ServerNo PurchasePrice,SupplierID, StaffID

SupplierID SupplierName, SupplierPhone, SupplierTechID

SupplierTechID SupplierTechName, SupplierTechPhone

SoftwareApplicationID SoftwareApplicationName, SoftwareApplicationVendor, SoftwareApplicationLicenseExpires, SoftwareApplicationLicensePrice,ServerNo

StaffID StaffName, StaffAuthenticationStarts, StaffAuthenticationEnds, StaffAuthenticationCode

Dependency Diagram is given as follows: