Enterprise Database Systems Oracle Assignment

Objectives & Structures:

This part provides experience in designing a small database including both logical and physical designs. Beside the main tool SQL Developer, you also practice on Data Modeler. You are going to create a database to manage the Olympic Game information, Olympic Game Database (OGDB).

The OGDB stores data related to all Olympic Games (OGs). There are several types of OGs, including but not limited to winter, summer, special, youth and senior. For each Olympic Game (OG), the OGDB stores data related the year and the name of the country that the OG taken place. It also stores the URL of the website for an OG if there is one. Except for the first OG, a country is eligible to host an OG if it has participated in an OG before.

The OGDB stores the details of the athletes and the countries participated in the OGs. Data about each country participated in the OGs, including the name of the country, and a three-character identification code. For example, the code for Australia is AUS, India is IND and China is CHN etc. For each athlete, the OGDB stores the name, gender, date of birth and email. An athlete can have multi-nationalities.

There are many individual as well as team-based sport events of different sport categories in each OG. Sport events are classified into different sport categories. Sport events can also be classified based on the gender; an event can be a male, female or mixed event. For example, tennis men double is a team-based, male type event with event title "Double" in the "Tennis" sport category.

In each OG, an athlete can be a representative of only one country. However, an athlete can represent different countries in different OGs. For example, Jing Chen participated in three summer Olympic Games. She was representing China in the 1988 and 1996, however, she was representing Taiwan in 2000 due to her nationality changed.

OGDB contains the data of all the contestants competing in each event including those did not win any medal. A contestant can be an individual-representative or a team-representative. Each event will have at least 2 contestants (team/individual based) competing. Winners of the competition will be awarded with gold, silver or bronze medals.

For a team-based event competition, each member of the winning team will receive a medal.

In some years, the Olympic Games were cancelled due to wars. For example, the Summer Olympics of 1944 in United Kingdom, and 1940 in Japan were cancelled due to World War II. If an OG was cancelled, the OGDB will only store the year and country where the game supposed to take place. No data related to athletes and event competitions of that game were included.

The OGDB will allow searching for event results based on criteria such as whether the event is by individual or by team; whether the event is a female, male or mixed competition, the title of sport, the title of the event, etc. The database can also be used for calculating statistics such as number of medals won by each country and each athlete.

There are four tasks of the assignment -

Task 1:

You need to create a database schema called og_jcxxxxxx to store all the database objects for this assignment. Firstly, it consists of the creation of a tablespace named ogts_jcxxxxxx where jcxxxxxx is your jc username. The tablespace should have the initial size of 100 MB and can be extended if required. Secondly, you need to create a user account named og_jcxxxxxx and grant appropriate privileges to the user for creating the database objects. The default tablespace of og_jcxxxxxx must be ogts_jcxxxxxx. In addition, the user og_jcxxxxxx can also have rights to create users and allow them to connect to the database. You need to follow the principle of least privilege when granting privileges.

TASK 1 - SUBMISSION FILE(S):

Firstly, create a folder named jcxxxxxx (your jc number, for example, jc165984) to store all of your files in this assignment-Part 1.

For this task, you have to produce a script called A1Task1.sql which contains SQL statement(s) for

- creating the ogts_jcxxxxxx tablespace

- creating the og_jcxxxxxx user

- granting appropriate privileges to the og_jcxxxxxx user

And store this file into your folder jcxxxxxx.

Task 2:

You need to use Data Modeler to model the OG database. It should consist of a logical model, a relational model and a generated DDL script. Here is a suggestion of OG entities.

TASK 2 - SUBMISSION FILE(S):

Produce a model named og_jcxxxxxx and store it in the folder jcxxxxxx. Data Modeler will automatically create a folder named og_jcxxxxxx for you.

The model should consist of a logical model and a relational model.

In addition, you should also produce:

- A generated script file named A2Task2.sql; store it in the same folder jcxxxxxx

- An E-R diagram named ER_jcxxxxxx.png; store it in the same folder jcxxxxxx

Task 3:

You need to modify the script file A2Task2.sql to complete additional constraints and save it as A3Task3.sql. In addition, you need to connect the Oracle DB server as og_jcxxxxxx user and then execute A3Task3.sql to create the required tables.

TASK 3 - SUBMISSION FILE(S):

Produce a script called A1Task3.sql (a modified version of A1Task2.sql) and put it in the folder jcxxxxxx.

Task 4:

Loading Data, Creating Sequences

You have to use the supplied file Ass1_data.zip to construct INSERT statements to load the data into the tables. In SQL Developer, you need to connect the database server as og_jcxxxxxx user and then create sequence generators for tables to facilitate the auto generation of primary key values. One sequence is for one table. You will then write INSERT statements to load the data. The sequence generators must be used in the INSERT statements.

TASK 4 - SUBMISSION FILE(S):

Produce a script called A1Task4.sql which contains SQL statement(s) for

- creating the required sequence generators

- inserting data into the tables

And put it in the folder jcxxxxxx.

Attachment:- Assignment Files.rar