A Comparsion of Databases and DataWarehouses

AComparsionofDatabasesandDataWarehousesName:LilianaLivorováSubject:DistributedDataProcessingContentConceptDefinitionsofDatabases,DataWarehousesDatabasemodelsHistoryDatabasesDataWarehousesOLTPvs.DataWarehouseConceptDefinitionDatabaseastructuredcollectionofrecordsordataDataWarehousealogicalcollectionofinformation,gatheredfrommanydifferentoperationaldatabases,thatsupportsbusinessanalysisactivitiesanddecision-makingtasksDatabasemodelsisthestructureorformatofadatabase,describedinaformallanguagesupportedbythedatabasemanagementsystemDatabasemodelsrelationalflathierarchicalnetworkdimensionalobjectdatabaseDataWarehouse•relationaldatabasemodelHistoryDatabase1960-thefirstdatabasemanagementsystem1970-thefirstrelationalmodel1980-distributeddatabasesystemsanddatabasemachines1990-object-orienteddatabases2000-XMLdatabaseDataWarehouseItbecameadistincttypeofcomputerdatabaseduringthelate1980sandearly1990sDatabasecollectionofrelateddatadatabasemanagementsystem(DBMS)isacollectionofprogramsthatenablesuserstocreateandmaintainadatabaseusedinmanyapplicationsusedinalle-commercesitestostoreproductinventoryandcustomerinformationDataWarehouses“Adatawarehouseissimplyasingle,complete,andconsistentstoreofdataobtainedfromavarietyofsourcesandmadeavailabletoendusersinawaytheycanunderstandanduseitinabusinesscontext.”--BarryDevlin,IBMConsultantDataWarehousesarecordofanenterprise'spasttransactionalandoperationalinformationdesignedtofavorefficientdataanalysisandreportingdatawarehousingisnotmeantforcurrentlivedataDataWarehouseslargeamountsofdata–sometimessubdividedintosmallerlogicalunits(dependentdatamarts)datastoringinadatawarehousesaretematicallyconsistentandconcernconcreteproblemorinstitutionsDataWarehousesComponentsofadatawarehouse:Sources-DataSourceInteractionDataTransformationDataWarehouse(DataStorage)Reporting(DataPresentation)MetadataDataWarehousesADVANTAGEScompletecontroloverthefourmainareasofdatamanagementsystems:CleandataQueryprocessing:multipleoptionsIndexes:multipletypesSecurity:dataandaccessDataWarehousesDISADVANTAGESAddingnewdatasourcestakestimeandassociatedhighcostDataownerslosecontrolovertheirdata,raisingownership,securityandprivacyissuesLonginitialimplementationtimeandassociatedhighcostDifficulttoaccommodatechangesindatatypesandranges,datasourceschema,indexesandqueriesOLTPvs.OLAPOLTP:OnLineTransactionProcessingDescribesprocessingatoperationalsitesOLAP:OnLineAnalyticalProcessingDescribesprocessingatwarehouseOLTPDatabasevs.DataWarehouserelationaldatabases-groupsdatausingcommonattributesfoundinthedatasetobjectivesaredifferentOLTPdatabaseDataWarehouseDesignedforrealtimebusinessoperationsDesignedforanalysisofbusinessmeasuresbycategoriesandattributesOLTPdatabaseDataWarehouseMostlyupdatesManysmalltransactionsMb-GbofdataMostlyreadsQueriesarelongandcomplexGb-TbofdataOLTPdatabaseDataWarehouseCurrentsnapshotRawdataThousandsofusers(e.g.,clericalusers)HistorySummarized,reconcileddataHundredsofusers(e.g.,decision-makers,analysts)SUMMARYfourquestionsforyouDesignedforrealtimebusinessoperationsDesignedforanalysisofbusinessmeasuresbycategoriesandattributes12DesignedforrealtimebusinessoperationsDesignedforanalysisofbusinessmeasuresbycategoriesandattributesDataWarehouseOLTPdatabaseOptimizedforbulkloadsandlarge,complex,unpredictablequeriesthataccessmanyrowspertable.Optimizedforacommonsetoftransactions,usuallyaddingorretrievingasinglerowatatimepertable.12Optimizedforbulkloadsandlarge,complex,unpredictablequeriesthataccessmanyrowspertable.Optimizedforacommonsetoftransactions,usuallyaddingorretrievingasinglerowatatimepertable.OLTPdatabaseDataWarehouseLoadedwithconsistent,validdata;requiresnorealtimevalidation.Optimizedforvalidationofincomingdataduringtransactions;usesvalidationdatatables.12Loadedwithconsistent,validdata;requiresnorealtimevalidation.Optimizedforvalidationofincomingdataduringtransactions;usesvalidationdatatables.OLTPdatabaseDataWarehouseSupportsthousandsofconcurrentusers.SupportsfewconcurrentusersrelativetoOLTP.12Supportsthousandsofconcurrentusers.SupportsfewconcurrentusersrelativetoOLTP.DataWarehouseOLTPdatabaseSources(blog)~franconi/teaching/2001/CS636/CS636-dw-intro.pptforyourattention!