2. Which of the previous expressions involve natural joins? 1
3. Which involve joins? (Note: the textbooks explanation of theta join is confusing. The symbol stands in for some comparison like X > 2: a join is called a join if it has a
specified join condition (not a natural join) and is not an equijoin.)
Evaluate the following relational algebra expressions; your answers must include the column header with the attribute names. Note: technically, the relational algebra deals with sets of tuples. Sets do not keep duplicate elements, so don't show them in your answers. (Many real RDBMSs do allow duplicates.) Some of the expressions may be illegal; if so, indicate why. (a) TMOD CODE(AIRCRAFT) (b) OMOD_SEATS=10(MODEL) (c) TMOD-CODE,MOD-CHG-MILE(OMOD-MANUFACTURER>MOD_NAME (MODEL)) (d) (TAC_NUMBER, MOD-CODE(AIRCRAFT)) - (TMOD.CODE,MOD_NAME (MODEL)) (e) TTMODEL.MOD-CODE,AC_NUMBER(AIRCRAFTXMODEL). In this query, there are two different columns that could be called MOD_CODE and the MODEL prefix indicates the one we want. You will do too much work if you carefully copy over attributes that don't end up in the final projection. Focus on the important attributes for this query, and getting the final number of rows correct. (f) TMOD-CODE(MODEL) TMOD-CODE(AIRCRAFT) (g) TCHAR-TRIP,CHAR_PILOT,MOD_CODE,AC-TTAF,MOD_NAME ((OCHAR.PILOT>107 (CHARTER). AIRCRAFT) MODEL) (h) TCHAR_TRIP,CHAR_HOURS_WAIT(CHARTER) ACHAR_HOURS_WAIT>MOD_SEATS TIMOD_CODE,MOD_SEATS (MODEL) (i) CHARTER U AIRCRAFT 104 50 Table name: CHARTER Database name: Ch03_AviaCo OUR TROUR DATE CHAR PLOT OUR_COPLOTAC NUMBE OUR DESTINATION OUR DISTANCE CHAR KURS-FLOW OUR HOURS WAT OHARRA GALLONS OUROL OSCUS_COCE 0001 05-Feb-10 2200 ATL 50 22 1 10011 05.10 101 PTV GNIA 18 00 726 10016 10003 05.10 105 10S 42TBY ONV 15140 78 00 08 2 10014 0804 06-7-10 106 1630 SIL 4720 29 972 1 10019 SOOS 06 Feb 10 101 2207 ATL 10230 57 35 27 2 10011 OBOS 007-10 100 42POY SRL 4720 25 1171 O 10017 1007 01718 Feb 104 105 TV GNV 15740 79 2 10012 10000 OT Feb 10 106 14 TVS 5446 40 00 1406 1 10014 0809 077-10 105 GNV 15740 66 234 4509 0 0 10017 10010 07 Feb 10 - 109 4279 ATL 3D 62 32 2797 0 10010 011 077-10 101 104 100 1920 10 53 904 1 10012 10012 010 101 2770 0040 40 42 2151 10010 10019 03.10 105 TVS 500 30 1783 1 10011 0014 09-10 106 4278Y ATL 990 60 21 3026 0 10017 10015 09-10 104 101 2201 ONY 16450 67 00 4905 2 10010 S0018 07-10 109 105 2770 MOY 3120 15 00 072 0 10011 2017 107-10 101 16 SIL 5080 31 00 1055 10014 10016 Feb 18 10/10 105 104 78Y BM SA 45 1024 0 10017 TYS The destinations are indicated by standard three-letter airport codes. For example, STL = St. Louis, MO ATL = Atlanta, GA BNA = Nashville, TN Table name: AIRCRAFT AC-TTAF = Aircraft total time, airframe (hours) AC_NUMBER MOD CODE AC_TTAF AC_TTEL AC_TTER AC-TTEL = Total time, left engine (hours) 1484P PA23-250 1833.1 1833.1 101.8 AC_TTER = Total time, right engine (hours) 2289L C-90A 4243.8 768.9 1123.4 2778V PA31-350 7992.9 1513.1 789.5 In a fully developed system, such attribute values 4278Y PA31-350 2147.3 622.1 243.2 would be updated by application software when the CHARTER table entries were posted. Table name: MODEL MOD_CODE MOD_MANUFACTURER MOD_NAME MOD_SEATS MOD_CHG_MILE B200 Beechcraft Super KingAir 10 1.93 C-90A Beechcraft KingAir 8 2.67 PA23-250 Piper Aztec 6 1.93 PA31-350 Piper Navajo Chieftain 10 2.35 Customers are charged per round-trip mile, using the MOD_CHG_MILE rate. The MOD_SEATS column lists the total number of seats in the airplane, including the pilot and copilot seats. Therefore, a PA31-350 trip that is flown by a pilot and a copilot has eight passenger seats available
