Database Design and Management: Assignment

MIS5370: Database Design and Management Home work

Question 1. After completing a course in database management, you have been asked to develop a preliminary ERD for a symphony orchestra. You discover the following entity types that should be included.

• CONCERT SEASON: The season during which a series of concerts will be performed. Identifier is Opening_Date which includes Month, Day, and Year.

• CONCERT: A given performance of one or more compositions. Identifier is Concert_Number. Another important attribute is Concert_date, which consists of the following: Month, day, year, andTime. Each concert typically has more than one concert date.

• COMPOSITION: Composition to be performed at each concert. Identifier is Composition_ID, which consists of the following: Composer_Name and Composition_Name. Another attribute is Movement_Number and Movement_Name. Many, but not all, compositions have multiple movements.

• CONDUCTOR: Person who will conduct the concert. Identifier is Conductor_ID. Another attribute is Conductor_Name.

• SOLOIST: Solo artist who performs a given composition on a particular concert. Identifier is, Soloist_ID. Another attribute is Soloist_Name.

During further discussions you discover the following.

• A concert season schedules one or more concerts. A particular concert is scheduled for only one concert season.

• A concert includes the performance of one or more compositions. A composition may be performed at one or more concerts or may not be performed.

• For each concert there is one conductor. A conductor may conduct any number of concerts or may not conduct any concerts.

• Each composition may require one or more soloists or may not require a soloist. A soloist may perform one or more compositions at a given concert or may not perform any composition. The symphony orchestra wishes to record the date when a soloist last performed a given composition (Date_Last_Performed).

Draw an ERD to represent what you have discovered. Identify a business rule in this description and explain how this business rule is modeled on the ER diagram.

Answer)

Question 2: A bank has three types of accounts: checking, savings, and loan. Following are the attributes for each type of account.
CHECKING: Acct. No, Date opened, Balance, Service Charge
SAVINGS: Acct. No, Date opened, Balance, Interest rate
LOAN: Acct. No, Date opened, Balance, Interest rate, Payment.
Assume that each bank account must be a member of exactly one of these subtypes. Using generalization, develop and EER segment to represent this situation using traditional EER notation and Visio notation.
Answer)
Traditional EER Notation

Visio Notation

Question 3: Part I
For each of the following relations, determine the normal form for that relation giving your reasoning for such determination. If the relation is not in third normal form (3NF), decompose it into 3NF relations. Functional dependencies (other those implied by the primary key) are shown where appropriate.
a. CLASS (Course_No, Section_No)
b. CLASS (Course_No, Section_No, Room)
c. CLASS (Course_No, Section_No, Room, Capacity)
Room Capacity
d. CLASS (Course_No, Section_No, Course_Name, Room, Capacity)
Course_No Course_Name RoomCapacity
Answer)
a) CLASS (Course_No, Section_No) - This relation is a 3NF and needs no further modification.
b) CLASS (Course_No, Section_No, Room) - This relation is a 3NF and do not require modification.
c) CLASS (Course_No, Section_No, Room, Capacity) Room → Capacity

The relation Room → Capacity is not a 3NF because the attribute ‘Capacity’ is related to ‘Room’ and not ‘Class’. Now, this happens to be a transitive realtion and so is a 2NF. So to make the realtion a 3NF, we need to do the following:

CLASS (Course_No, Section_No, Room)
ROOM (Room, Capacity)
d) CLASS (Course_No, Section_No, Course_Name, Room, Capacity)
Course_No → Course_Name and Room → Capacity
The above relations are in 1NF.
The partial dependecy is Course_No → Course_Name
The transitive dependency is Room → Capacity

The following is the 2NF for the above relation
CLASS (Course_No, Section_No, Room, Capacity)
COURSE (Course_No, Course_Name)

The following is the 3NF for the above 2NF
CLASS (Course_No, Section_No, Room)
COURSE (Course_No, Course_Name)
ROOM (Room, Capacity)

Part II) Figure below shows a class list from Millennium College.

Convert the above user view to a set of 3NF relations using an “enterprise key.” Assume the following
• An instructor has a unique location
• A student has a unique major
• A course has a unique title

Answer)
The user view when converted to 3NF with the given assumptions is

OBJECT (OID, Object_Type)
INSTRUCTOR (OID, Instructor_Name, Instructor_Location)
COURSE (OID, Course_Title, Course_No, Instructor_Name)
STUDENT (OID, Student_No, Student_Name, Major)
GRADE (OID, Student_No, Course_No, Grade)

Question 4. Table below shows a relation called GRADE REPORT for a university.

You are required to do the following.
a. Draw a relational schema and diagram the functional dependencies in the relation in the form it’s presented.
b. Determine what normal form the relation is (currently) stating the reason why it is in such normal form.
c. Decompose the GRADE REPORT into a set of 3NF relations
d. Draw a relational schema for your 3NF relations and show the referential integrity constraints.
e. Draw an ERD of the final version of 3NF relational schema

Please provide separate answer for each of the sub parts of questions viz. a, b, c, and d and do not combine these parts.
Answer)
4)
a. Draw a relational schema and diagram the functional dependencies in the relation in the form it’s presented.

b. Determine what normal form the relation is (currently) stating the reason why it is in such normal form.
The above relation is in its 1NF (1st Normal Form), because it has dependencies and so, it cannot be depicted in its 2NF (2nd Normal Form.)

c. Decompose the GRADE REPORT into a set of 3NF relations

d. Draw a relational schema for your 3NF relations and show the referential integrity constraints.

e. Draw an ERD of the final version of 3NF relational schema

Question 5: For each of the following relations, indicate the Normal Form for that relation. If the relation is not in third Normal Form, decompose it into 3NF relations. Functional dependencies other than those implied by the Primary Key are shown where appropriate.
a) PATIENT_TREATMENT(Patient_ID, Physician_ID, Treatment_Code, Date)
b) PATIENT_TREATMENT(Patient_ID, Physician_ID, Treatment_Code, Date, Time)
c) PATIENT_TREATMENT (Patient_ID, Physician_ID, Treatment_Code, Date, Time, Patient_name, Patient_Room, Room_phone_no) Patient_IDPatient_name and Patient_RoomRoom_phone_no
d) PATIENT_TREATMENT(Patient_ID, Physician_ID, Treatment_Code, Date, Time, Patient_name,Patient_Room, Room_phone_no, Physicisn_name) Patient_IDPatient_name and Patient_RoomRoom_phone_no and Physician_ID Physician_name
Note: You may make reasonable assumptions if necessary. Please list all such assumptions made as foot notes.
Answer)
a) 3NF

b) 3NF

c) 1NF
PATIENT_TREATMENT(Patient_ID, Physician_ID, Treatment_Code, Date, Time)
PATIENT(Patient_ID, Patient_Name)
ROOM(Patient_Room, Room_Phone_No)

d) 1NF
PATIENT_TREATMENT(Patient_ID, Physician_ID, Treatment_Code, Date, Time)
PATIENT(Patient_ID, Patient_Name)
ROOM(Patient_Room, Room_Phone_No)
PHYSICIAN(Physician_ID, Physician_Name)

Question 6: Develop a 3NF Relational schema for the following ERD of small production unit
a) Map all functional dependencies
b) Map all referential integrities
c) Ensure that all relations are in 3NF (if not in 3NF, decompose them into 3NF)

Note: Remember to denote primary keys with underscores and foreign key with dash line underscore.

a) Functional Dependencies
SALE
Receipt_No
Sales_Date

SALE_ITEM
Receipt_No
Product_ID
Qty_Sold

PRODUCT
Product_ID
Description

RECIPE
Product_ID
Inventory_No
Qty_Used

INVENTORY_ITEM
Inventory_No
Item_Descript
Qty_in_Stock
Type_of_Item
Min_Order_Qty

SHIPMENT_ITEM
Shipment_No
Inventory_No

SHIPMENT
Shipment_No
Invoice_Date
Paid? Y/N

INVOICE
Invoice_No
Invoice_Date
Vendor_ID

VENDOR
Vendor_ID
Vendor_Name

b) Referential Integrities

SALE
Receipt_No
Sales_Date

SALE_ITEM
Receipt_No
Product_ID
Qty_Sold

PRODUCT
Product_ID
Description

RECIPE
Product_ID
Inventory_No
Qty_Used

INVENTORY_ITEM
Inventory_No
Item_Description
Qty_in_Stock
Type_of_Item
Min_Order_Qty

SHIPMENT_ITEM
Shipment_No
Inventory_No

SHIPMENT
Shipment_No
Invoice_Date
Paid? Y/N

INVOICE
Invoice_No
Invoice_Date
Vendor_ID

VENDOR
Vendor_ID
Vendor_Name

C) The relation between Shipment and Vendor is not in 3NF. Hence another associative entity INVOICE is created to make the relation 3NF.

Question 7 Following is the diagram depicting an EERD of Vacation Property Rentals. This organization rents preferred properties in several states. As shown in the figure there are two basic types of properties: beach properties and mountain properties.
a) Transform the diagram into a relational schema that shows referential integrity constraint (see figure 4-5 on page 161 in textbook for example).
b) For each relation, diagram the functional dependencies (see figure 4-23 or 4-27 of the textbook for example)
c) If any of the relations are not in 3NF, transform those relations to 3NF
d) Suggest an integrity constraint that would ensure that no property is rented twice during the same time interval.

Answer)
a)Referential Integrity

b) Functional Dependencies

c) 3NF
d) A property shall have one and only one rental agreement in force at one time.