Database Systems Design Implementation and Management THIRD EDITION

Database Systems: Design, Implementation, and Management THIRD EDITION CHAPTER 1 File Systems and Databases 1

Chapter Objectives 4 4 4 Understand characteristics of a file system Understand advantages of using a database Understand characteristics of database systems Understand basic database concepts Understand three different types of database models 2

Chapter Overview 1. 1 Introducing the Database 1. 2 The Historical Roots of the Database: Files and File Systems 1. 3 A File System Critique 1. 4 Database Systems 1. 5 Database Models 3

Introducing the Database l Major Database Concepts u Data management u Database u Metadata u Database management system (DBMS) 4

Introducing the Database l Importance of DBMS u It helps make data management more efficient and effective. u Its query language allows quick answers to ad hoc queries. u It provides end users better access to more and better -managed data. u It promotes an integrated view of organization� operations -- �ig picture? u It reduces the probability of inconsistent data. 5

Introducing the Database Figure 1. 1 The DBMS Manages the Interaction between the End User and the Database 6

Introducing the Database l Why Database Design Is Important? u. A poorly designed database is a breeding ground for uncontrolled data redundancies. u A poorly designed database generates errors that lead to bad decisions. l A Practical Approach to Database Design u Focus on principles and concepts of practical database design u Use of two complete applications through the logical design stage 7

Historical Roots l Why Studying File Systems? u It provides historical perspective. u It teaches lessons to avoid pitfalls of data management. u Its simple characteristics facilitate understanding of the design complexity of a database. u It provides useful knowledge for converting a file system to a database system. 8

Historical Roots Table 1. 1 Basic File Terminology 9

Historical Roots l Reports Prepared by DP Specialist Using the CUSTOMER file: u Monthly summaries of types and amounts of insurance sold by each agent u Monthly checks to determine which customers must be contacted for renewal u Analysis reports on ratios of insurance types sold by each agent u Customer contact letters with coverage summary and customer relation bonuses 10

Historical Roots Figure 1. 2 A Simple File System 11

File System Critique l File System Data Management u File systems require extensive programming in 3 GL. u As file systems become more complex, managing files gets more difficult. u Making changes in existing file structures is important and difficult. u Data access programs are subject to change with file structure changes (structural dependence). u Security features are difficult to implement and are lacking. 12

File System Critique l Structural and Data Dependence u Structural Dependence A change in any file�structure requires the modification of all programs using that file. u Data Dependence A change in any file�data characteristics requires changes of all data access programs. u Data dependence makes file systems extremely cumbersome from a programming and data management point of view. 13

File System Critique l Field Definitions and Naming Conventions u. A good (flexible) record definition anticipates reporting requirements by breaking up fields into their components. l Example: v v Customer Name Last Name, First Name, Initial Customer Address Street Address, City, State u Selecting l l proper field names is very important. Descriptive Self-documenting 14

File System Critique l Data Redundancy: Uncontrolled data redundancy sets the stage for u Data Inconsistency (lack of data integrity) u Data anomalies l l l Modification anomalies Insertion anomalies Deletion anomalies 15

Database Systems l l l In a database system, logically related data are stored in a single data repository. The database represents a change in the way end user data are stored, accessed, and managed. DBMS makes it easier to eliminate most of the file system�data inconsistency, data anomalies, and data structural dependency problems. Current DBMS stores not only the data structure, but also the relationships. DBMS takes care of defining all the required access paths. 16

Database Systems Figure 1. 3 Contrasting Database and File System Designs 17

Database Systems Figure 1. 4 The Database System Environment 18

Database Systems l The Database System Components u Hardware l l Computer Peripherals u Software l l l Operating systems software DBMS software Applications programs and utilities software 19

Database Systems l The Database System Components u People l l l Systems administrators Database designers Systems analysts and programmers End users u Procedures l Instructions and rules that govern the design and use of the database system u Data l Collection of facts stored in the database 20

Database Systems l Database Systems and Organizational Factors u The complexity of database systems depends on various organizational factors: l l Organization�size Organization�function Organization�corporate culture Organizational activities and environment u Database solutions must be cost effective and strategically effective. 21

Database Systems l Types of Database Systems u Number l l of Users Single-user Multi-user u Scope l l l Desktop Workgroup Enterprise 22

Database Systems l Types of Database Systems u Location l l Centralized Distributed u Use l l l Transactional (Production) Decision support Data warehouse 23

Database Systems l DBMS Functions u Data l l Data dictionary stores definitions of the data elements and their relationships (metadata). It provides data abstraction and removes structural and data dependency from the system. u Data l Dictionary Management Storage Management DBMS creates data storage structure and relieves us from the task of defining and programming physical data characteristics. 24

Database Systems l DBMS Functions u Data l Transformation and Management DBMS relieves us from the chore of making distinction between logical format and physical format of data. u Security l l Management DBMS provides user security and data privacy within the database. Data security is especially important in multi-user database. 25

Database Systems l DBMS Functions u Multi-User l DBMS ensures that multiple users can access the database concurrently and still guarantees the integrity of the database. u Backup l l and Recovery Management DBMS provides backup and recovery procedures to ensure data safety and integrity. u Data l Access Control Integrity Management DBMS promotes and enforces integrity rules to eliminate data integrity problems. Ensuring data integrity is especially important in transaction-oriented database systems. 26

Database Systems l Managing the Database System: Shift in Focus u The role of DP specialists or manager changes from a programming emphasis to focus on the broader management aspects of the organization�data resource and on the administration of complex database software. u Shift in DP Personnel l DP Manager Systems Administrator Database Administrator 27

Database Models l l A database model is a collection of logical constructs used to represent the data structure and the data relationships found within the database. Two Categories of Database Models u Conceptual models focus on the logical nature of the data representation. They are concerned with what is represented rather than how it is represented. u Implementation models place the emphasis on how the data are represented in the database or on how the data structures are implemented. 28

Database Models l Three Types of Relationships in Conceptual Database Models u One-to-many relationships 29

Database Models l Three Types of Relationships in Conceptual Database Models u Many-to-many relationships 30

Database Models l Three Types of Relationships in Conceptual Database Models u One-to-one relationships 31

Database Models l Three Types of Implementation Database Models u Hierarchical database model u Network database model u Relational database model 32

Database Models l Hierarchical Database Model u Background l l l GUAM (Generalized Update Access Method) was developed by North American Rockwell. It conformed to upside-down tree structure -- hierarchical structure. Information Management System (IMS) -- Jointly developed by IBM and Rockwell. Hierarchical database model concepts for the basis for subsequent database development. Its limitations lead to a different way of looking at database design. 33

Database Models Figure 1. 5 A Hierarchical Structure 34

Database Models l Hierarchical Database Model u Basic l l l Structure Collection of records perceived as organized to conform to the upside-down tree structure. A tree structure is represented as a hierarchical path on the computer�storage media. One-to-Many (1: M) Relationship v v Each parent can have many children. Each child has only one parent. 35

Database Models Figure 1. 6 The Basic Components of a Hierarchical Structure 36

Database Models l Hierarchical Database Model u Advantages l l l Data sharing and security provision Data independence -- Reduced programming and maintenance effort Database integrity Efficiency dealing with a large database Large installed (mainframe) base Abundant business applications 37

Database Models l Hierarchical Database Model u Disadvantages l l l l Requirement of knowledge of physical level of data storage Inability to represent relationships that do not conform to the hierarchical 1: M standard Complex and inflexible to manage Time consuming and complicated application programming Lack of ad hoc query capability for end users Lack of standard concepts and implementation -- limited portability Requirement of extensive programming activities 38

Database Models l Network Database Model u Background l l CODASYL (Conference on Data Systems Language) group created Data. Base Task Group (DBTG) in 1971. DBTG specified three crucial database components: v v v Network schema defines the conceptual organization of the entire database as viewed by the database administrator. Subschema defines the portion of the database as seen by the applications programs. Data Management Language defines the data characteristics and the data structure and to manipulate the data. 39

Database Models l Network Database Model u Background l Three DBTG data management language components: v v v l Schema Data Definition Language (DDL) Subschema Data Definition Language Data Manipulation Language ANSI SPARC (Standards Planning And Requirements Committee) augmented the database standards in 1975. 40

Database Models l Network Database Model u Basic l Structure Set -- A relationship is called a set. Each set is composed of at least two record types: an owner (parent) record and a member (child) record. Figure 1. 7 A Set 41

Database Models Figure 1. 8 A Network Model 42

Database Models l Network Database Model l Relationships among the records are decomposed into a series of sets. Figure 1. 9 Defining Set Components 43

Database Models l Network Database Model u Advantages l l Easier implementation of M: N relationships Superior data access type and flexibility Enforced data integrity Sufficient data independence 44

Database Models l Network Database Model u Disadvantages l l Difficult to design and use properly Difficult to make changes in a database Very complex structure from the application programmer� point of view Complex navigational data access environment 45

Database Models l Relational Database Model u Background l E. F. Codd developed the relational model in 1970. v v v l Conceptually simple but versatile Major breakthrough for both users and designers From �tandard transmission? to �utomatic transmission Requires more computing power v v Considered impractical in the 1970� Modern computers (even PCs) are powerful enough to handle relational databases. 46

Database Models l Relational Database Model u Basic l Structure Relational Data. Base Management System (RDBMS) v v v RDBMS allows user/designer operate in a human logical environment. Relational database is perceived by the user as a collection of tables in which data are stored. Each table consists of series of row/column intersections. Tables (or relations) are related to each other by sharing a common entity characteristic. The relationship type is often shown in a relational schema. A table yields complete data and structural independence because it is a purely logical structure. 47

Database Models l Relational Database Model Figure 1. 10 A Relational Schema 48

Database Models l Relational Database Model u Advantages l l Data independence and structural independence Easy to design the database and to manage its contents Less programming effort required Powerful and flexible query capability: v Structured Query Language (SQL) Y Fourth Generation Language (4 GL) Y Specify �hat to do? not �ow to do Y Introduced by IBM in 1974 Y 3 parts: (1) User interface, (2) Set of tables (3) SQL engine 49

Database Models l Relational Database Model u Disadvantages l l RDBMS requires substantial hardware and operating system overhead. It tends to be slower than other database systems. 50