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

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

Solutions

Expert Solution

Explaination:

Normalization:

Normalization is the process of organizing the data in the database.
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.
Normalization divides the larger table into the smaller table and links them using relationship.
The normal form is used to reduce redundancy from the database table.

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

First Normal Form (1 NF)
Second Normal Form (2 NF)
Third Normal Form (3 NF)
Boyce Codd Normal Form or Fourth Normal Form ( BCNF or 4 NF)
Fifth Normal Form (5 NF)
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:

venereology answered 1 month ago

Next > < Previous

Related Solutions

Database: Question 11 Using the ERD from problem 10, create the relational schema.(Create an appropriate collection...

Database: Question 11 Using the ERD from problem 10, create the relational schema.(Create an appropriate collection of attributes for each of the entities. Make sure you use the appropriate naming conventions to name the attributes.)

1. Please read the business statement below and draw ER, NER, and Table Schema diagrams for...

1. Please read the business statement below and draw ER, NER, and Table Schema diagrams for it. Business Statement: The project is about developing an auction Web site. The details are as follows: BA is an online auction Web site. People can buy and sell items in this Web site. Buyers are people who like to buy items, and sellers are people who like to sell items.  Each seller can sell items .  Each item has a bidding...

Part 1: Please read the business statement below and draw ER, NER, and Table Schema diagrams...

Part 1: Please read the business statement below and draw ER, NER, and Table Schema diagrams for it. Business Statement: The project is about developing an auction Web site. The details are as follows: BA is an online auction Web site. People can buy and sell items in this Web site. Buyers are people who like to buy items, and sellers are people who like to sell items. Each seller can sell items. Each item has a bidding start time,...

Part 1: Please read the business statement below and draw ER, NER, and Table Schema diagrams...

Question: 1. Neatly, draw two cross sections showing differences in the arrangements of the vascular tissue......

Question: 1. Neatly, draw two cross sections showing differences in the arrangements of the vascular tissue... 1. Neatly, draw two cross sections showing differences in the arrangements of the vascular tissue found in a dicot herbaceous stem and a six year old dicot woody stem. Identify the following tissues if they are present: ground tissue (soft support), vascular tissue (both primary and secondary conducting tissues), wood, bark, seasonal growth rings, pith rays. (be complete) 2. Color code and explain the...

Question 1 Consider the table below showing amounts of good x and y that nation 1...

Question 1 Consider the table below showing amounts of good x and y that nation 1 and 2 can produce with one hour of labor time N1 N2 X/labor hour 8 4 Y/ labor hour 2 6 Which nation has absolute advantage in cloth and which one has absolute advantage in wheat? (explain your answer) How much will nation 1 and nation 2 gain from trade if each nation specialized in the production of the commodity of its absolute advantage...

Question 1: A Multiplexer (MUX) a) Write truth table and draw symbol for a 4-to-1 MUX....

Question 1: A Multiplexer (MUX) a) Write truth table and draw symbol for a 4-to-1 MUX. (1 mark) b) Write VHDL code for the above multiplexer. (1 mark) c) Write VHDL code for a test bench and simulate the design. (1 mark) d) Implement the design on FPGA, with inputs connected to switches and output to LED. (1 mark)

QUESTION FOUR (20 Marks) 4.1 Complete Table 4.1 given below showing quantity, fixed cost, variable cost,...

QUESTION FOUR 4.1 Complete Table 4.1 given below showing quantity, fixed cost, variable cost, total cost and marginal cost. Quantity Fixed Cost (R) Variable Cost (R) Total Cost (R) Marginal Cost (R) 0 25 0 (a) (d) 2 (b) 18 (c) (g) 4 (e) 31 (f) (j) 6 (h) (41) (i) 4.2 Using examples distinguish between fixed costs and variable costs. 4.3 Differentiate between marginal cost and marginal revenue. 4.4 Compare and contrast accounting and economic profits.

Subjects