You have a table R1 with five fields A, b, C, P and S. You have...
You have a table R1 with five fields A, b, C, P and S.
You have the following functional dependencies(this set is not complete, but it allows you to compute all the missing ones that you will need):
B -> A
P -> C
B, P -> A, C, S
1. What is the only choice for the primary key of this table? Why?
2. Give one normal form that the table does not satisfy, explain why.
3. Modify the design of table R1 so that the new design satisfies the first three normal forms. Specify the fields and primary key for each table in the final design. Explain why all of the normal forms are now satisfied.
Homework Answers
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...
Table T1(A, B, C, D, E) functional dependencies: A -> B AC -> DE D ->...
Table T1(A, B, C, D, E) functional dependencies: A -> B AC -> DE D -> C List candidate key(s), and how you found the key(s). Which functional dependency causes BCNF violation? Decompose T1 into BCNF tables. Please show all work here.
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...
a. Design a simple database with at least 5 relations for such applications as “students taking...
a. Design a simple database with at least 5 relations for such applications as “students taking courses taught by faculty in class rooms at a specific time” b. Is the relation R in 3NF? Explain your answer using functional dependencies. c. If your R above is in 3NF, create at least one table from your database that is not in 3NF. If your R is not in 3NF, decompose R into 3NF relations. In each case, you are required to...
3. Using the INVOICE table structure shown in Table P6.3, do the following: Table P6.3 a. ...
3. Using the INVOICE table structure shown in Table P6.3, do the following: Table P6.3 a. Write the relational schema, draw its dependency diagram, and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that an invoice number references more than one product. (Hint: This table uses a composite primary key.) b. Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the...
Relational Database Design Theory, please answers ALL parts because they are related to each other, and I can't separate it into different questions. Please help! By looking at the PHLogger tab...
Relational Database Design Theory, please answers ALL parts
because they are related to each other, and I can't separate it
into different questions. Please help!
By looking at the PHLogger table:
A. List all non-trivial functional dependencies.
B. What is the highest normal form the PHLogger table is in
currently?
C. The external consulting experts at DBInstructor,
Inc., have noticed that city and state of an address can be
inferred by its postal code (zip code). What new functional
dependencies...
The employee is table, the best candidate for primary key is Select one: a. Department ID...
The employee is table, the best candidate for primary key is Select one: a. Department ID b. Employee hiredate c. Employee ID d. Employee salary Question 2 A primary key can be composed of more than one attribute. Select one: True False Question 3 A foreign key allows you to join two tables. Select one: True False Question 4 Given two tables, department (dept_id, dept_location, dept_name) and EMPLOYEE(emp_id, emp_name, emp_dept_id, emp_name, emp_salary), which attribute (column) is the foreign key? Select...
You are designing a table for employee data of a company. You have a requirement to...
You are designing a table for employee data of a company. You have a requirement to identify if an employee is a United States citizen or not. You have designed a table called EMPLOYEE to store information related to each employee and you have a column called US CITIZEN INDICATOR to store if an employee is a US citizen or not. You only want to store values "Y" or "N" in this column. From the following options, what is the...
For the following table, the primary key is Patient_ID + Test. Explain whether the table satisfy...
For the following table, the primary key is Patient_ID + Test. Explain whether the table satisfy 1NF, 2NF, 3NF, and BCNF. If not, explain how you would achieve normal form(s). Patient ID Test Dr. 1 X-ray Dr. X 1 СТ Dr. C 2 Dr. X2 X-ray Ultrasound 2 Dr. U 2 MRI Dr. M 3 MRI Dr. M2 4 X-ray Dr. X
Consider a relation R with ve attributes A, B, C, D, and E. You are given...
Consider a relation R with ve attributes A, B, C, D, and E. You are given the following functional dependencies: A->B, BC->E, and ED->A. (a) List all keys for R. (10 points) (b) Is R in BCNF? If it is, explain why. If is not, decompose it into a collection of BCNF relations. (20 points) (c) Is R in 3NF? If it is, explain why. If it is not, convert it into a collection of 3NF relations. (20 points)
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...
Relational Database Design Theory, please answers ALL parts
because they are related to each other, and I can't separate it
into different questions. Please help!
By looking at the PHLogger table:
A. List all non-trivial functional dependencies.
B. What is the highest normal form the PHLogger table is in
currently?
C. The external consulting experts at DBInstructor,
Inc., have noticed that city and state of an address can be
inferred by its postal code (zip code). What new functional
dependencies...
You are designing a table for employee data of a company. You have a requirement to identify if an employee is a United States citizen or not. You have designed a table called EMPLOYEE to store information related to each employee and you have a column called US CITIZEN INDICATOR to store if an employee is a US citizen or not. You only want to store values "Y" or "N" in this column. From the following options, what is the...
For the following table, the primary key is Patient_ID + Test. Explain whether the table satisfy 1NF, 2NF, 3NF, and BCNF. If not, explain how you would achieve normal form(s). Patient ID Test Dr. 1 X-ray Dr. X 1 СТ Dr. C 2 Dr. X2 X-ray Ultrasound 2 Dr. U 2 MRI Dr. M 3 MRI Dr. M2 4 X-ray Dr. X
Most questions answered within 3 hours.
-
Phosphorous + bromine = phosphorous tribromide. If 35.0 g of
bromine are reacted and 27.9 grams...
asked 1 hour ago -
Derive the long wavelength limit of the Planck energy density
distribution
asked 53 minutes ago -
Calculate the pH of each of the following solutions.
0.50 M HBr
3.1×10−4 M KOH
4.2×10−5...
asked 4 hours ago -
For the year ended December 31, Depot Max’s cost of merchandise
sold was $85,600. Inventory at the...
asked 4 hours ago -
Week 10 - Professional Memo Assignment
Professional Memo Assignment
Your mission for this week, should you...
asked 4 hours ago -
Write a Python program that stores the data for each
player on the team, and it...
asked 4 hours ago -
In
the last 3 months, mike never knows when he is going to get his
allowance...
asked 5 hours ago -
Is Ca(OH)2 a Bronsted base, Lewis base, or both? Why?
asked 5 hours ago -
1A- Why don’t voters complain about U.S. tariffs on imported
sugar?
Because sugar is only a...
asked 5 hours ago -
Cash Payback Period
Primera Banco is evaluating two capital investment proposals for
a drive-up ATM kiosk,...
asked 5 hours ago -
Create a button in Swift (Xcode) that will create a charge,
create a charge using Stripe's...
asked 5 hours ago -
The reaction rate of CO and NO2 in the reaction
CO(g) + NO2(g) → CO2(g) +...
asked 5 hours ago