View the step-by-step solution to:

where i wrote OR means i am not sure which answer should be right. Thanks Instructions:

I have done my assignment , need help to find out if I did it correctly. where i wrote OR means i am not sure which answer should be right.

Thanks


Instructions:

Complete the exercises below and upload to Blackboard when finished. This is an individual assignment.


Part A: Data Anomalies

Consider the table below. For each data anomaly give an example of how the anomaly could affect the data and draw the solution (i.e. new table or tables).

doctor_id doctor_name patient_id diagnosis

D001 Smith P101 infection

D002 Jones P142 hernia

D001 Smith P130 infection

D003 Harris P115 migraine


The set of Candidate Keys is {(patient_id, doctor_id)}

The set of Prime attributes is: {patient_id, doctor_id}

The set of Non-prime attributes is: {doctor_name, diagnosis}

Per first normal form(1NF) rule, an attribute (column) of a table cannot hold multiple values, composite values and nested relation. It should hold only the atomic values. The given data/table is in 1NF form because each attribute has atomic values.

However, it's not in 2NF form because a relation schema R is in second normal form (2NF) if every non-prime attribute is fully functionally dependent (FFD) on the primary key, which is not the case here. The non-prime attributes such as doctor_name and diagnosis must be dependent upon both and not on any of the prime key attribute individually. In the above data doctor_name can be identified by doctor_id and diagnosis can be identified by patient_id independently. This is called the partial functional dependency, which is not allowed in 2NF.

To make the data/table complies with 2NF form, a relation schema R can be decomposed into 2NF relations via the process of 2NF normalization by breaking it into two tables/relations R1 and R2:

a.   A Deletion Anomaly occurs when the deletion of unwanted information results in the deletion of desired information. For example, the given table contains a single database record of patient (patient_id and diagnosis) along with doctor information (doctor_name and doctor_id), suppose if we want to delete Dr. Harris then, it would result in the deletion of the all information about the patient with patient_id P1115, since Dr. Harris is only assigned to the patient P115.


Solution:

         R1:

patient_id diagnosis doctor_id (FK)

P101 infection D001

P142 hernia D002

P130 infection D001

P115 migraine D003

           




              R2:

doctor_id doctor_name

D001 Smith

D002 Jones

D003 Harris





b.   An Insertion Anomaly occurs when inserting essential data into the database is not possible because other data is not there. For example, in this case if a new physician joins the practice but hasn't assigned any patients yet, then we wouldn't be able to insert the doctor's data into the table if patient-id does not allow nulls or no patient_id is there.


Solution:

R1:

patient_id diagnosis doctor_id (FK)

P101 infection D001

P142 hernia D002

P130 infection D001

P115 migraine D003

           




              R2:

doctor_id doctor_name

D001 Smith

D002 Jones

D003 Harris




c.   An Update Anomaly is a data inconsistency that occurs from data redundancy and partial update. In the given data we have data redundancy means we have two rows for Doctor Smith as he belongs to two different patients. If we want to update any information of Dr. Smith, then we have to update both rows otherwise the data will become inconsistent. There is also a possibility that if data is updated in one row only, then we will have 2 different information for same doctor and this will also make data inconsistent. 


Solution:

R1:

patient_id diagnosis doctor_id (FK)

P101 infection D001

P142 hernia D002

P130 infection D001

P115 migraine D003

           




              R2:

doctor_id doctor_name

D001 Smith

D002 Jones

D003 Harris




Part B: Functional Decomposition

Consider the table below. Identify all functional dependencies and write them under the table (i.e. attribute1 -> attribute 2).

DOG

dog_id dog_name dog_breed owner_id owner_name owner_phone

210 Sparky dalmation 21 Madison (210) 555-2100

408 Max dachshund 21 Madison (210) 555-2100

307 Paisley golden retriever 40 Sarah (210) 555-3010

dog_id -->dog_name --->dog_breed


owner_id---> owner_name--->owner_phone


dog_id ---> owner_id




Part C: Normalization

Problem One:

Consider the following relation and convert to the normal form indicated.  If the relation set is in its highest form simply state "2NF is also 3NF" or similar under the appropriate normal form. Make sure your Primary Key and its attribute(s) is/are underlined for full credit.

0NF: 

ORDER [order_num, date, SSN, cust_name, phone, email, (SKU, item_name, price)]

An order has only one customer, but a customer can place many orders.

Each order can have multiple items. 


1NF:

ORDER [order_num, date, SSN, cust_name, phone, email]

ORD_ITEM [order_num, SKU, item_name, price)]

2NF:

ORDER [order_num, date, SSN, cust_name, phone, email]

ORD_ITEM [order_num, SKU]

ITEM [SKU, item_name, price]

OR

ORDER [order_num, date]

CUSTOMER [SSN, cust_name, phone, email]

ORD_ITEM [order_num, SKU]

ITEM [SKU, item_name, price]




3NF

ORDER [order_num, date, SSN(FK)]

CUSTOMER [SSN, cust_name, phone, email]

ORD_ITEM [order_num, SKU]

ITEM [SKU, item_name, price]


OR

"2NF is also 3NF"







Problem Two:

Consider the following relation and convert to the normal form indicated. If the relation set is in its highest form simply state "2NF is also 3NF" or similar under the appropriate normal form. 

0NF:

COURSE [course_num, course_name, prof_name, prof_email, (student_id, student_name, student_email)]

Courses have many students and each student can take many courses. However, a professor can teach many courses, although a course can be taught by only one professor.


1NF:

COURSE [course_num, course_name, prof_name, prof_email]

STUDENT [course_no, student_id, student_name, student_email]


2NF:

COURSE [course_num, course_name, prof_name, prof_email]

STUDENT [course_no, student_id, student_name, student_email]

STU-COU [course_num, student_id]


3NF:

COURSE [course_num, course_name, prof_name (FK)]

PROFESSOR [prof_name, prof_email]

STUDENT [course_no, student_id, student_name, student_email]

STU-COU [course_num, student_id]




Problem Three:

Consider the following relation and convert to the Normal Form identified. Note this problem is already in 1NF. If the relation set is in its highest form simply state "2NF is also 3NF" or similar under the appropriate normal form.

1NF: 

ITEM [SKU, item_name, price]

ITEM-ORDER [SKU, order_num, date]

ORDER-EMPLOYEE [order_num, date, emp_id, emp_name, emp_title]


2NF:

ITEM [SKU, item_name, price]

ITEM-ORDER [SKU, order_num, date]

ORDER-EMPLOYEE [order_num, emp_id]

EMPLOYEE [emp_id, emp_name, emp_title]


3NF:

"2NF is also 3NF"

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question