Assignment
In this problem, you are required to create a relational database schema and to create a database using DDL and DML. Write all the SQL statements using PostgreSQL system.
1. Design the relational database schema for the ER Diagram of the airline database (problem 2 of assignment 1). You can use Figure 9.2 as an example of a relational database schema. Use the ER-to-Relational Mapping Algorithm for creating this schema.
2. Create the airline database.
a. Declare your relations using the SQL DDL. ?
b. 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 from the original ER Diagram, for example customer's ?ID or customer's number.
4. Populate the airline database
a. Populate the database using SQL DML. ?
b. Use the provided tables as the data to insert in the database.
5. Answer
a. List the departure date, flight number and the number of booked customers. ?
b. List the pilot names that can fly a A310 plane type. ?
c. List the departure date and flight number that never got any personnel assigned.
6. Create 5 more queries that will be useful for retrieving data from this database. Specify the queries and get a screenshot of the results.
7. Implement your database using PostgreSQL.
Create a single sql for the creation and population of the airline database.
Submit a document that contains each query and a screenshot of the result of the queries that you create. Also send the sql files (one query per file (name each file Assig2_P2Q.sql)- zip them all) using the Canvas system.
You can submit all the files from this assignment in one single zip file the following queries a few queries using PostgreSQL (psql or pgAdmin).
format: "xyzassignment2" (xyz are your initials) using the canvas system. Embed any graph in the with the name of the file using this document.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- Assignment-DB-SQL.rar