Develop database solution to solve a real world data storage
Course:- Database Management System
Reference No.:- EM131984931

Assignment Help
Expertsmind Rated 4.9 / 5 based on 47215 reviews.
Review Site
Assignment Help >> Database Management System

Relational Database Systems Assignment

Goal: To develop a relational database solution to solve a real world data storage and manipulation problem. This task will help to build your knowledge of relational database design and implementation techniques, and the ethical and sustainability implications of appropriate database design and implementation.

Product: A report and set of SQL files that together document the analysis of and solution to the prescribed real world situation.

Format: Further details will be available on Blackboard in the assignment specification.

Criteria - You will be assessed according to your use and application of:

  • Insightful analysis of the given problem
  • Critical reflection on the appropriateness of the design and implementation according to ethical and sustainability principles
  • Design completeness and accuracy
  • Consistency and ability to apply appropriate translation strategies through the different levels of design
  • Correctness and completeness of the SQL implementation

Background - Australia Zoo Wildlife Hospital (AZWH) is a charity organization that exists to treat and or care for sick, injured or orphaned wildlife. They are brought animals from across South East Queensland, and beyond, and are re-knowned for their specialization in both Koalas and Sea Turtles.

As a charity that operates separately from the main Australia Zoo company, the Zoo runs with very little funds. As part of an ongoing agreement between University of the Sunshine Coast and Australia Zoo Wildlife Hospital, we are re-developing their database systems.

Stage one is the Accession (admissions) system which stores information on who brought in the wildlife, where it was found, suspected injuries, initial triage and/or vet notes and what wildlife career if any the animal is assigned to for re-habilitation or care. Animals are brought for a large variety of reasons and sometimes multiple reasons, and the database is to record these and be able to query them.

The Wildlife hospital can see up to 6000-8000 admissions per year, and there is a large database of information that is maintained both for their own record keeping and for regulatory requirements set down by the State and Federal Governments. One such regulatory requirement is that all Koalas treated in Queensland are given a unique QPWS (Queensland Parks and Wildlife Service) identifier that must be maintained. A monthly report is generated to give to QPWS on these koalas and their treatment. Other wildlife such as birds can be either transferred in or out of AZWL, and as such may have more than one ID that the database needs to be able to store, recall and query.

In appendices of this document you fill find an example of a blank Australia Zoo Wildlife Hospital Accession form, a list of conditions that the animals may suffer, a partially completed form. These will form the basis of your universe of discourse. Sample data will be made available in or before in week 11 of semester (this is to have you consider your design before implementation).

User Reports - For the purposes of your assignment you are to create queries for the following user reports. The queries should be in the main .sql file but separated by a comment showing which query it is. Eg. # Query 2.a.i

You should include the query used on your database design to get that data.

1. List the patient id, accession number, animal name, and breed for all animals, sorted by animal type, that are currently being treated (where they have not been released, or sent to a carer or other facility).

2. Monthly report (this is multiple queries):

a. list the total for all in-coming accessions in the previous calendar month grouped by

i. Local government area

ii. Cause of affliction

b. List the total number of accessions for this month in the previous years.

3. List all details for Carer Groups with an expired permit.

Attachment:- Assignment Files.rar

Put your comment
View Conversion
  1. user image

    Submit your assignment to the link under Assessment-Task 2 on Blackboard. The submission link will be open a week before the due date. Please follow the submission instructions provided. The assignment will be marked out of a total of 100 marks and forms 30% of the total assessment for the course. ALL assignments will be checked for plagiarism by SafeAssign system provided by Blackboard automatically. Refer to your Course Outline or the Course Web Site for a copy of the “Student Misconduct, Plagiarism and Collusion” guidelines.

  2. user image

    Assignment submission extensions will only be made using the official Faculty of Arts & Business Guidelines. Requests for an extension to an assignment MUST be made to the course coordinator prior to the date of submission and requests made on the day of submission or after the submission date will only be considered in exceptional circumstances. Submission - The completed assignment is to be submitted to Blackboard by the due date. The assignment will be assessed according to the marking sheet. Late submission will be penalised according to the policy in the course outline. Please note Saturday and Sunday are included in the count of days late.

  3. user image

    Specific Instructions - You are not to contact the hospital directly as this takes valuable resources away from treating the wildlife. All client communication is to be directed through the Course Coordinator. You must use MySQL to develop the database. MS Access is not appropriate for any section of this assignment. You must use the ER notation that was taught in ICT701. Penalties will apply to incorrect notations.

  4. user image

    Submission Format - For Part A you are to include a word document or PDF that contains: ER Diagram, Relational Schema (including primary & foreign keys), Supplementary design requirements (e.g. any information on length of identifiers, postcodes, names, what data attributes are compulsory, structure and or format of any columns etc.) Assumptions that explain important design choices you made: for example: can a carer care for more than one animal at a time?

  5. user image

    For Part B you are to submit - A single plain text file, name studentNumber_azwh.sql. In this file you are to include all the SQL for your implementation. This includes: The License agreement as seen in Appendix E. CREATE TABLE statements including all integrity constraints, and actions on update and delete, INSERT INTO statements for populating the database (if this must happen in a particular order then make sure you order it appropriately!), SELECT statements for the required demonstration queries.

Ask Question & Get Answers from Experts
Browse some more (Database Management System) Materials
Enter the following text and make sure you press Enter at the end of each line. (Each row is considered an employee record with colons separating the fields. The first field
Table TakenCourses manages courses info that a student has taken. A course may require pre-requisite courses and the pre-requisite course info is managed in table PreRequisi
Write an organizational policy or personal policy, if not in the job force to correct the IT-related ethical issue that you described in Paper B1, where you mapped key organ
One of the major topics deals with the data collection for requirements gathering for a new proposed system. Welcome to week 3. Five characteristics that will come in handy
The use of the Internet and web-based services has the potential to integrate not only law enforcement data, but also data from other sources and to increase the exchange of
Indexing can affect the performance of a general-purpose database that has roughly equal numbers of fetches and changes. The decision to add an index can improve the performan
Create an action query that makes a table called WestCoast, with the following records. CA, WA, OR Create an action query to add the NV records to the newly created WestCoas
Water meters must be replaced when they have been in use for 5 years. We can assume that a meter will never be damaged or become unserviceable and will not be re-assigned to