The goal for these laboratory activities is for you to practice the technique of deriving a conceptual model in the form of an (enhanced)entity-relationship (EER) diagram from a data requirements specification (DRS), such as you have practiced deriving from a data flow diagram (DFD) in Laboratory 1.

Tasks 01:

A taxi company needs to model their activities.

There are two types of employees in the company: drivers and operators. For drivers it is interesting to know the date of issue and type of the driving license, and the date of issue of the taxi driver’s certificate. For all employees it is interesting to know their personal number, address and the available phone numbers.

The company owns a number of cars. For each car there is a need to know its type, year of manufacturing, number of places in the car and date of the last service.

The company wants to have a record of car trips. A taxi may be picked on a street or ordered through an operator who assigns the order to a certain driver and a car. Departure and destination addresses together with times should also be recorded.

Tasks 02:

Create a conceptual model using based on the case study below.

A tennis club called “Kilburn Tennis” manages information about their players, their current subscription, and official matches they play. A player has a name, year of birth, and gender. They also record their address which is usually composed of street, house number, postcode and town. Each player has a subscription for which the club is interested in the amount paid, the date it was paid, and the validity of the subscription, that is, until when that subscription is valid. Once a subscription is overdue, players have to pay for another subscription.

Players can belong to a team. Each team has a division and a captain, which is also a player. Players can play any number of matches. For each match the date and location have to be recorded. Matches are played by two players at a time. For each player playing a match the number of sets won has to be recorded as well.

In some occasions players misbehave, which has led the club to introduce penalties. Penalties are charged to players for a specific amount (in sterling pounds). Each penalty has a due date and an indication whether it has been paid already or not.

The club recently opened volunteer positions to help managing the club. All volunteers are players and they have start and end dates as volunteers. Volunteers register subscriptions and charge penalties. Each subscription has to be registered by a volunteer. Each penalty has to be charged by a volunteer also.

Tasks 03:

N.B. - This task is deliberately worded in an awkward manner and you should use your best judgement in your design choices.

Your task is to create an (E)ER diagram to model the structure of a company, Stockport Hats, according to the following specification, which was recorded in a client Requirements Interview with Mr Richard Cobden, Managing Director of Stockport Hats. Since the language is not precise, you may need to make some assumptions and decisions as you go:

1. Stockport Hats is organised into departments. Each department has a name, number and an employee who manages the department. We need to note the start date of the department manager. Each department controls a number of hat-related projects. Each project has a unique name, unique number and is located at a single site.
2. We need to store each Employee’s name, social security number (SSN), their home address, salary, gender, and date of birth.
3. Each employee belongs to only one department but may be assigned to several projects.
4. We need to keep track of the number of hours per week that an employee works on each project.
5. We need to keep track of the supervisor of each employee.
6. We run a health insurance scheme that an employee can share with their family members, so an employee may have a number of dependents. We need to keep a record of each dependent’s name, gender, date of birth, and relationship to the employee.

I have provided some solutions to the ER Models, remember these are my interpretations, your diagrams may look different but that is not to say that they are wrong.

Lab 02 Solutions