Module 5 3 4 Relational and Online Database

Module 5. 3. 4 Relational and Online Database Management Systems Normalisation ©G. Millbery 2005 Relational and Online Database Management Systems Slide 1

First Normal Form (1 NF) Ø Ø A table is in first normal form if all the data values are atomic values In English, this means that there can only be one value per attribute • The following table records managers of shoe shops. One manager can be a manager of more than one shop Manager Shop Shaw Gloucester, Bristol Jones Trafalgar Smith Ashford, Canterbury Greg Brighton, Hove ©G. Millbery 2005 This is not in 1 NF because each attribute does not contain a single value. For example, Shaw is manager of Gloucester and Bristol. To be in 1 NF these values need to be separated Relational and Online Database Management Systems Slide 2

First Normal Form (Cont. ) Manager Shop Shaw Gloucester, Bristol Jones Trafalgar Smith Ashford, Canterbury Greg Brighton, Hove Ø This is not in 1 NF because the values are not atomic Manager Ø Gloucester Shaw Bristol Jones Trafalgar Smith Canterbury Smith Ashford Brighton Greg ©G. Millbery 2005 Shaw Greg This is in 1 NF because each cell has only 1 value Shop Hove Relational and Online Database Management Systems Slide 3

First Normal Form - Difficulties Ø Terminology differences: • • • Atomic Indivisible Scalar » These all refer to a single value per attribute Ø Attributes to be careful of include: • • • Ø Address – e. g 24 The Grange, Oxford. OX 4 6 JP is not scalar Telephone – e. g 01234 567890 is not scalar (code and number) Atomic does not only refer to the data, but to the use of data. Just because you can split data does not means that you should. Telephone should only be split if you need to sort by code for example Official Definition: • At the end of 1 NF you end up with a table. In order to be a table it must have a primary key. This is officially correct and it may be necessary to add a field to give a primary key ©G. Millbery 2005 Relational and Online Database Management Systems Slide 4

Terminology: Primary Key Ø A primary key is a unique value which allows each record to be identified Customer. ID Last. Name 1 Brian Smith 2 Harry Adams 3 Joe Jones 4 Ø First. Name Harry Smith First. Name or Last. Name cannot be primary keys as they contain duplicate data. Customer. ID uniquely identifies a row and is therefore suitable ©G. Millbery 2005 Relational and Online Database Management Systems Slide 5

Terminology: Primary Key (Cont. ) Ø Sometimes there is no single field appropriate as a primary key. In these circumstances, it is possible to select two fields which, when taken together create a unique value: Order. No Item. No Employee. No Customer. No Item. Name Quantity 121 3 4 1024 Nut 4 121 4 4 1024 Bolt 3 122 8 9 176 Washer 6 123 3 6 154 Bolt 5 123 8 6 154 Washer 4 There are no unique fields, so the Primary Key is best suited by Order. No and Item. No taken together ©G. Millbery 2005 Relational and Online Database Management Systems Slide 6

Terminology: Functional Dependence Ø Functional Dependency • If you know the length, height and width of a room, you can calculate its volume: » Volume = width x height x length • Volume is functionally dependent on the length, height and width • Consider the following database, which holds orders. Each order is entered by a specific employee (only one employee is allowed to enter an order) ©G. Millbery 2005 Relational and Online Database Management Systems Slide 7

Terminology: Functional Dependency Order. No Item. No Employee. No Customer. ID Item. Name Quantity 121 3 4 1024 Nut 4 121 4 4 1024 Bolt 3 122 8 9 176 Washer 6 123 3 6 154 Bolt 5 123 8 6 154 Washer 4 Employee. No is functionally dependent on Order. No functionally determines Employee. No Order. No is the determinant The relationship is only one way ©G. Millbery 2005 Relational and Online Database Management Systems Slide 8

Terminology: Functional Dependency Ø Functional Dependency – More examples • A table containing Pupil. Name and Pupil. ID » Pupil. Name is functionally dependent on Pupil. ID 34 Smith 65 Sams 87 Hodd 654 Ø Pupil. Name Smith If you know the Pupil. ID you can find any Pupil. Name, but if you only have the name, it is not always possible to find the Pupil. ID ©G. Millbery 2005 Relational and Online Database Management Systems Slide 9

Second Normal Form (2 NF) Ø To be in 2 NF a table must: • • Ø Be in 1 NF (obviously) Have all non key fields fully functionally dependant on the primary key In English: • • A non key field is one that is not part of the primary key It means that you need to use the primary key to determine the value of the other fields in the table » If you can find the value of other fields without using the primary key, you should remove that field from the table and place it in a separate table ©G. Millbery 2005 Relational and Online Database Management Systems Slide 10

Second Normal Form Order. No* Employee. No Customer. ID 121 3 4 121 4 122 Ø Item. No* Quantity 1024 Item. Nam e Bolt 4 1024 Washer 3 8 7 176 Nut 5 3 7 176 Bolt 4 4 This table is not in 2 NF • • The primary key is Order. No and Item. No (combined) The quantity is functionally dependant on the Primary Key » The Item. Name is functionally dependant on the Item. No not the primary key » The Customer. ID is functionally dependant on the Order. No, not the Primary Key » The Employee. No is functionally dependant on the Order. No, not the primary key ©G. Millbery 2005 Relational and Online Database Management Systems Slide 11

Second Normal Form (Cont. ) Order. No* Item. No* Employee. No Customer. ID Item. Name Quantity 121 3 4 1024 Bolt 4 121 4 4 1024 Washer 3 122 8 7 176 Nut 5 122 3 7 176 Bolt 4 Ø We need to remove Item. Name, Customer. ID and Employee. No from the table – this involves setting up new tables ©G. Millbery 2005 Relational and Online Database Management Systems Slide 12

Second Normal Form (Cont. ) Employee. No and Customer. No are functionally dependant on the Order. No, therefore they are suited for a new table: Ø Orders Order. No* Employee. No Customer. No Ø Ø The Quantity is functionally dependant on both the Order. No and the Item. No, therefore they have a new table: We are left with the Item. Name. This is functionally dependent on the item number only, therefore a new table is required: ©G. Millbery 2005 Order. Spec Order. No* Item. No* Quantity Stock Item. No* Item. Name Relational and Online Database Management Systems Slide 13

Second Normal Form (Cont. ) Ø Ø Ø Do not be afraid to create new tables as appropriate but make sure that you are not breaking tables down for the sake of doing so. It should be appropriate and have advantages Make sure that each table you create is in both 1 NF and 2 NF as appropriate Ensure the original table can be rebuilt from the data contained in the new table ©G. Millbery 2005 Relational and Online Database Management Systems Slide 14

Second Normal Form Ø A slightly different approach: • • Each relation should only contain information about a single entity. If it contains information about more than one entity, then the table needs to be broken down For example: » HOUSE(House. Name, Street, Town, City, Postcode, City. Population) • • The City. Population is a separate entity to the house details, so it needs to be removed to a separate table To create a separate table, remove the attribute and a copy of the attribute on which it is dependent. The copy becomes the link between the two tables » HOUSE(House. Name, Street, Town, City, Postcode) » CITY(City, City. Population) ©G. Millbery 2005 Relational and Online Database Management Systems Slide 15

Third Normal Form (3 NF) Ø To be in 3 NF a table must: • • Ø Be in second (and therefore also first) normal form Have all non key fields non transitively dependent on the primary key In English: • Fields which do not form part of the primary key must always be solely dependent on the primary key and not on anything else, such as another non key field • Aside: it is usually difficult to create a table which is not in 3 NF – usually you will jump straight from 1 NF to 3 NF! ©G. Millbery 2005 Relational and Online Database Management Systems Slide 16

Third Normal Form (Cont. ) Ø In the following example, the company keeps data on its employees. Each employee is allocated a city where they work and each city is given a City. ID. Their salary is dependent on the type of job they do. Initially check the table below to ensure it is in 2 NF before continuing Employee. No Last. Name First. Name City. ID Type Salary 1 Taylor Sarah Canterbury CB Manager £ 22, 000 2 Jones Sam London LN Sales Person £ 15, 000 3 Smith Sally Birmingham BM Admin Assistant £ 13, 500 ©G. Millbery 2005 Relational and Online Database Management Systems Slide 17

Third Normal Form (Cont. ) Employee. No Last. Name First. Name City. ID Type Salary 1 Taylor Sarah Canterbury CB Manager £ 22, 000 2 Jones Sam London LN Sales Person £ 15, 000 3 Smith Sally Birmingham BM Admin Assistant £ 13, 500 Ø City. ID is dependent on City Salary is dependent on Type Ø Therefore this table is not in 3 NF Ø ©G. Millbery 2005 Relational and Online Database Management Systems Slide 18

Third Normal Form (Cont. ) Ø Ø Remove City from the table and create a new cities table Remove Salary from the table and create a job type table Staff Employee. ID* Last. Name First. Name Cities City. ID* City Job. Types Type. ID* Salary City. ID Type ©G. Millbery 2005 Relational and Online Database Management Systems Slide 19

Advantages of Normalisation Ø More efficient database structure • • Ø More flexible database structure • • Ø Allows the data to be used for a variety of purposes Data is independent of interface and use Easier to maintain database structure • Ø Better understanding of your data Easier to see how the data fits together Changing, adding and removing data structures is easier with a relational model than any other model Avoids redundant fields • ©G. Millbery 2005 And redundant records which reduced storage space and cost of hard disk drives Relational and Online Database Management Systems Slide 20