Database Management Systems
Assignment 2: Database Maintenance Report
Introduction
[Introduce the project (who it is for and the database). Detail what has been achieved, and
what is yet to be achieved]
Contents
INTRODUCTION 1
REQUIREMENT 1 1
SQL SCRIPT. 1
RESULTS 1
COMMENTS 2
REQUIREMENT 2 3
SQL SCRIPT. 3
RESULTS 3
COMMENTS 4
REQUIREMENT 3 5
SQL SCRIPT. 5
RESULTS 5
COMMENTS 5
REQUIREMENT 4 6
SQL SCRIPT. 6
RESULTS 6
COMMENTS 6
REQUIREMENT 5 7
SQL SCRIPT. 7
RESULTS 7
COMMENTS 7
REQUIREMENT 6 8
SQL SCRIPT. 9
RESULTS 9
COMMENTS 9
REQUIREMENT 7 10
SQL SCRIPT. 10
RESULTS 10
COMMENTS 10
REQUIREMENT 8 11
SQL SCRIPT. 11
RESULTS 11
COMMENTS 11
REQUIREMENT 9 12
SQL SCRIPT. 12
RESULTS 12
COMMENTS 12
REQUIREMENT 10 13
SQL SCRIPT. 13
RESULTS 13
COMMENTS 13
Use the file RTsetup.sql (available through MyLO) to set up the database and then to display
the content of each database table created
(remembering to also run the requirement.sql file).
Requirement 2
SQL Script.
a) Select TIME, SCULLID from RaceTimes where Time=(select min(TIME) from RaceTimes);
b) Select FAMILYNAME, GIVENNAME, DATEOFBIRTH from Member where GENDER= ‘F’ order
by FAMILYNAME;
c) Select ORGNAME, COUNTRY from Club order by COUNTRY;
Select ORGNAME, COUNTRY from Club group by ORGNAME,COUNTRY order by ORGNAME;
Results
Script. output:
Write an SQL script. to determine:
a) the quickest race time, showing the time taken and scull number;
b) a list of all Female rowers, showing their name and date or birth, ordered by their
family name;
c) a list showing the country of all the Clubs and their name. Order them by their
country, and then by their Club name within each country;
d) a list of all the Clubs and the sculls they own, showing the Club and scull IDs. List
the Club even if they don’t own a scull.
a)
Write an SQL script. to display (in family name order) a list of any members who are not in a
ScullCrew.
Write an SQL script. to display a listing of all the rowers who are members of a crew,
including their given and family names, their position in the scull, the scull ID, and scull
category. Order in ascending order by the scull ID, then by the position in descending order
within each scull.
Create a sequence for the Member table and name it Member_MemberID_SEQ. This will be
used in the insert command to add auto-numbering into the primary key MemberID for all
additional rowers. Produce a script. to insert the following detail using this sequence.
Given Name: Arnold
Family Name: Chen
Gender: M
Weight: 68
Date of Birth: 1st April, 1978
Write an SQL script. to show that it has been inserted correctly into the table using the
MemberID.
Requirement 6
This requirement requires you to write:
a) a parameterised SQL script. (keyboard data entry) to record a new scull. Insert
the new scull using this script. with the following data (entered via the keyboard).
Scull ID: 888
Scull Category: Crew-4
Bow Number: 4
Club ID: 909
Write an SQL script. to show that it has been inserted correctly into the table.
b) a second parameterised SQL script. to insert a new Club. Insert the new Club
using this script. with the following data (entered via the keyboard).
Club ID: 999
ClubName: UTas Masters
Phone: 61 3 6324 9999
Address: 8 Barron Street
City: Downdale
State: Tas
PostCode: 7325
Country: Aus
NewsOutlet: The Overstater
Write an SQL script. to show that it has been inserted correctly into the table.
c) a third parameterised SQL script. to update the Scull table so that the new Club
(URow Ocean Holidays) is recorded as the owner of the new scull (888) (entered
data via the keyboard).
Write an SQL script. to show that it has been inserted correctly into the table.
Display all the details of the new Team (999) including its owner.
Note: These scripts should be easy to use by the RowTas staff.
Note: *Interactive script. uses substitution variables – data is to be entered using the keyboard