ITECH 1006/5006 Database Management Systems

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

Coach
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.

We are the Best!

course-preview

275 words per page

You essay will be 275 words per page. Tell your writer how many words you need, or the pages.


12 pt Times New Roman

Unless otherwise stated, we use 12pt Arial/Times New Roman as the font for your paper.


Double line spacing

Your essay will have double spaced text. View our sample essays.


Any citation style

APA, MLA, Chicago/Turabian, Harvard, our writers are experts at formatting.


We Accept

Secure Payment
Image 3