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