Reference no: EM132382301

Database Programming

Background

Dr. Celine Frere's research group is based at USC Sippy Downs and studies wild animal populations to understand how animals evolve and to improve their conservation outcomes. Although Dr. Frere has contributed to research on a variety of species throughout her career (from giraffes to porpoises), her research group focuses on three main species: koalas on Australia's east coast, eastern water dragons in Brisbane's Central Business District, and bottlenose dolphins in Shark Bay Western Australia.

The eastern water dragon project is one of the most important projects of Dr. Frere's research group. This project was established around Brisbane's CBD in 2010. These dragons are bigger and bolder than their non-urban counterparts. They are also diversifying at rates that allow for the study of evolution in action. Dragons in Brisbane city show significant levels of genetic divergence despite the small geographic distances between parks (<5 km), and significant differences in morphology (body size, head and limb shape and forelimb and hindlimb length) depending on the park they reside in. This project has shown that, like mammals, dragons have preferred associates or "friends", social dominance hierarchies, and even share avoidances. Each dragon in the Parkland is identified by a unique scale pattern around each eye. By studying the behaviour and social networks of these lizards over their lifespans and across generations, Dr. Frere's research group is able to investigate questions about the importance of sociality to health, reproductive success and evolution.

The Problem

Dr. Frere's research group has a huge database written and maintained in Microsoft Access that they wish to port to MySQL. Their database has thousands of records that cover information about the eastern water dragons such as description, morphology and when they were contacted. Their database has also a series of views to facilitate complex queries by the numerous researchers that work within Dr. Frere's research group.

Unfortunately, their database has only 2 tables. You will be given 2 excel spreadsheets representing those tables and a subset of their data, and a data dictionary for the table fields. Your main task is to look at the spreadsheets and propose, justify and implement improvements to the database to make it more efficient.

Your assignment is in two parts: Part A: Report, Part B: Code. You are required to submit three files:

<student_Number>_<student_first_name_last_name>_ICT320_PartA.docx

<student_Number>_<student_first_name_last_name>_ICT320_PartB.sql

<student_Number>_<student_first_name_last_name>_ICT320_PartB.py

You are not to contact Dr. Frere's research group directly as this takes valuable resources away from field group. All client communication is to be directed through your lecturer/tutor or via the course coordinator Mr. Adriano da Silva Marinho.

Please note that all data is copyright and owned by Dr. Celine Frere's research group and or USC and is used with their permission for the purposes of this assignment. Further distribution of this data is not permitted.

For the data dictionary, please refer to the documents "User table and field descriptions.xlsx" and "Behavioural Surveys 2018.docx".

Submission Format and Requirements

For Part A you are to include an analysis of the current database design in a word document or PDF. You should include:
• Identification of major implementation flaws in the existing system, and/or areas for improvement, including (but not limited to) o Removing repeated data, o De- normalization for optimization o changing some areas to NoSQL (if so, include what type of NoSQL database).
o Rational for the creation of each specific constraint (Foreign Key and Unique) and/or index.
• Identification of the current Normal Form of the system.
• An ER Diagram for the current design of the system, and an ER Diagram for the re-designed system.
• Your assumptions and any supplementary design requirements regarding the system.
For Part B you are to submit
• A single plain text file, named
<student_Number>_<student_first_name>_<student_last_name>
_ICT320_PartB.sql. In this file you are to include all the SQL for your database modification. This includes:
o ALTER/CREATE TABLE statements for:
- Changes proposed in your redesign from Part A
- Commands for the missing FOREIGN KEYs, INDEXes and
UNIQUEness constraints.

o CREATE VIEWs for

- Animals vs Date vs Time vs Location: you are to create a view that lists all the animals that have been contacted by a researcher. This view should list the animal name, their contact location, the date and the time.
- Number of contacts per animal: you are to create a view

that lists the number of contacts for each animal.

o CREATE PROCEDUREs for

- Animal with the greatest number of contacts: You are to create a procedure that looks into the view described above and returns, as an output parameter, the animal with the greatest number of contacts.
- Year with the greatest number of contacts: You are to create a procedure that looks into the database and returns what was the year where the greatest number of animals was contacted.
o You need to test your database with some of the data provided to you. You are free to use any subset but make sure the subset you use accounts for any constraints you created or assumed. PAY ATTENTION TO THE DATA DICTIONARY, THERE ARE LEGACY FIELDS THAT SHOULD BE INCLUDED IN YOUR DESIGN.
• A single plain text file named
<student_Number>_<student_first_name>_<student_last_name>
_ICT320_PartB.py. In this file you are to include the Python for your programming implementation. This includes:
o Calling and outputting as a single HTML page for each of the views (i.e. a SELECT * for both views)
o In the same HTML page, calling and outputting the results of the procedures
o Format the views as tables.

Attachment:- Database Programming.rar