Topic: Functional dependencies and Normalisation Consider the following relation schema for table R: R(ENo, No, PNo, E N

[answerhappygod]

Topic Functional Dependencies And Normalisation Consider The Following Relation Schema For Table R R Eno No Pno E N 1 (142.66 KiB) Viewed 49 times

Topic: Functional dependencies and Normalisation Consider the following relation schema for table R: R(ENo, No, PNo, E Name, E Room, Phone, CCredit, C Level, P Amount) Relation R contains all the information involved in the modeling in respect to staff, courses and projects in the University. Attributes starting with "E" refer to staff, those starting with "C" refer to courses, and those with “P” to projects. Staff, courses and projects are each identified by their unique numbers. Names for staff are not generally unique. A staff is allocated with only one room and phone number, but a room and a phone number can be shared by a few staff. A room may be associated with a few different phone numbers, but a phone number is only mapped to a single room. Each course has a certain number of credits (e.g., 1 or 2) and it is offered at a particular level (e.g., either Undergraduate or Postgraduate). However, multiple courses may have the same number of credits and offered at the same level. Each research project has an amount of funding associated with it. Yet, multiple projects may be supported with the same amount of funding. A staff may be involved in teaching different courses and conducting research in different projects. Also, a course may be delivered by different staff and a research project may involve multiple staff. Your task: 3a. Identify the Functional Dependencies in R. Be sure to only include functional dependencies that satisfy the following 4 rules: 1) Only include non-trivial FDs; 2) Minimise the determinant (LHS), that is, only include full FDs; 3) Maximise the RHS; and 4) Only include FDs that cannot be derived from other FDs using Armstrong's axioms. Please refer to the relevant lecture notes for the details of the above requirements. 3b. Identify the candidate keys of R based on the Functional Dependencies. You need to use the concept of attribute closure to identify the keys. Intermediate steps in this process should be summarised. 3c. Assume that R is in INF. Now normalise the relation to 2NF, 3NF, and BCNF. Be sure to indicate the FDs you are removing at each step, and why. Just giving the decompositions in each of the three Normal Forms is not sufficient. Notes: • Please indicate the primary keys for the normalised tables; • Show the detailed normalisation process, rather than only the final normalisa- tion result.