Assignment - 1 - redoing to improve grades, Database Management System

Assignment Help:
Assignment #1
Data Management

The questions on this exam are based on the Baxter Aviation case scenario.

1. (5 points) The systems analyst proposes the following two relationships between the Flight and Pilot entities:



What question do you need to answer to decide which of the relationships is correct? (Hint:The answer requires only one sentence.)

2. A part of the initial ER diagram for the Baxter Aviation database appears as follows:






(a) (5 points) State in words the meaning of this relationship.

(b) (5 points) Why can’t this relationship be represented directly in a relational database?

(c) (5 points) Redraw the diagram so that it is compatible with the design of a relational database.

3. (10 points) Assume that you have been presented with the following relation for the Baxter Aviation database:

Charters (Pilot#, Pilot name, Aircraft ID#, #seats, Village, Flight#, Flight date, Flight time, Customer#, Customer name)

(a) How many entities are represented by this relation?

(b) What is the primary key of this relation? (Hint: Flight# is unique; flight numbers do not repeat as they do on commercial airlines with regularly scheduled flights. Each flight can go to many villages and over time a village can receive many flights.)


(c) Give a specific example of one insertion anomaly present in this relation.

(d) Give a specific example of one deletion anomaly present in this relation.

(e) Is this relation in first, second, or third normal form? Why?


4. A portion of the Baxter Aviation database is as follows:

Aircraft (Aircraft ID #, Make, Model, #seats, total airtime, hours on engine)
Village (Village name, Type of runway)
Flight (Flight time, Flight date, Aircraft ID#, Flight#, Customer #)
Destinations (Flight#, Village name)
Customers (Customer name, House number, Phone, Customer #)


(a) (5 points) Identify the primary key for each relation.

(b) (5 points) Identify all foreign keys and the primary keys they reference.

(c) (10 points) List all the constraints that must be verified whenever a row is inserted into the Flight relation. (In other words, what are the specific integrity checks that must be made?) In your list, indicate exactly which attributes are being checked and the specific test(s) being performed on each. (hint: Here’s a partial example: The database needs to validate that the primary key of the relation is unique.)

Related Discussions:- Assignment - 1 - redoing to improve grades

What is the function of sql server agent windows service, What is the funct...

What is the function of SQL Server Agent Windows service? - It's a Windows service that handles tasks scheduled within SQL Server environment. These tasks are also known as job

Write a query to find name from the relational algebra, Consider the relati...

Consider the relations City (city_name, state) Hotel (name, address) City_hotel (hotel_name, city_name, owner) Answer the following queries in relational algebra (i) Find

Design a gennalization and specialization hierarchy for an a, Design a genn...

Design a gennalization and specialization hierarchy for an automobile sales company

What is known as fudge factor, What is known as fudge factor? The numbe...

What is known as fudge factor? The number of partitions is enhanced by a small value called the fudge factor, which is usually 20 percent of the number of hash partitions compu

Consistency error-database errors, Consistency error : These errors happen ...

Consistency error : These errors happen due to the inconsistent state of database caused may be due to wrong implementation of commands or in case of a transaction abort.  To overc

Develop a fragment of a hypothetical system, The objectives of this assignm...

The objectives of this assignment are:  - to develop skills in the design of database driven applications  - to develop skills in applying SQL to implement the relational and

Data warehouse, XYZ is a large chain of home entertainment rental over 100 ...

XYZ is a large chain of home entertainment rental over 100 stores distributed over all states in Australia. XYZ lends entertainment products such as movies, TV shows and games on v

Explain the lock based protocol, Lock Based Protocol A lock is nothing ...

Lock Based Protocol A lock is nothing but a mechanism that tells the DBMS whether a particular data item is being used by any transaction for read/write purpose. As there are t

What are the disadvantages of file processing system, What are the disadvan...

What are the disadvantages of file processing system? The drawbacks of file processing systems are a) Data redundancy and inconsistency b) Complexity in accessing data

Illustrate the state model for the atm class, Illustrate the State model fo...

Illustrate the State model for the ATM class let us take state model for the ATM class given below in Figure showing state model of the ATM class and the pseudo code derived fr

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd