Question 1
Exercise 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. Eachvehiclehasdifferentdriversandadrivermaydrivedifferentvehicles. Somevehiclesmaynotbe 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