Creating and Querying a Database

This assignment follows on from assignment 1 in which a junior basketball tournament database administration system was designed. An E-R diagram for the system is shown on page 5. Your task in this assignment is to create a database (using SQL) based on this given design - using Microsoft Access. You will also write some SQL code to insert data into the database and to query it using SQL to answer some questions.

The first stage of the assignment is to create the database using SQL “CREATE TABLE…” statements, then for stage 2, you will insert some test data using SQL “INSERT INTO TABLE … VALUES ( ) ..” statements, then stage 3. write some queries using SQL “SELECT … FROM …. WHERE …” queries. Each stage is worth 5%. An additional 5% will be awarded based on your documentation. Your documentation must outline any assumptions you have made as well as provide a detailed description of each of your SQL queries. Your documentation should also outline your testing of SQL query code, the data you inserted and why you chose that data to test your queries.

This is an individual assignment. Each student is to submit their database as an access database file as well as their documentation in a word document or pdf file. Students are to familiarise themselves with the school policy regarding plagiarism and ensure that all work is completed individually. It is expected that students will choose their own test data in addition to the sample provided in this assignment specification and that no two students will submit an assignment with identical documentation or test data.

Stage 1. Creating your database

1.1. Conventions

When defining select queries and statements, you are required to adhere to the following assumptions and output formatting conventions:

• Names of people should be printed as GivenNameFamilyName (e.g. John Smith) in a column labelled NAME
• Identification fields will be created using Numeric Integer Values
• Phone Numbers will be stored as Text character fields of length up to 10 characters
• Address fields will be stored as Text character fields of length up to 50 characters
• Passwords will be stored as a string of characters up to 10 in length

According to the specification, ER design and sample schema provided on page 3, you are required to complete the following tasks. You may only use SQL View to create your queries instead of using the interface version. (Queries will be considered as incorrect solutions if the queries are in the format transferred from design view to SQL.) You are free to use more than one query for each question.

1.2. Create table SQL statements

You are required to create the tables using SQL CREATE TABLE …. Statements.You can only use SQL View to create and insert instead of using the interface version. You can enter SQL view in MSACCESS 2007 by choosing the create menu, selecting ‘query Design’ (on Right), closing the pop up window that appears and then clicking on SQL View (top left). Type in your SQL , check it works by running (!) tab , then save your SQL as a ‘query’.

Save all the create statements that you write to complete this task and include a copy of each of these in your documentation.

Create your tables including relationships (foreign keys), based on the E-R diagram provided on the next page and the Table structures in the appendix to this document. The following examples show create statements for Staff, Official, Umpire, Team Membership and Player tables. Your SQL is expected to look similar to these. Start by entering each of these statements into a ‘query’, running each and saving in your database. Save each create table command on a separate query tab in SQL View. In your SQL statements, you must create primary keys, foreign keys and constraints on values that cannot be NULL. Use the following example SQL Create statements to get you started: See image.

Save the database file as YourStudentId-Create.accdb (format xxxxxxx-Create.accdb) for example 2225991-Create.accdb.

Stage 2. Inserting test data

Using SQL INSERT INTO statements, insert some data records as described in this section. You are also expected to make up your own additional test data to insert into your database. Choose distinguishing data that will help you test that your queries are correct. Save all your insert statements and include them in your documentation.

Insert at least 2 records of data that you invent into each table you created. This should be inserted In addition to the suggested test data that is described below. In your documentation, explain what extra data you chose to insert and why it will help with your testing.

Insert common test data based on the following description:

• The age groupings available are under 12 years, under 14 years and under 16 years. The gradings are Red, Green and Gold. Each coach is assigned to coach only one team for the season. The coach does not take out club membership. Referees are given a level of accreditation (Level 1 or 2).
• Club EasternEagles has teams A with teamID 1 and B with teamID 2 in grade Gold of under 12 age group. Club Giants has teams A in RED division of under 14 with teamID 3 and team B in under 14 Gold with teamID 4. Each team has 7 players.
• The players in each team should have a date of birth that is consistent with the age level in which they are playing. For a player to play in the under 12 division, that player must be under 12 years at the end of the year 2011. In other words, a player in under 12 in 2011 will have a date of birth after 31-12-1999. A player is allowed to play in a higher division, that is, a player in under 12 division may play in the under 14 division if they choose. Insert at least one player who has enrolled in the wrong division (i.e. the player is too old). Players can only play in one team for the season.
• Create 2 venues: 'CoronetCity' and 'StMarys' and at each venue, create 4 courts, numbered 1,2,3,4.

Think carefully about what additional data needs to be inserted into your tables. You may use the datasheet view to insert test data, but, once you are convinced of what data you need, you MUST also write the sql INSERT commands to insert the data.

The Design: E-R Diagram showing cardinality and participation between relationships See image.

Stage 3. Querying the database using SQL queries

All students are to write queries in SQL to answer the following :

• How many teams are in the under 14 tournament?
• List the clubs that have teams in the under 14 series.
• List the Club Name and TeamID for all teams in the under 12 RED series.
• We need to find out if there any players who are too old for the division in which they are playing. Produce a list of Players, their names, club (ID and Name) and player ID along with their DOB, their age this year, and the age division in which their team is enrolled. To make it easier to see the age difference, create a new output column in the query (AgeDifference) that is based on the expression: Age Difference = The Player’s age this year – TeamAge.
• How many venues are unavailable due to status ‘unavailable’?
• List all players (Name and ID) who have scored an average of more than 15 points per game during the season. Use a sub-select query to solve this query.
• Also list the Club and player name for each of the players who have been scored more than 20 points in any game. Order the results so that players for each club appear in the list together (HINT: use ORDER BY as well as GROUP BY)
• List, for each grade in the tournament, the average number of personal fouls recorded so far.

Make sure that you have inserted data into your database to thoroughly test that your queries are correct. Save each query. Then Save a copy of the database as YourStudentId-Query.accdb (format xxxxxxx-Query.accdb) for example 2225991-Query.accdb.

Stage 4. Documentation

Compile a word document named YourStudentId-Documentation.docx/pdf that includes the following:

• Assumptions you have made in the implementation of tables in the database
• Your SQL “CREATE TABLE… “ statements
• Your SQL “INSERT into TABLE … “ statements
• Your SQL “SELECT from TABLE …. “ queries
• A description of your test data and how it was used to test your queries were correct. Explain how you chose distinguishing data that showed that the query worked (include data that should be selected and also data that should be excluded).
• Your test results: A sample of the output expected for each query and the output actually generated by your query.