In the United States, updates in laws relating to storing personal information have restricted certain organizations from using government issued identification to identify persons, unless certain limitations are guaranteed. Due to this new practice a significant number of Universities won't be able to use of Social Security numbers (SSN) as primary keys.
Typically, a unique Student identification number is issued to each student. Instead of the SSN, this student ID is usually used as the primary key since it can be used in all areas the system.
a. A number of database developers hesitate to use system generated keys (surrogate keys) in lieu of primary keys (example, Student_ID) since they are artificial. Propose by way of explanation any natural choices of keys that you believe would be appropriate to identify a student record in a university database.
b. If you were able to provide assurance that a natural key that includes last name is unique, discuss whether you could guarantee that the last name remains over the lifetime of the database.
c. If changing of the last name is supported by the database design, what solutions can be offered for generating a primary key that still contains the last name but maintains its uniqueness?
For the following table, apply normalization rules to bring to 3rd normal form. Create converted table(s) for each step and give explanations.
| Emp ID* | Name | Manager | Dept | Sector | Spouse/Children |
| 285 | Carl Carlson | Smithers | Engineering | 6G | |
| 365 | Lenny | Smithers | Some Dept | 8G | |
| 458 | Homer Simpson | Mr. Burns | Safety | 7G | Marge, Bart, Lisa, Maggie |
a) Analyse the table below and provide a critique of various update anomalies (insertion, deletion, and modification) that it could be susceptible to. For each anomaly - insertion, deletion and modification, state (present a simple scenario) how the anomaly would manifest.
Details of patient dental appointments.
| staffNo | dentistName | patientNo | patientName | appointment | surgeryNo | |
| date | time | |||||
| S1011 | Tony Smith | P100 | Gillian White | 12-Aug-03 | 10.00 | S10 |
| S1011 | Tony Smith | P105 | Jill Bell | 13-Aug-03 | 12.00 | S15 |
| S1024 | Helen Pearson | P108 | Ian MacKay | 12-Sept-03 | 10.00 | S10 |
| S1024 | Helen Pearson | P108 | Ian MacKay | 14-Sept-03 | 10.00 | S10 |
| S1032 | Robin Plevin | P105 | Jill Bell | 14-Oct-03 | 16.30 | S15 |
| S1032 | Robin Plevin | P110 | John Walker | 15-Oct-03 | 18.00 | S13 |
b) Describe the process of normalizing the table shown above to 3NF (explain how you would do it, illustrations not required). State any assumptions you make about the data shown in this table.
For the following document, where the unnormalized form (ONF) is:
ORDER(order##, customer##, name, address, orderdate (product#, description, quantity, unitprice)), convert the schema to 3NF, showing all intermediate stages (maintain the above format indicated with ONF) namely, INF and 2NF.
Order Form: see image.