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

help

Hire Expert 

Get a Professional Help


200
Select FileChangeRemove

TOP

Limited Time Offer! - 20% OFF on all Services Get Expert Assistance Today!

X