Assignment on Database PDF

COVER PAGE

1. ERD
SQL CODE
CREATE TABLE IF NOT EXISTS `client` (
clientNumber int(11) NOT NULL,
lastName varchar(50) NOT NULL,
firstName varchar(50) NOT NULL,
address varchar(50) NOT NULL,
city varchar(50) NOT NULL,
state varchar(50) NOT NULL,
postalCode int(11) NOT NULL,
tel varchar(25) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS guide (
guideNumber int(11) NOT NULL,
lastName varchar(50) NOT NULL,
firstName varchar(50) NOT NULL,
address varchar(50) NOT NULL,
city varchar(50) NOT NULL,
state varchar(50) NOT NULL,
postalCode int(11) NOT NULL,
tel varchar(25) NOT NULL,
dateHired date NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS trip (
tripID int(11) NOT NULL,
`name` varchar(50) NOT NULL,
startingLocation varchar(50) NOT NULL,
startingState varchar(50) NOT NULL,
tripDistance int(11) NOT NULL,
MaxGroupSize int(11) NOT NULL,
`type` varchar(25) NOT NULL,
season varchar(25) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS trip_clients (
tripID int(11) NOT NULL,
clientNumber int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS trip_guides (
tripID int(11) NOT NULL,
guideNumber int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE client
ADD PRIMARY KEY (clientNumber);
ALTER TABLE guide
ADD PRIMARY KEY (guideNumber);
ALTER TABLE trip
ADD PRIMARY KEY (tripID);
ALTER TABLE trip_clients
ADD PRIMARY KEY (tripID,clientNumber), ADD KEY clientNumber
(clientNumber);
ALTER TABLE trip_guides
ADD PRIMARY KEY (tripID,guideNumber), ADD KEY guideNumber
(guideNumber);
ALTER TABLE client
MODIFY clientNumber int(11) NOT NULL
AUTO_INCREMENT,AUTO_INCREMENT=3;
ALTER TABLE guide
MODIFY guideNumber int(11) NOT NULL
AUTO_INCREMENT,AUTO_INCREMENT=3;

ALTER TABLE trip
MODIFY tripID int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;
ALTER TABLE trip_clients
ADD CONSTRAINT trip_clients_ibfk_1 FOREIGN KEY (tripID)
REFERENCES colonial.trip (tripID) ON DELETE CASCADE ON UPDATE
CASCADE,
ADD CONSTRAINT trip_clients_ibfk_2 FOREIGN KEY (clientNumber)
REFERENCES colonial.client (clientNumber) ON DELETE CASCADE ON
UPDATE CASCADE;
ALTER TABLE trip_guides
ADD CONSTRAINT trip_guides_ibfk_1 FOREIGN KEY (tripID) REFERENCES
colonial.trip (tripID) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT trip_guides_ibfk_2 FOREIGN KEY (guideNumber)
REFERENCES colonial.guide (guideNumber) ON DELETE CASCADE ON
UPDATE CASCADE;
Tables created in clude
 Table Guide
 Table Client

 Table Trip
 Table Trip_guides
 Table Trip_clients

2. Expanding the database to accept classes;
 Adding date of birth column in table client
alter table client add column dob date null;
 Addition of a table class
create table class ( classNumber integer primary key auto_increment, description v
archar(500) not null, maxPersons integer not null, fees decimal not null );
 Addition of enrollment table
create table enrollment( classNumber integer not null, clientNumber integer not nu
ll, primary key (classNumber, clientNumber), foreign key (classNumber) references
class (classNumber) on update cascade on delete cascade, foreign key(clientNumber)
references client (clientNumber) on update cascade on delete cascade );

 New Schema
 Complete SQL code
CREATE TABLE IF NOT EXISTS class (
classNumber int(11) NOT NULL,
description varchar(500) NOT NULL,
maxPersons int(11) NOT NULL,
fees decimal(10,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `client` (
clientNumber int(11) NOT NULL,
lastName varchar(50) NOT NULL,
firstName varchar(50) NOT NULL,
address varchar(50) NOT NULL,
city varchar(50) NOT NULL,
state varchar(50) NOT NULL,
postalCode int(11) NOT NULL,
tel varchar(25) NOT NULL,
dob date DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS enrollment (
classNumber int(11) NOT NULL,
clientNumber int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS guide (
guideNumber int(11) NOT NULL,
lastName varchar(50) NOT NULL,
firstName varchar(50) NOT NULL,
address varchar(50) NOT NULL,
city varchar(50) NOT NULL,
state varchar(50) NOT NULL,
postalCode int(11) NOT NULL,
tel varchar(25) NOT NULL,
dateHired date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS trip (
tripID int(11) NOT NULL,
`name` varchar(50) NOT NULL,
startingLocation varchar(50) NOT NULL,
startingState varchar(50) NOT NULL,
tripDistance int(11) NOT NULL,
MaxGroupSize int(11) NOT NULL,
`type` varchar(25) NOT NULL,
season varchar(25) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS trip_clients (
tripID int(11) NOT NULL,
clientNumber int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS trip_guides (
tripID int(11) NOT NULL,
guideNumber int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE class
ADD PRIMARY KEY (classNumber);
ALTER TABLE client
ADD PRIMARY KEY (clientNumber);
ALTER TABLE enrollment
ADD PRIMARY KEY (classNumber,clientNumber), ADD KEY
clientNumber (clientNumber);

ALTER TABLE guide
ADD PRIMARY KEY (guideNumber);
ALTER TABLE trip
ADD PRIMARY KEY (tripID);
ALTER TABLE trip_clients
ADD PRIMARY KEY (tripID,clientNumber), ADD KEY clientNumber
(clientNumber);
ALTER TABLE trip_guides
ADD PRIMARY KEY (tripID,guideNumber), ADD KEY guideNumber
(guideNumber);
ALTER TABLE class
MODIFY classNumber int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE client
MODIFY clientNumber int(11) NOT NULL
AUTO_INCREMENT,AUTO_INCREMENT=3;
ALTER TABLE guide
MODIFY guideNumber int(11) NOT NULL
AUTO_INCREMENT,AUTO_INCREMENT=3;
ALTER TABLE trip
MODIFY tripID int(11) NOT NULL
AUTO_INCREMENT,AUTO_INCREMENT=3;
ALTER TABLE enrollment
ADD CONSTRAINT enrollment_ibfk_1 FOREIGN KEY (classNumber)
REFERENCES class (classNumber) ON DELETE CASCADE ON UPDATE
CASCADE,
ADD CONSTRAINT enrollment_ibfk_2 FOREIGN KEY (clientNumber)
REFERENCES client (clientNumber) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE trip_clients
ADD CONSTRAINT trip_clients_ibfk_1 FOREIGN KEY (tripID)
REFERENCES colonial.trip (tripID) ON DELETE CASCADE ON
UPDATE CASCADE,
ADD CONSTRAINT trip_clients_ibfk_2 FOREIGN KEY
(clientNumber) REFERENCES colonial.client (clientNumber) ON
DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE trip_guides
ADD CONSTRAINT trip_guides_ibfk_1 FOREIGN KEY (tripID)
REFERENCES colonial.trip (tripID) ON DELETE CASCADE ON
UPDATE CASCADE,

ADD CONSTRAINT trip_guides_ibfk_2 FOREIGN KEY (guideNumber)
REFERENCES colonial.guide (guideNumber) ON DELETE CASCADE ON
UPDATE CASCADE;
3. Expanding database to include invoice to clients
 Addition of table invoice
create table invoice( invoiceNumber integer primary key, clientNumber integer not
null, invoiceDate date not null, totalDue decimal not null, foreign key (clientNum
ber) references client (clientNumber) on update cascade on delete cascade );
 Addition of invoice_classes to hold classes for an invoice
create table invoice_classes( invoiceNumber integer not null, classNumber integer
not null, primary key (invoiceNumber, classNumber), foreign key (invoiceNumber) re
ferences invoice (invoiceNumber) on update cascade on delete cascade, foreign key(
classNumber) references class (classNumber) on update cascade on delete cascade );
 Revised schema

 Complete revised code
CREATE TABLE IF NOT EXISTS class (
classNumber int(11) NOT NULL,
description varchar(500) NOT NULL,
maxPersons int(11) NOT NULL,
fees decimal(10,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `client` (
clientNumber int(11) NOT NULL,
lastName varchar(50) NOT NULL,
firstName varchar(50) NOT NULL,
address varchar(50) NOT NULL,
city varchar(50) NOT NULL,

state varchar(50) NOT NULL,
postalCode int(11) NOT NULL,
tel varchar(25) NOT NULL,
dob date DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS enrollment (
classNumber int(11) NOT NULL,
clientNumber int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS guide (
guideNumber int(11) NOT NULL,
lastName varchar(50) NOT NULL,
firstName varchar(50) NOT NULL,
address varchar(50) NOT NULL,
city varchar(50) NOT NULL,
state varchar(50) NOT NULL,
postalCode int(11) NOT NULL,
tel varchar(25) NOT NULL,
dateHired date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS invoice (
invoiceNumber int(11) NOT NULL,
clientNumber int(11) NOT NULL,
invoiceDate date NOT NULL,
totalDue decimal(10,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS invoice_classes (
invoiceNumber int(11) NOT NULL,
classNumber int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS trip (
tripID int(11) NOT NULL,
`name` varchar(50) NOT NULL,
startingLocation varchar(50) NOT NULL,
startingState varchar(50) NOT NULL,
tripDistance int(11) NOT NULL,
MaxGroupSize int(11) NOT NULL,
`type` varchar(25) NOT NULL,
season varchar(25) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS trip_clients (
tripID int(11) NOT NULL,

clientNumber int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS trip_guides (
tripID int(11) NOT NULL,
guideNumber int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE class
ADD PRIMARY KEY (classNumber);
ALTER TABLE client
ADD PRIMARY KEY (clientNumber);
ALTER TABLE enrollment
ADD PRIMARY KEY (classNumber,clientNumber), ADD KEY
clientNumber (clientNumber);
ALTER TABLE guide
ADD PRIMARY KEY (guideNumber);
ALTER TABLE invoice
ADD PRIMARY KEY (invoiceNumber), ADD KEY clientNumber
(clientNumber);
ALTER TABLE invoice_classes
ADD PRIMARY KEY (invoiceNumber,classNumber), ADD KEY
classNumber (classNumber);
ALTER TABLE trip
ADD PRIMARY KEY (tripID);
ALTER TABLE trip_clients
ADD PRIMARY KEY (tripID,clientNumber), ADD KEY clientNumber
(clientNumber);
ALTER TABLE trip_guides
ADD PRIMARY KEY (tripID,guideNumber), ADD KEY guideNumber
(guideNumber);
ALTER TABLE class
MODIFY classNumber int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE client
MODIFY clientNumber int(11) NOT NULL
AUTO_INCREMENT,AUTO_INCREMENT=3;
ALTER TABLE guide

MODIFY guideNumber int(11) NOT NULL
AUTO_INCREMENT,AUTO_INCREMENT=3;
ALTER TABLE trip
MODIFY tripID int(11) NOT NULL
AUTO_INCREMENT,AUTO_INCREMENT=3;
ALTER TABLE enrollment
ADD CONSTRAINT enrollment_ibfk_1 FOREIGN KEY (classNumber)
REFERENCES class (classNumber) ON DELETE CASCADE ON UPDATE
CASCADE,
ADD CONSTRAINT enrollment_ibfk_2 FOREIGN KEY (clientNumber)
REFERENCES client (clientNumber) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE invoice
ADD CONSTRAINT invoice_ibfk_1 FOREIGN KEY (clientNumber)
REFERENCES client (clientNumber) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE invoice_classes
ADD CONSTRAINT invoice_classes_ibfk_1 FOREIGN KEY
(invoiceNumber) REFERENCES invoice (invoiceNumber) ON DELETE
CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT invoice_classes_ibfk_2 FOREIGN KEY
(classNumber) REFERENCES class (classNumber) ON DELETE
CASCADE ON UPDATE CASCADE;
ALTER TABLE trip_clients
ADD CONSTRAINT trip_clients_ibfk_1 FOREIGN KEY (tripID)
REFERENCES colonial.trip (tripID) ON DELETE CASCADE ON
UPDATE CASCADE,
ADD CONSTRAINT trip_clients_ibfk_2 FOREIGN KEY
(clientNumber) REFERENCES colonial.client (clientNumber) ON
DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE trip_guides
ADD CONSTRAINT trip_guides_ibfk_1 FOREIGN KEY (tripID)
REFERENCES colonial.trip (tripID) ON DELETE CASCADE ON
UPDATE CASCADE,
ADD CONSTRAINT trip_guides_ibfk_2 FOREIGN KEY (guideNumber)
REFERENCES colonial.guide (guideNumber) ON DELETE CASCADE ON
UPDATE CASCADE;