Dashboard Data 620 Assignment 31 Your Name Professor Date

Dashboardumuc Data 620assignment 31your Nameprofessordateproblemyo

Create an Entity Relationship diagram for the airlines database using ER Assistant, including entities, keys, and relationships with cardinalities, and add descriptions for each relationship. Submit a screenshot and the ER Assistant file.

Write SQL code to list all flights departing from Atlanta, showing flight ID, origin and destination airport codes, ordered alphabetically by destination airport code. Submit the code and a screenshot of the results.

Add "Frontier Airlines" to the list of carriers in the database with SQL, providing "before" and "after" screenshots to demonstrate the addition. Ensure the new carrier remains in the database for subsequent queries.

Update the carrier name from "Southwest" to "Southwest Airlines" using SQL, with screenshots showing "before" and "after" results to confirm the change. Keep the updated name throughout the remaining tasks.

List each airline carrier with the total number of flights they have operated, including carriers with zero flights. Sort the results with the highest number of flights at the top. Include the SQL query and corresponding results with screenshots.

Count the number of flights flown by each aircraft type, combining manufacturer and model into a single field (e.g., "Boeing – 787 Dreamliner"), and sort from most to fewest. Show the SQL code and execution results with screenshots.

Remove the flight scheduled from Seattle to Philadelphia on July 5th at 23:38, showing "before" and "after" screenshots to verify the deletion. Ensure the flight remains deleted in subsequent operations.

Generate a list of upcoming flights, including origin and destination airport codes, departure and arrival times, airline, manufacturer, and model, only for flights in the future. Sort the list by the soonest departure times and include the SQL code, results, and the date/time of query execution.

Identify aircraft manufactured and purchased between Jan 1, 2010, and Dec 31, 2015, and list their combined aircraft type and total seat count, formatted to two decimal places. Use the results to estimate how many seat coverings need ordering. Present SQL, results, and reasoning.

Create a flat file with the first five flights from the airlines database, including detailed and assumed data fields as specified. Write a memo outlining concerns about transferring data management from relational databases to flat files, discussing advantages and disadvantages, and potential problematic scenarios.

Paper For Above instruction

The airline industry relies heavily on robust and efficient data management systems to ensure operational efficiency, safety, and strategic planning. The assignment encompasses designing a comprehensive Entity Relationship Diagram (ERD), writing and executing SQL queries for specific data retrieval and modification tasks, and assessing data management approaches. This process demonstrates critical understanding of relational database design, query formulation, data integrity, and the pros and cons of different data formats.

The first task involves creating an ERD for the airlines database using ER Assistant. The ERD must detail entities such as aircraft, airports, carriers, and flights, highlighting their primary keys and relationships with proper cardinality annotations. For example, a relationship might be "each flight departs from one airport and arrives at another," with appropriate one-to-many or many-to-one cardinalities. Clear relationship descriptions aid in understanding the data structure and support normalization.

The second task requires developing an SQL statement to retrieve all flights departing from Atlanta, regardless of whether they are past or future flights. The query should accurately join the flights and airports tables to extract flight ID, origin, and destination airport codes, ordered by destination. Proper use of joins and where clauses ensures reliable data extraction. Execution screenshots serve as verification of query correctness.

The third and fourth tasks involve updating the carrier list: adding "Frontier Airlines" and renaming "Southwest" to "Southwest Airlines." These modifications highlight essential SQL data manipulation skills, including INSERT and UPDATE statements. Demonstrating the changes with before-and-after screenshots confirms successful updates. Maintaining these changes ensures subsequent queries and reports reflect current data accurately.

The fifth task is to generate a report listing each airline with the total number of flights operated, including airlines with zero flights by performing a LEFT JOIN or aggregation with COUNT. Sorting results from highest to lowest reveals operational volume, guides business insights, and supports decision-making.

The sixth task focuses on analyzing aircraft types. It involves concatenating manufacturer and model fields into a single descriptive column, counting total flights per aircraft type, and ordering from most frequent to least. This information aids in fleet management and maintenance planning.

Subsequently, the removal of a specific scheduled flight tests deletion commands, requiring before-and-after screenshots to validate successful action, which helps simulate real-time data management adjustments.

The eighth task is a complex query that lists upcoming flights, filtering for future departure times relative to the current date/time. Sorting by earliest departure ensures actionable scheduling insights. Commentary in the SQL clarifies assumptions and logic used.

The ninth task involves identifying aircraft purchased within a specified date range, calculating their total seat capacity, and determining how many seat coverings are needed, given an issue with the fabric's fire-retardant properties. The query needs to aggregate data accurately and present the results with appropriate formatting, facilitating inventory planning.

The final task compares relational database utility with flat file formats. Constructing a simplified flat file containing the first five flights, along with comprehensive assumptions, serves as a foundation for a memo. The memo critically evaluates advantages such as simplicity and portability versus drawbacks like limited data integrity, difficulty in updates, and challenges in maintaining relationships. Discussing real-world implications and potential scenarios highlights risks and benefits of transitioning data systems.

References

• Date, C. J. (2019). Database Design and Relational Theory: Normal Forms and Beyond. O'Reilly Media.
• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
• Database System Concepts (7th ed.). McGraw-Hill Education.
• Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson.
• Rob, P., & Coronel, C. (2007). Database Systems: Design, Implementation, & Management. Cengage Learning.
• Connolly, T., & Begg, C. (2014). Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.
• Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, and Management. Cengage Learning.
• Dayley, B. (2020). SQL Fundamentals. McGraw Hill.
• Pratt, T., & Adamski, G. (2019). Beginning Database Design. Course Technology.
• Corbett, J., & Nagaraj, S. (2018). SQL for Data Analysis. O'Reilly Media.