Entity-Relationship Model
• • •
Entities Attributes Relationships
•
Objects: data + methods
•
– Not an object
Entity: only data
Entity
– Leads to a entity type (serves same purpose as OO class)
– Ex: Student, Account, Book
– Usually a noun in requirements, a term in business rule
•
•
Entity defined as a set of attributes – Attributes are the “fields” of the record – Ex: Name, Height, Birth date
Attribute
Attributes are not a specific value
– Ex: Male, Sophomore
– Compare with data members of a class
•
Composite — made of simpler values
– Ex: Address, Name
– Does it matter if we treat these as one or several attributes?
Kinds of Attributes
Multi-valued attributes
•
Attribute may consist of multiple values
– Ex: Color of a shirt (what other attributes of a shirt?)
– Why isn’t shirt color a composite attribute? – Ex: Definition of a word
•
multi-valued?
Can an attribute be both composite and
•
attribute values
•
How do you guarantee uniqueness?
Uniqueness
Every entity is unique : has a unique set of
– Ex: every student record must be unique – By this definition, are OO objects unique?
•
with unique values for each entity
Keys
Key: for an entity type, a set of attributes – Ex: SSN for student
– Ex: State + license plate number for car For one entity type, may have more than
•
one candidate key
•
Primary key: the chosen key
•
Why does it matter?
– Usually data organized to improve performance when using primary key
– One entity type refers to another using the 2nd’s primary key [see Relationships]
• •
Efficiency: ex: int vs. float
Usability: ex: ID (like SSN) vs. Name
Choosing Keys
•
Entity type without a key
– Need owner entity to identify
– Usually lack of a key is due to business rules (check this)
– Ex: Dependent – first name, gender
– Ex: Section – section #, instructor last name
– What would we need to add to make these entities regular?
Weak Entities
•
Connection between 2 or more entities – Ex: Employee works for Department
– Ex: Student registered in Section
– Ex: Employee is supervised by Employee
Relationship
•
Last example is a recursive relationship
•
Connection between more than 2 entities
– Ex: Instructor teaches Course in Quarter
– Ex: Product made of Material (outer) and Material (lining)
N-ary Relationships
•
relationships
Usually easier if replaced by binary
– Ex: Product exterior made of Material – and Product lining made of Material
•
Can we do the same for teaches?
Relationship Properties (Cardinality)
•
participate
# times entities from each entity type may
– Ex: Employee works for Department
– N:1 — Many (N) employees work for one department
– Other possibilities — 1:1, 1:N, M:N
Determining cardinality
•
sample data
To work this out, try to tabulate some
•
has “many” cardinality
Duplicates in a column mean other column
Employee
Department
Carson
Sales
Singh
Marketing
Wu
Sales
Cardinality (continued)
•
relationships?
What about the other example
– Often determined by business rules
•
database schema (organization of tables)
Cardinality does matter — helps determine
•
Does every entity of the type participate?
– Ex: Employee leads Department
– Optional (partial) participation — Employee might lead Department
– Mandatory (total) participation — Department must have leader
Relationship Properties
(Participation)
•
– Again business rules affect this
What about the example relationships?
Determining Participation
•
Tabulating sample data helps
– If every entity occurrence must appear, mandatory participation
• Ex: In previous slide, every employee, department must appear at least once
– If it might not, optional participation
• Ex: In Employee manages Department, not all Employees will show, but again, every department should be listed