Project Proposal Template Instructions Download This Form

Project Proposal Templateinstructions Download This Form Or Create A

Download this form or create a similar one in a new document.

Paper For Above instruction

This assignment involves designing a relational database for an airline industry scenario. You are tasked with creating a database to record airlines and pilots, based on initial entities discussed with industry representatives. The core entities are Airlines and Pilots, with specific attributes and key points outlined, such as PilotID being unique for pilots and AirlineID for airlines, and the relationship that each pilot works for one airline while each airline can have multiple pilots. The database design must be normalized through at least the third normal form, applying functional dependencies, and ensuring data integrity.

Your submission will include a Word-compatible document detailing your design process, including assumptions, entity identification, normalization steps, and creation of the database. Additionally, you will implement the design in Microsoft Access, creating a database named with your last name and term project, populating it with sample data, and developing queries, forms, and reports based on functional and normalization requirements. The queries should list pilots sorted by their IDs, the form should allow input of new pilot data, and the report should display all pilots with their names and salaries. Final submission must use clear organization, proper spelling, and grammar.

Paper For Above instruction

Designing a relational database for an airline industry scenario involves a systematic process of understanding data requirements, defining entities, ensuring data integrity through normalization, and implementing the design practically within database software. This process begins with a careful review of the provided report, functional dependencies, and sample data, leading to a clear conceptual model that supports data consistency and future scalability. The following sections detail each step, illustrating how theoretical concepts translate into a practical database solution.

1. Assumptions and Initial Entities

Based on the provided report and functional dependencies, I assume that each pilot is uniquely identifiable by a PilotID, which simplifies the identification of each employee. The airline entity is defined by a unique AirlineID, and attributes such as NPlanes, NRoutes, and NPilots are attributes that describe each airline’s operational scope. It is also assumed that each pilot works exclusively for one airline, and this relationship is one-to-many from airline to pilots. The data suggests that there are no additional entities beyond pilots and airlines necessary initially, but if future data expands, additional entities like routes or planes could be included. Each pilot’s salary and job title are personal attributes, whereas airline attributes include operational data.

2. Functional Dependency Explanation

A functional dependency is a relationship where the value of one set of attributes determines the value of another set. For example, in the provided dependencies, PilotID → FName, LName, JobTitle, Salary, AirlineID, NPlanes, NRoutes, NPilots indicates that for each unique PilotID, the associated first name, last name, job title, salary, airline ID, and airline attributes are uniquely determined. This means that knowing a pilot’s ID allows us to identify all other details associated with that pilot without ambiguity. Essentially, PilotID is the primary key that functionally determines all other attributes in the table.

3. Entity Relationship Explanation

The relationship between the initial entities—pilots and airlines—is a one-to-many relationship. Specifically, one airline can have many pilots, but each pilot is associated with only one airline. This is evident because each PilotID is linked to one AirlineID, but an AirlineID can correspond to multiple PilotIDs. This multiplicity emphasizes that the airline entity is the "one" side, and pilots form the "many" side of the relationship, aligning with standard one-to-many modeling in relational databases.

4. Database Design and Normalization Process

First Normal Form (1NF)

According to the standard, a table is in 1NF if all fields contain atomic (indivisible) values, and each record is unique. The initial schema—PilotAirlines (PilotID, FName, LName, JobTitle, Salary, AirlineID, NPlanes, NRoutes, NPilots)—appears to conform to 1NF because each field contains single atomic values. There are no repeating groups or multi-valued attributes. To ensure compliance, verify that no fields contain multiple values, such as multiple NRoutes stored in a single field.

Assessment shows the table meets 1NF as all attributes are scalar. No modifications are required.

Second Normal Form (2NF)

2NF requires that all non-key attributes are fully functionally dependent on the primary key. The primary key in this case could be PilotID, which determines athlete attributes and airline-related metrics, but airline attributes like NPlanes, NRoutes, and NPilots depend only on AirlineID. Since these are included in the same table, partial dependency exists, violating 2NF. To normalize, separate airline-specific data into its own table, linked via AirlineID.

Third Normal Form (3NF)

3NF requires that all attributes are only dependent on the primary key, with no transitive dependencies. After decomposing airline attributes into a separate table, each table will only contain attributes directly dependent on its primary key acronym, PilotID or AirlineID, thereby satisfying 3NF.

5. Normalized Tables

Following normalization steps, the design culminates in the following tables:

  • Pilots: (PilotID, FName, LName, JobTitle, Salary, AirlineID)
  • Airlines: (AirlineID, NPlanes, NRoutes, NPilots)

Both tables are in 3NF, with primary keys functioning as unique identifiers, and dependencies fully functional.

6. Implementation in MS Access

Using the normalized tables, I will create a new MS Access database named "LastName_TermProject," and load it with sample data reflective of realistic values. Establishing relationships between the two tables via AirlineID enforces referential integrity. The database schema will include these tables, their attributes, and primary/foreign keys, ensuring data is appropriately structured and consistent across the system.

7. Query: Listing Pilots by ID

A SQL query will be designed to list each PilotID along with the pilot’s first and last names and the airline each works for, sorted by PilotID:

SELECT p.PilotID, p.FName, p.LName, a.AirlineID

FROM Pilots AS p


JOIN Airlines AS a ON p.AirlineID = a.AirlineID


ORDER BY p.PilotID;

8. Form for New Pilot Input

I will create a form titled "New Pilot Input" that allows users to enter new pilot data, including PilotID, first name, last name, job title, salary, and associated AirlineID, with validation to ensure correct data entry and referential integrity.

9. Report: Pilot List

The report, named "Pilot List," will display all pilots, showing first name, last name, and salary, formatted for clarity. This report provides a comprehensive view of pilot data for managerial review or operational purposes.

10. Submission and Organization

The final submission will include the documentation of the design process, normalization steps, and the MS Access database file containing the data, queries, forms, and reports. The entire project is organized clearly, with proper spelling and grammar, demonstrating thorough understanding and effective application of database design principles.

References

  • Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387.

Related Assignments