Question 3. Relational model and Normalisation An incorrect relation NursingSkill has been shown below. The primary key of the relation is (NurseId, SkillId). Examine the relation and answer the questions that follow.

Question 3                                                                                                                    3 Marks

Relational model and Normalisation

An incorrect relation NursingSkill has been shown below. The primary key of the relation is (NurseId, SkillId). Examine the relation and answer the questions that follow.

Note: A nurse can have many skills.  A nurse is identified by NurseId and a skill is identified by SkillId

NursingSkill

NurseId Name SkillId SkillName
N001 John Smith S1 Wound Management
N001 John Smith S2 Blood sample collection
N002 Mary Smith S1 Wound Management
N003 Rose Miller S1 Wound Management

 

  1. Explain the ‘insertion anomaly’ that exists in the above relation by providing an example.                                                                                                                                                (1 mark)
  2. What is the highest normal form that NursingSkill relation satisfies and why?

                                                                                                                                                 (1 mark)

  1. Normalise NursingSkill relation into a set of relations that satisfy 3NF using the format as follows:

Customer (CustomerID, CustomerName)

Order (OrderID, Amount, Date, CustomerID)

Foreign key (CustomerID) references Customer

Expert's Answer

Hire Expert Tutors

Get Professional Tutoring at Low Price in Australia


Professional

Tutoring Services

25,187+

Orders Delivered

4.9/5

5 Star Rating

621

PhD Experts

 

Amazing Features

Plagiarism Free

Top Quality

Best Price

On-Time Delivery

100% Money Back

24 x 7 Support

You may also like

TOP