Uni database assignment, needed to be done asap.
Assignment Part 2
In this assignment you will create a database, designed in part 1.
What to do:
1. Check your design before you start to create tables. All tables must have primary keys. Tables must have links via foreign keys.
2. Create script ‘[login to view URL]’ which will create tables with primary and foreign keys implemented. Note you have to test script for each table separately before you combine all scripts in one file. Remember it is important to create tables in correct order
3. Run script in your Oracle schema, describe all tables you created. Save your final version in spool file [login to view URL]
4. Insert sample data in your tables using script ‘[login to view URL]’.
5. Create and run the following queries for the data you have in your tables. Save queries and their results in a spool file queries.txt. Note you have to have enough data in your tables to obtain queries results
1. What is total number of medals per region?
2. What is total number of medals in swimming per country?
3. What is ratio of number of medals to population per country?
4. Who is the youngest gold medalist in your data?
5. What dates were for medal events in men’s swimming?
6. What kind of sports had events between 5 and 10 of August?
6. Create the following stored procedures and functions
1. Create a stored procedure to show all events and results for a specified athlete. Use athlete id as input parameter. Your procedure must show athlete name, age, event date, result, and award.
2. Create a stored procedure to show all events for a specified date. Use a date as input parameter. Show event date, sports kind, and sports description
3. Create a stored procedure to show all medals for a specified country. Use country name as
input parameter. Show country name, sports kind, sports description, and award (gold, silver. Or bronze)
4. Create a function to return a number of medals per region. Use region as input parameter.
Note for each stored procedure or function you have to create a script containing an anonymous block to run it.
What to submit:
1. Scripts [login to view URL] and [login to view URL] and spool file [login to view URL](4 marks)
2. Spool file [login to view URL] with queries and their results (4 marks)
3. Four scripts for procedures and function (12 marks)