Entity-Relationship Diagrams
Entity-Relationship Diagrams
Jianjun Chen
Database Design
This lecture introduces the technique to design a database from a piece of written requirements.
Need to consider
What is the database going to be used for?
What tables, attributes, keys are needed?
Designing your database is important
Often results in a more efficient and simpler queries once the database has been created.
May help reduce data redundancy in the tables.
Entity-Relationship Modelling
E/R Modelling is used for conceptual design
Entities: objects or items of interest.
Attributes: properties of an entity.
Relationships: links between entities.
For example, in a University database we might have entities for Students, Modules and Lecturers
Students might have attributes such as their ID, Name, and Course
Students could have relationships with Modules (enrolment) and Lecturers (tutor/tutee)
Entity-Relationship Diagrams
E/R Models are often represented as E/R diagrams that
Give a conceptual view of the database
Are independent of the choice of DBMS
Can identify some problems in a design
Lecturer
Student
Module
Tutors
Studies
Name
ID
Course
E/R: Diagram Conventions
There are various notations for representing E/R diagrams
These specify the shape of the various components, and the notation used to represent relationships
For this introductory module, we will use simplified notation
Lecturer
Student
Module
Tutors
Studies
Name
ID
Course
Component 1: Entities
Entities represent objects or things of interest
Physical things like students, lecturers, employees, products
More abstract things like modules, orders, courses, projects
Entity:
Is a general type or class, such as Lecturer or Module
Has instances of that particular type. E.g. DBI and IAI are instances of Module
Has attributes (such as name, email address)
E/R Diagram: Entities
In E/R Diagrams, we will represent Entities as boxes with rounded corners
The box is labelled with the name of the class of objects represented by that entity
Lecturer
Student
Module
Tutors
Studies
Name
ID
Course
Component 2: Attributes
Attributes are facts, aspects, properties, or details about an entity
Students have IDs, names, courses, addresses, …
Modules have codes, titles, credit weights, levels, …
Attributes have:
A name
An associated entity
Domains of possible values
For each instance of the associated entity, a value from the attributes domain
E/R Diagram: Attributes
In an E/R Diagram attributes are drawn as ovals
Each attribute is linked to its entity by a line
The name of the attribute is written in the oval
Lecturer
Student
Module
Tutors
Studies
Name
ID
Course
Component 3: Relationships
A relationship is an association between two or more entities
Each Student takes several Modules.
Each Module is taught by a Lecturer.
Each Employee works for a single Department.
Relationships have
A name.
A set of entities that participate in them .
A degree: the number of entities that participate (most have degree 2).
A cardinality ratio.
Cardinality Ratios
One to one (1:1)
Each lecturer has a unique office & offices are single occupancy
One to many (1:M)
A lecturer may tutor many students, but each student has just one tutor
Many to many (M:M)
Each student takes several modules, and each module is taken by several students
E/R: Diagram Relationships
Relationships are shown as links between two entities
The name is given in a diamond box
The ends of the link show cardinality
Lecturer
Student
Module
Tutors
Studies
Name
ID
Course
One to many
Many to many
Making E/R Models
To make an E/R model you need to identify:
Entities
Attributes
Relationships
Cardinality ratios
We obtain these from a problem description
General guidelines
Since entities are things or objects they are often nouns in the description
Attributes are facts or properties, and so are often nouns also
Verbs often describe relationships between entities
Example
A university consists of a number of departments. Each department offers several courses. A number of modules make up each course. Students enroll in a particular course and take modules towards the completion of that course. Each module is taught by a lecturer from the appropriate department (several lecturers work in the same department), and each lecturer tutors a group of students. A lecturer can teach more than one module but can work only in one department.
Entity, Attributes, Relationships: What shall be identified first? Followed by what?….
Example – Entities
A university consists of a number of departments. Each department offers several courses. A number of modules make up each course. Students enroll in a particular course and take modules towards the completion of that course. Each module is taught by a lecturer from the appropriate department (several lecturers work in the same department), and each lecturer tutors a group of students. A lecturer can teach more than one module but can work only in one department.
Entities – Department, Course, Module, Student, Lecturer
Example – Relationships
A university consists of a number of departments. Each department offers several courses. A number of modules make up each course. Students enroll in a particular course and take modules towards the completion of that course. Each module is taught by a lecturer from the appropriate department (several lecturers work in the same department), and each lecturer tutors a group of students. A lecturer can teach more than one module but can work only in one department.
Entities – Department, Course, Module, Student, Lecturer
Relationships – Offers, Make Up, Enroll, Take, Taught By, Work in, Tutors
Entities in E/R Diagram
Entities – Department, Course, Module, Student, Lecturer
Department
Module
Course
Lecturer
Student
Relationships in E/R Diagram
Each Department offers several Courses
Department
Module
Course
Offers
Lecturer
Student
Relationships in E/R Diagram
A number of modules make up each Course
Department
Module
Course
Offers
Includes
Lecturer
Student
19
Relationships in E/R Diagram
Students enroll in a particular course
Department
Module
Course
Offers
Includes
Enrols
Lecturer
Student
Relationships in E/R Diagram
Students take several modules
Department
Module
Course
Offers
Includes
Enrols
Takes
Lecturer
Student
Relationships in E/R Diagram
A lecturer can teach more than one module
Department
Module
Course
Offers
Includes
Enrols
Takes
Taught By
Lecturer
Student
Relationships in E/R Diagram
Each department employs a number of lecturers
Department
Module
Course
Offers
Includes
Enrols
Takes
Taught By
Employs
Lecturer
Student
Relationships in E/R Diagram
Each Lecturer tutors a number of Students
Department
Module
Course
Offers
Includes
Enrols
Takes
Taught By
Employs
Lecturer
Tutors
Student
The Complete E/R Diagram
The completed diagram. All that remains is to remove M:M relationships
Department
Module
Course
Offers
Includes
Enrols
Takes
Taught By
Employs
Lecturer
Tutors
Student
Removing M:M Relationships
Many to many relationships are difficult to represent in a database:
Student
SID sName sMod
1001 Jack Smith DBI
1001 Jack Smith PRG
1001 Jack Smith IAI
1002 Anne Jones PRG
1002 Anne Jones IAI
1002 Anne Jones Vis
Student
SID sName sMod
1001 Jack Smith DBI, PRG, IAI
1002 Anne Jones VIS, IAI, PRG
Module
MID mName
DBI Databases and Interfaces
PRG Programming
IAI AI
VIS Computer Vision
The problem is:
In the first student table, SID cannot be a candidate key.
In the second student table, sMod cannot be a foreign key.
It can be problem when you access a remote DB: you need to check whether sMod is in Module.MID. You will have to get every rows of Module. What if Module is a huge table?
26
Removing M:M Relationships
We can split a many to many relationship into two, one to many relationships
An additional entity is created to represent the M:M relationship
Module
Takes
Student
Module
In
Enrolment
Has
Student
Relationships
The Enrolment table
Will have columns for the student ID and module code attributes
Will have a foreign key to Student for the ‘has’ relationship
Will have a foreign key to Module for the ‘in’ relationship
Student
Has
Enrolment
In
Module
mCode
mTitle
mCredits
sID
mCode
sID
sName
sAddress
sYear
Entities and Attributes
Sometimes it is hard to tell if something should be an entity or an attribute
They both represent objects or facts about the world
They are both often represented by nouns in descriptions
General guidelines
Entities can have attributes but attributes have no smaller parts
Entities can have relationships between them, but an attribute belongs to a single entity
Example
We want to represent information about products in a database. Each product has a description, a price and a supplier. Suppliers have addresses, phone numbers, and names. Each address is made up of a street address, a city name, and a postcode.
Example – Entities/Attributes
Entities or attributes:
product
description
price
supplier
address
phone number
name
street address
city name
postcode
Products, suppliers, and addresses all have smaller parts so we make them entities
The others have no smaller parts and belong to a single entity
Example – E/R Diagram
Product
Supplier
Address
Price
Description
Phone Number
Name
Street Address
City Name
Postcode
Example – Relationships
Each product has a supplier
Each product has a single supplier but there is nothing to stop a supplier supplying many products
A many to one relationship
Each supplier has an address
A supplier has a single address
It does not seem sensible for two different suppliers to have the same address
A one to one relationship
Example – E/R Diagram
Product
Address
Price
Description
Phone Number
Name
Street Address
City Name
Postcode
Takes
Supplier
Has A
One to One Relationships
Some relationships between entities, A and B, might be redundant if:
It is a 1:1 relationship between A and B
Every A is related to a B and every B is related to an A.
Example
The supplier-address relationship – Is one to one
Every supplier has an address
We don’t need addresses that are not related to a supplier
One to One Relationships
We can merge the two entities that take part in a redundant relationship together
They become a single entity
The new entity has all the attributes of the old ones
B
c
b
x
y
z
A
a
c
b
AB
a
x
z
y
Example – E/R Diagram
Product
Price
Description
Phone Number
Name
Street Address
City Name
Postcode
Has A
Supplier
E/R Diagram: Summary of Steps
From a description of the requirements, identify:
Entities
Attributes
Relationships
Cardinality ratios of the relationships
Draw the E/R diagram and then
Look at one to one relationships as they might be redundant
Look at many to many relationships as they will often need to be split into two one to many links, using an intermediate entity
From E/R Diagram to SQL Tables.
Entities Become table names.
Attributes of an entity becomes the columns.
Relationships become foreign keys.
Relationships
Relationships as Foreign Keys:
1:1 are usually not used, or can be treated as a special case of M:1
M:1 are represented as a foreign key from the M-side to the 1.
M:M are split into two M:1 relationships.
Student
Has
Enrolment
In
Module
mCode
mTitle
mCredits
sID
mCode
sID
sName
sAddress
sYear
Questions?
/docProps/thumbnail.jpeg