资源描述：
《大型数据库设计ch04》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、ChapterFour:DatabaseDesignUsingNormalizationDatabaseProcessing:Fundamentals,Design,andImplementation1ChapterPremiseWehavereceivedoneormoretablesofexistingdataThedataistobestoredinanewdatabaseQUESTION:Shouldthedatabestoredasreceived,orshoulditbetransformedforstorage?2HowManyTables?Shouldwestore
2、thesetwotablesastheyare,orshouldwecombinethemintoonetableinournewdatabase?3AssessingTableStructure4CountingRowsinaTableTocountthenumberofrowsinatableusetheSQLbuilt-infunctionCOUNT(*):SELECTCOUNT(*)ASNumRowsFROMSKU_DATA;5ExaminingtheColumnsTodeterminethenumberandtypeofcolumnsinatable,useanSQLSE
3、LECTstatementTolimitthenumberofrowsretrieved,usetheSQLTOP{NumberOfRows}keyword:SELECTTOP(10)*FROMSKU_DATA;6CheckingValidityofAssumedReferentialIntegrityConstraintsGiventwotableswithanassumedforeignkeyconstraint:SKU_DATA(SKU,SKU_Description,Department,Buyer)BUYER(BuyerName,Department)WhereSKU_D
4、ATA.BuyermustexistinBUYER.BuyerName7CheckingValidityofAssumedReferentialIntegrityConstraintsTofindanyforeignkeyvaluesthatviolatetheforeignkeyconstraint:SELECTBuyerFROMSKU_DATAWHEREBuyerNOTIT(SELECTBuyerFROMSKU_DATA,BUYERWHERESKU_DATA.BUYER=BUYER.BuyerName;8TypeofDatabaseUpdateabledatabaseorrea
5、d-onlydatabase?Ifupdateabledatabase,wenormallywanttablesinBCNFIfread-onlydatabase,wemaynotuseBCNFtables9Designing UpdateableDatabases10Normalization: AdvantagesandDisadvantages11Non-NormalizedTable: EQUIPMENT_REPAIR12NormalizedTables: ITEMandREPAIR13CopyingDatatoNewTablesTocopydatafromonetable
6、toanother,usetheSQLcommandINSERTINTOTableNamecommand:INSERTINTOITEMSELECTDISTINCTItemNumber,Type,AcquisitionCostFROMEQUIPMENT_REPAIR;INSERTINTOREPAIRSELECTItemNumber,RepairNumber,RepairDate,RepairAmmountFROMEQUIPMENT_REPAIR;14ChoosingNottoUseBCNFBCNFisusedtocontrolanomaliesfromfunctionaldepend
7、enciesTherearetimeswhenBCNFisnotdesirableTheclassicexampleisZIPcodes:CUSTOMERS(Number,Name,Street,City,State,Zip)ZIPcodesalmostneverchangeAnyanomaliesarelikelytobecaughtbynormalbusinesspracticesNothavingtouseSQLtojoindataintwotableswill
