University of Ballarat – Graduate School of Information Technology and Mathematical Sciences ITECH 1006/5006 Database Management Systems Teaching Period 00, 2011 – Weighting 20% Assignment 2: Creating and querying a database This assignment follows on from assignment 1 in which a junior cricket series database administration system was designed. An E-R diagram for the system is shown on page 3. 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 GivenName FamilyName (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 University of Ballarat – Graduate School of Information Technology and Mathematical Sciences ITECH 1006/5006 Database Management Systems Teaching Period 00, 2011 – Weighting 20% Assignment 2: Creating and querying a database 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: create table STAFF ( RegistrationNo INTEGER NOT NULL, Name CHAR(30) NOT NULL, MPhoneNo CHAR(10), Email CHAR(30), Address CHAR(50) CONSTRAINT pkstaff PRIMARY KEY (RegistrationNo) ); CREATE TABLE OFFICIAL (RegistrationNo Number NOT NULL PRIMARY KEY, Password CHAR(10) ); CREATE TABLE UMPIRE (ReigistrationNo INTEGER NOT NULL PRIMARY KEY, UmpireLevel INTEGER); CREATE TABLE PLAYER (PlayerID INTEGER NOT NULL PRIMARY KEY, Name CHAR(30), DOB DATE, ClubID INTEGER NOT NULL, CONSTRAINT clubmem FOREIGN KEY (ClubID) REFERENCES Club (ClubID) ); CREATE TABLE TeamMembership (TeamID INTEGER NOT NULL, PlayerID INTEGER NOT NULL , CONSTRAINT pkeyteammem PRIMARY KEY (TeamID, PlayerID) ); Save the database file as YourStudentId-Create.accdb (format xxxxxxx-Create.accdb) for example 2225991-Create.accdb. ONLY ITECH 5006 Students need to create the 3 tables: MatchStatistics, MatchSchedule and Grounds. Total Marks for this section: [5 marks] University of Ballarat – Graduate School of Information Technology and Mathematical Sciences ITECH 1006/5006 Database Management Systems Teaching Period 00, 2011 – Weighting 20% Assignment 2: Creating and querying a database 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 12 and under, 14 and under and 16 and under. 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. Umpires are given a level of accreditation (Umpire Level) that enables them to umpire games to a given age level. In this database, you can assume that all umpires have ‘junior’ level accreditation that will allow them to umpire any junior match. Club Hindmarsh has teams A with teamID 1 and B with teamID 2 in grade Gold of 12 and under age group. Club Strathmore has teams A in RED division of 14 and under with teamID 3 and team B in 14 and under Gold with teamID 4. Each team has 11 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 2010. In other words, a player in under 12 will have a date of birth after 31-12- 98. 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. too old). Players can only play in one team for the season. In the GROUNDS table, insert some grounds with status ‘Poor’, and some with status ‘Good’, some with status ‘Fair’. 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. Total marks for this section: [5 marks] University of Ballarat – Graduate School of Information Technology and Mathematical Sciences ITECH 1006/5006 Database Management Systems Teaching Period 00, 2011 – Weighting 20% Assignment 2: Creating and querying a database The Design: E-R Diagram showing cardinality and participation between relationships Staff Registration No. Name Mob. Phone no. Email Address Official RegistrationNo Password Umpire RegistrationNo UmpireLevel Player PlayerID Name DateOfBirth ClubID
CoachID Name PhoneNo Address Email
Club ClubID Address ClubName ClubManagerRegistrationID Team TeamID ClubID TeamGrade TeamAge CoachID TeamMembership TeamID PlayerID Grade Levels TeamGrade Age Levels TeamAge Competition Series TeamGrade TeamAge Enrolled Teams TeamGrade TeamAge TeamID Grounds GroundNo Address ContactPersonRegID Status Match Schedule MatchID StartDate StartTime GroundNo Team1_ID Team2_ID UmpireID Match Statistics MatchID WinningTeam Team1_Tot Runs Team2_Tot Runs OversPlayed PlayerIDofMatch University of Ballarat – Graduate School of Information Technology and Mathematical Sciences ITECH 1006/5006 Database Management Systems Teaching Period 00, 2011 – Weighting 20% Assignment 2: Creating and querying a database 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 series? [1 mark] List the clubs that have teams in the under 14 series. [1 mark] List the Club Name and TeamID for all teams in the under 12 RED series. [1 mark] 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 currently, 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 current age – TeamAge. [2 marks] ONLY ITECH 5006 Students are to write SQL for these additional queries: How many grounds are unavailable due to poor status? [0.5 mark] List all players (Name and ID) who have been given player of the match during the season. Use a sub-select query to solve this query. [1.5 marks] Also list the CLUB for each of the players who have been awarded player of the match. Order the results so that players for each club appear in the list together (HINT: use ORDER BY) [1.5 marks] List, for each series, the average number of runs scored in a match. [1.5 marks] 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. Total marks for this section: [ITECH 1006 Students: 5 marks] [ITECH 5006 Students: 10 marks] Stage 4. Documentation Compile a word document named YourStudentId-Documentation.docx/pdf that includes the following: 1. Assumptions you have made in the implementation of tables in the database 2. Your SQL “CREATE TABLE… “ statements 3. Your SQL “INSERT into TABLE … “ statements 4. Your SQL “SELECT from TABLE …. “ queries 5. 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). 6. Your test results: A sample of the output expected for each query and the output actually generated by your query. Total Marks for this section: [5 marks] University of Ballarat – Graduate School of Information Technology and Mathematical Sciences ITECH 1006/5006 Database Management Systems Teaching Period 00, 2011 – Weighting 20% Assignment 2: Creating and querying a database Submission Submit your database and documentation files using Moodle according to the instructions provided by your Lecturer/Tutor. Assessment Assessment will be based on the correctness and completeness of your work. You must thoroughly test your SQL to ensure it works correctly. You must ensure that your SQL statements and queries are saved in .accdb files so that they can be re-run when marking.