i need help with a database problem

Use the database AviaCo attached to answer the following questions. 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.

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.

Question 1: For each table, where possible, identify:

  • The primary key
  • A superkey
  • A candidate key
  • The foreign key(s)
  • A secondary key(s)

Question 2: 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.)

Question 3: Create the relational diagram.

Question 4: Modify the ERD you created in Question 2 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.)

Question 5: 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.

Question 6: 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.

Question 7: Create the table that would result from applying a DIFFERENCE relational operator of your result from question 5 to your result from question 6.

 

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code “Newclient” for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.


The post i need help with a database problem appeared first on The Nursing TermPaper.

"Are you looking for this answer? We can Help click Order Now"