Case Study

User Generated

grr321

Computer Science

Into to Relational Database Management Systems

America public university

Description

You were just hired by an airline to create a database to record the various types of aircraft in their fleet; all types of aircraft each pilot is certified to fly, and the date a pilot’s certification for each type of aircraft expires.

Discussions with the airline representatives focused on two initial entities, Pilots and Aircraft; the following key points were agreed:
1. Each pilot is given a unique PilotID
2. Each aircraft type has a unique AirID designation
3. Each pilot may be certified to fly zero, one, or many types of aircraft, and each type of aircraft may be certified to be flown by zero, one, or many pilots.
4. The cDate field is the date the pilot’s certification on that type of aircraft expires
5. DOB is date of birth
6. nOwned is the number of that type of aircraft in service by the airline
7. No fields beyond those in the spreadsheet are needed.



The objective of this exercise is to demonstrate an understanding of some basic concepts covered in the course. The exercise is a straight forward application of those concepts – there are no “hidden” complexities – should you identify something in the key points or data that adds complexity, contact the instructor before submission – you may be over thinking the exercise.



The sample data may not represent all possible values of each field – consider each field’s domain during the design.
Your objective is to replace the following report with a relational database. Your submission will consist of a word compatible document to record the design process, and an Access DB.





Here is the un-normalized relational schema (table notation) for the above report / table:
PilotAircraft (PilotID, LastName, FirstName, DOB, (AirID, nOwned, cDate))



The specific tasks are listed below, the percentage in parenthesis corresponds to the grade weight for each task.
Name your document Last Name_CaseStudy (i.e. Smith_CaseStudy). When you are asked to provide an explanation or description, you must include enough content to demonstrate that you understand the definition, term, concept, etc. and how it applies to this exercise.



SUGGESTION: Review the Terms and Concepts Forum, especially the Many-to-Many and One-To-Many Normalization Process: Parts and Suppliers examples. There is also a normalization MP4 file that can be downloaded from the Resources section.



You must use the following outline in your submission – only include the section numbers – not the questions or other content from the instructions.
1) Review the existing report, with the sample data (as well as field domains and common knowledge) and document any assumptions you feel are appropriate (beyond those in the key points). (10%)
2 a) Submit a functional dependency analysis (all fields must be included at least once), use the functional dependency notation shown below and on pages 410-413 Examples 14.2, 14.3, 14.4 and 14.5 of the Connolly text to document the functional dependencies. Only one set of functional dependencies is needed. There may be one or more rows of functional dependencies.(10%)
Functional dependency notation example:
Field A -> Field B, Field C, means that Field A is a determinant for Field B and Field C.
Similarly if a field is dependent on the combination of values of two fields then:
Field X, Field Y -> Field Z
2 b) Define functional dependency, and explain one row of the submitted functional dependencies in plain English (use field names and values in this exercise). (10%) (you do not need to explain partial or transitive dependency)
3) Explain the relationship between the initial Entities in plain English – (either one-to-many, or many-to-many). (10%)



4) Design: Hint: all fields should be included in each normal form level.
a) Submit a complete set of 1NF table(s). You must use relational scheme, table notation (see the above UNF example). Also refer to the relational schema under Figure 4.2.6 on page 111 of the Connolly text) include the first normal form definition, and a plain English (original) explanation of why the table(s) is (are) in 1NF. Copy the definition from the Connolly text and include fields from this exercise in the original explanation. (10%)
b) Submit a complete set of 2NF table(s). You must use relational scheme, table notation (see the above UNF example). Also refer to the relational schema under Figure 4.2.6 on page 111 of the Connolly text) include the second normal form definition, and a plain English (original) explanation of why the table(s) is (are) in 2NF. Copy the definition from the Connolly text and include fields from this exercise in the original explanation. (10%)
c) Submit a complete set of 3NF table(s). You must use relational scheme, table notation (see the above UNF example). Also refer to the relational schema under Figure 4.2.6 on page 111 of the Connolly text) include the third normal form definition, and a plain English (original) explanation of why the table(s) is (are) in 3NF. Copy the definition from the Connolly text and include fields from this exercise in the original explanation. (10%)



5) Use the 3NF tables in your design, to create an new MS Access database, including the data ,
Name your database Last Name_CaseStudy (i.e. Smith_CaseStudy). (5%).
6) Create a Query (In MS Access), that provides the results for the following request: List each PilotID, FirstName, LastName, and all their AirID certifications, sorted by pilot. (5%)
7) Create one Form: New Pilot Input - this form should include all relevant fields (5%)
8) Create One Report: Pilot List – list all pilots - this report should include all relevant fields (5%)
Upload the database to the assignment area as one of the deliverables
9) Submission content organization, clarity, spelling and grammar (10%)

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

Student's Name
Course Instructor
Course Name
Date Due

1. cDate can be used to list all names and PilotID for pilots whose aircraft certification expires
on a particular date. PilotID can be the determinant for LastName, FirstName, DOB, AirID,
and cDate because PilotID is unique. Last name cannot solely determine PilotID, FirstName,
DOB, AirID, or cDate. FirstName also on its own cannot determine PilotID, LastName, DOB,
AirID, or cDate. According to the case study DOB does not contain similar values, therefore,
maybe used to list PilotID, LastName, FirstName, AirID, or cDate. However, in some rare
cases people in an organization may share the same date of birth.

2. (a) PilotID is the determinant for LastName, FirstName, DOB, AirID, and cDate cDate can be
used as a determinant for LastName, Fir...


Anonymous
I was stuck on this subject and a friend recommended Studypool. I'm so glad I checked it out!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags