ER Models Revisited

1
ER Models Revisited
Cecil Eng Huang Chua Information Technology
Operations Management aehchua_at_ntu.edu.sg
2
Technical Skills

• Conceptual data modeling
• Enhanced ER diagrams
• ER to Relational
• Logical data modeling (relational)
• Developing tables with controls
• Normalization
• Physical application development
• SQL
• Connecting to Programming Languages

3
Outline

• Review of AB113 ER Models
• Fixing AB113 ER Models
• Attributes vs. Relationships vs. Entities
• Rethinking Attributes
• Rethinking Entities
• (break)
• Rethinking Relationships

4
Entity-Relationship Model

• Conceptual view of a database
• Communication between user and database designer
• Documentation of how database looks like
• Represented in an entity relationship diagram
  (ERD)
• Components of an ERD
• Entities corresponds to tables
• Really Entity Set
• Attributes corresponds to fields of a table
• Relationships represents links between tables

5
Entities

• Entity - person, place, object, event, concept
  etc
• Examples Student, customer, receipts
• Anything about which we want to store data.
• What do we want to store? Attributes
• Examples name, address, birthday
• Entity Set is collection of entities
• Lazy- call just entity

6
Attributes

• Describe an entity set
• Two types of attributes
• Key fields uniquely identify entity
• Non-key fields non-unique values
• Values contained in fields may be
• Single valued
• Composite
• Derived

7
Relationships

• Association between entities
• crows foot in ERD
• Connectivity describes relationship
  classification
• 11, 1M, MN
• Relationship participation
• Mandatory At least 1
• Optional Can be 0
• Cardinality
• Minimum Cardinality Normally 0 or 1
• Maximum Cardinality Normally 1 or M

8
Definitions ERD

• Entity Set A collection of entities
• Entity A single thing
• Attribute Partial description of entity set
• Relationship Links two entities
• Value Instantiation of entity and attribute

9
ERD Notation
10
Different ERD Notations
Customer
Vehicle
(0M)
(0M)
Purchases
(01)
(0M)
Salesperson
Sold By
11
Practice Case

• Each department (e.g. Accounting ITOM Law
  MIB Corporate Services etc) within the School
  may offer courses (e.g. AB113 BC307 etc). Some
  departments do not offer any courses, e.g.
  Corporate Services Department but a course must
  be offered by a department. A course will be
  taught by at least one professor. Students must
  enroll in at least one course each semester.

12
Solution (Crows Foot)
Department
Course
Student
offers
enrolls
teaches
Professor
13
Solution (Original)
(11)
Enrolls
(0M)
Offers
(1M)
(1M)
Department
Course
Student
(1M)
Teaches
(0M)
Professor
14
Outline

• Review of AB113 ER Models
• Fixing AB113 ER Models
• Attributes vs. Relationships vs. Entities
• Rethinking Attributes
• Rethinking Entities
• (break)
• Rethinking Relationships

15
Fixing AB113 ER Models

• Attributes vs. Relationships vs. Entities
• Rethinking Attributes
• Rethinking Entities
• Rethinking Relationships

16
Attributes vs. Entities

• If the thing needs to be further described, it is
  an entity
• Person is further described by NRIC
• NRIC needs no further description
• Context dependent
• Telephone is normally an attribute
• For the telephone company, it is an entity
• Date of last servicing
• Types of service (call waiting, IDD, etc.)

17
Entities vs. Relationships

• If it needs to be related to something else it is
  an entity
• Student takes many course-sections
• The course-section each student takes has many
  grades
• The intersection between student and
  course-section must be an entity (classroll)

18
Attributes vs. Relationships

• Attributes cannot be related to more than one
  thing
• Relationships are always related to more than one
  thing
• Even if that second thing is the first thing
• Explain later

19
Fixing AB113 ER Models

• Attributes vs. Relationships vs. Entities
• Rethinking Attributes
• Rethinking Entities
• Rethinking Relationships

20
Rethinking Attributes Keys

• Candidate key
• Attribute or set of attributes that uniquely
  identifies an entity
• Choose one to be a primary key
• Primary key should not be a superkey
• Composite key
• Set of attributes that uniquely identifies an
  entity
• Superkey
• Candidate key
• Subset of superkey is candidate key

21
Key Example (1)

• Entity Set Student
• Attributes NRIC, Matric No, Name, DOB, Gender
• Candidate keys
• NRIC
• Matric No
• NRIC, Name
• Matric No, Name

22
Key Example (2)

• Composite Keys
• NRIC, Name
• Matric No, Name
• SuperKeys
• NRIC, Name
• Matric No, Name
• Potential Primary Keys
• NRIC
• Matric No

23
Rethinking Attributes Association

• Attributes are associated with
• Entities (AB113)
• Relationships
• Attributes

24
Example (Original ER Notation)

• Student takes course

Matric_No
Name
Grade
Course_No
Coordinator
Student
Course
(0M)
(0M)
Takes
Address
Name
Block
Hex
Street
Zip
25
Example (Crows Foot)
Course
Student
Matric No Name Address Block Hex Street Zip
Course_No Name Coordinator
Takes
Grade
26
Fixing AB113 ER Models

• Attributes vs. Relationships vs. Entities
• Rethinking Attributes
• Rethinking Entities
• Rethinking Relationships

27
Rethinking Entities

• Not all entities are equal
• Strong entity
• Weak entity
• Weak Entity
• Dependent on another entity for identity
• Section dependent on course
• Always 1M with 1 side mandatory
• No need to describe 1 side
• Relationship called identifying relationship

28
Weak Entities (Original ER Notation)
Section_No
Course_No
(0M)
Course
Has
Sections
Room
Instructor
Name
Coordinator
29
Weak Entities (Crows Foot)
30
Class Exercise Database for the landlord of a
mall chain (1)

• A mall has many lots, and one manager
• A manager manages zero or one mall
• Each lot is identified by its mall, floor and lot
  number
• Each lot has telephone patch points, and square
  area
• Malls have addresses (comprising a street and zip
  code)
• Managers have names (first and last),
  date_of_hire, and gender

31
Break

• (QA)

32
Fixing AB113 ER Models

• Attributes vs. Relationships vs. Entities
• Rethinking Attributes
• Rethinking Entities
• Rethinking Relationships

33
Rethinking Relationships Connections

• Relationships connect to
• Attributes (Just discussed)
• Entities (AB113/Just discussed)
• Relationships (Associative Entities)

34
Associative Entities vs. Intersection Tables

• AB113
• Intersection tables
• Always decompose MN
• Tablephysical data structure
• No such thing in ER!
• Associative Entity
• Weak entity in MN
• Only use when neither entity nor relationship fit
• Otherwise keep as MN

35
Associative Entity Example

• Many students take many sections
• For each section, the student has multiple grade
  components (e.g., class participation, quiz
  score, final exam).

36
Simple Way (Allowed in EER)
(1,6)
(15,M)
Student
Takes
Section
(1,M)
Matric No
Course No
Section No
Scores
Grade
Grade Type
Score
37
Associative Entities (Done in ER)
Enrolls In
Course No
Has
ClassRoll
Student
Section
(1,6)
(15,M)
(1,M)
Matric No
Section No
Scores
Grade
Grade Type
Score
38
Creating an Associative Entity

• MN Relationship becomes weak entity
• Establish two 1M identifying relationships
• Participation of the 1 is mandatory
• Definition of weak entity
• Cardinality/participation of weak entity is
  cardinality/participation of entity on the
  opposite side

39
A Closer Look (Crows Foot)
40
A Closer Look (Original ER)
(15,M)
(1,6)
Student
Takes
Section
Enrolls In
Has
ClassRoll
Student
Section
(1,6)
(15,M)
41
Rethinking Relationships of Entities

• How many entities in a relationship?
• Binary relationship (AB113)
• Two entities
• Unary relationship
• One entity
• N-ary relationship
• More than two entities

42
One-to-one Binary Relationships
Student
Citizen
(0,1)
(0,1)
Could be a
Student
Person
(1,1)
Is-a
(0,1)
Student
Pupil
(1,1)
Is-always-a
(1,1)
There are 11/Mandatory Unary relationships
43
Unary Relationships
Person
(0,1)
Marries
(0,1)
Employee
(0,N)
Supervises
(0,1)
Part
(0,N)
Part-of
(0,M)
44
N-Ary Relationships
Student
Club
(1,M)
(1,M)
Participates In
(1,M)
Activites
45
Problems with N-Ary Relationships

• Is it N-ary or multiple binary?
• Students get many grades from many sections
• Two-way interactions in three-way relationships
• An activity must be by students and clubs
• A club must have students and activities
• A student may not belong to a club
• A student may not participate in an activity
  (even as a club member)
• Tread carefully

46
Class Exercise Database for the landlord of a
mall chain (2)

• A store can rent many lots
• Lots may be connected to many other lots via
  doors or escalators
• Customers buy many items from many stores
• There are many generic tasks to be done for each
  store (e.g., fix the plumbing).
• These tasks are assigned to various work groups
  to perform.
• If you think this exercise is hard, we will make
  it harder next week

47
Module Summary
48
Homework

• Read
• Whitten et al. 6
• Mannino Chapter 5, 6.1
• Submit
• About me form