Information Systems Analysis and Design CSC340 Information Systems Analysis and Design Databases XX Database Design Databases and and DBMS DBMS Data Models Models Hierarchical Hierarchical Network Network Relational Relational Database Design Design Restructuring an ER ER schema schema Restructuring an Performance analysis Performance analysis Analysis of of Redundancies Redundancies Removing Removinggeneralizations generalizations Translation into into aa Relational RelationalSchem Schem Normal Forms and and Normalization Normalization 2004 John Mylopoulos A database is a collection of persistent data shared by a number of applications Databases have been founded on the concept of data independence Applications should not have to know the organization of the data or the access strategy employed to fetch the data facility query queryoptimization optimization Query facility Databases also founded on the concept of data sharing Applications should be able to work on the same data concurrently without knowing of each others existence transactions Database transactions Database Design 1 Information Systems Analysis and Design CSC340 2004 John Mylopoulos CSC340 Database Concepts Data model defines a set of data structures along with associated operations for building and accessing a database Database management system DBMS generic tool for building accessing updating and managing a database E g Oracle DB2 Access are all relational DBMSs Database schema describes the types and structure of the data stored in the database consists of one or more relation schemas Transaction an atomic operation on a database looks like a procedure but has different semantics when called it either completes its execution or aborts and undoes all changes it made to the database Databases Advantages Good for data integration allow for more flexible formats not just records Files Advantages many already exist good for simple applications very Disadvantages high cost efficient drawbacks in a centralized facility Disadvantages data duplication hard to evolve hard to build for complex applications The future is is with with databases databases Database Design 3 Information Systems Analysis and Design CSC340 2004 John Mylopoulos Database Design 4 Information Systems Analysis and Design CSC340 Database Management Systems Types of Databases Conventional databases relational relational network network hierarchical hierarchical consist of records of many different record types database looks like a collection of files Object Oriented databases database consists of objects and possibly associated programs database schema consists of classes which can be objects too Multimedia databases database can store formatted data i e records but also text pictures Active databases database includes event conditionaction rules more 2004 John Mylopoulos Database Design 2 Information Systems Analysis and Design Conventional Files vs Databases 2004 John Mylopoulos CSC340 Database Design 5 DML data manipulation language DDL data definition language allows definition of database schema 4GL fourth generation language useful for declarative query processing report generation 2004 John Mylopoulos Host Programming Language DBMS DML DDL 4GL Database Database Design 6 Information Systems Analysis and Design CSC340 Information Systems Analysis and Design CSC340 The Hierarchical Data Model The Network Data Model Database consists of hierarchical record structures a field may have as value a list of records every record has at most one parent A database now consists of records with pointers links to other records Offers a navigational view of a database Book B365 Customer Customer War Peace 8 99 parent Borrower cycles of links are allowed 1 n link children 38 Elm Region Region Order Order Toronto Borrowing Jan 28 1994 Ordered Ordered Part Part Feb 24 1994 2004 John Mylopoulos Database Design 7 Information Systems Analysis and Design CSC340 Part Part Sales Sales History History 2004 John Mylopoulos Database Design 8 Information Systems Analysis and Design CSC340 The Relational Data Model Comparing Data Models A database consists of sets of records or equivalently sets of tuples relations or equivalently tables no links allowed in the database Every tuple is an element of exactly one relation and is identified uniquely by a primary key Ordered Part Part The oldest DBMSs were hierarchical dating back to the mid60s 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 too implementation oriented machine dependent The relational model is the most recent arrival early 80s and it has taken over the database market Relational databases are considered simpler than their hierarchical and network cousins because they don t allow any links pointers which are necessarily machine dependent Customer Customer Cust Name Cust 1127 1377 1532 George Maria Manolis Order Ord Ord 1997 4237 25 Mars St 1552 12 Low Ave 1 Bloor St Address Date Amount 11 3 93 25 8 93 12 12 93 65 87 126 88 284 21 Part Part 2397 2908 6590 Part 2397 2908 6590 Desc Desc Quantity widget widget nut nut bolt 12 980 16 000doz 16 000doz 14 340doz 14 340doz 2004 John Mylopoulos Ord Ord Quantity 1997 1997 4237 980 100doz 100doz 40doz 40doz Database Design 9 Information Systems Analysis and Design CSC340 Designing a Database Schema Relational 1 N Part orders 1 N Customer 1 1 Date supplies Part Name Description Part Supplier Name Addr Customer Name Addr Supplies Name Part Date Orders Name Part 1 N Supplier Network Hierarchical Part Part Customer name Supplier Customer 2004 John Mylopoulos Database Design 10 Information Systems Analysis and Design CSC340 Relational Database Design Given a class or Entity Relationship diagram or schema produced during requirements analysis generate a logical relational schema This is not just a simple translation from one model to another for two main reasons not all the constructs of the Entity Relationship model can be translated naturally into the relational model the schema must be restructured in such a way as to make the execution of the projected operations as efficient as possible Supplier 2004 John Mylopoulos Database Design 11 2004 John Mylopoulos Database Design 12 Information Systems Analysis and Design CSC340 Database Design Process 2004 John Mylopoulos CSC340 Logical Design Steps It is helpful to divide the
View Full Document
Unlocking...