Reference no: EM133830156
Homework: Database Project
Objectives
1) Demonstrate conceptual design principles
2) Analyzing data requirements to determine data entities and attributes.
3) Analyzing entities and attributes to determine their relationships.
4) Developing and creating an entity relationship diagram for modeling a database.
5) Executing the general methods of design using 3NF, to normalize a database.
6) Writing SQL statements to create simple tables.
7) Creating data integrity controls.
8) Using various schemes for determining and modifying dimension attribute values.
9) Demonstrate an understanding of basic SQL (Standard Query Language) syntax.
10) Ability to optimize information retrieval.
11) Ability to create a database.
Guidelines
Project
The project is broken down to three-part. This has been done so students will have the opportunity to work in an incremental bases, hence avoid being overwhelmed by this homework. At the completion of the three parts, the student will receive one Project Grade which is an average of the three components that make up the project.
1) The tasks are:
2) Create the ERD for Project #3 from your textbook.
3) Utilizing the ERD developed, for Project #3, create the database, conduct normalization, relationships, and tables.
4) Run queries indicate in the database created.
The Project should have the following (Click on each Tab):
Part A: Project ERD
This project is based on eight tables (AIRPORT, FLIGHT, DEPARTURES, PASSENGER, RESERVATION, EQUIP_TYPE, PILOTS, and TICKET) that contain data about Belle Airlines. Download and use data in Project 3 zip file for this project.
Some Background on Belle Airlines
Belle Airlines is a regional carrier that operates primarily in the southwestern United States. At the present time, Belle Airlines operates its own reservation information system. To simplify our analysis, we will assume that all reservations on Belle Airlines flights are placed through Belle Airlines employees. Flights are not booked through travel agents and Belle Airlines does not participate in industry-wide reservations services. Each flight is assigned a unique flight number and has its own set of flight characteristics (ie. flight number, origin, destination, departure time, arrival time, meal code, base fare, mileage between origin and destination, and the number of changes in time zone between the origin and destination of the flight). Departures of each flight are stored in the Departures table. Each departure contains four attributes (flight number, departure date, pilot id, and equipment number).
Belle Airlines flies out of airports located all over the country. Data on these airports is stored in the Airport table. Data on these airports include a three-character airport code, location of the airport, elevation, phone number, hub airlines that operates out of the airport. Since Belle Airlines flies out of airports located all over the country, Belle Airlines pilots live all over the country. Data on these pilots is stored in the Pilots table, which contains the following attributes: pilot id, pilot name, social security number, street address, city, state, zip code, flight pay, date of birth, and date hired. The company also owns its own fleet of airplanes. Data on these airplanes is stored in the Equip_Type table which contains the following attributes: equipment number, equipment type, seating capacity, fuel capacity, and miles per gallon.
Three additional tables populate the Belle Airlines database: the Passenger table (with attributes: passenger name, itinerary number, and confirmation number), the Reservation table (with attributes: confirmation number, reservation date, reservation name, reservation phone, reservation flight number, and reservation flight date), and the Ticket table (with attributes: itinerary number, flight number, flight date, and seat homework).
Instructions:
Read "Project Details" above
Utilize "Google Draw" or draw.io to: Create a set of ERD diagrams (Presentation ERD, UML Diagram, and Logical Schema OR Conceptual ERD, Logical ERD, and Physical ERD)
1) Create Presentation Layer ERD (File Name: first name + last name + title, i.e., MSmith_EERD)
2) Create a UML Diagram (File Name: first name + last name + title, i.e., MSmith_UML)
3) Convert the EERD (Extended Entity Relational Data Model) into your logical schema (File Name: first name + last name + title, i.e., MSmith_LogicalSchema)
Part B: Database Normalization
Next, using the ERD, UML, Logical Diagram, and database information complete
Utilize the materials and resources from part 1, and the attached resources:
1) Create the database if using Oracle, SQL or Access. Make sure to use the raw data for Oracle or SQL.
2) Identify functional dependencies and derive candidate keys, and
3) Follow the normalization process to determine tables and relationships, and
4) Ensure the database is normalized, to 3NF.
Download the following "raw data" to create the database raw data file attached.
DOWNLOAD AND INSTALL ORACLE IN YOUR COMPUTER:
1) Go to oracle
2) Click on downloads
3) Select database downloads
4) Under the database, category choose Oracle 11g express edition
5) Click you accept the agreement
6) Download the appropriate version (for windows either the 32-bit or 64-bit version)
7) Note: Create an account if you do not have one
8) The system should be in a zipped folder when downloaded. Locate this folder and extract the contents
9) Open the extracted folder and run the setup executable
10) Click next
11) Accept the license agreement
12) Click next twice
13) Enter a password of your choice
14) Click next
15) Click Install
16) Click Finish
HOW TO USE ORACLE:
1) Open cmd
2) C:\>sqlplus system/password (note password is the password you entered during installation)
3) Set echo on
4) To run the script: at the prompt @filename (create script and drag file to console)
5) Spool filename (create file.txt and drag to console)
6) Spool off
7) When finished type exit to quit sqlplus
Part C: SQl w/Database
Test Your Database the SQL Queries and Submit Reports w/Database
Run the following SQL Queries in the database you have created for project and submit reports:
1) Display the origin, destination, departure time from origin, and arrival time at destination for all flights that occur in the same time zone. Your results should be displayed in order by flight number.
2) Display the code, location, and elevation of all airports without a hub airline. Your results should be in descending order by elevation.
3) Display the departures originating from Los Angeles, CA. Include in your results flights from Los Angeles for which no departures currently exist. Los Angeles, CA and not LAX should be used in the WHERE clause of your query.
4) Display the flight numbers and the codes for the origins and destinations of all flight reservations made by Andy Anderson.
5) Display the seating capacity, fuel capacity, and miles per gallon for all aircraft manufactured by Boeing. Information about each equipment type should be displayed only once.
6) Display the names of all pilots who live outside of the state of Texas. Order the results in alphabetical order by last name.
7) Display the flight number, flight date, fare, origin, and destination for all tickets with a flight date of July 2006. Use the fare in the FLIGHT table as the fare for the ticket. Order your results in ascending order by flight date and within flight date by flight number.
8) Display all flights that originate at an airport without a hub airline.
9) Display all flights that arrive at an airport without a hub airline.
10) Display all flights that both originate and arrive at an airport without a hub airline.
11) Display all departures that are flown by an aircraft not manufactured by Boeing. Your results should be in ascending order by departure date and within departure date by flight number.
12) Display the distance divided by the fare for each flight. For each flight, display the flight number, the origin, the destination, the fare, and the quotient. Your results should be in descending order by the quotient and rounded to two places to the right of the decimal point. Create a descriptive column alias for the quotient.
13) Display the total number of flights that originate from each point of origin.
14) Revise the previous query so that instead of displaying the code for each point the location of each point of origin from the AIRPORT table is displayed.
15) Revise the previous query to also include the display of those locations where no flights originate.
16) Display the average flight pay for pilots that live in each state.
17) Display the name and flight pay for those pilots whose flight pay exceeds the average flight pay for all pilots.
18) Display the name and flight pay for those pilots whose flight pay exceeds the average flight pay for all pilots in the state in which they reside.
19) Display the date of the most recent departure flown by each pilot. Include in what you display the name of the pilot.
20) Display not only the date of the most recent departure by each pilot but also the number of days since the last departure date. Truncate the number of days (i.e., if 37.67655, display 37) to zero places to the right of the decimal point. Order the result in descending order by the number of days.
21) Display the number of departures that involve flights for each of the three time zone differences.
22) Display the number of airports located in each state.
23) Display the number of departures where the distance flown is greater than or equal to 1000 miles.
24) Display the difference in age between the oldest and youngest pilot.
25) For each type of aircraft, display the total distance that can be flown before refueling. Display your results in descending order by total distance that can be flown.
26) For each passenger listed in the PASSENGER table, display the name of the person responsible for his or her reservation.
27) For each passenger listed in the PASSENGER table, display the name of the person responsible for his or her reservation only if the passenger himself or herself was not responsible for making the reservation.
28) For each reservation in the RESERVATION table, display the name of the pilot who will be piloting the flight.
29) Display those tickets that include only one flight.
30) Display the name of the passengers whose tickets include only one flight.