Principles ofDatabase Design, Part II
AIMS 2710
R. Nakatsu
Entity-Relationship (ER)Modeling
ER Modeling is the process of creating a diagram of the structure of the database by defining entities and relations among them.
The ER model serves as the basic database blueprint.
The ER model can be used as a communication tool.
ER Model Components
An entity is represented by a rectangle containing the entity’s name. An entity corresponds to a table in the relational environment.
A relationship is an association between entities. It is represented by a line connecting the two entities.
Cardinality expresses the specific number of entity occurrences associated with the related entity.
Relationships
Types of Relationships One-to-Onee.g., one dean to one university
one head-of-state to one country One-to-Manye.g., one painter paints many paintings
one customer places many orders Many-to-Manye.g., students enroll in courses
ER Diagrams with Crow’s Foot Notationand Cardinalities
ER Diagram Problem Acme Insurance Company is made up of several divisions. A
claims adjuster is always assigned to one and only one division. Claims adjusters work on several insurance claims. Each
insurance claim is always worked on by a team of at least two but no more than five claims adjusters.
Each claims adjuster is assigned to his/her own company car (no claims adjuster needs to “share” a car). A claims adjuster may elect not to be assigned to a company car. Furthermore, some of company cars are “unassigned”—Acme likes to keep a few spare cars in inventory just in case.
Draw the ER Diagram for this situation. Note: a claims adjuster is an employee who investigates and
processes insurance claims.
Database Systems, 9th Edition7
Another ERD Notation
Linking Tables:1:Many and 1:1
In a one-to-one and one-to-many link, you link a primary key in one table to a foreign key in another table.
For a one-to-one relationship, the foreign key must be unique (in Access you set the Index value to Yes (No Duplicates).
Referential Integrity: A condition in which a child table’s foreign key must have either a null entry or a matching entry in the related parent table.
Linking Tables: Many-to-Many For many-to-many relationships, you must
create a separate intersection table to handle the relationship.
Access will not let you define a many-to-many relationship directly between two tables.
The primary key of the intersection table is a composite primary key.
Two one-to-many relationships are created.