Term Project – week 8 – (17%):
You were just hired by an airline industry to create a database to record the various airlines and the pilots that work for them
Discussions with the industry representatives focused on two initial entities, Airlines and Pilots; the following key points were agreed:
1. Each pilot is assigned a unique PilotID.
2. Each AirlineID is unique
3. Each pilot may work for one airline, each airline can have many pilots.
4. FName, LName, JobTitle and Salary describe each pilot (with the obvious meaning)
5. AirlineID, NPlanes, NRoutes, NPilots describe each airline (NPlanes number of planes, NRoutes number of routes, NPilots number of pilots)
6. No fields beyond those in the report 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.
The objective is to replace the following report with a relational database. The submission will consist of a word compatible document to record the design process, and an Access DB.
Here is the relational schema (table notation) for the above report / table:
PilotAirlines (PilotID, FName, LName, JobTitle, Salary, AirlineID, NPLanes, NRoutes, NPilots)
The functional dependencies are:
PilotID – – > FName, LName, JobTitle, Salary, AirlineID, NPLanes, NRoutes, NPilots
PilotID – – > FName, LName, JobTitle, Salary, AirlineID
AirlineID – – > NPLanes, NRoutes, NPilots
The specific tasks are listed below, the percentage in parenthesis corresponds to the grade weight for each task.
Name your document Last Name_TermProject (i.e. Smith_TermProject). When you are asked to provide an explanation or description, 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 Discussion, especially the One-to-Many Normalization Process: Parts and Suppliers example. There is also a normalization MP4 file that can be downloaded from the Resources section.
Incorporate the following outline in your submission – include the section numbers – not the questions or other content from the instructions.
1) Review the existing report, functional dependencies provided and 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) and identify initial entities (person, place, thing). (10%)
2 Define functional dependency, and explain one row of the functional dependencies provided above in plain English (use field names). (15%) (you do not need to explain partial or transitive dependency)
3) Based on multiplicity – explain the relationship between the initial Entities in plain English – (either one-to-many, or many-to-many). (15%)
4) Design: specify all tables and fields at each normal form level
a) First Normal Forum (1NF) assessment / action – if needed 10%
Copy the 1NF definition from the text (include quotes and page number)
Assess the UNF table provided and if necessary, make the changes needed to conform to the 1NF definition. Document the resulting 1NF table(s) using relational schema notation or spreadsheet format (see the above example or page 111 Figure 4.2.6 of the text).
Explain how each table(s) meets the 1NF definition (use field names)
b) Second Normal Form (2NF) assessment / action – if needed 10%
Copy the 2NF definition from the text (include quotes and page number)
Assess the 1NF table(s) in the previous section and if necessary, make the changes needed to conform to the 2NF definition. Document the resulting 2NF table(s) using relational schema notation or spreadsheet format.
Explain how each table meets the 2NF definition (use field names)
c) Third Normal Form (3NF) assessment / action if needed 10%
Copy the 3NF definition from the text (include quotes and page number)
Assess the 2NF table(s) in the previous section and if necessary, make the changes needed to conform to the 3NF definition. Document the resulting 3NF table(s) using relational schema notation or spreadsheet format.
Explain how each table meets the 3NF definition (use field names)
5) Use the 3NF tables in your design, to create an new MS Access database, load the sample data provided,
Name your database Last Name_TermProject (i.e. Smith_TermProject). (5%).
6) Create a Query, that provides the results for the following request: List each PilotID, FirstName, LastName, the Airline each pilot works for, sorted by pilot. (5%)
7) Create a Form: New Pilot Input (5%)
8) Create a Report: Pilot List – list all pilots, their first and last names, and Salary (5%)
Upload the database to the assignment area as one of the deliverables
9) Submission content organization, clarity, spelling and grammar (10%)
Why Work with Us
Top Quality and Well-Researched Papers
We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.
Professional and Experienced Academic Writers
We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.
Free Unlimited Revisions
If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.
Prompt Delivery and 100% Money-Back-Guarantee
All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.
Original & Confidential
We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.
24/7 Customer Support
Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.
No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.
Admission Essays & Business Writing Help
An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.
Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.
If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.