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

Computer sciencein reference to your database design

Computer Science In reference to your Database Design Proposal: Proposal Outline, provide a narrative analysis of the customer and user needs by defining the customers and users as well as describing their individual nee ...

Individual outsourcing risksprepare a 8- to 10-slide

Individual: Outsourcing Risks Prepare a 8- to 10-slide PowerPoint presentation, including speaker notes, that identifies the possible risks to an organization in each of the following outsourcing situations: • The use of ...

Homeworkunlimited pickers is a group of workers who have

Homework Unlimited Pickers is a group of workers who have joined together to provide harvesting services to farmers who need to have their crops brought in. The organization has many teams of workers who travel from loca ...

Objective this is an individual assignment aimed to give

Objective: This is an individual assignment aimed to give the student exposure in understanding, designing, building and analyzing database systems for a given real-time based scenario and be able to conduct optimal stra ...

Capstone project purpose of the assessmentthe purpose of

Capstone Project Purpose of the assessment: The purpose of this assignment is to identify new learning, self realisation and plan for improvement. Students will have an opportunity to explore the use of the online tools ...

Create a database to keep inventory of something you own

Create a database to keep inventory of something you own, such as books, CDs, movies, collectibles, etc. You can make these up if you want. (You could use this in real life to keep track of what other people have borrowe ...

1 draw and explain erm diagram andnbsp explain what is

1. Draw and explain ERM diagram and  explain what is REM 2. Explain what is Normalization . what is 0NF,1NF, 2NFand ,3NF and explain what is in the tables Entity Relationship Modeling and Normalization: 1 System Analysis ...

Problem 1 please create a plsql procedure to print out the

Problem 1: Please create a PL/SQL procedure to print out the reservation ID, checkin date, and checkout date of reservations made by a guest. The input parameters is the name of the guest. There is no output parameter. P ...

Frontlines united states of secrets all Frontline's United States of Secrets, all

Frontline's United States of Secrets, all episodes: http://www.pbs.org/wgbh/frontline/film/united-states-of-secrets/ (Links to an external site.) Nova's Genome specials http://www.pbs.org/wgbh/nova/body/cracking-the-code ...

Taskoverview of business casealive amp boating aampb is a

Task Overview of business case: Alive & Boating (A&B) is a small start­up company that sells small boats in Wagga. A&B keeps its models in several showrooms across the city. At this stage customers cannot view the availa ...

  • 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

WalMart Identification of theory and critical discussion

Drawing on the prescribed text and/or relevant academic literature, produce a paper which discusses the nature of group

Section onea in an atwood machine suppose two objects of

SECTION ONE (a) In an Atwood Machine, suppose two objects of unequal mass are hung vertically over a frictionless

Part 1you work in hr for a company that operates a factory

Part 1: You work in HR for a company that operates a factory manufacturing fiberglass. There are several hundred empl

Details on advanced accounting paperthis paper is intended

DETAILS ON ADVANCED ACCOUNTING PAPER This paper is intended for students to apply the theoretical knowledge around ac

Create a provider database and related reports and queries

Create a provider database and related reports and queries to capture contact information for potential PC component pro