Determine the Functional Dependencies that exist in the following Orders table. This table lists customer and...

Determine the Functional Dependencies that exist in the following Orders table. This table lists customer and order data.

Orders (SupplierNum, SupplierName, Supp_Phone, ProductNum, Description, Product_type, QuotedPrice) .

Normalize the above relation to 3 rd normal form, ensuring that the resulting relations are dependency-preserving and specify the primary keys in the normalized relations by underlining them.

Expert Solution

Orders (SupplierNum, SupplierName, Supp_Phone, ProductNum, Description, Product_type, QuotedPrice)

Here,

SupplierNum determines SupplierName and Supp_Phone
ProductNum determines Description and Product_type
SupplierNum, ProductNum together determines SupplierNum, SupplierName, Supp_Phone, ProductNum, Description, Product_type and QuotedPrice

=> SupplierNum, ProductNum is the primary key of Orders table.

Since SupplierNum and ProductNum individually determines some attributes, the orders table is not in 2NF.

To make it into 2NF, we split the table

Suppliers (SupplierNum, SupplierName, Supp_Phone)
Products (ProductNum, Description, Product_type)
Orders (SupplierNum, ProductNum, QuotedPrice)

Now, we need to check the above tables are in 3NF (there should not be any transitive dependencies)

The above tables don't have any transitive dependencies, all are in 3NF.

Suppliers (SupplierNum, SupplierName, Supp_Phone) SupplierNum --> SupplierName, Supp_Phone
Products (ProductNum, Description, Product_type) ProductNum --> Description, Product_type
Orders (SupplierNum, ProductNum, QuotedPrice) SupplierNum, ProductNum --> QuotedPrice

Dependencies are also preserved.

venereology answered 1 year ago

a. Determine the functional dependencies that exist in the following table. Orders (OrderNum, OrderDate, ItemNum, Description,...

a. Determine the functional dependencies that exist in the following table. Orders (OrderNum, OrderDate, ItemNum, Description, NumOrdered, QuotedPrice) b. After determining the functional dependencies, perform 1nf, 2nf, 3nf.

Task 1. For each table on the list, identify the functional dependencies. List the functional dependencies....

Task 1. For each table on the list, identify the functional dependencies. List the functional dependencies. Normalize the relations to BCNF. Then decide whether the resulting tables should be implemented in that form. If not, explain why. For each table, write the table name and write out the names, data types, and sizes of all the data items, Identify any constraints, using the conventions of the DBMS you will use for implementation. Write and execute SQL statements to create all...

Consider a relation R (ABCDEFGH) with the following functional dependencies: ACD --> EF AG --> A...

Consider a relation R (ABCDEFGH) with the following functional dependencies: ACD --> EF AG --> A B --> CFH D --> C DF --> G F --> C F --> D Find minimal cover and identify all possible candidate keys. In order to receive full credit, please list each step taken and the rules that you applied.

Normalize the following data by: a) identifying the functional dependencies, b) stating your assumptions, \ c)...

Consider the following functional dependencies: Z -> XYD, X -> Y. Find the minimal cover of...

Consider the following functional dependencies: Z -> XYD, X -> Y. Find the minimal cover of the above.

1. Consider the following functional dependencies: Z -> XYD, X -> Y Find the minimal cover...

1. Consider the following functional dependencies: Z -> XYD, X -> Y Find the minimal cover of the above. 2. Consider the following two sets of functional dependencies: F = {A -> C, AC -> D, E -> AD, E -> H} and G = {A -> CD, E -> AH}. Check whether they are equivalent.

For the relation R(A,B,C,D,E) with the following Functional Dependencies: A → B, A → C, BC...

For the relation R(A,B,C,D,E) with the following Functional Dependencies: A → B, A → C, BC → D, AC → E, CE → A, list all non-trivial FDs following from the above. Generate all possible keys for R. Check whether R is in 3NF. If it is in 3NF, explain the criteria you used. If it is not in 3NF, convert it into 3NF, showing the new relations and their FDs.

Consider the following relation R(A, B, C, D, E, G) and the set of functional dependencies...

Consider the following relation R(A, B, C, D, E, G) and the set of functional dependencies F={ A → BCD, BC → DE, B→D, D → A} Note: Show the steps for each answer. (a) Compute B+ . (b) Prove (using Armstrong’s axioms) that AG is superkey. (c) Compute Fc. (d) Give a 3NF decomposition of the given schema based on a canonical cover. (e) Give a BCNF decomposition of the given schema based on F. Use the first functional...

Normalization: Answer all 4 questions. You are given the following relation R and some functional dependencies....

Normalization: Answer all 4 questions. You are given the following relation R and some functional dependencies. R(SID, Project, Code, ListOfSupplies, Name, Initials, Abbrev) Project → ListOfSupplies SID → Name Name → Initials Project, Initials → Abbrev SID, Project → Code Code → SID Is R in 1NF? If not, normalize R into a collection of 1NF relations. Is R in 2NF? If not, normalize R (or your collection of 1NF relations) into a collection of 2NF relations. Is R in...

Normalize the following relations. Show possible candidate/primary keys and the functional dependencies. Explain the normal form...

Normalize the following relations. Show possible candidate/primary keys and the functional dependencies. Explain the normal form in which the relation is currently in (based on the sample data) and how do you break the relations to get 3rd Normal Form. Show the new relations obtained after normalization and underline the candidate/primary key in each new relation and italic the foreign key. Relation 1: COLLEGE PARKING TICKET (STID, LName, FName, PhoneNo, StateLic, LicNo, Ticket#, Date, Code, Fine) (Illustrated with sample data)...

Question