principles of database design, part ii aims 2710 r. nakatsu
TRANSCRIPT
![Page 1: Principles of Database Design, Part II AIMS 2710 R. Nakatsu](https://reader036.vdocuments.us/reader036/viewer/2022083004/56649e7d5503460f94b7fbfb/html5/thumbnails/1.jpg)
Principles ofDatabase Design, Part II
AIMS 2710
R. Nakatsu
![Page 2: Principles of Database Design, Part II AIMS 2710 R. Nakatsu](https://reader036.vdocuments.us/reader036/viewer/2022083004/56649e7d5503460f94b7fbfb/html5/thumbnails/2.jpg)
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.
![Page 3: Principles of Database Design, Part II AIMS 2710 R. Nakatsu](https://reader036.vdocuments.us/reader036/viewer/2022083004/56649e7d5503460f94b7fbfb/html5/thumbnails/3.jpg)
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.
![Page 4: Principles of Database Design, Part II AIMS 2710 R. Nakatsu](https://reader036.vdocuments.us/reader036/viewer/2022083004/56649e7d5503460f94b7fbfb/html5/thumbnails/4.jpg)
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
![Page 5: Principles of Database Design, Part II AIMS 2710 R. Nakatsu](https://reader036.vdocuments.us/reader036/viewer/2022083004/56649e7d5503460f94b7fbfb/html5/thumbnails/5.jpg)
ER Diagrams with Crow’s Foot Notationand Cardinalities
![Page 6: Principles of Database Design, Part II AIMS 2710 R. Nakatsu](https://reader036.vdocuments.us/reader036/viewer/2022083004/56649e7d5503460f94b7fbfb/html5/thumbnails/6.jpg)
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.
![Page 7: Principles of Database Design, Part II AIMS 2710 R. Nakatsu](https://reader036.vdocuments.us/reader036/viewer/2022083004/56649e7d5503460f94b7fbfb/html5/thumbnails/7.jpg)
Database Systems, 9th Edition7
Another ERD Notation
![Page 8: Principles of Database Design, Part II AIMS 2710 R. Nakatsu](https://reader036.vdocuments.us/reader036/viewer/2022083004/56649e7d5503460f94b7fbfb/html5/thumbnails/8.jpg)
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.
![Page 9: Principles of Database Design, Part II AIMS 2710 R. Nakatsu](https://reader036.vdocuments.us/reader036/viewer/2022083004/56649e7d5503460f94b7fbfb/html5/thumbnails/9.jpg)
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.