DBST 651 (Formerly CSMN 661) Sample Scripts:

Create Database

SPOOL MARY_LOG
/*---------------------------------------------------*/
/* DROP OBJECTS */
/*---------------------------------------------------*/

drop table NOTE;
drop table SAVING;
drop table CHECKING;
drop table CUSTOMER;
drop table ZIP;
drop view customer_view;
drop view two_table;

/*---------------------------------------------------*/
/* CREATE TABLES */
/*---------------------------------------------------*/
create table ZIP
(ZIPCODE char(5) NOT NULL,
CITY char(25),
STATE char(2),
constraint pk_zip primary key (ZIPCODE)
)
;

create table CUSTOMER
(ACCOUNT_NUMBER number(10) NOT NULL,
SSN number(9) NOT NULL,
FIRST_NAME char(10),
MIDDLE_INT char(1),
LAST_NAME char(20) NOT NULL,
FIRST_LINE_AD char(20),
CHECKING_TOTAL number(5),
SAVINGS_TOTAL number(5),
FK_ZIPCODE char(5),
constraint pk_customer primary key (ACCOUNT_NUMBER),
constraint fk_zip foreign key (FK_ZIPCODE) references ZIP
)
;

create table CHECKING
(CH_ACCOUNT_NUM number(10) NOT NULL,
ACCOUNT_NUMBER number(10),
LAST_TRANSACTION number(10) NOT NULL,
CHECKING_CHANGE number(9),
Constraint pk_checking primary key (CH_ACCOUNT_NUM, LAST_TRANSACTION),
Constraint fk_customer1 foreign key (ACCOUNT_NUMBER) references CUSTOMER
)
;

create table SAVING
(SA_ACCOUNT_NUM number(10) NOT NULL,
ACCOUNT_NUMBER number(10),
LAST_TRANSACTION number(10) NOT NULL,
SAVING_CHANGE number(9),
constraint pk_saving primary key (SA_ACCOUNT_NUM, LAST_TRANSACTION),
constraint fk_customer2 foreign key (ACCOUNT_NUMBER) references CUSTOMER
)
;

create table NOTE
(NOTE_ACCOUNT_NUM number(10) NOT NULL,
NOTE_DATE date NOT NULL,
ACCOUNT_NUMBER number(10),
COMMENTS char(100),
constraint pk_note primary key (NOTE_ACCOUNT_NUM, NOTE_DATE),
constraint fk_customer3 foreign key (ACCOUNT_NUMBER) references CUSTOMER
)
;

/*-------------------------------------------------*/
/* CREATE INDEXES */
/*-------------------------------------------------*/

create index fk_zip
on CUSTOMER (FK_ZIPCODE);

create index fk_customer1
on CHECKING (ACCOUNT_NUMBER);

create index fk_customer2
on SAVING (ACCOUNT_NUMBER);

create index fk_customer3
on NOTE (ACCOUNT_NUMBER);

/*---------------------------------------------*/
/* CREATE VIEWS */
/*---------------------------------------------*/

create view customer_view as
select First_Name, Middle_Int, Last_Name
from customer
where Last_Name='Smith';

create view two_table as
select c.last_name, z.city, c.checking_total,
c.savings_total
from customer c, zip z
where z.zipcode=c.fk_zipcode;

/*---------------------------------------------*/
/* CHECK CONTENT OF CATALOG */
/*---------------------------------------------*/

select substr(object_name, 1, 20), object_type, status
from user_objects
;

SPOOL OFF

Home