CS计算机代考程序代写 database ER Exercise 1

Exercise 1

Question 1
The CBD and South East Light Rail is a new light rail network for Sydney, currently under

construction. The 12km route will feature 19 stops, extending from Circular Quay along George

Street to Central Station, through Surry Hills to Moore Park, then to Kensington and Kingsford via

Anzac Parade and Randwick via Alison Road and High Street. Construction will be completed, and

services will start running in 2019.

As part of a Public Private Partnership with the NSW State Government, the construction company

ACCIONA is responsible for delivering the infrastructure for this iconic addition to Sydney’s

transport network and a key initiative of Sydney’s Long-Term Transport Master Plan, thus playing an

important role in the light rail construction. In order to improve the management efficiency, the

construction manager should be very familiar with the construction teams. Please help the manager to

draw an ER diagram based on the following specifications about the construction company

ACCIONA. State your assumptions if any.

The company has multiple construction teams, identified by their team id. Team name is also recorded.
Each team has zero or more workers, only one of which is the team leader. In addition, each team has

at least one vehicle.

A worker must work only in one team and is identified by his/her id. We also need to record the name,
phone number and salary. Each leader can only lead one team to guarantee work quality. Drivers and

team leader are workers as well.

Each vehicle is identified by its plate number. Its model, colour and purchase date are also recorded.
Each vehicle has different drivers and a driver may drive different vehicles. Some vehicles may not be

owned by any team but must be owned by only one team if any.

The company receives construction orders identified by order ID, construction location, working
duration and price. Each order is conducted by multiple teams and a team can conduct only one order.

Draw an ER diagram to represent this scenario, and clearly state the assumptions you make if any.

Question 2
Convert your ER-diagram from Question 1 into a relational model.

Question 3

Consider the ER diagram shown above for part of a BANK database. Each bank can have multiple

branches, and each branch can have multiple accounts and loans.

(a) List the strong (non-weak) entity types in the ER diagram.

(b) Is there a weak entity type? If so, give its name, its partial key, and its identifying relationship.

(c) What constraints do the partial key and the identifying relationship of the weak entity type specify in

this diagram?

Question 4

List concisely the user requirements that led to the ER schema design in Question 3.

Question 5
Cardinality ratios often dictate the detailed design of a database. The cardinality ratio depends on the real-

world meaning of the entity types involved and is defined by the specific application. For the binary

relationships below, suggest cardinality ratios based on common-sense meaning of the entity types. Clearly

state any assumptions you make.

Entity 1 Cardinality Ratio Entity 2

1. Student SocialSecurityCard

2. Student Teacher

3. ClassRoom Wall

4. Country CurrentPresident

5. Course TextBook

6.
Item (that can be

found in an order)
Order

7. Student Class

8. Class Instructor

9. Instructor Office

10. E-bay Auction item E-bay bid