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

Describe entity referential integrity and integrity, Describe entity refere...

Describe entity referential integrity and integrity. Give an example of each. Entity Integrity Rule - If there is a attribute A of relation R is a prime attribute of R then A

State the process of inheritance adjustment, Inheritance Adjustment As...

Inheritance Adjustment As in object oriented analysis and design terms inheritance defines a relationship between classes, where as one class shares structure or behaviour def

Construct substantial data model, PART MARKING:  We will only mark TWO of t...

PART MARKING:  We will only mark TWO of the four questions below but you must submit answers to all four. We will choose the questions we mark randomly and we will supply you with

Define about the stored procedure, Define about the Stored Procedure - ...

Define about the Stored Procedure - It's a set of T-SQL statements combined together to perform a single task formed by combining numerous small tasks. - When you essentiall

Define the query optimisation, Define the Query optimisation The query ...

Define the Query optimisation The query parser commonly generates a standard initial tree to correspond to an SQL query, with no doing any optimization. Such type of a canonica

Er diatgrams, Ask q“A database will be made to store information about pati...

Ask q“A database will be made to store information about patients in a hospital. On arrival, each patient’s personal details (name, address, and telephone number) are recorded wher

State in brief about object design, Give the Introduction of OBJECT DESIGN ...

Give the Introduction of OBJECT DESIGN Strategies which are selected during system design are carried out in object-oriented design. In this procedure, objects which are identi

Find a 3nf decomposition , Find a 3NF decomposition of the following relati...

Find a 3NF decomposition of the following relation scheme: University (Faculty, Dean, Department, Chair, Professor, Rank, Student}. The University relation satisfies the following

find a non-redundant cover and canonical cove, Given the following set of ...

Given the following set of functional dependencies {cf→ bg, g → d, cdg → f, b → de, d → c} defined on R(b,c,d,e,f,g) a. Is cf→ e implied by the FDs? b. Is dg a superkey?

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