Information Systems Analysis and Design CSC340 XX Database Design Databases and DBMS Data Models Hierarchical Network Network Relational Relational Database Design Restructuring an ER schema Performance analysis Analysis of Redundancies Redundancies Removing Removing generalizations generalizations Translation into a Relational Relational Schema Schema The Training Company Example Normal Forms and Normalization Normalization of of Relational Relational Schemas Schemas 2003 John Mylopoulos Database Design 1 Information Systems Analysis and Design CSC340 Databases A database is a collection of persistent data shared by a number of applications Databases have been founded on the concept of data independenc independencee Applications should not have to know the organization of the data or the access strategy 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 2003 John Mylopoulos Database Design 2 Information Systems Analysis and Design CSC340 Conventional Files vs Databases 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 with databases 2003 John Mylopoulos Database Design 3 Information Systems Analysis and Design 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 2003 John Mylopoulos Database Design 4 Information Systems Analysis and Design CSC340 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 5 Information Systems Analysis and Design CSC340 Database Management Systems 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 The Hierarchical 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 Book B365 War Peace 8 99 parent Borrower children 38 Elm Toronto Borrowing Jan 28 1994 Feb 24 1994 2003 John Mylopoulos Database Design 7 Information Systems Analysis and Design CSC340 The Network Data Model A database now consists of records with pointers links to other records Offers a navigational view of a database Customer Customer 1 n link cycles of links are allowed Order Order Ordered Ordered Part Part Part Part Sales Sales History History Region Region 2003 John Mylopoulos Database Design 8 Information Systems Analysis and Design CSC340 The Relational Data Model 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 by a primary key 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 Amount 11 3 93 25 8 93 12 12 93 65 87 126 88 284 21 Part Part 2397 2908 6590 Ordered Part Part Ord Ord Quantity Quantity Date 2397 2908 6590 Desc Quantity Quantity widget nut bolt 12 980 16 000doz 14 340doz 1997 1997 4237 2003 John Mylopoulos 980 100doz 40doz Database Design 9 Information Systems Analysis and Design CSC340 Comparing Data Models 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 navigator 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 they don t allow any links pointers which are necessarily implementationdependent 2003 John Mylopoulos Database Design 10 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 Supplier 2003 John Mylopoulos Database Design 11 Information Systems Analysis and Design CSC340 Relational Database Design The aim of
View Full Document
Unlocking...