Consider the following relational database that Best Airlines uses to keep track of its mechanics, their skills, and their airport locations. Mechanic number, airport name, and skill number are all unique fields.

Size is an airport's size in acres. Skill Category is the type of skill, such as an engine skill, wing skill, tire skill, etc. Year Qualified is the year that a mechanic first qualified in a particular skill; Proficiency Rating is the mechanic's proficiency rating in a particular skill.

Analyze each of the following situations and, using the physical database design techniques discussed in this chapter, state how you would modify the logical design shown to improve performance or otherwise accommodate it.

a. There is a high-priority need to quickly find any particular airport's data given only the airport's city and state.

b. There is a frequent need to find the total salary of all of the mechanics at any particular airport.

c. There is a high-priority need to quickly find any particular mechanic's data together with the data about the airport at which she works.

d. There is a frequent need to list the names and telephone numbers of themechanics who work at any particular airport, together with the airport's city and state.

e. Assume that there is an additional attribute called Skill Description in the SKILL table. This attribute is used to store lengthy descriptions of each skill. The problem is that its presence in the SKILL table is slowing down access to the rest of the data in the table, which is accessed much more frequently.

f. The need to access data about the ten largest airports in the country is much more frequent than the need to access data about the rest of the airports.

1)Select MECHNAME,AGE from MECHANIC;

2)Select * from airport where size >= 20 and yearopened >= 1935 and city like 'California' order by size;

3)Select * from airport where size >= 20 and yearopened >= 1935 and city like 'California'

4)select avg(size) from airport where yearopened >= 1935 and city like 'California'

5)select count(airname) from airport where yearopened >= 1935 and city like 'California'

6)select count(*) from airport where yearopened >= 1935 group by (state);

7)select count(airname) from airport where yearopened >= 1935 group by (state) having count(airname) >= 5;

8)select m.MECHNAME from MECHANIC m,AIRPORT a where m.AIRNAME = a.AIRNAME and a.city like 'California';

9)select m.MECHNAME from MECHANIC m,QUALIFICATION q,SKILL s where m.MECHNUM = q.MECHNUM and s.SKILLNUM = q.SKILLNUM and s.SKILLNAME like 'Fan blade' and q.PROFRATE = 4;

10)select m.MECHNAME from MECHANIC m,QUALIFICATION q,SKILL s,AIRPORT a where m.MECHNUM = q.MECHNUM and s.SKILLNUM = q.SKILLNUM and m.AIRNAME = a.AIRNAME and a.city like 'California' and s.SKILLNAME like 'Fan blade' and q.PROFRATE = 4;

11)Select total(m.salary) from from MECHANIC m,AIRPORT a where m.AIRNAME = a.AIRNAME and a.city like 'California';

12)select max(size),Airname from Airport;

13)select max(size),Airname from Airport where city like 'California';

2ANS:

1)Select STARTDATE,ENDDATE from CRUISE where CRUISENUM = 35218;

2)Select SHIPNUM,SHIPNAME from SHIP where BUILDER like 'Ace Shipbuilding Corp.' and WEIGHT > 60000

3)Select S.BUILDER from SHIP s,CRUISE c where c.SHIPNUM = s.SHIPNUM and c.DIRECTOR like 'Happy Cruise Lines';

4)Select total(NUMDOCKS) from PORT where COUNTRY like 'Canada';

5)Select avg(WEIGHT) from SHIP where BUILDER like 'Ace Shipbuilding Corp' and LAUNCHDATE >= 2000;

6)Select count(portname) from PORT where COUNTRY like 'Venezuela' having count(portname) >= 3;

7)select total(NUMDOCKS) from PORT group by COUNTRY order by total(NUMDOCKS);

8)select total(NUMDOCKS) from PORT group by COUNTRY;

9)select total(NUMDOCKS) from PORT group by COUNTRY having total(NUMDOCKS) >= 12;

10)Select s.SHIPNAME from SHIP s,CRUISE c where s.SHIPNUM = c.SHIPNUM and c.CRUISENUM = 35218;

11)Select p.PASSENGERNAME,p.STATE,p.COUNTRY from PASSENGER p,SHIP s, CRUISE c, VOYAGE v where p.PASSENGERNUM = v.PASSENGERNUM and v.CRUISENUM = c.CRUISENUM and c.SHIPNUM = s.SHIPNUM and s.SHIPNAME like 'The Spirit of Nashville' and c.STARTDATE like '%july-2011';

12)Select SHIPNAME, max(WEIGHT) from SHIP ;

13) Select s.SHIPNAME, max(s.WEIGHT) from SHIP s,CRUISE c where s.SHIPNUM = c.SHIPNUM and c.STARTDATE like '15-july-2011' and c.ENDDATE like '31-JULY-2011';