ICT285 Databases TJA 2018 Assignment 2

ICT285 Databases
TJA 2018
Assignment 2

Worth: 20% of your final grade.
Due: Sunday, 8 April 2018, 11:55 PM
AssignmentTutorOnline

Submit to: LMS, via the Assignments tool. Submit Parts 1 and 2, Part 3 sample data and
Part 4 CREATE VIEW statements as a SINGLE Word document. Parts 3 and 4
should be completed in Oracle on arion.
Ensure you complete the declaration that is part of the submission process.
Complete the process fully and do not leave your submission in draft. You do
not need to include a separate cover sheet but you should include your name
and student number as part of your document filename. Your name and
student number should also be included within in the assignment document.
Late assignments that do not have an extension will be penalised at the rate of 5% per day.
For example, if your original mark was 75% and your assignment was 2 days
late, your final mark will be 65%.
This is an
INDIVIDUAL assignment.
This assignment requires you to implement the database you designed to address the
requirements of the Pet Net case study in Assignment 1. You will need to incorporate any
changes you may need to make to your design as a result of the feedback on Assignment 1
both individually and as a class (e.g. on the online forum), and to address the
additional/amended requirements listed in this document.
The assignment addresses the following learning outcomes for the unit:
3. Demonstrate practical skills in using SQL
5. Demonstrate practical skills in normalisation and convert a conceptual database
design to a logical design in 3NF
6. Create a database from a given design using a DBMS and implement specified
constraints using appropriate tools and approaches
7. Explain and implement security as it applies in the database environment.
Marks are distributed as follows:

Part 1: Revised ERD and schema 10
Part 2: Data dictionary 20
Part 3: Implementation 30
Part 4: Views 40
Total 100

Case study
Re-read the description of the Pet Net case in Assignment 1 if you need to refresh your memory.
Elizabeth and Jeremy Trellis are pleased with your work so far and asked you to go on to implement
your design. They have some
additions to the original specifications that you should note:
They would like to extend the services that participants can offer to include dog walking, dog
grooming and dog training. These services would operate in the same way as the boarding,
with the services being offered and bookings made between owner and service provider.
Potential dog walkers would state whether they are available for regular walks or one-off (or
both), and whether they are available for long (>5km) walks. Potential dog groomers would
state what sort of cuts they can provide. Dog trainers would describe whether they offer
basic, intermediate or advanced training. In each case the dog walker/groomer/trainer
states the price(s) for the service(s) they offer. Of course, a person can be any or all of pet
sitter, dog walker, dog groomer or dog trainer.
Part 1: Revised ERD and schema (10 marks)
a) Create and submit the ERD for this database that you are going to use as the basis of your
implementation.
b) Include a one or two paragraph explanation as to the changes you have made to the ERD on
the basis of your feedback from Assignment 1 and/or as a result of having to support the
new functionality and views described in this assignment.
c) Show the relational schema in 3NF that will be the basis of your implemented design. Show
your relations using the following convention:
RELATION_NAME (PrimaryKey, Attribute, Attribute, …
ForeignKey)
Part 2: Data dictionary (20 marks)
Create a data dictionary for your database. This should include:
a) For each table: a definition of each column (attribute), consisting of the column name, brief
description of what it represents, its data type and size, domain (allowable values), any
default value, whether it is required, whether unique, and any constraints (primary key,
foreign key). You can follow the example in Lab 07. Use the data types available in Oracle.
b) For each of the columns that is a foreign key, give the appropriate referential integrity rules
(i.e. the “on delete…. ; on update“ etc actions that should apply when the corresponding
primary key is altered). The appropriate action should be included whether or not there is a
statement in Oracle to implement it.
c) Any business rules (enterprise constraints) that should apply to the database that haven’t
already been covered.
Note that your data dictionary must be consistent with your ERD and schema.
Part 3: Implementation (30 marks)
Implement the tables for the Pet Net database in Oracle SQLPlus on arion.murdoch.edu.au. Note the
following:
a) All
tables should be created as per your ERD and data dictionary; the marker will check your
ERD against your tables. You do NOT need to include the SQL CREATE TABLE statements
that you used to create the tables.
b) All
entity and referential integrity constraints should be created and appropriately named.
c) All columns (attributes) should be of an appropriate
data type/size and be set as required,
unique
or not as appropriate.
d) All
domain constraints should be implemented.
e) All tables should be populated with
sample data that will allow the marker to test that your
database fulfils the application requirements as specified and supports the transactions and
views listed below.
Also provide the same sample data in your Word document. If you use
a screen dump, it MUST be a size that is readable without zooming.
Note you do not need to include the SQL INSERT statements that you used to add the data.
f) SELECT, UPDATE, INSERT and DELETE permissions should be GRANTED on all database
objects (particularly tables and views) to the user
MARKERTL. This is most important. If you
do not grant this permission, the marker will not be able to mark this part of your
assignment and you will not get any marks for it.
g) Please state in your documentation whether you have used your V account or H account.
Part 4: Views (40 marks)
Create VIEWS for the following in Oracle (views should be named as ViewA, ViewB etc). Note that
some of these have changed from Assignment 1 in line with WHZ’s new requirements.
You should
also provide the CREATE VIEW statements you used to create the views in your Word
document. Include sufficient attributes in your solutions that they make sense without reference to
the question.
VIEW A All the pet sitters who can look after large dogs in the dog’s own home.
VIEW B Pet sitters who are available for cats during March 2018.
VIEW C All sitters whose average service has been rated as four stars or over.
VIEW D The number of pet sitters registered in the database for each type of animal.
VIEW E The average price charged by pet sitters.
VIEW F Details of all of the bookings for a particular owner and their pet(s), in descending
order of date (oldest first)
VIEW G All of the dog walkers in a particular suburb, and the services they offer.
VIEW H Details of all of the services offered for large dogs in a particular suburb.
VIEW I All comments about a particular sitter.
VIEW J Total amount spent on each dog service (boarding, grooming, training and walking)
in 2018 (so far)
NOTES:
Note that where the requirement is for ‘a particular’ owner/suburb/etc, you can assume for this
assignment the view definition includes a specific value such as “Bridget Jones” or “Murdoch”
(although, obviously, it should work for all relevant values).
Formatting:
You can use TO_CHAR to format your output: (see e.g.
https://www.techonthenet.com/oracle/functions/to_char.php )
Dates and times in Oracle:
https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm
http://www.akadia.com/services/ora_date_time.html
http://psoug.org/reference/date_func.html

Please note the following about the marking of this assignment:
The marker will view your documentation and then match your documentation to your
implementation. This means for example, that tables, columns and constraints should be named
in your database as they are in your documentation. Relationships defined in your ERD should be
defined in your database using foreign keys.
The marker will view the sample data in your tables.
The marker will execute each of the views created for Part 4 above.
AGAIN, please ensure that you GRANT the appropriate privileges on all relevant objects (tables
and views) to the user
MARKERTL. If you do not do this, the marker will not be able to mark part
of your assignment (and you may be awarded 0 for this section).