Comp-3150: Database Management Systems PDF

Comp-3150: Database Management Systems Ramez Elmasri , Shamkant B. Navathe(2016) Fundamentals of Database Systems (7th Edition), Pearson, isbn 10: 0-13-397077-9; isbn-13:978-0-13-397077-7. Chapter 1: Databases and Database Users Comp-3150 Dr. C. I. Ezeife (2024) wit...

Comp-3150: Database Management Systems Ramez Elmasri , Shamkant B. Navathe(2016) Fundamentals of Database Systems (7th Edition), Pearson, isbn 10: 0-13-397077-9; isbn-13:978-0-13-397077-7. Chapter 1: Databases and Database Users Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 1 materials from Elmasri & Navathe, 7th Chapter 1: Databases and Database Users : Outline 1. Example Databases and Applications 2. Basic Definitions (Database, Data, Miniworld, DBMS, Database System) 3. Typical DBMS Functionality (or Work) 4. Example of a Database (UNIVERSITY) 5. Main Characteristics of the Database Approach 6. Types of Database Users 7. Advantages of Using the Database Approach Versus File System Approach 8. Historical Development of Database Technology Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 2 materials from Elmasri & Navathe, 7th 1. Example Databases and Applications 1. Bank saving/checking account databases for ATM banking System 2. Hotel reservation database for Hotel booking System 4. Airline reservation database for Flight booking System 5. Library book catalogue database for Library Management System 7. Student record database for Student Information System Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 3 materials from Elmasri & Navathe, 7th 2. Basic Definitions (Database, Data, Miniworld, DBMS, Database System, Data type) 1. What is a Database? It is a collection of related data. Example is a student record database. 2. What is/are Data? Data are known facts that can be recorded and have an implicit meaning. Examples are data for describing a student such as name, course such as course id, course enrolment. 3. What is a Mini-world? Some part of the real world about which data is stored in a database. For example, student grades and transcripts at a university. 4. What is a Database Management System (DBMS)? A software package/ system to facilitate the creation and maintenance of a computerized database. Example is Oracle 12c DBMS, MySQL DBMS. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 4 materials from Elmasri & Navathe, 7th 2. Basic Definitions (Database, Data, Miniworld, DBMS, Database System, Data type) 5. What is Database System? The DBMS software together with the data itself. Sometimes, the applications are also included. Example is a student information system (SIS). 6. Types of data stored in a database & database management systems (dbms) 1) Traditional datatypes mostly textual or numeric 2) New data types include social network posts, images, video clips are managed by non traditional database systems like big data and not only sql (NOSQL) systems used by companies such as google, amazon and yahoo. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 5 materials from Elmasri & Navathe, 7th 2. A Database System Environment Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 6 materials from Elmasri & Navathe, 7th 3. Typical DBMS Functionality (or Work) 1. DBMS provides a data model which are the data types, structures, and constraints for defining a particular database in (e.g., the relational model) 2. DBMS provides a data definition language (DDL) for Constructing or Loading the initial database structures and contents on a secondary storage medium (e.g., SQL). 3. DBMS provides a data manipulation language (DML) (e.g., SQL) for manipulating the database as follows: Retrieval: Querying, generating reports Modification: Insertions, deletions and updates to its content Accessing the database through Web applications 4. DBMS allows processing and sharing of data through concurrent access to data by users and application programs – yet, keeping all data valid and consistent by enforcing integrity constraints (business rules that must hold) (ICs). - Example ICs are domain constraint for specific data type of an attribute, key constraints for unique key values, foreign key or referential ICs for records in a file referencing records in another file, entity IC requiring a value for a unique identifying attribute, general semantic IC. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 7 materials from Elmasri & Navathe, 7th 3. Typical DBMS Functionality (or Work) 5. DBMS provides Insulation between programs and data using 3-level architecture: Called program-data independence. Allows changing data structures and storage organization without having to change the DBMS access programs. 6. DBMS provides database security and maintenance facilities such as: Protection or Security measures to prevent unauthorized access Maintenance of the database and associated programs over the lifetime of the database application using such mechanisms as the system catalog (called meta data) that keeps the description of each database. Called database, software, and system maintenance Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 8 materials from Elmasri & Navathe, 7th 4. Example of a Database (UNIVERSITY) (With a Conceptual Data Model) An example university database for maintaining information concerning students, courses, grades in a university environment is shown next. Mini-world for the example: Part of a UNIVERSITY environment. Some mini-world entities: STUDENTs COURSEs SECTIONs (of COURSEs) (academic) DEPARTMENTs INSTRUCTORs Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 9 materials from Elmasri & Navathe, 7th 4. Example of a Database (UNIVERSITY) (With a Conceptual Data Model) Some mini-world relationships: SECTIONs are of specific COURSEs STUDENTs take SECTIONs to obtain grades COURSEs have prerequisite COURSEs INSTRUCTORs teach SECTIONs COURSEs are offered by DEPARTMENTs STUDENTs major in DEPARTMENTs Note: The above entities and relationships are typically expressed in a conceptual data model, such as the ENTITY-RELATIONSHIP data model Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 10 materials from Elmasri & Navathe, 7th 4. Example of a Database (part of a UNIVERSITY DB) Figure 1.2 A database that stores student and course information. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 11 materials from Elmasri & Navathe, 7th 5. Main Characteristics of the Database Approach 1. Self-describing nature of a database system: 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. 2. Insulation between programs and data: Called program-data independence made possible through the 3-level architecture of external level, conceptual level and physical level. Allows changing data structures and storage organization without having to change the DBMS access programs. ----------------------------------------------------------------------------- * Some newer systems such as a few NOSQL systems need no meta-data: they store the data definition within its structure making it self describing Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 12 materials from Elmasri & Navathe, 7th 5. Example of a simplified database catalog Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 13 materials from Elmasri & Navathe, 7th 5. Main Characteristics of the Database Approach 3. Provision of Data Abstraction with a data model: 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 4. 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 representing the external data level. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 14 materials from Elmasri & Navathe, 7th 5. Main Characteristics of the Database Approach 5. Sharing of data and multi-user transaction processing: Allowing a set of concurrent users to retrieve from and to update the same database (e.g, airline reservation database). Concurrency control within the DBMS guarantees that each transaction is correctly executed or aborted Recovery subsystem ensures each completed transaction has its effect permanently recorded in the database OLTP (Online Transaction Processing) is a major part of database applications. This allows hundreds of concurrent transactions to execute per second. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 15 materials from Elmasri & Navathe, 7th 6. Types of Database Users: Five Types 1. Database administrators (DBA): responsible for managing the primary resource (database) and the secondary resource (dbms) and related software dba tasks including: Authorizing access to the database (eg. Creating user accounts) Coordinating and monitoring its use. Acquiring software and hardware resources as needed. Taking care of problems such as security breaches and poor system response time. 2. Database designers: responsible for identifying data to be stored in the database and choosing appropriate structures to represent and store this data and their design including design of user views. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 16 materials from Elmasri & Navathe, 7th 6. Types of Database Users: Five Types 3. End users: are people who query the database, and update it to generate reports. The categories of end users are: a. Casual end users who access the db occasionally using a sophisticated database query interphase (e.g. managers ) b. Naïve or parametric users who constantly query and update db using standard types of queries and updates called canned transactions. Many of these tasks are now available in mobile apps. Example tasks performed by the these users are: Bank customers and tellers checking account balances, posting withdrawal and deposits. Reservation agents or customers for airlines, hotels making reservations. Social media users posting and reading posts on websites. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 17 materials from Elmasri & Navathe, 7th 6. Types of Database Users: Five Types c. Sophisticated end users: e.g. scientists, engineers and business analysists implementing their own applications with dbms for complex requirements. d. Standalone users such as bank financial planners who use ready-made program packages with easy to use menu-based or graphics-based interfaces. 4. System analysts and application programmers (software engineers): System analysts define requirements of end users and develop specifications for standard transactions meeting these requirements. Application programmers implement these specifications as programs. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 18 materials from Elmasri & Navathe, 7th 6. Types of Database Users: Five Types 5. DBMS software and system builders: Dbms developers do not work on the db content but on designing, developing and operating the dbms software and system. These dbms workers include: a. DBMS system designers and implementers, who implement such dbms modules as the catalogue, query language processing, interface processing, data access and buffer access, concurrency control, data recovery and security. The dbms must interface with other system software such as operating systems, compilers and programming languages. b. Tools developers implement software packages that facilitate db modeling and design, db system design and improved performance. Example tools are, packages for natural language or graphical interfaces, prototyping etc. c. Operators and maintenance personnel: (system administrators personnel) are responsible for actual running and maintenance of the hardware and software environment for the db. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 19 materials from Elmasri & Navathe, 7th 7. Advantages of Using the Database Approach Versus File System Approach 1. Controlling redundancy in data storage: Centralized storage of data shared by multiple users and through data normalization. 2. Restricting unauthorized access to data. Only the DBA staff uses privileged commands and facilities. 3. Providing persistent storage: databases are used to provide persistent storage for program objects and data structures. 4. Providing Storage Structures (e.g. indexes) for efficient Query Processing. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 20 materials from Elmasri & Navathe, 7th 7. Advantages of Using the Database Approach Versus File System Approach 5. Providing optimization of queries for efficient processing. 6. Providing backup and recovery services. 7. Providing multiple interfaces to different classes of users. 8. Representing complex relationships among data. 9. Enforcing integrity constraints on the database. 10. Drawing inferences and actions from the stored data using deductive and active rules and triggers. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 21 materials from Elmasri & Navathe, 7th 7. Advantages of Using the Database Approach Versus File System Approach 11. Reduced application development time: Incremental time to add each new application is reduced 12. Other Advantages: dbms allows for enforcement of standards in the stored data Reduced application time. Flexibility in database application design. Availability of up to date data to all users (e.g. SIS). Better use of resources: consolidation of data and applications. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 22 materials from Elmasri & Navathe, 7th 8. Historical Development of Database Technology 1. Early database systems: the main types of early database systems were based on three main paradigms of hierarchical, network model based systems and inverted file system. These early systems operated like file systems implemented only on programming language interface with low data abstraction and program data independence capabilities. 2. Relational dbms: from late 1970’s came the relational dbms which separates the physical storage of data from its conceptual representation and provides a mathematical foundation for data representation and querying. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 23 materials from Elmasri & Navathe, 7th 8. Historical Development of Database Technology It also provides high level query languages as an alternative to programming lang interface so new queries can be written more quickly. It provided better data abstraction and program data independence. It became the dominant traditional dbms employed on all types of computers with good indexing, query optimization, concurrency access etc supports. 3. Object-oriented dbms: emerged in 1980’s with object oriented programming paradigm as a way to capture more complex data (not single valued) such as geographic information system(GIS) Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 24 materials from Elmasri & Navathe, 7th 8. Historical Development of Database Technology They are used in specialized applications such as engineering design and not used a lot as main dbms. Many relational dbms’s were extended as object relational dbms in order to capture the essence of object oriented dbms. 4. Web database using xml: data can be stored in the web using web publishing language like hypertext markup language (html) or the extended markup lang (xml) and users can access them using web browsers. Starting 1990’s electronic commerce (e-commerce) became a major application on the web where xml and web applications combine concepts from document systems with database concepts to dynamically extract data like flight information from databases. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 25 materials from Elmasri & Navathe, 7th 8. Historical Development of Database Technology 5. Some extended database capabilities: database systems offer extensions to support specialized requirements for new applications including Data mining applications for analyzing large amounts of data 6. Big data storage systems and nosql databases Nosql means not only sql. Nosql means that for big data applications such as social media web sites large e-commerce companies and cloud storage backup, some of the data are stored using sql systems, while others are stored using nosql which depend on the application requirements. Comp-3150 Dr. C. I. Ezeife (2024) with Figures and some 26 materials from Elmasri & Navathe, 7th