DBST 651 Midterm Part II: Database Normalization and Integrity Analysis

Preview (3 of 9 Pages)

100%

DBST 651 Midterm Part II: Database Normalization and Integrity Analysis - Page 1 preview image

Loading page ...

DBST 651 Midterm Part II: Database Normalization and IntegrityAnalysisDBST 651Midterm Part IIName:Date:Question 1:The table shown below lists dentist-patient appointment data.A patient isgiven an appointment at a specific time and date with a dentist located at aparticular surgery.On each day of patient appointments, a dentist isallocated to a specific surgery for that day.StaffNoDentist NamePatNoPatNameAppointment Date& TimeSurgeryNoS1011S1011S1024S1024S1032S1032James BondJames BondAndrew JoeAndrew JoeKevin KingKevin KingP100P105P108P108P107P110Kim SuJenny GoldIan ChappellIan ChappellJohn SpoonerFrank Holey1/9/05 10.001/9/05 12.001/9/05 10.002/7/05 14.002/7/05 16.302/7/05 18.00S10S15S10S10S15S13Evaluate if the table shown above is normalized, if not describe and illustratethe process of normalizing the table shown above to third normal form.State any assumptions you make about the data shown in this table.(15Points)Solution:To be in 3NF every columnmustrelate to the superkey and it canonlyrelate tothe 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 violationsto 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

DBST 651 Midterm Part II: Database Normalization and Integrity Analysis - Page 2 preview image

Loading page ...

DBST 651 Midterm Part II: Database Normalization and Integrity Analysis - Page 3 preview image

Loading page ...

Thus first break it up into the following 3NF tables:Patients-Key:PatientNo|Columns:PatientNameStaff-Key:StaffNo|Columns:StaffNameNow 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 abit 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:SurgeryNoNote that it has superkey over several values-making the assumption that there is only one staffmember/patient per surgery. Each tuple in this table can be read as "this staff member operatedon this patient scheduled 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, thesuperkey.When implemented in SQL the StaffNo and PatientNo of the Surgeries table would be foreignkeys.It is important here to note that SurgeryNo doesnotrelate to a specific instance of a surgery butrather the 'type' of surgery done, as evidenced by the same SurgeryNo appearing for differentstaff/patient/appoint dates.This is whySurgeries-Key:SurgeryNowould be wrong as there would be duplicate keyvalues, which is prohibited for keys.Several other superkey combinations can be ruled out through logic of the problem domain. Forinstance a staff member cannot have two surgeries at the same time; but two surgeries could beoccurring 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).

Preview Mode

This document has 9 pages. Sign in to access the full document!

Report

Study Now!

Document Details

Related Documents

DevOps and Its Importance Study Notes

Veeam Certified Engineer VMCE-V12

Modules 1 - 3 Basic Network Connectivity and Commu

Notes CCNA

CCNA 200-301 Portable Command Guide 5th Edition 20

CCNA 200-301 Portable Command Guide 5th Edition 22

CCNA 200-301 Portable Command Guide 5th Edition 36

Foundations of Novell Networking Section 26

Modulo 7 DHCPv4

Build and troubleshooting Integration Challenge

Company

Explore

Study Tools