Database Systems: Design, Implementation, and Management

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

2. Chapter Objectives • What a database is, what it does, and why database design is important • How modern databases evolved from files and file systems • Data management problems associated with a file system • Advantages of using a database over a file system • Functions of a database management system • Five different types of database models

3. Introducing the Database • Data versus Information • Data constitute building blocks of information • Information produced by processing data • Information reveals meaning of data • Good, timely, relevant information key to decision making • Good decision making key to organizational survival

4. Database Management • Database is shared, integrated computer structure housing: • End user data • Metadata • Database Management System (DBMS) • Manages Database structure • Controls access to data • Contains query language

5. Importance of DBMS • Makes data management more efficient and effective • Query language allows quick answers to ad hoc queries • Provides better access to more and better-managed data • Promotes integrated view of organization’s operations • Reduces the probability of inconsistent data

6. DBMS Manages Interaction

7. Historical Roots Table 1.1 Basic File Terminology

8. Historical Roots Figure 1.5

9. File System Critique • File System Data Management • File systems require extensive programming in 3GL. • As file systems become more complex, managing files gets more difficult. • Security features are difficult to implement and are lacking. • Changing requirements mandate changes in existing file structures. • Programs accessing a file are subject to change when the file structure changes - More maintenance.

10. File System Critique • Structural and Data Dependence • Structural Dependence A change in any file’s structure requires the modification of all programs using that file. Example:addition or deletion of a field • Data Dependence A change in any file’s data characteristics requires changes of all data access programs. • Data logical format (how human beings view) • Data physical format (how computer sees) Example: integer to decimal, • Structural and data dependence make file systems very difficult to manage - High Maintenance.

11. File System Critique • Field Definitions and Naming Conventions: break up to last name, firstname etc.; area code and phone#. • Data Redundancy:The same information is stored in more than one place. Example: • Problems due to uncontrolled data redundancy • Data Inconsistency (lack of data integrity) • Example: • Data anomalies • Modification anomalies (Inconsistent data due to modifications). Example: • Insertion anomalies (Inconsistent data due to insertions) Example: • Deletion anomalies (Inconsistent data due to deletions) Example:

12. Database Systems • The database represents a change in the way end user data are stored, accessed, and managed. • Logically related data are stored in a single data repository. • DBMS makes it easier to eliminate most of the file system’s data inconsistency, data anomalies, and data structural dependency problems. • DBMS stores not only the data structure, but also the relationships. • Note the difference between a database and a DBMS

13. File System vs Database Systems Figure 1.6

14. Database Systems Figure 1.7

15. Database Systems • The Database System Components • Hardware • Computer • Peripherals • Software • Operating systems software • DBMS software • Applications programs and utilities software

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

17. Database Systems • Types of Database Systems • Number of Users • Single-user • Multi-user • Scope • Desktop (Single user) • Workgroup (Used by a department, nbr of users < 50) • Enterprise (Used by the entire organization, users in 100s)

18. Database Systems • Types of Database Systems • Location • Centralized (Database located in a single site) • Distributed (Database distributed across multiple sites) • Use • Transactional (aka Production) • Data integrity, consistency and operational efficiency are important • Decision support or Data warehouse (for strategic or tactical decision) • Historical, aggregated data • Used for decision support (for forecast, pricing,market positioning, etc. long term issues. Extenssive data manipulation)

19. Database Systems • DBMS Functions • Data Dictionary Management • Data dictionary stores definitions of the data elements and their relationships (metadata). • It removes structural and data dependency from the system. • Data Storage Management • DBMS creates data storage structure and relieves the programmer from the task of defining and programming physical data characteristics. • Data Transformation and Management • DBMS transforms data from its logical format to its physical format and vice versa. • Security Management • DBMS provides user security and data privacy within the database. • Data security is especially important in multi-user database.

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

21. Database Systems • DBMS Functions • Database Access Languages & Application Programming Interfaces • The DBMS’s non procedural query language simplifies data access • Data Definition Language (DDL) is used to define the database structure • Data Manipulation Language (DML) is used for accessing and manipulating data • DBMS also provides interfaces to application programs written in procedural languages such as COBOL, C etc. • Database Communication Interfaces • DBMS provides communication interfaces so that the database can be accessed through a network (internet).

22. Database Models • 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 • Conceptual models focus on the logical nature of the data representation. They are concerned with what is represented rather than how it is represented. (E-R model, OO) • Implementation models place the emphasis on how the data are represented in the database or on how the data structures are implemented. (Hierarchical, Network, relational, OO)

23. Database Models • Three Types of Relationships in Conceptual Database Models • One-to-many relationships • Example: • Many-to-many relationships • Example: • One-to-one relationships • Example:

24. Database Models • Types of Implementation Database Models • Hierarchical • Network • Relational • Object Oriented

25. Database Models • Hierarchical Database Model • Background • GUAM (Generalized Update Access Method) was developed by North American Rockwell to simplify parts inventory management for the Apollo project. • Information Management System (IMS) -- Jointly developed by IBM and Rockwell. • Data records are organized in an inverted (upside-down) tree structure -- hierarchical structure.

26. Database Models Figure 1.8

27. Database Models • Hierarchical Database Model • Basic Structure • Collection of records (segments) organized to conform to the upside-down tree structure. • One-to-Many (1:M) Relationship • Each parent can have many children. • Each child has only one parent. • Example: • A tree structure is represented as a hierarchical path on the computer’s storage media. • Preorder (Top down) traversal-more popular • Postorder (Bottom up) traversal: • With preorder traversal frequently accessed segments are placed close to the left side of the tree to minimize access time.

28. Database Models • Hierarchical Database Model • Advantages • Conceptual simplicity • Data sharing and security provision • Data independence -- Reduced programming and maintenance effort • Database integrity- a child must have a parent • Efficiency dealing with a large database containing data with predominantly 1:M relationships - many business applications fall in this category

29. Database Models • Hierarchical Database Model • Disadvantages • Requirement of knowledge of physical level of data storage • Inability to represent relationships that do not conform to the hierarchical 1:M standard • Applications programming is complex - programmer must know the database structure and how to traverse it • Lack of ad hoc query capability for end users • Difficult to manage and Lack of standards • Complex implementation (No standard was developed)-- limitedportability • Requires extensive programming activities to use the database - Most serious problem • Lacks structural independence

30. Database Models • Network Database Model • Background • CODASYL (Conference on Data Systems Language) group created DataBase Task Group (DBTG) in 1971 to develop standards for a database environment. • DBTG specified three crucial database components: • Network schemadefines the conceptual organization of the entire database as viewed by the database administrator. • Subschemadefines 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.

31. Database Models • Network Database Model • Background • Three DBTG data management language components: • Schema Data Definition Language (DDL) - used by DBA to define schema components • Subschema Data Definition Language - used by application programs to define part of the schema to be used by the program • Data Manipulation Language - allows manipulation of the database contents • ANSI SPARC(Standards Planning And Requirements Committee) augmented the database standards in 1975. • A popular network DBMS is Computer Associates’ IDMS/R

32. Database Models • Network Database Model • Basic 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. A Member may have several owners.

33. Database Models • Network Database Model • Relationships among the records are decomposed into a series of sets. Figure 1.10

34. Database Models • Network Database Model • Advantages • Conceptual simplicity • Handles more relationship types. Easier implementation of M:N relationships • More flexible data access compared to Hierarchical model - An application can access an owner and all the members within the set, one can also access owner from a member. • Enforced data integrity - owner record must be defined before defining member records • Data independence through the DBMS • Disadvantages • Very complex structure from the application programmer’s point of view • Difficult to design and use properly - because of the navigational nature of the data structure • Difficult to make changes in a database - no structural independence

35. Database Models • Relational Database Model • Background • E. F. Codd developed the relational model in 1970. • Conceptually simple but versatile • Major breakthrough for both users and designers • Requires more computing power • Considered impractical in the 1970’s • Modern computers (even PCs) are powerful enough to handle relational databases.

36. Database Models • Relational Database Model • Basic Structure • Relational Data Base Management System (RDBMS) • 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.

37. Database Models • Relational Database Model

38. Database Models

39. Database Models • Relational Database Model • Advantages • Data independence and structural independence • Improved conceptual simplicity. Easy to design, implement and manage (powerful DBMS). • Less programming effort required • Powerful and flexible query capability (Ad hoc query capability): • Structured Query Language (SQL) • Fourth Generation Language (4GL) • Specify “what to do” not “how to do” • Introduced by IBM in 1974 • Disadvantages • RDBMS requires substantial hardware and operating system overhead. • It tends to be slower than other database systems. • Poor design and implementation is made easy.

40. Entity Relationship Database Model • Complements the relational data model concepts • Represented in an entity relationship diagram (ERD) • Based on entities, attributes, and relationships

41. Entity Relationship Database Model • Advantages • Exceptional conceptual simplicity • Visual representation • Effective communication tool • Integrated with the relational database model • Disadvantages • Limited constraint representation • Limited relationship representation • No data manipulation language • Loss of information content

42. Database Models • OO Database Model • Objects are abstractions of real-world entities • Example: Student, Invoice, Employee • Objects capture both attribute and behavior (methods) • Objects with similar characteristics are grouped together as a class • Classes are organized in a class hierarchy • A subclass inherits the attributes and methods from its superclass

43. Database Models • OO Database Model • Advantages • Provides a richer modeling language - captures attributes, methods, and class hierarchies • Useful in niche application areas such as CAD/CAM • Database integrity • Both structural and data independence • Disadvantage • Data access method resembles the navigational style • Lack of standardization • Lack of OODM • Steep learning curve • High system overhead slows transactions

44. Review • File system • Basic File Terminology • Data Management • Structural Dependence • Data Dependence • Data Redundancy • Data Inconsistency • Data anomalies • Database Systems • stores not only the data structure, but also the relationships. • difference between a database and a DBMS • Structure: Hardware- endusers. • Components:people,procedures,data • Types:no of users, scope,location, use.

45. Review DBMS Functions Data Dictionary Management Data Storage Management Data Transformation and Management Security Management Multi-User Access Control Backup and Recovery Management Data Integrity Management Database Access Languages & Application Programming Interfaces Database Communication Interfaces Database models Conceptual models Implementation models Three Types of Relationships in Conceptual Database Models Types of Implementation Database Models Hierarchical, Network, Relational,OO. Structure, Advantages, Disadvantages.