Transcript Lecture Notes 9 – Entity Relationship Model

Database Design (Data Modeling)
DCO11310
Database Systems and Design
By Rose Chang
System Development Life Cycle
Analysis
Design
Implement
Testing
Production
2
Data Modeling
 A particular way of representing data
 Analysis of what information the database must
hold
 Relationships among components of that
information
Object-Oriented
DBMS
ODL, UML, etc.
Ideas
Relations
ER
Relational
DBMS
3
Data Models
Entity model of
client’s model
Model of
system
in client’s
mind
Table model
of entity model
Oracle
server
Tables on disk
4
Why Use Data Modelling ?
 Programming relies very much on data model
 Provides a method to reconcile the very different
end-user views of the nature and role of data
 Helps us to understand the complexity of a real
world data environment
 Yields a proper “blueprint” of the database
design
5
Steps in Data Modelling
Analysis of Requirements
Entity Relationship Modelling
Transformation into Data Structures
Normalisation of Data Structures
6
Requirement Analysis
 Must incorporate all necessary information from
different major users
 Identify data requirements


Description of data used or generated
Details of how data is to be used and generated
 Describe information to be recorded
 Combine into a single global view
 Analyse the types of transactions to be performed
7
ER Modelling of Requirements
 Construct an ER model to describe the data
objects and their interrelationships
 Identify key attributes
 Decide on the functionality of relationships
(mandatory or optional)
 Construct a schematic ER model which gives a
global view of the entire database
8
Transformation of the ER Model
to a Schema
 Map the ER model onto a set of data structure, or
schema
 Schema : the description of the organization of a
database seen by the DBA
9
Normalisation of the Schema
 Reduce each relation to the highest stage of
normalisation
 BUT designer may wish to refine the resultant
schema in order to improve processing efficiency
10
Logical vs. Physical DB Design
(II)
Program
Data
Views
Logical
DB Design
Physical
DB
Design
Selected and
derived tables
and columns
Tables and
columns
How the data
is stored in the
computer
11
Components of Database Design
 The four representations or abstractions will
progressively get more details of the system :




Data requirements
(Textual Information)
Conceptual data model (E-R Model)
Logical schema
(Normalized Entity)
Physical Storage schema (Create Table Statements)
12
Conceptual Data Model (I)
1. Identify entity types
2. Identify relationship types
3. Identify and associate attributes with entity or
relationship types
4. Determine attribute domain
5. Determine candidate, primary and alternate ket
attributes
13
Conceptual Data Model (II)
6. Validate conceptual model against user
transactions
7. Review conceptual data model with user
14
Entity-attribute-relationship
Models (EAR)
 Used to produce conceptual data model
 Consists of :




A formal description of each entity in terms of its
attributes
Descriptions of the meanings of relationships
Descriptions of any constraints
The ER diagram
15
16
Entity Relationship Model
 There are many notations for ERD such as UML, Chen,
Crow’s feet
 Create an entity relationship diagram from business
specifications or narratives
EMPLOYEE
number
name
job title
have
DEPARTMENT
number
name
location
Child entity
Parent entity
 A department have many employee
17
18
Entities Relationship
 Define associations among entities
 All relationships are bi-directional
 Degrees of relationships



One-to-one relationship (1:1)
One-to-many relationship (1:M)
Many-many relationship (M:N)
19
Degrees of Relationships
One-to-one Relationship
Student-id
Student-name
Course-code
Student
have
Email
Account-id
usage
password
One-to-many Relationship
Detail Entity
Student-id
Student-name
Program-code
Student
Master Entity
have
Program
Program-code
Program-name
20
Many-to-many Relationship
Student-id
Student-name
Program-code
Course-code
take
Student
Course
have
Course-code
Course-name
You should resolve it by adding link entity
Student-id
Student-name
Program-code
Master Entity
Master Entity
Student
Course
Student-id
Course-code
mark
belong
to
get
Mark
Link Entity
Course-code
Course-name
for
have
21
Optional vs. Mandatory
Relationship
“A lecture may
teach one or many
courses.”
Lecturer
teach
Course
“A course must
be taught by only
one lecturer.”
22
Draw an ERD
 Each company operates four departments, and
each department belongs to one company. Each
department employs one or more employees, and
each employee works for one department. Each
of the employee may or may not have one or
more dependants, and each dependant belongs to
one employee. Each employee may or may not
have an employment history
23
Q&A
24