数据库处理课后习题答案.docx

数据库处理课后习题答案.docx

《数据库处理课后习题答案.docx》由会员分享，可在线阅读，更多相关《数据库处理课后习题答案.docx（88页珍藏版）》请在冰豆网上搜索。

数据库处理课后习题答案

《数据库处理》复习要点及参考答案

最近更新时间：

4/11/2021

第一章MicrosoftAccess2007（第一次作业）

复习要点

（1）.知识网络图

图

（2）.基本的定义：

a.DBS:

=用户+数据库应用程序+DBMS+DB。

各个部分有什么作用

b.元数据metadata

（3）.Access的使用-作业

CreateaMicrosoftAccessdatabasenamed.

AnswerstotheProjectQuestionsarecontainedinthedatabase,whichisavailableonthetext’sWebsite（databaseiscreatedasdescribedinAppendixA.Thetwotablestobecreatedare:

DEPARTMENT（DepartmentName,BudgetCode,OfficeNumber,Phone）

EMPLOYEE（EmployeeNumber,FirstName,LastName,Department,Phone,Email）

WhereAnunderlinedcolumnnameindicatesthetablekey（primarykey）ofthetable,andanitalicizedcolumnindicatesaforeignkeylinkingtwotables.

Figure1-26showsthecolumncharacteristicsfortheWPCDEPARTMENTtable.Usingthecolumncharacteristics,createtheDEPARTMENTtableinthedatabase.

Figure1-27showsthedatafortheWPCDEPARTMENTtable.UsingDatasheetview,enterthedatashowninFigure1-27intoyourDEPARTMENTtable.

Figure1-28showsthecolumncharacteristicsfortheWPCEMPLOYEEtable.Usingthecolumncharacteristics,createtheEMPLOYEEtableinthedatabase.

CreatetherelationshipandreferentialintegrityconstraintbetweenDEPARTMENTandEMPLOYEE.Enableenforcingofreferentialintegrityandcascadingofdataupdates,butdonotenablecascadingofdatafromdeletedrecords.

UsingtheMicrosoftAccessformwizard,createadatainputformfortheEMPLOYEEtableandnameitWPCEmployeeDataForm.Makeanyadjustmentsnecessarytotheformsothatalldatadisplayproperly.UsethisformtoentertherestofthedataintheEMPLOYEEtableshowninFigure1-29intoyourEMPLOYEEtable.

UsingtheAccessreportwizard,createareportnamedWedgewoodPacificCorporationEmployeeReportthatpresentsthedatacontainedinyourEMPLOYEEtablesortedfirstbyemployeelastnameandthenbyemployeefirstname.Makeanyadjustmentsnecessarytothereportsothatallheadingsanddatadisplayproperly.Printacopyofthisreport.

Toproducethereportasshownbelow,someworkintheReportDesignviewisnecessary–takethetimetoshowyourstudentshowtomodifyreportformatsinReportDesignview.

UsingtheMicrosoftAccessformwizard,createaformthathasallofthedatafrombothtables.Whenaskedhowyouwanttoviewyourdata,selectbyDEPARTMENT.Choosethedefaultoptionsforotherquestionsthatthewizardasks.Openyourformandpagethroughyourdepartments.

Toproducethereportasshownbelow,someworkintheFormDesignviewisnecessary–takethetimetoshowyourstudentshowtomodifyreportformatsinFormDesignview.

UsingtheAccessreportwizard,createareportthathasallofthedatafrombothtables.Whenaskedhowyouwanttoviewyourdata,selectbyDEPARTMENT.ForthedatacontainedinyourEMPLOYEEtableinthereport,specifythatitwillbesortedfirstbyemployeelastnameandthenbyemployeefirstname.Makeanyadjustmentsnecessarytothereportsothatallheadingsanddatadisplayproperly.Printacopyofthisreport.

Toproducethereportasshownbelow,someworkintheReportDesignviewisnecessary–takethetimetoshowyourstudentshowtomodifyreportformatsinReportDesignview.

Explain,tothelevelofdetailinthischapter,whatisgoingonwithinMicrosoftAccessinProjectQuestions,,,and.WhatsubcomponentcreatedtheformandreportWhereisthedatastoredWhatroledoyouthinkSQLisplaying

AccessusesSQLSELECTstatementstoquerythedatabasetablesforthedatatobedisplayedintheformsandthereport.Theresultsofthequeryarestoredinatemporarytablecreatedtoholdthisdata,andthistableisthesourceofthedatadisplayedintheformandthereport.SQLisusedtogatherthedataneededfordisplayintheformandreport.

第二章结构化查询语言简介（第二次作业）

复习要点

（1）.定义DDL、DML。

p32.

（2）.SQL的写法-基本、重要。

SELECT...FROM...WHERE...ORDERBY...GROUPBY...HAVING...IN...EXISTS...JOIN...ON

（3）.难点：

多表连接、相关子查询、谓词计算

（4）.发现数据模式-动脑、扩展。

例如题目.

（5）.实验教材。

A.TheChangeCloseonFridays.

SELECTChangeClose

FROMNDX

WHERETDayOfWeeK='Friday';

B.

Theminimum,maximum,andaverageChangeCloseonFridays.

SELECTMIN（ChangeClose）ASMinFridayChangeClose,

MAX（ChangeClose）ASMaxFridayChangeClose,

AVG（ChangeClose）ASAverageFridayChangeClose

FROMNDX

WHERETDayOfWeeK='Friday';

C.TheaverageChangeClosegroupedbyTYear.ShowTYear.

SELECTTYear,AVG（ChangeClose）ASAverageChangeClose

FROMNDX

GROUPBYTYear

ORDERBYTYear;

D.TheaverageChangeClosegroupedbyTYearandTMonth.ShowTYearandTMonth.

SinceTYearandTMontharebeingdisplayed,itmakessensetosorttheresultsbyTYearandTMonthalthoughthisisnotexplicitlystatedinthequestion.

SELECTTYear,TMonth,

AVG（ChangeClose）ASAverageChangeClose

FROMNDX

GROUPBYTYear,TMonth

ORDERBYTYear,TMonth;

Unfortunately,thetableNDXdoesnotcontainanumericvalueofthemonth,soinordertosortthemonthscorrectly,weneedaTMonthNumberwhichhasacolumncontainingarepresentativenumberforeachmonth（January=1,February=2,etc.）.Intheanddatabases,thiscolumnisincludedinatablenamedNDX_FULL.

SELECTTYear,TMonth,

AVG（ChangeClose）ASAverageFridayChangeClose

FROMNDX_Full

GROUPBYTYear,TMonth,TMonthNumber

ORDERBYTYear,TMonthNumber;

E.

TheaverageChangeClosegroupedbyTYear,TQuarter,TMonthshownindescendingorderoftheaverage（youwillhavetogiveanametotheaverageinordertosortbyit）.ShowTYear,TQuarter,andTMonth.Notethatmonthsappearinalphabeticalandnotcalendarorder.Explainwhatyouneedtodotoobtainmonthsincalendarorder.

SELECTTYear,TQuarter,TMonth,

AVG（ChangeClose）ASAverageChangeClose

FROMNDX

GROUPBYTYear,TQuarter,TMonth

ORDERBYAverageChangeCloseDESC;

Unfortunately,asdiscussedabove,MicrosoftAccesscannotprocesstheORDERBYclausecorrectlywhenanSQLbuilt-infunctionisused.

Thecorrectresult,obtainedfromSQLServer2008,is:

Inordertoobtainthemonthsincalendarorder,wewouldhavetouseanumericalvalueforeachmonth（1,2,3,…,12）andsortbythosevalues.

F.ThedifferencebetweenthemaximumChangeCloseandtheminimumChangeClosegroupedbyTYear,TQuarter,TMonthshownindescendingorderofthedifference（youwillhavetogiveanametothedifferenceinordertosortbyit）.ShowTYear,TQuarter,andTMonth.

SELECTTYear,TQuarter,TMonth,

（MAX（ChangeClose）–MIN（ChangeClose））ASDifChangeClose

FROMNDX

GROUPBYTYear,TQuarter,TMonth

ORDERBYDifChangeCloseDESC;

Unfortunately,asdiscussedabove,MicrosoftAccesscannotprocesstheORDERBYclausecorrectlybecauseitcontainsanaliasedcomputedresult.

Thecorrectresult,obtainedfromSQLServer2008,is:

G.TheaverageChangeClosegroupedbyTYearshownindescendingorderoftheaverage（youwillhavetogiveanametotheaverageinordertosortbyit）.Showonlygroupsforwhichtheaverageispositive.

SELECTTYear,

AVG（ChangeClose）ASAverageChangeClose

FROMNDX

GROUPBYTYear

HAVINGAVG（ChangeClose）>0

ORDERBYAverageChangeCloseDESC;

Unfortunately,asdiscussedabve,MicrosoftAccesscannotprocesstheORDERBYclausecorrectlybecauseitcontainsanaliasedcomputedresult.

Thecorrectresult,obtainedfromSQLServer2008,is:

H.Displayasinglefieldwiththedateintheform:

day/monthy/year.Donotbeconcernedwithtrailingblanks.

ThesolutiontothisquestionrequiresthestudenttousetheDBMShelpfunctionorotherreferencestofigureoutaconversionfunctiontoconvertthenumericaldayofthemonthtoacharacterstringthatcanbecombinedwithotherdataalreadyincharacterformat.

ThetableNDXdoesnothaveanumericvalueformonth,sothenamesofthemonthswillappearinthesolution.Ifwewantthenumericvalueofthemonth,wecouldusetheNDX_Fulltable,whichhasanumericvalue.Wewouldneedtousethedatatypeconversiononthisfieldaswell.

TheSQLStatementusingSQLServer2008characterstringfunctionsis:

SELECTCAST（TDayOfMonthASChar

（2））+'/'+

TMonth+'/'+TYearASDisplayDate

FROMNDX

WHERETDayOfMonth=25

ANDTMonth='September'

ANDTYear='2001';

TheSQLServer2008resultis:

TheSQLStatementusingMicrosoftAccess2007characterstringfunctionsis:

SELECTCStr（TDayOfMonth）+'/'+

TMonth+'/'+TYearASDisplayDate

FROMNDX

WHERE=25

AND='September'

AND='2001';

TheMicrosoftAccess2007resultis:

Itispossiblethatvolume（thenumberofsharestraded）hassomecorrelationwiththedirectionofthestockmarket.UsetheSQLyouhavelearnedinthischaptertoinvestigatethatpossibility.DevelopatleastfivedifferentSQLstatementsinyourinvestigation.

Ifvolumeiscorrelatedwiththedirectionofthestockmarket,thismeansthatthereshouldbeeither:

（1）POSITIVECORRELEATION:

Highervolumewhenthemarketcloseshigher,or

（2）NEGATIVECORRELATION:

Highervolumewhenthemarketcloseslower.

WhendoesthemarketclosehigherWhenispositive.

SELECTTMonth,TDayOfMonth,TYear,ChangeClose

FROMNDX

WHEREChangeClose>0;

WhendoesthemarketcloselowerWhenisnegative.

SELECTTMonth,TDayOfMonth,TYear,ChangeClose

FROMNDX

WHEREChangeClose<0;

Now,whataretheaveragepositiveandnegativechanges

SELECTAVG（ChangeClose）ASAvgPositiveChange

FROMNDX

WHEREChangeClose>0;

SELECTAVG（ChangeClose）ASAvgNegativeChange

FROMNDX

WHEREChangeClose<0;

Now,whataretheaveragevolumesassociatedwiththepositiveandnegativechanges

SELECTAVG（ChangeClose）ASAvgPositiveChange,

AVG（Volume）ASAvgVolumeOnPositiveChange

FROMND