Primary Key
- We typically choose one of the candidate keys as the primary key.
- In an ER diagram, the primary key attribute(s) are underlined.
Data Mechanics
Course Overview — Database Design and ER Models
What Other Options Are There?
- View a DBMS as being composed of two layers.
- At the bottom is the storage layer or storage engine.
- stores and manages the data
- Above that is the logical layer.
- provides an abstract representation of the data
- based on some data model
- includes some query language, tool, or API for accessing and modifying the data
- To get other approaches, choose different options for the layers.
ER Models
- An entity-relationship (ER) model is a tool for database design.
- graphical
- implementation-neutral
- ER models specify:
- the relevant entities (“things”) in a given domain
- the relationships between them
Entities: the “Things”
- Represented using rectangles.
- Examples:
- Strictly speaking, each rectangle represents an entity set, which is a collection of individual entities.
Attributes
Associated with entities are attributes that describe them.
- represented as ovals connected to the entity by a line
- double oval = attribute that can have multiple values
Keys
A key is an attribute or collection of attributes that can be used to uniquely identify each entity in an entity set.
Observe that an entity set may have more than one possible key.
- Possible keys include:
- id
- (id, email)
- (id, name)
Candidate Key
A candidate key is a minimal collection of attributes that is a key. Note that minimal = no unnecessary attributes are included (not the same as minimum)
Example 1: assume (name, address, age) is a key for Person
- It is a minimal key because we lose uniqueness if we remove any of the three attributes.
Observe that:
- (name, address) may not be unique, e.g., a father and son with the same name and address
- (name, age) may not be unique
- (address, age) may not be unique
Example 2: (id, email) is a key for Person
- It is not minimal, because just one of these attributes is sufficient for uniqueness
- It is not a candidate key
Keys and Candidate Keys (cont.)
Consider an entity set for books:
Assume that:
- each book has a unique isbn
- an author doesn’t write two books with the same title
Which of these are candidate keys of this entity set?
A. isbn — yes
B. (author_id, title) — yes, both needed for uniqueness
C. (author_id, isbn) — no, author_id isn’t needed
D. A and B, but not C
E. A, B, and C
Answer: D. A and B, but not C
Which of these are keys of this entity set?
A. isbn.
B. (author_id, title).
C. (author_id, isbn).
D. A and B, but not C.
E. A, B, and C.
Answer: E. A, B, and C — adding author_id to isbn still uniquely identifies a book; it’s just not minimal.
Relationships Between Entities
- Relationships between entities are represented using diamonds that are connected to the relevant entity sets.
- For example: students are enrolled in courses
- Another example: courses meet in rooms
Relationships Between Entities (cont.)
- Strictly speaking, each diamond represents a relationship set, which is a collection of relationships between individual entities.
- In a given set of relationships:
- an individual entity may appear 0, 1, or multiple times (e.g. CDS 164)
- a given combination of entities may appear at most once
- example: the combination (DS 110, CAS 315) may appear at most once
Attributes of Relationships
A relationship set can also have attributes.These attributes specify information associated with the relationships in the set.
Example:
Key of a Relationship Set
A key of a relationship set can be formed by taking the union of the primary keys of its participating entities.
Example: (person.id, course.name) is a key of enrolled
The resulting key may or may not be a primary key. Why?
It may not be minimal.
Degree of a Relationship Set
Enrolled is a binary relationship set: it connects two entity sets. It has a degree = 2.
It’s also possible to have higher-degree relationship sets. A ternary relationship set connects three entity sets. It has degree = 3.
Relationships with Role Indicators
- It’s possible for a relationship set to involve more than one entity from the same entity set.
- For example: every student has a faculty advisor, where students and faculty members are both members of the Person entity set.
- In such cases, we use role indicators (labels on the lines) to distinguish the roles of the entities in the relationship.
Cardinality (or Key) Constraints
- A cardinality constraint (or key constraint) limits the number of times that a given entity can appear in a relationship set.
Example: each course meets in at most one room
- A key constraint specifies a functional mapping from one entity set to another.
- each course is mapped to at most one room (course → room)
- As a result, each course appears in at most one relationship in the meets in relationship set.
- The arrow in the ER diagram has same direction as the mapping.
Many-to-One Relationships
- Meets In is an example of a many-to-one relationship.
- We need to specify a direction for this type of relationship.
- example: Meets In is many-to-one from Course to Room
- Each course participates in at most one Meets In relationship.
- could be 0 (if the course doesn’t have a room)
- could be 1
- cannot be more than 1
- Each room can participate in an arbitrary number (0, 1, 2, …) of Meets In relationships.
Many-to-One Relationships (cont.)
In general, in a many-to-one relationship from A to B:
- an entity in A can be related to at most one entity in B
- an entity in B can be related to an arbitrary number of entities in A (0 or more)
Another Example of a Many-to-One Relationship
- The diagram above says that:
- a given book can be borrowed by at most one person
- a given person can borrow an arbitrary number of books
- Borrows is a many-to-one relationship from Book to Person.
One-to-One Relationships
- In a one-to-one relationship involving A and B: [not from A to B]
- an entity in A can be related to at most one entity in B
- an entity in B can be related to at most one entity in A
- We indicate a one-to-one relationship by putting an arrow on both sides of the relationship:
- Example: each department has at most one chairperson, and each person chairs at most one department.
Many-to-Many Relationships
- In a many-to-many relationship involving A and B:
- an entity in A can be related to an arbitrary number of entities in B (0 or more)
- an entity in B can be related to an arbitrary number of entities in A (0 or more)
- If a relationship has no cardinality constraints specified (i.e., if there are no arrows on the connecting lines), it is assumed to be many-to-many.
How can we indicate that each student has at most one major?
- Answer: B. The arrow goes from Person into Majors In (toward Department), encoding “each Person majors in at most one Department.”
What type of relationship is Majors In?
A. many-to-many
B. many-to-one from Person to Department
C. many-to-one from Department to Person
D. one-to-one
- Answer: B. many-to-one from Person to Department
- A given person can major in at most one department.
- A given department can have an arbitrary number of people majoring in it.
What if each student can have more than one major?
- Observe that there are no arrows.
- Majors In is what type of relationship in this case?
- many-to-many
Cardinality Constraints and Ternary Relationship Sets
- The arrow into “study group” encodes the following constraint: “a person studies in at most one study group for a given course.”
- In other words, a given (person, course) combination is mapped to at most one study group.
- a given person or course can itself appear in multiple studies-in relationships
- For relationship sets of degree >= 3, we use at most one arrow, since otherwise the meaning can be ambiguous.
Participation Constraints
- Cardinality constraints allow us to specify that each entity will appear at most once in a given relationship set.
- Participation constraints allow us to specify that each entity will appear at least once (i.e., 1 or more time).
- indicate using a thick line (or double line)
- Example: each department must have at least one chairperson.
- We say Department has total participation in Chairs.
- by contrast, Person has partial participation
Participation Constraints (cont.)
We can combine cardinality and participation constraints.
- a person chairs at most one department
- specified by which arrow? the one into Department
- a department has exactly one person as a chair
- arrow into Person specifies at most one
- thick line from Dept to Chairs specifies at least one
- at most one + at least one = exactly one
