Reference no: EM132487948

CP5503 - Enterprise Database Systems - Oracle Assignment - James Cook University, Australia

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 rental cars and invoices of the Car Hire Me (CHM) Company.

The database should record the details of rental cars and invoices. CHM consists of several offices around Australia and customers can rent a car from one office (or location) and return it to another office (or another location).

CHM quite often launches special discounts or promotions. Each promotion has a valid code within a valid time and its value is formulated based on rental car cost, for example, 5% or 10% of the rental cost.

There are 6 different types of cars:

1. Economy, for example, Holden Spark, Manual Transmission

2. Compact, for example, Suzuki Swift, Automatic

3. Standard, for example, Toyota Corolla, Automatic

4. Full Size, for example, Toyota Camry, Automatic

5. Full Size Van, for example, Kia Carnival, Automatic

6. Full Size 4wd, for example, Mitsubishi, Automatic

Each car type should contain a number of doors, a number of seats, a number of airbags, car cost and insurance cost. In addition, each care type can also be classified as a normal or luxury car.

Beside the normal information such as license plate, rego, name, manufacturer, a number of cylinders, color, image and location, each car should also be known whether to have USB ports or Bluetooth or not. In addition, the database should also have a field which user can input some extra information.

When a car is hired, the following information should be recorded: the hired date (the date it is hired), the expected date in which the car should be returned, the actual date it is returned, the location it is returned, the promotion, and whether the car is insured.

When the payment is successfully proceeded, an invoice is issued. If the customer is less than 25 years old, a double of the insurance cost will be applied. In addition, if a car is returned more than 15' late, an additional one-day payment is applied, and an additional invoice will be issued. Invoices should have the details of invoice date, car cost, insurance cost, less-than-25 cost, total cost and GST.

There are four tasks of the assignment - Part 1:

Task 1 - You need to create a database schema called chm_jcxxxxxx to store all the database objects for this assignment. Firstly, it consists of the creation of a tablespace named chmts_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 chm_jcxxxxxx and grant appropriate privileges to the user for creating the database objects. The default tablespace of chm_jcxxxxxx must be chmts_jcxxxxxx. In addition, the user chm_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 2 - You need to use Data Modeler to model the CHM database. It should consist of a logical model, a relational model and a generated DDL script. Here is a suggestion of CHM entities.

Task 3 - You need to produce a script file named A1Task3.sql which is the modified version of the script file A1Task2.sql. A1Task3.sql should also contain additional constraints specified below. In SQL Developer, you need to connect the Oracle DB server by the user chm_jcxxxxxx and then execute the modified script to create the required tables.

Task 4 - Loading Data, Creating Sequences: You should use the file (Ass1_data.zip) provided to construct INSERT statements to load the data into the tables. In SQL Developer, you need to connect the database server as chm_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.

Attachment:- Enterprise Database Systems – Oracle Assignment File.rar