Information Systems Analysis and Design CSC340 Information Systems Analysis and Design Databases XX Database Design Databases and and DBMS DBMS Data Models Hierarchical Network Relational Database Design Restructuring an ER schema schema Performance analysis Analysis of Redundancies Removing generalizations Translation into into aa Relational Relational Schema Schema The Training Training Company Company Example Example Normal Forms and Normalization Normalization of of Relational RelationalSchemas Schemas 2003 John Mylopoulos A database is a collection of persistent data shared b y a number of applications Databases ha ve been founded on the concept of data independence Applications should not have to know the organization of the data or the access strateg y employed Need query processing facility which generates automatically an access plan given a query 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 Database procedures defined in terms of atomic operations called transactions Database Design 1 Information Systems Analysis and Design CSC340 2003 John Mylopoulos CSC340 Database Concepts Data model defines a set of data structures along with associated operations for building and accessing a database e g the relational model offers relations tables as data structure for building 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 E g Employee emp name addr sal dept mngr 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 E g TransferFunds fromAcct toAcct amount date 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 2003 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 condition action rules Deductive databases like large Prolog programs Hypertext databases store and access efficiently HTML XML documents provide navigational facilities through a database so that a user can retrieve and or browse not available commercially 2003 John Mylopoulos Database Design 2 Information Systems Analysis and Design Conventional Files vs Databases 2003 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 Host Programming Language DBMS DML DDL 4GL Database 2003 John Mylopoulos 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 e very 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 Customer Customer Book B365 War Peace 8 99 1 n link parent Borrower children 38 Elm cycles of links are allowed Order Order Toronto Ordered Ordered Part Part Borrowing Jan 28 1994 Part Part Sales Sales History History Feb 24 1994 Region Region 2003 John Mylopoulos Database Design 7 Information Systems Analysis and Design CSC340 2003 John Mylopoulos Database Design 8 Information Systems Analysis and Design CSC340 The Relational Data Model Comparing Data Models A database now 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 b y a primary key The oldest DBMSs were hierarchical dating back to the mid 60s IMS IBM product is the most popular among them Many old databases are hierarchical The network data model came next early 70s At the time of its proposal it was viewed as a breakthrough It emphasized the role of the database programmer as na vigator chasing links pointers actually around a database But the network model was found to be in many respects too implementation oriented not insulating sufficiently the programmer from implementation features of network DBMSs 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 the y don t allow any links pointers which are necessarily implementationdependent Order Ord Ord Customer Customer Cust Name Cust 1127 1377 1532 Amount 11 3 93 25 8 93 12 12 93 65 87 126 88 284 21 1997 25 Mars St 4237 12 Low Ave 1552 1 Bloor St Address George Maria Manolis Ordered Part Part Part Ord Ord Quantity Date Part Part 2397 2908 6590 2397 2908 6590 Desc Desc Quantity widget widget nut nut bolt 12 980 16 000doz 16 000doz 14 340doz 14 340doz 2003 John Mylopoulos 1997 1997 4237 980 100doz 100doz 40doz 40doz Database Design 9 Information Systems Analysis and Design CSC340 Designing a Database Schema Relational 1 Part orders 1 Customer 1 1 Date supplies Part Name Description Part Supplier Name Addr Customer Name Addr Supplies Name Part Date Orders Name Part 1 Supplier Network Hierarchical Part Part Customer name Supplier Customer 2003 John Mylopoulos Database Design 10 Information Systems Analysis and Design CSC340 Relational Database Design The aim of
View Full Document
Unlocking...