Database application technologies

Specification
The Motor Racing Team
 
A multinational company with business interests in your area has decided that generating positive, but possibly controversial, publicity would help.  They have therefore decided to create a women-only motor racing championship called Women Into Motor Racing (WIMR).  Each team must have two female drivers.

 The team principal must be female as well as at least half of the mechanics and the administrative staff.
 
These restrictions mean that the governing body of WIMR will need a database to track the teams to ensure there is no cheating.  Each team will have to register all their personnel with WIMR.

 However, these are humans, so people may move between teams over time.  It is also possible that people will have more than one role over time.  Someone who is now a driver may become a team principal or a mechanic may take a driving role, etc.


 
The database should also allow race results to be entered.  There are two championships; one for the drivers and one for the teams.  Your database should allow someone else (definitely NOT you) to create a web-based front end to show the league tables for each championship.


 
Points are awarded after a race.  A driver gets 25 points for coming first, 18 for second, 15 for third, 12 for fourth, 10 for fifth, 8 for sixth, 6 for seventh, 4 for eighth, 2 for ninth and 1 for 10th place.  The team get the sum of the points for each of their drivers, e.g.

If a team has drivers who come 2nd and 8th in a race then the team gets 18 + 4 = 22 points.  It is not known how many races there will be each season yet.
 
Your database does not need to show costs/prize money – that is for the accountants to worry about.



Requirements
Create the conceptual erd, physical erd, database (with test data) and queries to support wimr.
 
 
Sample Queries
The following database queries are required to be displayed on screen (i.e. Produce the SQL code only, no form, report or web page).

Choose one of the sets of queries to answer.  If you are in a pair, you cannot choose the same set of queries as your partner.
 
List A
A1.

Joining tables
List the drivers and the team for which they drive.
 
A2 order by
list the mechanics ordered by the name of the team, the family name of the mechanic and the first name of the mechanic.
 
A3 group by … having
list the number of male and female administrators for each team.


 
A4 Sub-queries
Create a query which shows the championship table for the drivers, i.e. The number of points for each driver ordered by their total points.
 
List B
B1. Joining tables
List the mechanics and the team for which they work.


 
B2 order by
list the administrators ordered by the name of the team, the family name of the administrator and the first name of the administrator.
 
B3 group by … having
list the number of male and female mechanics for each team.
 
B4 Sub-queries
Create a query which shows the championship table for the teams, i.e.

The number of points for both drivers in a team ordered by their total points.
 

Deliverables
 
Your electronic submission should include:
• You should upload a report which includes all the deliverables shown below (D1, D2, D3, D4, D5 and D6) with sufficient evidence that each step works.
 
All deliverables are listed below:
D1.

One A4 page containing the conceptual data model diagram (i.e. An entity relationship diagram using consistently either Chen or Crows Foot notation showing:
• Relevant entity types
• The primary key for each entity (underlined)
• Relationship types with role names (plus relationship attributes if any)
• Any sub-classes (showing disjoint or overlapping) which would make the diagram more informative.
• Structural constraints on each relationship (both cardinality and participation)
Note: If you show attributes other than the primary keys (e.g. Foreign keys) on the conceptual model then you will be penalised.


 
D2. For the above model, produce the physical data model. The model represents your mapping from the conceptual model into a relational schema and should show:
• All entities and their relationships to the other entities with their cardinalities.


• Resolve any sub-classes and M-M relationships as necessary.
• For each prospective table identify the primary key and any necessary foreign keys then add all appropriate fields.
• Identify the data types for each field.


 
D3. Make a clear statement of any assumptions that you make about the data, in particular noting any information that you believe should be included but is not mentioned in the outline specification or any assumptions that affect the design of your database.
 
D4.

Create a database for the above schema using the Oracle dbms using sql and populate each table with typical records to clearly demonstrate the application results. Create an insertion plan and then use an on-line data generation site to generate a suitable quantity of test data for each table.  Give the SQL code for the creation scripts and samples of the population scripts.

You should populate the database with sufficient data to clearly and unambiguously demonstrate all queries submitted.
 
D5. The SQL queries used to fulfil the three queries chosen from A1 to A4 or B1 to B4.

 Show the output results (i.e. Screen dumps of your executed queries).
 
D6. Include a detailed evaluation of your work