下载文档原格式
Solutions Chapter 6Attributes must be separated by commas. Thus here B is an alias of A.6.1.2a)SELECT address AS Studio_AddressFROM StudioWHERE NAME = 'MGM';b)SELECT birthdate AS Star_BirthdateFROM MovieStarWHERE name = 'Sandra Bullock';c)SELECT starNameFROM StarsInWHERE movieYear = 1980OR movieTitle LIKE '%Love%';However, above query will also return words that have the substring Love e.g. Lover. Below query will only return movies that have title containing the word Love.SELECT starNameFROM StarsInWHERE movieYear = 1980OR movieTitle LIKE 'Love %'OR movieTitle LIKE '% Love %'OR movieTitle LIKE '% Love'OR movieTitle = 'Love';d)SELECT name AS Exec_NameFROM MovieExecWHERE netWorth >= 10000000;e)SELECT name AS Star_NameFROM movieStarWHERE gender = 'M'OR address LIKE '% Malibu %';a)SELECT model,speed,hdFROM PCWHERE price < 1000 ;MODEL SPEED HD----- ---------- ------1002 2.10 2501003 1.42 801004 2.80 2501005 3.20 2501007 2.20 2001008 2.20 2501009 2.00 2501010 2.80 3001011 1.86 1601012 2.80 1601013 3.06 8011 record(s) selected.b)SELECT model ,speed AS gigahertz,hd AS gigabytesFROM PCWHERE price < 1000 ;MODEL GIGAHERTZ GIGABYTES ----- ---------- ---------1002 2.10 2501003 1.42 801004 2.80 2501005 3.20 2501007 2.20 2001008 2.20 2501009 2.00 2501010 2.80 3001011 1.86 1601012 2.80 1601013 3.06 8011 record(s) selected.c)SELECT makerFROM ProductWHERE TYPE = 'printer' ; MAKER-----DDEEEHH7 record(s) selected.d)SELECT model,ram ,screenFROM LaptopWHERE price > 1500 ; MODEL RAM SCREEN ----- ------ -------2001 2048 20.12005 1024 17.02006 2048 15.42010 2048 15.44 record(s) selected.e)SELECT *FROM PrinterWHERE color ;MODEL CASE TYPE PRICE----- ----- -------- ------3001 TRUE ink-jet 993003 TRUE laser 9993004 TRUE ink-jet 1203006 TRUE ink-jet 1003007 TRUE laser 2005 record(s) selected.Note: Implementation of Boolean type is optional in SQL standard (feature IDT031). PostgreSQL has implementation similar to above example. Other DBMS provide equivalent support. E.g. In DB2 the column type can be declare as SMALLINT with CONSTRAINT that the value can be 0 or 1. The result can be returned as Boolean type CHAR using CASE.CREATE TABLE Printer(model CHAR(4) UNIQUE NOT NULL,color SMALLINT ,type VARCHAR(8) ,price SMALLINT ,CONSTRAINT Printer_ISCOLOR CHECK(color IN(0,1)));SELECT model,CASE colorWHEN 1THEN 'TRUE'WHEN 0THEN 'FALSE'ELSE 'ERROR'END CASE ,type,priceFROM PrinterWHERE color = 1;f)SELECT model,hdFROM PCWHERE speed = 3.2AND price < 2000;MODEL HD----- ------1005 2501006 3202 record(s) selected.6.1.4a)SELECT class,countryFROM ClassesWHERE numGuns >= 10 ; CLASS COUNTRY------------------ ------------ Tennessee USA1 record(s) selected.b)SELECT name AS shipName FROM ShipsWHERE launched < 1918 ; SHIPNAME------------------HarunaHieiKirishimaKongoRamilliesRenownRepulseResolutionRevengeRoyal OakRoyal Sovereign11 record(s) selected.c)SELECT ship AS shipName, battleFROM OutcomesWHERE result = 'sunk' ; SHIPNAME BATTLE------------------ ------------------ Arizona Pearl Harbor Bismark Denmark Strait Fuso Surigao Strait Hood Denmark Strait Kirishima Guadalcanal Scharnhorst North Cape Yamashiro Surigao Strait 7 record(s) selected.d)SELECT name AS shipNameFROM ShipsWHERE name = class ;SHIPNAME------------------IowaKongoNorth CarolinaRenownRevengeYamato6 record(s) selected.e)SELECT name AS shipNameFROM ShipsWHERE name LIKE 'R%';SHIPNAME------------------RamilliesRenownRepulseResolutionRevengeRoyal OakRoyal Sovereign7 record(s) selected.Note: As mentioned in exercise 2.4.3, there are some dangling pointers and to retrieve all ships a UNION of Ships and Outcomes is required.Below query returns 8 rows including ship named Rodney.SELECT name AS shipNameFROM ShipsWHERE name LIKE 'R%'UNIONSELECT ship AS shipNameFROM OutcomesWHERE ship LIKE 'R%';f) Only using a filter like '% % %' will incorrectly match name such as ' a b ' since % can match any sequence of 0 or more characters.SELECT name AS shipNameFROM ShipsWHERE name LIKE '_% _% _%' ;SHIPNAME------------------0 record(s) selected.Note: As in (e), UNION with results from Outcomes.SELECT name AS shipNameFROM ShipsWHERE name LIKE '_% _% _%'UNIONSELECT ship AS shipNameFROM OutcomesWHERE ship LIKE '_% _% _%' ;SHIPNAME------------------Duke of YorkKing George VPrince of Wales3 record(s) selected.6.1.5a)The resulting expression is false when neither of (a=10) or (b=20) is TRUE.a = 10b = 20 a = 10 OR b = 20NULL TRUE TRUETRUE NULL TRUEFALSE TRUE TRUETRUE FALSE TRUETRUE TRUE TRUEb)The resulting expression is only TRUE when both (a=10) and (b=20) are TRUE.a = 10b = 20 a = 10 AND b = 20TRUE TRUE TRUEThe expression is always TRUE unless a is NULL.a < 10 a >= 10 a = 10 ANDb = 20TRUE FALSE TRUEFALSE TRUE TRUEd)The expression is TRUE when a=b except when the values are NULL.a b a = bNOT NULL NOT NULL TRUE when a=b; else FALSEe)Like in (d), the expression is TRUE when a<=b except when the values are NULL.a b a <= bNOT NULL NOT NULL TRUE when a<=b; else FALSE6.1.6SELECT *FROM MoviesWHERE LENGTH IS NOT NULL;6.2.1a)SELECT AS starNameFROM MovieStar M,StarsIn SWHERE = S.starNameAND S.movieTitle = 'Titanic'AND M.gender = 'M';b)SELECT S.starNameFROM Movies M ,StarsIn S,Studios TWHERE ='MGM'AND M.year = 1995AND M.title = S.movieTitleAND M.studioName = ;SELECT AS presidentName FROM MovieExec X,Studio TWHERE X.cert# = T.presC#AND = 'MGM';d)SELECT M1.titleFROM Movies M1,Movies M2WHERE M1.length > M2.lengthAND M2.title ='Gone With the Wind' ;e)SELECT AS execNameFROM MovieExec X1,MovieExec X2WHERE Worth > WorthAND = 'Merv Griffin' ;6.2.2a)SELECT R.maker AS manufacturer,L.speed AS gigahertzFROM Product R,Laptop LWHERE L.hd >= 30AND R.model = L.model ; MANUFACTURER GIGAHERTZ------------ ----------A 2.00A 2.16A 2.00B 1.83E 2.00E 1.73E 1.80F 1.60F 1.60G 2.0010 record(s) selected.SELECT R.model,P.priceFROM Product R,PC PWHERE R.maker = 'B'AND R.model = P.model UNIONSELECT R.model,L.priceFROM Product R,Laptop LWHERE R.maker = 'B'AND R.model = L.model UNIONSELECT R.model,T.priceFROM Product R,Printer TWHERE R.maker = 'B'AND R.model = T.model ; MODEL PRICE----- ------1004 6491005 6301006 10492007 14294 record(s) selected.c)SELECT R.makerFROM Product R,Laptop LWHERE R.model = L.model EXCEPTSELECT R.makerFROM Product R,PC PWHERE R.model = P.model ; MAKER-----FG2 record(s) selected.SELECT DISTINCT P1.hd FROM PC P1,PC P2WHERE P1.hd =P2.hdAND P1.model > P2.model ; Alternate Answer:SELECT DISTINCT P.hdFROM PC PGROUP BY P.hdHAVING COUNT(P.model) >= 2 ;e)SELECT P1.model,P2.modelFROM PC P1,PC P2WHERE P1.speed = P2.speed AND P1.ram = P2.ramAND P1.model < P2.model ; MODEL MODEL----- -----1004 10121 record(s) selected.f)SELECT M.makerFROM(SELECT maker,R.modelFROM PC P,Product RWHERE SPEED >= 3.0AND P.model=R.modelUNIONSELECT maker,R.modelFROM Laptop L,Product RWHERE speed >= 3.0AND L.model=R.model) MGROUP BY M.makerHAVING COUNT(M.model) >= 2 ; MAKER-----B1 record(s) selected.6.2.3a)SELECT FROM Ships S,Classes CWHERE S.class = C.classAND C.displacement > 35000;NAME------------------IowaMissouriMusashiNew JerseyNorth CarolinaWashingtonWisconsinYamato8 record(s) selected.b)SELECT ,C.displacement,C.numGunsFROM Ships S ,Outcomes O,Classes CWHERE = O.shipAND S.class = C.classAND O.battle = 'Guadalcanal' ;NAME DISPLACEMENT NUMGUNS------------------ ------------ -------Kirishima 32000 8Washington 37000 92 record(s) selected.Note:South Dakota was also engaged in battle of Guadalcanal but not chosen since it is not in Ships table(Hence, no information regarding it's Class isavailable).SELECT name shipName FROM ShipsUNIONSELECT ship shipName FROM Outcomes ; SHIPNAME------------------ArizonaBismarkCaliforniaDuke of YorkFusoHarunaHieiHoodIowaKing George VKirishimaKongoMissouriMusashiNew JerseyNorth CarolinaPrince of Wales RamilliesRenownRepulseResolutionRevengeRodneyRoyal OakRoyal Sovereign ScharnhorstSouth DakotaTenneseeTennesseeWashingtonWest VirginiaWisconsinYamashiroYamato34 record(s) selected.SELECT C1.countryFROM Classes C1,Classes C2WHERE C1.country = C2.country AND C1.type = 'bb'AND C2.type = 'bc' ; COUNTRY------------Gt. BritainJapan2 record(s) selected.e)SELECT O1.shipFROM Outcomes O1,Battles B1WHERE O1.battle = AND O1.result = 'damaged'AND EXISTS(SELECT B2.dateFROM Outcomes O2,Battles B2WHERE O2.battle= AND O1.ship = O2.shipAND B1.date < B2.date) ;SHIP------------------0 record(s) selected.f)SELECT O.battleFROM Outcomes O,Ships S ,Classes CWHERE O.ship = AND S.class = C.class GROUP BY C.country,O.battleHAVING COUNT(O.ship) > 3; SELECT O.battleFROM Ships S ,Classes C,Outcomes OWHERE C.Class = S.classAND O.ship = GROUP BY C.country,O.battleHAVING COUNT(O.ship) >= 3;6.2.4Since tuple variables are not guaranteed to be unique, every relation Ri should be renamed using an alias. Every tuple variable should be qualified with the alias. Tuple variables for repeating relations will also be distinctlyidentified this way.Thus the query will be likeSELECT A1.COLL1,A1.COLL2,A2.COLL1,…FROM R1 A1,R2 A2,…,Rn AnWH ERE A1.COLL1=A2.COLC2,…6.2.5Again, create a tuple variable for every Ri, i=1,2,...,nThat is, the FROM clause isFROM R1 A1, R2 A2,...,Rn An.Now, build the WHERE clause from C by replacing every reference to some attribute COL1 of Ri by Ai.COL1. In addition apply Natural Join i.e. add condition to check equality of common attribute names between Ri and Ri+1 for all i from 0 to n-1. Also, build the SELECT clause from list of attributes L by replacing every attribute COLj of Ri by Ai.COLj.6.3.1a)SELECT DISTINCT makerFROM ProductWHERE model IN(SELECT modelFROM PCWHERE speed >= 3.0);SELECT DISTINCT R.makerFROM Product RWHERE EXISTS(SELECT P.modelFROM PC PWHERE P.speed >= 3.0AND P.model =R.model);SELECT P1.modelFROM Printer P1WHERE P1.price >= ALL(SELECT P2.priceFROM Printer P2) ;SELECT P1.modelFROM Printer P1WHERE P1.price IN(SELECT MAX(P2.price)FROM Printer P2) ;c)SELECT L.modelFROM Laptop LWHERE L.speed < ANY(SELECT P.speedFROM PC P) ;SELECT L.modelFROM Laptop LWHERE EXISTS(SELECT P.speedFROM PC PWHERE P.speed >= L.speed ) ;SELECT modelFROM(SELECT model,priceFROM PCUNIONSELECT model,priceFROM LaptopUNIONSELECT model,priceFROM Printer) M1WHERE M1.price >= ALL (SELECT priceFROM PCUNIONSELECT priceFROM LaptopUNIONSELECT priceFROM Printer) ;(d) – contd --SELECT modelFROM(SELECT model,priceFROM PCUNIONSELECT model,priceFROM LaptopUNIONSELECT model,priceFROM Printer) M1WHERE M1.price IN(SELECT MAX(price)FROM(SELECT priceFROM PCUNIONSELECT priceFROM LaptopUNIONSELECT priceFROM Printer) M2) ;e)SELECT R.makerFROM Product R,Printer TWHERE R.model =T.model AND T.price <= ALL(SELECT MIN(price)FROM Printer);SELECT R.makerFROM Product R,Printer T1WHERE R.model =T1.model AND T1.price IN(SELECT MIN(T2.price) FROM Printer T2);f)SELECT R1.makerFROM Product R1,PC P1WHERE R1.model=P1.modelAND P1.ram IN(SELECT MIN(ram)FROM PC)AND P1.speed >= ALL(SELECT P1.speedFROM Product R1,PC P1WHERE R1.model=P1.model AND P1.ram IN(SELECT MIN(ram)FROM PC));SELECT R1.makerFROM Product R1,PC P1WHERE R1.model=P1.modelAND P1.ram =(SELECT MIN(ram)FROM PC)AND P1.speed IN(SELECT MAX(P1.speed)FROM Product R1,PC P1WHERE R1.model=P1.model AND P1.ram IN(SELECT MIN(ram)FROM PC));6.3.2a)SELECT C.countryFROM Classes CWHERE numGuns IN(SELECT MAX(numGuns)FROM Classes);SELECT C.countryFROM Classes CWHERE numGuns >= ALL(SELECT numGunsFROM Classes);b)SELECT DISTINCT C.class FROM Classes C,Ships SWHERE C.class = S.classAND EXISTS(SELECT shipFROM Outcomes OWHERE O.result='sunk' AND O.ship = ) ;SELECT DISTINCT C.class FROM Classes C,Ships SWHERE C.class = S.classAND IN(SELECT shipFROM Outcomes OWHERE O.result='sunk' ) ;c)SELECT FROM Ships SWHERE S.class IN(SELECT classFROM Classes CWHERE bore=16) ;SELECT FROM Ships SWHERE EXISTS(SELECT classFROM Classes CWHERE bore =16AND C.class = S.class );SELECT O.battleFROM Outcomes OWHERE O.ship IN(SELECT nameFROM Ships SWHERE S.Class ='Kongo' );SELECT O.battleFROM Outcomes OWHERE EXISTS(SELECT nameFROM Ships SWHERE S.Class ='Kongo' AND = O.ship );SELECT FROM Ships S,Classes CWHERE S.Class = C.ClassAND numGuns >= ALL(SELECT numGunsFROM Ships S2,Classes C2WHERE S2.Class = C2.Class AND C2.bore = C.bore) ;SELECT FROM Ships S,Classes CWHERE S.Class = C.ClassAND numGuns IN(SELECT MAX(numGuns)FROM Ships S2,Classes C2WHERE S2.Class = C2.Class AND C2.bore = C.bore) ;Better answer;SELECT FROM Ships S,Classes CWHERE S.Class = C.ClassAND numGuns >= ALL(SELECT numGunsFROM Classes C2WHERE C2.bore = C.bore) ;SELECT FROM Ships S,Classes CWHERE S.Class = C.ClassAND numGuns IN(SELECT MAX(numGuns)FROM Classes C2WHERE C2.bore = C.bore) ;6.3.3SELECT titleFROM MoviesGROUP BY titleHAVING COUNT(title) > 1 ;6.3.4SELECT FROM Ships S,Classes CWHERE S.Class = C.Class ;Assumption: In R1 join R2, the rows of R2 are unique on the joining columns. SELECT COLL12,COLL13,COLL14FROM R1WHERE COLL12 IN(SELECT COL22FROM R2)AND COLL13 IN(SELECT COL33FROM R3)AND COLL14 IN(SELECT COL44FROM R4) ...6.3.5(a)SELECT ,S.addressFROM MovieStar S,MovieExec EWHERE S.gender ='F'AND Worth > 10000000AND = AND S.address = E.address ;Note: As mentioned previously in the book, the names of stars are unique. However no such restriction exists for executives. Thus, both name and address are required as join columns.Alternate solution:SELECT name,addressFROM MovieStarWHERE gender = 'F'AND (name, address) IN(SELECT name,addressFROM MovieExecWHERE netWorth > 10000000) ;(b)SELECT name,addressFROM MovieStarWHERE (name,address) NOT IN(SELECT name addressFROM MovieExec) ;6.3.6By replacing the column in subquery with a constant and using IN subquery forthe constant, statement equivalent to EXISTS can be found.i.e. replace "WHERE EXISTS (SELECT C1 FROM R1..)" by "WHERE 1 IN (SELECT 1 FROM R1...)"Example:SELECT DISTINCT R.makerFROM Product RWHERE EXISTS(SELECT P.modelFROM PC PWHERE P.speed >= 3.0AND P.model =R.model) ;Above statement can be transformed to below statement.SELECT DISTINCT R.makerFROM Product RWHERE 1 IN(SELECT 1FROM PC PWHERE P.speed >= 3.0AND P.model =R.model) ;6.3.7(a)n*m tuples are returned where there are n studios and m executives. Each studiowill appear m times; once for every exec.(b)There are no common attributes between StarsIn and MovieStar; hence no tuplesare returned.(c)There will be at least one tuple corresponding to each star in MovieStar. Theunemployed stars will appear once with null values for StarsIn. All employedstars will appear as many times as the number of movies they are working in. Inother words, for each tuple in StarsIn(starName), the correspoding tuple fromMovieStar(name)) is joined and returned. For tuples in MovieStar that do nothave a corresponding entry in StarsIn, the MovieStar tuple is returned with nullvalues for StarsIn columns.6.3.8Since model numbers are unique, a full natural outer join of PC, Laptop andPrinter will return one row for each model. We want all information about PCs,Laptops and Printers even if the model does not appear in Product but vice versais not true. Thus a left natural outer join between Product and result above isrequired. The type attribute from Product must be renamed since Printer has atype attribute as well and the two attributes are different.(SELECT maker,model,type AS productTypeFROM Product) RIGHT NATURAL OUTER JOIN ((PC FULL NATURAL OUTER JOIN Laptop) FULL NATURAL OUTER JOIN Printer);Alternately, the Product relation can be joined individually with each ofPC,Laptop and Printer and the three results can be Unioned together. Forattributes that do not exist in one relation, a constant such as 'NA' or 0.0 canbe used. Below is an example of this approach using PC and Laptop.SELECT R.MAKER ,R.MODEL ,R.TYPE ,P.SPEED ,P.RAM ,P.HD ,0.0 AS SCREEN,P.PRICEFROM PRODUCT R,PC PWHERE R.MODEL = P.MODELUNIONSELECT R.MAKER ,R.MODEL ,R.TYPE ,L.SPEED ,L.RAM ,L.HD ,L.SCREEN,L.PRICEFROM PRODUCT R,LAPTOP LWHERE R.MODEL = L.MODEL;6.3.9SELECT *FROM Classes RIGHT NATURALOUTER JOIN Ships ;6.3.10SELECT *FROM Classes RIGHT NATURALOUTER JOIN ShipsUNION(SELECT C2.class ,C2.type ,C2.country ,C2.numguns ,C2.bore ,C2.displacement,C2.class NAME ,FROM Classes C2,Ships S2WHERE C2.Class NOT IN(SELECT ClassFROM Ships)) ;6.3.11(a)SELECT *FROM R,S ;(b)Let Attr consist ofAttrR = attributes unique to RAttrS = attributes unique to SAttrU = attributes common to R and SThus in Attr, attributes common to R and S are not repeated. SELECT AttrFROM R,SWHERE R.AttrU1 = S.AttrU1AND R.AttrU2 = S.AttrU2 ...AND R.AttrUi = S.AttrUi ;(c)SELECT *FROM R,SWHERE C ;6.4.1(a)DISTINCT keyword is not required here since each model only occurs once in PC relation.SELECT modelFROM PCWHERE speed >= 3.0 ;(b)SELECT DISTINCT R.makerFROM Product R,Laptop LWHERE R.model = L.modelAND L.hd > 100 ;(c)SELECT R.model,P.priceFROM Product R,PC PWHERE R.model = P.modelAND R.maker = 'B'UNIONSELECT R.model,L.priceFROM Product R,Laptop LWHERE R.model = L.modelAND R.maker = 'B'UNIONSELECT R.model,T.priceFROM Product R,Printer TWHERE R.model = T.modelAND R.maker = 'B' ;SELECT modelFROM PrinterWHERE color=TRUEAND type ='laser' ;(e)SELECT DISTINCT R.makerFROM Product R,Laptop LWHERE R.model = L.modelAND R.maker NOT IN(SELECT R1.makerFROM Product R1,PC PWHERE R1.model = P.model) ;better:SELECT DISTINCT R.makerFROM Product RWHERE R.type = 'laptop'AND R.maker NOT IN(SELECT R.makerFROM Product RWHERE R.type = 'pc') ;(f)With GROUP BY hd, DISTINCT keyword is not required. SELECT hdFROM PCGROUP BY hdHAVING COUNT(hd) > 1 ;(g)SELECT P1.model,P2.modelFROM PC P1,PC P2WHERE P1.speed = P2.speedAND P1.ram = P2.ramAND P1.model < P2.model ;SELECT R.makerFROM Product RWHERE R.model IN(SELECT P.modelFROM PC PWHERE P.speed >= 2.8)OR R.model IN(SELECT L.modelFROM Laptop LWHERE L.speed >= 2.8)GROUP BY R.makerHAVING COUNT(R.model) > 1 ;(i)After finding the maximum speed, an IN subquery can provide the manufacturer name.SELECT MAX(M.speed)FROM(SELECT speedFROM PCUNIONSELECT speedFROM Laptop) M ;SELECT R.makerFROM Product R,PC PWHERE R.model = P.modelAND P.speed IN(SELECT MAX(M.speed)FROM(SELECT speedFROM PCUNIONSELECT speedFROM Laptop) M)UNIONSELECT R2.makerFROM Product R2,Laptop LWHERE R2.model = L.modelAND L.speed IN(SELECT MAX(N.speed)FROM(SELECT speedFROM PCUNIONSELECT speedFROM Laptop) N) ;Alternately,SELECT COALESCE(MAX(P2.speed),MAX(L2.speed),0) SPEED FROM PC P2FULL OUTER JOIN Laptop L2ON P2.speed = L2.speed ;SELECT R.makerFROM Product R,PC PWHERE R.model = P.modelAND P.speed IN(SELECT COALESCE(MAX(P2.speed),MAX(L2.speed),0) SPEED FROM PC P2FULL OUTER JOIN Laptop L2ON P2.speed = L2.speed)UNIONSELECT R2.makerFROM Product R2,Laptop LWHERE R2.model = L.modelAND L.speed IN(SELECT COALESCE(MAX(P2.speed),MAX(L2.speed),0) SPEED FROM PC P2FULL OUTER JOIN Laptop L2ON P2.speed = L2.speed)SELECT R.makerFROM Product R,PC PWHERE R.model = P.modelGROUP BY R.makerHAVING COUNT(DISTINCT speed) >= 3 ;(k)SELECT R.makerFROM Product R,PC PWHERE R.model = P.modelGROUP BY R.makerHAVING COUNT(R.model) = 3 ;better;SELECT R.makerFROM Product RWHERE R.type='pc'GROUP BY R.makerHAVING COUNT(R.model) = 3 ;6.4.2(a)We can assume that class is unique in Classes and DISTINCT keyword is not required.SELECT class,countryFROM ClassesWHERE bore >= 16 ;(b)Ship names are not unique (In absence of hull codes, year of launch can help distinguish ships).SELECT DISTINCT name AS Ship_NameFROM ShipsWHERE launched < 1921 ;(c)SELECT DISTINCT ship AS Ship_NameFROM OutcomesWHERE battle = 'Denmark Strait'AND result = 'sunk' ;(d)SELECT DISTINCT AS Ship_NameFROM Ships S,Classes CWHERE S.class = C.classAND C.displacement > 35000 ;SELECT DISTINCT O.ship AS Ship_Name,C.displacement ,C.numGunsFROM Classes C ,Outcomes O,Ships SWHERE C.class = S.classAND = O.shipAND O.battle = 'Guadalcanal' ;SHIP_NAME DISPLACEMENT NUMGUNS------------------ ------------ -------Kirishima 32000 8Washington 37000 92 record(s) selected.Note: South Dakota was also in Guadalcanal but its class information is not available. Below query will return name of all ships that were in Guadalcanal even if no other information is available (shown as NULL). The above query is modified from INNER joins to LEFT OUTER joins.SELECT DISTINCT O.ship AS Ship_Name,C.displacement ,C.numGunsFROM Outcomes OLEFT JOIN Ships SON = O.shipLEFT JOIN Classes CON C.class = S.classWHERE O.battle = 'Guadalcanal' ;SHIP_NAME DISPLACEMENT NUMGUNS------------------ ------------ -------Kirishima 32000 8South Dakota - -Washington 37000 93 record(s) selected.(f)The Set opearator UNION guarantees unique results.SELECT ship AS Ship_NameFROM OutcomesUNIONSELECT name AS Ship_NameFROM Ships ;(g)SELECT C.classFROM Classes C,Ships SWHERE C.class = S.classGROUP BY C.classHAVING COUNT() = 1 ;better:SELECT S.classFROM Ships SGROUP BY S.classHAVING COUNT() = 1 ;(h)The Set opearator INTERSECT guarantees unique results.SELECT C.countryFROM Classes CWHERE C.type='bb'INTERSECTSELECT C2.countryFROM Classes C2WHERE C2.type='bc' ;However, above query does not account for classes without any ships belonging to them.SELECT C.countryFROM Classes C,Ships SWHERE C.class = S.classAND C.type ='bb'INTERSECTSELECT C2.countryFROM Classes C2,Ships S2WHERE C2.class = S2.classAND C2.type ='bc' ;SELECT O2.ship AS Ship_Name FROM Outcomes O2,Battles B2WHERE O2.battle = AND B2.date > ANY(SELECT B.dateFROM Outcomes O,Battles BWHERE O.battle = AND O.result ='damaged' AND O.ship = O2.ship);6.4.3a)SELECT DISTINCT R.maker FROM Product R,PC PWHERE R.model = P.modelAND P.speed >= 3.0;b)Models are unique.SELECT P1.modelFROM Printer P1LEFT OUTER JOIN Printer P2 ON (P1.price < P2.price) WHERE P2.model IS NULL ;c)SELECT DISTINCT L.model FROM Laptop L,PC PWHERE L.speed < P.speed ;Due to set operator UNION, unique results are returned.It is difficult to completely avoid a subquery here. One option is to use Views. CREATE VIEW AllProduct ASSELECT model,priceFROM PCUNIONSELECT model,priceFROM LaptopUNIONSELECT model,priceFROM Printer ;SELECT A1.modelFROM AllProduct A1LEFT OUTER JOIN AllProduct A2ON (A1.price < A2.price)WHERE A2.model IS NULL ;But if we replace the View, the query contains a FROM subquery. SELECT A1.modelFROM(SELECT model,priceFROM PCUNIONSELECT model,priceFROM LaptopUNIONSELECT model,priceFROM Printer) A1LEFT OUTER JOIN(SELECT model,priceFROM PCUNIONSELECT model,priceFROM LaptopUNIONSELECT model,priceFROM Printer) A2ON (A1.price < A2.price) WHERE A2.model IS NULL ;e)SELECT DISTINCT R.makerFROM Product R,Printer TWHERE R.model =T.modelAND T.price <= ALL(SELECT MIN(price)FROM Printer);f)SELECT DISTINCT R1.makerFROM Product R1,PC P1WHERE R1.model=P1.modelAND P1.ram IN(SELECT MIN(ram)FROM PC)AND P1.speed >= ALL(SELECT P1.speedFROM Product R1,PC P1WHERE R1.model=P1.modelAND P1.ram IN(SELECT MIN(ram)FROM PC));6.4.4a)SELECT DISTINCT C1.countryFROM Classes C1LEFT OUTER JOIN Classes C2 ON (C1.numGuns < C2.numGuns) WHERE C2.country IS NULL ;。
答案仅供参考第一章数据库系统概述选择题B、B、A简答题1.请简述数据,数据库,数据库管理系统,数据库系统的概念。
P27数据是描述事物的记录符号，是指用物理符号记录下来的，可以鉴别的信息。
数据库即存储数据的仓库，严格意义上是指长期存储在计算机中的有组织的、可共享的数据集合。
数据库管理系统是专门用于建立和管理数据库的一套软件，介于应用程序和操作系统之间。
数据库系统是指在计算机中引入数据库技术之后的系统，包括数据库、数据库管理系统及相关实用工具、应用程序、数据库管理员和用户。
2.请简述早数据库管理技术中，与人工管理、文件系统相比，数据库系统的优点。
数据共享性高数据冗余小易于保证数据一致性数据独立性高可以实施统一管理与控制减少了应用程序开发与维护的工作量3.请简述数据库系统的三级模式和两层映像的含义。
P31答：数据库的三级模式是指数据库系统是由模式、外模式和内模式三级工程的，对应了数据的三级抽象。
两层映像是指三级模式之间的映像关系，即外模式/模式映像和模式/内模式映像。
4.请简述关系模型与网状模型、层次模型的区别。
P35使用二维表结构表示实体及实体间的联系建立在严格的数学概念的基础上概念单一，统一用关系表示实体和实体之间的联系，数据结构简单清晰，用户易懂易用存取路径对用户透明，具有更高的数据独立性、更好的安全保密性。
第二章关系数据库选择题C、C、D简答题1.请简述关系数据库的基本特征。
P48答：关系数据库的基本特征是使用关系数据模型组织数据。
2.请简述什么是参照完整性约束。
P55答：参照完整性约束是指：若属性或属性组F是基本关系R的外码，与基本关系S的主码K 相对应，则对于R中每个元组在F上的取值只允许有两种可能，要么是空值，要么与S中某个元组的主码值对应。
3.请简述关系规范化过程。
答：对于存在数据冗余、插入异常、删除异常问题的关系模式，应采取将一个关系模式分解为多个关系模式的方法进行处理。
一个低一级范式的关系模式，通过模式分解可以转换为若干个高一级范式的关系模式，这就是所谓的规范化过程。
第六章关系数据理论第六章讲解关系数据理论。
这是关系数据库的又一个重点。
学习本章的目的有两个。
一个是理论方面的，本章用更加形式化的关系数据理论来描述和研究关系模型。
另一个是实践方面的，关系数据理论是我们进行数据库设计的有力工具。
因此，人们也把关系数据理论中的规范化理论称为数据库设计理论，有的书把它放在数据库设计部分介绍以强调它对数据库设计的指导作用。
一、基本知识点本章讲解关系数据理论，内容理论性较强，分为基本要求部分(《概论》6.1～6.3)和高级部分《概论》6.4)。
前者是计算机大学本科学生应该掌握的内容；后者是研究生应该学习掌握的内容。
①需要了解的:什么是一个“不好”的数据库模式；什么是模式的插入异常和删除异常；规范化理论的重要意义。
②需要牢固掌握的:关系的形式化定义；数据依赖的基本概念(函数依赖、平凡函数依赖、非平凡的函数依赖、部分函数依赖、完全函数依赖、传递函数依赖的概念，码、候选码、外码的概念和定义，多值依赖的概念)；范式的概念；从lNF 到4NF的定义；规范化的含义和作用。
③需要举一反三的:四个范式的理解与应用，各个级别范式中存在的问题(插入异常、删除异常、数据冗余)和解决方法；能够根据应用语义，完整地写出关系模式的数据依赖集合，并能根据数据依赖分析某一个关系模式属于第几范式。
④难点:各个级别范式的关系及其证明。
二、习题解答和解析1.理解并给出下列术语的定义：函数依赖、部分函数依赖、完全函数依赖、传递依赖、候选码、主码、外码、全码(All-key)、lNF、2NF、3NF、BCNF、多值依赖、4NF。
解析解答本题不能仅仅把《概论》上的定义写下来。
关键是真正理解和运用这些概念。
答函数依赖:设R(U)是一个关系模式,U是R的属性集合,X和Y是U的子集。
对于R(U)的任意一个可能的关系r,如果r中不存在两个元组，它们在X上的属性值相同，而在Y上的属性值不同，则称“X函数确定Y”或“Y函数依赖于X”，记作X→Y。
练习题66.1 名词解释1)事务：事务是构成单一逻辑工作单元的操作集合。
要么完整地执行，要么完全不执行。
2)数据库的一致性：一个事务独立执行的结果，应保持数据库的一致性，即数据不会因事务的执行而遭受破坏。
3)数据库的可恢复性：系统能把数据库从被破坏、不确定的状态，恢复到最近一个正确的状态，DBMS的这种能力称为数据库的可恢复性（Recovery）4)并发操作：在多用户共享系统中，许多事务可能同时对同一数据进行操作，这种操作称为并发操作。
5)封锁：封锁是系统保证对数据项的访问以互斥方式进行的一种手段。
6)X锁：事务T对某数据加了X锁后，其他事务要等T解除X锁后，才能对这个数据进行封锁。
7)S锁：事务T对某数据加了S锁后，仍允许其他事务再对该数据加S锁，但在对该数据的所有S锁都解除之前决不允许任何事务对该数据加X锁。
8)调度：事务的执行次序称为“调度”。
9)串行调度：多个事务依次执行，称为事务的串行调度。
10)并发调度：利用分时的方法，同时处理多个事务，则称为事务的并发调度。
11)可串行化调度：如果一个并发调度的执行结果与某一串行调度的执行结果等价，那么这个并发调度称为“可串行化的调度”。
12)不可串行化调度：如果一个并发调度的执行结果不与某一串行调度的执行结果等价，那么这个并发调度称为“不可串行化调度”。
6.2事务的COMMIT语句和ROLLBACK语句各做什么事情？答：COMMIT（提交）：语句表示事务执行成功地结束，此时告诉系统，DB要进入一个新的正确状态，该事务对DB的所有更新都已交付实施（写入磁盘）。
ROLLBACK（“回退”或“回滚”）：语句表示事务执行不成功地结束，此时告诉系统，已发生错误，DB可能处在不正确的状态，该事务对DB的所有更新必须被撤消，DB应恢复该事务到初始状态。
6.3试叙述事务的四个性质，并解释每一个性质由DBMS的哪个子系统实现？每一个性质对DBS有什么益处？答：①事务的原子性：是指一个事务对DB的所有操作，是一个不可分割的工作单元。
