Laboratory 2

ER Modelling and Relational Table Transformation

References

Lecture Notes: Topics 2 and 3 - Elmasri and Navathe, 2017: Chapters 3 and 9

In this class you will learn to model given database problems using the Entity-Relationship modelling technique and how to transform an ER-Model into Relational Tables. You are to use the notation shown in the lecture notes for the Rental-Property example.

Helpful Tips:

1.      Relational Table Transformation - 7 Steps

2.      Foreign key is not drawn in the ER-Model, only when you transform it into tables, FK

will be added into tables based on the ER-Model transformation rules.

3.      After the transformation, do not forget to list the final tables.

Exercise 1

a)      Construct an ER-Model for the following problem description. State any assumptions that you make.(including entity, attribute, pk, relationship, cardinality and participation)

The Municipal Arts  Centre (MAC) runs  creative  art  courses  for residents  of the  local community. They require a database to keep track of course and current enrolment details. At present they run five courses: Pottery; Photography; Painting with Watercolours; Oil Painting; Needlework; and Doll Making. Each course is offered three times a year, once in each term. Information to be kept on courses includes: the course code, course title, course description and course cost. The cost of each course is fixed across all terms. Each course may have a number of different class times per week for residents to choose from. A class will only go ahead if it has at least five people enrolled. The maximum number of people a class can cater for is 15. The class venue, time and day of the week need to be stored.

Each class is run by one instructor. Each instructor may teach up to three classes, however if there are insufficient enrolments they may not be required for a particular term. The name, address and telephone number of each instructor need to be stored. Each resident may take up to three courses per term. Once they have enrolled in one course their details are to be kept on the system so they can be sent advertisements of future courses.

Answer:

b)      Transform your ER-model into relational tables. Make sure you follow the 7-steps and list your final tables at the end.

Answer:

Exercise 2

a)      Construct an ER-Model for the following problem description. State any assumptions that you make.

DSS (Dig, Set, Spike) Volleyball is an amateur volleyball association. Each team in the association represents a particular suburb. Each suburb has its own sporting complex where the team's home games are played. Teams are identified by their id and also have a name, a contact person and a contact phone number. Each team has a maximum often players and a minimum of seven players registered. Players are identified by a unique player id and their name, address and contact phone number need to be recorded. Each team has a coach; some have up to two coaches. Coaches also have unique ids, and their name, address, contact phone number and coaching qualifications are required.

During the season, each team plays two games, one as the home team and one as the visitor, against each of the other teams. For each game the date of the game, start time and final score need to be recorded.

As a special treat at the end of the season the association holds a break-up party for the players. At the break-up party treats are given out to all the players’ children who are under 12. The association therefore requires each child’s name and date ofbirth.

Answer:

b)      Transform your ER-model into relational tables. Make sure you follow the 7-steps and list your final tables at the end.

Answer:

Exercise 3

a)      Construct an ER-Model for the following problem description. State any assumptions that you make.

An electrical appliance company needs to keep track of the sales made by its employees. In particular it wants to know which products each employee has sold and who the employee has sold the products to. The company keeps the following personal information about its employees: employee name, address, and contact phone number. For warranty purposes, it also keeps the following information about its customers: customer name, address, contact phone number,products bought and the date on which they were bought.

The company manager requires the new system to also keep track of product stock levels so she/he  knows  when  to  contact  the  company's  suppliers  to  order  more  stock.  Product information includes: a unique product id, product name, product description, unit price, current stock level and the name of the company who supplies the product. In order to easily contact the suppliers the system should also store theirrelevant contact details.

Answer:

b)      Transform your ER-model into relational tables. Make sure you follow the 7-steps and list your final tables at the end.

Answer:

Exercise 4

a)      Construct an ER-Model for the following problem description. State any assumptions that you make.

The Melbourne Employment Corporation (MEC) places temporary workers in companies during peak periods. MEC’s manager gives you the following description of the business:

MEC has a file of candidates who are willing to work. They record the candidate's number, name, contact address, contact phone number and maximum hours the candidate is available per week. Each candidate may have several qualifications. MEC uses special codes to record the candidate’s qualification for a position. As well as recording the code, MEC also records the experience of the candidate in each qualification area, expressed as the number of months of experience. The list of qualification codes includes:

MEC also has a list of companies that request temporaries. Each company is assigned a company number as an identifier. The company name, address, phone number, type of business and principal contact for employment placements is also recorded.

Each time a company requests a temporary employee MEC makes an entry in the open positions file. This file contains a position number, the company requesting an employee, the required qualifications, starting date, anticipated ending date, the number of hours per week required and hourly pay. MEC may be able to fill the opening from the staff on its books; however, in some circumstances it cannot fill the request.

When a candidate has the relevant qualifications, and is available, he/she is given the job, and an entry is made in the placement record  folder. This folder contains  the position number, candidate number, actual start date, total hours worked to date, and end date if the placement  is  completed.  The  placement record  folder  is  used by MEC  as  a  source  of placement histories for its various temporaries. 1

Answer: