Information Systems Analysis and DesignCSC340© 2004 John Mylopoulos Database Design -- 1XX. Database DesignDatabases and DBMSDatabases and DBMSData Models, Hierarchical, Network, RelationalData Models, Hierarchical, Network, RelationalDatabase DesignDatabase DesignRestructuring an ER schemaRestructuring an ER schemaPerformance analysisPerformance analysisAnalysis of Redundancies, Removing generalizationsAnalysis of Redundancies, Removing generalizationsTranslation into a Relational Translation into a Relational SchemSchemNormal Forms and NormalizationNormal Forms and NormalizationInformation Systems Analysis and DesignCSC340© 2004 John Mylopoulos Database Design -- 2DatabasesDatabases A databasedatabase is a collection of persistent data shared bya number of applications. Databases have been founded on the concept of datadataindependenceindependence: Applications should not have to knowthe organization of the data or the access strategyemployed to fetch the data.⇒⇒ Query facility, query optimization Query facility, query optimization Databases also founded on the concept of data sharingsharing:Applications should be able to work on the same dataconcurrently, without knowing of each others’ existence.⇒⇒Database transactionsDatabase transactionsInformation Systems Analysis and DesignCSC340© 2004 John Mylopoulos Database Design -- 3Conventional Files Conventional Files vsvs Databases DatabasesFilesFilesAdvantagesAdvantages -- many already exist; -- many already exist;good for simple applications; verygood for simple applications; veryefficientefficientDisadvantagesDisadvantages -- data duplication; -- data duplication;hard to evolve; hard to build forhard to evolve; hard to build forcomplex applicationscomplex applicationsDatabasesDatabasesAdvantagesAdvantages -- Good for data -- Good for dataintegration; allow for more flexibleintegration; allow for more flexibleformats (not just records)formats (not just records)DisadvantagesDisadvantages -- high cost; -- high cost;drawbacks in a centralized facilitydrawbacks in a centralized facilityThe future is with databases!The future is with databases!Information Systems Analysis and DesignCSC340© 2004 John Mylopoulos Database Design -- 4Database ConceptsDatabase ConceptsData modelData model -- defines a set of data structures along withassociated operations, for building and accessing a databaseDatabase management systemDatabase management system (DBMS) -- generic tool forbuilding, accessing, updating and managing a database E.g., Oracle, DB2, Access,… are all relational DBMSsDatabase schemaDatabase schema -- describes the types and structure of thedata stored in the database; consists of one or more relationschemasTransactionTransaction -- an atomic operation on a database; looks like aprocedure but has different semantics: when called, it eithercompletes its execution, or aborts and undoes all changes itmade to the database.Information Systems Analysis and DesignCSC340© 2004 John Mylopoulos Database Design -- 5Types of DatabasesTypes of DatabasesConventional databasesConventional databases -- (relationalrelational, networknetwork,hierarchicalhierarchical) consist of recordsrecords of many different recordtypes (database looks like a collection of files)Object-Oriented databasesObject-Oriented databases -- database consists ofobjects (and possibly associated programs); databaseschema consists of classes (which can be objects too).Multimedia databasesMultimedia databases -- database can store formatteddata (i.e., records) but also text, pictures,...Active databasesActive databases -- database includes event-condition-action rules …more…Information Systems Analysis and DesignCSC340© 2004 John Mylopoulos Database Design -- 6Database Management SystemsDatabase Management SystemsHost Programming LanguageDMLDDL4GLDBMSDatabaseDMLDML -- data manipula-tion languageDDLDDL -- data definitionlanguage (allows defini-tion of database sche-ma)4GL4GL -- fourth generationlanguage, useful fordeclarative query proce-ssing, report genera-tion,...Information Systems Analysis and DesignCSC340© 2004 John Mylopoulos Database Design -- 7The Hierarchical Data ModelThe Hierarchical Data Model Database consists of hierarchical record structureshierarchical record structures;a field may have as value a list of records; every recordhas at most one parentB36538 ElmWar & Peace $8.99TorontoJan 28, 1994 Feb 24, 1994parentparentchildrenchildrenBookBookBorrowerBorrowerBorrowingBorrowingInformation Systems Analysis and DesignCSC340© 2004 John Mylopoulos Database Design -- 8The Network Data ModelThe Network Data Model A database now consists of records with pointers (links)to other records. Offers a navigational view of adatabase.CustomerCustomerPartPartRegionRegionOrderOrderOrderedPartOrderedPartSales HistorySales Historycycles of links are allowed1::n linkInformation Systems Analysis and DesignCSC340© 2004 John Mylopoulos Database Design -- 9The Relational Data ModelThe Relational Data Model A database consists of sets of records or (equivalently)sets of tuples (relations) or (equivalently) tables; no linksallowed in the database. Every tuple is an element of exactly one relation and isidentified uniquely by a primary keyCustomerCustomerCustCust##NameNameAddressAddress11271127GeorgeGeorge25 Mars St25 Mars St13771377MariaMaria12 Low Ave.12 Low Ave.15321532ManolisManolis1 1 BloorBloor St. St.......OrderOrderOrdOrd##DateDateAmountAmount1997199711/3/9311/3/93$65.87$65.874237423725/8/9325/8/93$126.88$126.881552155212/12/9312/12/93$284.21$284.21......Part#Part#DescDescQuantityQuantity23972397widgetwidget12,98012,98029082908nutnut16,000doz.16,000doz.65906590boltbolt14,340doz14,340doz......PartPartPart#Part#OrdOrd##QuantityQuantity23972397199719979809802908290819971997100doz.100doz.659065904237423740doz40doz......Ordered PartOrdered PartInformation Systems Analysis and DesignCSC340© 2004 John Mylopoulos Database Design -- 10Comparing Data ModelsComparing Data Models The oldest DBMSs were hierarchical, dating back to the mid-60s. IMS (IBM product) is the most popular among them. The network data model came next (early ‘70s). Emphasis on“navigating” -- chasing pointers -- around a database. Network model was found to be in many respects tooimplementation-oriented, machine-dependent. The relational model is the most recent arrival
View Full Document
Unlocking...