Transcript CSE 1012 Basic Computer Applications

CSE 1012
BASIC COMPUTER
APPLICATIONS
ENTITY-RELATIONSHIP
DIAGRAM
Appavoo Paramasiven
Lesson Objectives
2
Learn about the components that make up an
Entity Relationship Diagram (ERD) using the Chen’s
notation
 Learn how to draw an ERD
 Learn how to transform an ER Model into a
Relational Model

Content
3

ER Model
 Entities
 Relationships
 Attributes
Classes of Attributes
 Cardinality Mapping
 From entities to relations

Introduction


What is a model?
Representation of sth
Entity Relationship Model
 Representation
of relationships between data
 Developed by Peter Chen (1976)
4
ER Diagramming - Modelling a DB
5




Data view of a system
Provides a high level, conceptual view of the DB
structure
What data should be stored??
What relationship exists between data items??
DB
Student
marks
Link
Student
details
File
The ER Model
6

Used for:


Conceptual database design
Made up:
 Entities
 Attributes
 Relationships

Benefits:
 Pictorial
representation (non-tech people)
 Easily map to relations
ER Model - Entity
7

Entity
 Group
EntityName
of ‘objects’
 In real ‘world’
 With same properties
Each rectangle contains the name of the entity it represents


An entity can be uniquely identified
Examples:
Car, University, Department, Person
Car
ER Model - Relationships
8

Relationships
 Associations
Relationship
Name
between entities
 Corresponds to primary key-foreign key equivalencies
in related tables
Each diamond contains the name of the relationship it
represents

Examples
Own, register, employ
Own
ER Model - Attributes
9

Attributes


“ describe the entity’s characteristics”
AttributeName
Attributes are represented by an Ellipse/Oval connected to the Entity
with a line
Each oval contains the name of the attribute it represents
Entity:
Attributes:
Person
NID, Name, Address, LicenseNumber, height
NID
ER Model
10
Entities
Attributes
Relationships
NID
Name
Address
Age
License No
Owns
Plate No
Make
Model
Colour
ER Diagram
11
Make
Address
Name
License No
Plate No
Colour
NID
Model
Person
Own
Since
Car
Classes of Attributes
12
1.
2.
3.
4.
5.
Simple Attribute
Composite Attribute
Derived Attributes
Single-valued
Multi-valued Attribute
Classes of Attributes
13
1.
Simple Attribute

2.
cannot be subdivided
Composite Attribute

can be further subdivided to yield additional attributes.
Classes of Attributes
14
3.
Derived Attribute


is not physically stored within the database
is derived by using an algorithm
Classes of Attributes
15
4.
Single-valued


can have only a single value.
Examples:



A person can have only one social security number.
A manufactured part can have only one serial number.
A single-valued attribute is not necessarily a simple
attribute.
Classes of Attributes
16
5.
Multivalued attributes


Can have many values
Examples:


A person may have several college degrees
A household may have several phones with different numbers
Cardinality Mapping
17

It is the specification of the number of occurrences of one
object that can be related to the number of occurrence of
another object.



One to one (1:1)
One to many (1:N)
Many to many (M:N)
Examples
18
Mother
School
Lecturer
1
1
M
Give
birth
employ
guide
N
N
Child
Teacher
N
Student
Students can register for one course at a time.
Course
1
register
N
Student
ACTIVITY –
Identify the entities and their relationships for the following:
19





A department hires many employee. A employee is
employed by one department.
A manager manages one department. A department is
managed by one manager.
A person must have one and only one DNA pattern and
that pattern must apply to one and only one person.
Each student takes many classes, and each class is taken by
many students.
A team consists of many players. A player plays for only
one team.
Steps for creating an ERD
20
1.
2.
3.
4.
Identify the entities
Identify the attributes
Identify the relationships
Identify cardinalities
ACTIVITY
21
Lecturers can lecture many courses and give lecture to
many students. Each student can register for one course
and at the same time and in each course there will be
many students.
Draw the ERD of the above.
You are free to decide the attributes of the entities.
name
DOB
Age
Address
22
Phone #
Entities
Relationship
Attributes
Lecturer
lecture
StudentID
Student
name
register
DOB
Age
Address
Course
Course #
Title
Table Design (1)
23
Make
Address
Name
License No
Plate No
Colour
NID
Model
Person
Owns
Since
Car
Table Design (1)
24
Person( NID, Name, Address, LicenseNo )
Car( PlateNo, Colour, Make, Model )
Owns( NID, PlateNo ) , DatePurchased )
From Entities to relation!!!
Table Design and Normalisation
25





Each entity in the Logical Design model, along with the
associated attributes, corresponds to one or more tables
in the system.
A table is the grouping of attributes to identify a physical
entity.
The table name should ‘describe’ the entity name.
The unique attributes may become the unique key.
Base your table design on a normalized logical data
model.
Table Design and Normalisation
26
Normalized data model avoids redundancies and
inconsistencies in your data.
 Tables in a database should be at least in the 3rd
Normal Form.

Draw the ERD of the above using the
CHEN’s model.

The Ministry of Fisheries wants to keep records of the existing fishing
companies. Given the following:
A fisherman can work for one fishing company at a time and each fishing
company can have many fishermen. Training is provided to at most five
trainee fishermen in each company who are guided by existing fishermen of
that company. Fishing companies also own fishing boats. The Ministry wants to
store the identity number, social security number, name, surname, address,
date of birth and number of years of experience for each fisherman. The
same information will be kept for the trainees. As for that of the fishing
company, its name and address, the contact person and the telephone
number will be stored (there can be more than one telephone number). Every
boat has a name, a manufacturer, a capacity, and a date of purchase and is
assigned to a particular fisherman.
Recap
28

ER Model
 Entities
 Relationships
 Attributes
Classes of Attributes
 Cardinality Mapping
 From entities to relations
