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

Prepare a brief report that describes how data warehouses

Prepare a brief report that describes how data warehouses and online transaction processing complement each other. In your report make sure to include the following: • Why do companies use data warehouses? • What are the ...

Lab modifying a database design in visiothis assignment

Lab: Modifying a Database Design in Visio This assignment contains two Sections: Visio Diagram and Design Summary. You must submit both sections as separate files in order to complete this assignment. Use "Appendix A: De ...

Fundamentals of databases assignment 1 -q1 explain the

Fundamentals of Databases Assignment 1 - Q1. Explain the distinction between total and partial constraints. Q2. Explain the difference between a weak and a strong entity set. Q3. Explain the distinction between disjoint ...

Make the menageriegis database using petgissqllook at the

Make the menagerieGIS database using petGIS.sql Look at the petGIS.png to see where the pets are. 1. Display the name, astext location, X and Y values of each pet 2. Display the pets with their location displayed in stan ...

Oracle databaseusing the schema you developed in homework 1

Oracle Database Using the Schema you developed in Homework 1 for your e-Commerce project, design and implement a Virtual Private Database that limits database access. Your design should be based on two of your columns an ...

1 how are qbe tools and sql similar2 how are they

1. How are QBE tools and SQL similar? 2. How are they different? 3. What is a data warehouse? 4. How does it differ from a database?

Replacement china incproject descriptionyou work as an

Replacement China, Inc. Project Description: You work as an associate database manager at Replacement China, Inc. This firm specializes in finding difficult-to-replace, no-longer manufactured china, crystal, silver, and ...

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

Hotel reservationsproject descriptionthe main portion of

Hotel Reservations Project Description: The main portion of the resort is the hotel. The hotel wants to store information about hotel guests, reservations, and rooms. You will design tables, import data from Access and E ...

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

  • 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