Oracle: creating tables
Lab. #1 (Total score: 90, Due date: 9/15/05 (Thursday))
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 Oracle SQL*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 0 and 0 and < $500
OutletNo number(8) fk and not null
RentalAgreement
Attribute Type Constraint
clientNo number(8) pk
clientName varchar2(50) not null and unique
clientStreet varchar2(40) not null
clientCity varchar2(15)
clientState varchar2(15)
clientZipCode varchar2(9)
clientTelNo varchar2(20)
contactName varchar2(50) not null
contactEmail varchar2(100)
Outlet
Attribute Type Constraint
rentalNo number(8) pk
dateStart date not null
timeStart number(4,2) not null
dateReturn date
timeReturn number(4,2)
mileageBefore number(8)
mileageAfter number(8)
policyNo number(15) unique and not null
insuranceCoverType varchar(10)
insurancePremium number(5,2)
clientNo number(8) not null fk
vehLicenseNo varchar2(10) not null fk
Employee
Attribute Type Constraint
EmployeeNo number(8) pk
Title varchar2(10) not null
firstName varchar2(30) not null
lastName varchar2(30) not null
address varchar2(100)
homeTelNo varchar2(20)
ssn char(12) not null and unique
DOB date
Gender char M or F
Salary number(8,2) not null
DateStarted date not null
OutletNo number(8) fk
Vehicle
FaultReport
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- simple interest tables for loans
- mortgage rate tables monthly payments
- minecraft custom crafting tables mod
- indoor water tables for children
- 3rd grade times tables worksheets
- oracle list tables in schema
- creating tables and graphs ratios
- creating relationships between tables excel
- creating tables in excel 2016
- oracle list tables for user
- oracle list tables in database
- oracle dba tables list