Term Project - Week 5: You Were Just Hired By An Airline

Term Project - week %): You were just hired by an airline industry to create a database to record the various airlines and the pilots that work for them

Develop a comprehensive database design for an airline industry application based on provided entities, functional dependencies, and specified requirements. The task involves analyzing the existing report, defining dependencies, relationships, normalization processes, creating appropriate database tables at various normalization levels, and implementing the design in Microsoft Access. Your submission should include documentation of the design process, creation of the database, sample data loading, and development of queries, forms, and reports as specified.

Paper For Above instruction

1. Assumptions and Entity Identification

The initial report specifies two primary entities: Airlines and Pilots, with relevant attributes and relationships. Beyond this, additional assumptions are necessary to facilitate a robust database design. It is assumed that each airline has a unique identifier, AirlineID, which distinguishes it within the database. Each pilot is assigned a unique PilotID, ensuring individual identification. It is also assumed that each pilot is associated with exactly one airline, reflecting the "each pilot works for one airline" statement, though an airline may employ many pilots, establishing a one-to-many relationship. The pilot attributes include First Name (FName), Last Name (LName), Job Title (JobTitle), and Salary, which describe the individual pilot, while airline attributes include number of planes (NPlanes), number of routes (NRoutes), and number of pilots (NPilots), describing the airline's operational scope. It is also assumed that the attributes for the number of planes, routes, and pilots are intended for overview metrics, derived from operational data. The domain of each field is considered where applicable; for example, NPlanes, NRoutes, and NPilots are positive integers. Additional assumptions include that no other attributes or entities are necessary unless indicated, and that each pilot's employment details are captured in the same record, simplifying the relationship.

2. Functional Dependency Explanation

A functional dependency (FD) describes a relationship whereby the value of one set of attributes determines the value of another set within a relation. For instance, in the provided dependencies, PilotID functionally determines the pilot's First Name, Last Name, Job Title, Salary, AirlineID, NPlanes, NRoutes, and NPilots. This means that given a particular PilotID, the database can uniquely identify all other attributes related to that pilot. For example, if PilotID is 'P123', then the pilot's first name is 'John', last name is 'Doe', job title is 'Captain', salary is $120,000, and he works for airline 'A1' which has 10 planes, 15 routes, and employs 50 pilots. Therefore, PilotID uniquely determines all other data points for that pilot.

3. Multiplicity and Entity Relationships

The relationship between Airlines and Pilots is characterized as a one-to-many relationship. Each airline can have many pilots, but each pilot works for only one airline. This is supported by the attributes and constraints: each PilotID is associated with exactly one AirlineID, but an AirlineID can correspond to many PilotID entries. Thus, in plain English, an airline is a "one" side of the relationship, and pilots constitute the "many" side. This simplifies the database design and is consistent with the description where each pilot is assigned to a single airline, but each airline can have multiple pilots.

4. Normalization Design Process

First Normal Form (1NF)

According to the definition, as per Date (2004, p. 188), "A relation is in 1NF if all underlying domains contain only atomic values; that is, indivisible values." The initial report's table, PilotAirlines, combines pilot and airline attributes in a single table, with each record containing multiple data points that, in this case, are atomic, assuming each attribute holds a single value per record. To conform to 1NF, each field must contain only indivisible, atomic values, which appears to be the case. Therefore, the table is already in 1NF, with attributes such as PilotID, FName, LName, JobTitle, Salary, AirlineID, NPlanes, NRoutes, NPilots, each holding a single value.

Second Normal Form (2NF)

As per Date (2004, p. 189), "A relation is in 2NF if it is in 1NF and all non-key attributes are fully functionally dependent on the primary key." The current table depends on PilotID as the primary key, and attributes such as FName, LName, JobTitle, Salary, AirlineID are dependent on PilotID, which is appropriate. However, airline-specific attributes like NPlanes, NRoutes, NPilots depend solely on AirlineID, which suggests partial dependency if these attributes are stored in the same table with PilotID as prime key. To achieve 2NF, we need to separate airline-specific attributes into a different table, linked via AirlineID.

The resulting tables:

- Pilots: PilotID (PK), FName, LName, JobTitle, Salary, AirlineID (FK)

- Airlines: AirlineID (PK), NPlanes, NRoutes, NPilots

These eliminate partial dependency and meet 2NF, with each attribute depending entirely on the primary key of its table.

Third Normal Form (3NF)

According to Date (2004, p. 190), "A relation is in 3NF if it is in 2NF and all the attributes are non-transitively dependent on the primary key." In our case, no transitive dependencies exist because airline attributes depend solely on AirlineID, and pilot attributes depend solely on PilotID. The separation into two tables also ensures that no non-prime attribute depends on another non-prime attribute within the same table unless it is a foreign key. Hence, both tables are in 3NF.

5. Implementation in MS Access & Sample Data

Using the normalized tables, the Microsoft Access database will be created containing the 'Pilots' and 'Airlines' tables. Sample data will be entered for both, ensuring that foreign key relationships are maintained. Primary keys will be defined on PilotID and AirlineID, with appropriate data types assigned, such as Text for IDs and Names, Number for counts, and Currency or Number for salaries.

6. Query: List Pilots with Airline Names

A query will be created to retrieve PilotID, FName, LName, and the associated AirlineID, replaced with airline names if such data exists, sorted by pilot name. This involves joining the 'Pilots' table with the 'Airlines' table on AirlineID and ordering by PilotID or Name fields.

7. Form: New Pilot Input

A form will be designed in Access for inputting new pilot records, validating input, and ensuring referential integrity with existing airlines. It will contain fields for PilotID, FName, LName, JobTitle, Salary, and AirlineID (with a lookup for existing airlines).

8. Report: Pilot List

A report will be built listing all pilots, their first and last names, and salaries, formatted for clarity and ease of reporting. Filters and sorting options will be included as needed.

9. Additional Submission Details

The final submission will include the database file named with the student's last name plus '_TermProject' (e.g., Smith_TermProject), along with a Word document explaining the design process, normalization steps, and implementation details, ensuring clarity, proper spelling, and organization throughout.

References

• Date, C. J. (2004). An Introduction to Database Systems (8th ed.). Pearson.