5CI022 Lecture 5

Logical Model – Relational Model

Primary Keys
Relationships
Logical Model – Relational Model

 SUBTITLED: Data Stored in Tables.

 The majority of database management systems
(DBMS) purchased today are relational DBMS's.
 A relational database is based on the relational
model of data described by IBM's Dr. E. F. Codd in
1970 (Codd 1970).
 Codd chose the mathematical concept of relations
as the basis for his model of data.
 It is convenient to think of a relation as being a
table, although in fact a mathematical relation is
different from a table.
 You have been introduced to relational concepts
over the last lectures, here we will look at the
relational model formally.
2
Relational Table
 A table consists of rows and columns:

 If the rows of the table correspond to an instance

of an entity and the columns correspond to
attributes of an entity it is easy to use the table
to represent some real world situation
3
Example – University Database
A database is required to keep information on Students at the University,
which will hold details of the Courses, Lecturers and the Students .

 Suppose we have an entity type:

STUDENT
 Student has a number of
attributes:
 Number
 Name
 Gender (Male, Female)
 Date of birth (DOB)

4
Example
 Certain facts may be known about certain
students
 E.g. Susan is a female student, born on the 1st Feb
1995; Tom is a male student, born on the 23rd March
1991.
Student
StudentN Name Gende DOB
o r
1300912 Susan Female 1st February 1995
1101113 Tom Male 23rd March 1991
0423171 John Male 15th May 1993
1010123 Julie Female 30th December 1981
Each row represents a different student (entity instance)
Each column contains information of the same type
e.g., DOB will contain dates
5
Relational Terminology
 Each row is called a tuple
 A tuple is equivalent to a record in a conventional file
system.
 Each column represents an attribute of the entity type
the table is modelling.
 A row has a fixed number of attributes (represented as
columns of a table).
 The number of attributes in the relation is called
the degree of the relation.
 In the Student example there are four attributes, so the
relation is considered to be of degree four.
 The number of tuples in a relation is called the
cardinality of the relation.
 The cardinality of a relation changes with time, but the
degree remains constant
6
Terminology Continued….
 Attributes in the relational model are
atomic
 That is, if anything smaller than the whole of the
attribute is used it can have no meaning
 For example “1995” on its own does not define a
date of birth
 The relational model also recognises that
attributes should only take on sensible
values.
 E.g.,Gender is male or female; DOB will be from
a range of ages between 18+.
 The relational model associates all attributes
with a domain. A domain is the set of all
permitted values for that attribute. 7
Domains
 At any time the domain may contain more values
than found in the database.
 E.g., the DOB domain would contain all the possible
DOBs a student can be, but the current set of students
might not exhaust this pool.

 The concept of domain allows integrity checks

on individual attributes to be incorporated into
database systems based on the relational model.
 E.g., comparing two attributes from different domains
may not be sensible, such as trying to compare weight
and quantity, even though both are numeric fields.
 Strict implementation of domains can be likened to
typing in programming languages.
 It can help identify nonsense queries or nonsense joins.
8
Relation example
What is the What is the
E.g., STUDENT degree of this cardinality?
table?
Columns

StudentN Name Gende DOB
o r
1300912 Susan Female 1st February 1995  Rows
1101113 Tom Male 23rd March 1991
0423171 John Male 15th May 1993
1010123 Julie Female 30th December
 Domain for DOB = valid date of births, so each student is at
1981
least 18 years old.
 Why would the following dates be rejected:
29-FEB-2001
12-APT-1967
01-JAN-2010
12-JUL-1901
9
Examples of Attribute Domains
Attribute Domain name Meaning Domain Definition
StudentNo StudentNumber Set of all Number: size 7, starts
s possible student with 2 digit year of
numbers entry + 5 digits

Name StudentNames Set of all pupil Character: size 20

names
DOB DateOfBirth Possible values Date: calculated to be
of student DOBs 18 and above.
Format dd-mm-yyyy

Gender Genders The gender of a Character: size 1, value

student either M or F

10
Summary of Terminology
Relational Traditional Informal
Relation File Table
Attribute Field Column
Tuple Record Row
Domain Enforced by Pool of legal values
programs
Degree Number of columns in
relation
Cardinality Number of rows in
relation
11 11
Further Properties
 Relations possess certain properties:
 There are no duplicate tuples
 Tuples are unordered (top to bottom)
 Attributes are unordered (left to right)
 All attribute values are atomic

12
Relational Keys
 Each entity in the Data Modeller tool has an
identifier, or key, which is used to uniquely
distinguish one entity instance from another.
 The relational model has a similar concept.
 Each tuple in a relation must be different in some way
 At least one field must hold unique values, e.g., a student
number
 Some relations may have more than one field that
contains unique values, e.g., an employee could have an
employee number and a national insurance number
 These are the candidate keys
 In some cases a composite key is needed to provide
uniqueness
 The key must contain the minimum set of attributes
needed to uniquely identify one tuple from another.

13
Relational Keys
 Primary Key
 Only one of the candidate keys is selected to become
the primary key
 If there is only one candidate key, it automatically becomes
the primary key.
 Alternate Keys
 If a relation has several candidate keys, the ones not
chosen to be the primary key, become alternate keys.
 Use UNIQUE to enforce this in SQL.
 Foreign Key
 An attribute that is used to represent a relationship
between two relations.
 Note on the ER diagram, foreign keys should not be included.
These will be added when we map our diagram to tables

14
Representing the relations
 When showing the structure of a relation,
you do not need to draw out the tables
 Each relation can be shown as a relational
heading in the format of:
RelationName(primaryKey field(s), field1, field2,
….)
 E.g.
Student(studentNo, studentName, gender, dob)

15
Student Database – ER Model
 An ER Diagram for the Student Database could
be as follows:

16
ER to Relational Mapping
 On the following relational headings
that represents the Student database,
identify at least one of the following:
1. relation
2. primary key
3. foreign key
4. one-to-many (1:N) relationships
5. one-to-one (1:1) relationships
6. many-to-many (M:N) relationship

17
Student Database – Relational Model
1 2

Student (studentNo, studentName, gender, dob) 5

Course (courseCode, courseName, courseLeader)
Lecturer (employeeNo, name, salary, gender, dob,
courseCode) 4
Module (moduleNo, moduleName, noOfCredits,
location)
Enrolment (studentNo, moduleNo, year_taken,
final_grade) 6
Registration (studentNo, courseCode)
3

18
Foreign keys
 What does this mean in practice?
 They are represented by including the primary key of the
relation at the 1 end of a relationship as a foreign key in the
relation at the many end.
 E.g., Lecturer is a personal tutor of a Course
The Course table holds details about the courses:
Course
CourseCode CourseName Etc
CS006 BSc Computer Technology
CI005 BSc Information Technology
CI008 BSc Business Information Technology
AC001 MSc Business and Maths
CI009 MSc Computer Science

19
 1:M Relationships
 The primary key of Course (CourseCode) is used
as a foreign key in the Lecturer table to represent
the 1:M Relationship:
Lecturer(employeeNo, Name, DOB,…, courseCode)
Sample data for the table:

LECTURE
R
EmployeeNo Name DOB …….. CourseCode
10 Alan Sugar 18-MAR-1955 …….. CI005
20 Peggy Archer 14-JUN-1953 …….. CS006
30 Phil Mitchel 11-JUL-1971 …….. AC001
40 Alan Sugar 11-APR-1978 …….. CS006

20
Using the relationships
 The query "What are the names of lecturers who are
personal tutors on the BSc Computer Technology course?"
can be answered by referring to the two tables.
 Examining the COURSE table to find the course code of the
BSc Computer Technology course: CS006
 Look at the course codes in the LECTURER table to look for any
instances of CS006.
 The LECTURER table records the fact that are two tutors on
this course: Alan Sugar and Peggy Archer.
 The foreign key column is used to allow the two tables to be
connected in a meaningful way.
 There are no explicit links in the database, the connections
are implicit via the data values.
 For example, the course codes in the LECTURER table contain
one of the associated primary key values from the COURSE
table.

21
1:1 Relationships
 1:1 Relationships are just a special case of 1:M
relationships
 The foreign key could be placed in either of the
relations taking part in the relationship
 Generally speaking, the foreign key should be placed at
the side where the relationship is mandatory
 This reduces the number of nulls in the database
 E.g., Each Course is led by a Lecturer:

COURSE(courseCode, courseName,
courseLeader_employeeNo)
22
M:N Relationships
 For M:N relationships, you do not add a foreign key at each end of
the relationship
 For example, this relationship:

 Should not generate these relations:

Student(studentNo, studentName, gender, year, DOB,
courseCode)
Course(courseCode, courseName, other course details, studentNo)
 Can you see why?
 If a course has a 100 students each year, you would have to repeat
the course details for each student.
 A student is not likely to take a 100 courses, but you would still have
to repeat the student details for each course they took
23
M:N Relationships
 A M:N relationship must always be
represented in the relational model with a
separate relation.
 This can be introduced if we have broken
down the M:N relationship into two
identifying 1:N relationships, with a link
entity in between.
 An example of this is the Enrolment link entity
in the Student Database example:
 Enrolment (studentNo, moduleNo, year_taken,
final_grade)

24
24
 Primary key Constraints
 Certain rules apply to primary and foreign key attributes.
 The values in a primary key field must be UNIQUE and NOT NULL.
 If the primary key is composite, the values must be unique across
all the attributes making up the primary key.
 For example, the primary key of Enrolment is both the ModuleNo and
StudentNo:
 Some modules will appear more than once in the ModuleNo attribute
(5CI008).
 Some students will appear more than once in the StudentNo attribute
(0912345).
 However, combined no student takes the same module more than once.
Enrolment
This is called: ModuleNo StudentNo Grade
entity
integrity 5CI008 0912345 B11
6CI008 0912345 A15
6CI007 0811223 C9
5CI008 1011012 A14 25
25
 Foreign key constraints
The foreign key is also subject to constraints.
The values in the foreign key field must match a
value in the related primary key field.
The only exception is if the relationship is optional,
then the foreign key field can be NULL.
For example, in the Enrolment table, the module
number and student numbers must exist in the
related tables
 This is called: referential integrity
Student
Module
StudentNo Name Etc…
ModuleNo Name
0912345 John
5CI008 Intro to DB Enrolment
0811223 Julie
6CI008 DD and Apps ModuleNo StudentNo Grade
1011012 Manjit
6CI007 DT and Admin 5CI008 0912345 B11
6CI008 0912345 A15
6CI007 0811223 C9 studentNos here
moduleNos here must exist in Student
must exist in Module 5CI008 1011012 A14
26
26
 Foreign key constraints
If you define primary and foreign keys in your
tables, the DBMS will enforce these rules.
 You will notice this when you add data to your
tables.
For instance, the DBMS would not allow the first
record below to be added.
Can you see why?
Module Student
ModuleNo Name StudentNo Name Etc…
5CI008 Intro to DB 0912345 John
6CI008 DD and Apps 0811223 Julie
Enrolment
6CI007 DT and Admin 1011012 Manjit
ModuleNo StudentNo Grade
6CC123 1212345 G10
6CI008 0912345 A15
6CI007 0811223 C9
5CI008 1011012 A14
27
27
N-ARY Relationships
Employee Project

 Most examples have requires

shown 2 entities taking

uses

part in the relationship. Employee

Computer
 Some relationships can Project

involve more than 2, used

on
e.g., a ternary (3).
 A link table can be used Computer

to represent this also.

Gives the following tables:
 The primary key of the
Employee(empNo, empName, …..)
3 relations taking part
Computer(serialNo, name, …..)
in the relationship
Project(projectNo, budget,…..)
would be included as
EmpCompProj(empNo, serialNo,
foreign keys in the link projectNo, …..)
table
28
Summary of Relational Model (1)
 Basic Characteristics of the Relational Model:
 Data is viewed as a collection of two-dimensional tables.
 These tables are manipulated by mathematical
operations (relational algebra).
 It is a logical model, independent of physical
considerations (no physical pointers).
 Each relation must have a PRIMARY KEY.
 The primary key cannot be a NULL value and must hold
unique values.
 The formal name for this is: Entity Integrity

29
Summary of Relational Model (2)
 Basic Characteristics of the Relational Model
continued:
 If one or more attributes of a relation hold the primary
key value of another relation, then this attribute (or
attributes) is called a FOREIGN KEY.
 The foreign key may or may not form part of the primary
key.
 An occurrence of a foreign key value in relation A must
match an occurrence of a primary key value in relation
B, or must be NULL.
 That is, made-up values are not allowed
 The formal name for this is: Referential Integrity

30
References and Background Reading
 Reference:
 CODD, E.F. (1970) A Relational Model for Large
Shared Data Banks. Communications of the ACM,
13(6), pp. 377-387
 His 12 rules for a relational database can be found
in many textbooks, a summary can be found here:
http://www.databaseanswers.org/codds_rules.htm
 Background Reading (see module guide for
references):
 Relational Model:
 Chapter 3 in Connolly and Begg
 Chapter 3 in Rob et al.

31