Database

30 Sep Database

ICT285 Databases Assignment 2

Worth: 20% of your final grade.

Due: Sunday 28 July 2019 11:55PM

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

This is an INDIVIDUAL assignment.

This assignment requires you to implement the database you designed to address some of the requirements of the GardenSwap case study in Assignment 1. You will need to incorporate any changes to your original design required 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 GardenSwap case study in Assignment 1 Q5 if you need to refresh your

memory. Also view any additional material that has been posted about hints and partial solutions.

Astrid Winterblossom is pleased with your work so far and has asked you to go on to implement

your design. However, like so many clients, she wants to make some changes to the requirements.

As it turns out, people were not very keen on formalising the swapping part of their activities,

although they would still like to view the various skills that other members have and are prepared to

help others with. The swaps themselves will probably continue informally, but will NOT be recorded

as part of the database at this stage.

However, the event advertising part of Astrid’s garden community has proved to be very popular,

and she would like you to develop this part as a priority, with the resultant system renamed

GardenFriends. She has a revised set of requirements based on feedback from the community.

Astrid’s revised requirements are as follows:

 As before, all people who host or attend an event must be registered on the GardenFriends

database, with the same requirements for registration information as stated previously. This

includes a list of the services/skills they are prepared to offer.

 There are three categories of events: Open Gardens, where attendees are taken round a

member’s garden where features of particular interest are pointed out; Skills and

Techniques, where attendees learn skills such as composting or chainsaw maintenance; and

Know Your Plants, which focuses on the cultivation of a particular species or group, such as

roses or Banksias.

 Every event has an organiser, as well as an event leader who presents the event on the day

(these may or may not be the same person). Some events also include additional helpers

who assist in providing 1:1 assistance to attendees. These helpers come from the pool of

registered members and each helper may volunteer to assist in many different events.

 The event information required is similar to before (i.e. title, description, date, location, and

number of places available), but additional information has also proved useful and is to be

included in the final system. In particular, Astrid would like to ensure the postcode of the

location is collected, and any instructions to participants included (“bring your own trowel

and secateurs” or “don’t forget your hat and sunscreen”. She would also like to record

whether the event meets accessibility guidelines for people in wheelchairs or with limited

mobility.

 Astrid would like to open the events to the general public as well as registered

GardenFriends members, in the hope that many will be inspired to join GardenFriends.

Guests sign up on the website with their name, suburb, mobile phone number and email.

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 by cardinality or participation constraints.

Note that your data dictionary must be consistent with your ERD and schema.

Part 3: Implementation (30 marks)

Implement the tables for the GardenFriends 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 Astrid’s new requirements.

You should also provide the CREATE VIEW statements you used to create the views in your Word

document. These should be as text, not screen dumps.

A. All the members represented in the database, and details of the skills they offer, listed in alphabetical order of last name.

B. A list of all the equipment available for loan, the contact details of the member who is loaning the equipment, and any specific information about the conditions of loan.

C. A list of attendees (name, phone number, and whether they are registered members or guests) who have signed up for Fran Forklift’s workshop on ‘Chainsaw use and maintenance’.

D. All the events (title, date) that were more popular (i.e. higher attendance) with guests than with registered members.

E. All the upcoming events (title, date, location) in a given postcode (e.g. 6150) for the next month (i.e. the whole of the next calendar month).

F. Details (title, description, date, location, leader name, instructions) of all the events that mention roses.

G. Names of any members who have organised, led or helped in more events than they have attended as participants. The result table should also show the number of events.

H. All the events (title, date, leader) that meet accessibility guidelines.

I. A list of guests who attended (or are signed up to attend) events that are not in their home suburb. The result table should also show their home suburb and the event suburb.

J. The number of unfilled places in each category of event that has been held so far.

NOTES:

 Note that where the requirement names a specific literal date, suburb, gardener etc, you should ensure that your sample data demonstrates this. However, it is important that your query also works for other possible values, without changing anything apart from the literal value.

 Do not include information that is not provided in the query requirement. For example, if “the previous month” is asked for, the query should not use a particular month such as ‘November’ in the SQL.

 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

 Formatting: You can use TO_CHAR to format your output (see e.g. https://www.techonthenet.com/oracle/functions/to_char.php )

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