Tom Kelliher, CS 318
Feb. 6, 2002
PostgreSQL lab, SQL DDL.
- Participation constraints.
E/R example and limitations.
- Design methodology, not a model --- distinct from relational
- Design begins from a specification. In our case, the project
description and application specification.
- Entities --- objects:
Have properties --- attributes.
- Relationships --- associations between entities:
Relationships can also have properties: how long a faculty member has
taught a course.
- Student takes course.
- Faculty teaches course.
- Student takes course taught by faculty.
- Employee reports to employee.
- Constraints: A student can't take two courses offered at the same
Steps in the design process:
- Select entities. Specific objects: butter pecan, vanilla,
bittersweet mint, DQ, Moxleys, PSU Creamery, Ben & Jerry's.
- Determine entity types (groups of semantically related entities):
- Ice cream.
- Ice cream makers.
- Determine attributes and domains --- essential properties and their
- Ice cream maker: ID, name, address, phone, hot line, etc.
- Attributes can be set-valued: hobbies, children, courses.
These are not supported in the relational model. Reconciliation?
- Choose key. Similar to candidate key, but watch out for set-valued
- Derive schema. Similar to relation model, but graphical:
- Key indicator.
- Set-valued attribute indicator.
- Represent in relation model.
Implications of set-valued attributes:
- ``Same'' tuple occurs multiple times.
- Set-valued attributes must be included in key.
CREATE TABLE Person (
PRIMARY KEY (SSN, Hobby) );
Steps in the design process:
- Determine what relationships are needed.
Identify the related entities: Person likes ice cream.
- Define roles: How each entity enters into the relationship.
Usually obvious, but what about: Employee reports to employee?
Subordinate, supervisor roles.
- Define any attributes specific to the relationship. Examples:
- Start date of employment.
- Date of major declaration.
- Affinity for ice cream flavor.
- Design the schema:
- Attributes: Choose domain, decide scalar or set-valued.
- Roles: Derive entity types.
- Determine constraints.
- Produce relationship diagram. Examples:
- Role indications.
- Key indicators: arrow, set.
What are the keys?
- All relationship instances within the relationship type must be
unique on key (obvious).
- Minimal subset of roles, attributes.
- Again, not exactly the same as the candidate key.
- Representation in relational model:
- Attributes: carry over directly.
- Roles: Include the primary key of each entity type as
Not the primary key of the relation!
- Candidate keys: Use keys of relationship. Dealing with set-valued
- Foreign keys: Attributes corresponding to roles become foreign
keys referring back to relation created from the entity type.
CREATE TABLE WorksIn (
PRIMARY KEY (ProfId),
FOREIGN KEY (ProfId) REFERENCES Professor (Id),
FOREIGN KEY (DeptId) REFERENCES Department );
CREATE TABLE Married (
PRIMARY KEY (SSNHusband),
-- Assume we dealt with Hobby so that SSN is now
-- primary key in person.
FOREIGN KEY (SSNHusband) REFERENCES Person (SSN),
FOREIGN KEY (SSNWife) REFERENCES Person (SSN) );
- IsA relationships and entity type hierarchies.
Use inheritance from object design.
- Student IsA Person.
- Professor IsA Employee Person.
- How do you guarantee that an entity participates in a particular
- A professor is a member of some department.
- A student is taking courses this semester.
- Implementation: define a foreign key from the entity type relation to
the relationship relation.
Or, use a trigger (don't have the keys we'd like --- transcript relation in
student example). Performance tradeoffs.
Thomas P. Kelliher
Tue Feb 5 11:39:03 EST 2002