Project Deliverable 3: Database and Programming Design
John Clevenger
Strayer University
Undergrad Information Technology Capstone
CIS 498
Reddy Urimindi
November 16, 2014
PROJECT DELIVERABLE 3: DATABASE AND PROGRAMMING 3
Project Deliverable 3: Database and Programming Design
The Above shown diagrams show the separate sectors of the business. First is the Airline Parts sale due to its location near commercial and private air stations. The tourist rental side includes a bicycle rental business as well. This paper will focus on the bicycle rental side and elaborate on database design for the bicycle Rental business. The Bicycle Rental Business mainly revolves around renting out bicycles to customers on basis of time. The Business has grown around the area well and is to be expanded around the neighboring areas as well. Hence, the business is computerized in order to store the details of the customers and also the details and automated payment system. The Relational database design consists of seven tables.
1. Bicycles
2. Renters
3. Rentals
4. Rental_Rates
5. Payment_Status
6. Renters_Payment_Methods
7. Payment_menthod
Database Creation, tables, fields, relationships, views, and indexes, primary key , foreign key.
1)
The Below Section shows the table creation along with various constraints.
The Primary Key are indicated in RED
Foreign Key are indicated in BLUE
Other Constraints are indicated in GREEN
1)
a) CREATE TABLE IF NOT EXISTS `mydb1`.`Bicycles` (
`id_Bicycles` INT NOT NULL ,
`bicycle_details` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`id_Bicycles`) ,
UNIQUE INDEX `id_Bicycles_UNIQUE` (`id_Bicycles` ASC) );
b)
CREATE TABLE IF NOT EXISTS `mydb1`.`Renters` (
`id_Renters` INT NOT NULL ,
`Renter_fname` VARCHAR(45) NULL ,
`Renter_lname` VARCHAR(45) NULL ,
`Renter_address` VARCHAR(45) NULL ,
`Renter_town` VARCHAR(45) NULL ,
`Renter_state` VARCHAR(45) NULL ,
`Renter_pcode` VARCHAR(45) NULL ,
`Renter_phone` VARCHAR(45) NULL ,
`Renter_DOB` VARCHAR(45) NULL ,
`Renter_registered` DATETIME NULL ,
`Renter_last_rental` DATETIME NULL ,
`Renter_other_detail` LONGTEXT NULL ,
UNIQUE INDEX `id_Renters_UNIQUE` (`id_Renters` ASC) ,
PRIMARY KEY (`id_Renters`) );
c)
CREATE TABLE IF NOT EXISTS `mydb1`.`Payment_Method` (
`id_Payment_Method` INT NOT NULL ,
`Payment_Method_description` VARCHAR(45) NULL ,
UNIQUE INDEX `id_Payment_Method_UNIQUE` (`id_Payment_Method` ASC) ,
PRIMARY KEY (`id_Payment_Method`) )
;
d)
CREATE TABLE IF NOT EXISTS `mydb1`.`Payment_Status` (
`id_Payment_Status` INT NOT NULL ,
`Payment_Status_description` VARCHAR(45) NULL ,
PRIMARY KEY (`id_Payment_Status`) ,
UNIQUE INDEX `id_Payment_Status_UNIQUE` (`id_Payment_Status` ASC) );
e)
CREATE TABLE IF NOT EXISTS `mydb1`.`Rental_Rates` (
`id_Rental_Rates` INT NOT NULL ,
`Rental_Rates_daily` DECIMAL(2) NULL ,
`Rental_Rates_hourly` VARCHAR(2) NULL ,
UNIQUE INDEX `id_Rental_Rates_UNIQUE` (`id_Rental_Rates` ASC) ,
PRIMARY KEY (`id_Rental_Rates`) );
f)
CREATE TABLE IF NOT EXISTS `mydb1`.`Renters_Payment_Methods` (
`id_Renters_Payment_Methods` INT NOT NULL ,
`id_renter` INT NULL ,
`id_Payment_Methods` INT NULL ,
`Renters_Payment_Methods_card_details` VARCHAR(45) NULL ,
`Renters_Payment_Methods_other_details` TEXT NULL ,
PRIMARY KEY (`id_Renters_Payment_Methods`) ,
UNIQUE INDEX `id_Renters_Payment_Methods_UNIQUE` (`id_Renters_Payment_Methods` ASC) ,
INDEX `id_renter_idx` (`id_renter` ASC) ,
INDEX `id_Payment_Methods_idx` (`id_Payment_Methods` ASC) ,
CONSTRAINT `id_renter`
FOREIGN KEY (`id_renter` )
REFERENCES `mydb1`.`Renters` (`id_Renters` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `id_Payment_Methods`
FOREIGN KEY (`id_Payment_Methods` )
REFERENCES `mydb1`.`Payment_Method` (`id_Payment_Method` )
ON DELETE NO ACTION
ON UPDATE NO ACTION);
g)
CREATE TABLE IF NOT EXISTS `mydb1`.`Rentals` (
`id_Rentals` INT NOT NULL ,
`id_Renters` INT NULL ,
`id_Bicycles` INT NULL ,
`id_Renter_payment_Method` INT NULL ,
`Rentals_all_day` BINARY NULL ,
`Rentals_booked_start` DATETIME NULL ,
`Rentals_booked_end` DATETIME NULL ,
`Rentals_actual_start` DATETIME NULL ,
`Rentals_actual_end` DATETIME NULL ,
`Rentals_payment_due` DECIMAL(2) NULL ,
`Rentals_payment_made` BINARY NULL ,
`Rentals_other_details` TEXT NULL ,
`id_rental_rates` INT NULL ,
`id_payment_status` INT NULL ,
UNIQUE INDEX `id_Rentals_UNIQUE` (`id_Rentals` ASC) ,
PRIMARY KEY (`id_Rentals`) ,
INDEX `id_bicycles_idx` (`id_Bicycles` ASC) ,
INDEX `id_Renter_payment_Method_idx` (`id_Renter_payment_Method` ASC) ,
INDEX `id_renters_idx` (`id_Renters` ASC) ,
CONSTRAINT `id_renters`
FOREIGN KEY (`id_Renters` )
REFERENCES `mydb1`.`Renters` (`id_Renters` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `id_bicycles`
FOREIGN KEY (`id_Bicycles` )
REFERENCES `mydb1`.`Bicycles` (`id_Bicycles` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `id_Renter_payment_Method`
FOREIGN KEY (`id_Renter_payment_Method` )
REFERENCES `mydb1`.`Payment_Method` (`id_Payment_Method` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `id_rental_rates`
FOREIGN KEY (`id_Rentals` )
REFERENCES `mydb1`.`Rental_Rates` (`id_Rental_Rates` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `id_payment_status`
FOREIGN KEY (`id_Renter_payment_Method` )
REFERENCES `mydb1`.`Payment_Status` (`id_Payment_Status` )
ON DELETE NO ACTION
ON UPDATE NO ACTION);
d)
TABLES IN 3NF.
The above created tables are in 3NF form.
ER DIAGRAM
DFD DIAGRAM
i)
1)
Alter table Renters add
(
email Varchar(20),
comments Varchar(20),
send_NewsLetter Boolean );
j)
Alter table renters modify column Renter_DOB date;
3)
1)
mysql> insert into renters(id_Renters,Renter_fname,Renter_lname,Renter_address,Renter_town,Renter_state,Renter_pcode,Renter_phone,Renter
_DOB) values(12345,'Jones','George','123 Bentinck St','Bathurst','NSW','2795','0212345678','1964/05/01');
insert into renters(id_Renters,Renter_fname,Renter_lname,Renter_address,Renter_town,Renter_state,Renter_pcode,Renter_phone,Renter_DOB) values (23456,’Jones’,’Pauline’,’48 Summer St’,’Orange’,’NSW’,’2800’,’0223456789’,’1986/09/11’);
2)
Delete from renters where renter_town=’orange’;
j)
Database Queries
1)Select part_short_name where Quotation_item_price = 3455;
2) insert into Customer_Order_Items values (‘SG35’, 23554, ‘two_days’, ‘NA’);