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?
Homework Answers
Answer:------------
Given,
Relation, SHIPPING (ShipName, ShipType, VoyageID, Cargo,
Port, ArrivalDate)
Key is ShipName and ArrivalDate
FD1: ShipName > ShipType
FD2: VoyageID > ShipName, Cargo
FD3: ShipName, ArrivalDate > VoyageId, Port
Firstly we need to normalize it in 2NF.
Starting with the initial relation:
Shipping (ShipName, ShipType, VoyageID, Cargo, Port,
ArrivalDate)
We have a partial key dependency as ShipName alone can determine
ShipType. So we normalize to:
SHIPS( ShipName, ShipType)
ShipName -> ShipType
VOYAGES (ShipName, VoyageID, Cargo, Port,
ArrivalDate)
ShipName, ArrivalDate -> VoyageID, Port
VoyageID -> ShipName, Cargo
Now normalize in 3NF:--------
As in 2NF, VOYAGES has a transitive dependency:
ShipName, ArrivalDate -> VoyageID -> Cargo,
So normalize VOYAGES:
SHIPPORTS (ShipName, VoyageID, Port,
ArrivalDate)
ShipName, ArrivalDate -> VoyageID, Port
VoyageID -> ShipName
CARGO (VoyageID, Cargo)
VoyageId -> Cargo
SHIPS( ShipName, ShipType)
ShipName -> ShipType
Add Answer to:
Consider the following schema and functional dependencies:
SHIPPING (ShipName, ShipType, VoyageID, Cargo, Port,
ArrivalDate) Key: ShipName,...
1) Consider the relation R, with key and functional dependencies shown below. What Normal form is...
1) Consider the relation R, with key and functional dependencies shown below. What Normal form is R in right now? Why is this the case? What actions would you take to normalize R to the next higher normal form? (Describe the steps) Follow the steps you described in the prior question to normalize R to the next higher form. Be sure to show all of the steps. Once you have normalized R, what normal forms are each the two new...
The right side of any functional dependency must contain a candidate key. TRUE FALSE » Given a set of functional dependencies F, there always exists a canonical cover of F TRUE FALSE Some schemas can...
The right side of any functional dependency must contain a candidate key. TRUE FALSE » Given a set of functional dependencies F, there always exists a canonical cover of F TRUE FALSE Some schemas cannot be transformed into BCNF FALSE TRUE Every schema can be transformed into 3NF, and the resulting schema is dependency- preserving TRUE FALSE . Any schema that is in BCNF is also in 3NF FALSE TRUE
The right side of any functional dependency must contain a...
Consider the following relation R, where {A, B} is its PK. Assume that R is in...
Consider the following relation R, where {A, B} is its PK. Assume that R is in the first normal form (INF). R (A, B, C, D, E, F) Functional dependencies: FD1: BàC FD2: AàDE FD3: ABàF Why is this table not in 2NF? Specify which FDs make R violate 2NF. Normalize the data shown in this table to second normal form (2NF). Specify the primary and foreign key (if any) in each table of your 3NF relations. Normalize the data...
Language: SQL - Normalization and Functional Dependencies Part 4 Normalization and Functional Dependencies Consider the following relation R(A, B, C, D)and functional dependencies F that hold o...
Language: SQL - Normalization and Functional
Dependencies
Part 4 Normalization and Functional Dependencies Consider the following relation R(A, B, C, D)and functional dependencies F that hold over this relation. F=D → C, A B,A-C Question 4.1 (3 Points) Determine all candidate keys of R Question 4.2 (4 Points) Compute the attribute cover of X-(C, B) according to F Question 43 (5 Points) Compute the canonical cover of F.Show each step of the generation according to the algorithm shown in class....
Consider a relational schema R(A, B, C, D) with a set of functional dependencies F =...
Consider a relational schema R(A, B, C, D) with a set of functional dependencies F = { D --> AB, C --> B, CD --> A, AD --> B, B --> A } a. Compute { B, C }+ b. Show that { C, D } is a candidate key of R. c. Is { R1(A, B, C), R2(C, D ) } a lossless-join decomposition? Why? d. Compute a minimal cover Fmin of F.
Consider the relational schemas given below and the respective sets of functional dependencies va...
Write the complete proof.
Consider the relational schemas given below and the respective sets of functional dependencies valid in the schemas For each one of the relational schemas, determine the highest normal form, which is valid for a schema. Justify your answer If a schema is not in BCNF, then decompose it into a minimum number of schemas so that each one of them is in BCNF. Justify your answers. Justification must include the derivations of minimal keys from the...
Consider a relation R(A,B,C,D,E) with the following functional dependencies: 8. AB C BCD CDE DEA (a) Specify all candidate keys for R. (b) Which of the given functional dependencies are Boyce-Codd No...
Consider a relation R(A,B,C,D,E) with the following functional dependencies: 8. AB C BCD CDE DEA (a) Specify all candidate keys for R. (b) Which of the given functional dependencies are Boyce-Codd Normal Form (BCNF) violations'? (c) Give a decomposition of R into BCNF based on the given functional dependencies. (d) Give a different decomposition of R into BCNF based on the given functional dependencies. (e) Give a decomposition of R into 3NF based on the given functional dependencies.
Consider a...
Question 1: Functional Dependencies [7 marks Consider a relation R on attributes (A, B, C, D,...
Question 1: Functional Dependencies [7 marks Consider a relation R on attributes (A, B, C, D, E, F,G, H) and the following functional dependen- cies. B →G C →D DE →GC → EF DEF → H (a) What is the closure of [F, G, Hy? (b) List all of the candidate keys of R under the dependencies above. (c) List all of the FDs above that are 3NF violations (d) List all of the FDs above that are BCNF violations....
DATABASE NORMALIZATION Answer the following questions for this relational schema and functional dependencies: R (A, B,...
DATABASE NORMALIZATION Answer the following questions for this relational schema and functional dependencies: R (A, B, C, D, E, F, G, H, I) A -> C ; C -> D ; A,C -> D B -> E,F ; A,B -> G ; G -> H,I ; A,G -> I List all candidate keys of R. (2 marks) Does the functional dependency A,C -> I hold? (1 mark) Does the functional dependency B -> F hold? (1 mark) Normalize R into...
Consider a relation R(A,B,C,D,E) with the following functional dependencies: 8. AB C BCD CDE DEA (a)...
Consider a relation R(A,B,C,D,E) with the following functional dependencies: 8. AB C BCD CDE DEA (a) Specify all candidate keys for R. (b) Which of the given functional dependencies are Boyce-Codd Normal Form (BCNF) violations'? (c) Give a decomposition of R into BCNF based on the given functional dependencies. (d) Give a different decomposition of R into BCNF based on the given functional dependencies. (e) Give a decomposition of R into 3NF based on the given functional dependencies.
The right side of any functional dependency must contain a candidate key. TRUE FALSE » Given a set of functional dependencies F, there always exists a canonical cover of F TRUE FALSE Some schemas cannot be transformed into BCNF FALSE TRUE Every schema can be transformed into 3NF, and the resulting schema is dependency- preserving TRUE FALSE . Any schema that is in BCNF is also in 3NF FALSE TRUE
The right side of any functional dependency must contain a...
Language: SQL - Normalization and Functional
Dependencies
Part 4 Normalization and Functional Dependencies Consider the following relation R(A, B, C, D)and functional dependencies F that hold over this relation. F=D → C, A B,A-C Question 4.1 (3 Points) Determine all candidate keys of R Question 4.2 (4 Points) Compute the attribute cover of X-(C, B) according to F Question 43 (5 Points) Compute the canonical cover of F.Show each step of the generation according to the algorithm shown in class....
Write the complete proof.
Consider the relational schemas given below and the respective sets of functional dependencies valid in the schemas For each one of the relational schemas, determine the highest normal form, which is valid for a schema. Justify your answer If a schema is not in BCNF, then decompose it into a minimum number of schemas so that each one of them is in BCNF. Justify your answers. Justification must include the derivations of minimal keys from the...
Consider a relation R(A,B,C,D,E) with the following functional dependencies: 8. AB C BCD CDE DEA (a) Specify all candidate keys for R. (b) Which of the given functional dependencies are Boyce-Codd Normal Form (BCNF) violations'? (c) Give a decomposition of R into BCNF based on the given functional dependencies. (d) Give a different decomposition of R into BCNF based on the given functional dependencies. (e) Give a decomposition of R into 3NF based on the given functional dependencies.
Consider a...
Question 1: Functional Dependencies [7 marks Consider a relation R on attributes (A, B, C, D, E, F,G, H) and the following functional dependen- cies. B →G C →D DE →GC → EF DEF → H (a) What is the closure of [F, G, Hy? (b) List all of the candidate keys of R under the dependencies above. (c) List all of the FDs above that are 3NF violations (d) List all of the FDs above that are BCNF violations....
Consider a relation R(A,B,C,D,E) with the following functional dependencies: 8. AB C BCD CDE DEA (a) Specify all candidate keys for R. (b) Which of the given functional dependencies are Boyce-Codd Normal Form (BCNF) violations'? (c) Give a decomposition of R into BCNF based on the given functional dependencies. (d) Give a different decomposition of R into BCNF based on the given functional dependencies. (e) Give a decomposition of R into 3NF based on the given functional dependencies.
Most questions answered within 3 hours.
-
Where is the error in this code sequence?
String s1 = "Hello";
String s2 = "ello";...
asked 10 months ago -
Financial data for Joel de Paris, Inc., for last year
follow:
Joel de Paris, Inc.
Balance...
asked 10 months ago -
Consider this reaction:
Al2(SO4)3 (aq)+ BaCl3
(aq) Al2Cl6 (aq)- +
3BaSO4(s) . What is the...
asked 10 months ago -
Suppose that Savneet is considering increasing her
recent random sample from 20 car rentals to 40...
asked 10 months ago -
Trucks arrive at an unloading terminal at an average rate of 120
per hour.
Trucks arrive...
asked 10 months ago -
Why are methanol and ethanol completely soluble in water while
octanol is not very little soluble....
asked 10 months ago -
A facilities manager at a university reads in a research report
that the mean amount of...
asked 10 months ago -
When the CuSO4 is rehydrated by adding water to the anhydrous
compound, is this an endothermic...
asked 10 months ago -
A ray of sunlight is passing from diamond into crown glass; the
angle of incidence is...
asked 10 months ago -
A block of mass 0.249 kg is placed on top of a light, vertical
spring of...
asked 10 months ago -
how do the kidneys compensate in the presences of acidosis
a) trigger hyperventilate
b) reserve acid...
asked 10 months ago -
Question 501 pts
The rental rate of capital to the firm increases. Which of the
following...
asked 10 months ago