CUSTOMER TABLE STATEMENT

CUSTOMER TABLE:

CREATE TABLE Customer

( CustomerID int(15) NOT NULL AUTO_INCREMENT PRIMARY KEY,

Name varchar(50),

Address varchar(255),

Phone int(15),

DrivingLicense varchar(15) NOT NULL UNIQUE,

CreditCardNum int(15) NOT NULL UNIQUE,

DateTime date

);

VEHICLE TABLE:

CREATE TABLE Vehicle

( VehicleID int(15) NOT NULL AUTO_INCREMENT PRIMARY KEY,

Type varchar(255),

RegistrationNo varchar(10) NOT NULL UNIQUE,

YearMakeModel varchar(255),

VinNumber varchar(20) NOT NULL UNIQUE,

DistanceTravelled float(7,2),

CurrentCondition varchar(100),

AvailabilityStatus varchar(100)

);

RENTAL TABLE:

CREATE TABLE Rental

(RentalID int(15) NOT NULL AUTO_INCREMENT PRIMARY KEY,

VehicleID int(15),

CustomerID int(15),

DateOf_Checkout date,

DueDate date,

PricePerDay float(5,2),

OverDuePricePerDay float(5,2),

TotalPrice float(7,2),

DemeritPointStatus varchar(30),

MembershipStatus varchar(30),

CONSTRAINT vehicle_FK FOREIGN KEY (VehicleID)

 REFERENCES Vehicle(VehicleID)

ON DELETE SET NULL ON UPDATE CASCADE,

CONSTRAINT customer_FK FOREIGN KEY (CustomerID)

REFERENCES Customer(CustomerID)

ON DELETE SET NULL ON UPDATE CASCADE

);

INSERT INTO STATEMENTS FOR TABLE Customer:

INSERT INTO Customer(CustomerID, Name, Address, Phone, DrivingLicense, CreditCardNum, DateTime)

VALUES ( 1, Ruby Tan, 201A 140 Bourke Street Melbourne VIC 3000, 0333445234, 234567098, 87368299847635, 2018 8 9 04:54:33),

(2, Armstrong K, 151 Beach St Port Melbourne VIC 3207, 0429676219, 897567345, 78494735684098, 2018 8 9 07:20:33),

(3, Amore AP, 463 Victoria St West Melbourne VIC 3003, 0393282686, 672890276, 90678967893466, 2018 8 10 02:33:50),

(4, Ash E, 12 Hotham St South Melbourne VIC 3205, 0396867577, 563876250, 86358096782654, 2018 8 11 03:55:00),

(5, Alexander M, 120 Princes st Port Melbourn VIC 3207, 0390410973, 836509873, 56840976386574, 2018 8 11 06:07:39);

INSERT INTO STATEMENT FOR TABLE Vehicle:

INSERT INTO Vehicle( VehicleID, Type, RegestrationNo, YearMakeModel, VinNumber, DistanceTravelled, CurrentCondition, AvailabilityStatus)

VALUES( 1, sedan, TFS676, 2017 Suzuki, YV3R6R620GA177608, 476378.00, good, available),

( 2, SUV, MUV890, 2015 Toyota, YV3R6R621GA177875, 476378.00, excellent, available),

( 3, Mini Van, AUO987, 2018 Balleno, YV3R6R621GA178136, 476378.00, good, on rent),

( 4, Hatch Back, VIG567, 2017 Honda, YV3R6R621GA178170, 476378.00, good, available),

( 5, Pick Up, TYH098, 2016 Toyota, YV3R6R622GA177609, 476378.90, very good, on rent),

( 6, Station Wagon, KLB456, 2017 Balleno, YV3R6R622HA184190, 476378.78, need work, on rent),

( 7, Hatch Back, TFB778, 2013 Suzuki, YV3R6R623GA177876, 476378.05, good, available),

( 8, Mini Van, YHM987, 2015 Suzuki, YV3R6R625GA178169, 476378.67, very good, on rent),

( 9, SUV, GRN782, 2016 Honda, YV3R6R628GA177873, 476378.56, excellent, available),

( 10, sedan, FGN654, 2014 Toyota, YV3R6R629GA177610, 476378.67, good, on rent);

INSERT INTO STATEMENT FOR TABLE Rental:

INSERT INTO Rental (RentalID, VehicleID, CustomerID, DateOf_Checkout, DueDate, PricePerDay, OverDuePricePerDay, TotalPrice, DemeritPointStatus, MembershipStatus)

VALUES (1, 5, 2, 2018 8 9, 2018 8 9, AUD 17.00, AUD 22.00, AUD 39.00, None, Active),

(1, 3, 4, 2018 8 9, 2018 8 10, AUD 15.00, AUD 20.00, AUD 30.00, None, Active),

(1, 8, 5, 2018 8 18, 2018 8 18, AUD 15.00, AUD 20.00, AUD 15.00, None, Active);

SELECT STATEMENT FOR VEHICLES NOT BEEN RENTED:

SELECT VehicleID, Type, RegistrationNo, YearMakeModel, VinNumber

FROM Vehicle

WHERE AvailabilityStatus=’available’ ;

SELECT STATEMENT TO CALCULATE TOTAL AMOUNT PAID PER CUSTOMER:

SELECT CustomerID, Name, Phone, TotalPrice AS TotalAmount

FROM Customer INNER JOIN Rental ON Customer.CustomerID=Rental.CustomerID;

NORMALIZATION AND ENTITIES IN 3NF:

Normalization is the process of removing redundancy or making sure that there is no duplicity in entity tables. Normalization is usually divided into following forms:

1^ST NORMAL FORM:

In first normal form we make sure that the attributes are having single values, deciding there relevant data types for each attribute and attributes have unique and proper names following the table rules in DBMS. As in our database we have followed these rules and values in all the attributes are single valued.

2^ND NORMAL FORM:

In 2^nd normal we make sure that tables are already in first normal form and there is no partial dependency on any of the attributes. As we make a unique attribute as primary key which means there is no duplicity in the values of this attribute and no other column/ attribute will be depending on the value of this field. In our case we make our CustomerID, VehicleID, RentalID as primary key in auto increment form and no other field is depending on them.

 3^RD NORMAL FORM:

In 3^rd normal form when tables are already in second normal form and they are free from transitive dependency. Which means that we make sure that our table is fields/attributes are free from functional dependencies. That our table column does not depend on another column of the table. Our all the tables are in normal form as they are in 2^nd normal form and they don’t have transitive dependency in any of the field of tables.

 BOYCE CODD NORAL FORM (BCNF):

BCNF is more advance level of 3^RD normal for as in this case we make sure that our table is already in 3^rd normal form and if any field has functional dependencies we make field as super key for the other field. We make the many to many relationship in one to many or one to one relationship by splitting the tables and make foreign keys in the tables.

DATABASE SECURITY AND CONTROL MEASURES:

We have to make sure that our database is protected from unauthorized access as it contains confidential information of the customers. We will be applying password to access the records and taking regular backup of our database. We should follow the security and control rules for database which are:

Authenticity:

We can apply privileges to access the user logins b other persons and restrict these authentications.

Encryption:

We can use data encoding algorithms in our database to protect the customer data like there credit card numbers by encoding them.

Flow Control:

We will be applying privacy policies to prevent the unauthorized access by the users and using pathways.

Security Measures:

We can control the security by controlling the login process. Privileges, restrictions and access control can be handle by making user login.