Given the relation below (note that there is a composite primary key in this relation): (Prescrip...
Given the relation below (note that there is a composite primary key in this relation):
(PrescriptionNumber, PatientID, Date, Drug, Dosage, PrescriberID, PrescriberName, PatientName)
Depending on your assumption for the relation (for example, assume that the value of PrescriberID can uniquely determine the value of PrescriberName),
- Is this relation in at least 3rd normal form? Explain your reasoning.
- If your answer to the 2nd question is ‘No,’ normalize this relation to 3rd normal form (or above). Write down the final relation(s) after normalization. Keep in mind that during normalization, new foreign keys may be introduced to the relation(s).
- Specify the relationships between the normalized tables and highlight the foreign keys.
- List the functional dependencies for the normalized relation(s).
Homework Answers
Add Answer to:
Given the relation below (note that there is a composite primary key in this relation): (Prescrip...
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 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...
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...
Normalize the un-normalized table given below through INF, 2NF and 3NF. Show the normalization pr...
Normalize the un-normalized table given below through INF, 2NF and 3NF. Show the normalization process in a file (e.g., creating tables and drawing lines in MS Word or MS Excel), save as a .pdf file, and submit it through the link available on Canvas. Name your file according to this scheme: yourlastname a3_normalize.pdf. Do not protect your .pdf file. The presentation of your normalization process should mimic the normalization class exercise and the example provided on Canvas In order to...
Assignment 7 Activities: Using the order form below identify entities to be used in a relational...
Assignment 7
Activities:
Using the order form below identify entities to be used
in a relational database. Normalize to first, second, and third
normal form. Clearly identify within each form with the tables in
each form and specify any assumptions made.
Utilize the following format for each table:
TableName(attribute,etc)
Indicate Primary Key by underlining the attribute name. List
Foreign Keys as such FK=attribute, below the table
listing.
Example:
Sales Rep (EmployeeID, MTD Sales, YTD Sales, MTD
Commission, YTD Commission,...
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...
write the answer clearly thank you so much 1. Simple Technology, a Kansas based company, is...
write the answer clearly thank you so much
1. Simple Technology, a Kansas based company, is working on a project to design and implement a database for tracking employees, their personal information, and information about projects. Some of the attributes that they want to store in the database is given as a tabular format below PC de PTitle PM nager PBudget PDept PD ation l Num EN Ed ity, EZ ED pt ED ptam eHRate P1 Payroll Martin 100K Accounting...
Please help me answer the following questions regarding data relationship tables. Employee ID First Name Last...
Please help me answer the following questions regarding data relationship tables. Employee ID First Name Last Name email Title Address Extension Department Department ID Hiring Date Department Phone # 0001 John Smith jsmith Accountant 1300 West st 5775 Accounting 2100 8/1998 407-366-5700 0002 Brian Miller badams Admin Assistant 1552 Palm dr 5367 Human resource 2300 4/1995 407-366-5300 0003 James Miller miller Inventory Manager 2713 Buck rd 5432 Production 2520 8/1998 407-366-5400 0004 John Jackson jackson_sam Sales Person 433 tree dr...
for question (c) do like this 3. A table to record the information of Dentists, Patients,...
for question (c) do like this
3. A table to record the information of Dentists, Patients, and Appointments in a clinic is given below. DentistNo DentistName PatientNo PatientName PatientPhone AppointmentDate Appointment Time AppointmentFee On a single day each patient can have more than one appointment. It is known that DentistNo is unique for each dentist. PatientNo is unique for each patient. PatientNo must not be used as a composite key. A patient is allowed to see any dentist. (a) Use...
E/R diagram: Draw a complete Doctors office system E-R diagram using UML or crow’s foot notation...
E/R diagram: Draw a complete Doctors office system E-R diagram using UML or crow’s foot notation that includes all of the entities, attributes, identifiers. Relationships should be appropriately labeled with verb phrases. Make it neat. Once the E-R model has created, convert the E-R model to a set of relations by using rules. We will call this set of relations the initial set of relations derived from the E-R model. You use to implement your database application in MS Access....
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 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...
Normalize the un-normalized table given below through INF, 2NF and 3NF. Show the normalization process in a file (e.g., creating tables and drawing lines in MS Word or MS Excel), save as a .pdf file, and submit it through the link available on Canvas. Name your file according to this scheme: yourlastname a3_normalize.pdf. Do not protect your .pdf file. The presentation of your normalization process should mimic the normalization class exercise and the example provided on Canvas In order to...
Assignment 7
Activities:
Using the order form below identify entities to be used
in a relational database. Normalize to first, second, and third
normal form. Clearly identify within each form with the tables in
each form and specify any assumptions made.
Utilize the following format for each table:
TableName(attribute,etc)
Indicate Primary Key by underlining the attribute name. List
Foreign Keys as such FK=attribute, below the table
listing.
Example:
Sales Rep (EmployeeID, MTD Sales, YTD Sales, MTD
Commission, YTD Commission,...
write the answer clearly thank you so much
1. Simple Technology, a Kansas based company, is working on a project to design and implement a database for tracking employees, their personal information, and information about projects. Some of the attributes that they want to store in the database is given as a tabular format below PC de PTitle PM nager PBudget PDept PD ation l Num EN Ed ity, EZ ED pt ED ptam eHRate P1 Payroll Martin 100K Accounting...
for question (c) do like this
3. A table to record the information of Dentists, Patients, and Appointments in a clinic is given below. DentistNo DentistName PatientNo PatientName PatientPhone AppointmentDate Appointment Time AppointmentFee On a single day each patient can have more than one appointment. It is known that DentistNo is unique for each dentist. PatientNo is unique for each patient. PatientNo must not be used as a composite key. A patient is allowed to see any dentist. (a) Use...
Most questions answered within 3 hours.
-
3) Consider the combustion reaction for octane (C8H18), which is
a primary component of gasoline.
2C8H18+25O2⟶16CO2+18H2O...
asked 3 minutes ago -
A protein molecule in an electrophoresis gel has a negative
charge. The exact charge depends on...
asked 2 minutes ago -
we have seen the development of e-commerce in the last 20 years
, what do you...
asked 18 minutes ago -
Two blocks are sliding toward one another along a level
horizontal surface. One block is moving...
asked 23 minutes ago -
Oklahoma Corp. uses the indirect method to prepare its statement
of cash flows. Refer to the...
asked 37 minutes ago -
A 1.5 m long string is tied between two posts. what is the
wavelength of the...
asked 40 minutes ago -
The personnel office at a large electronics firm regularly
schedules job interviews and maintains records of...
asked 48 minutes ago -
Determine the P-value for this hypothesis test. (Round to the
nearest three decimal places as needed)...
asked 1 hour ago -
A manufacturer of banana chips would like to know whether its
bag filling machine works correctly...
asked 2 hours ago -
According to an airline, flights on a certain route are on time
85% of the time....
asked 1 hour ago -
Calculate the pH of a buffer solution made with 0.75 M
HC2H3O2 and 0.25 M
NaC2H3O2...
asked 53 minutes ago -
A manufacturer of medical supplies provides the following
partial financial information:
Total fixed costs: $35,000
Unit...
asked 53 minutes ago