Ask Question, Ask an Expert

+1-415-315-9853

info@mywordsolution.com

Ask DBMS Expert


Home >> DBMS

The problems in this exam are based on the Baxter Aviation scenario that you used for the first exam. Please read the following narrative carefully, as many of the rules have changed!

Baxter Aviation’s business has increased so dramatically that the company is now going to offer regularly scheduled flights to the remote villages. Each flight is scheduled to make a circle of two or more villages.

In terms of the database used to manage flights and associated data, this change has the following impacts:

A) Flight numbers for the regularly scheduled flights are not unique. However, any flight number will be used at most once a day.

B) The database must now store passenger lists. In other words, the database must include every person that has purchased a ticket and the flight(s) on which they fly.

C) Children under two years of age fly free if they are held by their parents; children under 12 years of age pay half price for their tickets. The database must therefore also include data about the price paid for each ticket. Otherwise, Baxter Aviation will not have enough data to do its accounting.

D) The database must store the villages to which each flight flies. The order of the village in a flight’s route is given by the Order_in_route attribute. For ex, if a flight goes from Ambler to Shungnak to Kobuk and then back to Kotzebue, the value of Order_in_route for Ambler will be 1, for Shungnak 2, and Kobuk 3.

Consider the following relation in light of the revised rules presented above:

Flights (Pilot#, Flight#, Village name, Flight date, Aircraft ID#, Customer #, Flight time, Customer first name, Customer last name, Customer home phone number, Ticket price paid, Pilot home phone number, Pilot house number, #seats in aircraft, Order in route)

1) What is the primary key of this relation?

2)

a) Give a specific ex of an insertion anomaly in this relation.

b) Give a specific ex of a deletion anomaly in this relation.

c) Give a specific ex of a modification/update anomaly in this relation.

3) What are the functional dependencies among the data represented in this first normal form relation? (Major hint: Don’t forget that functional dependencies are independent of the structure of a relation.)

4) In which normal form is the relation? Why?

5) Transform this relation into a set of third normal form relations. For each table indicate its primary key; underlining is the easiest way to show this. Then list the foreign keys in each table and indicate that relations that they reference.

6) Consider the following request for information from the database:

“Display the first and last names of every passenger that flew on flight #180 on 4/6/11.”

Using the relations you created for problem 5, prepare the sequence of relational algebra operations needed to perform the query.

7) Consider the following request for information from the database:

“Display the names of all the villages that are not scheduled to have flights on 12/1/11.”

Using the relations you created for problem 5, prepare the sequence of relational algebra operations needed to perform the query.

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M92432

Have any Question? 


Related Questions in DBMS

Stored procedures and stored functions - major assessment

Stored Procedures and Stored Functions - Major Assessment Task Background You are to create tables to store product and customer data. You will create a number of stored procedures and functions (SPFs) to insert / update ...

Systems and database design assignment database design amp

Systems and Database Design Assignment: Database Design & Implementation (Airline) The assignment consists of two tasks. The first task, Database Design, requires a word processed document in PDF format detailing the des ...

In general my report talks about the database management

In general, my report talks about the Database management system. But I want to a specific topics of it in 3 pages. The topics are: 1. Comparison between File approach and Database approach. 2. Advantages and disadvantag ...

When 100 ml of m10 h2so4 is mixed with 500 ml of my10 naoh

When 100 ml of M/10 H2SO4 is mixed with 500 ml of My/10 NaOH then nature of resulting solution and normality of excess reactant left is

Db questions1 what is a consistent database state and how

DB QUESTIONS 1. What is a consistent database state, and how is it achieved? 2. List and discuss the five transaction properties. 3. What is a transaction log, and what is its function? 4. What is a deadlock, and how can ...

Project guidelinesoverviewthe final project for this course

Project Guidelines Overview The final project for this course is the creation of a database proposal and implementation plan report that will address the needs of a given scenario. The purpose of this assessment is to as ...

1 what is the difference between a column constraint and a

1. What is the difference between a column constraint and a table constraint? 2. What is a recursive join? 3. Explain the difference between an ORDER BY clause and a GROUP BY clause. 4. What is a CROSS JOIN? Give an exam ...

Overviewfor this assignment you will implement and query a

Overview For this assignment you will implement and query a database from a supplied ER Diagram and Schema. You will be required to write the SQL statements to create the database structures, to fill the database with da ...

Your assignment should be submitted in a single word doc

Your assignment should be submitted in a single Word doc (.doc or .docx). It should include your scenario diagram for Problem 1, and your Ruby source code for Problems 2 and 3. Contact me or post to our discussion board ...

A quick guide to pivot tables is found at the end of this

A "Quick Guide to Pivot Tables" is found at the end of this document. Review it BEFORE beginning the questions. The data for this exercise (structured as a flat file) can be found in an Excel workbook located on Blackboa ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

A cola-dispensing machine is set to dispense 9 ounces of

A cola-dispensing machine is set to dispense 9 ounces of cola per cup, with a standard deviation of 1.0 ounce. The manuf

What is marketingbullwhat is marketing think back to your

What is Marketing? • "What is marketing"? Think back to your impressions before you started this class versus how you

Question -your client david smith runs a small it

QUESTION - Your client, David Smith runs a small IT consulting business specialising in computer software and techno

Inspection of a random sample of 22 aircraft showed that 15

Inspection of a random sample of 22 aircraft showed that 15 needed repairs to fix a wiring problem that might compromise

Effective hrmquestionhow can an effective hrm system help

Effective HRM Question How can an effective HRM system help facilitate the achievement of an organization's strate