Systems Analysis and Design 10th Edition Rosenblatt Chapter 9 Case Studies Chapt

Question

Systems Analysis and Design 10th Edition Rosenblatt Chapter 9 Case Studies Chapter Case: Scenic Routes Task #3 page 397

Scenic Routes operates a bus company that specializes in travellling on secondary roads, rather than Interstate Highways. Their slogan is :"It Takes a Little Longer, But It's Scenic." The firm needs to update its passenger reservation system.

Background:

Data Items must include reservation number, Route Number, Date, Origin, Destinations, Departure Time, Arrival Time, Passenger Name, and Seat Number. For Example, Route 97 leaves Monroe, VA, dailyat 8:00am and arrives in Spencer, VA, 100 miles away, at 11:00am. Scenic wants to use an alphabetic reservation code, similar to the codes that airlines use.

#3 For each of the entities identified, design tables and identify the possible candidate keys, the primary key, a probable foreign key, and potential secondary keys.

Explanation / Answer

DROP SCHEMA IF EXISTS `brs` ;
CREATE SCHEMA `brs` ;
CREATE TABLE `brs`.`route` (`id` INT NOT NULL AUTO_INCREMENT,
`origin` VARCHAR(20) NOT NULL,
`destination` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`))
AUTO_INCREMENT=10;
CREATE UNIQUE INDEX ROUTE_UNIQUE ON `brs`.`route`(`origin`, `destination`);
CREATE TABLE `brs`.`bus` (
`id` INT NOT NULL AUTO_INCREMENT,
`routeid` INT NOT NULL,
`ac` boolean NOT NULL,
`fare` int NOT NULL,
`departuretime` VARCHAR(6) NOT NULL,
`ARRIVALTIME` VARCHAR(6) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`routeid`) REFERENCES `brs`.`route` (`id`))
AUTO_INCREMENT=200;
CREATE TABLE `brs`.`passenger` (
`id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(20) NOT NULL UNIQUE,
`password` VARCHAR(20) NOT NULL,
`name` VARCHAR(40) NOT NULL,
`email` VARCHAR(32) NOT NULL,
`mobile` bigint NOT NULL,
PRIMARY KEY (`id`))
AUTO_INCREMENT = 500;
CREATE TABLE `brs`.`reserve` (
`id` INT NOT NULL AUTO_INCREMENT,
`passengerid` int NOT NULL,
`busid` int NOT NULL,
`dt` DATE NOT NULL,
`tstamp` DATE NOT NULL,
`seat` int NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`passengerid`) REFERENCES `brs`.`passenger` (`id`),
FOREIGN KEY (`busid`) REFERENCES `brs`.`bus` (`id`))
AUTO_INCREMENT = 1000;
CREATE UNIQUE INDEX SEAT_UNIQUE ON `brs`.`reserve`(`busid`, `dt`, `seat`);
CREATE VIEW `brs`.`reservation` AS
SELECT
`reserve`.`id` AS `id`,
`reserve`.`passengerid` AS `passengerid`,
`reserve`.`busid` AS `busid`,
`reserve`.`seat` AS `seat`,
`reserve`.`dt` AS `dt`,
`reserve`.`tstamp` AS `tstamp`,
`route`.`origin` AS `origin`,
`route`.`destination` AS `destination`,
`bus`.`departuretime` AS `departuretime`,
`bus`.`ARRIVALTIME` AS `arrivaltime`
FROM
((`brs`.`reserve`
JOIN `brs`.`bus`)
JOIN `brs`.`route`)
WHERE
((`reserve`.`busid` = `bus`.`id`)
AND (`route`.`id` = `bus`.`routeid`))
ORDER BY `reserve`.`id`;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.