Question

In: Computer Science

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.

Solutions

Expert Solution

Consider relation given

Orders (OrderNum, OrderDate, ItemNum, Description, NumOrdered, QuotedPrice)

First Normal Form (1NF) :

Given relation is in the first normal form because all columns are atomic in nature
All the columns have single value

Second Normal Form (2NF) :

In second normal form (2NF) above table needs to normaliz into 2NF because above relation contains partial dependancy
In second normal form all non key columns should depends upon primary key column.

Below are tables in second normal form (2NF)

1.Table Name :Item

Description :This table stores item details like ItemNum, Description

Schema :Item ( ItemNum, Description)

FD: ItemNum==>Description

2.Table Name :Orders

Description :This table stores order details like OrderNum, OrderDate

Schema :Orders( OrderNum, OrderDate)

FD: OrderNum==>OrderDate

Third Normal Form (3NF) :

In third normal form need to remove transitive dependancy.

Below are tables in 3NF.

1.Table Name :Item

Description :This table stores item details like ItemNum, Description

Schema :Item ( ItemNum, Description)

FD: ItemNum==>Description

2.Table Name :Orders

Description :This table stores order details like OrderNum, OrderDate

Schema :Orders( OrderNum, OrderDate)

FD: OrderNum==>OrderDate

3.Table Name :OrderDetails

Description :This table stores order details like OrderNum, and ItemNum,NumOrdered, QuotedPrice

Schema :OrderDetails( OrderNum,ItemNum, NumOrdered, QuotedPrice)

FD: OrderNum,ItemNum==>NumOrdered, QuotedPrice

venereology answered 7 months ago

Next > < Previous

Related Solutions

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.

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.

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)...

=>Set of functional dependencies(F) = {A -> BC, BC -> AD, D -> E} =>Set of...

=>Set of functional dependencies(F) = {A -> BC, BC -> AD, D -> E} =>Set of functional dependencies(F) = {AB -> C, A -> DE, B -> F, F -> GH, D -> IJ} Decompose the previous R{A, B, C, D, E, F, G, H, I, J} into each higher normal form relations above its current NF. For example, if its current NF is 0NF, then you need to decompose R to 1NF relations, 2NF relations, up to 3NF relations...

Consider the following schema and functional dependencies: SHIPPING (ShipName, ShipType, VoyageID, Cargo, Port, ArrivalDate) Key: ShipName,...

Consider the following schema and functional dependencies: SHIPPING (ShipName, ShipType, VoyageID, Cargo, Port, ArrivalDate) Key: ShipName, ArrivalDate FD1: ShipName > ShipType FD2: VoyageID > ShipName, Cargo FD3: ShipName, ArrivalDate > VoyageId, Port 1.Please list the final set of 3NF schema including all its keys. 2.Do any of the finalized 3NF schema have determinates that are not candidate keys? If yes, explain - which schema(s)? Why?

Subjects