Homework answers / question archive / Business Data Management Assignment II   [Question 1] Use the database shown in following Figure to answer Problems (A)−(F)

Business Data Management Assignment II   [Question 1] Use the database shown in following Figure to answer Problems (A)−(F)

Computer Science

Share With

---

 

[Question 1] Use the database shown in following Figure to answer Problems (A)−(F). ROBCOR is an aircraft charter company that supplies on-demand charter flight services using a fleet of four aircraft. Aircrafts are identified by a unique registration number. Therefore, the aircraft registration number is an appropriate primary key for the AIRCRAFT table.

FIGURE AviaCo Database Tables (Part 1)

 

 

The nulls in the CHARTER table’s CHAR_COPILOT column indicate that a copilot is not required for some charter trips or for some aircraft. Federal Aviation Administration (FAA) rules require a copilot on jet aircraft and on aircraft having a gross take-off weight over 12,500 pounds. None of the aircraft in the AIRCRAFT table are governed by this requirement; however, some customers may require the presence of a copilot for insurance reasons. All charter trips are recorded in the CHARTER table.

FIGURE AviaCo Database Tables (Part 2)

 

 

NOTE

It is best to avoid homonyms and synonyms. In this problem, both the pilot and the copilot are pilots in the PILOT table, but EMP_NUM cannot be used for both in the CHARTER table. Therefore, the synonyms CHAR_PILOT and CHAR_COPILOT were used in the CHARTER table.

Although the solution works in this case, it is very restrictive and it generates nulls when a copilot is not required. Worse, such nulls proliferate as crew requirements change. For example, if the AviaCo charter company grows and starts using larger aircraft, crew requirements may increase to include flight engineers and load masters. The CHARTER table would then have to be modified to include the additional crew assignments; such attributes as CHAR_FLT_ENGINEER and CHAR_LOADMASTER would have to be added to the CHARTER table. Given this change, each time a smaller aircraft flew a charter trip without the number of crew members required in larger aircraft, the missing crew members would yield additional nulls in the CHARTER table.

You will have a chance to correct those design shortcomings in Problem 27. The problem illustrates two important points:

1. Don’t use synonyms. If your design requires the use of synonyms, revise the design!
2. To the greatest possible extent, design the database to accommodate growth without requiring structural changes in the database tables. Plan ahead and try to anticipate the effects of change on the database.

 

(A). For each table, where possible, identify:

 

1. 1. The primary key
   2. A superkey
   3. An alternative candidate key (i.e., don’t give the primary key you identified earlier. If  there is none, explain why)
   4. The foreign key(s)

 

(B). Create the ERD. (Hint: Look at the table contents. You will discover that an AIRCRAFT can fly many CHARTER trips but that each CHARTER trip is flown by one AIRCRAFT. Similarly, you will discover that a MODEL references many AIRCRAFT but that each AIRCRAFT references a single MODEL, etc.)

 

(C). Modify the ERD you created in Problem (B) to eliminate the problems created by the use of synonyms. (Hint: Modify the CHARTER table structure by eliminating the CHAR_PILOT and CHAR_COPILOT attributes; then create a composite table named CREW to link the CHARTER and EMPLOYEE tables. Some crewmembers, such as flight attendants, may not be pilots. That’s why the EMPLOYEE table enters into this relationship.)

 

 

You are interested in seeing data on charters flown by either Mr. Robert Williams (employee number 105) or Ms. Elizabeth Travis (employee number 109) as pilot or copilot, but not charters flown by both of them.  Complete problems (D) –(F) to find these data.

 

(D). Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by either employee 105 or employee 109.

 

(E). Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by both employee 105 and employee 109.

 

(F). Create the table that would result from applying a DIFFERENCE relational operator of your result from problem (D) to your result from problem (E).

 

[Question 2] Using the Crow’s Foot methodology, create an ERD that can be implemented for a medical clinic, using at least the following business rules:

 

1. A patient can make many appointments with one or more doctors in the clinic, and a doctor can accept appointments with many patients. However, each appointment is made with only one doctor and one patient.
2. Emergency cases do not require an appointment. However, for appointment management purposes, an emergency is entered in the appointment book as “unscheduled.”
3. If kept, an appointment yields a visit with the doctor specified in the appointment. The visit yields a diagnosis and, when appropriate, treatment.
4. With each visit, the patient’s records are updated to provide a medical history
5. Each patient visit creates a bill. Each patient visit is billed by one doctor, and each doctor can bill many patients.
6. Each bill must be paid. However, a bill may be paid in many installments, and a payment may cover more than one bill.
7. A patient may pay the bill directly, or the bill may be the basis for a claim submitted to an insurance company.
8. If the bill is paid by an insurance company, the deductible is submitted to the patient for payment.

 

 

 

 

[Question 3] The dependency diagram in Figure Q6.7 indicates that authors are paid royalties for each book that they write for a publisher.  The amount of the royalty can vary by author, by book, and by edition of the book. 

 

Figure Q6.7 Book royalty dependency diagram

 

 

 

a.   Based on the dependency diagram, create a database whose tables are at least in 2NF, showing the dependency diagram for each table.

b.   Create a database whose tables are at least in 3NF, showing the dependency diagram for each table.

 

 

 

 

 

 

 

 

 

 

[Question 4] Using the INVOICE table structure shown in Table P6.3, do the following:

Table P6.3 Sample INVOICE Records

Attribute Name	Sample Value	Sample Value	Sample Value	Sample Value	Sample Value
INV_NUM	211347	211347	211347	211348	211349
PROD_NUM	AA-E3422QW	QD-300932X	RU-995748G	AA-E3422QW	GH-778345P
SALE_DATE	15-Jan-2014	15-Jan-2014	15-Jan-2014	15-Jan-2014	16-Jan-2014
PROD_LABEL	Rotary sander	0.25-in. drill bit	Band saw	Rotary sander	Power drill
VEND_CODE	211	211	309	211	157
VEND_NAME	NeverFail, Inc.	NeverFail, Inc.	BeGood, Inc.	NeverFail, Inc.	ToughGo, Inc.
QUANT_SOLD	1	8	1	2	1
PROD_PRICE	$49.95	$3.45	$39.99	$49.95	$87.75

 

a.   Write the relational schema, draw its dependency diagram and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table uses a composite primary key.)

b.   Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you created.

c.   Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also identify the normal forms for each table structure you created.