Information Systems Analysis and Design CSC340© 2003 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 SchemaTranslation into a Relational SchemaThe Training Company ExampleThe Training Company ExampleNormal Forms and Normalization of Relational SchemasNormal Forms and Normalization of Relational SchemasInformation Systems Analysis and Design CSC340© 2003 John Mylopoulos Database Design -- 2DatabasesDatabases A databasedatabase is a collection of persistent data shared by a number ofapplications. Databases have been founded on the concept of datadataindependenceindependence: Applications should not have to know theorganization of the data or the access strategy employed⇒⇒Need query processing facility, which generates automaticallyNeed query processing facility, which generates automaticallyan access plan, given a queryan access plan, given a query Databases also founded on the concept of data sharingsharing:Applications should be able to work on the same data concurrently,without knowing of each others’ existence.⇒⇒Database procedures defined in terms of atomic operations calledDatabase procedures defined in terms of atomic operations calledtransactionstransactionsInformation Systems Analysis and Design CSC340© 2003 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 Design CSC340© 2003 John Mylopoulos Database Design -- 4Database ConceptsDatabase ConceptsData modelData model -- defines a set of data structures along with associatedoperations, for building and accessing a database e.g., the relational model offers relations (tables) as datastructure for building a databaseDatabase management systemDatabase management system (DBMS) -- generic tool for building,accessing, updating and managing a database E.g., Oracle, DB2, Access,… are all relational DBMSsDatabase schemaDatabase schema -- describes the types and structure of the datastored in the database; consists of one or more relation schemas E.g., Employee(emp#,name,addr,sal,dept,mngr)TransactionTransaction -- 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 it made tothe database. E.g., TransferFunds(fromAcct#,toAcct#,amount,date)Information Systems Analysis and Design CSC340© 2003 John Mylopoulos Database Design -- 5Types of DatabasesTypes of DatabasesConventional databasesConventional databases -- (relationalrelational, networknetwork, hierarchicalhierarchical) consistof recordsrecords of many different record types (database looks like acollection of files)Object-Oriented databasesObject-Oriented databases -- database consists of objects (andpossibly associated programs); database schema consists of classes(which can be objects too).Multimedia databasesMultimedia databases -- database can store formatted data (i.e.,records) but also text, pictures,...Active databasesActive databases -- database includes event-condition-action rulesDeductive databasesDeductive databases* -- like large Prolog programsHypertext databasesHypertext databases -- store and access efficiently HTML/XMLdocuments; provide navigational facilities through a database, so thata user can retrieve and/or browse.* -- * -- not available commerciallynot available commerciallyInformation Systems Analysis and Design CSC340© 2003 John Mylopoulos Database Design -- 6Database Management SystemsDatabase Management SystemsHost Programming LanguageDMLDDL4GLDBMSDatabaseDMLDML -- data manipulationlanguageDDLDDL -- data definitionlanguage (allows definition ofdatabase schema)4GL4GL -- fourth generationlanguage, useful fordeclarative query processing,report generation,...Information Systems Analysis and Design CSC340© 2003 John Mylopoulos Database Design -- 7The Hierarchical Data ModelThe Hierarchical Data Model Database consists of hierarchical record structureshierarchical record structures; a field mayhave as value a list of records; every record has at most oneparentB36538 ElmWar & Peace $8.99TorontoJan 28, 1994 Feb 24, 1994parentparentchildrenchildrenBookBookBorrowerBorrowerBorrowingBorrowingInformation Systems Analysis and Design CSC340© 2003 John Mylopoulos Database Design -- 8The Network Data ModelThe Network Data Model A database now consists of records with pointers (links) to otherrecords. Offers a navigational view of a database.CustomerCustomerPartPartRegionRegionOrderOrderOrderedPartOrderedPartSales HistorySales Historycycles of links are allowed1::n linkInformation Systems Analysis and Design CSC340© 2003 John Mylopoulos Database Design -- 9The Relational Data ModelThe Relational Data Model A database now consists of sets of records or (equivalently) setsof tuples (relations) or (equivalently) tables; no links allowed in thedatabase. Every tuple is an element of exactly one relation and is identifieduniquely by a primary keyCustomerCustomerCustCust##NameNameAddressAddress11271127GeorgeGeorge25 Mars St25 Mars St13771377MariaMaria12 Low Ave.12 Low Ave.15321532ManolisManolis1 1 BloorBloor St.
View Full Document