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