数据库系统基础教程第一版数据库习题答案ch10.docx

《数据库系统基础教程第一版数据库习题答案ch10.docx》由会员分享，可在线阅读，更多相关《数据库系统基础教程第一版数据库习题答案ch10.docx（20页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Solutions ManualWHERE mult = single)UNION(SELECT PathSingle. class, Rei.rclassFROM PathSingle, ReiWHERE PathSingle. rclass = Rei. classAND Rei. mult =single)SELECT class, rclassFROM PathAllEXCEPTSELECT class, rclassFROM PathSingle(e) Ne include the edge label as part of the recursive relation and th

2、en, basically, we build the path as in (a) except we only add edges that have an opposite label.WITH RECURSIVE Path(class, rclass, mult) AS (SELECT class, rclass, multFROM Rei)UNION(SELECT Path, class, Rei. rclass, Rei. multFROM Path, ReiWHERE Path, rclass = Rei. classAND Path, mult Rei. mult )SELEC

3、T *FROM Path; WITH RECURSIVE Path(class, rclass) AS (SELECT class, rclassFROM ReiWHERE mult =single)UNION(SELECT Path. class, Rei.rclassFROM Path, ReiWHERE Path, rclass = Rei. classAND Rei. mult = single )SELECT *FROM Path XWHERE EXISTS(SELECTFROMWHEREAND1Path YY. class = X. rclassY. rclass = X. cla

4、ss )Section 10.3(a) Stars (name, address, birthdate)Movies (title, year, length, stars (*Stars)(b) Stars(name, address, birthdate)Movies (title, year, length, stars (*Stars)Studios(name, address, movies (*Movies()(c) Stars (name, address, birthdate)Movies (title, year, length, studio (name, address)

5、, stars (住Stars)ssNo, accts (*Accounts) owners (*Customers)ssNo, accts (*Accounts) owner(Customers)Customers(name, address, phone, Accounts(number, type, balance,Customers(name, address, phone, Accounts(number, type, balance,Players(name)Teams (name, players (*Players), Eans(name, fav teams(*Tcams)

6、captain(*Players), colors) fav_players (*Players) fav_color)People(name, mother(*People), father(*People), chiIdren(*People()Section 10.4Movies (title year length genre studioName producerC# )MovieStar( name address gender birthdate)Starsln( movieTitle movieYear starName)MovicExec( name address cert

7、# netWorthTitleType, YearType, DurationType, GenreType, BusinessNameType, CertificateTypePersonNameType, AddressType, GenderType, DateTypeTitleType, YearType, PersonNameTypePersonNameType, AddressType, CertificateType, CurrencyTypeStudio(name address presC# )BusinessNameType, AddressType, Certificat

8、eType(a) CREATE TYPE NameType AS( first VARCHAR(30), middle VARCHAR(50), last VARCHAR(30), title VARCHAR(IO)；(b) CREATE TYPE PersonType AS( name NameType, mother REF(PersonType), father REF(PersonType) )；CREATE TYPE MarriageType AS( dateDATE,husband REF(PersonType), wifeREF(PersonType)；CREATE TYPE P

9、roductType AS( makerCHAR (5),modelINTEGER,typeCHAR (8)；CREATE TABLE Product OF ProductType( REF IS Productld SYSTEM GENERATED)；CREATE TABLE model speed ram hd price)；PC(REF (ProductType) DECIMAL (5,2), INTEGER, INTEGERDECIMAL (10, 2)SCOPEProduct,CREATE TABLE model speed ram hd screen price )；Laptop(

10、REF(ProductType) DECIMAL(5,2), INTEGER, INTEGERDECIMAL (5,2), DECIMAL (10, 2)SCOPEProduct,CREATE TABLE model color type pricePrinter(REF (ProductType) CIIAR(l), VARCHAR(IO), DECIMAL(10, 2)SCOPEProduct,);Model attribute in Products cannot be a reference to the tuple in the relation for that type of p

11、roduct because that would create a circular reference situation where the model is a reference to the relation itself which has a model attribute but is a reference, etc. There would not be a column that stores the actual model values.CREATE TYPE class type country numGunsCREATE TYPE class type coun

12、try numGunsClassType AS ( VARCHAR(30), CHAR(2), VACIIAR(30), INTEGER,);bore disp )；INTEGER, INTEGERCREATE TYPEname classShipType AS ( VARCHAR (30), REF(ClassType),launched)；INTEGERCREATE TYPEname date)；BattleType AS ( VARCHAR (30), DATECREATE TYPE ship battle resultOutcomeType AS ( REF(ShipType), RE

13、F(BattleType), VARCHAR(10)CREATE TABLE Classes OF ClassTypeREF IS classID SYSTEM GENERATED)；CREATE TABLE Ships OF ShipType( REF IS shipID SYSTEM GENERATEDCREATE TABLE Battles OF TYPE BattleType( REF IS battleTD SYSTEM GENERATEDCREATE TABLE Outcomes OF TYPE OutcomeType( REF IS outcomelD SYSTEM GENERA

14、TEDSection 10.5(a) SELECT star-nameFROM StarslnWHERE movic-titlc = Dogma;(b) SELECT DISTINCT movie-title, movie-yearFROM StarslnWHERE star-address. cityO = * Malibu ;(c) SELECT FROM WHEREmovieStarslnstar-name = Melanie GriffithJ ;(d) SELECT FROM GROUP BY HAVINGmovic-title, movie-year Starslnmovie-ti

15、tie, movie-yearCOUNT(*) = 5;(a) SELECT FROM WHEREmodel-makerPChd 60;(b) SELECT DISTINCT model-makerFORM PrintersWHERE type = lasef ;(c) WITH MaxSpeedsPerMaker(maker, maxSpeed) AS(SELECT model-maker, MAX(speed)FROM LaptopsGROUP BY model-maker),MakerTopModel(maker,topModel) AS(SELECT M. maker, L. mode

16、l-modelFROM Laptops L, MaxSpeedsPerMaker MWHERE L. model-maker = M. makerAND L. speed= maxSpeed )SELECT model-model, topModel FROM Laptops L, MakerTopModel MWHERE L. model-maker = M. maker(a) SELECT x. name FROM Ships x WHERE x.class-disp 35000;SELECT DISTINCT x.battle-name FROMOutcomesxWHEREx. resu

17、lt=sunk* ;(b) SELECT DISTINCT x.class-classFROMShips xWHEREx.launched1930;(c) SELECTDISTINCTx.battle-nameFROMOutcomesxWHEREx. result=damagecfAND x. ship-class_country = USA;StarType, StarType )CREATE FUNCTION StarLEG(pl p2RETURNS INTEGERIF ELSEIF ELSE END IFpl. name p2. namep2. nameTHEN RETURN (-1)T

18、HEN RETURN( 1)RETURN(AddrLEG(pl. address,p2. addres)CREATE ORDERING FOR StarTypeORDERING FULL BY RELATIVE WITH StarLEG;CREATE PROCEDURE DeleteStar(IN pName VARCHAR(50) BEGINDELETE FROM StarslnWHERE star-name = pName;DELETE FROM MovieStar xWHERE x.name = pName;END;Section 10.6(a) Dimension attributes

19、 are: cust, date, proc, memory, hd, od. Dependent attributes are: quant, price.(b) Cust(custID, name, address, phone, creditCard) Proc(procID, manufacturer, name, model, speed) IID(hdID, manufacturer, name, model, capacity, cylinders, surfaces, speed)OD(odID, manufacturer, type, capacity, speed)Firs

20、t we could select the number of orders that had DVD disks and the number of orders that had CD disks. This would show just the totals over all orders.SELECT DI. type, COUNT(*)FROMOrders F, 01) DIWHEREF. od = Dl.odlDGROUP BY DI. typeHAVING DI. type INC DVH ； CU ) Then we could drill-down to see what

21、the totals are per month, hopefully seeing that the numbers for DVDs increase and the numbers for CDs decrease.SELECT FROM WHERE GROUP BY HAVINGMONTH(F. date) MONTHS, DI. type, COUNT(*) Orders F, OD DI F. od = DI. odID MONTHS, DI. typeDI. type INC DVU / C1T )Next we could drill-up to show the totals

22、 per year.SELECT FROM WHERE GROUP BY HAVINGYE/R(F. date) YEARS, DI. type, COUNT(*) Orders F, OD DI F. od = DI. odIDYEARS, DI. typeDI. type INC DVU CU )10(a) The ratio is 1110!,or about 2.59.io(b) The ratio is 32,or about 57.66.(a) Assuming the column name for SUM(val) in SalesCube is val:SELECTdeale

23、r, valFROMSalesCubeWHEREmodel IS NULLANDcolor = blueANDdate IS NULLANDdealer IS NOT NULL(b) Assuming the column name for SUM(cnt) in SalesCube is ent:Chapter 10Section 10.1(a) SELECT on MovieStar, SELECT on MovieExec.(b) SELECT on MovieExec, SELECT on Movies, SELECT on Starsln.(c) SELECT on Movies,

24、SELECT on Studio, INSERT on Studio (or INSERT(name) on Studio).(d) DELETE on Starsln.(e) UPDATE on MovieExec (or UPDATE(name) on MovieExec).(f) REFERENCES on MovieStar (or REFERNCES(gender, name) on MovieS- tar).(g) REFERENCES on Studio, REFERENCES on MovieExec (or REFERENCES(name, presC#) on Studio

25、, REFERENCES(ccrt#, net Worth) on MovieExec).SELECTFROMWHERE AND AND ANDent SalesCube model = Gobi color = green date IS NULLdealer = Sni1 in Sally(c) Assuming the column names for SUM(cnt) and SUM(val) in SalesCubc arc ent and val:SELECTval/cntFROMSalesCubeWHEREmodel = GobiANDcolor IS NULLAND YEAR(

26、date) = 2007ANDMONTH(date) =3ANDdealer IS NOTNULLThe rollup would not help and would make it more difficult to ensure that we do not double count the rows and only consider the rows that are in CUBE(Sales) but not in Sales.CREATE MATERIALIZED VIEW OrdersCube(cust, date, proc, memory, hd, od, tquant,

27、 tprice)AS(SELECT cust, date, proc, memory, hd, od, SUM(quant), SUM(price)FROM OrdersGROUP BY cust, date, proc, memory, hd, od)WITH CUBE;(a) SELECT DI. speed, MONTH (F. date), SUM(F. tquant)FROM OrdersCube F, Proc DIWHERE F. proc = DI. proc ID AND F. cust IS NULLAND YEAR(F. date) = 2007AND F. memory

28、 IS NULLAND F. hd IS NULL, AND F. od TS NULLGROUP BY DI. speed, MONTH(F, date)(b)SELECTDI. type, D2. type, SUM(F. tquant)FROMOrdersCube F, Proc DI, HD D2WHEREF. proc = DI. procIDANDF. hd = D2. hdIDANDF. cust IS NULLANDF.date IS NULLANDF.memory IS NULLANDF. od IS NULLGROUPBY DI. type, D2. type(c) SEL

29、ECT MONTH(F. date), SUM(tprice)/SUM(F. tquant) FROM OrdersCube F, Proc DIWHEREF. proc = DI. procIDANDDI. speed = 3. 0ANDF.cust IS NULLANDF. date = 01/01/2005ANDF. memory IS NULLANDF. hd IS NULL,ANDF. od IS NULLGROUPBY MONTH (F. date)Yes, other rollups could contain these tuples. Those rollups can be

30、 formed by rearranging the group by list so that columns we need to be aggregated are at the tail of the list. For instance, to include tuple Gobf , NULL, 2001-05-2V , Friendly Fred , 152000, 7)The group by list would be:GROUP BY model, date, dealer, color WITH ROLLUPIn the worst case, the fact tabl

31、e could have only one row, the CUBE(F) would add an additional 2n tuples, and so the ratio would be 2.After step (4), the grant diagram is as follows:After step (5), the grant diagram is as follows:After step (6), the grant diagram is as follows:After step (5), the grant diagram is as follows:The gr

32、ant diagram after the final step is asSection 10.2(a) The rules for trips that have reasonable connections are:Trips(x ,arr) - Flights(, arr),y,dep,x, y,depTrips(x ,arr) - Trips(x, arr1)AND,y, dcp, z, deplTrips(z,arr2)AND,y, dep2arrl 6 dep2 -100(b) Using the books syntax, the SQL is:WITH RECURSIVE T

33、rips(frm, to, dep, arr) AS (SELECT frm, to, dep, arrFROM Flights)UNION(SELECT T. frm, F. to, T. dep, F. arrFROM Trips T, Flights FWHERE T. to = F. fromAND T. arr = 2;This is, in a sense, a reverse of (e) above, because to have at most one fol- lowon means that the total count of the tuples grouped b

34、y the given movie x must be no greater than 2 (one for the movie and its sequel, and the other for the sequel and its sequel). Using the books syntax, the SQL would be:WITH RECURSIVE FollowOn(movie, followOn) AS (SELECT movie, sequel FROM SequelOf )UNION(SELECT F. movie, S.sequelFROM FollowOn F, Seq

35、uel SWHERE F. followOn = S.movie)SELECT movie, followonFROM FollowOnWHERE movie IN(SELECT movieFROM FollowOnGROUP BY movieHAVING COUNT(followon) = 2);WITH RECURSIVE Path(class, rclass) AS (SELECT class, rclassFROM Rei)UNION(SELECT Path, class, Rei.rclassFROM Path, ReiWHERE Path, rclass = Rei. class)

36、SELECT *FROM Path;WITH RECURSIVE Path(class, rclass) AS (SELECT class, rclassFROM ReiWHERE mu 11 = single)UNION(SELECT Path. class, Rei. rclass FROM Path, ReiWHERE Path, rclass = Rei. classAND Rei. mul t = f singl e* ) SELECT * FROM Path;WITH RECURSIVE Path (class, rclass) AS (SELECT class, rclassFR

37、OM ReiWHERE mult = multi)UNION(SELECT Path, class, Rei. rclassFROM Path, ReiWHERE Path, rclass = Rei. class)UNION(SELECT Rei.class, Path, rclassFROM Path, ReiWHERE Rei.rclass = Path, class)SELECT *FROM Path;(a) This could be viewed as relation from (a) EXCEPT relation from (b).WITH RECURSIVE PathAll(class, rclass) AS (SELECT class, rclassFROM Rei)UNION(SELECT PathAll. class, Rei.rclassFROM PathAll, ReiWHERE PathAll. rclass = Rei. class),RECURSIVE PathSingle(class, rclass) AS (SELECT class, rclassFROM Rei