MS Access database_Database_Design

  

Excersises.docx The queries should be supplied in a MS Word Document. You may create the tables in a MS Access database to test your queries if so desired and submit the database in lieu of the MS Word document.
excersises.docx

Unformatted Attachment Preview

Don't use plagiarized sources. Get Your Custom Essay on
MS Access database_Database_Design
Just from $13/Page
Order Essay

Exercise 1
Consider the following relational database that Best Airlines uses to keep track of its mechanics,
their skill, and their airport locations. Mechanic number (MECHNUM), airport name
(AIRNAME), an skill number are all unique fields. SKILLCAT is a skill category, such as an
engine skill, wing skill, tire skill, etc. YEARQUAL is the year that a mechanic first qualified in a
particular skill; PROFRATE is the mechanic’s proficiency rating in a particular skill.
MECHNUM
MECHNAME
AGE
SALARY
AIRNAME
MECHANIC Table
AIRNAME
CITY
STATE
SIZE
YEAROPENED
AIRPORT Table
SKILLNUM
SKILLNAME
SKILLCAT
SKILL Table
MECHNUM
SKILLNUM
_________
________
YEARQUAL
PROFRATE
QUALIFICATION Table
Write SQL SELECT commands to answer the following queries:
1. List the names and ages of all the mechanics.
2. List the Airports in California that are at least 20 acres in size and have been open since
1935. Order the results from smallest to largest airport.
3. List the Airports in California that are at least 20 acres in size and have been open since
1935.
4.
5.
6.
7.
Find the average size of the airports in California that have been open since 1935.
How many airports have been open in California since 1935?
How many airports have been open in each state since 1935?
How many airports have been open in each state since 1935? Include in your answer
only those states that have at least five such airports.
8. List the names of the mechanics who work in California
9. Fan blade replacement is the name of a skill. List the names of the mechanics who have a
proficiency rating of 4 in fan blade replacement.
10. Fan blade replacement is the name of a skill. List the names of the mechanics who work
in California have a proficiency rating of 4 in fan blade replacement.
11. List the total, combined salaries of all the mechanics who work in each city in California
12. Find the largest of all the Airports.
13. Find the largest airport in California
Minicase 1.
Consider the following relational database for Happy Cruise Lines. It keeps track of
ships, cruises, ports, and passengers. A “cruise” is a particular sailing of a ship on a particular
date. For example, the seven-day journey of the ship Pride of Tampa that leaves on June 13,
2011, is a cruise. Note the following facts about this environment.
•
•
•
•
•
•
Both ship number and ship name are unique in the SHIP Table.
A ship goes on many cruises over time. A cruise is associated with a single ship.
A port is identified by the combination of port name and country.
As indicated by the VISIT Table, a cruise includes visits to several ports and a port is
typically included in several cruises.
Both Passenger Number and Social Security Number are unique in the PASSENGER
Table. A particular person has a single Passenger Number that is used for all of the
cruises that she takes.
The VOYAGE Table indicates that a person can take many cruises and a cruise, of
course, has many passengers.
SHIPNUM
SHIP Table
SHIPNAME
BUILDER
LAUNCHDATE
WEIGHT
CRUISENUM
STARTDATE
ENDDATE
DIRECTOR
SHIPNUM
CRUISE Table
PORTNAME
COUNTRY
NUMDOCKS
MANAGER
PORT Table
CRUISENUM
PORTNAME
COUNTRY
_________
________
ARRDATE
DEPDATE
SOCSECNUM
STATE
VISIT Table
PASSENGERNUM
PASSENGERNAME
COUNTRY
PASSENGER Table
PASSENGERNUM
CRUISENUM
_____________
__________
ROOMNUM
VOYAGE Table
Continued below
Write SQL SELECT commands to answer the following queries.
1. Find the start and end dates of cruise number 35218.
FARE
2. List the names and ship numbers of the ships built by the Ace Shipbuilding Corp. that
weigh more than 60, 000 tons.
3. List the Companies that have built ships for Happy Cruise Lines.
4. Find the total number of docks in all the ports in Canada.
5. Find the average weight of the ships built by the Ace Shipbuilding Corp. that have been
launched since 2000.
6. How many ports in Venezuela have at least three docks?
7. Find the total number of docks in each country. List the results in order from most to
least.
8. Find the total number of ports in each country.
9. Find the total number of docks in each country but include only those countries that have
at least twelve docks in your answer.
10. Find the name of the ship that operated on (was used on) cruise number 35218.
11. List the names, states and countries of the passengers who sailed on The Spirit of
Nashville on cruises that began during July, 2011.
12. Find the names of the company’s heaviest ships.
13. Find the names of the company’s heaviest ships that began a cruise between July 15,
2011 and July 31, 2011.

Purchase answer to see full
attachment

  
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more