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.

Google Online Preview   Download