topic3 entityrelationshipmodelling aug14
DESCRIPTION
APU CT014!3!2 DWDSTRANSCRIPT
Database and Web Database Systems
CT014-3-2
Entity Relationship Modelling
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Topic & Structure of the lesson
• Entity Relationship Modelling – Terminologies– ER Modelling Notations– How to build an ER Model
Slide 2 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Learning outcomes
At the end of this lecture you should be able to:•explain terminologies used.•explain the use of ER Modelling notations.•demonstrate how to build an ER Model.
Slide 3 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Key terms you must be able to use
– Entity• Attributes• Value of an attribute
– Primary Key– Foreign Key– Relationship
• degree of relationship– one to one– one to many– many to many
• multiplicity • cardinality
Slide 4 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Terminologies
Entity Relationship Diagrams (ERDs) illustrate
the logical structure of databases.
Slide 5 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Terminologies
• Entity– an object about which the system need to store data,
such as Customer or Product– Attributes of an entity
• data items or elements that make up that entity• E.g. – customerName, customerAddress – those significant
to the system
– Value of an attribute• E.g. – John Barrett is the value of the attribute
customerName
Slide 6 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Terminologies
• Primary Key– of an entity is an attribute or a set of attributes whose
values uniquely identify one occurrence of that entity– Entity primary keys are underlined as shown here:
• E.g. – Customer (customer#, initial, surname, customerAddress, customerPhone)
• Foreign key– an attribute in one entity which is the primary key of
another entity– acts as a link or navigation route between related
entities
Slide 7 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Terminologies
• Relationship– a link between two entities which is significant for the
system– E.g. – customer places an order
• “places” describes a relationship between Customer and an order
• Degree of relationship– can be
• one to one
• one to many
• many to many
– multiplicity and cardinality are used to refer to the degree of relationship
Slide 8 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Entity Relationship modelling
• Notations– wide variety
Slide 9 (of 22)
Figure 1: Variation 1 – Chen Notation from Elmasri book
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Entity Relationship modelling
• Notations
Slide 10 (of 22)
Figure 2: Variation 2 – “Information Engineering” notation from Oracle Designer/ 2000 CASE
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Entity Relationship modelling
• Notations
Slide 11 (of 22)
Figure 3: Variation 3 – Visible Analyst Notation
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Entity Relationship modelling
• Notations
Slide 12 (of 22)
Figure 4: Variation 4 – Sybase PowerDesigner Notation
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Entity Relationship modelling
• Notations
Slide 13 (of 22)
Figure 5: Variation 5 – Popkin System Architect Crow’s Feet Notation
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Chen Notation
Slide 14 (of 22)
Entity Weak Entity Attribute Key attribute
Multivalued attribute
Derived attribute
RelationshipsWeak
Relationships
Cardinality Recursive Relationship
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Quick Review Question
• List the various notations for– entities– relationships– cardinalities– multiplicities
Slide 15 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
How to build an ER Model – (1)[i]
• Identify data objects (entities) about which the system needs to
store data.
• For each entity, list its attributes and check the attributes for
completeness.
• Using the practical guidelines listed below, make any necessary
improvements.
– Data items should be put into logical groups – groups that go
together.
– For each data group, or entity, there should be a key that uniquely
identifies individual members of that entity.
Slide 16 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
How to build an ER Model – (2) [i]
– There should be no redundant data in the model – Data is
deemed redundant in the following situations:
• the data is never used by the system
• the same data items (CustomerName, CustomerAddress) are
stored in more than one place in the system.
• Data in one place can be derifed from data held in another place in
the system.
Slide 17 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
How to build an ER Model – (3) [i]
• Investigate and record relationships. – link entities so that all significant real-life relationships are
captured.
• Check the entity descriptions against the data
dictionary descriptions – Make sure that each process on the data flow diagram has
available to it all the data it needs;
[i] Britton, C., Doake, J., Chapter 5: Data Modelling, Software System Development
– A gentle introduction, McGraw-Hill
Slide 18 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Follow-up Assignment
• Answer Questions 1 and 2 of Tutorial
Slide 19 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Summary
• Entity Relationship Modelling – Terminologies– ER Modelling Notations– How to build an ER Model
Slide 20 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Q & A Session
Slide 21 (of 22)
CT014-3-2 Database and Web Database Systems Entity Relationship Modelling
Next Session
• Logical Data Modelling
Slide 22 (of 22)