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.
When defining select queries and statements, you are required to adhere to the following assumptions and output formatting conventions:
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.
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:
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.
All students are to write queries in SQL to answer the following :
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.
Compile a word document named YourStudentId-Documentation.docx/pdf that includes the following: