Ask DBMS Expert


Home >> DBMS

Implement the database you designed to address the requirements of the Western Highlands Zoo case study

The assignment addresses the following learning outcomes for the unit:
1. Demonstrate practical skills in using SQL
2. Demonstrate practical skills in normalisation and convert a conceptual database design to a logical design in 3NF
3. Create a database from a given design using a DBMS and implement specified constraints using appropriate tools and approaches
4. Explain and implement security as it applies in the database environment.

Case study

Re-read the description of the Western Highlands Zoo case in Assignment 1 if you need to refresh your memory.

The WHZ Director is pleased with your work so far and asked you to go on to implement your design. They have an addition to the original specifications that you should note:
- The WHZ want to provide a number of visitor ‘experiences', consisting of guided tours and close encounters with the animals. They are modelling these somewhat on those at Perth Zoo

- The experiences happen on various days and times, but each one is scheduled regularly (for example, the Penguin tour is always Tuesdays and Thursdays at 10.00am). All the experiences are scheduled for a month ahead (i.e. your data will show the schedule for October and November 2017).

- The following information is to be stored about each experience: Name, brief description, date, time, and duration. In addition, the ‘close encounters' experience also stores minimum age, maximum group size, cost, and any restrictions on who can participate.

- Each of the experiences has one or more particular animal types as its main attraction, and of course an animal type may participate in more than one experience.

- The WHZ wish the list of experiences to be searchable by potential visitors so that they can see what is on offer in the coming weeks. However, they are not planning on using it to make bookings as yet.

They also have some minor clarifications that you should note:

- Zones and habitat types are different things. A zone is a broad region of the zoo that encompasses a range of habitat types, whereas a habitat is where the animal occurs in the wild, and might be particular to only one or two species. There are around half a dozen main zones, which are marked on the zoo map that visitors can use to plan their visits.

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.

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

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

Implement the tables for the Western Highlands Zoo 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. You can use the Perth Zoo website for inspiration on sample 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

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.

VIEW A All the animal types represented at the zoo, the class they belong to, and their conservation status.
VIEW B The number of individuals of each animal type, ranked from most individuals to least. VIEW C All Australian animal types that are endangered.
VIEW D All the individual animals looked after by a particular keeper, and the enclosures they are in.
VIEW E The names of all the head keepers, and the number of keepers supervised by each of them. VIEW F All the animal types represented in a particular zone.
VIEW G All the keepers who work in a particular zone.
VIEW H The history notes about Sophie the giraffe for 2016.
VIEW I All the experiences for a particular day, in time order (starting in the morning)
VIEW J All the guided tours that involve elephants for the week beginning 1 November 2017.

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M92503277
  • Price:- $90

Guranteed 48 Hours Delivery, In Price:- $90

Have any Question?


Related Questions in DBMS

Data mining assignment -in this assignment you are asked to

Data Mining Assignment - In this assignment you are asked to explore the use of neural networks for classification and numeric prediction. You are also asked to carry out a data mining investigation on a real-world data ...

Sql query assignment -for this assignment you are to write

SQL Query Assignment - For this assignment you are to write your answers in a word document. This assignment is in three parts: Part A (reporting queries), Part B (query performance), Part C (query design). For this assi ...

The groceries datasetimagine 10000 receipts sitting on your

The groceries Dataset Imagine 10000 receipts sitting on your table. Each receipt represents a transaction with items that were purchased. The receipt is a representation of stuff that went into a customer's basket. That ...

You are in a real estate business renting apartments to

You are in a real estate business renting apartments to customers. Your job is to define an appropriate schema using SQL DDL in MySQL. The relations are Property(Id, Address, NumberOfUnits), Unit(ApartmentNumber, Propert ...

Objectivethe objective of this lab is to be familiar with a

OBJECTIVE: The objective of this lab is to be familiar with a process in big data modeling. You're required to produce three big data models using the MS PowerPoint software. This tool is available on UMUC Virtual Deskto ...

The relation memberstudentid organizationid roleid stores

The relation Member(StudentId, OrganizationId, RoleId) stores the membership information of student joining organization. For example, ('S1', 'O2', 'R3') indicates that student with Id 'S1' joined the organization with i ...

Relational database exerciseyou have been assigned to a new

Relational Database Exercise: You have been assigned to a new development team. A client is requesting a relational database system to manage their present store with the anticipation of adding more stores in the future. ...

Relational database design a given the following business

Relational Database Design A) Given the following business rules, identify entity types, attributes (at least two attributes for each entity, including the primary key) and relationships, and then draw an Entity-Relation ...

We can represent a data set as a collection of object nodes

We can represent a data set as a collection of object nodes and a collection of attribute nodes, where there is a link between each object and each attribute, and where the weight of that link is the value of the object ...

Data model development and implementationpurpose of the

Data model development and implementation Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop data models and map Database System into a standard development environment to gain unde ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As