Tutorial3a.docx
《Tutorial3a.docx》由会员分享，可在线阅读，更多相关《Tutorial3a.docx（21页珍藏版）》请在冰豆网上搜索。
Tutorial3a
Tutorial3
1.Reviewquestions:
（1）WhatisOLAP?
WhatarethecharacteristicsofOLAPcomparedwithOLTP?
OnlineAnalyticalProcessing（OLAP）isprocessofanalysingcurrentandhistoricaldatatoidentifyusefulpatternsandsupportbusinessstrategies.TheemphasisforOLAPisoncomplex,interactive,exploratoryanalysisofverylargedatasetscreatedbyintegratingdatafromacrossallpartsofanenterprise.ForexampleAcompanyanalysespurchasesbyallcustomerstocomeupwithnewproductsoflikelyinteresttothecustomers.OLAPdataisusuallystoredinaDataWarehouse.OnlineAnalyticalProcessingvsOnlineTransactionProcessing:
OLTP
OLAP
ManyUpdates
MostlyReads,Updatesarerare
ManySmallTransactions
Querieslong,complex
QuickResponse
Allowsslowerresponse
Mb-GbofData
Gb-TbodData
RawData
Summarized,consolidatedData
Up-To-DateData
CurrentandHistoricalData
ClericalUsers
Decision-Makers,Analystsasusers
Consistency,RecoverabilityCritical
Minorinconsistencyisallowed
（2）Whatisadatawarehouse?
Howdoesitdifferfroma（transactional）database?
Adatawarehouseisarepositoryofinformationgatheredfrommultiplesources,storedunderaunifiedschema,usuallyatasinglesite.Thedatamaybeaugmentedwithadditionalattributes,suchastimestampsandsummaryinformation.Dataisstoredforalongtime,permittingaccesstohistoricaldata.Thereisaninteractiveresponsetimeexpectedforcomplexqueries,ad-hocupdatesareuncommon.TraditionaldatabasesaregenerallyusedforOLTPandarenormalisedusingcomplextablejoinstoreduceredundantdata,operationsareoptimisedforwriting.DataWarehousesdifferinthattheynormallydonotnormalisetheinformationtoreducetheresponsetimeinanalyticalprocessing,havehigherperformancesinanalyticalqueriesandareoptimisedforreadoperations.
（3）Explaintheterms:
DataCubes,Multi-Dimensionaldatamodel,ROLAP,MOLAP,StarSchema,SnowflakeSchema,Roll-Up,Drill-Down,Slicing-and-Dicing.
DataCubes:
Thegeneralisationofacrosstab,whichis2-dimensional,inton-dimensions（forexample,forCarSales:
ColourvsMakevsSize）.
Multi-DimensionalDataModels:
Datathatcanbemodelledasdimensionattributesandmeasureattributesarecalledmulti-dimensionaldata（i.e.“num_sold”isameasureattributesinceitcanbeusedtomeasuresomevalueandcanbeaggregated,“make,colour,size”arecalleddimensionalattributessincetheydefinethedimensionsofwhichmeasureattributesareviewed）.
MOLAP:
MultidimensionalOLAPSystems（MOLAP）arewhenOLAPsystemsusemulti-dimensionalarraystostoredatacubes.
ROLAP:
RelationalOLAPSystems（ROLAP）arewhenOLAPsystemsuserelationsinarelationaldatabasetostorethedata.Themainrelationwhichrelatesdimensionstomeasuresarecalledthefacttable（i.e.sales（prod_id,date,sho_id,num_sold）–itisverylargeandistheaccumulationoffactssuchassales）.Eachdimensioncanhaveadditionalattributesandanassociateddimensionaltable（i.e.product（prod_id,price,colour）whereprod_idisaforeignkey,shops（shop_id,location,manager））.Dimensiondataissmallerandgenerallystatic.
StarSchema:
Astarschemaconsistsofthefacttableandoneormoredimensiontables.Dimensiontablesareusuallynotnormalisedtoincreasethereadspeedsincethereisnotaneedtojoinmultipletablestogether.Atypicalqueryofteninvolvesajoinofthefacttableandthedimensiontable.
SnowflakeSchema:
VariationoftheStarSchemawherethedimensiontablesarenormalised.
Roll-Up:
Movingfromfinergranularitytocoarsergranularitybymeansofaggregation（i.e.giventotalsalesforeachcity,findthetotalsalesforeachstate）.
Drill-Down:
Theinverseofroll-up（i.e.movingfromcoarsergranularitytofinergranularity）.
Slicing-and-Dicing:
Slicingtheexistingdatasetstofindspecificinformationrelatingtocertainconditions（i.e.Fromthedatacube,findthecross-tabonModelandColourforMediumCars）.Thecross-Tabcanbeviewedasasliceofthedatacube.
（4）Explainwhatmaterializedviewsareandwhattheirroleisindatawarehousesystems.
MaterialisedViewsaredatabaseobjectsthatcontainstheresultsofqueries（i.e.CREATEVIEWASSELECTCOUNT（sid）FROMStudent;）.Theyareimportantindatawarehousesastheremaybesituationswhereexpensiveaggregationsareneededanditmaynotbepossibletopre-computealltheaggregates.Amaterialisedviewcanbeusedtostoretheaggregateddatasothattheresponsetimeismuchfasterthanthetraditionalqueryingprocesses.
2.Labexperiments:
Createauseraccountforyourselfusingthefollowingscript（addingwhatevertextyouwouldlikein“your_name”and“my_password”aslongasyouwillnotforgeteither）:
Createuseryour_nameidentifiedbymy_password;
Grantyourselfbasicdatabaseprivilegesusingthefollowingcommandsreplacing“your_name”withtheusernameyougaveyouraccountintheabovestatement:
Grantconnecttoyour_name;
Grantcreatetabletoyour_name;
Alteruseryour_namedefaulttablespaceUSERS;
Alteruseryour_nametemporarytablespaceTEMP;
Alteruseryour_namequotaunlimitedonUSERS;
LogoutoftheSYSTEMaccount,thenlogintoyournewaccount.Createthetablesandinsertthedatausingthegivenscriptfileusingthe“@C:
\dbbook.sql”commandonce“dbbook.sql”hasbeendownloadedfromthecoursewebsiteandputintoCdrive.
ExperimentwithOracleSQLaggregates,rollup,partialrollup,andcubeextensiontogroupby.
SQLAggregates:
--Findtheoldeststudent.
SELECTMAX（age）FROMStudent;
--Findtheaveragebudgetfromthedepartments
SELECTAVG（budget）FROMdept;
--Findthenumberofflights
SELECTCOUNT（flno）FROMflights;
SQLRoll-Ups:
--SelecttheaverageageRoll-Upforeachstudentgroupedbymajorandstanding
SELECTmajor,standing,AVG（age）ASavg_age_student
FROMstudent
GROUPBYROLLUP（major,standing）
ORDERBYmajor,standing;
SQLPartialRoll-Ups:
--SelecttheaverageageRoll-Upforeachstudentgroupedbymajorandstanding
--Note:
Thiswillonlyworkiftherearemultiplestudentswiththesamenamesthathavedifferentmajorsandstandings（JohnSmith?
）
SELECTmajor,standing,AVG（age）ASavg_age_student
FROMstudent
GROUPBYROLLUP（major,standing）
ORDERBYmajor,standing;
SQLCubeExtension:
--SelecttheaverageageRoll-Upforeachstudentgroupedbymajorandstanding
SELECTmajor,standing,AVG（age）ASavg_age_student
FROMstudent
GROUPBYCUBE（major,standing）
ORDERBYmajor,standing;
SQLScripts:
droptablestudentcascadeconstraints;
droptablefacultycascadeconstraints;
droptableclasscascadeconstraints;
droptableenrolledcascadeconstraints;
droptableempcascadeconstraints;
droptableworkscascadeconstraints;
droptabledeptcascadeconstraints;
droptableflightscascadeconstraints;
droptableaircraftcascadeconstraints;
droptablecertifiedcascadeconstraints;
droptableemployeescascadeconstraints;
droptablesupplierscascadeconstraints;
droptablepartscascadeconstraints;
droptablecatalogcascadeconstraints;
droptablesailorscascadeconstraints;
--
--Now,addeachtable.
--
createtablestudent（
snumnumber（9,0）primarykey,
snamevarchar2（30）,
majorvarchar2（25）,
standingvarchar2
（2）,
agenumber（3,0）
）;
createtablefaculty（
fidnumber（9,0）primarykey,
fnamevarchar2（30）,
deptidnumber（2,0）
）;
createtableclass（
namevarchar2（40）primarykey,
meets_atvarchar2（20）,
roomvarchar2（10）,
fidnumber（9,0）,
foreignkey（fid）referencesfaculty
）;
createtableenrolled（
snumnumber（9,0）,
cnamevarchar2（40）,
primarykey（snum,cname）,
foreignkey（snum）referencesstudent,
foreignkey（cname）referencesclass（name）
）;
createtableemp（
eidnumber（9,0）primarykey,
enamevarchar2（30）,
agenumber（3,0）,
salarynumber（10,2）
）;
createtabledept（
didnumber（2,0）primarykey,
dnamevarchar2（20）,
budgetnumber（10,2）,
manageridnumber（9,0）,
foreignkey（managerid）referencesemp（eid）
）;
createtableworks（
eidnumber（9,0）,
didnumber（2,0）,
pct_timenumber（3,0）,
primarykey（eid,did）,
foreignkey（eid）referencesemp,
foreignkey（did）referencesdept
）;
createtableflights（
flnonumber（4,0）primarykey,
originvarchar2（20）,
destinationvarchar2（20）,
distancenumber（6,0）,
departsdate,
arrivesdate,
pricenumber（7,2）
）;
createtableaircraft（
aidnumber（9,0）primarykey,
anamevarchar2（30）,
crusingrangenumber（6,0）
）;
createtableemployees（
eidnumber（9,0）primarykey,
enamevarchar2（30）,
salarynumber（10,2）
）;
createtablecertified（
eidnumber（9,0）,
aidnumber（9,0）,
primarykey（eid,aid）,
foreignkey（eid）referencesemployees,
foreignkey（aid）referencesaircraft
）;
createtablesuppliers（
sidnumber（9,0）primarykey,
snamevarchar2（30）,
addressvarchar2（40）
）;
createtableparts（
pidnumber（9,0）primarykey,
pnamevarchar2（40）,
colorvarchar2（15）
）;
createtablecatalog（
sidnumber（9,0）,
pidnumber（9,0）,
costnumber（10,2）,
primarykey（sid,pid）,
foreignkey（sid）referencessuppliers,
foreignkey（pid）referencesparts
）;
createtablesailors（
sidnumber（9,0）primarykey,
snamevarchar2（30）,
ratingnumber（2,0）,
agenumber（4,1）
）;
--
--NowInserttheData
--
INSERTINTOstudentVALUES（051135593,'MariaWhite','English','SR',21）;
INSERTINTOstudentVALUES（060839453,'CharlesHarris','Architecture','SR',22）;
INSERTINTOstudentVALUES（099354543,'SusanMartin','Law','JR',20）;
INSERTINTOstudentVALUES（112348546,'JosephThompson','ComputerScience','SO',19）;
INSERTINTOstudentVALUES（115987938,'ChristopherGarcia','ComputerScience','JR',20）;
INSERTINTOstudentVALUES（132977562,'
升级会员 