Midterm Questions -
Question 1 - The table below shows all information stored for an order entry system. It keeps track of customer information, order date and item/quantity, as well as MSRP and manufacturer for each item. Customer No is the unique identifier for customer. Business Rules: A customer can place multiple orders, but each order contains at most one item (multiple quantities are allowed), and the customer cannot order the same item again during the same day.
|
CustomerNo
|
CustomerName
|
CustomerCity
|
CustomerPhone
|
OrderDate
|
Item
|
Quantity
|
Manufacturer
|
ManufacturerPhone
|
MSRP
|
|
S1011
|
James Bond
|
DC
|
1238888, 1239999
|
1/9/05
|
ipad
|
1
|
Apple
|
8888999
|
888
|
|
S1011
|
James Bond
|
DC
|
1238888, 1239999
|
1/9/05
|
iphone
|
2
|
Apple
|
8888999
|
999
|
|
S1024
|
Andrew w Joe
|
LA
|
7778888, 7779999
|
1/9/05
|
kindle
|
3
|
Amazon
|
7773333
|
333
|
|
S1024
|
Andrew w Joe
|
LA
|
7778888, 7779999
|
2/7/05
|
Kindle
|
5
|
Amazon
|
7773333
|
333
|
|
S1032
|
Kevin King
|
New York
|
9998888
|
3/1/05
|
iphone
|
1
|
Apple
|
8888999
|
999
|
|
S1032
|
Kevin King
|
New York
|
9998888
|
3/1/05
|
Pixel 2
|
8
|
Google
|
3338899
|
777
|
Evaluate if the table shown above is normalized, if not, describe and illustrate the process of normalizing the table shown above to third normal form. State any assumptions you make about the data shown in this table.
I. Is the table in 1NF, 2NF, or 3NF?
II. If not, describe and illustrate the process of normalizing it to 3NF. Identify functional dependencies before each conversion.
III. Show the final tables with data after normalization.
Question 2 - Design a database.
John owns a portfolio of rental properties with his parterns. He wants to develop a database to store property information as well rental data. Below are some of the data that need to be kept track of.
A. For each property, the database needs to store property address, size, number of rooms and bathrooms, and purchase date.
B. There are two types of properties - single family house and condo. Need to store yard size for single family house and monthly HOA (Home Owner Association) fee for condo.
C. For current and potential customers, need to store customer name, current address, family size, and contact phone number.
D. A customer needs to submit an application to rent a property. Need to track application submission date, potential start date, application result and explanation.
E. Once approved, a lease will be signed and need to track start date, end date, sign date, month' rent, and deposit amount.
F. Need to track monthly payment information including payment amount, pay date, and payment method.
G. A customer can set up different payment methods. Each method includes financial institute name, address, and account number. There are two types of methods - credit card and check. For credit card, need to store expiration date and security code. For check, need to store bank routing number.
Please complete the following tasks to design the database to meet requirements specified. State any relevant assumptions you make, but do not add any additional data requirement unless it is really necessary.
I. Identify entities and attributes. List entity name and attribute names for each entity.
II. Identify relationships among entities. List relationship type in terms of cardinality and specify business rules (e.g. 1: M between Library and Branch: a library has many branches; a branch belongs to one and only one library).
III. For each entity, identify primary key and key if applicable. If you use surrogate key for PK, also specify business unique key (natural key). For FK, also specify parent entity and the matching attribute in parent entity.
IV. Create ERD using crow's foot notation using ER Assistant or Visio. The ERD should incorporate all items mentioned in I, II, and III (NOTE: I, II, and III still need to be answered separately in narrative format).