ISYS5000 Designing Database Systems
Group Assignment – 2018 Semester 2
This is a group assignment and you are required to be in a group of 3. A group of 4 is not allowed. You must
form. groups from within your lab session.
Objectives:
1. To apply what you have learnt in this unit by designing and implementing a database and providing
some functionality in the form. of SQL scripts (as part of application design) to support the business
processing requirements specified in this assignment. The assignment is a good learning tool, as it will
take you through the steps required to design and implement a database.
2. To gain experience working in a team as it would be in a real life project.
Learning Outcomes:
1. Demonstrate the ability to normalise given data to a suitable normal form.
2. Design and construct a secure database
3. Create a relational database schema based on a conceptual model and any other data storage
requirements otherwise identified.
4. Use SQL to construct, secure and access the database
_____________________________________________________________________________
Case Study
Driver Training School was established in 2009. Since then, the School has grown steadily and now has an
office in a number of suburbs in the city. Each office has several driving instructors and the office is managed
by one of the driving instructors. The School has a number of vehicles and each driving instructor is provided
a particular vehicle.
To join the school, client must first register at one of the school offices. Each client must complete an
application form. that records their personal details. Upon a successful registration, a client is assigned a
driving instructor whom he or she usually takes lessons from. A lesson lasts for one hour between 8am and
5pm Monday to Saturday. After each lesson, the driving instructor records the progress made by the client
and notes the distance travelled during the lesson.
Once a client has mastered to drive, the driving instructor recommends that the client apply for a driving
test. If a client fails the driving test, the instructor records the reason for the failure.
The following ER Model shows entities of interest to the firm. Also, provided is a description of the entities
and relationships.
Your team’s task is to design and implement a database to:
1) Store the information specified in the ER model
2) Support the business processing requirements specified in this document.
TEST ENTITY
Attribute Name Attribute Type
I, C, D, M
Description Example Data Type
testID I ID of test 103 Char
testDate Date of test 14-Aug-2018 Date
testResult Result of test Fail Char
reasonFailed Reason client failed test Did not stop at
the stop sign
Char
Business Processing Requirements
As part of application design, write SQL scripts to provide the functionality required to fulfil the following
business processing requirements.
Use SELECT statements for the reporting scripts and DML statements for the data maintenance scripts.
Reporting Scripts
Create as many records (test data) as required to test the reporting scripts. Populate your tables with your
own test data using the INSERT statement. I would recommend that you save the INSERT statements for
your test data in scripts in case you need to re-insert the data.
Important: Remember to sort your output where applicable
1. Write a report that lists today’s booking for instructors. Remember to sort your output.
OFFICE INSTRUCTOR NAME TIME CLIENT NAME
------ --------------- ----- -------------
Nedlands Hamish Lindal 1030 Rajon Durant
Hint: Use the Oracle function that returns the current system date
2. Display details of all the clients who have sat for test and passed. Display the instructor's name, the
client's name, the date the client registered with the office, the date of the test(s) and test result.
OFFICE SUBURB CLIENT NAME DATE REGISTERED DATE OF TEST RESULT
------------- ------------- --------------- ---------------- ------
Cannington Peter Lee 15-Jan-2018 18-Sep-2018 Pass
3. Provide a list of clients who passed their driving test after attempting the test at least once. Display the
suburb of the office the client was registered with, the client's name, the name of the client's instructor
and test result.
OFFICE SUBURB INSTRUCTOR CLIENT NAME RESULT
------------- ---------- ----------- ------
South Perth Zak Brown Choi Pak Pass
Hint: You could use a subquery to check for clients who passed a test but has also failed a test.
5 of 8
4. Provide details of client(s) who took the maximum number of lessons and is yet to pass a driving test.
Display the office suburb, instructor name, client name and the number of lessons taken.
Office Suburb Instructor Client Name No of lessons
------------- ------------- ----------- ------------
East Perth Tom John Tim Chew 14
Hint: You could use a subquery with two inner queries for this question. One inner query would get
the maximum number of lessons taken by a client and the other would get a list of clients who failed
a driving test.
5. Provide a breakdown of staff for each office. List the total number of instructors, the number of staff
by gender, instructors’ average age and office suburb.
Total No of Instructors Female Male Average Age Office Suburb
----------------------- ------ ------ ----------- -------------
60 32 28 48 East Perth
Hint: You could use the COUNT and DECODE functions to work out the number of males and females.
6. Display the number of instructors who have been employed for 4 months or more and have not had a
student yet. The information required is:
Office Suburb No of Instructors
------------- --------------
Bullcreek 3
Hint: Use a date function to work out the number of months.
7. A bonus is given to instructors whose all of their students have passed a test without failing. The
following details are required:
Instructor Office suburb Number of students never failed
---------- ------------- -------------------------------
Peter Maris East Perth 3
Hint: You could use a subquery with two inner queries for this question. One inner query would find
instructors who have students who passed and the other would find instructors who have students
that failed.
8. Provide details of clients who have not booked a test yet and clients who have booked a test but have
not sat for the test. The following details are required:
Instructor Client Date Registered Test Date
---------- ------------- ---------------- ------------
Jimmy Jay Maria Smith 12-Jan-2018 25-Sep-2018
Sanju Kumar Verma Chandra 14-Nov-2017
Hint: You might need to use the UNION command. Do a search e.g. “union command using oracle sql”
or something similar for examples of the union command.
Question
If the manager of each office wants to record which clients referred other clients to the driving school,
briefly discuss the different ways in which the requirement could be accommodated. Briefly explain, in your
opinion, which is the best alternative.
6 of 8
Data Maintenance Scripts
You are required to write the following SQL data maintenance scripts to provide some functionality to the
user.
Note: If a question requires more than one DML statement, save each DML statement in a separate script.
and execute the scripts in the proper sequence when testing. For example, if two INSERT statements are
required for a question, save each INSERT statement in a separate script. and execute the scripts in the
proper sequence to fulfil the requirements of the question.
1. A new client has registered with an office. Write scripts that will:
a) Book a lesson with the instructor who is the most junior instructor in the office in the client’s suburb.
b) Record the client's details and register the client with the office in the client’s suburb.
Note: The only data values specified in the scripts should be the details of the client and date and time
of first lesson.
Hint: You need to use subqueries in the INSERT statements to retrieve the office in the client’s suburb
and the instructor who has been longest with the School.
2. A driving instructor has been given a new vehicle because his/her old vehicle is to be returned to
manufacturer. Write scripts that will record the details of a new vehicle and assign the new vehicle to
the instructor whose vehicle will be sent to manufacturer.
Note: The only data values specified in the scripts should be the details of the new vehicle and the
vehicle that is to be returned to manufacturer.
Hint: You need to use a subquery to retrieve the instructor whose vehicle is to be sent to manufacturer.
3. An instructor has left the school. Write a script. that will re-assign the instructor's lessons to the manager
of the instructor's office.
Note: The only data values specified in the scripts should be the id of the instructor who has left the
school.
Hint: You need to use a subquery to retrieve the manager of the office of the instructor who has left the
school.
Overview of Assignment Requirements
Provide the following deliverables in your assignment document in the following order:
1. A set of tables (relations) derived from the ER model provided.
2. A physical data model of the database.
3. A set of table instance charts (TICs) and tables.
4. The SQL reporting scripts
5. The SQL data manipulation scripts.
6. Data in each table.
IMPORTANT: Refer to the document named “Assignment Format and Instructions” for detailed
instructions about the requirements of the assignment.
7 of 8
Note: Print your completed marking scheme, group assignment plan and progress reports (all signed by
your group) and give it to your lecturer in your last laboratory session.
General Instructions (Read this section carefully)
1. Questions about the assignment requirements
If there is anything about the case study or requirements that you are unsure of, ask at the lecture and/or
lab. The lecturer will act as the user. Remember that you should work interactively with the users when
designing your database and scripts. You should be using a user-centred or participatory approach.
2. Workload of team members
a) Group Assignment Plan (to be done before assignment is started)
The division of work between group members should be decided before you start the assignment.
The work should be evenly divided between group members. The amount of work involved in completing
a task is reflected roughly by the amount of marks allocated to that task.
Before you start the assignment, indicate in the Group Assignment Plan (found in the assignment folder
on Blackboard) which member(s) will complete each task. All group members must take part in deriving
the set of tables (relations) from the ER model and the creations of the TICs and tables. You will have to
split the work of creating the TICs and tables between your group members.
You must get it signed by your lecturer before you start the assignment.
b) Group Progress Reports (to be done while doing the assignment)
Your group will have to provide progress reports which have to be seen and/or signed by your lecturer. Use
the format of the Progress Report in the assignment folder on Blackboard. Each group must have a group
leader as the point of contact for the team.
Start your assignment early; do not wait to the last minute. Get help early from your lecturer if you are
having problems with the assignment.
c) Marking scheme (to be done after assignment is completed)
When you have finished your assignment, you must clearly identify in the Marking Scheme each task of the
assignment with the name(s) of the group member(s) who actually attempted the task. For the section on
TICs and table creation, you must specify the name of the group member that developed each TIC and
created the corresponding table.
Group members are rewarded for the work they do. This means that if you do not contribute equally to
the assignment and/or the parts that you attempt are of poor quality, you will get a lower mark than
your group members.
If there is a dispute about the allocation of marks you should see your lecturer as a group. Your lecturer will
make a final decision on the allocation of marks. If you are having problems within your group that you are
unable to resolve, please remember to speak to your lecturer about the problems early.
8 of 8
3. Grant access
Important: Grant the user DB210742F access (ALL) to the tables that you create for your assignment. Grant
access ONLY to your group members and DB210742F.
4. Submission of assignment document
The assignment must be presented as one word-processed document with page numbers. The contents of
the assignment document should follow the order shown in the assignment requirements section (see
assignment format and instructions document).
You use Courier New 10pt for all SQL SELECT statements and Arial 8 for the output. Use Times Roman 12pt
1.5 spacing for all text.
Submission details will be provided later.