Database Management Systems PDF

# Database Management Systems Introduction ## Outlines - Definitions - Database Management System - Definition and Functionalities - Properties of Database - A Simplified Database System Environment - Example of a University Database ## Definitions - **Data**: Raw, unprocessed facts - Ex: 25...

# Database Management Systems Introduction ## Outlines - Definitions - Database Management System - Definition and Functionalities - Properties of Database - A Simplified Database System Environment - Example of a University Database ## Definitions - **Data**: Raw, unprocessed facts - Ex: 25, Suresh, Bangalore - **Information**: Processed data - Ex: The age of Suresh is 25. - **Database**: Collection of related data - Ex: Online banking system, library management system - **Meta-data**: The database definition ## Database Management System - **Definition**: Collection of programs that enables users to create and maintain the database. - **Functionalities**: - **Define**: Specifying the data type, structures and constraints for the data to be stored. - **Construct**: Process of storing data on some storage medium. - **Manipulate**: Querying the database to retrieve specific data, updating database and generating reports. - **Share**: Allows multiple users and programs to access the database concurrently. ## Properties of Database 1. A database represents some aspects of the real world. 2. A database is a logically coherent collection of data with some inherent meaning. 3. A database is designed, built and populated with data for a specific purpose. ## DB System Environment An image of a database system environment is shown. It depicts the relationships between the following: - **Users/Programmers** - **Application Programs/Queries** - **Software to Process Queries/Programs** - **Software to Access Stored Data** - **Stored Db Definition (Meta-Data)** - **Stored Database** ## Example of a University Database The image shows two tables: **STUDENT** | Name | Roll_No | Class | Major | |---|---|---|---| | Smith | 17 | 1 | CS | | Brown | 8 | 2 | CS | **COURSE** | Course Name | Course No | Dept | |---|---|---| | Data Structures | CS1310 | CS | | Discrete Mathematics | MATH2410 | MATH | | Database | CS380 | CS | The image also shows a **GRADE_REPORT** table, which shows the grades received by students for different courses: **GRADE_REPORT** | Roll_No | Course No | Grade | |---|---|---| | 17 | MATH2410 | B | | 17 | CS1310 | A | | 8 | CS1310 | A | This database stores student and course information. # Database Management Systems Characteristics of DBMS ## FILE SYSTEM APPROACH An image of a file system approach is shown. It demonstrates how data is organized in separate files for different departments: - **Accounts Dept.** - Roll_No - Name - Fees Paid - Fees Due - **Exam Dept.** - Roll_No - Name - Course - Grades ## DBMS APPROACH An image shows a database with multiple users accessing it. ## Characteristics of DBMS Approach 1. **Self-Describing Nature of a Database System**: Database system = Database + Meta-data (DB Definition) - Stored in: DBMS catalog - Used by: DBMS Software & Database Users 2. **Insulation between Programs and Data, and Data Abstraction**: - In traditional file processing, the structure of data files is embedded in the application programs. - In database approach, the structure of data files is stored in the DBMS catalog, separate from access programs. 3. **Support of Multiple Views of the Data**: A database has many users, each of whom may require a different view of the database. - A view is a subset of the database that contains virtual data derived from the database (not explicitly stored). 4. **Sharing of Data and Multiuser Transaction Processing**: A multiuser DBMS allows multiple users to access the database at the same time. - DBMS must include concurrency control. - OLTP (Online Transaction Processing) is a major part of database application. - DBMS must enforce several transaction properties: - Isolation - Atomicity ## Self-Describing Nature of a Database System - Database system → Database + Meta-data (DB Definition) - Stored in: DBMS catalog - Used by: DBMS Software & Database Users - DBMS software must work equally well with any number of database applications. - In traditional file processing, data definition is part of application programs, and they work with only one specific DB. ## An Example of Database Catalog The image shows a table with two sections - **RELATIONS** and **COLUMNS**. **RELATIONS** | Relation_Name | No_of_Columns | |---|---| | STUDENT | 4 | | COURSE | 3 | | GRADE_REPORT | 3 | **COLUMNS** | Col_Name | Data_Type | Belongs_to_Relation | |---|---|---| | Name | Character(30) | STUDENT | | Roll_No | Integer(4) | STUDENT | | Class | Integer(1) | STUDENT | | Major | Major_type | STUDENT | | Course Name | Character(10) | COURSE | | Grade | Character(1) | GRADE_REPORT | ## Insulation between Programs and Data, and Data Abstraction - The characteristic that allows program-data independence is called data abstraction. - DBMS provides users with a conceptual representation of data. - Data model is a type of data abstraction that provides a conceptual representation. ## Support of Multiple Views of the Data - A database has many users, each of whom may require a different view of the database. - A view is a subset of the database that contains virtual data derived from the database (not explicitly stored). ## Sharing of Data and Multiuser Transaction Processing - A multiuser DBMS allows multiple users to access the database at the same time. - DBMS must include concurrency control. - OLTP (Online Transaction Processing) is a major part of database application. - DBMS must enforce several transaction properties: - Isolation - Atomicity # Database Management Systems Introduction to Data Models ## Data Models - Underlying structure of a database. - Conceptual tool - Data + Relationship + Semantics + Constraints - Design the database at physical, logical and view level. ## Categories of Data Model 1. **Relational Model**: Collection of tables. Tables represent both data and relationships. Multiple columns with unique names. Tables = Relations. - Record-based model. - Fixed-format records of several types. - Each record type defines a fixed number of fields, or attributes. - Most widely used data model. - Current DBS are based on the relational model. 2. **Entity-Relationship Model**: E-R data model or E-R model. Collection of basic objects called entities and its relationship. - An entity is a real-world thing or object. - Distinguishable from other objects - Widely used in database design. 3. **Object-Based Model**: - OOP Languages - C++, Java, C# etc - Need for Object-oriented data model. - Object-Based Model = E-R Model + OO features. - Object-Relational Data Model = Object-Based Model + Relational Model. 4. **Semistructured Data Model**: - Specification of data. - Individual data items of the same type may have different sets of attributes. - Contrast to previous data models. - Extensible Markup Language (XML). ## Relational Model - An image shows a relational database management system that includes tables, fields, and records. Also, the image shows a set of entities, relationships, and attributes to demonstrate the modeling of data in the relational model. - Table = Relation - Each row is a record, each column is a field/attribute in each record ## Entity-Relationship Model A diagram shows a set of entities, including: - **Employee** - MName - FName - LName - Ssn - Salary - Sex - Name - **Dependent** - Name - BirthDate - Sex - Relationship The diagram also shows several relationships, including: - **WORKS_FOR** - **MANAGES** - **DEPENDENTS_OF** - **WORKS_ON** - There are also entities representing: - **Department** - **Project** ## Object-Based Model - An object-based model is shown as a diagram including a **PERSON** object and its attributes. The relationship between these objects is shown as a line. - **PERSON** - **NAME** - **AGE** - **ADDRESS** - **PHONE NUMBER** ## Semistructured Data Model - Specification of data. - Individual data items of the same type may have different sets of attributes. - Contrast to previous data models. - Extensible Markup Language (XML). ## Other Data Models - Network data model and Hierarchical data model. - Tied closely to underlying implementation. - Complicated the task of modeling the data. - Not widely used except old database. # Database Management Systems Instances and Schemas ## Schemas - The overall design of the database. - Database schema = Variable declaration. - Type definitions. - No frequent changes or not at all. - Example: ``` type Student = record Rollno : numeric (5); Name : char (25); Class : char (10); end; ``` ## Instances - The collection of information stored at a particular moment. - Frequent changes. - Database instant = Value of the variable. - An image shows a diagram of the relationship between schemas and instances. The diagram shows: - **Sub-schemas** - **Logical Schema** - **Physical Schema** # Database Management Systems Three-Schema Architecture & Data Independence ## Three-Schema Architecture - Goal: to separate the user applications and the physical database. - 3 Levels: - **Internal Level**: Describes the physical storage structure of the database. Describes complete details of data storage and access paths. - **Conceptual Level**: Hides the details of the physical storage structure and concentrates on describing entities, data types, relationships, constraints, etc. - **External Level**: Describes the part of the database that a user is interested in and hides the rest of the database from the user group. - An image shows a diagram of the three-schema architecture, including: - **External Level** - **External View** - external/conceptual mapping - **Conceptual Level** - conceptual/internal mapping - **Internal Level** ## Data Independence - **Definition**: Capacity to change the schema at one level of a database system without having to change the schema at the next higher level. - **Logical Data Independence**: Ability to modify the conceptual schema without changing the external schemas or application programs. - **Physical Data Independence**: Ability to modify the internal schema without changing the conceptual schema. Changes may be needed to improve performance. - An image shows a diagram of the relationship between different schemas and levels in a database system to illustrate how changes at one level can affect other levels. # Database Management Systems DBMS Languages, Interfaces & Classification ## DBMS Languages - In DBMS, where no strict separation of levels is maintained, **Data Definition Language (DDL)** is used to define the internal and conceptual schemas. - In DBMS, where a clear separation is maintained between conceptual and internal levels: - **DDL** is used to specify the conceptual schema only. - **Storage Definition Language (SDL)** is used to specify the internal schema. - **View Definition Language (VDL)** is used to specify user views and their mappings to the conceptual schema. - **Data Manipulation Language (DML)** is used for manipulation of data in the database. - An image shows a diagram of the three-schema architecture, including: - **External Level** - **External View** - external/conceptual mapping - **Conceptual Level** - conceptual/internal mapping - **Internal Level** ## Types of DMLs - **High-level (or non-procedural DML)**: Used to specify complex database operations in a concise manner. Also called set-at-a-time DMLs. - **Low-level (or procedural DML)**: Embedded in a general-purpose programming language. Also called record-at-a-time DMLs. ## DBMS Interfaces - **Menu-Based Interfaces**: These interfaces present the users with a list of options (menus). Most popular are pull-down menus. - **Forms-Based Interfaces**: Displays a form to each user, which is designed and programmed for naive users. - **Form-Based Interface**: Image shows a form for hotel booking with fields for required information like check-in date, check-out date, number of people, special requirement, personal information etc. - **Graphical User Interfaces**: Displays a schema to the user in diagrammatic form. Utilizes both menus and forms and uses a pointing device to pick certain parts of the displayed diagram. - **Natural Language Interfaces**: Has its own schema and dictionary. Refers to them while interpreting a request. If interpretation is successful, a high-level query is generated. Otherwise, a dialogue is started with the user for further clarification. - **Interfaces for Parametric Users**: Parametric users have a small set of operations that they must perform repeatedly. System analysts and programmers design and implement a special interface for these users. - **Interfacees for the DBA**: Privileged commands (like for creating accounts, granting access) that can be used only by DBA staff. - The image shows an example of a menu bar and pull-down menus in a Notepad application. # Database Management Systems Database System Environment ## DBMS Component Modules - **DDL compiler**: Processes schema definition and stores it in the catalog. - **Query compiler**: Handles high-level queries. - **Pre-compiler**: Extracts DML commands from an application program. - **DML compiler**: Compilation of DML commands into object code. - **Runtime database processor**: Handles database accesses at runtime. - **Stored data manager**: Data transfer between disk and main memory. - An image shows a diagram of the interaction between various modules in a DBMS, including DDL compiler, query compiler, pre-compiler, and DML compiler. The diagram also shows the connection between these modules and the database and the database manager. ## Database System Utilities - Common utilities have the following types of functions: - **Loading**: - Used to load existing data files into the database. - The source file format and the target data file structure are mentioned to the utility. Automatically reformats the data and stores it in the database. - **File reorganization**: Used to reorganize a database file into a different file organization. - **Performance monitoring**: Monitors database usage and provides statistics to the DBA. ## Other Tools - **CASE tools**: Used during the design of the database. - **Data Dictionary (or data repository) system**: Storing catalog information. - **Application Development Environments**: Provides an environment for developing database applications, such as JBuilder and PowerBuilder system. - **Communications software**: Remote access to the database. # Database Management Systems Centralized DBMS Architecture - **Centralized DBMS Architecture**: Earlier, mainframe computers were used to process all system functions. Users accessed systems via computer terminals, which provided only display capabilities, but no processing capabilities. - **Processing**: Performed remotely on the computer system. - **Display information**: Sent to the terminals. - **Prices of hardware**: Declined, terminals replaced by PCs & workstations. - An image shows a diagram of the centralized DBMS architecture, including a mainframe computer, terminals, a network, and various software components. # Database Management Systems Basic Client/Server Architectures - **Goal**: Define specialized servers with specific functionalities. - **Client**: User machine that provides user interface capabilities & local processing. - **Server**: Provides services to client machines. - An image shows two diagrams - **Physical Client-Server Architecture** and **Logical Client-Server Architecture**. The physical client-server architecture shows clients and servers located at different sites connected via a network. The Logical client-server architecture shows a network with multiple clients, a print server, a file server, a DBMS server, and a client. ## Two-Tier Client/Server Architectures - In RDBMS, user interfaces & application programs moved to client side. - Query & transaction functionality is on the server side (query server/transaction server). - When DBMS access is required, the program establishes a connection with the DBMS (server side). - **Open Database Connectivity (ODBC)** provides API that allows programs (client-side) to call DBMS. - **Advantages of two-tier architecture**: - Simplicity - Compatibility - **Emergence of World Wide Web**: Led to three-tier architecture. ## Three-Tier Client/Server Architectures - **Additional intermediate layer**: Between client & database server, application server or web server. - **Stores rules**: Used to access data. - **Accepts requests**: From the client. - **Processes the requests**: Then sends commands to the database server. - The image shows a diagram of a three-tier client-server architecture. The client has a GUI, which makes requests to the application server. This server processes the requests and sends them to the database server to retrieve the data from the database management system.