PROBLEM 2: DESIGN A RELATIONAL DATABASE SCHEMA AND IMPLEMENT THEDATABASE. [50]

In this problem, you are required to create a relationaldatabase schema and to create and populate a database using DDL andDML. Write all the SQL statements using PostgreSQL system.

1. Design the relational database schema for the ER Diagram of theCar Ride database (problem 1 of assignment 1). You can use Figure9.2 as an example of a relational database schema. Use theER-to-Relational Mapping Algorithm for creating this schema.

2. Create the database.

   1. Declare your relations using the SQL DDL.

   2. Include your constraints in the DDL: primary keys, foreign keys,unique values, Default, null,

      not null, etc.

   3. Include any necessary column that is missing in the dataprovided that was included in the

      original ER Diagram, for example Passenger ID or add any othernecessary primary key

   4. Add any necessary table that can improve the design of thedatabase and make it more

      efficient.

3. Populate the database.

   1. Populate the database using SQL DML.

   2. Use the provided data in order to populate the database.

   3. Avoid any possible data duplication.

4. Answer the following questions using SQL queries and show theresults via postgreSQL (psql or pgAdmin):

   1. List the passenger first name and last name, car make, carmodel, car color, driver name and last name, pickup date and timeof the passengers with bookings.

   2. List the name and last name, email and cellphone number ofpassengers that have not used the service yet.

   3. List the first name and last name of the drivers that have notworked at all.

   4. List the origin and destination of the most popular trip.

5. Create 5 queries that will be useful for retrieving data fromthis database. Specify the queries and get

   a screenshot of the results.

6. Other questions:

   1. How can you improve the database in order to keep the originalprice paid per ride in case that the prices of trips change in thefuture? You can optionally implement this.

   2. How can you improve the database in order to handle drivers thatdrive more than one car? You can optionally implement this.

Create a single sql for the creation and population of theairline database. For example: create_airline_db.sql

This is the ER schema of the car ride

Use this information to insert data in thedatabase:

PASSENGER

FName	LName	Street	City	State	ZipCode	CellPhone	Email
Anne	Roberts	123 Thomas St.	Toledo	OH	57556	801-556-2239	AR@test.com
Robert	Schulls	234 Pines St.	Los Angeles	CA	34898	801-552-2943	RoSh@test.com
John	Peters	345 Star St.	Raleigh	NC	79999	801-393-2230	JPet@test.com
Bryan	Brown	435 Palm St.	Miami	FL	30533	801-933-2320	Bryan1@test.com
Mark	Williams	348 Andrew St.	Fort Lauderdale	FL	33318	801-343-2320	mw@test.com
Carol	Phillips	395 Pine St.	Omaha	NE	88899	801-323-2320	carolp@test.com
Madison	Parker	285 Diamond St.	San Diego	CA	99977	801-493-2203	NULL
Justin	Colano	223 Easy St.	Tampa	FL	66798	801-193-2320	JCol@test.com
Claudia	Stevens	775 Main St.	Saint Louis	MO	99878	801-303-2222	ClauS@test.com
Arthur	Hooper	456 Rose St.	Las Vegas	NV	17878	313-912-2101	ahoop@test.com
Sergio	Ryan	567 Spruce St.	Lincoln	NE	87898	801-228-6729	sergior@test.com
Julia	Maverick	678 Tulip St.	Raleigh	NC	79999	313-888-2497	NULL
Brandon	Gordon	789 First St.	Miami	FL	30533	754-111-1111	bgor@test.com
Maurice	Vernon	49s Mark St.	Hollywood	FL	33252	954-954-9541	NULL

PAYMENT

ID	PaymentType	CardNum	ExpiryDate
001	CreditCard	546876546546	07/24
002	CreditCard	865498479879	01/23
003	CreditCard	064068489999	07/25
004	CreditCard	540654865761	08/27
005	CreditCard	464899843110	10/26
006	CreditCard	648984946554	11/27
007	CreditCard	654898988788	03/23
008	CreditCard	654899879788	04/28

last name name email passenger id cell phone number city address Passenger (1M) makes Reservation reservation date_time zipcode has for makes payment i tip id date Trip (1.1) start_date_time has HOME price amount Payment 71 (1,1) estimated and Date destination type type-card- made on (1,1) Car type card_number Credit Card (1.1) last name name driver id registration _number expiry_date phone_number Driver T (1,1) driven by email license_number