1 / 33

Databases

Databases. Database Normalisation. Learning Objectives. Design simple relational databases to the third normal form (3NF). Database Normalization.

francis-horton
Download Presentation

Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Databases Database Normalisation

  2. Learning Objectives • Design simple relational databases to the third normal form (3NF).

  3. Database Normalization • A technique for designing relational database tables to minimize duplication of information which can lead to inconsistencies, leading to a loss of data integrity.

  4. Example of Database Normalisation

  5. Example Database • A database is designed to store data about students at a college and the subjects that they study. • All students are based in a tutor group • A tutor supervises all the students in their tutor group • Each subject has one subject teacher only • Students study a number of subjects

  6. Un-Normalised Form (UNF) • A relation with repeating groups (fields which are duplicated an unknown number of times) which have their own primary keys. • (i.e. many to many relationships) • The next table is in un-normalised form.

  7. Table: StudentSubjects Repeating groups • Un-Normalised Form (UNF): • The table has rows/records/tuples which contain a repeated group of attributes (which have their own primary keys, in this case -SubjectID) that represent the subjects each student studies. • Underlined field is the primary key.

  8. UNF Table description / shorthand notation / design: Subjects Student studied by studies • Table: StudentSubjects (StudentID, StudentName, TutorGroup, Tutor, (SubjectID, Subject, Level,SubjectTeacher, Grade)) • The words in brackets separated by a , = Fields / Columns / Attributes • Underlined Field = Primary Key • Inner brackets = Repeating group (many to many relationships) • Some websites / books also underline the primary key of the repeating group e.g. the SubjectID in this case. I will not here as it is I believe it is confusing when thinking about 1NF – see next slides.

  9. First Normal Form (1NF) • Removerepeatinggroups(and their primary keys). • Expand the primarykey to include the unique key of the repeating group(concatenated / combination / compound primary key).

  10. Table: StudentSubjects • First Normal Form (1NF): • Repeating groups have been removed and the primary key has now been expanded to include SubjectID(StudentID + SubjectID = StudentID/SubjectID), therefore the primary key no longer repeats. • I have placed these 2 fields side by side but this is not necessary, I have only done this to make it easier to see the new primary key.

  11. 1NF Table description / shorthand notation / design: • Table: StudentSubjects (StudentID, StudentName, TutorGroup, Tutor, SubjectID, Subject, Level, SubjectTeacher, Grade) • Primary key = StudentID + SubjectID = StudentID/SubjectID • Note the fields used for the combined primary key do not have to be shown together, just underlined. This is why I did not underline before in UNF but even if you did, the inner brackets show the group repetition in UNF and they are no longer in the notation above for 1NF.

  12. Table: Students You can also move the repeating group attributes into a new table at this stage. • Table: Students (StudentID, StudentName, TutorGroup, Tutor) • Table: Subjects (StudentID, SubjectID, Subject, Level, SubjectTeacher, Grade) Dashed underline indicates a Foreign key (a non-primary key in one table which is the primary key of another table). Table: Subjects • There is a one-to-many relationship with Students being the ‘one side’ table and Subjects being the ‘many side‘ table. • The primary key StudentID in the Students table links to the foreign key StudentID in the Subjects table. studies studies StudentID/ SubjectID StudentID/ SubjectID Students Subjects Students Subjects studied by studied by

  13. 2NF Definition • A relation that is in 1NF and every non-primary key attribute is fully dependent on the primary key (no partial dependencies are allowed). • Do any of the fields in the 1NF table depend on only a portion of the primary key? • Yes = Not 2NF • No = 2NF

  14. Not in 2NF because: • Subject, Level, SubjectTeacher depend on the SubjectID but not on the StudentID, and, StudentName, TutorGroup, Tutor depend on the StudentID but not on the SubjectID. • Only the Grade depends on the entire concatenated / combination primary key (StudentID + SubjectID).

  15. To convert a table to 2NF, you must: • Duplicateeach portion of the concatenated / combination primary key into another table and moveany fields that depend wholly on the duplicated portion into this table. Leaving the concatenated / combination primary key and any fields that wholly depend on it, in the original table; also identify each portion as foreign keys. • In this case: • SubjectID, Subject, Level & SubjectTeacher. • StudentID, StudentName, TutorGroup & Tutor. duplicated moved

  16. Table: Grades Table: Students 2NF • Dashed underline indicates a Foreign key (a non-primary key in one table which is the primary key of another table). All fields are fully dependent on the entire primary key of their respective tables. Table: Subjects • There is a one-to-many relationship with Students being the ‘one side’ table and Grades being the ‘many side‘ table. • The primary key StudentID in theStudents table links to the foreign key StudentID in the Grades table (similarly for SubjectIDin the Students & Grades tables). achieves given awarded SubjectID/ Grade StudentID/ Grade Subjects Grades Students shows achieved by

  17. given SubjectID/ Grade Subjects Grades Subjects shows Entity / Table Link Entity / Table • Note that SubjectID/Grade is not a table/entity, it is just a way of showing how the tables/entities Subjects and Grades are linked. • It is also possible to show only the tables and assume there is a link (the primary key - SubjectID - from Subjectsis duplicated in Gradesand is known as the foreign key in Grades). • Due to the primary key of Grades now being StudentID/SubjectID, there are now many SubjectIDs in Subjectsto one StudentID/SubjectID in Grades. • The table without a combination primary key forms (in this case Subjects) the many side and the table with a combination primary key (in this case Students) forms the one side. given Subjects Grades shows

  18. given StudentID/ Grade Grades Students shows Entity / Table Entity / Table Link • Note that StudentID/Grade is not a table/entity, it is just a way of showing how the tables/entities Grades and Students are linked. • It is also possible to show only the tables and assume there is a link (the primary key - StudentID - from Studentsis duplicated in Gradesand is known as the foreign key in Grades). • Due to the primary key of Grades now being StudentID/SubjectID, there are now many StudentIDs in Studentsto one StudentID/SubjectID in Grades. • The table with a combination primary key (in this case Grades) forms the one side and the table without a combination primary key forms (in this case Students) the many side. given Grades Students shows

  19. 2NF Table description / shorthand notation / design: achieves given awarded SubjectID/ Grade StudentID/ Grade Subjects Grades Students shows achieved by Table: Students (StudentID, StudentName, TutorGroup, Tutor) Table: Subjects (SubjectID, Subject, Level, SubjectTeacher) Table: Grades (SubjectID, StudentID, Grade)

  20. 3NF Definition • A relation that is in 1NF and 2NF, and if no non-key field is dependent on another non-key field • (i.e. no one to one relationships).

  21. Table Students is not in 3NF because: • TutorGroup is dependent on Tutor and Tutor is dependent on TutorGroup. Table: Students Students TutorGroup Tutor To convert the table to 3NF, you must: Duplicate one of the non-key fields that depends on another non-key field into another table and move all other non-key fields that depend on another non-key field into this table (in this case: duplicate TutorGroup & move Tutor). Use the duplicatedfield as the primarykey of the new table and as a foreignkey in the original table.

  22. Table: Students • Dashed underline indicates a Foreign key (a non-primary key in one table which is the primary key of another table). 3NF Table: Grades Table: Subjects Table: Tutors Tables: Subjects & Grades were already in 3NF. • Third Normal Form (3NF): • Each table relates to only 1 entity, has only 1 primary key and there are no repeating non-key fields.

  23. 3NF Table description / shorthand notation / design: Students Tutor_TutorGroup • Table: Students (StudentID, StudentName, TutorGroup) • Dashed underline indicates a Foreign key (a non-primary key in one table which is the primary key of another table). • Table: Tutors (TutorGroup, Tutor) • Table: Subjects (SubjectID, Subject, Level, SubjectTeacher) • Table: Grades (SubjectID, StudentID, Grade)

  24. Normalisation Summary • UNF • The table has rows which contain repeated groups of attributes (which have their own primary keys). • 1NF • Removerepeatinggroups(and their primary keys). • Expand the primarykey to include the unique key of the repeating group (concatenated / combination primary key). • 2NF • Duplicateeach portion of the concatenated / combination primary key into another table and moveany fields that depend wholly on the duplicated portion into this table. Leaving the concatenated / combination primary key and any fields that wholly depend on it, in the original table; also identify each portion as foreign keys. • 3NF • Duplicate one of the non-key fields that depends on another non-key field into another table and move all other non-key fields that depend on another non-key field into this table (in this case: duplicate TutorGroup & move Tutor). • Use the duplicated as the primarykey of the new table and as a foreignkey in the original table.

  25. Plenary • When a customer orders flowers, an order form has to be completed. • The order form is shown on the next slide.

  26. Plenary • Create a table in UNF, called ORDER, which contains all the attributes shown on the order form. • Explain why it is not normalised.

  27. Plenary • ORDER (OrderNumber, CustomerNumber, Date, (Quantity), (FlowerID)) • ORDER is not normalised as it is has repeating groups.

  28. Plenary • Show this data model in 1NF, 2NF and finally 3NF.

  29. Plenary • Remove the repeating groups to obtain 1NF. • ORDER (OrderNumber, CustomerNumber, Date, Quantity, FlowerID)

  30. Plenary • CustomerNumber and Date are not dependent on FlowerID so remove them to another table to obtain 2NF. • ORDER (CustomerNumber, Date) • FLOWERORDER (OrderNumber, FlowerID, Quantity) • This is also in 3NF.

  31. Plenary • The owner of a flower shop uses a relational database to store information about orders placed by customers, and the types of flower in stock. • One entity is defined as CUSTOMERS. • List the attributes which you identify as belonging to this entity.

  32. Plenary • Forename, Surname • Address1, Address2, County • Postcode • Date of last order • Credit limit

More Related