Oracle: creating tables
Lab. #1 (Total score: 90, Due date: Feb. 7, 2006)
PerfectPets is a practice that provides private health care for domestic pets throughout America. This service is provided through various clinics located in the main cities of America. The Director of Perfect Pets is concerned that there is a lack of communication within the practice and particularly in the sharing of information and resources across the various clinics. To resolve this problem the Director has requested the creation of a centralized database system to assist in the more effective and efficient running of the practice. The relational schemas for the PerfectPets database is illustrated as follows:
In this Lab., you will implement the relational database for the database PerfectPets based on the given schemas (table specifications). Note that pk represents a primary key; fk represents a foreign key; pk1, pk2, …, pkn in a table represents a composite primary key that consists of pk1, pk2, …, pkn.
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.
← Keep a text file that contains all your commands.
Answer/perform the following questions/tasks:
1) What is the SPOOL command for? Briefly explain it and give an example. [2]
2) What is the HOST command for? Briefly explain it and give an example. [2]
3) Create all the tables based on the given schemas for PerfectPets database using Oracle SQL*Plus and display the structure of each table after you created it. Make sure you implement all the necessary integrity constraints to the database. [30]
4) Change the salary column type in Staff table to NUMBER(8,2) using the ALTER command. [2]
5) Add the domain constraint, >0 and 0 in Staff table. [2]
9) Display all the constraint names AND types for the Staff table using the SELECT command. [4]
10) List all the table names you created so far using the SELECT command. [2]
11) Load all the tables for the PerfectPets database with at least one record for each table using the INSERT command. [30]
12) Show an example of UPDATE command with any table in the PerfectPets database. [2]
13) Show an example of DELETE command with any table in the PerfectPets database. [2]
*If you finished all the above exercises, make sure you keep all the commands and the results returned from Oracle in a file (e.g., a script file) before you lose them.
14) Drop all the tables for the PerfectPets database. [6]
You need to turn in:
• The printed copy of Oracle commands and the results (or responses from Oracle) of the commands. DO NOT include the practice commands with errors. If you do, I will arbitrarily pick any answer you provided.
• Fore each question, write the question number and your answer. If you don’t put the question number for your answer, I will simply ignore it without grading it.
-----------------------
Attribute Type Constraint
treatNo char(4) pk
description varchar2(40)
cost number(5,2)
Attribute Type Constraint
staffNo char(4) pk
sFName varchar2(30) not null
sLName varchar2(30) not null
sStreet varchar2(40)
sCity varchar2(15)
sState char(2)
sZipCode varchar2(9)
sTelNo varchar2(20) not null
DOB date
gender char
ssn char(12) not null, unique
position varchar2(20) not null
salary number >0
clinicNo char(5) fk(Clinic), not null
Clinic
Attribute Type Constraint
penNo char(4) pk1, fk(Pen)
petNo char(6) pk2, fk(Pet)
dateIn date pk3
dateOut date
comments varchar2(40)
Attribute Type Constraint
examNo char(6) pk1, fk(examination)
treatNo char(4) pk2, fk(treatment)
startDate date not null
endDate date not null
quantity number(4,1)
petComments varchar2(40)
Attribute Type Constraint
penNo char(4) pk
penCapacity number default 2, >=1 and 0
Staff
Attribute Type Constraint
petNo char(6) pk
petName varchar2(30) not null
petType varchar2(20)
petDesc varchar2(40)
petDOB date
dateRegistered date
petStatus char not null
ownerNo char(5) fk(PetOwner)
clinicNo char(5) fk(Clinic)
PetOwner
Attribute Type Constraint
ownerNo char(5) pk
oFName varchar2(30) not null
oLName varchar2(30) not null
oStreet varchar2(40)
oCity varchar2(15)
oState char(2)
oZipCode varchar2(9)
oTelNo varchar2(20) not null
clinicNo char(5) fk(Clinic)
Attribute Type Constraint
examNo char(6) pk
examDate date not null
examTime number(4,2)
examResults varchar2(40) not null
petNo char(6) fk(Pet)
staffNo char(4) fk(Staff)
Attribute Type Constraint
itemNo char(6) pk1, fk(Item)
clinicNo char(5) pk2, fk(Clinic)
inStock number(6)
reorderLevel number(6)
reorderQty number(6)
Pharmacy
ItemClinicStock
PharmClinicStock
Item
PetTreatment
PetPen
Pen
Treatment
Examination
Invoice
Appointment
Attribute Type Constraint
drugNo char(3) pk1, fk(Pharmacy)
clinicNo char(5) pk2, fk(Clinic)
inStock number(6) not null
reorderLevel number(6)
reorderQty number(6)
Attribute Type Constraint
invoiceNo char(6) pk
invoiceDate date not null
datePaid date
paymentMethod varchar2(15) not null
ownerNo char(5) fk(petOwner)
examNo char(6) fk(examination)
Attribute Type Constraint
appNo char(6) pk
appDate date not null
appTime number(4,2) not null
petNo char(6) fk(pet)
................
................
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