Database Design 3: Advanced Data Modeling Concepts

CS 320

Composite Relationships Relationship that has attributes Always has M:M cardinality

Composite Relationships:Another Example

Decomposing a Composite Relationship

Can always be decomposed into 2 1:M relationships!

Degree of a Relationship

Specifies the number of entities that participate in a relationship

Up until now, all relationships have been binary (2 entities)

Unary Relationships

Entity has a relationship with itself

Ternary Relationships Relationship exists among 3 entities

UWEC_STUDENT

PK Student_ID

Student_First_Name Student_Last_Name Student_DOB

UWEC_PROGRAM

PK Program_ID

Program_Name Program_Desc

UWEC_ADVISOR

PK Advisor_ID

Advisor_First_Name Advisor_Last_Name

Generalization/Specialization Relationships Does not show cardinalities

Supertype

Subtypes

How common are non-binary relationships? Unary

Rare Ternary, generalization/specialization

More common than you would think

Homonyms Different attributes have the same name

Avoid!This is why attributes should take their

name from their entity

UniversityStudent

PK StudentID

Name

UniversityInstructor

PK InstructorID

Name

Synonyms

Same attribute (field) has a different name in a different table

Avoid! PURCH_ID PROD_ID CUST_ID PURCH_DATE DELIVERY_DATE POUNDS STATUS

1 1 5 28-Oct-04 28-Oct-04 3.5 PAID2 2 6 28-Oct-04 30-Oct-04 15 PAID3 1 9 28-Oct-04 28-Oct-04 2 PAID3 3 9 28-Oct-04 28-Oct-04 3.7 PAID4 3 2 28-Oct-04 3.7 PAID5 1 7 29-Oct-04 29-Oct-04 3.7 NOT PAID5 2 7 29-Oct-04 29-Oct-04 1.2 NOT PAID5 3 7 29-Oct-04 29-Oct-04 4.4 NOT PAID6 2 7 29-Oct-04 3 PAID7 2 10 29-Oct-04 14 NOT PAID7 5 10 29-Oct-04 4.8 NOT PAID8 1 4 29-Oct-04 29-Oct-04 1 PAID8 5 4 29-Oct-04 7.6 PAID9 5 4 29-Oct-04 29-Oct-04 3.5 NOT PAID

PRODUCT_ID PROD_DESC PROD_COSTPROD_PRICE

1 Celestial Cashew Crunch 7.45$ 10.00$

2 Unbrittle Peanut Paradise 5.75$ 9.00$

3 Mystery Melange 7.75$ 10.50$

4 Millionaire’s Macadamia Mix 12.50$ 16.00$

5 Nuts Not Nachos 6.25$ 9.50$

CANDY_PURCHASE

CANDY_PRODUCT

Summary: Strategies for Developing ER Models How do you decide if an item is an entity or an

attribute? An item is an entity if:

Multiple instances exist (or might exist) in the database

An item is an attribute if: It is a property of an entity instance Each entity instance has a single value of that property

Summary: Strategies for Associating an Attribute With the Correct Entity

To which entity does an attribute belong? Example: Is AdvisorName an attribute of a

UniversityStudent instance? Answer: Only if each advisor advises only one

student. Otherwise, you are repeating the name multiple times.

Test Yourself:A composite relationship:

a. Is always a 1:M relationship

b. Is always a M:M relationship

c. Can be made into a new entity

d. Might have an attribute that belongs to the relationship

e. All of the above except a

Test Yourself:A composite relationship:

a. Is always a 1:M relationship

b. Is always a M:M relationship

c. Can be made into a new entity

d. Might have an attribute that belongs to the relationship

e. All of the above except a

Test Yourself: In an ER model, a relationship must always involve at least two entities.

a. True

b. False

Test Yourself: In an ER model, a relationship must always involve at least two entities.

a. True

b. False

Test Yourself: In an ER model, a relationship can never involve more than 3 entities.

a. True

b. False

Test Yourself: In an ER model, a relationship can never involve more than 3 entities.

a. True

b. False

Test Yourself: Naming an attribute CUST_ID in the CANDY_CUSTOMER table and CUSTOMER_ID in the CANDY_PURCHASE table illustrates creating a(n):

a. Composite relationship

b. Acronym

c. Homonym

d. Synonym

e. None of the above

Test Yourself: Naming an attribute CUST_ID in the CANDY_CUSTOMER table and CUSTOMER_ID in the CANDY_PURCHASE table illustrates creating a(n):

a. Composite relationship

b. Acronym

c. Homonym

d. Synonym

e. None of the above

Test Yourself: Suppose you create an attribute named DESC that describes products in the CANDY_PRODUCT table. Then you create a second attribute named DESC that describes customer types in the CANDY_CUST_TYPE table. This is a(n):

a. Homonym

b. Synonym

c. Acronym

d. Generalization/specialization

e. None of the above

Test Yourself: Suppose you create an attribute named DESC that describes products in the CANDY_PRODUCT table. Then you create a second attribute named DESC that describes customer types in the CANDY_CUST_TYPE table. This is a(n):

a. Homonym

b. Synonym

c. Acronym

d. Generalization/specialization

e. None of the above

UW- Eau Claire keeps data on students including student ID, student name, date of birth, campus address and phone number, permanent address and phone number, and email address.

Data is also kept on courses that are offered. This data includes the course call ID (like ‘CS 320’), course name (like ‘Web Database Systems’), department (such as "Computer Science"), and the course description that appears in the catalog. Courses are offered multiple times. For each course section offering, the database stores the term offered (such as "Fall 2012", section number, day, time, location, and instructor.

Instructor data includes instructor ID, name, email address, title, office location, and office phone number. Instructors teach course sections, and instructors also advise students.

Students enroll in course sections, and grades are assigned for students in course sections. Students can have one or more instructors who act as advisors.

Your Turn: Draw the ER Model for the following database application.

Solution