Scribd Logo
Upload

Welcome to Scribd!

  • 47 views

    Activity 3

    Uploaded by

    Jet
    The document describes an appointment scheduling table that contains insertion, deletion, and modification anomalies. It then shows how to normalize the table from 1st normal form to 2nd and 3rd normal forms by identifying and removing functional dependencies to eliminate anomalies and dividing the table into multiple tables.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd

    Activity 3

    Uploaded by

    Jet
    47 views3 pages
    The document describes an appointment scheduling table that contains insertion, deletion, and modification anomalies. It then shows how to normalize the table from 1st normal form to 2nd and 3rd normal forms by identifying and removing functional dependencies to eliminate anomalies and dividing the table into multiple tables.

    Copyright

    © © All Rights Reserved

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes an appointment scheduling table that contains insertion, deletion, and modification anomalies. It then shows how to normalize the table from 1st normal form to 2nd and 3rd normal forms by identifying and removing functional dependencies to eliminate anomalies and dividing the table into multiple tables.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    47 views3 pages

    Activity 3

    Uploaded by

    Jet
    The document describes an appointment scheduling table that contains insertion, deletion, and modification anomalies. It then shows how to normalize the table from 1st normal form to 2nd and 3rd normal forms by identifying and removing functional dependencies to eliminate anomalies and dividing the table into multiple tables.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    You are on page 1of 3

    ACTIVITY #3

    The table below is susceptible to insertion, deletion, and anomalies .


    appointmentSched
    dentistID dentistName patientID patientName serviceCode
    date time
    D-100 Henry Lim P-100-1 Bella Maria Catan 06/10/2021 1:00 DS-100
    D-100 Henry Lim P-100-2 Kent Paul Pacita 06/12/2021 3:00 DS-200
    D-200 Ana Maria Sy P-200-3 Ralph Mayono 6/14/2021 8:00 S-100
    D-200 Ana Maria Sy P-200-3 Ralph Mayono 07/08/2021 9:30 DS-300
    D-300 John Kent Reyes P-300-3 Kent Paul Pacita 7/21/2021 10:00 DS-300
    D-300 John Kent Reyes P-300-3 Annaliza Cortes 08/10/2021 9:00 DS-100

    1. Provide examples of Insertion, Deletion, and Modification anomalies.

    Insertion Anomalies

    In inserting a new patient particular to make an appointment with the Dentist, we need
    to enter the correct detail for the dentist (ex. dentistID). For instance, to insert the
    details of new patient in patientID, patientName and an appointment, we must enter
    the correct details of the Dentist (dentistID and dentistName) in such way the patient
    details are consistent with values for the designated Dentist.

    Deletion Anomalies

    Example if we want to delete a patient named Ralph Mayono, two records need to be
    deleted as in row 3 and 4. This anomaly also obvious when we want to delete the
    dentistName, multiple records need to be deleted to maintain the data integrity. When
    we delete a dentist record, for instance Ana Maria Sy, the details about his patients also
    lost from the database.

    Modification anomalies

    With redundant data, if we want to change the value of one column of a particular
    Dentist, example the dentistName, we must update all the Dentist records that assigned
    to the particular patient otherwise the database will become inconsistent. Appointment
    schedules also need modification because different Dentist has different schedules.

    2. Assume that a patient may have more than one appointment on a given day and
    may avail of the different dental services.
    Illustrate the process of converting the table from the 1NF to the 2NF and the 3NF.

    1NF:

    FD1

    dentistID apptSchedDate apptSchedTime dentistName patientID patientName serviceCode


    FD2 FD3

    dentistID apptSchedDate apptSchedTime dentistName patientID patientName serviceCode


    FD4

    2NF:

    dentistID apptSchedDate apptSchedTime patientID patientName

    dentistID apptSchedDate serviceCode

    dentistID dentistName

    3NF: FK
    dentistID apptSchedDate apptSchedTime patientID

    FK

    dentistID apptSchedDate serviceCode

    dentistID dentistName

    patientID patientName

    Re-arranging and giving names to the tables.

    dentistID dentistName

    Dentist (dentistID, dentistName)

    FK

    dentistID apptSchedDate serviceCode

    Service (dentistID, apptSchedDate, serviceCode)

    patientID patientName

    Patient (patientID, patientName)

    dentistID apptSchedDate apptSchedTime patientID

    Appointment (dentistID, apptSchedDate, apptSchedTime, patientID)

    You might also like