Download - 3 Entity Relationship Diagrams
-
8/3/2019 3 Entity Relationship Diagrams
1/23
ACE2141-3 Tony Cornah 2006/07 1
Entity-Relationship Diagrams
Steps in producing an E-R Diagram (ERD)
Identify the Entities.
Identify the relationships.
Consider 1-to-1 relationships.
Deal with M-to-N relationships.
Check for dependent existence.
Draw the diagram.
Check for Connection Traps.
Redo the Entities table if necessary.
-
8/3/2019 3 Entity Relationship Diagrams
2/23
ACE2141-3 Tony Cornah 2006/07 2
Entities
Three rules.
Each entity must have a key uniquely identifying it.
The number of values for an attribute must be the same
for all instances of an entity.
Any relationship between entities must be 1-to-N.
Identify the Entities.
Summarise the information from system descriptions
and the Data Flow Diagram.
Identify the things described by this data.
Check that the entities satisfy the first two of the Three
Rules.
-
8/3/2019 3 Entity Relationship Diagrams
3/23
ACE2141-3 Tony Cornah 2006/07 3
Member of Staff
Staff IDName
Address
Course
CourseCodeTitle
Summary
Timetabled Class
Day
Time
Location
CourseCode
StaffID
(Day, Time, Location, StaffID)
(Weds, 17:15, G29, A123)
(Mon, 17:15, G29, B456)
(Thurs, 19:15, G38, B456)
-
8/3/2019 3 Entity Relationship Diagrams
4/23
ACE2141-3 Tony Cornah 2006/07 4
Identify the Entities in the following description:
When a customer telephones the hotel, Chris, the receptionist,
checks the booking log for an available room, which he offers to
the customer, along with the price. If the customers is happy
with that room, Chris fills in a duplicate booking form with the
customers name, address and telephone number, and the dates,room number, room occupancy and price of the booking. He
then updates the booking log by writing the customers name
against the appropriate dates for the booked room. At various
convenient points during the day, the receptionist sends all the
original booking forms filled in so far to the customers as
confirmation. He then files the duplicates in the booking form
file.
-
8/3/2019 3 Entity Relationship Diagrams
5/23
ACE2141-3 Tony Cornah 2006/07 5
Identify the relationships.
Identify pairs of entities which have a connection. Check that the relationships satisfy the third of the
Three Rules.
Can one A have (more than) one B?
Can one B have only one A? Consider 1-to-1 relationships.
Either: Incorporate one entity into the other.
Or: Keep them separate.
Deal with M-to-N relationships.
Create a new entity.
Create two new 1-to-N relationships.
Relationships
-
8/3/2019 3 Entity Relationship Diagrams
6/23
ACE2141-3 Tony Cornah 2006/07 6
Relationships are
a Staff Member teaches a Class (taught by
)
a Classbelongs to a Course (has)
Can a Staff Member teach more than one Class?
Can a Classbe taught by only one Staff Member?
Can a Classbelong to (more than) one Course?
Can a Course have only one Class?
Yes
Yes
No
Yes
Yes
No
Can a Course have more than one Class?
Can a Classbelong to only one Course?
-
8/3/2019 3 Entity Relationship Diagrams
7/23
ACE2141-3 Tony Cornah 2006/07 7
Member of Staff Course
Timetabled Class
-
8/3/2019 3 Entity Relationship Diagrams
8/23
ACE2141-3 Tony Cornah 2006/07 8
Now consider some modifications
some Classes have (has) a Practical Session
some Classes are taught by more than one Staff Member
Can a Class have (more than) one Practical Session?
Can a Practical Sessionbe for by only one Class?
Can a Staff Member teach more than one Class?
Can a Classbe taught by only one Staff Member?
Yes
Yes
Yes
No
Yes
No
Can a Classbe taught by only one Staff Member?
Can a Staff Member teach more than one Class?
-
8/3/2019 3 Entity Relationship Diagrams
9/23
ACE2141-3 Tony Cornah 2006/07 9
Member of Staff Course
Timetabled Class
Practical session
(only 1 per class)
Teacher For
-
8/3/2019 3 Entity Relationship Diagrams
10/23
ACE2141-3 Tony Cornah 2006/07 10
Check for dependent existence.
Can A exist without a corresponding B?
Can B exist without a corresponding A?
Can a Staff Member exist without teaching?
Can a Course exist without timetabled
classes? Can a timetable slot exist without a Staff
Member to teach and/or belonging to a
Course?
Yes
No
No
-
8/3/2019 3 Entity Relationship Diagrams
11/23
ACE2141-3 Tony Cornah 2006/07 11
Draw the diagram.
Entities as named boxes.
Relationships as arrows from the 1 to the N.
Absolute (i.e. not dependent) existence as dotted
half line.
Describe each end of the relationship.
-
8/3/2019 3 Entity Relationship Diagrams
12/23
ACE2141-3 Tony Cornah 2006/07 12
Member of Staff Course
Timetabled Class
Teaches
Taught By
Has
Belongs To
-
8/3/2019 3 Entity Relationship Diagrams
13/23
ACE2141-3 Tony Cornah 2006/07 13
Hall of Residence Student
University SiteHolds Details Of
Registered At
Contains
Is In
If these were the relationships identified then there would be no way
of discovering where a student lived.
If students could live in any hall (or in private accommodation):
Lives In
Resident of
If students must live in a hall on their site:
Check for Connection Traps.
-
8/3/2019 3 Entity Relationship Diagrams
14/23
ACE2141-3 Tony Cornah 2006/07 14
Produce an Entity-Relationship Diagram
for the Hotel Booking System
Other Entity-Relationship Diagram
notations: http://members.iinet.net.au/~lonsdale/docs/erd.pdf http://ace2141.group.shef.ac.uk/SupplementaryMaterial/erd.pdf
http://www.smartdraw.com/examples/view/index.aspx?catID=.Examples.
SmartDraw.Software_Design.Entity_Relationship_Diagrm_-_ERD
The future UML Class Diagrams: (Unified Modelling Language) http://www.agilemodeling.com/artifacts/classDiagram.htm
-
8/3/2019 3 Entity Relationship Diagrams
15/23
ACE2141-3 Tony Cornah 2006/07 15
ROOM
CUSTOMER
BOOKING LOG ENTRY
there are redundant relationships here
Appears In
Refers To
Is Reserved By
Reserves
Makes
Is For
Appears In
Contains
Consists Of
Made Up Of
-
8/3/2019 3 Entity Relationship Diagrams
16/23
ACE2141-3 Tony Cornah 2006/07 16
Creating a database in Access
Open Access
New Blank Database
Specify values for
Save in: choose directory
File name: type file name
Create
-
8/3/2019 3 Entity Relationship Diagrams
17/23
ACE2141-3 Tony Cornah 2006/07 17
Creating a table using a Wizard Tables tab
Double clickCreate table by using wizard
Select required Sample Table Customers
Choose required Sample Field and select with "
Can Rename Field if required
Next to choose Primary Key and a new name for the tableif required
Can Modify the Table Design if required then Finish
If design is modified, Close the design view and clickYes
when asked if you want to Save the changes to the table
-
8/3/2019 3 Entity Relationship Diagrams
18/23
ACE2141-3 Tony Cornah 2006/07 18
Creating a table without a Wizard
Tables tab
Double click on Create table in Design view Fill in Field Name: Booking Date
Select Data Type:Date/Time
Etc.
References to other tables must match in type but
Use Number to refer to AutoNumber fields
Select the table key
select the row(s) containing the key item(s)
KEY icon orEdit - Primary Key
Close the design view and clickYes when asked if youwant to Save the changes to the table and name it
-
8/3/2019 3 Entity Relationship Diagrams
19/23
ACE2141-3 Tony Cornah 2006/07 19
Consider these tables1) for composite foreign keys
2) with the previous entity-
relationship diagram
Redo the Entities table if necessary.
-
8/3/2019 3 Entity Relationship Diagrams
20/23
ACE2141-3 Tony Cornah 2006/07 20
-
8/3/2019 3 Entity Relationship Diagrams
21/23
ACE2141-3 Tony Cornah 2006/07 21
Creating relationships
Open the existing database and select Relationships
(Right click and Show Table), Add all the tables and Close
For each relationship
Drag the attribute from the One side to the N side
Select Enforce Referential Integrity ClickCreate
Close, saving the changes to, Relationships
-
8/3/2019 3 Entity Relationship Diagrams
22/23
ACE2141-3 Tony Cornah 2006/07 22
-
8/3/2019 3 Entity Relationship Diagrams
23/23
ACE2141-3 Tony Cornah 2006/07 23