CIS 207 Database Design and SQL

CIS 207 Database Design and SQL

HOMEWORK: # 2 – Sections 4 & 5

DUE:

Exercises 1 – 4 refer to the Try It / Solve It exercises in Oracle iLearning.

These are found in iLearning on the first page of each module..

1. Section 4, Practice Guide Database Design Lesson 1, Exercise 1, 2, 3, 4

1. Identify which item off of the following list is the supertype entity and which items are the subtypes of that entity.

Solution:

Amputation = ______

Visual Impairment = ______

Hearing Impairment = ______

Disability = ______

Paralysis = ______

2. For each rule stated, indicate if the rule is applicable to supertypes or subtypes.

Solution:
______They share common attributes

______They inherit all attributes and relationships of the entity

______It never exists alone

______It contains the attributes held in common by all instances

3. Name three things you consider when modeling supertypes and subtypes.

Solution:

4. Find the incorrect subtypes in the illustration. Explain why you think the subtype is incorrect. Adjust the model to improve it.

Solution:

2. Section 5, Practice Guide Database Design Lesson 1, Exercise 1 (b, d, f, h, j, l, n)

1. Draw ERDs for each of the following. Draw softboxes, relationship lines, and labels for each relationship in both directions. Indicate non-transferability when appropriate.

Solution:

b. Each room may house one or more guests. Each guest may stay in one and only one room.

d. Each hotel may be the host of one or more guests. Each guest may be hosted in one or more hotels.

f. Each garment must have one and only one price. Each price may be for one or more garments.

h. Each automobile must use one and only one tire size. Each tire size may be used by one or more automobiles.

j. Each person must be of one and only one blood type. Each blood type may classify one or more persons.

l. Each student may learn from one or more teachers. Each teacher may educate one or more students.

n. Each fingerprint must belong to one and only one person. Each person must have one and only one fingerprint.

3. Section 5, Practice Guide Database Design Lesson 2, Exercise 3

3. Draw an entity relationship diagram to represent the following:

a. Each CLUB must be assigned to one and only one DEPARTMENT

b. Each DEPARTMENT may be responsible for one or more CLUBs

c. Each STUDENT may join one or more CLUBs

d. Each CLUB may be composed of one or more STUDENTs

Solution:

4. Section 5, Practice Guide Database Design Lesson 3, Exercise 1

1. Resolve the M:M between TEACHER and CLASS as well as INTERPRETER and LANGUAGE. For each intersection entity, think of additional attributes like a UID.

Solution

5. Section 5, Practice Guide Database Design Lesson 4, Exercise 1

1. Identify the part of the CRUD analysis that best suits the task in the table.

• Create

• Retrieve

• Update

• Delete

Alter
Bring up
Change
Discard
Enter
Import
Input
Load
Look Up
Modify
Print
Purge
Read
Record
Remove
Trash
View

Upload the completed lab to lab 2 in Canvas.

Homework_2_Sp_18.doc
1/30/18