1 / 23

Database Design

Database Design. A2 Computing Comp3. Aims and Objectives. To understand the process of breaking down complex data structures using normalisation To understand the need for normalisation in database design To be able to normalise a set of data to produce a relational database design.

amelia-hurley
Download Presentation

Database Design

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. Database Design A2 Computing Comp3

  2. Aims and Objectives • To understand the process of breaking down complex data structures using normalisation • To understand the need for normalisation in database design • To be able to normalise a set of data to produce a relational database design.

  3. What is a relational database? • A database which has many interrelated tables • Each table has a heading (fields / attributes) • Each table contains data (records or tuples) • Tables define entities (objects of interest ) • Entities are linked together to form relationships (1:1, 1:n, n:n) • These objects and relationships are modelled using an ER diagram

  4. Linking database tables • Each table in a database must have a name and a primary key • A primary key is a field or attribute which will uniquely identify each row (tuple) in the table • Sometimes more than one attribute is needed to uniquely identify each row • A composite key is made up of 2 or more attributes to uniquely identify each row in the table • A foreign key is an attribute in one table that is a primary key of another table. • Links between tables are made using the foreign key

  5. Examples Which attribute would be suitable for a primary key?

  6. Examples Which attribute is being used as a foreign key? Can you think of a potential problem using foreign keys?

  7. Database design • How do we come up with a database design that will work? • Some data structures are complex • It can be difficult to represent them accurately without errors and inconsistencies • This man came up with a formal process called normalisation Dr Edgar F Codd

  8. Normalisation • Normalisation is about finding good ways of arranging the data into entities and attributes in a database • It’s a formal process that ensures your database will work efficiently and effectively • Consists of 3 stages • first normal form (1NF) • second normal form (2NF) • third normal form (3NF) • Each stage in normalisation is designed to remove potential problems in entity design

  9. Why normalise? • To ensure data is not duplicated • To ensure data is consistent throughout the database • To ensure the structure of each table is flexible enough to allow you to enter as many or as few items as required • The structure should enable a user to make all kinds of complex queries relating data from different entities

  10. Worked example Primary key for each tuple Sample data held on Students taking courses at college

  11. First Normal form • A table is in first normal form (1NF) if it contains no repeating attributes or groups of attributes. • For each student what info is repeated? (what info occurs more than once with different values each time?) • Establishing 1NF means removing the repeating attributes into another table and adding the primary key from the first table

  12. Establishing 1NF

  13. Standard Notation STUDENT(studentNo,StudentName,DOB,Gender)

  14. Standard Notation STUDENTCOURSE(StudentNo,CourseNo,CourseName,LecturerNo,LecturerName)

  15. Second normal form • A table is in second normal form (2NF) if it is in 1NF and contains no partial key dependencies. • Every attribute in the table which is not a key depends on all keys present • If there is only part dependency then attributes are removed and placed into another table with the part key dependency

  16. Establishing 2NF • Looking at tables with more than one key present STUDENTCOURSE(StudentNo,CourseNo,CourseName,LecturerNo,LecturerName ) • Remove any partial key dependencies • CourseName,LecturerNo,LecturerName depend on CourseNo and not on StudentNo • So we can remove them into a new table with a copy of CourseNo

  17. Establishing 2NF • The Course table becomes COURSE(CourseNo,CourseName,LecturerNo,LecturerName) • The STUDENTCOURSE table becomes STUDENT_COURSE(studentNo, CourseNo) • We now have three tables in 2NF STUDENT(studentNo, studentName, DOB,Gender) STUDENT_COURSE(studentNo, CourseNo) COURSE(CourseNo,CourseName,LecturerNo,LecturerName)

  18. Third normal form • A table is in third normal form (3NF) if it is in 2NF and contains no non-key dependencies • Looking at the tables so far:- STUDENT(studentNo, studentName, DOB,Gender) STUDENT_COURSE(studentNo, CourseNo) COURSE(CourseNo,CourseName,LecturerNo,LecturerName) • Is there anything in these tables that does not depend on the primary key of the table?

  19. Establishing 3NF COURSE(CourseNo,CourseName,LecturerNo,LecturerName) • Lecturer name is not defined by courseNo • Lecturer name is defined by LecturerNo so we remove them into a new table LECTURER(LecturerNo, LecturerName) • The Course table keeps a link to the Lecturer by adding the key COURSE(CourseNo,CourseName,LecturerNo)

  20. Summary • Normalisation is a formal process to determine the structure of a database • 1NF – remove repeating attributes • 2NF – remove part-key dependencies • 3NF – remove non-key dependancies (The key, the whole key and nothing but the key, so help me Codd!”)

  21. Glossary Primary Key: an attribute that uniquely identifies a tuple Composite key: a combination of attributes that uniquely identify a tuple Foreign Key: an attribute in one table that is a primary key in another table Referential integrity: if a value appears as a foreign key in one table it must also appear as a primary key in another Normalisation: a technique used to produce a set of entities with no redundant data

More Related