IT 214 NOVA Car Rental Service MySQL Project . You must use MySQL to implement the database you designed in Part I. Test the queries you used to create and

IT 214 NOVA Car Rental Service MySQL Project . You must use MySQL to implement the database you designed in Part I. Test the queries you used to create and populate the database in MySQL. After successful completion of an SQL query:

Type your name at the following mysql> prompt.
Take a screen shot (Alt + Print Screen) that includes the query, the confirmation message, the results (if any), and your name.
Paste the screen shot in the appropriate location in your MS Word file.

IMPORTANT NOTICE: If some of the tables in your database have the same names as the tables in the databases used in class (Premiere Products, Henry Books, and Alexamara Marina Group), re-name your tables. For instance, if the name of your table is CUSTOMER, change it to CUSTOMER_1. Do not delete existing tables from the databases used in class otherwise you will not be able to complete the rest of the homeworks.

Part II must include:

1.The title page with the course number, section number, and your name.

2.The ERD of the database you designed in Part 1. If you have to make changes in the structure of the database, you must receive your professor’s approval before you implement them.

3.Screen shots of the queries used to create tables and populate them with data. For each table, include the following:

a.A screen shot(s) of the SQL query you used to create the table with appropriate attributes and data types together with primary keys, foreign keys, relationships and constraints.

b.A screen shot(s) of the DESCRIBE table_name; command to show the structure of the table.

c.A screen shot(s) of all SQL queries you used to populate the table with valid data. Each table must contain a minimum of 3 records.

d.A screen shot(s) of the SELECT * command to show the resulting table with all the valid data inserted.

Paste all screen shots from parts a-d for each table together in one place.

4.Screen shots of SQL queries you used to retrieve data from the tables along with the query results. For each query, explain in a complete, coherent sentence what it is intended to do, what data is expected as a result and how they are different from previous queries. Clearly identify each screen shot you paste into this section. To create queries, use the following structures:

A query that is based on two tables and includes both an arithmetic and a special operator in the conditional expression.
A query that is based on more than two tables and includes both an arithmetic and a special operator in the conditional expression. Both the arithmetic and the special operator must be different from the ones used in part a.
A query that includes a sub-query.
A query that contains the GROUP BY clause, one of the aggregate functions, the WHERE statement, and the HAVING operator.
Two additional complex queries designed by you that are different from the above. A complex query is based on two or more tables and includes either several conditions or a subquery (or both). Project 1 Final
“Car Rental Service”
Description of the Project and its Scope:
“Car Rental Service” is a service given by a Car Rental company that rents cars for brief
timeframes. “Car Rental Service” permits individuals to rent a vehicle for a couple of hours to
half a month or can be a month. Each individual can rent a car depending upon what kind of car
they want; as car rental company provides a different model of cars. The company has a different
location where individuals can pick up and drop off the car in any location they want. If the
individual returns the car after the due date, they will be charged a late fee. The renter can also
buy insurance to protect their rental car; which is totally optional. The company feels its
commitment to help each individual in their time of need, their thought process is to make the
clients (renter) life simple alongside the company itself. Every individual dealing with a vehicle
rental company has an immense amount of knowledge when it comes to problem-solving and
communicating. The company might want to have a database to monitor its vehicles. “Car Rental
Services” today, carry more than just one vehicle, it provides various vehicles of various models
so that the renters will have an opportunity to pick the car according to their comfortability. They
might likewise want to have each type of vehicle and its model to be spoken to in the database.
This will permit the company to create reports summarizing the services that they provided.
The scope of the project consists of the following sections:
• Car Type
• Car Model
• Renters
The car entity section describes various types of lists of cars are provided by the company. Each
car will be related to a classification of cars and cars will have attributes like make, model,
mileage, etc. The car entity section describes various models of car is leased by the company.
For every car, the car code and the description of the car are recorded.
The car rental service provides plenty of cars of various models. The car model section describes
that each car has its own car type. For each car model, the car type, car name, price, and register
number of cars must be recorded. Cost is determined dependent on the car type. The car type will
have attributes like name, registration number, and make of the car.
The renter section describes the customer will be the person who will utilize a car rental service
to rent a car. The customer entity will store subtleties like customer driving license number,
email address, home address, first name, last name, SSN and telephone number.
Business Rules:
Every single “car rental service”, the company is supposed to have lot of car collections of
different models and its make. Sometimes, renters are exceptionally specific about car makes and
their models. In any case, a few people are more specific in one viewpoint than another. While
picking a specific car model, the renters naturally pick the make of that vehicle. Thus, it is
important for a company to collect a lot of cars of different models as well as its make.

All car models share common attributes like the car type, car name, price, and number of
cars. But some car models have a unique attribute, for instance, registration of the cars,
make of the cars and the mileage of the cars.

Each car could be rented by many renters however each car must have a unique name, or
a code related to it (Example registration number).

Every car must have a place with a specific car type and every car will have many places
with a different location for drop off and pick up.
Entities and Attributes:
Entity #1: CAR_TYPE
The CAR_TYPE entity will contain information that describes different types of cars.
Attribute #1 (PK): CAR_TYPE_CODE
Description: CAR_TYPE_CODE is a unique identifier that will be used as a reference in other
tables.
Data Type: Text.
Sample Data: MAKE_MODEL, CAR_ID, CAR_MODEL, etc.
Attribute #2: CAR_TYPE_DESC
Description: CAR_TYPE_DESC will be the name of the car type.
Data Type: Text.
Sample Data: OWNED_CAR, NEW_CAR, OLD_CAR, etc.
Entity #2: CAR MODEL
Since not all car models share basic qualities, there is a requirement for the specialization chain
of command. The CAR MODEL entity will incorporate attributes basic to all the CARS. In
expansion, two sub-entities must be made to incorporate unique information about cars and their
models.
Attribute #1 (PK): CAR_MODEL_ID
Description: CAR_MODEL_ID is a unique identifier that will be used as a reference in other
tables.
Data Type: Numeric
Sample Values: 100, 200, 300, etc.
Attribute #2: CAR_MODEL_NAME
Description: CAR_MODEL _NAME will be the name of the car.
Data Type: Text.
Sample Values: Mustang, Pontiac, Prius, Focus, and corolla, Camry, etc.
Attribute#3: CAR_MODEL_PRICE
Description: CAR_MODEL _PRICE will represent the price of the car.
Data Type: Numeric
Sample Values: $20/hrs., $15/hrs., etc.
Attribute #4 (FK): CAR_MODEL _CODE
Description: CAR_MODEL _CODE is a foreign key column that references the CAR_MODEL
table.
Data Type: Text
Sample Values: RC, LC, UX, etc.
Entity #3: Locations
Attribute #1(PK): LOCATION_ID
Description: LOCATION_ID is a unique identifier that will be used as a reference in
other tables.
Data type: Numbers
Sample values: Location 1, 2, 3,.
Attribute #2: LOCATION_ADDRESS
Description: LOCATION_ADDRESS will be the address where the renters will drop or pick the
car.
Data Type: Text
Sample Values: Gemstone CT, valley view, etc.
Attribute #3: LOCATION_STATE
Description: LOCATION_STATE will be the state where the renters will drop or pick the car.
Data Type: Text
Sample Values: VA, NC, TX, ETC
Attribute #2: LOCATION_CODE
Description: LOCATION_CODE will be the zipcode where the renters will drop or pick the car.
Data Type: Number
Sample Values: 22030, 22033,22124
Entity #4: CAR MAKES
Attribute #1 (PK): CAR_MAKES_ID
Description: CAR_MAKES_ID is a unique identifier that will be used as a reference in other
tables.
Data Type: Number
Sample Values: 2011, 2012, 2019, 2020, etc.
Attribute #2 : CAR_MAKES_NAME
Description: CAR_MAKES_ID is a unique identifier that will be used as a reference in other
tables.
Data Type: Text
Sample Values: Toyota, Ford, Honda, Hyundai-Kia, Nissan, Ferrari, Chevrolet, etc
Entity #5: RENTER
The renter’s entity will contain information that describes renters.
Attribute #1 (PK): RENTER _ID
Description: RENTERS _ID is a unique identifier that will be used as a reference in
other tables.
Data Type: Numeric
Sample Values: 1232, 2234, 4453, etc.
Attribute #2: RENTER _LNAME
Description: RENTERS _LNAME will contain the last name of the renters.
Data Type: Text
Sample Values: Lesando. Gomez, etc.
Attribute #3: RENTER _FNAME
Description: RENTERS _FNAME will contain the first name of the renters.
Data Type: Text
Sample Values: Bob, Matt, Rubia, etc.
Attribute #3: RENTERS _CONTACT
Description: RENTERS _CONTACT will contain the contact information of the
Renter.
Data Type: Text
Sample Values: boblesando@gmail.com
Entities and Relationships:
Relationship: CAR_TYPE “includes” CAR
Relationship type: 1:M
Explanation: business rules state, “Each car belongs to one and only car type. Each car has
different car types. The CAR_TYPE entity is on the “one” side of the relationship and the CAR
entity is on the “many” sides of the relationship.
Relationship participation: mandatory on the “one” side of the relationship and mandatory on the
“many” side of the relationship.
Explanation: The CAR_TYPE entity is mandatory in the relationship because “Each car belongs
to one and only one car type”. The CAR entity is mandatory in the relationship because “Each
car type may include many cars”.
Cardinality:
CAR_TYPE is (1:1)
CAR is (1:M)
Relationship strength: Weak
• Explanation: The relationship is weak because the primary key of the CAR entity does
not contain the primary key of the CAR_TYPE entity.
Relationship: CAR “is a” MAKE_MODEL
Relationship type: 1:1
• Explanation: Since the CAR entity is a super-entity and the MAKE_MODEL entity is a
sub-entity, there is a 1:1 relationship between them.
Relationship participation: NA
Relationship strength: Strong
• Explanation: The relationship is strong because the primary key of the MAKE_MODEL
entity contains the primary key of the CAR entity.
Relationship: CAR “is a” MAKE_MODEL
Relationship type: 1:1
• Explanation: Since the CAR entity is a super-entity and the MAKE_MODEL entity is a
sub-entity, there is a 1:1 relationship between them.
Relationship participation: NA
Relationship strength: Strong
• Explanation: The relationship is strong because the primary key of the MAKE_MODEL
entity contains the primary key of the CAR entity.
Relationship: RENTERS “Rents” CAR
Relationship type: 1:M

Explanation: Business rules state, “Each CAR can have many renters, but each CAR
must have at least one renters name associated with it”. The RENTER entity is on the
“one” side of the relationship and the CAR entity is on the “many” sides of the
relationship.
Relationship participation: Mandatory on the “one” side of the relationship and mandatory on
the “many” sides of the relationship.
• Explanation: The RENTER entity is mandatory in the relationship because “Each CAR
can have many renters, but each CAR must have at least one renters name associated with
it”. The CAR entity is mandatory in the relationship because “Each renter can rent many
CARS”.
Cardinality:
• RENTER is (1:1)
• CAR is (1:M)
Relationship strength: Weak
• Explanation: The relationship is weak because the primary key of the CAR entity does
not contain the primary key of the RENTER entity.
ERD:
IT 214: Database Fundamentals
Project Description: Part II
Part II:
Part II is 40% of the total grade of the project. You must use MySQL to implement the database you
designed in Part I. Test the queries you used to create and populate the database in MySQL. After
successful completion of an SQL query:
• Type your name at the following mysql> prompt.
• Take a screen shot (Alt + Print Screen) that includes the query, the confirmation message, the
results (if any), and your name.
• Paste the screen shot in the appropriate location in your MS Word file.
IMPORTANT NOTICE: If some of the tables in your database have the same names as the tables in the
databases used in class (Premiere Products, Henry Books, and Alexamara Marina Group), re-name your
tables. For instance, if the name of your table is CUSTOMER, change it to CUSTOMER_1. Do not
delete existing tables from the databases used in class otherwise you will not be able to complete the rest
of the homeworks.
Part II must include:
1. The title page with the course number, section number, and your name.
2. The ERD of the database you designed in Part 1. If you have to make changes in the structure of
the database, you must receive your professor’s approval before you implement them.
3. Screen shots of the queries used to create tables and populate them with data. For each table,
include the following:
a. A screen shot(s) of the SQL query you used to create the table with appropriate attributes
and data types together with primary keys, foreign keys, relationships and constraints.
b. A screen shot(s) of the DESCRIBE table_name; command to show the structure of the
table.
c. A screen shot(s) of all SQL queries you used to populate the table with valid data. Each table
must contain a minimum of 3 records.
d. A screen shot(s) of the SELECT * command to show the resulting table with all the valid
data inserted.
Paste all screen shots from parts a-d for each table together in one place.
4. Screen shots of SQL queries you used to retrieve data from the tables along with the query
results. For each query, explain in a complete, coherent sentence what it is intended to do, what
data is expected as a result and how they are different from previous queries. Clearly identify
each screen shot you paste into this section. To create queries, use the following structures:
a. A query that is based on two tables and includes both an arithmetic and a special operator
in the conditional expression.
b. A query that is based on more than two tables and includes both an arithmetic and a
special operator in the conditional expression. Both the arithmetic and the special
operator must be different from the ones used in part a.
c. A query that includes a sub-query.
d. A query that contains the GROUP BY clause, one of the aggregate functions, the
WHERE statement, and the HAVING operator.
e. Two additional complex queries designed by you that are different from the above. A
complex query is based on two or more tables and includes either several conditions or a
subquery (or both).
CAR_TYPE
PK CAR_TYPE_ID
CAR
(1,M)
(1,M)
Includes
CAR_TYPE_CODE
|.(1,1)
CAR_TYPE_DESC
FK CAR_MODEL_CODE
PK CAR_ID
FK CAR_MODE_ID
FK CAR_MAKE
(1,MFK RENTER_ID
Rents
Is a
Is a
RENTER
PK RENTER ID
# (1,1)
(1,M]

CAR_MAKE
RENTER_LNAME
RENTER_FNAME
(1,1)
PK CAR_MAKE_ID
CAR_MAKE_NAME
(1,1)
CAR_MODEL
(1,1)
Drops/pickup
PK CAR_MODEL_ID
LOCATION
PK LOCATION_ID
LOCATION_ADDRESS
LOCATION_STATE
LOCATION_CODE
FK1 CAR_MODEL_CODE
CAR_MODEL_PRICE
(1.M
CAR_MODEL_LNAME
CAR_MODEL_FNAME
CAR_MODEL_PRICE
(1,M)

Purchase answer to see full
attachment

Submit a Comment