INFO151 2019-T1 Assignment 4 (20%)
1. Examine the dependency diagram carefully and answer the questions below.
(a) Normalisetheabovedependencytableto3rdnormalform.Youmustshowtheprogressfrom 1NF to 2NF, and then to 3NF. Indicate all the primary keys and foreign keys in the normalised tables. [8 marks]
(b) Write the DDL to create the final 3NF tables.For primary keys use the ‘Integer’ datatype, and for non-key attributes use the ‘Text’ data type. For attributes F and G use the affinity data type that can store date and time. Use TBL1, TBL2, and TBL3 etc. as the table name. [6 marks]
2. A student record form is shown below. A student can only register for ONE degree programme. The Course title is allowed to change after a full year.
(a) Convert the form into a table, then identify the primary key in the table. [4 marks]
(b) Draw all the dependencies and identify the type of dependency for the 1NF table. [6 marks]
(c) Normalise the table into 3rd normal form. You must show the progress from 1NF to 2NF, and
then to 3NF. Indicate all the primary keys and foreign keys in the normalised tables [8 marks]
Use the table names StudentInfo, CourseInfo and GradeInfo to represent the normalised
tables respectively. You can use the affinity data type for the attributes in the tables. [8 marks]
(e) Write the SQL in a correct sequence to insert all the data shown in the sample form into the
respective tables. [6 marks]
(f) Write the SQL to find the total number of ‘A+’ grade for each student. The SQL should display ‘StudentID’ and the ‘Total number of A+ Grade’ including student with no ‘A+’ Grade when more data are available. Use the table names provided in (d). Use only the “JOIN ON or LEFT JOIN” method for joining tables. [6 marks]
3. A table to record the information of Dentists, Patients, and Appointments in a clinic is given below.
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) Useexampleswithdatatoillustratewhytheabovetableisnotagooddatabasesolutionwith respect to insert, delete and update anomalies. [6 marks]
(b) What is the entity relationship between Dentist and Patient? Provide examples with data to illustrate the relationship.[2 marks]
(c) Provide a normalised 3NF solution. You must show the progress from 1NF to 2NF, and then to 3NF. Indicate all the primary keys and foreign keys in the normalised tables. [8 marks]
(d) If the constraint changes such that a patient can only see a particular dentist, are there any changes to the 3NF tables? Explain your answer to get full mark. [2 marks]
(1) Submit your assignment as a PDF file only (to avoid misalignment in the diagrams).
will receive 0 marks.
(4) Any diagrams and representation method that are not the same as those taught in workshop
and lecture will receive 0 marks.
Total Number of A+ Grade
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:itcsdx