Transcript Relational Databases

Relational Databases
This topic forms part of the ISDD unit in Higher
Computer Science.
An introduction was made to the relational
database model at Nat 5. At Higher, our depth of
knowledge will be further developed.
Content
Design notation
– ER diagrams
– Data dictionaries
Structures and Links
• Relational database structures
• Keys – primary, foreign and compound
• Relationship cardinality
• Complex database operations – queries, forms,
reports and calculations
Learning Intentions
By the end of this unit, pupils should be able to:
•
•
•
•
•
Describe flat files and relational databases.
Design a simple relational database.
Draw and interpret entity-relationship (E-R) diagrams.
Create data dictionaries.
Define and explain the purpose of primary, secondary and
foreign keys.
• Create relational databases in Microsoft Access.
• Create complex database operations – queries, forms, reports
and calculations.
• Write scripts in SQL.
Databases – Change in Focus
At SG and Nat 5 databases were covered at a more basic level, with flat
file databases being the main focus.
At Nat 5 level, relational databases were touched upon, where the
focus was on the idea of linked tables and an introduction to primary
and foreign keys was made. This is known as the relational model.
The relational model improves data accuracy and has less duplication
of data since data is only entered once, therefore fewer errors occur.
For Higher, a much closer look is taken at the relational model. In
order to begin work on the relational model, it is necessary to firstly
understand the problems (or anomalies) that exist in the flat file
model.
Data Anomalies
• Anomalies are problems that can occur in poorly planned, unnormalised databases where all the data is stored in one table (a
flat-file database).
• Insertion Anomaly - The nature of a database may be such that it is
not possible to add a required piece of data unless another piece of
unavailable data is also added. E.g. A library database that cannot
store the details of a new member until that member has taken out
a book.
• Deletion Anomaly - A record of data can legitimately be deleted
from a database, and the deletion can result in the deletion of the
only instance of other, required data, E.g. Deleting a book loan from
a library member can remove all details of the particular book from
the database such as the author, book title etc.
• Modification Anomaly - Incorrect data may have to be changed,
which could involve many records having to be changed, leading to
the possibility of some changes being made incorrectly.
Example
Let’s look at the Kitten Order Database to illustrate these so called data anomalies.
The following assumptions can be made:
• Customers can buy more than one kitten.
• Kittens can be resold but not to a previous owner.
Kitten Orders Database
Cust ID
Cust
Name
Address
743
Adams
743
Adams
743
Adams
137
Roy
51
Inverdale
Drive
51
Inverdale
Drive
51
Inverdale
Drive
12 Bright
Avenue
654
Fraser
654
Fraser
9 Fair
View
9 Fair
View
First
Time
Own
er
No
Kitten
ID
Kitten
Name
Sex
Kitten
Cost
Litter
ID
DOB
Father
Mother
Date
Ordere
d
Date
Collecte
d
11
Poppy
M
£225
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
9
Fluffy
F
£300
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
12
Patch
M
£250
2
17/7/1
3
Spike
Kitty
21/8/1
3
26/10/1
3
Yes
10
Sprite
M
£225
1
11/10/
13
Stan
Tilly
9/11/1
3
3/1/14
No
3
Coco
F
£350
3
Sid
Betty
No
6
Gerry
M
£275
2
17/7/1
3
18/8/1
3
Spike
Kitty
19/9/1
3
18/9/1
3
12/11/1
3
23/11/1
3
Insertion Anomaly
Kitten Orders Database
Cust ID
Cust
Name
Address
743
Adams
743
Adams
743
Adams
137
Roy
51
Inverdale
Drive
51
Inverdale
Drive
51
Inverdale
Drive
12 Bright
Avenue
654
Fraser
654
Fraser
9 Fair
View
9 Fair
View
First
Time
Own
er
No
Kitten
ID
Kitten
Name
Sex
Kitten
Cost
Litter
ID
DOB
Father
Mother
Date
Ordere
d
Date
Collecte
d
11
Poppy
M
£225
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
9
Fluffy
F
£300
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
12
Patch
M
£250
2
17/7/1
3
Spike
Kitty
21/8/1
3
26/10/1
3
Yes
10
Sprite
M
£225
1
11/10/
13
Stan
Tilly
9/11/1
3
3/1/14
No
3
Coco
F
£350
3
Sid
Betty
No
6
Gerry
M
£275
2
17/7/1
3
18/8/1
3
Spike
Kitty
19/9/1
3
18/9/1
3
12/11/1
3
23/11/1
3
Adding a piece of information to a flat file database may not be possible unless another,
unavailable piece of data can also be added. For example, it is not possible to store the
details of a customer in this database until they have actually ordered a kitten.
Update (or modify) Anomaly
Kitten Orders Database
Cust ID
Cust
Name
Address
743
Adams
743
Adams
743
Adams
137
Roy
51
Inverdale
Drive
51
Inverdale
Drive
51
Inverdale
Drive
12 Bright
Avenue
654
Fraser
654
Fraser
9 Fair
View
9 Fair
View
First
Time
Own
er
No
Kitten
ID
Kitten
Name
Sex
Kitten
Cost
Litter
ID
DOB
Father
Mother
Date
Ordere
d
Date
Collecte
d
11
Poppy
M
£225
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
9
Fluffy
F
£300
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
12
Patch
M
£250
2
17/7/1
3
Spike
Kitty
21/8/1
3
26/10/1
3
Yes
10
Sprite
M
£225
1
11/10/
13
Stan
Tilly
9/11/1
3
3/1/14
No
3
Coco
F
£350
3
Sid
Betty
No
6
Gerry
M
£275
2
17/7/1
3
18/8/1
3
Spike
Kitty
19/9/1
3
18/9/1
3
12/11/1
3
23/11/1
3
Data needing to be changed, may require many records to be changed. This can lead to
errors or omissions being made. For example, if the customer Adams moves house, the
address will have to be changed in 3 separate records which may lead to some being
changed correctly and others not. Imagine this in a much larger database where
hundreds of records might need to be changed.
Deletion Anomaly
Kitten Orders Database
Cust ID
Cust
Name
Address
743
Adams
743
Adams
743
Adams
137
Roy
51
Inverdale
Drive
51
Inverdale
Drive
51
Inverdale
Drive
12 Bright
Avenue
654
Fraser
654
Fraser
9 Fair
View
9 Fair
View
First
Time
Own
er
No
Kitten
ID
Kitten
Name
Sex
Kitten
Cost
Litter
ID
DOB
Father
Mother
Date
Ordere
d
Date
Collecte
d
11
Poppy
M
£225
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
9
Fluffy
F
£300
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
12
Patch
M
£250
2
17/7/1
3
Spike
Kitty
21/8/1
3
26/10/1
3
Yes
10
Sprite
M
£225
1
11/10/
13
Stan
Tilly
9/11/1
3
3/1/14
No
3
Coco
F
£350
3
Sid
Betty
No
6
Gerry
M
£275
2
17/7/1
3
18/8/1
3
Spike
Kitty
19/9/1
3
18/9/1
3
12/11/1
3
23/11/1
3
Deleting the record for customer Roy would be perfectly valid if they decided they didn’t
want to go ahead with the order.
Deletion Anomaly
Kitten Orders Database
Cust ID
Cust
Name
Address
743
Adams
743
Adams
743
Adams
137
Roy
51
Inverdale
Drive
51
Inverdale
Drive
51
Inverdale
Drive
12 Bright
Avenue
654
Fraser
654
Fraser
9 Fair
View
9 Fair
View
First
Time
Own
er
No
Kitten
ID
Kitten
Name
Sex
Kitten
Cost
Litter
ID
DOB
Father
Mother
Date
Ordere
d
Date
Collecte
d
11
Poppy
M
£225
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
9
Fluffy
F
£300
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
12
Patch
M
£250
2
17/7/1
3
Spike
Kitty
21/8/1
3
26/10/1
3
Yes
10
Sprite
M
£225
1
11/10/
13
Stan
Tilly
9/11/1
3
3/1/14
No
3
Coco
F
£350
3
Sid
Betty
No
6
Gerry
M
£275
2
17/7/1
3
18/8/1
3
Spike
Kitty
19/9/1
3
18/9/1
3
12/11/1
3
23/11/1
3
Deleting the record for customer Roy would be perfectly valid if they decided they didn’t
want to go ahead with the order.
Deletion Anomaly
Kitten Orders Database
Cust ID
Cust
Name
Address
743
Adams
743
Adams
743
Adams
654
Fraser
654
Fraser
51
Inverdale
Drive
51
Inverdale
Drive
51
Inverdale
Drive
9 Fair
View
9 Fair
View
First
Time
Own
er
No
Kitten
ID
Kitten
Name
Sex
Kitten
Cost
Litter
ID
DOB
Father
Mother
Date
Ordere
d
Date
Collecte
d
11
Poppy
M
£225
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
9
Fluffy
F
£300
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
12
Patch
M
£250
2
17/7/1
3
Spike
Kitty
21/8/1
3
26/10/1
3
No
3
Coco
F
£350
3
Sid
Betty
No
6
Gerry
M
£275
2
17/7/1
3
18/8/1
3
Spike
Kitty
19/9/1
3
18/9/1
3
12/11/1
3
23/11/1
3
Deleting the record for customer Roy would be perfectly valid if they decided they didn’t
want to go ahead with the order.
This leaves a problem though. What is the address for the customer Roy?
It has been deleted along with the order and is no longer stored in the database so we do
not know.
Keys and Data Dependency
In order to move on it is important to understand the idea of different keys in a relational
database and also the idea of data dependency.
Primary key (or key field) – this uniquely identifies each row in the database table.
Cust ID
Forename
Surname
Address
113
John
Smith
10 Bond Street
107
Carrie
James
9 The Orchard
114
John
Smith
53 Mayburn Road
In the example above, Cust ID uniquely identifies the details for each customer. If we
know that the Cust ID is 107, we know we are referring to details for Carrie James, 9 The
Orchard.
If we didn’t have a primary key, we would have difficulty distinguishing between John
Smith of 10 Bond Street and John Smith of 53 Mayburn Road.
Keys and Data Dependency
Foreign key – allows tables to be joined together and enables relationships between
tables to be created. It is a field in one table that links with the primary key of another
table.
Customer
Order
Cust ID
Forename
Surname
Address
113
John
Smith
10 Bond Street
107
Carrie
James
9 The Orchard
114
John
Smith
53 Mayburn Road
Order ID Product
Name
Quantity
Cust ID
001
spanner
3
113
002
tyre
4
113
001
paint pot
1
107
001
spray gun
5
114
Cust ID is the primary key in the Customer table and a foreign key in the Order table. This
enables a relationship to be created between the tables.
Normalisation
A flat file database contains all the data in one file and is said to be un-normalised (UNF).
Normalisation is a process that removes many of the anomalies discussed in the previous
section.
Kitten Orders Database
Cust ID
Cust
Name
Address
743
Adams
743
Adams
743
Adams
137
Roy
51
Inverdale
Drive
51
Inverdale
Drive
51
Inverdale
Drive
12 Bright
Avenue
654
Fraser
654
Fraser
9 Fair
View
9 Fair
View
First
Time
Own
er
No
Kitten
ID
Kitten
Name
Sex
Kitten
Cost
Litter
ID
DOB
Father
Mother
Date
Ordere
d
Date
Collecte
d
11
Poppy
M
£225
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
9
Fluffy
F
£300
1
11/10/
13
Stan
Tilly
12/12/
13
25/1/14
No
12
Patch
M
£250
2
17/7/1
3
Spike
Kitty
21/8/1
3
26/10/1
3
Yes
10
Sprite
M
£225
1
11/10/
13
Stan
Tilly
9/11/1
3
3/1/14
No
3
Coco
F
£350
3
Sid
Betty
No
6
Gerry
M
£275
2
17/7/1
3
18/8/1
3
Spike
Kitty
19/9/1
3
18/9/1
3
12/11/1
3
23/11/1
3
Normalisation
The information in the un-normalised table can be written a different way as follows:
UNF (un-normalised form)
Kitten Orders
(Cust ID
Cust Name
Cust Address
First Time Owner
Kitten ID
Kitten Name
Sex
Kitten Cost
Litter ID
DOB
Father
Mother
Date Ordered
Date Collected)
Normalisation – 1NF
Cust
ID
Cust
Name
Address
743
Adams
51
Inverdale
Drive
137
Roy
654
Fraser
654
Fraser
12 Bright
Avenue
9 Fair
View
9 Fair
View
First
Time
Owner
No
Kitten
ID
Kitten
Name
Sex
Kitten
Cost
Litter
ID
DOB
11
9
12
Poppy
Fluffy
Patch
M
F
M
£225
£300
£250
1
1
2
Yes
10
Sprite
M
£225
No
3
Coco
F
No
6
Gerry
M
Father
Mother
Date
Ordered
Date
Collected
11/10/13 Stan
11/10/13 Stan
17/7/13 Spike
Tilly
Tilly
Kitty
12/12/13 25/1/14
12/12/13 25/1/14
21/8/13 26/10/13
1
11/10/13 Stan
Tilly
9/11/13
3/1/14
£350
3
17/7/13
Sid
Betty
19/9/13
12/11/13
£275
2
18/8/13
Spike
Kitty
18/9/13
23/11/13
For 1NF remove repeating data to a new entity, along with a copy of the key.
What is repeating data? Focus on one row and for each of the boxes determine how
many items it could hold. Boxes which can hold more than one possible value form
part of the repeating group.
Normalisation – 1NF
Cust
ID
Cust
Name
Address
743
Adams
51
Inverdale
Drive
137
Roy
654
Fraser
654
Fraser
12 Bright
Avenue
9 Fair
View
9 Fair
View
First
Time
Owner
No
Kitten
ID
Kitten
Name
Sex
Kitten
Cost
Litter
ID
DOB
11
9
12
Poppy
Fluffy
Patch
M
F
M
£225
£300
£250
1
1
2
Yes
10
Sprite
M
£225
No
3
Coco
F
No
6
Gerry
M
Father
Mother
Date
Ordered
Date
Collected
11/10/13 Stan
11/10/13 Stan
17/7/13 Spike
Tilly
Tilly
Kitty
12/12/13 25/1/14
12/12/13 25/1/14
21/8/13 26/10/13
1
11/10/13 Stan
Tilly
9/11/13
3/1/14
£350
3
17/7/13
Sid
Betty
19/9/13
12/11/13
£275
2
18/8/13
Spike
Kitty
18/9/13
23/11/13
For 1NF remove repeating data to a new entity, along with a copy of the key.
What is repeating data? Focus on one row and for each of the boxes determine how
many items it could hold. Boxes which can hold more than one possible value form
part of the repeating group.
Normalisation – 1NF
Cust
ID
Cust
Name
Address
743
Adams
51
Inverdale
Drive
137
Roy
654
Fraser
654
Fraser
12 Bright
Avenue
9 Fair
View
9 Fair
View
First
Time
Owner
No
Kitten
ID
Kitten
Name
Sex
Kitten
Cost
Litter
ID
DOB
11
9
12
Poppy
Fluffy
Patch
M
F
M
£225
£300
£250
1
1
2
Yes
10
Sprite
M
£225
No
3
Coco
F
No
6
Gerry
M
Father
Mother
Date
Ordered
Date
Collected
11/10/13 Stan
11/10/13 Stan
17/7/13 Spike
Tilly
Tilly
Kitty
12/12/13 25/1/14
12/12/13 25/1/14
21/8/13 26/10/13
1
11/10/13 Stan
Tilly
9/11/13
3/1/14
£350
3
17/7/13
Sid
Betty
19/9/13
12/11/13
£275
2
18/8/13
Spike
Kitty
18/9/13
23/11/13
For 1NF remove repeating data to a new entity, along with a copy of the key.
What is repeating data? Focus on one row and for each of the boxes determine how
many items it could hold. Boxes which can hold more than one possible value form
part of the repeating group.
Normalisation – 1NF
The information in the un-normalised table can be written a different way as follows:
UNF (un-normalised form)
Kitten Orders
(Cust ID
Cust Name
Cust Address
First Time Owner
Kitten ID
Kitten Name
Sex
Kitten Cost
Litter ID
DOB
Father
Mother
Date Ordered
Date Collected)
Repeating data
Normalisation – 1NF
Kitten Database
Customer
(Cust ID
Cust Name
Cust Address
First Time
Owner)
Order
(Kitten ID
Kitten Name
Sex
Kitten Cost
Litter ID
DOB
Father
Mother
Date Ordered
Date Collected
Cust ID )
The new entity now requires a primary key and any new foreign keys must now be
identified.
Normalisation – 1NF
Kitten
ID
Kitten
Name
Sex
Kitten
Cost
Litter
ID
DOB
Father
Mother Date
Ordered
Date
Collected
Cust
ID
11
Poppy
M
£225
1
11/10/13
Stan
Tilly
12/12/1
3
25/1/14
743
9
Fluffy
F
£300
1
11/10/13
Stan
Tilly
12/12/1
3
25/1/14
743
11
Poppy
M
£300
1
11/10/13
Stan
Tilly
9/3/14
11/4/14
137
3
Coco
F
£350
3
17/7/13
Sid
Betty
19/9/13
12/11/13
654
6
Gerry
M
£275
2
18/8/13
Spike
Kitty
18/9/13
23/11/13
654
Using the sample data above, it is now necessary to determine the primary key and
any foreign key.
Normalisation – 1NF
Kitten
ID
Kitten
Name
Sex
Kitten
Cost
Litter
ID
DOB
Father
Mother Date
Ordered
Date
Collected
Cust
ID
11
Poppy
M
£225
1
11/10/13
Stan
Tilly
12/12/1
3
25/1/14
743
9
Fluffy
F
£300
1
11/10/13
Stan
Tilly
12/12/1
3
25/1/14
743
11
Poppy
M
£300
1
11/10/13
Stan
Tilly
9/3/14
11/4/14
137
3
Coco
F
£350
3
17/7/13
Sid
Betty
19/9/13
12/11/13
654
6
Gerry
M
£275
2
18/8/13
Spike
Kitty
18/9/13
23/11/13
654
We could consider something obvious, such as Kitten ID. However, there are two
entries for Kitten ID 11. They are attached to different customer orders. Kitten ID
alone will not uniquely identify the record.
We could also consider the other obvious choice of Litter ID, however it occurs more
than once, for different kittens, so this would not work either. The same is the case
with Cust ID.
In this example, there is nothing on its own that can act as a unique identifier.
Normalisation – 1NF
Kitten
ID
Kitten
Name
Sex
Kitten
Cost
Litter
ID
DOB
Father
Mother Date
Ordered
Date
Collected
Cust
ID
11
Poppy
M
£225
1
11/10/13
Stan
Tilly
12/12/1
3
25/1/14
743
9
Fluffy
F
£300
1
11/10/13
Stan
Tilly
12/12/1
3
25/1/14
743
11
Poppy
M
£300
1
11/10/13
Stan
Tilly
9/3/14
11/4/14
137
3
Coco
F
£350
3
17/7/13
Sid
Betty
19/9/13
12/11/13
654
6
Gerry
M
£275
2
18/8/13
Spike
Kitty
18/9/13
23/11/13
654
We have to consider a compound key. Think back to the scenario given for this example:
A kitten can be resold but not to the same owner
Kitten ID 11 can therefore not be resold to Cust ID 743. We could use both fields and
make a compound key of Kitten ID and Cust ID. This would provide us with unique rows
in the table.
For 1NF, the foreign key is always in the new table that was created from the repeating
data. It is always the primary key from the original table. In this case Cust ID.
Normalisation – 1NF
Kitten Database
Customer
(Cust ID
Cust Name
Cust Address
First Time
Owner)
Customer Table
Primary key – Cust ID
Foreign Key - none
Order
(Kitten ID
Kitten Name
Sex
Kitten Cost
Litter ID
DOB
Father
Mother
Date Ordered
Date Collected
Cust ID *)
Order Table
Primary key – Kitten ID and Cust ID
Foreign Key – Cust ID
Relationships
After normalisation, a database is likely to have a series of different tables which have to link
together to make the database function efficiently. Relationships are used to map these
links from table to table.
Person and Passport (Person 1:1 Passport)
One person has one passport.
One passport belongs to one person.
Orders and Items (Order 1:M Item)
One order has many items on that order.
One item can be part of one order
Patients and Doctors (Patient M:N Doctor)
One patient in hospital can see many different doctors.
One doctor can see many patients .
Relationships
Many to Many relationships are considered a poor relational database design. For this
reason Many to Many relationships are placed with two One to Many relationships by using
a join table between the two tables.
Patients and Doctors (Patient M:N Doctor)
One patient in hospital can see many different doctors.
One doctor can see many patients .
By using a join table called Appointment we get the following:
Patients, Appointments and Doctors
(Patient 1:M Appointment and Doctor 1:M Appointment)
One patient in hospital can have many appointments but a particular
appointment will be for one patient.
One doctor can take many appointments but a particular appointment will only
be with one doctor .
Relationships
Relationship Type ?
1 to 1
1 to M
M to N
shop and department
person and driving license
car and service
patient and dental appointment
buses and routes
car model and car manufacturer
books and borrowers
dress and designer
Relationships
shop and department
person and driving license
car and service
patient and dental appointment
buses and routes
car model and car manufacturer
books and borrowers
dress and designer
Relationship Type ?
1 to 1
1 to M
M to N
√
Relationships
shop and department
Relationship Type ?
1 to 1
1 to M
M to N
√
person and driving license
√
car and service
patient and dental appointment
buses and routes
car model and car manufacturer
books and borrowers
dress and designer
Relationships
shop and department
Relationship Type ?
1 to 1
1 to M
M to N
√
person and driving license
√
car and service
patient and dental appointment
buses and routes
car model and car manufacturer
books and borrowers
dress and designer
√
Relationships
shop and department
Relationship Type ?
1 to 1
1 to M
M to N
√
person and driving license
√
car and service
√
patient and dental appointment
√
buses and routes
car model and car manufacturer
books and borrowers
dress and designer
Relationships
shop and department
Relationship Type ?
1 to 1
1 to M
M to N
√
person and driving license
√
car and service
√
patient and dental appointment
√
buses and routes
car model and car manufacturer
books and borrowers
dress and designer
√
Relationships
shop and department
Relationship Type ?
1 to 1
1 to M
M to N
√
person and driving license
√
car and service
√
patient and dental appointment
√
buses and routes
car model and car manufacturer
books and borrowers
dress and designer
√
√
Relationships
shop and department
Relationship Type ?
1 to 1
1 to M
M to N
√
person and driving license
√
car and service
√
patient and dental appointment
√
buses and routes
car model and car manufacturer
books and borrowers
dress and designer
√
√
√
Relationships
shop and department
Relationship Type ?
1 to 1
1 to M
M to N
√
person and driving license
√
car and service
√
patient and dental appointment
√
buses and routes
car model and car manufacturer
√
√
books and borrowers
dress and designer
√
√
Entity Relationship Diagrams
ERDs have many different representations. The following are all
valid representations of a one to many relationship between a band
and albums.
Album
Band
Band
Band
1
1
M
makes
1..
*
Album
Album
Entity Relationship Diagrams
Manager
Band
Album
Track
Manager ID
Band Name
Album ID
Song Title
Surname
Genre
Album ID*
Forename
No of
members
Manager ID*
Album
Name
Running
Time
Released
Email
Duration
Track
Position
Band
Name*
Manager
Band
Album
Track
Entity Relationship Diagrams
Kitten Database
Customer
(Cust ID
Cust Name
Cust Address
First Time
Owner)
Order
(Kitten ID*
Date Ordered
Date Collected
Cust ID )
Kitten
(Kitten ID
Kitten Name
Sex
Kitten Cost
Litter ID*)
Litter
(Litter ID
DOB
Father
Mother)
An Entity Relationship (E/R) diagram is used to model the relationships between tables.
The E/R diagram matches primary keys to foreign keys, and displays the cardinality of the
relationships that are displayed as 1:1, 1:M. (In the case of an unnormalised system it
could be possible to display a M:N (many to many) relationship).
Entity Relationship Diagrams
Kitten Database
Customer
(Cust ID
Cust Name
Cust Address
First Time
Owner)
Order
(Kitten ID*
Date Ordered
Date Collected
Cust ID )
Kitten
(Kitten ID
Kitten Name
Sex
Kitten Cost
Litter ID*)
Litter
(Litter ID
DOB
Father
Mother)
An Entity Relationship (E/R) diagram is used to model the relationships between tables.
The E/R diagram matches primary keys to foreign keys, and displays the cardinality of the
relationships that are displayed as 1:1, 1:M. (In the case of an unnormalised system it
could be possible to display a M:N (many to many) relationship).
Entity Relationship Diagrams
Kitten Database
Customer
(Cust ID
Cust Name
Cust Address
First Time
Owner)
Order
(Kitten ID*
Date Ordered
Date Collected
Cust ID )
Kitten
(Kitten ID
Kitten Name
Sex
Kitten Cost
Litter ID*)
Litter
(Litter ID
DOB
Father
Mother)
An Entity Relationship (E/R) diagram is used to model the relationships between tables.
The E/R diagram matches primary keys to foreign keys, and displays the cardinality of the
relationships that are displayed as 1:1, 1:M. (In the case of an unnormalised system it
could be possible to display a M:N (many to many) relationship).
Entity Relationship Diagrams
Customer
Order
Litter
Kitten
1 Customer can make many Orders
1 particular Order can only be for 1 Customer
1 Order will be for 1 particular Kitten.
1 Kitten can be part of many different Orders.
1 Kitten is part of only 1 Litter.
1 Litter can have many Kittens.
Data Dictionary
A data dictionary holds detailed information about database
tables and their attributes including:
-
primary/foreign keys (PK/FK)
data type (and size)
if the contents should be unique
if the contents are required (presence check)
formatting eg currency, dd/mm/yy
any validation that is required (range, lookup, restricted
choice, length check, calculation etc
Data dictionaries hold information on the structure of the data
in the database but not the actual data itself.
Data Dictionary
Order
Table
Field
name
Key
Type(size) Unique Req? Validation
PK/FK
Order ID PK
Number
Yes
Yes
Order
Date
Date
No
Yes
Payment
Type
Text(11)
No
Customer
FK
ID
Lookup
No
Format
Auto increment
00000
from 00000
Restricted
Choice from
[Credit Card,
Yes
Debit Card,
Paypal, Gift
Card]
Lookup from
Yes
Customer
dd/mm/yy
0000
Data Dictionary
Part Order
Table
Field
name
Key
Type(size) Unique Req? Validation
PK/FK
Part ID PK
Number
Yes
Yes
Gift
Wrap
Boolean
No
Yes
Quantit
y
Number
No
Yes
In range >=1 and
<=9
Subtotal
Calculatio
n
No
Yes
=quantity *
product.price
Lookup
No
Yes
Lookup
No
Order
FK
ID
Product
FK
ID
Format
checkbox
Lookup from
Order
Lookup from
Yes
Product
currency
Useful Exam Questions
H IS 2012
2. Define the terms
(a) compound key
(b) foreign key
H IS 2010
2. Golf handicaps are a measure of a golfer’s ability and are constantly adjusted.
Kenneth Cruise has a golf handicap of 12. His golf club list him as KC12, his
initials followed by his current handicap.
State two problems of using this meaningful identifier as a primary key.
Useful Exam Questions
H IS 2013 Q12 a and b
Carter’s Carriage is a transport company which operates a fleet of vans carrying goods between 25 depots
throughout the country. Every trip follows one of a number of set routes between an origin depot and a
destination depot. Refuelling, if necessary on longer routes, is only permitted at a particular town on the
route.
A relational database has been created to help the company. The structure of the data model is as follows:
Driver
Driver number
Driver name
Mobile number
Trip
Driver number*
Reg numer*
Date
Route number*
Van
Reg number
Capacity
Date purchased
Route
Route number
Origin
Destination
Refuel town
(a) Draw an entity relationship diagram to represent the data model.
(b) The data dictionary below represents the Trip entity. State a suitable entry for each of the missing items
A to D.
Attribute
Data
Validation
Unique Key
Type
Driver Number
Reg Number
Integer
A
Date
Date
Route Number
Integer
Lookup from Driver
N
PK/FK
Lookup from Van
N
PK/FK
C
PK
N
D
B
Useful Exam Questions
H IS 2010 Q14
Teachers at North Craig High School organise several trips throughout the year. Teachers may organise
more than one trip in a year and pupils may book more than one trip. A relational database has been set
up to record pupil payments. The entities and attributes are as follows:
PUPIL (Pupil Id, Name, Form Class*)
TRIP (Trip ID, Trip Leader, Destination, Date, Cost)
PAYMENT (Pupil ID*, Trip ID*, Payment date, Amount)
FORM CLASS (Form class, Form teacher)
(a) Draw an entity relationship diagram to represent this data model.
(b) The data dictionary below represents the Payment entity. State a suitable entry for each of the missing
items A to C.
Attribute
Data
Validation
Required Key
Type
Pupil ID
Integer
Trip ID
Text
Payment Date
A
Amount
Real
Lookup from Pupil
B
Y
PK/FK
Y
PK/FK
Y
C
Y
Useful Exam Questions
H IS 2011 Q17a and 17c
SUPPLIER (Supplier Name, Address1, Address2)
RENTAL (Customer ID*, Tool ID*, Date, Insurance)
CUSTOMER (Customer ID, Forename, Surname, Address1, Address2, Tel No)
TOOL (Tool ID, Type, price, Supplier Name *)
(a) Draw an entity relationship diagram to represent the structure of this database.
(b) Part of the data dictionary is shown below. State a suitable entry for each of the items A to D.
Entity
Attribute
PK
FK
Required
Unique
Type
SUPPLIER
Supplier Name
Y
N
Y
Yes
Text
Address1
N
Y
No
Text
Address2
N
Y
No
Text
Customer ID
A
Y
C
Text
RENTAL
Tool ID
Date
N
N
B
Validation
D
Database Implementation
You will use MicroSoft Access to implement your database design. At Higher
level you are required to undertake complex database operations, all of which
are covered in the practical task booklet:
Queries – used to interrogate a database in order to find specific pieces of
information. For example a query may be used to find all the kittens born on
a particular date.
Forms – Provides a user interface which makes it easier to enter data for
example.
Reports – reports present information based on a table or query in a readable
attractive way. Records can be sorted in a particular way, show only certain
fields and provide summaries based on calculations.
SQL – Structured Query language
Information in a relational database can be accessed and modified using a
scripting language called SQL. Common SQL commands used are SELECT,
WHERE, INSERT, UPDATE and DELETE.
SELECT Kitten ID, Kitten Name, Sex
FROM Kitten
WHERE Kitten Cost < 300
This means find the Kitten ID, Kitten Name and Sex of kittens stored in the
Kitten table that cost less than £300.
You will work through SQL tutorials at www.brightredbooks.net and also at
www.w3schools.com/sql