B01Dbfn212 Database Fundamentals-Dependency Diagram Answers Assessment Answers
bike was taken out, the time it was due back and the actual time it was returned. In addition,the amount of rent paid is recorded which is determined through the class size code. Each rental record is associated with only one customer. A bike can be rented out any number of times or may never be rented.
Complete the following tasks:
1. Create a complete ERD to support the business needs as described above
2. Convert the ERD in at least 3NF and draw a dependency diagram
3. Create a database for FBR using MS ACCESS (Create tables only for bikes, rentals and customers with primary and foreign keys defined and other their other data)
4. Create a form to input data for bikes.
5. Input some significant data (8 bikes, 5 customers and 4 rentals)
6. Create SELECT, JOIN and NUMERIC Function queries (one of each type; you can use any tables and any fields)
7. Create a report using any one of the above queries.
Answer:
RentalTypeID -> RentalTypeName, RentalTypeCost
RideTypeID -> RideTypeName
BikeSizeID -> BikeSizeName
StatusID -> StatusName
PartID -> PartName
BikeID -> Model, Manufacturer, ManuYear, RideTypeID, BikeSizeID,
StatusID
CustomerID -> CustomerName, Address, City, PostCode, Phone, Email
DealerID -> DealerName, Address, Phone
SalesID -> SalesDate, SalesCost, BikeID, DealerID
RentalID -> RentalDate, StartTime, EndTime, RentalTypeID, TotalCost,
CustomerID, BikeID
MaintainID -> Description, MaintainDate, MaintainCost, BikeID,
CompletedDate
PartID, MaintainID -> Quantity
Relational Schema
RentalType (RentalTypeID, RentalTypeName, RentalTypeCost)
Primary Key (RentalTypeID)
RideType (RideTypeID, RideTypeName)
Primary Key (RideTypeID)
BikeSize (BikeSizeID, BikeSizeName)
Primary Key (BikeSizeID)
Status (StatusID, StatusName)
Primary Key (StatusID)
Parts (PartID, PartName)
Primary Key (PartID)
Bike (BikeID, Model, Manufacturer, ManuYear, RideTypeID, BikeSizeID, StatusID)
Primary Key (BikeID),
Foreign Key (RideTypeID) References RideType (RideTypeID),
Foreign Key (BikeSizeID) References BikeSize (BikeSizeID),
Foreign Key (StatusID) References Status (StatusID)
Customer (CustomerID, CustomerName, Address, City, PostCode, Phone, Email)
Primary Key (CustomerID)
Dealer (DealerID, DealerName, Address, Phone)
Primary Key (DealerID),
Sales (SalesID, SalesDate, SalesCost, BikeID, DealerID)
Primary Key (SalesID),
Foreign Key (BikeID) References Bike (BikeID),
Foreign Key (DealerID) References Dealer (DealerID)
Rental (RentalID, RentalDate, StartTime, EndTime, RentalTypeID, TotalCost, CustomerID, BikeID)
Primary Key (RentalID),
Foreign Key (RentalTypeID) References RentalType(RentalTypeID),
Foreign Key (CustomerID) References Customer (CustomerID),
Foreign Key (BikeID) References Bike (BikeID)
Maintenance (MaintainID, Description, MaintainDate, MaintainCost, BikeID, CompletedDate)
Primary Key (MaintainID),
Foreign Key (BikeID) References Bike(BikeID)
PartsUsed (PartID, MaintainID, Quantity)
Primary Key (PartID, MaintainID)
Foreign Key (PartID) References Parts(PartID),
Foreign Key (MaintainID) References Maintenance (MaintainID)
Create Table Statements:
CREATE TABLE RentalType (RentalTypeID INTEGER, RentalTypeName VARCHAR(30) NOT NULL, RentalTypeCost FLOAT NOT NULL,Primary Key (RentalTypeID));
CREATE TABLE RideType (RideTypeID INTEGER, RideTypeName VARCHAR(30) NOT NULL, Primary Key (RideTypeID));
CREATE TABLE BikeSize (BikeSizeID INTEGER, BikeSizeName VARCHAR(30) NOT NULL, Primary Key (BikeSizeID));
CREATE TABLE Status (StatusID INTEGER, StatusName VARCHAR(30) NOT NULL, Primary Key (StatusID));
CREATE TABLE Parts (PartID INTEGER, PartName VARCHAR(30) NOT NULL, Primary Key (PartID));
CREATE TABLE Bike (BikeID INTEGER, Model VARCHAR(30) NOT NULL, Manufacturer VARCHAR(30) NOT NULL, ManuYear INTEGER NOT NULL, RideTypeID INTEGER NOT NULL, BikeSizeID INTEGER NOT NULL, StatusID INTEGER NOT NULL, Primary Key (BikeID), Foreign Key (RideTypeID) References RideType (RideTypeID), Foreign Key (BikeSizeID) References BikeSize (BikeSizeID), Foreign Key (StatusID) References Status (StatusID));
CREATE TABLE Customer (CustomerID INTEGER, CustomerName VARCHAR(30) NOT NULL, Address VARCHAR(60) NOT NULL, City VARCHAR(30) NOT NULL, PostCode INTEGER NOT NULL, Phone VARCHAR(15) NOT NULL, Email VARCHAR(45) NOT NULL, Primary Key (CustomerID))
CREATE TABLE Dealer (DealerID INTEGER, DealerName VARCHAR(30) NOT NULL, Address VARCHAR(60) NOT NULL, Phone VARCHAR(15) NOT NULL, Primary Key (DealerID));
CREATE TABLE Sales (SalesID INTEGER, SalesDate Date NOT NULL, SalesCost FLOAT NOT NULL, BikeID INTEGER NOT NULL, DealerID INTEGER NOT NULL, Primary Key (SalesID), Foreign Key (BikeID) References Bike (BikeID), Foreign Key (DealerID) References Dealer (DealerID));
CREATE TABLE Rental (RentalID INTEGER, RentalDate Date NOT NULL, StartTime Date NOT NULL, EndTime Date, RentalTypeID INTEGER NOT NULL, TotalCost FLOAT, CustomerID INTEGER NOT NULL, BikeID INTEGER NOT NULL, Primary Key (RentalID), Foreign Key (RentalTypeID) References RentalType(RentalTypeID), Foreign Key (CustomerID) References Customer (CustomerID), Foreign Key (BikeID) References Bike (BikeID));
CREATE TABLE Maintenance (MaintainID INTEGER, Description VARCHAR(60) NOT NULL, MaintainDate Date NOT NULL, MaintainCost FLOAT, BikeID INTEGER NOT NULL, CompletedDate Date, Primary Key (MaintainID), Foreign Key (BikeID) References Bike(BikeID));
CREATE TABLE PartsUsed (PartID INTEGER, MaintainID INTEGER, Quantity INTEGER NOT NULL, Primary Key (PartID, MaintainID), Foreign Key (PartID) References Parts(PartID), Foreign Key (MaintainID) References Maintenance (MaintainID));
INSERT INTO RentalType (RentalTypeID, RentalTypeName, RentalTypeCost) VALUES (101,'Half Day',30);
INSERT INTO RentalType (RentalTypeID, RentalTypeName, RentalTypeCost) VALUES (102,'Full Day',50);
INSERT INTO RentalType (RentalTypeID, RentalTypeName, RentalTypeCost) VALUES (103,'Hourly',3);
INSERT INTO RideType (RideTypeID, RideTypeName) VALUES (201, 'Mountain');
INSERT INTO RideType (RideTypeID, RideTypeName) VALUES (202, 'Road');
INSERT INTO BikeSize (BikeSizeID, BikeSizeName) VALUES (301, 'Infant');
INSERT INTO BikeSize (BikeSizeID, BikeSizeName) VALUES (302, 'Child');
INSERT INTO BikeSize (BikeSizeID, BikeSizeName) VALUES (303, 'Teenager');
INSERT INTO BikeSize (BikeSizeID, BikeSizeName) VALUES (304, 'Adult');
INSERT INTO BikeSize (BikeSizeID, BikeSizeName) VALUES (305, 'Standard Adult');
INSERT INTO BikeSize (BikeSizeID, BikeSizeName) VALUES (306, 'Larger Adult');
INSERT INTO Status (StatusID, StatusName) VALUES (401, 'Rented');
INSERT INTO Status (StatusID, StatusName) VALUES (402, 'Sold');
INSERT INTO Status (StatusID, StatusName) VALUES (403, 'Under Maintenance');
INSERT INTO Status (StatusID, StatusName) VALUES (404, 'Scrapped');
INSERT INTO Bike (BikeID, Model, Manufacturer, ManuYear, RideTypeID, BikeSizeID, StatusID) VALUES (1001, '1200 Enduro','Multistrada',2015, 202,305,401);
INSERT INTO Bike (BikeID, Model, Manufacturer, ManuYear, RideTypeID, BikeSizeID, StatusID) VALUES (1002, 'F800 GS','BMW',2016, 201,306,401);
INSERT INTO Bike (BikeID, Model, Manufacturer, ManuYear, RideTypeID, BikeSizeID, StatusID) VALUES (1003, '250 SX-F','KTM',2016, 202,304,403);
INSERT INTO Bike (BikeID, Model, Manufacturer, ManuYear, RideTypeID, BikeSizeID, StatusID) VALUES (1004, 'NINJA ZX-10R','KAWA SAKI',2014, 202,305,401);
INSERT INTO Bike (BikeID, Model, Manufacturer, ManuYear, RideTypeID, BikeSizeID, StatusID) VALUES (1005, 'Ranger RZR','Polaris',2011, 201,306,404);
INSERT INTO Bike (BikeID, Model, Manufacturer, ManuYear, RideTypeID, BikeSizeID, StatusID) VALUES (1006, 'XDiavel','Diavel',2009, 201,303,402);
INSERT INTO Bike (BikeID, Model, Manufacturer, ManuYear, RideTypeID, BikeSizeID, StatusID) VALUES (1007, '946','Vespa',2013, 201,304,403);
INSERT INTO Bike (BikeID, Model, Manufacturer, ManuYear, RideTypeID, BikeSizeID, StatusID) VALUES (1008, 'YZ250','Yamaha',2011, 202,305,401);
INSERT INTO Customer (CustomerID, CustomerName, Address, City, PostCode, Phone, Email) VALUES (2001,'Alanis','6, Green Lane','Washington',8483,'983489498348','[email protected]');
INSERT INTO Customer (CustomerID, CustomerName, Address, City, PostCode, Phone, Email) VALUES (2002,'Edmanda','24, Red Lane','Los Angeles',3453,'23258988952','[email protected]');
INSERT INTO Customer (CustomerID, CustomerName, Address, City, PostCode, Phone, Email) VALUES (2003,'Jameson','72, Yellow Lane','Houston',4578,'62256588258','[email protected]');
INSERT INTO Customer (CustomerID, CustomerName, Address, City, PostCode, Phone, Email) VALUES (2004,'Marleina','45, Pink Lane','San Diego',9687,'2548785993','[email protected]');
INSERT INTO Customer (CustomerID, CustomerName, Address, City, PostCode, Phone, Email) VALUES (2005,'Patrick','81, Blue Lane','Boston',7245,'32248789933','[email protected]');
INSERT INTO Customer (CustomerID, CustomerName, Address, City, PostCode, Phone, Email) VALUES (2006,'Taurina','90, White Lane','Laredo',6354,'32148789932','[email protected]');
INSERT INTO Rental (RentalID, RentalDate, StartTime, EndTime, RentalTypeID, TotalCost, CustomerID, BikeID) VALUES (3001, '04/21/2017','11:00','13:00',103,6,2002,1004);
INSERT INTO Rental (RentalID, RentalDate, StartTime, EndTime, RentalTypeID, TotalCost, CustomerID, BikeID) VALUES (3002, '05/19/2017','08:00','12:00',102,50,2005,1008);
INSERT INTO Rental (RentalID, RentalDate, StartTime, EndTime, RentalTypeID, TotalCost, CustomerID, BikeID) VALUES (3003, '05/19/2017','09:00','12:00',101,30,2003,1002);
INSERT INTO Rental (RentalID, RentalDate, StartTime, EndTime, RentalTypeID, TotalCost, CustomerID, BikeID) VALUES (3004, '05/20/2017','08:00','20:00',102,50,2004,1004);
INSERT INTO Rental (RentalID, RentalDate, StartTime, EndTime, RentalTypeID, TotalCost, CustomerID, BikeID) VALUES (3005, '05/20/2017','13:00','21:00',101,30,2005,1001);
INSERT INTO Rental (RentalID, RentalDate, StartTime, EndTime, RentalTypeID, TotalCost, CustomerID, BikeID) VALUES (3006, '05/21/2017','18:00','21:00',103,9,2001,1002);
SELECT Customer.CustomerName, Rental.RentalDate, Rental.TotalCost FROM Customer INNER JOIN Rental ON Customer.CustomerID=Rental.CustomerID;
SELECT Customer.CustomerName, SUM(Rental.TotalCost) AS OverallRentalCost FROM Customer INNER JOIN Rental ON Customer.CustomerID=Rental.CustomerID
GROUP BY Customer.CustomerName;
Buy B01Dbfn212 Database Fundamentals-Dependency Diagram Answers Assessment Answers Online
Talk to our expert to get the help with B01Dbfn212 Database Fundamentals-Dependency Diagram Answers Assessment Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
