Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

CSC8017 Database Systems Coursework 1

This coursework is worth 50% of the total assessment for this module.

Aims:

To assess your ability to:

● Design a relational database, expressing that design in an entity-relationship diagram.

● Implement the design in MySQL.

Learning Outcomes:

● Design a database from a problem statement.

● Implement a database designed with an E-R diagram.

Problem Statement:

The organisers of a charity running competition have asked you to design a database which they can use to provide more efficient administration during the competition.

The competition consists of a number of races featuring different teams of athletes at different venues in nearby towns. Some of these venues are stadia, others are streets or parks for road racing. All of the venues host many different races during the competition. Teams compete in a number of different races during the competition. Different athletes have different specialist events so each team may bring up to (but not necessarily exactly) 15 athletes.

Customers have to book tickets to watch the races in advance. There are no limits on the number of races a customer may attend. If a customer wishes to buy many tickets for a particular race, the organisers only need to know the details of the customer who is actually making the booking.

The organisers need to store basic contact details for customers, e.g. address and telephone number. For teams, basic contact details (particularly e-mail and phone number) are required but there is some further information needed too. Each team has a name which you may assume is unique. To help produce the competition publicity material, a brief overview/description is needed for each team. The organisers need contact details for each team’s agent. Note that an agent is a person or company which represents a team and handles the administration surrounding booking that team to appear at events, etc. One agent might represent multiple teams. The agent only represents the team as a whole and does not represent individual athletes. With regards to the individual athletes, the only information required about them are their name and a brief biography that can be used in the race programmes. You may assume that there are no athletes who compete for multiple teams during the competition.

For each venue, basic contact details and the maximum number of visitors it can accommodate are required. For the street races, the location of the start/finish line is used as the address of the venue In case of emergency, the organisers need the names, phone numbers and job titles of up to five senior members of staff at each venue. You may assume that all venues have sufficient staff members and that no staff member works at more than one venue.

Finally, the organisers would like you to include some information about each race - the date, start time, venue and the number of tickets sold so far. Each race also has a unique reference number (starting at 1) which is used to distinguish between races plus a brief description of that race, e.g. “Men’s Marathon” or “Ladies’ 100M Final”.

Tasks:

1) Draw an entity-relationship diagram for the above scenario. You must explain any assumptions that you make during the design process. (35 marks).

2) Implement your design in MySQL. Populate your database tables with data of your choice. If you make any changes to your original design from Task 1, these must be explained. Include the SQL statements you used to create your tables in your answer document and a screenshot of each table’s contents (i.e. the result of doing SELECT * from each table). If you are unsure of how to take a screenshot, please see http://www.take-a-screenshot.org/ (15 marks).

What to submit: A single Word or PDF document containing your answers to the above tasks. You should submit your work electronically through NESS.