1 / 15

Understanding Relational Database Normalisation and Indexing

Learn about relational databases, normalisation process, and indexing to improve database search speed and data integrity.

derrickalvarez
Download Presentation

Understanding Relational Database Normalisation and Indexing

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. Lesson Objectives Aims You should know about: 1.3.2: (a) Relational database, flat file, primary key, foreign key, secondary key, entity relationship modelling, normalisation and indexing.

  2. Some key terms Indexing: An index is a data structure used to shorten the length of time it takes to search a database. An index may point to other sub indexes Because these structures are smaller than the whole database it increases search speed

  3. Normalisation Normalisation is the process of converting a flat file database (with a single table) to a relational database (with many tables). There are various levels of normalisation that remove repetition to a greater or lesser extent.

  4. Normalisation Weird. Because logically, you’d never put data in to the first normal form… But they are clear, well defined steps. Read and make notes on the “Normalisation to 3NF” in the shared area

  5. Task Step 1: Turn the un-normalised data in to 1NF FIRST STEP: To change into First Normal Form – the repeated groups of fields must go. To do this, data must be split in to separate tables

  6. 1st Normal Form A table is in First Normal Form (1NF) if there are no repeating groups. In other words, each column must contain only a single value and each row must have an item in every column. This can usually be done by putting the data into two tables ... separating the repeated data into a separate group.

  7. 1NF

  8. 2NF • To move to 2NF, any partial dependencies must be removed • This basically means each record should not have a composite primary key • This removes: • Many to many relationships • Repeated Data

  9. Many to many In the first table, there is a composite key (PupilID and ExamID) There is also a lot of data repetition (many students taking many exams)

  10. Second Normal Form

  11. Third Normal Form • 3rd Normal Form removes something called “Transitive Dependency” • The advantage of removing transitive dependency is: • Amount of data duplication is reduced. • Data integrity achieved.

  12. What on earth is transitive dependency? Basically it means all data in the table should be dependent solely on the primary key. Any other data should be in a new table

  13. In our 2NF table, ExamID is the PK However, RoomName does NOT depend on ExamID, it is dependent on RoomID. Therefore this data should be in a new table

  14. PUPILS (PupilID, PupilName, DOB) EXAMS (ExamID, Subject, Level, Date, RoomID) PUPIL_SITS (PupilID, ExamID) ROOMS (RoomID, RoomName)

  15. Review/Success Criteria You should know: • How to normalise data • The definitions of the three normal forms • The purpose of normalisation

More Related