PROBLEM 2: DESIGN A RELATIONAL DATABASE SCHEMA AND IMPLEMENT THEDATABASE. [50] In this problem, you are required
Question:
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.
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.
-
Create the database.
Declare your relations using the SQL DDL.
-
Include your constraints in the DDL: primary keys, foreign keys,unique values, Default, null,
not null, etc.
-
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
-
Add any necessary table that can improve the design of thedatabase and make it more
efficient.
-
Populate the database.
Populate the database using SQL DML.
Use the provided data in order to populate the database.
Avoid any possible data duplication.
-
Answer the following questions using SQL queries and show theresults via postgreSQL (psql or pgAdmin):
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.
List the name and last name, email and cellphone number ofpassengers that have not used the service yet.
List the first name and last name of the drivers that have notworked at all.
List the origin and destination of the most popular trip.
-
Create 5 queries that will be useful for retrieving data fromthis database. Specify the queries and get
a screenshot of the results.
-
Other questions:
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.
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 |
|
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 |