Information Systems Analysis and Design CSC340 XX Database Design Databases and DBMS Data Models Hierarchical Network Relational Database Design Restructuring an ER schema Performance analysis Analysis of Redundancies Removing generalizations generalizations Translation into a Relational Schem Normal Forms and Normalization 2004 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 independence Applications should not have to know the organization of the data or the access strategy employed to fetch the data Query facility query optimization 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 transactions 2004 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 2004 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 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 2004 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 conditionaction rules more 2004 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 2004 John Mylopoulos Host Programming Language DBMS DML DDL 4GL Database 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 2004 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 cycles of links are allowed 1 n link Region Region Order Order Ordered Ordered Part Part 2004 John Mylopoulos Part Part Sales Sales History History Database Design 8 Information Systems Analysis and Design CSC340 The Relational Data Model 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 Customer Cust Name Cust 1127 1377 1532 George Maria Manolis Order Ord Ord Address 1997 4237 25 Mars St 1552 12 Low Ave 1 Bloor St 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 Quantity Quantity widget nut bolt 12 980 16 000doz 14 340doz 2004 John Mylopoulos Ord Ord Quantity Quantity 1997 1997 4237 980 100doz 40doz Database Design 9 Information Systems Analysis and Design CSC340 Comparing Data Models 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 2004 John Mylopoulos Database Design 10 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 Supplier 2004 John Mylopoulos Database Design 11 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 2004 John Mylopoulos Database Design 12 Information Systems Analysis and Design CSC340 Database Design Process 2004 John Mylopoulos Database Design 13 Information Systems Analysis and Design CSC340 Logical Design Steps It is helpful to divide the logical design into two steps Restructuring of the Entity Relationship schema based on criteria for the optimization of the schema and the simplification of the following step Translation into the logical model based
View Full Document
Unlocking...