Module 13 Week 6 Dimensional Data Modeling Assignment

User Generated

jvyyvnzxhzv

Computer Science

Description

1. Dimensional Data Modeling Assignment

The 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.) youtube

2. Data Management Project: Final

Please 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 database
2. Purpose of database
3. Business goals and rules for database
4. User requirements for database
5. Data requirements for database
6. Reporting requirements for database
7. Estimated project schedule and project costs for creating the database and populating it with data
8. Logical data model
9. Data dictionary for all tables in data model
10. 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

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 A Retail Bank Dimensional Model. Back to the Home Page. Complete Dimensional Model Accounts Account_Number Account_Detais Ref_Account_Status Account_Status_Code Account_Status_Description eg Active, Closed Addresses Address_10 Address_Details Ref_Account_Types Account_Type_Code Account_Type_Description eg Checking, Savings etc Banks Bank_ID Bank_Details Dimensional_Model Fact_ID Account_Number Account_Status_code Account_Type_Code Address_ID Bank_ID Branch_D Branch_type_Code Card_Number Customer_10 Merchant_ID Monthly_Balance_JD Payment Method Type Code Reporting Date_and_Time Transaction_10 Transaction_Type_Code Averages, Counts, Totals Other Derived Figures Ref_Branch_Types Branch_Type_Code Branch_Type_Description eg Large Urban, Small Rural Branches_ Branch_ID Branch_Details Ref_Calendar H® Day Date_and_Time Calendar_Detais Customers Customer_ID Customer_Details Customers_Cards Card_Number Customer_Card_Details Ref_Payment Method Types Payment_Method_Type_Code Payment_Method_Type_Description eg AMEX-American Express eg MC=Master Card Merchants Monthly_Balances Monthly_Balance_ID Monthly_Balance_Details Merchant_ID Merchant_Detais + Ref_Transaction_Types Transaction_type_Code Transaction_Type_Description eg Deposit. Withdrawal Transactions Transaction_ID Transaction_Details
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hello check the paper, review and in case of a comment inform.

DIMENSIONAL DATA MODELING
Student’s Name
Course title
April 27, 2019

Dimensional Data Modeling

Solutions to Dimensional Data Modelling
1. The fact table
The fact table is Dimensional_Model

2. The dimension tables.
i.

Banks

ii.

Addresses

iii.

Merchants

iv.

Customers

v.
vi.

Accounts

Monthly_Balances, etc.

3. Dimension Table Attributes
Dimension Table

Attributes

Banks

Bank_ID, Bank_details

Addresses

Address_ID, address_details

Merchants

Merchant_ID, Merchant _details

Customers

Customer_ID, Customer_details

Accounts

Account_Number, Account_details

Monthly_Balances, etc.

Monthly_Balance_ID,
Monthly_Balance_Details

Queries to be produced from the Above model
a. The number of accounts that each Customer hold at the bank.
b. The number of branches that the bank has?
c. The monthly balance that each account holds.
d. The time that each transaction was done on the customer account.
e. The customer account type and the account status.
f. The customer’s address.


Running head: FINAL PROJECT DRAFT

1

Car Rental MySQL Database Project
April 20, 2019

FINAL PROJECT DRAFT

2

Background of database
This car rental management system project was implemented for a purpose of providing 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
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 prof...


Anonymous
Excellent resource! Really helped me get the gist of things.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags