闪电代写 -代写CS作业_CS代写_Finance代写_Economic代写_Statistics代写_代码代做_IT代写_加急帮助

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

ISM 6215 - Business Database Systems I

Fall 2018

HW 1 Answers

Questions 1 – Convert the following ERD into logical schema

(a)

Answer 1(a) –

(b)

Answer 1(b)

(c)

Answer 1(c) –

Questions 2 – A Figure below shows the EER diagram of the vacation property rentals. This organization rents properties in several states. As shown in the figure, there are two basic types of properties: beach properties and mountain properties.

· Draw the relational schema in 3 NF. Show the functional dependencies in each relation in the relations in 3NF.

Answer 2 –

a. 3NF Relations, with functional dependencies noted

NOTE- Here the functional dependency was asked to be shown so the student should show it in each relation.

Some student may just write that Activity is a multivalued attribute and they are not creating a separate relation for Activity , as the details of this attribute is not given – this is OK.

Question 3 - Table 4.5 below shows a shipping manifest of a shipping company. Create a 3 NF logical schema of a database that should be able to produce this shipping manifest as a report. In Table 4.5 Captain ID is 002-15 and captain name is Henry Moore. A ship can be captained by different persons over its lifetime and a person can be a captain of different ships over his/her tenure.

Answer the following

· Draw a dependency diagram that shows all functional dependencies (full, partial, and transitive as applicable) for one combined table based on the sample data shown in Table 4.5. Please clearly mark full, partial, and transitive dependencies in your diagram.

· Give example of insertion anomalies that can result in using this relation.

· Convert this one relation into a set of 3NF relations and clearly show functional dependencies (show that they are full functional dependencies only) and referential integrity (PK-FK match).

(10 +8 +12=30 marks)

Answer 3 –

Total weight is a derived attribute and can be calculated from weight and quantity. Therefore, it is not shown here as part of the relational schema.

The Primary Key of the relation is à (ShipmentID, ItemNumber)

Full functional dependency

ShipmentID, Item Numberà Quantity

Partial functional dependency

ShipmentID à Shipment date, ExpArrDate, Origin, Destination, ShipNumber, CaptainID

ItemNumber à Type, Description, Weight

Transitive functional dependency

ShipmentID à CaptianID à Captain Name

Alternative solution – Some students may assume that the ShipNo uniquely determines the captain (each ship is assigned to one captain only). Then ShipmentID determines ShipNumber and ShipNumber determines CaptainID, which intern determines CaptainName.

b. Insertion anomalies

- Adding a new item in a shipment will require duplicate data entry of shipment, ship, and captain details.

- Details about an item cannot be stored unless it is included in a shipment, because storing item details without shipmentID will result in part of primary key being null.

c. 3NF relations with referential integrity and functional dependencies shown

NOTE- Students are asked to show dependencies and referential integrity (PF-FK match) so they should clearly show it.

Questions 4 – A Figure below shows the EER diagram of a restaurant, its tables, and the waiters and waiting staff managers who work at the restaurant. n.

· Draw the relational schema in 3 NF. Show the functional dependencies in each relation in the 3NF relations.

Answer 4 –

3NF Relations, with functional dependencies noted

RTableNo

RTableNoofSeats

RtableRatings

RTableNo

SeatingID

NoofGuests

StTimeDate

EndTimeDate

MEmpID

SeatingID

WEmpID

StTimeDate

EndTimeDate

TipEarned

WEmpID

HourlyWage

MEmpID

WEmpID

Speciality

MEmpID

MonthlySalary

EmpID

EmpFName

EmpLName

Questions 5–

Consider the set of relations in the Figure below. What normal form are these relations in? Convert the relations into the ER diagram. Clearly state any assumption that you have to make in this conversion.

(30 marks)

Answer 5 -

All relations are in 3NF since there are no partial functional dependencies or transitive dependencies. It could be argued that zip code is determined by city and state (also by address with 9-digit zip codes).

While converting into ER Diagram, students must note that the foreign key goes on the many side of the relationship and accordingly choose the entity to place the FK.

The student may assume either optional or mandatory minimum cardinality in this question. But they must clearly show the maximum cardinality in each relationship.

Question 6 –