Entty-Relatonship Diagram (ERD)
1 Database Design
- Before we look at how to create and use a database we’ll look at how to design one
- Need to consider
o What tables, keys, and constraints are needed?
o What is the database going to be used for?
- Conceptual design
o Build a model independent of the choice of DBMS
- Logical design
o Create the database in a given DBMSrell
- Physical design
o How the database is stored in hardware
Note:
1. Conceptual Design is an early phase of the design process, in which the broad outlines of
functon and form of something are artculated. It includes the design of interactons,
experiences, processes and strategies. It involves an understanding of people's needs - and how
to meet them with products, services, & processes. Common artfacts of conceptual design are
concept sketches and models. Conceptual step is very important because you cannot produce a
good design without it.
2. The logical design of a system pertains to an abstract representaton of the data fows, inputs
and outputs of the system. This is ofen conducted via modelling, using an over-abstract (and
sometmes graphical) model of the actual system. In the context of systems, designs are
included. Logical design includes entty-relatonship diagrams (ER diagrams).
3. The physical design relates to the actual input and output processes of the system. This is
explained in terms of how data is input into a system, how it is verifeddauthentcated, how it is
processed, and how it is displayed. In physical design, the following requirements about the
system are decided.
I. Input requirement,
II. Output requirements,
III. Storage requirements,
IV. Processing requirements,
V. System control and backup or recovery.
- Put another way, the physical porton of system design can be broken down into three sub-
tasks:
I. User Interface Design
II. Data Design
III. Process Design
o Physical design, once the logicalderd is put into the DBMS and it is physically usable.
o the richest possible understanding of the meaning of data necessary for an info system.
o Be able to identfy enttes and relate them to each other with actons.
Entity Relationship Diagaam
- The purpose of an ERD is to capture the richest possible understanding of the meaning of data
necessary for an informaton system or organizaton.
- ERDs are made from Enttes, Atributes, and Relatons
1
, Univeasity Entity-Relationship Diagaam
What is an Entityy
- An Entty is an abstracton of similar things
- Enttes represent obcects or things of interest
o Physical things like students, lecturers, employees, products
o More abstract things like modules, orders, courses, procects
- An Entty has its own identty that distnguishes it from other enttes.
o Examples:
Person: PROFESSOR, STUDENT
Place: STORE, UNIVERSITY
Obcect: MACHINE, BUILDING
Event: SALE, REGISTRATION
Concept: ACCOUNT, COURSE
Example Entteie A university consists of a number of depaatments. Each department ofers several
couases. A number of modules make up each course. Students enrol in a partcular course and take
modules towards the completon of that course. Each module is taught by a lectuaea from the
appropriate department, and each lecturer tutors a group of students
Note:
- Entty relatonship diagram is valid in whatever DBMS you choose.
- Can use a tool where you draw your ERD and once it has been drawn you can automatcally put
it in whichever DBMS you choose.
- To build a database, you don’t frst build the database you need - you build the conceptual
model. Once you get the structure then you put it in a system and fll it.
- Build the conceptual phase with an entty relatonship diagram. (ERD)
- ERD is the frst conceptual stage. Has been used since the 70s.
- Atributes are a part of enttes.
- Before building a database:
- What enttes are relevant and how are they going to relate?
2
, - Conceptual Design - to build a model independent of the choice of DBMS (oracle, access,
etc). Completely separate from dbms.
- Entty relatonship diagram is valid in whatever dbms you choose.
- Can use a tool where you draw your ERD and once it has been drawn you can automatcally
put it in whichever DBMS you choose.
- Logical Design - db that is drawn in terms of tables that are linked by primary and ….
- Physical design, once the logicalderd is put into the DBMS and it is physically usable.
- Conceptual step is very important because you cannot produce a good design without it.
- The purpose of an ERD is to capture the richest possible understanding of the meaning of
data necessary for an info system.
- Be able to identfy enttes and relate them to each other with actons.
- Entty: person, place, obcect, event, concept.
- Also have to fnd the scope.
Diagaamming Entities
- In an EdR Diagram, an entty is usually drawn as a
box with rounded corners
- The box is labelled with the name of the class of
obcects represented by that entty
Attaibutes
- Atributes are in enttes. They are aspects of entty.
- Atributes are facts, aspects, propertes, or details about an entty
o Students have IDs, names, courses, addresses, …
o Modules have codes, ttles, credit weights, levels, …
- Atributes have
o A name
o An associated entty
o Domains of possible values
o Values from the domain for each instance of the entty they are belong to
Diagaamming Attaibutes
- In an EdR Diagram atributes may be drawn as ovals
- Each atribute is linked to its entty by a line
- The name of the atribute is writen in the oval
Relationships
- Relatonships are an associaton between two or more enttes
o Each Student takes several Modules
o Each Module is taught by a Lecturer
3
, o Each Employee works for a single Department
- Relatonships have
o A name
o A set of enttes that partcipate in them
o A degree - the number of enttes that partcipate (most have degree 2)
o A cardinality
Example Relatinihipie A university consists of a number of departments. Each department offeas
several courses. A number of modules make up each course. Students enaol in a partcular course
and take modules towards the completon of that course. Each module is taught by a lecturer faom
the appropriate department, and each lecturer tutoas a group of students
Note:
- Relatonships: an associaton between two are more enttes.
- 90% of the tmes the relatonship will be between 2 enttes.
- Most tricky part is the cardinality. Produce a class diagram and there is a lot of discussion about
the cardinality. (the number of elements in a set or other grouping, as a property of that
grouping.)
Caadinality Ratios
- Each entty in a relatonship can partcipate in zero, one, or more than one instances of that
relatonship
- This leads to diferent types of relatonships
o One to one (1:1)
Each lecturer has a unique ofce
o One to many (1:M)
A lecturer may tutor many students, but each student has cust one tutor
o Many to many (M:M)
Each student takes several modules, and each module is taken by several
students
Note:
- Cardinality (also called multplicity) Ratos: Each entty in a relatonship can partcipate in zero,
one or more than one instances of that relatonship. This leads to diferent types of
relatonships
- Example: A student follows a course. Add the cardinality. The student follows between 1 and N
courses. The course follows between 1 and N students.
- Advice: think about what 1 student could do and what can one course do when determining
cardinality. Also think about it in tme. When it is more than one you put n. If you put a star it is
the same as n.
Basic Caadinality Type Caadinality con’t
4
, Diagaamming Relationships
- Relatonships are links between two enttes
- The name is given in a diamond box
- The ends of the link show cardinality
Making E/R Models
- To make an EdR model you need to identfy
o Enittes
o Atributes
o Relatonships
o Cardinality ratos
- from a descripton, in real life you mostly get it from interviews
- General guidelines
o Since enttes are things or obcects they are ofen nouns in the descripton
o Atributes are facts or propertes, and so are ofen nouns also
o Verbs ofen describe relatonships between enttes
Examplee A university consists of a number of departmenti. Each department ofers several courses.
A number of modules make up each ciurie. Studenti enrol in a partcular course and take modules
towards the completon of that course. Each module is taught by a lecturer from the appropriate
department, and each lecturer tutors a group of students
Note:
- Model is the abstract, things you can do with the entty relatonships
- Diagram is an instance of the model onto one specifc case.
- Using technical documentaton is one thing you need in order to make your diagram and in the
modeling process. Also, can use the current database and reverse engineer the database.
- Since enttes are things or obcects they are ofen nouns in the descripton
- Atributes are facts or propertes, and so are ofen nouns also
- Verbs ofen describe relatonships between enttes
- Paradigm is the case tool he will teach us.
- practce making a conceptual diagram with university example
- Enttes can have atributes, but atributes have no smaller parts
- Enttes can have relatonships between them, but an atribute belongs to a single entty
5
