Fundamentals of Database Systems PDF

Chapter 1 Databases and Database Users Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Overview  Traditional database applications  Store textual or numeric information  Multimedia databases  Store images, audio c...

Chapter 1 Databases and Database Users Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Overview  Traditional database applications  Store textual or numeric information  Multimedia databases  Store images, audio clips, and video streams digitally  Geographic information systems (GIS)  Store and analyze maps, weather data, and satellite images Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Overview (cont'd.)  Data warehouses and online analytical processing (OLAP) systems  Extract and analyze useful business information from very large databases  Support decision making  Real-time and active database technology  Control industrial and manufacturing processes Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Introduction  Database  Collection of related data  Known facts that can be recorded and that have implicit meaning  Miniworld or universe of discourse (UoD)  Represents some aspect of the real world  Logically coherent collection of data with inherent meaning  Built for a specific purpose Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Introduction (cont'd.)  Example of a large commercial database  Amazon.com  Database management system (DBMS)  Collection of programs Enables users to create and maintain a database  Database System:  The DBMS software together with the data itself. Sometimes, the applications are also included.  Defining a database  Specify the data types, structures, and constraints of the data to be stored Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Introduction (cont'd.)  Meta-data  Database definition or descriptive information  Stored by the DBMS in the form of a database catalog or dictionary  Manipulating a database  Retrieval: Querying, generating reports  Modification: Insertions, deletions and updates to its content  Accessing the database through Web applications  Generate reports Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Introduction (cont'd.)  Sharing a database  Allow multiple users and programs to access the database simultaneously  Application program  Accesses database by sending queries to DBMS  Query  Causes some data to be retrieved Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Introduction (cont'd.)  Transaction  May cause some data to be read and some data to be written into the database  Protection includes:  System protection  Security protection  Maintain the database system  Allow the system to evolve as requirements change over time Copyright © 2011 Ramez Elmasri and Shamkant Navathe  An Example  UNIVERSITY database  Information concerning students, courses, and grades in a university environment  Data records  STUDENT  COURSE  SECTION  GRADE_REPORT  PREREQUISITE Copyright © 2011 Ramez Elmasri and Shamkant Navathe  An Example (cont'd.)  Specify structure of records of each file by specifying data type for each data element  String of alphabetic characters  Integer  Etc. Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  An Example (cont'd.)  Construct UNIVERSITY database  Store data to represent each student, course, section, grade report, and prerequisite as a record in appropriate file  Relationships among the records  Manipulation involves querying and updating Copyright © 2011 Ramez Elmasri and Shamkant Navathe  An Example (cont'd.)  Examples of queries:  Retrieve the transcript  List the names of students who took the section of the ‘Database’ course offered in fall 2008 and their grades in that section  List the prerequisites of the ‘Database’ course Copyright © 2011 Ramez Elmasri and Shamkant Navathe  An Example (cont'd.)  Examples of updates:  Change the class of ‘Smith’ to sophomore  Create a new section for the ‘Database’ course for this semester  Enter a grade of ‘A’ for ‘Smith’ in the ‘Database’ section of last semester Copyright © 2011 Ramez Elmasri and Shamkant Navathe  An Example (cont'd.)  Phases for designing a database:  Requirements specification and analysis  Conceptual design(that can be represented and manipulated using some computerized tools so that it can be easily maintained, modified, and transformed into a database)  Logical design(represent data model)  Physical design(during which further specifications are provided for storing and accessing the database) Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of the Database Approach  Traditional file processing  Each user defines and implements the files needed for a specific software application  Database approach  Single repository maintains data that is defined once and then accessed by various users Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Characteristics of the Database Approach (cont'd.)  Main characteristics of database approach  Self-describing nature of a database system  Insulation between programs and data, and data abstraction  Support of multiple views of the data  Sharing of data and multiuser transaction processing Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Self-Describing Nature of a Database System  Database system contains complete definition of structure and constraints  A DBMS catalog stores the description of a particular database (e.g. data structures, types, and constraints)  The description is called meta-data.  This allows the DBMS software to work with different database applications.  Meta-data  Describes structure of the database  Database catalog used by:  DBMS software  Database users who need information about database structure Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Insulation Between Programs and Data  Called program-data independence.  Allows changing data structures and storage organization without having to change the DBMS access programs.  Program-operation independence  Operations specified in two parts: Interface includes operation name and data types of its arguments Implementation can be changed without affecting the interface Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Data Abstraction  Allows program-data independence and program-operation independence  A data model is used to hide storage details and present the users with a conceptual view of the database.  Programs refer to the data model constructs rather than data storage details  Conceptual representation of data  Does not include details of how data is stored or how operations are implemented  Data model  Type of data abstraction used to provide conceptual representation Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Support of Multiple Views of the Data  Each user may see a different view of the database, which describes only the data of interest to that user.  View  Subset of the database  Contains virtual data derived from the database files but is not explicitly stored  Multiuser DBMS  Users have a variety of distinct applications  Must provide facilities for defining multiple views Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Sharing of Data and Multiuser Transaction Processing  Allow multiple users to access the database at the same time  Concurrency control software  Ensure that several users trying to update the same data do so in a controlled manner Result of the updates is correct  OLTP (Online Transaction Processing) is a major part of database applications. This allows hundreds of concurrent transactions to execute per second. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Sharing of Data and Multiuser Transaction Processing (cont'd.)  Transaction  Central to many database applications  Executing program or process that includes one or more database  Isolation property Each transaction appears to execute in isolation from other transactions  Atomicity property Either all the database operations in a transaction are executed or none are Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Database Users  Users may be divided into  Those who actually use and control the database content, and those who design, develop and maintain database applications (called “Actors on the Scene”), and  Those who design and develop the DBMS software and related tools, and the computer systems operators (called “Workers Behind the Scene”). Slide 1- 26 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Actors on the Scene  Database administrators (DBA) are responsible for:  Authorizing access to the database  Coordinating and monitoring its use  Acquiring software and hardware resources  Database designers are responsible for:  Identifying the data to be stored  Choosing appropriate structures to represent and store this data Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Categories of End-users  Actors on the scene (continued)  End-users: They use the data for queries, reports and some of them update the database content. End-users can be categorized into: Casual: access database occasionally when needed Naïve or Parametric: they make up a large section of the end-user population. They use previously well-defined functions in the form of “canned transactions” against the database. Examples are bank-tellers or reservation clerks who do this activity for an entire shift of operations. Slide 1- 28 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Categories of End-users (continued) Sophisticated: These include business analysts, scientists, engineers, others thoroughly familiar with the system capabilities. Many use tools in the form of software packages that work closely with the stored database. Stand-alone: Mostly maintain personal databases using ready-to-use packaged applications. An example is a tax program user that creates its own internal database. Another example is a user that maintains an address book Slide 1- 29 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Actors on the Scene (cont'd.)  System analysts  Determine requirements of end users  Application programmers  Implement these specifications as programs Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Workers behind the Scene  DBMS system designers and implementers  Design and implement the DBMS modules and interfaces as a software package  Tool developers  Design and implement tools  Operators and maintenance personnel  Responsible for running and maintenance of hardware and software environment for database system Copyright © 2011 Ramez Elmasri and Shamkant Navathe Advantages of Using the DBMS Approach  Controlling redundancy  Data normalization  Denormalization Sometimes necessary to use controlled redundancy to improve the performance of queries  Restricting unauthorized access  Security and authorization subsystem  Privileged software Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Advantages of Using the DBMS Approach (cont'd.)  Providing storage structures and search techniques for efficient query processing  Indexes  Buffering and caching  Query processing and optimization Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Advantages of Using the DBMS Approach (cont'd.)  Providing backup and recovery  Backup and recovery subsystem of the DBMS is responsible for recovery  Providing multiple user interfaces  Graphical user interfaces (GUIs)  Representing complex relationships among data  May include numerous varieties of data that are interrelated in many ways Copyright © 2011 Ramez Elmasri and Shamkant Navathe Advantages of Using the DBMS Approach (cont'd.)  Enforcing integrity constraints  Referential integrity constraint Every section record must be related to a course record  Key or uniqueness constraint Every course record must have a unique value for Course number Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Additional Implications of Using the Database Approach  Potential for enforcing standards:  This is very crucial for the success of database applications in large organizations. Standards refer to data item names, display formats, screens, report structures, meta-data (description of data), Web page layouts, etc.  Reduced application development time:  Incremental time to add each new application is reduced. Slide 1- 36 Copyright © 2011 Ramez Elmasri and Shamkant Navathe Additional Implications of Using the Database Approach (continued)  Flexibility to change data structures:  Database structure may evolve as new requirements are defined.  Availability of current information:  Extremely important for on-line transaction systems such as airline, hotel, car reservations.  Economies of scale:  Wasteful overlap of resources and personnel can be avoided by consolidating data and applications across departments. Slide 1- 37 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  When Not to Use a DBMS  More desirable to use regular files for:  Simple, well-defined database applications not expected to change at all  Stringent, real-time requirements that may not be met because of DBMS overhead  Embedded systems with limited storage capacity  No multiple-user access to data Copyright © 2011 Ramez Elmasri and Shamkant Navathe