ICT285-Databases

Case study

Chester Merrythought 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, some of which have become evident as the venture continues to grow in popularity. 

Chester’s additional requirements are as follows:

Chester has decided to move the toys in the ‘sports and activities’ category, currently housed on the ground floor of her Fremantle house, to her friend Henley’s house in White Gum Valley, where there is more space. The location of a toy will continue to be recorded by room and shelf number within each house, except for some very large toys at Henley’s where only room is recorded.

The library is now too busy for Chester to manage all the loans and returns herself, so she has decided to set up a roster so that members of the library can also contribute. She has produced a list of shifts (morning, afternoon) for her opening days at each branch (Fremantle and White Gum Valley) of the library. She creates the list of shifts a month in advance (i.e. if it is currently October, the November one will be created), and members sign up for as many shifts as they want to do. 

Over time the toys have begun to suffer from wear and tear, and some are not in a fit state to be borrowed any more. Chester has moved these toys from their usual location to the attic at the Fremantle house, and flagged them as unavailable for loan. Luckily, some of the older members of the Library are willing to undertake repairs, and periodically venture into the attic and take away some toys to fix up. Once the toys are able to be borrowed again, they are returned to the shelf. The repair is recorded: the toy ID, date of repair, description of repair and the member who repaired it.

Chester feels there should be some reward for staffing the library, so has decided that any member who signs up for at least two shifts per month can borrow an additional toy at a time during that month. Chester also feels there should be some reward for repairing toys, and has decided that any member who repairs at least one toy per month can also borrow an additional toy that month. 

Some requirements have been discontinued or changed:

The themed events will remain, but it will not be possible to borrow toys just for the event, as this proved too difficult to manage (many teddy bears never returned from their picnic). Obviously, any toys that are already borrowed can be brought along to an event, but this is not recorded. 

Details of donors who are not members are no longer recorded. If a toy is donated by a nonmember this is recorded simply as ‘non-member donation’.

Part 1: Revised ERD and schema

a) Create and submit the ERD for this database that you are going to use as the basis of your implementation. Remember that all ERDs should include a title and legend.

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. Also state any assumptions that you are making.

c) Show the relational schema in 3NF that will be the basis of your implemented design. Show your relations using the following convention:

Part 2: Data dictionary

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 (the allowable values for the attribute), any default value, whether it is required, whether unique, and any primary key or foreign key constraints. 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…” referential actions that should apply when the corresponding primary key is altered). The appropriate action should be noted here 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. 

Part 3: Implementation

Implement the tables for the All You Need Are Toys Library database in Oracle SQLPlus on arion.murdoch.edu.au. Do this in the same location as usual this semester. You can use either your V or H account. 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, either using CHECK constraints or through lookup tables, depending on your design.

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.