RAFT 4 Schema Creation Script
grant create view to student
select * from cat;
select 'drop table ' || table_name || ';' from cat where table_type='TABLE';
purge recyclebin;
alter session set nls_date_format = 'YYYY-MM-DD';
drop view clients;
drop view counselors;
drop view employees;
drop table scheduledFor;
drop table appointment;
drop table counselor;
drop table client;
drop table employee;
drop table room;
drop table person;
create table Person(
personID int GENERATED ALWAYS as IDENTITY(START with 100) primary key ,
FirstName varchar(50),
LastName varchar(50),
Address varchar(50),
City varchar(50),
State char(2),
ZIP char(10),
Phone varchar(14),
Email varchar(100),
birth_date date
);
create table Client(
ClientID int primary key references Person,
FoundOut varchar(50),
ReminderMethod varchar(50),
ReferredByClientID int references client
);
create table Employee(
EmployeeID int references Person primary key ,
DateHired date,
Wage number(4,2)
);
create table Counselor(
CounselorID int references Person primary key,
DegreeSuffix varchar(50)
);
create table Room(
RoomNumber int primary key,
RoomName varchar(50)
);
create table Appointment(
AppointmentID int GENERATED ALWAYS as IDENTITY(START with 1000) primary key,
AppointmentTime date,
Duration int,
CounselorID int references counselor not null,
EmployeeID int references employee not null,
RoomNumber int references room not null,
Notes varchar(3000)
);
create table ScheduledFor(
AppointmentID int references appointment not null,
ClientID int references client not null,
Assignment varchar(2000),
kept number(1,0),
primary key (appointmentid, clientid)
);
/* Create Indexes for foreign keys */
create index counselorAppointments on appointment(counselorID);
create index roomAppointments on appointment(roomNumber);
create index clientAppointments on scheduledFor(clientID);
create index clientReferreBy on client(referredByClientID);
/*create indexes for common sorting*/
create index person_lastname_first on person(lastname, firstname);
create index appointment_time on appointment(appointmentTime);
/*create views for sub classes*/
create view Clients as
select ClientID, Person.FirstName, Person.LastName, Person.Address, Person.City, Person.State,
Person.ZIP, Person.Phone, Person.Email, FoundOut, ReminderMethod,
referrer.FirstName || ' ' || referrer.LastName as ReferredBy
from client join person on clientid=personid
left outer join person referrer on referredByClientID = referrer.personid;
create view Counselors as
select CounselorID, FirstName, LastName, Address, City, State, ZIP, Phone, Email, DegreeSuffix from counselor
join person on counselorid=personid;
create view Employees as
select EmployeeID, Person.FirstName, Person.LastName, Person.Address, Person.City, Person.State,
Person.ZIP, Person.Phone, Person.Email, DateHired, Wage
from employee
join person on employeeid=personid;
/*data for Counselors*/
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Quinn','Fowler','5208 Walmsley Blvd','Richmond','VA','23224','(804) 286-4422','qfowler@raftcounseling.com');
insert into counselor values((select max(personid) from person), 'MS');
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Elizabeth','Baker','3409 Irvington St','Richmond','VA','23234','(804) 539-9337','ebaker@raftcounseling.com');
insert into counselor values((select max(personid) from person), 'PhD');
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Darold','Cooper','4003 Corbin St','Richmond','VA','23222','(804) 286-4422','dbcooper@raftcounseling.com');
insert into counselor values((select max(personid) from person), 'MFT');
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Diana','Chandler','600 Idlewood Ave','Richmond','VA','23220','(804) 737-9704','dchandler@raftcounseling.com');
insert into counselor values((select max(personid) from person), 'EdD');
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Phoebe','Wright','707 Old Locke Lane','Richmond','VA','23226','(804) 592-0056','pwright@raftcounseling.com');
insert into counselor values((select max(personid) from person), 'MFT');
/*data for Clients*/
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Bernice','Mason','3513 A Grove Ave','Richmond','VA','23221','(804) 612-2961','masonjar27@msn.com');
insert into Client values((select max(personid) from person), 'Radio','Phone', null);
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Leola','White','1625 W Laburnum Ave','Richmond','VA','23227','(804) 786-0698','thewhitehouse@yahoo.com');
insert into Client values((select max(personid) from person), 'Billboard','Text', null);
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Janessa','McSweeney','5717 Campbell Ave','Richmond','VA','23231','(804) 518-7098','Janessa1820@gmail.com');
insert into Client values((select max(personid) from person), 'Family','Text',
(select personid from person where firstname='Leola')
);
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Shannon','Fake','3019 E Marshall St','Richmond','VA','23223','(804) 223-8622','therealshannon@gmail.com');
insert into Client values((select max(personid) from person), 'Billboard','Email', null);
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Brothers','Yasini','205 Wickham St','Richmond','VA','23222','(804) 966-8112','brothersk@me.com');
insert into Client values((select max(personid) from person), 'Family','Text',
(select personid from person where firstname='Shannon')
);
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Wendi','Overton','915 N 33rd St','Richmond','VA','23223','(804) 649-6464','wendycity99@gmail.com');
insert into Client values((select max(personid) from person), 'Family','Text',
(select personid from person where firstname='Shannon')
);
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Bessie','Murchison','2918 Edgewood Ave','Richmond','VA','23222','(804) 620-9185','bmurch77@yahoo.com');
insert into Client values((select max(personid) from person), Null,'Postcard',
(select personid from person where firstname='Shannon')
);
/*data for Employees*/
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Holly','White','1414 Rogers St','Richmond','VA','23223','(804) 640-6817','hollyw@raftcounseling.com');
insert into Employee values((select max(personid) from person), TO_DATE('2013-07-01', 'yyyy-MM-dd'),'17.50');
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Mark','Lucas','1203 1/2 Oakwood Ave','Richmond','VA','23223','(804) 327-2454','markl@raftcounseling.com');
insert into employee values((select max(personid) from person), TO_DATE('2013-09-01', 'yyyy-MM-dd'),'15.00');
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('John','Taylor','913 N 3rd St','Richmond','VA','23219','(804) 331-2700','johnt@raftcounseling.com');
insert into employee values((select max(personid) from person), TO_DATE('2020-08-01', 'yyyy-MM-dd'),'15.00');
insert into Person(firstname, lastname, address, city, state, zip, phone, email)
values('Molly','Helgeson','900 Maggie Walker Ave','Richmond','VA','23222','(804) 888-0759','mollyh@raftcounseling.com');
insert into employee values((select max(personid) from person), TO_DATE('2013-06-01', 'yyyy-MM-dd'),'16.80');
/*data for Rooms*/
insert into room(roomnumber, roomName) values(1,'Clear sky');
insert into room(roomnumber, roomName) values(2,'Flowing waters');
insert into room(roomnumber, roomName) values(3,'Calm ocean');
insert into room(roomnumber, roomName) values(4,'Peaceful woods');
/*data for Appointments*/
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2020-10-01 8:00:00', 'yyyy-MM-dd hh:mi:ss'),90,
(select counselorid from counselors where firstname='Elizabeth'),
(select employeeid from employees where firstname='Holly'),
(select roomnumber from room where roomname='Clear sky')
);
insert into scheduledfor
values(
(select max(appointmentid) from appointment),
(select clientid from clients where firstname='Brothers'),
null,
1
);
insert into scheduledfor
values(
(select max(appointmentid) from appointment),
(select clientid from clients where firstname='Shannon'),
null,
1
);
insert into scheduledfor
values(
(select max(appointmentid) from appointment),
(select clientid from clients where firstname='Wendi'),
null,
1
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2020-10-01 11:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select counselorid from counselors where firstname='Elizabeth'),
(select employeeid from employees where firstname='Holly'),
(select roomnumber from room where roomname='Clear sky')
);
insert into scheduledfor
values(
(select max(appointmentid) from appointment),
(select clientid from clients where firstname='Bessie'),
null,
0
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2020-10-01 10:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select counselorid from counselors where firstname='Quinn'),
(select employeeid from employees where firstname='John'),
(select roomnumber from room where roomname='Flowing waters')
);
insert into scheduledfor
values(
(select max(appointmentid) from appointment),
(select clientid from clients where firstname='Leola'),
null,
0
);
insert into scheduledfor
values(
(select max(appointmentid) from appointment),
(select clientid from clients where firstname='Janessa'),
null,
1
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2020-10-01 10:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select counselorid from counselors where firstname='Elizabeth'),
(select employeeid from employees where firstname='Holly'),
(select roomnumber from room where roomname='Calm ocean')
);
insert into scheduledfor
values(
(select max(appointmentid) from appointment),
(select clientid from clients where firstname='Shannon'),
null,
1
);
insert into scheduledfor
values(
(select max(appointmentid) from appointment),
(select clientid from clients where firstname='Brothers'),
null,
1
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2020-10-01 10:00:00', 'yyyy-MM-dd hh:mi:ss'),90,
(select counselorid from counselors where firstname='Darold'),
(select employeeid from employees where firstname='Holly'),
(select roomnumber from room where roomname='Peaceful woods')
);
insert into scheduledfor
values(
(select max(appointmentid) from appointment),
(select clientid from clients where firstname='Bernice'),
null,
1
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2020-10-08 10:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select counselorid from counselors where firstname='Elizabeth'),
(select employeeid from employees where firstname='Holly'),
(select roomnumber from room where roomname='Calm ocean')
);
insert into scheduledfor
values(
(select max(appointmentid) from appointment),
(select clientid from clients where firstname='Shannon'),
null,
1
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2020-10-08 10:00:00', 'yyyy-MM-dd hh:mi:ss'),60,
(select counselorid from counselors where firstname='Quinn'),
(select employeeid from employees where firstname='Holly'),
(select roomnumber from room where roomname='Flowing waters')
);
insert into scheduledfor
values(
(select max(appointmentid) from appointment),
(select clientid from clients where firstname='Leola'),
null,
1
);
insert into appointment(AppointmentTime,duration, counselorid, employeeid, roomnumber)
values(TO_DATE('2020-10-08 10:00:00', 'yyyy-MM-dd hh:mi:ss'),90,
(select counselorid from counselors where firstname='Darold'),
(select employeeid from employees where firstname='Holly'),
(select roomnumber from room where roomname='Peaceful woods')
);
insert into scheduledfor
values(
(select max(appointmentid) from appointment),
(select clientid from clients where firstname='Bernice'),
null,
0
);
/* show data */
select * from clients;
select * from employees;
select * from counselors;
select * from appointment;
Labels: hide