1. Dimensional Data Modeling AssignmentThe data warehousing / data mart dimensional attached below. Copy the data model into a MS Word document. In your MS Word document. (You can copy the data model graphic by right-clicking on it. On PCs, the keyboard combination of ctrl-V is the shortcut command for pasting.) In the MS Word document, 1) identify the fact table 2) identify the dimension tables, 3) for each dimension table, identify the attribute(s) that would be matched to an attribute in the fact table, and 4) list four queries that could be produced from this data model. For example, the queries from a data model of real estate sales that could typically be produced by matching a fact table to one or more dimension tables include: a) list of properties sold by each real estate agent, b) total properties sold by state, c) types of properties sold by month and year, d) dollar value of properties sold by each real estate company, e) total homes sold by real estate agent by company by year.Video: Module 13 – Introduction to Data Warehousing (5:27 min.) youtube2. Data Management Project: FinalPlease make corrections to the draft of final project that you submitted to me. i attached copy below. Your final project must include the following elements:1. Background of database – why you chose to design this particular database2. Purpose of database3. Business goals and rules for database4. User requirements for database5. Data requirements for database6. Reporting requirements for database7. Estimated project schedule and project costs for creating the database and populating it with data8. Logical data model9. Data dictionary for all tables in data model10. SQL statements: – CREATE TABLE statements (for entities defined in model) – Minimum of two SELECT statements – INSERT, UPDATE, or DELETE statements for at least one table 11. Identify the database management system (DBMS) software that will be used for the database 12. Specify the hardware for storing your database 13. Create a basic database back-up and recovery plan that is consistent with the business goals
retail_bank.png
project_rough_draft_week6.docx
Unformatted Attachment Preview
Running head: FINAL PROJECT DRAFT
1
Car Rental MySQL Database Project
April 20, 2019
FINAL PROJECT DRAFT
2
Background of database
The database project, in this case, entails all about a car rental management system. This
is a MySQL implemented database project that provides a platform where the car owners/vehicle
agents would be able to outline all the car make and models; the system also provides availability
of the same car to the customers who want to lease the vehicle on temporarily basis. This database
project also includes customer registration using details such as the customer name and address,
the vehicle details, the agencies among other details.
Purpose of database
The purpose of the project is;
i.
To ease the process of car rental services by ensuring that the customers can access the
vehicles of their choice at ease.
ii.
To provide a platform where the car owners/agents would be able to outline their services
and products, and also ensure that customer can view and select from those services.
iii.
To ensure that it is easier to calculate the cost and the payment depending on the vehicle
rental period.
Business goals
Many people want to experience a driving experience with or without having to own a
vehicle. In most cases, people who don’t own any vehicle on their own find it difficulty accessing
vehicle there can drive for their own needs at their own free time. This MySQL database project
FINAL PROJECT DRAFT
3
aims to ensure that it provides a platform where any individual can be able to register and access
a vehicle of his/her choice temporarily. This will enable them to perform their task with ease. In
specific the project will help solve the following problems;
i.
Enable car owner, or an agent leases his/her car temporarily for a profit.
ii.
Enable an individual to rent a vehicle of his choice for temporary usage.
iii.
Provide a wide choice of vehicle make and model to choose from.
iv.
Provide a flexible timeline for the car lease and rent – This enables each person to work
according to his time.
Database rules
The database shall have Five entities as follows;
1. The customers shall be identified by their Customer ID, Customer Name, Address,
Telephone Number and their agencies ID.
2. The Agencies shall be identified by their Agency ID and the Location.
3. The vehicle shall be identified by their Vehicle ID, Vehicle Class and the vehicle class
Name.
4. Vehicle rentals shall Contains the customer ID, the rental start and end Date, the vehicle
ID and the Agency ID.
The following database Entity relation diagram outlines the business rules.
FINAL PROJECT DRAFT
4
User requirements for database
The car rental database system shall support the following
1. Allow for each of the customers to provide his/her name and address information and other
important personal information
2. Allow for each of the vehicle agency to outline his/her location in the database.
3. Allow for the car rental details to be captured such includes the rental start and return date.
4. Allow for each of the vehicles’ name and description to be captured in the database
Data requirements for database
Each of the customer shall have a unique identifier, the Customer Id.
Each of the Customer Shall have Name, address, Phone and location inserted in the Customer
table.
Each of the car shall have a unique identifier, the car Id and a related Vehicle Class Name.
FINAL PROJECT DRAFT
5
Reporting requirements for database
The car rental database project shall have to meet the following requirements;
1. To enable the project stakeholder be able to meet the system objectives and goals by
reporting on all the critical data containing the car renting.
2. The system must report the daily vehicle rents and the Total amount realized.
3. The system shall be able to report the amount of customer order request per day.
4. The system shall be able to report the number of agencies, their location, cars and cost.
5. The system must be able to report any other critical information requested by the
management.
Estimated project schedule and project costs for creating the database and populating it
with data
The Project Cost
Project Resources
Cost
Computer Resource
$500
Software and Licenses
$250
Other Cost
$100
Total Cost
$850
FINAL PROJECT DRAFT
6
The project Schedule
Logical data model
Data dictionary for all tables in data model
Data Dictionary for Car Entity
FINAL PROJECT DRAFT
7
Car
—
Data
Format
Description
Values
Xxxxxxx
This is the unique
1HBG
Type
RegNo
VarChar
identifier for the Car
table
Millage
Integer
1-9999
This value is used to
1990KM
identify the car
Millage
Engine_Size
Integer
1-9999
This is a value which
1980Cc
is used to identify the
car millage
Daily_Hire_Rate
Date
Xx-xx-xxxx
Indicate the date
9-12-2011
which the car was
hired
Date_Not Due
Date
Xx-xx-xxxx
Indicate the time the
9-12-2011
car is free
Data Dictionary for Car Entity
Car
Attribute Name
Data
Format
Description
Values
Xxxxxxx
This is the unique
1HBG
Type
RegNo
VarChar
identifier for the Car
table
FINAL PROJECT DRAFT
Millage
8
Integer
1-9999
This value is used to
1990KM
identify the car
Millage
Engine_Size
Integer
1-9999
This is a value which
1980Cc
is used to identify the
car millage
Daily_Hire_Rate
Date
Xx-xx-xxxx
Indicate the date
9-12-2011
which the car was
hired
Date_Not Due
Date
Xx-xx-xxxx
Indicate the time the
9-12-2011
car is free
Data Dictionary for Car Model
Car Model
Attribute Name
Data
Format
Description
Values
Xxxxxxx
This is the unique
1HBG
Type
Model Code
VarChar
identifier for the Car
model
Model Name
String
xxxxxx
This value contain the Toyota Vitz
name of the car
model
Model_desc
String
xxxxx
This is a value
contains any other
information about the
car model
Data Dictionary for Customer
Van
FINAL PROJECT DRAFT
9
Customer
Attribute Name
Data
Format
Description
Values
1-999
This is the unique
1
Type
Customer ID
Integer
identifier for the
customer
Customer Name
String
xxxxxx
This value contain
John Smith
the name of the
Customer
gender
String
xxxxx
This is a value
Male/Female
contains the
customer gender
Email Address
String
xxxxx@gmail.com This value
abc@gmail.com
represent the
customer email
address
Address
String
xxxxxxxx
The customer ‘s
ABC Street Y
address
Phone
Integer
The customer’s
1-999
10244566
Phone
Data Dictionary Booking
Booking
Attribute Name
Data
Type
Format
Description
Values
FINAL PROJECT DRAFT
Booking ID
10
Integer
1-999
This is the unique
1
identifier for the
Booking Table
From_Date
Date
xx-xx-xxxx
This value
8-4-2019
represent the date
the car was booked
To_Date
Date
xx-xx-xxxx
This is a value
9-5-2019
represent the data
the car is available
for booking
Confirmation
String
xxxxx
Whether the car is
Not available
available or not
Payment
Currency
1-999.9999
The payment made
$100.25
for the booking
Data Dictionary Booking
Booking Status
Attribute Name
Data
Format
Description
Values
Xxxxx
This is the unique
1BR
Type
Booking Status
Varchar
Code
identifier for the
Booking Status
Table
Status_description
String
Xxxxxx
Any Other
N/A
Information about
the booking
To_Date
Date
xx-xx-xxxx
This is a value
represent the data
9-5-2019
FINAL PROJECT DRAFT
11
the car is available
for booking
Confirmation
String
xxxxx
Whether the car is
Not available
available or not
Payment
Currency
1-999.9999
The payment made
$100.25
for the booking
SQL statements
Create Database
CREATE DATABASE `Car_Rental`
Table car model
CREATE TABLE `Car_Model` (
`modelcode` VARCHAR(50) NOT NULL,
`ModelName` VARCHAR(50) NULL,
`modeldesc` TEXT NULL,
PRIMARY KEY (`modelcode`)
);
Table Car
CREATE TABLE `Car` (
`Reg_No` VARCHAR(50) NOT NULL,
`Model_Code` VARCHAR(50) NULL,
`millage` INT NULL,
`Engine_Size` INT NULL,
`Daily_Hire_Rate` DATE NULL,
`Date_Not_Due` DATE NULL,
PRIMARY KEY (`Reg_No`)
);
Foreign Key
ALTER TABLE `Car`
ADD CONSTRAINT `FK_Car_car_model` FOREIGN KEY (`Model_Code`) REFERENCES
`car_model` (`modelcode`) ON UPDATE NO ACTION;
Table Customer
FINAL PROJECT DRAFT
CREATE TABLE `Customer` (
`CustomerID` INT NOT NULL,
`Customer_Name` VARCHAR(100) NULL,
`Gender` VARCHAR(25) NULL,
`Email_Address` VARCHAR(50) NULL,
`Address` VARCHAR(50) NULL,
`Phone` INT NULL,
PRIMARY KEY (`CustomerID`)
);
Booking Table
CREATE TABLE `Booking` (
`BookingID` INT NOT NULL,
`Reg_No` VARCHAR(50) NULL,
`CustomerID` INT NULL,
`Booking_Status_Code` VARCHAR(50) NULL,
`Date_From` DATETIME NULL,
`Date_To` DATETIME NULL,
`Confirmation` VARCHAR(50) NULL,
`Payment` DOUBLE NULL,
PRIMARY KEY (`BookingID`)
);
Table Booking Status
CREATE TABLE `Booking Status` (
`BookingStatusCode` VARCHAR(50) NOT NULL,
`StatusDesc` TEXT NULL,
PRIMARY KEY (`BookingStatusCode`)
);
Foreign Key
ALTER TABLE `booking`
ADD CONSTRAINT `FK_Booking_car` FOREIGN KEY (`Reg_No`) REFERENCES `car`
(`Reg_No`) ON UPDATE NO ACTION,
ADD CONSTRAINT `FK_Booking_customer` FOREIGN KEY (`CustomerID`) REFERENCES
`customer` (`CustomerID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
ADD CONSTRAINT `FK_booking_booking status` FOREIGN KEY
(`Booking_Status_Code`) REFERENCES `booking status` (`BookingStatusCode`) ON
UPDATE NO ACTION ON DELETE NO ACTION;
Insert Statement
INSERT INTO `car_rental`.`car_model` (`modelcode`, `ModelName`, `modeldesc`)
VALUES (‘TC’, ‘Toyota Camry’, ‘Toyota car’);
12
FINAL PROJECT DRAFT
13
INSERT INTO `car_rental`.`car_model` (`modelcode`, `ModelName`, `modeldesc`)
VALUES (‘TCO’, ‘Toyota Collora’, ‘N/a’);
INSERT INTO `car_rental`.`car_model` (`modelcode`, `ModelName`, `modeldesc`)
VALUES (‘TR’, ‘Toyota Rav4’, ‘N/a’);
INSERT INTO `car_rental`.`car_model` (`modelcode`, `ModelName`, `modeldesc`)
VALUES (‘HC’, ‘Honda Civic’, ‘Honda Saloon Car’);
INSERT INTO `car_rental`.`car_model` (`modelcode`, `ModelName`, `modeldesc`)
VALUES (‘HCRV’, ‘Honda CR-V’, ‘Honda Hatch Back ‘);
Database management system (DBMS) software
For this project, The Car Rental MySQL Database Project, we would use MySQL Database
Management System to implement the project. MySQL related workbench Would be the tool for
editing the SQL Statements.
Hardware Requirements
The Car Rental MySQL Database would be centralized where a single database instance
would be used to serve the multiple client’s requests. In this case, a server would be used to
implement the database with a web or a mobile application interface for user access. To make sure
that the database would be available throughout and secure while at the same time using minimal
cost, the database would use cloud computing instead of private servers which are expensive and
time-consuming to acquire and configure. I would use an open source MySQL database under
Oracle Corporation for the DBMS software as it is cheap and have a vast community for support.
For the database hardware vendor, I would consider HP hardware as they are easily available at
minimal cost, (NUNNS, 2015). Some of the factor that I would consider includes; Performance
where I would consider the highest performing DBMS software and Database hardware for the
implementation. Cost where I would consider the cheapest about the performance and other
FINAL PROJECT DRAFT
14
related factors. Security – This is a vital factor where I would consider the most secure DBMS and
database hardware for the project.
Database back-up and recovery plan
Using MySQL database, I would be able to consider the following, ensuring to use
MYSQL utility tools such as Dump to create a copy of the Car Rental Database. Ensuring that
there is a schedule for the database backup, this would prevent potential data losses. Similarly,
ensure to use an external device for the database back, this would be effective during the recovery
plan. The location and the instructions should be included in the recovery session. Depending on
the situation a full database recovery can be made where the database and its associated meta are
recovered and restored. Similarly, a Transaction log backup can be carried out to safeguard a single
transaction, (tipsandtricks-hq.com, 2010).
FINAL PROJECT DRAFT
15
References
(2010, 7 28). Retrieved from tipsandtricks-hq.com: https://www.tipsandtricks-hq.com/databaserecovery-techniques-2621
NUNNS, J. (2015, 8 16). Retrieved from https://www.cbronline.com/news/bigdata/hardware/top-5-big-data-hardware-vendors-4647479
Moustafaev, J. (2014). Project Scope Management: A Practical Guide to Requirements for
Engineering, Product, Construction, IT and Enterprise Projects. CRC Press.
Rouse, M. (2015, 3 1). Retrieved from techtarget.com:
https://whatis.techtarget.com/definition/constraint-project-constraint
NUNNS, J. (2015, 8 16). Retrieved from https://www.cbronline.com/news/bigdata/hardware/top-5-big-data-hardware-vendors-4647479
FINAL PROJECT DRAFT
16
…
Purchase answer to see full
attachment
