Scenario:  

You have been contracted to create a database to store information for a company that provide replica dinosaur skeletons for museum exhibits. 

Along with the bones, they also provide kiosks that provide information about the various periods of pre-history, along with the dinosaurs on display. Your database will be used to populate the information on these kiosks. 

Thus far sub-sets of the whole collection have been used for 5 exhibitions. These may be repeated in future, though the database needs to be structured in such a way to allow for future exhibitions to be added easily. 

Make sure to use your university allocated database account to answer the questions.  

Provide screenshots in the report evidencing each step of your work, as well as text-based copies of any SQL. 

Task 1  

The data for the collection can be found as spreadsheet in the Assessment tab within the ilearn module, structured as seen here: 

Logical Model 

Based on the scenario and the data provided, create a logical model for the database using a crow’s foot style (e.g. Barker/UML, do NOT use the Chen diagramming style). 

Make sure to normalise the data to 3nf, adding keys and identifier columns as appropriate. Make sure to note any assumptions made. 

Physical Model 

Using your logical model as a basis, create a physical ERD using crow’s foot notation for either a MySQL or Oracle database. Make sure to use appropriate naming conventions and to identify data types, keys and constraints. Note any assumptions made or further changes to the structure beyond those in the logical model.   

Implementation 

From the ERD constructed above, create the database using appropriate SQL in 

Oracle APEX. Add at least 10 lines of sample data from the provided information. 

Sample SQL Queries 

Design and run 3 SQL test queries of your choice to verify that your database functions and meets the needs of the scenario. 

   (2100 words)  

(70 marks)  

(LOs: 2 & 3)  

Task 2 

Provide solutions for the following problems in the form of SQL code and a brief explanation of your reasoning (approx. 200-300 words each). Apply your code to a copy of the sample database and include screenshots evidencing this in each case: 

Extra column 

Add a column to a relevant table to store the value (in millions of years) that a type of dinosaur existed. i.e. The difference between the “period start” and “period end” values. 

Data derivation 

Write SQL statement(s) to calculate the values and populate this new column with the relevant values, using the data already available in the table. (Do not simply do the calculations manually). 

Cretaceous Creatures 

The Cretaceous Creatures exhibit has been rebooked for a different museum. Write a query that will return the name, species and country of origin of all relevant dinosaurs listed in your database.  

Great Grazers 

The collection is due to be used for a new exhibition that will include all herbivorous dinosaurs. This new exhibition will be called “Great Grazers”. Write commands to add the exhibition to your database and link all relevant dinosaur records. 

End of questions