代写代考 CS143 Entity-Relationship Model

CS143 Entity-Relationship Model
Professor Junghoo “John” Cho

Entity-Relationship (E/R) Model

• Q: How should we design tables in our database? • Tables are not “given”
• “Good” tables may not be easy to come up with
• E/R model: graphical, intuitive and “informal” representation of information on database
• Used to “capture” what we learn from domain experts/database users • Not directly implemented by DBMS
• Tools exist to automatically convert E/R model into tables
• Two main components
• Entity sets and relationship sets

Entity Set
• Entity: “thing” or “object” in real world • E.g., I, this book, UCLA
• Entity set: a set of entities (objects). Like a class in OOP • Rectangle in ER
• Consists of “name” and “attributes”
sid name addr age GPA
dept cnum sec title unit
name title office email

Entity Set
• Entities with attributes can be thought as “tuples” (or records) • (301, John, 13 Hilgard, 18, 3.3), (303, James, 12 , 19, 2.5), …
• Key: a set of attributes that uniquely identifies an entity in an entity set
• Underline in E/R
• All entity sets in E/R need a key
sid name addr age GPA
dept cnum sec title unit
name title office email

Relationship Set
• Relationship: “connection” between entities
• Relationship set: a set of relationships of the same kind • Diamond in ER
• Relationships can be thought as “edges” between entities
• Relationships can have attributes
• Not all entities have to participate in a relationship
sid name addr age GPA
dept cnum sec title unit
name title office email

Cardinality of Relationships
• Cardinality: how many times entities participate in a relationship? • One-to-one One-to-many Many-to-many
E1 R E2 E1 R E2 E1 R E2
• Cardinality: Add arrow on the “one” side
• Total participation
• every entity participates in the relationship at least once • Double line in E/R model

Meaning of Cardinality
• Q: What does it mean
• Many-to-one in Teach?
• One-to-one in Teach?
• Double-line between Classes and Teach?
• Double-line and arrow between Teach and Faculty?
• Double lines at both sides of Teach vs one-to-one of Teach. Are they the same?
Students Take Classes Teach Faculty

General Cardinality Notation
• Label an edge with “a..b”
• The entity participates in the relationship between a through b times • * means unlimited
E1 0..* R 0..1 E2
• Don’t get confused: for one-to-many relationship, “0..*” appears on the “one” side and “0..1” appears on the “many” side

N-ary Relationship
• We may need more than binary relationship sometimes • Example: Students, TA’s, and Classes
• All TA’s help all students
• Each student is assigned to a particular TA

• We can designate a “role” to each entity set that participate in a relationship set
• Labels on edges of a relationship in E/R model
• Useful if an entity set participates more than once in a relationship

Superclass and Subclass
ForeignStudents
DomesticStudents
HonorStudents
TransferStudents
transferred_units
• ISA relationship in E/R connects superclass and subclass
• Specialization: superclass → subclass, generalization: subclass → superclass
• Subclass inherits all attributes of its superclass
• Subclass participates in the relationships of its superclass • Subclass may participate in its own relationship
• Disjoint specialization vs overlapping specialization • Either-orvsmultiplespecialization
• singlehollowarrowvsmultiplehallowarrows

Weak Entity Set
• Weak Entity Set: An entity set without a unique key • Double rectangle in E/R model
• Part of its key comes from one or more entity sets it is linked to
• Owner entity set: entity set providing part of the key
• Identifying relationship: relationship between a weak entity set and owner entity set
• DoublediamondinE/Rmodel
• Discriminator: attributes in a weak entity set that become part of the key
• Dashedunderline
ProjectReport
project_num title

E/R for Stores and Products
• All products are either “private-label products” (like Kirkland shoes at Costco) or “national-brand products” (like Kleenex Tissue)
• Every product is manufactured by exactly one manufacturer (like 7up by Coke company, etc.)
• Every private-label product is carried by exactly one chain store (eg, Kirkland shoes by Costco)
• Some national-brand products may not be carried by any chain store

E/R for Stores and Products
• All products are either “private-label products” or “national-brand products”
• Every product is manufactured by exactly one manufacturer
• Every private-label product is carried by exactly one chain store
• Some national-brand products may not be carried by any chain store
NB- Carried- By
Manufacturers
Private-label Products
National-brand Products
PL- Carried- By
Chain Stores

E/R Design Principles
• Often it is not clear what choices to make
• One gigantic entity set with many attributes vs many smaller entity sets? • Attribute vs Entity set?
• General rule of thumb for good design: avoid redundancy • Saying the same thing more than once
• Space waste and potential inconsistency

E/R Design Example
• Faculty(name, addr) are instructors of Class(dept, cnum, title)
title fname faddr
dept cnum title
• Things to consider for entity set vs attribute
• Do we need more attributes than keys?
• Is it one-to-one relationship?
• Create multiple entity sets for many-to-many or many-to-one relationships

E/R to Relation
• Converting E/R diagram to tables is mostly straightforward • Automatic conversion tools exists
• (Strong) entity set: one table with all attributes

Example Conversion from E/R to Relation
Submit coder
ProjectReport
cnum title
ForeignStudent
HonorStudent
fellowship

E/R to Relation
• Converting E/R diagram to tables is mostly straightforward • Automatic conversion tools exists
• (Strong) entity set: one table with all attributes
• Relationship set: one table with keys from the linked entity sets and its own attributes
• If attribute names conflict, prefix them with entity set name

Example Conversion from E/R to Relation
ProjectReport num
• Student(name, addr) Class(cnum, title) TA(name, addr) Faculty(name, addr)
Student name addr
TA name addr
Class cnum title
ForeignStudent country
HonorStudent fellowship
Faculty name addr

ProjectReport
Submit coder
• Student(name, addr) Class(cnum, title) TA(name, addr) Faculty(name, addr)
• Teach(name, cnum) Take(Student.name, cnum,
TA.name, quarter) Partner(coder, tester)
Example Conversion from E/R to Relation
cnum title
ForeignStudent
HonorStudent
fellowship

Conversion of Subclass(es)
• Two popular approaches
1. One table for each subclass with its own attributes and
the key of its superclass
• Student(name, addr) ForeignStudent(name, country) HonorStudent(name, fellowship)
2. One gigantic table for the super class that includes all attributes
• Student(name, addr, country, fellowship)
• NULL values for missing attributes
ForeignStudent
HonorStudent
fellowship

Related Posts