Creating, Altering Tables, Loading Data COSC 3480 Lab1

Lab. #1 (Total score: 90, Due date: 9/23/02 (Tuesday))

BestRental is a car rental company that has over 3,000 employees and many outlets. The company owns variety of vehicles in different outlets. The company maintains the vehicles in best condition by checking vehicles regularly. Mechanics check each vehicle as soon as it is returned and write a fault report. The relational schemas for the BestRental database is illustrated as follows:

Note that pk represents a primary key and fk represents a foreign key. Remember that there should be only one primary key but there may be more than one foreign key in a table.

In this Lab., you will implement the relational database for BestRental based on the given schemas.

Useful Tips for the Labs. throughout this course

Prepare your program (SQL) using the Text editor such as Note Pad.

Copy and Past your program from the Text editor to SQL*Plus command line.

If the program works, save the program. Otherwise, fix the error for the program in the Text editor and try it again.

Save your text file regularly before you lose what you have typed.

You can also save the result of your commands using SPOOL command.

Answer/perform the following questions/tasks:

(a)What is the SPOOL command for? Briefly explain it and give an example. [2]

(b)What is the HOST command for? Briefly explain it and give an example. [2]

(c)Create all the tables based on the given schemas for BestRental database and display the structure of each table after you created it using OracleSQL*Plus. Make sure you implement all the necessary integrity constraints for the database. [6x5=30]

(d)Change the salary column type in Employee table to NUMBER(9,2) using the ALTER command. [2]

(e)Add the domain constraint, >0 and <10 for the Capacity column in Vehicle table using the ALTER command. [2]

(f)Change the type of “clientZipCode” in Client table to CHAR(9) using the ALTER command. [2]

(g)Add a new column, VehicleYear to Vehicle table using the ALTER command. [2]

(h)Remove the not null constraint for Title column in Employee table. [2]

(i)Display all the constraint names AND types for Outlet tables using the SELECT command. [4]

(j)List all the table names you created so far using the SELECT command. [2]

(k)Load all the tables for the BestRental database with at least one record for each table using the INSERT command. Make sure that your insertions do not violate “common sense”.[6x5=30]

(l)Show an example of UPDATE command. [2]

(m)Show an example of DELETE command. [2]

If you finished all the above exercises, make sure you keep all the commands in a file (e.g., a script file) before you lose them.

(n)Drop all the tables for the BestRental database. [6]

You need to turn in:

  • The printed copy of Oracle commands and results (e.g., responses from Oracle) of the commands. DO NOT include the practice commands with errors. If you do, we will only obtain partial credit for your solution.

1