Skip to content
Solved Assignments

DBST 651 Midterm Part II: Database Normalization and Integrity Analysis

Explores database normalization and data integrity techniques.

Preview 3 of 9 pages

See the first 3 pages exactly as they appear in the document.

  1. Page 1
    Read page 1 as text

    DBST 651 Midterm Part II: Database Normalization and Integrity Analysis DBST 651 Midterm Part II Name: Date: Question 1: The table shown below lists dentist - patient appointment data. A patient is given an appointment at a specific time and date with a dentist located at a particular surgery. On each day of patient appointments, a dentist is allocated to a specific surgery for that day. StaffNo Dentist Name PatNo PatName Appointment Date & Time SurgeryNo S1011 S1011 S1024 S1024 S1032 S1032 James Bond James Bond Andrew Joe Andrew Joe Kevin King Kevin King P100 P105 P108 P108 P107 P110 Kim Su Jenny Gold Ian Chappell Ian Chappell John Spooner Frank Holey 1/9/05 10.00 1/9/05 12.00 1/9/05 10.00 2/7/05 14.00 2/7/05 16.30 2/7/05 18.00 S10 S15 S10 S10 S15 S13 Evaluate if the table shown above is normalized, if not describe and illustrate the process of normalizing the table shown above to third normal form. State any assumptions you make about the data shown in this table. (15 Points) Solution: To be in 3NF every column must relate to the superkey and it can only relate to the superkey. In the current table this does not hold and it is not 3NF. Trivially, the current superkey is (StaffNo, PatNo, SurgeryNo) but there are many violations to this which are realized in the form of duplicated values. Namely: • A Patient Name only depends on the Patient; not the Staff or the Surgery • A Staff Name only depends on the Staff member; not the Patient or the Surgery • A Surgery [type] depends on the Staff/Patient/Appointment Date

  2. Page 2
  3. Page 3
    Read page 3 as text

    Thus first break it up into the following 3NF tables: Patients - Key: PatientNo | Columns: PatientName Staff - Key: StaffNo | Columns: StaffName Now we have two tables such that the columns they contain relate to, and only relate to, the [super] key which meets 3NF (along with 2NF requirements). However, we have currently lost a bit of information: which Staff member does which Surgery for which Patient? And when? Now, this is where it gets a bit tricky, but consider this table: Surgeries - Key: (StaffNo, PatientNo, AppointmentTime) | Columns: SurgeryNo Note that it has superkey over several values - making the assumption that there is only one staff member/patient per surgery. Each tuple in this table can be read as "this staff member operated on this patient sche duled to start at this time and did this surgery type". This table meets 3NF because the type of surgery done depends on, and only depends on, the superkey. When implemented in SQL the StaffNo and PatientNo of the Surgeries table would be foreign keys. It is important here to note that SurgeryNo does not relate to a specific instance of a surgery but rather the 'type' of surgery done, as evidenced by the same SurgeryNo appearing for different staff/patient/appoint dates. This is why Surgeries - Key: SurgeryNo would be wrong as there would be duplicate key values, which is prohibited for keys. Several other superkey combinations can be ruled out through logic of the problem domain. For instance a staff member cannot have two surgeries at the same time; but two surgeries could be occurring at the same time. And the same patient can come back for the same surgery again, possibly with the same staff - but never at the same appointment date. Depending upon actual 'rules' the Surgeries superkey could also be (StaffNo, AppointmentTime) or (PatientNo, AppointmentTime).

6 more pages in the full document

Unlock it once and it stays in your library, ready to chat with or turn into flashcards.

Unlock the full document

More Information Technology study materials

See all

Study this document with CramX

Once it is in your library, every tool can work from it.