Tour Management Agency Database Design & Development

Context

A tour management agency runs concert tours.  It sets up tours by bands.  On a tour, a band gives concerts at venues on various dates. Only one band goes on a tour, and a band goes on only one tour at a time.  Tours have names. A band has musicians as members.   A band also has a music style, such as “heavy metal” or “folk”. There are several tour dates on a tour.  Each occurs at a venue, on a particular date. At each date, a certain amount of money is received in ticket sales.

Each venue has a cost for hiring it.  Each concert yields a certain amount from ticket sales.  The difference between the cost and the ticket sales is the profit on the concert.

What a Database will be used for?

The management agency wants a database about its business.  It wants to be able to find out such things as

• which tours it has arranged.  (Assume that all tours have finished - covering tours that are still going on is an easy extension, but not needed.)
• how much a tour has made.   
• what concerts it arranged 
• on a given date 
• in a given period
• at a given venue at any date
• where any band and its members were on a given date

Discussion with Further Users

It is clear from this information, and your discussions with the managers of the tour management agency, that the things and relations that the managers think about and talk about are these:

• Tours, each of which is a tour by a single band
• Tour-dates.   Each tour-date occurs on a single tour, at a single date, at some venue.  Usually a tour will only play once at a venue, but playing more than one is possible.
• Bands, which have members, and a style of music that they play. 
• Musicians.  The agency does not allow a musician to be a member of more than one band, perhaps because if they were, it would create a risk of the musician having to be on two tours at once.  The line-up of musicians in a band does not change, at least for the bands that they agency manages.   
• Venues.   All the management agency really needs to know about venues is what venues there are, and how much each venue costs to hire.

That list is intended to give a very strong steer to the design of the needed database.    It seems almost certain that each of those items should become either an entity type or a relation in the conceptual design.

The data in the Design

Some of the data that the management agency holds is given in the spreadsheet "tour management agency data". (What is in the spreadsheet is only a sample of that sort of information, but if the database can hold that information, then adding more is just a matter of typing.)

Warning:  in the spreadsheet, the data is organized into rows, and columns, and pages.   That organization may be easy for a human to read.   But remember the importance of each table in a database being normalized - that is,

• each table is a real table, with one and only value in each cell, and with a primary key
• each table is about a single sort of thing.  

What is to be done?

Database Conceptual Design:

Give a conceptual design of a suitable database, with appropriate entity types and relations.

It should be in graphic form, using the notation for conceptual design used in the lectures.
The names of relation can be shown in diamonds, but if you feel that is unnecessary, they can be shown on a line between entity types.

A relation should show its maximum cardinality (1:1, 1:*, *:*).

This should be in a file called "tour management agency conceptual.X", where X is .pptx, .jpg, .pdf, or .png.   Of course it may have been produced using a different format, or by hand, and only converted to one of these formats at the end.

Database Logical Design:

Give a logical design of a suitable database, with appropriate entity types and relations.

It should be in graphic form, using the notation for logical design used in the lectures.

Primary and foreign keys should be indicated.  Arrows should be used to indicate the tables that foreign keys refer to.

This should be in a file called "tour management agency logical.X", and otherwise as for the conceptual design.

Database Implementation:

Give a SQL script that

• creates those tables, 
• Attributes should have appropriate data types.  
• Primary and foreign keys should be defined.   Remember that 
• a table that has a foreign key can only be defined if the table that it references has already been defined.
• when inserting data into a table that has a foreign key, the value of the foreign key must already exist in the table that is referenced and fills them with the data provided, and shows that it has created and filled those tables, The script should also include "SELECT * from ...." commands to show that the tables have been created and filled. 
• and runs some relevant queries.  
• The script should also include code for the queries about this database that are given below.  
• That entire script should be in a text file called "tour management agency script.txt".   (It should not be .sql)

Database Reports/Queries:

These queries are straightforward.   The point of this exercise is more about constructing a database than asking it complex queries.

1. Which bands play rock?
2. Which venues had concerts on tours run by the agency on 17/10/2012, and which bands were giving them?
3. Where was Matthews playing on 18/10/2012?
4. Which music styles have been played at the Midlands Civic?
5. For each tour date on the 'Horizons on tour - 2012' tour, what were ticket sales, the venue hire cost, and the net profit?

Get Project Solution by contacting us

- via WhatsApp: +92-324-7042178

- via email: codelogixstudio@gmail.com