1 / 13

Database Design

Database Design. CP4 Revision. Database Design. Careful database design is needed to avoid… Data Redundancy – storing the same data item in more than one place (waste of storage space) Data Inconsistency – two versions of the same data may be different. Entities.

yael-emerson
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 CP4 Revision Computing

  2. Database Design • Careful database design is needed to avoid… • Data Redundancy – storing the same data item in more than one place (waste of storage space) • Data Inconsistency – two versions of the same data may be different. Computing

  3. Entities • An entity is a thing about which data is stored (Eg Customer, Employee, Stock) • A relationship is a link between two entities. Computing

  4. Relationships • One-to-One : One Pupil has one Network Account. • One-to-Many : One Pupil borrows many LibraryBooks • Many-to-Many : One Teacher teaches many Pupils – One Pupil is taught by many Teachers. Computing

  5. Entity-Relationship Diagrams One-to-One One-to-Many Many-to-Many Computing

  6. Key Fields • Each Entity must have a unique key field – the primary key. PatientID is the primary key field. An Entity may have foreign keys – primary key fields of other entities. These provide the links (relationships) between different entities. WardID is a foreign key. Computing

  7. Table Design • The standard notation for writing down the design of a table… • CAPITAL letters for the name of the table • Underline the key field, • Italicise the foreign keys (or overline). • Eg.:PATIENTS (PatientID, Surname, Forename, Disease, WardID) Computing

  8. Database Normalisation • Avoids data duplication • Avoids data inconsistencies • …ensures the best possible design for a database so other applications can use it. Computing

  9. Un-Normalised Data There are a number of repeated fields here… To change into First Normal Form – the repeated groups of fields must go… Split into two linked tables… Computing

  10. First Normal Form • PUPILS ( PupilID, PupilName, DOB, ExamID) • EXAMS ( ExamID, Subject, Level, Date, RoomID, RoomName) • The key field of the PUPILS table is a combined key field. • The link is made through the foreign key ‘ExamID’. To get this into Second Normal Form, no field must be dependant on only part of the key field. Do this by creating a linking table HINT : You need to do this every time you have a Many-to-Many relationship) Computing

  11. Second Normal Form • PUPILS ( PupilID, PupilName, DOB) • EXAMS ( ExamID, Subject, Level, Date, RoomID, RoomName) • PUPIL_SITS(PupilID,ExamID) The Many-to-Many relationship… …must be changed to… Computing

  12. Second Normal Form To make this database into Third Normal Form, there must be no non-key dependencies… ..so separate the Rooms into another table… Computing

  13. Third Normal Form PUPILS (PupilID, PupilName, DOB) EXAMS (ExamID, Subject, Level, Date, RoomID) PUPIL_SITS (PupilID, ExamID) ROOMS (RoomID, RoomName) Computing

More Related