unit 18 database design extended diploma in...
TRANSCRIPT
Unit 18 Database Design
Extended Diploma in ICT
The ERD is a top down method of modelling the real life business
Normalisation is a bottom up method Often based on existing system (possibly paper)
Both methods can be used
They may give different results and the designer has to decide which ones to implement
Make sure the database is:
As intended
Always unambiguous
Make sure the table
Has a field that uniquely identifies each row (the primary key)
Does not contain duplicate fields
Does not repeat the same type of values
Does not contain fields that belong in other tables
Make sure fields
Represent a characteristic of the table subject
Contain a single value
Are atomic (not multipart)
Are not calculated
Are unique throughout the database
Have an appropriate name
The basic levels:
0NF- list of attributes without calculated fields and removal of duplicated data
1NF – the removal of repeating groups
2NF – free from partial-key dependencies
3NF – non-key dependencies removed
Some of the higher levels are named after their originator (e.g. the Boyce-Codd Normal Form (4th stage but not called 4NF!))
Examine the existing documentation
Reports
Screen layouts
UNF - an unnormalised table
1.List all the attributes – a column for each data item
2.Ignore any calculated fields
1. Fields that can be derived from data in other fields
3.Enter sample data into the fields
4.Identify a key
5.Remove duplicate data
Contains repeating attributes
1NF = First Normal Form
Move any repeating attributes to a new table
(Any data field which has more than one entry for a single value of the key).
Identify repeating fields
Place in a new table with a copy of the key from the UNF
Use the original key as part of a new key in the new table (a compound key – which must be unique)
Remove partial key dependencies
Any simple key dependencies go straight to 2NF
Any key which only depends on part of the key for its value
Given A is there only one possible value for B (& vice versa)?
Eg employee name, department number and department name only depend on employee number
Put into a new table
Let A become the primary key
Keep in original table as foreign key
Tables with zero or only one non-key attribute go straight to 3NF
Move to a new table any attributes which are more dependent on another attribute than the key
Move a copy of the attribute that they are dependent on (becomes 1° key)
Remains as foreign key in original table
1NF – no repeating columns
2NF – in 1NF and columns only depend on the whole primary key
3NF – 2NF and all non-key columns depend only on the primary key
Transitive dependency – a non-key column is dependent on another non-key column; remove the columns dependant on non-key items to another table
1. List all the attributes you need to store
2. Remove all calculated fields (eg age,total)
3. Make sure each attribute is atomic
4. Remove any repeating items to a separate table and make a link (using keys) to establish the relation
5. The structure will now be in first normal form
We want a database to record enquiries to the college
We want a database to record enquiries to the college Course
Length
Location
Level
Date
Name
Address
DOB
Age
Action to take
Course
Duration
Tutor
Location
Level
Date Name Address DOB Action
Course ICT
Duration 2
Tutor Bob Higgie
Location Folly Lane
Level 3
Date Name Address DOB Action
07/05/2011 Theo Walcott The Emirates 16/03/1989 Send prospectus
08/06/2011 Brian Cox Manchester 03/03/1968 Ring back
21/07/2011 Kate Winslett Hollywood 05/10/1975 None
Address field is not atomic
We should fix this into address 1 etc, but for simplicity in the examples I am going to leave it.
0NF 1NF 2NF 3NF Table names
0NF 1NF 2NF 3NF Table names
0NF 1NF 2NF 3NF Table names
Course Name
Length
Tutor
Location
Level
Date
Name
Address
DOB
Action
0NF 1NF 2NF 3NF Table names
Course Name
Length
Tutor
Location
Level
(Date
Name
Address
DOB
Action)
0NF 1NF 2NF 3NF Table names
CourseID
Course Name
Length
Tutor
Location
Level
(EnquirerID
Date
Name
Address
DOB
Action)
0NF 1NF 2NF 3NF Table names
CourseID
Course Name
Length
Tutor
Location
Level
(EnquirerID
Date
Name
Address
DOB
Action)
CourseID
Course Name
Length
Tutor
Location
Level
0NF 1NF 2NF 3NF Table names
CourseID
Course Name
Length
Tutor
Location
Level
(EnquirerID
Date
Name
Address
DOB
Action)
CourseID
Course Name
Length
Tutor
Location
Level
EnquirerID
CourseID
Date
Name
Address
DOB
Action
There are no repeating groups
The second group has a compound primary key (EnquirerID/CourseID)
This corresponds to 2 tables
All records can be recombined into one record by using the keys
To put the database into 2NF we need to make sure it is in 1NF and all the attributes (excluding the keys) depend entirely on the primary
key
0NF 1NF 2NF 3NF Table names
CourseID
Course Name
Length
Tutor
Location
Level
(EnquirerID
Date
Name
Address
DOB
Action)
CourseID
Course Name
Length
Tutor
Location
Level
EnquirerID
CourseID
Date
Name
Address
DOB
Action
CourseID
Course Name
Length
Tutor
Location
Level
0NF 1NF 2NF 3NF Table names
CourseID
Course Name
Length
Tutor
Location
Level
(EnquirerID
Date
Name
Address
DOB
Action)
CourseID
Course Name
Length
Tutor
Location
Level
EnquirerID
CourseID
Date
Name
Address
DOB
Action
CourseID
Course Name
Length
Tutor
Location
Level
0NF 1NF 2NF 3NF Table names
CourseID
Course Name
Length
Tutor
Location
Level
(EnquirerID
Date
Name
Address
DOB
Action)
CourseID
Course Name
Length
Tutor
Location
Level
EnquirerID
CourseID
Date
Name
Address
DOB
Action
CourseID
Course Name
Length
Tutor
Location
Level
EnquirerID
Name
Address
DOB
0NF 1NF 2NF 3NF Table names
CourseID
Course Name
Length
Tutor
Location
Level
(EnquirerID
Date
Name
Address
DOB
Action)
CourseID
Course Name
Length
Tutor
Location
Level
EnquirerID
CourseID
Date
Name
Address
DOB
Action
CourseID
Course Name
Length
Tutor
Location
Level
EnquirerID
Name
Address
DOB
EnquiryID
EnquirerID
CourseID
Date
Action
0NF 1NF 2NF 3NF Table names
CourseID
Course Name
Length
Tutor
Location
Level
(EnquirerID
Date
Name
Address
DOB
Action)
CourseID
Course Name
Length
Tutor
Location
Level
EnquirerID
CourseID
Date
Name
Address
DOB
Action
CourseID
Course Name
Length
Tutor
Location
Level
EnquirerID
Name
Address
DOB
EnquiryID
EnquirerID
CourseID
Date
Action
0NF 1NF 2NF 3NF Table names
CourseID
Course Name
Length
Tutor
Location
Level
(EnquirerID
Date
Name
Address
DOB
Action)
CourseID
Course Name
Length
Tutor
Location
Level
EnquirerID
CourseID
Date
Name
Address
DOB
Action
CourseID
Course Name
Length
Tutor
Location
Level
EnquirerID
Name
Address
DOB
EnquiryID
EnquirerID
CourseID
Date
Action
CourseID
Course Name
Length
TutorID
Location
Level
TutorID
Tutor
EnquirerID
Name
Address
DOB
EnquiryID
CourseID
EnquirerID
Date
Action
0NF 1NF 2NF 3NF Table names
CourseID
Course Name
Length
Tutor
Location
Level
(EnquirerID
Date
Name
Address
DOB
Action)
CourseID
Course Name
Length
Tutor
Location
Level
EnquirerID
CourseID
Date
Name
Address
DOB
Action
CourseID
Course Name
Length
Tutor
Location
Level
EnquirerID
Name
Address
DOB
EnquiryID
EnquirerID
CourseID
Date
Action
CourseID
Course Name
Length
TutorID
Location
Level
TutorID
Tutor
EnquirerID
Name
Address
DOB
EnquiryID
CourseID
EnquirerID
Date
Action
Course
Tutor
Enquirer
Enquiry
Put the table name in capital letters
Underline the primary key
List the attributes after the primary key
Put an * next to the foreign keys
COURSE (CourseID, CourseName, Length, TutorID*, Location, Level)
TUTOR (TutorID, Tutor)
ENQUIRER (EnquirerID, Name, Address, DOB)
ENQUIRY (EnquiryID, CourseID*, EnquirerID*, Date, Action)
1NF – no repeating columns
2NF – in 1NF and columns only depend on the whole primary key
3NF – 2NF and all non-key columns depend only on the primary key
Transitive dependency – a non-key column is dependent on another non-key column; remove the columns dependant on non-key items to another table
1. List all the attributes you need to store
2. Remove all calculated fields (eg age,total)
3. Make sure each attribute is atomic
4. Remove any repeating items to a separate table and make a link (using keys) to establish the relation
5. The structure will now be in first normal form
Normalise the data required for our veterinary surgery.
Use the Veterinary Surgery Paper record on the wiki