Posted: September 13th, 2022

Case Study

Student’s Name
Professor’s Name
Course
Date

1)
• PilotID is the primary key
• The primary key is unique and can be used to query data about the pilot information
• An airline can have more than one route
• An airline can have multiple planes
• A pilot cannot work for more than one airline
• Each airplane is assigned a particular route
• PilotID and AirlineID can be used together to come up with a composite key that will add uniqueness to the table
• An airline may hire multiple pilots
• Each airline has a specified number of routes that they can utilize

2)

Functional dependency refers to when a particular attribute uniquely refers to that of another. It occurs between two attributes one of which is usually the primary key while the other is a non-key attribute in the same table. According to Connolly (2005), it can be a constraint that occurs between a set of attributes in a relation.
PilotID – – > FName, LName, JobTitle, Salary, AirlineID, NPlanes, NRoutes, NPilots
The PilotID is used to identify the names of the pilots as well as their wages and title. An airline may have multiple pilots who will each be identified through their PilotID. A pilot may not work for more than one airline. This is because these would create multiple dependencies. A pilot is assigned a particular route by the airline and a particular plane. Through the PilotID as the primary key, all the information about him and his trips can be retrieved.
3)
Multiplicity refers to the specification whereby the number of possible occurrences of a certain property. It also refers to the number of allowable elements that may be in a relationship at a time. It is a one to many relationship because the primary key which in the PilotID is used to identify all other information. The initial entities including the PilotID, the name, the salary, job title and routes are linked through a one to many relationship to the details concerning the airplane. A pilot can only work for one airline but n airline can hire multiple pilots which makes it a one to many relationship.
4)
a)
To normalize a database is similar to organizing the data or information in it. The process is done with the aim of getting flexible and eliminating redundancies. The point of the process is to ensure that the database is free of redundancies and is at its optimum state. The process involves the creation of tables and relationships between them which in turn make the database more dynamic in terms of its use.
According to Connolly and Begg ,“A relation in which the intersection of each row and column contains one and only one value” (Connolly & Begg, 2014). Carlson and Arora define the first normal form as a relation where the domain of an attribute cannot be further separated or is indivisible (1982).

Pilot
PK PilotID
LName
FName
Salary
Job Title
NRoutes
NPlanes
NPilots

The table belongs to the first normal form because there are still partial dependencies in the table. For instance for the fields of NPilots the table is not normalized because a pilot can only work for one airline. Having this field in this table make the schema rigid and inflexible.

b)
According to Connolly and Begg (2014), “a relation that is in first normal form and every non-primary-key attribute is fully functionally dependent on the primary key and every non-candidate-key attribute is fully functionally dependent on any candidate key”. It is also defined as The second normal form is where a relation belongs to the first normal form and at the same time does not consist of non-prime attributes that are functionally dependent (Fagin 1977).

Pilot
PK PilotID
LName

FName
Salary
Job Title
NRoutes
NPilots
NPlanes
NPilots
NRoutes
NPlanes

This table belongs in the second normal form because it has been separated to all non primary key attributes fit in as fully functionally dependent on the primary key. At the same time, the table also belongs to the first normal form. The partial dependencies have been eliminated by separating the data in both tables. The first table had data on the pilots while the second table consists of data involving multiple planes and their different routes.

c)
Connolly and Begg (2014) define third normal form as, “ a relation that is in first and second normal form in which no non-primary-key attribute is transitively dependent on the primary key”

References
Alotaibi, Y., & Ramadan, B. (2017). A Novel Normalization Forms for Relational Database Design throughout Matching Related Data Attribute. International Journal of Engineering and Manufacturing, 7(5), 65.
Connolly, T. M., & Begg, C. E. (2005). Database systems: a practical approach to design, implementation, and management. Pearson Education.
Fagin, R. (1977). Functional dependencies in a relational database and propositional logic. IBM Journal of research and development, 21(6), 534-544.
McMurdo, G. (1982). Database file normalization as an information science related activity. Journal of Information Science, 4(1), 9-17.

Order | Check Discount