relatinal model
Develop a diagram that shows the functional dependencies in the SHIPMENT relation.
2. In what normal form is “SHIPMENT”? Why?
3.Convert SHIPMENT to BCNF if necessary. Show the resulting table(s) with the sample data presented in SHIPMENT.
Homework Answers
1) Dependencies are
(Origin,Destination) -> Distance
#Shipment -> (Origin,Destination)
2) Currently table is in 1st normal form because Distance can be identified by (Origin, Destination) which in the above table is not the primary key combination hence it is not in 2nd normal form.
3) Converting above table
Origin_Destination_Table(PK = Origin_Distance_ID uniquely defines Origin-Destination)
Origin_DistanceID Origin Destination Distance
1 Seatle Denver 1517
2 Chicago Dallas 1058
3 Boston Atlanta 1214
4 Denver Los Angeles 975
Shipment_Table
Shipment# Origin_DistanceID
409 1
618 2
723 3
824 4
629 1
Databases question Consider the following relation that stores information about students living in dormitories at a...
Databases question
Consider the following relation that stores information about
students living in dormitories at a college:
a. Using these assumptions and stating any others you need to
make, list all the non-trivial functional dependencies for this
relation
b. What are the candidate keys for this relation? Identify the
primary keys
c. Is this relation in third normal form? if not find a 3NF
lossless join decomposition of College that preserves
dependencies.
d.Is the relation or the resulting set of...
Suppose we have the following relation (R) that keeps track of car sales: R(car date_sold, salesperson#,...
Suppose we have the following relation (R) that keeps track of car sales: R(car date_sold, salesperson#, comission_rate, discount) In addition to the functional dependency implied by the primary key (car#), assume the following functional dependencies: date_sold discount salesperson commission_rate Explain why this relation is not in Boyce-Codd Normal Form (BCNF). a. b. Show how you would normalize this relation to achieve Boyce-Codd Normal Form by drawing a new relational schema. Highlight all primary keys by underlining them and use arrows...
Consider relation R(A,B,C,D) with functional dependencies: B → C D→ A BA → D CD →...
Consider relation R(A,B,C,D) with functional dependencies: B → C D→ A BA → D CD → B Decompose R into Boyce-Codd Normal Form (BCNF). Clearly show all intermediary steps.
For the following relations and set of FDs: 1. give a key for the relation; 2....
For the following relations and set of FDs: 1. give a key for the relation; 2. state whether the relation is in BCNF, and if it is not state why: 3. give a set of relations in 3NF equivalent to the original relation 1. (33 points) What is the closure of (A,B) with respect to R(A,B,C,D,E,F,G)if R has the following functional dependencies? (a) GCDE AF BF - ABC FC G (b) D-AC-D A+B ABC 2 33 points for each of...
1. (30 points: 15 each) INormalization: 4NFI Consider the relation SUPPLY below (right), mapped from the...
1. (30 points: 15 each) INormalization: 4NFI Consider the relation SUPPLY below (right), mapped from the ternary relationship SUPPLY in the ER diagram below (left), and answer the questions below Sname Quantity ProiName SUPPLY SUPPLIER N PROJECT SUPPLY Sname ProjName PartNo Quantity Date Parte PART a) Show all functional dependencies and multivalued dependencies applied to the attributes in the SUPPLY relation. Use box notations like Figure 14.13 (note: single arrow head for functional dependency and double arrowhead for multivalued dependency)....
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....
Dependency Very Good dependency (key dependency) : XA where Table Very good If all dependencies i...
Dependency Very Good dependency (key dependency) : XA where Table Very good If all dependencies in a table are '"very good", the table is in BCNF X is a candidate key Good Good dependency: X-> A where If all dependencies in a table are "very good" or "good", the table is in 3NF X is not a candidate key X is a part of a candidate key A is prime attribute Bad Bad dependency (Transitive Dependency): X A where If...
Here's a relation (R), its attributes and its functional dependencies (F): R(A, B, C, D, E)...
Here's a relation (R), its attributes and its functional dependencies (F): R(A, B, C, D, E) C D → B A → D D → C E → C What is the closure of AB ({AB}+)? What is the closure of F (F+)? [ set of closures for all LHS][each LHS on one line] What is the minimal set (cover) for F? Provide a key for relation R (a minimal set of attributes that can determine all attr.) Decompose the...
Consider a table Inventory with the following functional dependencies: cus_code, prod_id cus_name,cus_address,prod_name,unit_cost, quantity cus_code ...
Consider a table Inventory with the following functional dependencies: cus_code, prod_id cus_name,cus_address,prod_name,unit_cost, quantity cus_code cus_name, cus_address prod_id prod_name,unit_cost. a) In what normal form is relation R? Explain why? b) Is it possible to decompose R into a number of relations in order to achieve a higher normal form? Show the tables that result from the decomposition.
database managament systems Weight Price Product Name Milk Amount bought 2 1 lt 5 Coffee 100...
database managament systems
Weight Price Product Name Milk Amount bought 2 1 lt 5 Coffee 100 gr Customer Customer Bar ID Name Code 111 Asli 112233 Kaya Asli 223344 Kaya 122 Melis 112233 Çelik 133 Mesut 223344 Demir 6 Milk 1 lt 5 4 Coffee 100 gr 6 2 a) List all the functional dependencies for the above table. b) What is the primary key of the above table? Why? Explain. c) Is the above table in BCNF? Explain your...
Databases question
Consider the following relation that stores information about
students living in dormitories at a college:
a. Using these assumptions and stating any others you need to
make, list all the non-trivial functional dependencies for this
relation
b. What are the candidate keys for this relation? Identify the
primary keys
c. Is this relation in third normal form? if not find a 3NF
lossless join decomposition of College that preserves
dependencies.
d.Is the relation or the resulting set of...
Suppose we have the following relation (R) that keeps track of car sales: R(car date_sold, salesperson#, comission_rate, discount) In addition to the functional dependency implied by the primary key (car#), assume the following functional dependencies: date_sold discount salesperson commission_rate Explain why this relation is not in Boyce-Codd Normal Form (BCNF). a. b. Show how you would normalize this relation to achieve Boyce-Codd Normal Form by drawing a new relational schema. Highlight all primary keys by underlining them and use arrows...
For the following relations and set of FDs: 1. give a key for the relation; 2. state whether the relation is in BCNF, and if it is not state why: 3. give a set of relations in 3NF equivalent to the original relation 1. (33 points) What is the closure of (A,B) with respect to R(A,B,C,D,E,F,G)if R has the following functional dependencies? (a) GCDE AF BF - ABC FC G (b) D-AC-D A+B ABC 2 33 points for each of...
1. (30 points: 15 each) INormalization: 4NFI Consider the relation SUPPLY below (right), mapped from the ternary relationship SUPPLY in the ER diagram below (left), and answer the questions below Sname Quantity ProiName SUPPLY SUPPLIER N PROJECT SUPPLY Sname ProjName PartNo Quantity Date Parte PART a) Show all functional dependencies and multivalued dependencies applied to the attributes in the SUPPLY relation. Use box notations like Figure 14.13 (note: single arrow head for functional dependency and double arrowhead for multivalued dependency)....
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....
Dependency Very Good dependency (key dependency) : XA where Table Very good If all dependencies in a table are '"very good", the table is in BCNF X is a candidate key Good Good dependency: X-> A where If all dependencies in a table are "very good" or "good", the table is in 3NF X is not a candidate key X is a part of a candidate key A is prime attribute Bad Bad dependency (Transitive Dependency): X A where If...
database managament systems
Weight Price Product Name Milk Amount bought 2 1 lt 5 Coffee 100 gr Customer Customer Bar ID Name Code 111 Asli 112233 Kaya Asli 223344 Kaya 122 Melis 112233 Çelik 133 Mesut 223344 Demir 6 Milk 1 lt 5 4 Coffee 100 gr 6 2 a) List all the functional dependencies for the above table. b) What is the primary key of the above table? Why? Explain. c) Is the above table in BCNF? Explain your...
Most questions answered within 3 hours.
-
4. Without doing any calculations, predict whether the observed
∆T would increase, decrease or remain the...
asked 17 minutes ago -
Based on the range, which of the following sets of scores has
the greatest variability? 3,...
asked 1 hour ago -
Ripples in a pond travel at a velocity of 3 m/s with one peak
passing a...
asked 1 hour ago -
A man stands on the roof of a building of height 13.0 mm and
throws a...
asked 1 hour ago -
The extent to which assets are financed by borrowed funds and
other liabilities is indicated by:...
asked 2 hours ago -
Explain in detail
Germany is the fifth largest economy
explain what goods and services Germany specializes...
asked 2 hours ago -
The density of platinum is 21.45 g/mL. If a cube of platinum
with a mass of...
asked 2 hours ago -
Accounts Receivable
Sales
A/R Posting
Extended Sales Invoice
Packing Slip
Compare invoice to packing slip 2...
asked 2 hours ago -
Michaella, age 23, is a full-time law student and is claimed by
her parents as a...
asked 2 hours ago -
Why are polymers not typically casted into products?
asked 3 hours ago -
When rolling a die 129 times, what is the probability of rolling
a 6 no more...
asked 3 hours ago -
4. A call option currently sells for $7.75. It has a strike
price of $85 and...
asked 3 hours ago