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

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 ...

Moving average forecasting modelsmoving average forecasting

Moving Average Forecasting Models Moving average forecasting models are powerful tools that help managers in making educated forecasting decisions. A moving average is mainly used to forecast short historical range data. ...

Database design discusion 150 wordsnormalizationplease

Database Design Discusion (150 words) Normalization Please respond to BOTH of the following questions: Question A In your own words discuss the benefits of normalization. Question B Do you think we should normalize our d ...

How up-to-date should data warehouse information

HOW UP-TO-DATE SHOULD DATA WAREHOUSE INFORMATION BE? Information timeliness is a must in a data warehouse-old and obsolete information leads to poor decision making. Below is a list of decision-making processes that peop ...

Problem iyoull be happy to know that we made a strong case

Problem I "You'll be happy to know that we made a strong case to manage¬ment that we should hire a new systems analyst to specialize in ecommerce development," says Al Falfa, a systems analyst for the multi-outlet intern ...

Exercise 1 - html and Exercise 1 - HTML and XML ID Name Stars Facilities Address Distance

Exercise 1 - HTML and XML ID Name Stars Facilities Address Distance from center (KM) Available 1 Les Jardins du Marais 3 Internet 74 rue Amelot, Paris, 75011 2 True 2 Golden Tulip Little Palace 4 Internet Gym Parking Res ...

Restaurants and fast-food chains rely heavily on business

Restaurants and fast-food chains rely heavily on business intelligence to make important decisions. Casinos do as well. Some of the leading restaurants, fast-food chains, and casinos using data warehouses include AFC Ent ...

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 ...

Relational database systems comp 1005objective this is an

Relational Database Systems (COMP 1005) 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 scen ...

Hospital database determining keysdevelop a microsoft

Hospital Database: Determining Keys Develop a Microsoft Access database based upon the following business scenario. Be sure to include tables, fields, keys, relationships, and test data in your database. Your final submi ...

  • 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

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

Describe what you learned about the impact of economic

Describe what you learned about the impact of economic, social, and demographic trends affecting the US labor environmen