UNIT 01 Introduction to Database Systems.pdf

Slide 1- 1  UNIT 01 Introduction to Database Systems Reviewed and Edited by Prof. Suleiman H. Mustafa Slide 1- 2 OUTLINE Basic Definitions and Types of Databases, Database Applications, and database system DBMS Function...

Slide 1- 1  UNIT 01 Introduction to Database Systems Reviewed and Edited by Prof. Suleiman H. Mustafa Slide 1- 2 OUTLINE Basic Definitions and Types of Databases, Database Applications, and database system DBMS Functionality Example of a Database (UNIVERSITY) Main Characteristics and advantages of the Database Approach Database Users Data Models, Schemas, Instances, and States DBMS Languages Database System Utilities and Tools Dr. Suleiman H. Mustafa Slide 1- 3 Basic Definitions and Concepts Dr. Suleiman H. Mustafa Slide 1- 4 Database Concepts Data Known facts that can be recorded and have an implicit meaning. Database A collection of organized related data to serve a given purpose. Types of Databases Numerical Databases Textual Databases Image databases Multimedia Databases Databases are used in most human activities and fields of application Examples: Banking, Insurance, Transportation, Healthcare, Retail, Manufacturing, Education, GIS, etc. Dr. Suleiman H. Mustafa Slide 1- 5 Database Concepts Data Warehouse A data warehouse is a type of data management system that is designed to enable and support business intelligence (BI) activities, especially data analysis (analytics). Data warehouses are solely intended to perform queries and analysis and often contain large amounts of historical data. Dr. Suleiman H. Mustafa Slide 1- 6 Database Management System (DBMS) A computerized database is created and managed using a special software called Database Management System (DBMS) A Database Management System (DBMS) is A software package/ system to facilitate the creation, access, and maintenance of a database. DBMS Components (see figure next slide) The DBMS consists of several subsystems each of which is responsible for a given function. The DBMS interacts with the operating system to manage and access data. Dr. Suleiman H. Mustafa Slide 1- 7 Slide 1- 8 Functions of DBMS 1. Defining a particular database in terms of its data types, structures, and constraints 2. Constructing or Loading the initial database contents on a secondary storage device. 3. Manipulating the database: Retrieval: Querying, generating reports, and accessing the database through Web applications Update: Insertions, deletions and modifications to its content 4. Processing and Sharing by a set of concurrent users and application programs (yet, keeping all data valid and consistent) Dr. Suleiman H. Mustafa Slide 1- 9 Functions of DBMS 5. Protecting or providing security measures to prevent unauthorized access 6. Presentation and Visualization of data 7. System and software Maintenance of the database and associated programs over the lifetime of the database application. Dr. Suleiman H. Mustafa Slide 1- 10 Users Interaction with Databases through their Applications Slide 1- 11 Database Applications Database applications are:  programs developed by database users to utilize the data stored in a given database Applications interact with a database by generating - Queries (retrieval): that access different parts of data and formulate the result of a request. - Update Transactions (insert, delete, modify): that may read some data and “update” certain values or generate new data and store that in the database Applications must not allow unauthorized users to access data and must keep up with changing user requirements against the database Dr. Suleiman H. Mustafa Slide 1- 12  Database System A Database System consists of the DBMS software together with the data itself and the database applications. Dr. Suleiman H. Mustafa Slide 1- 13 Example of a Database (with a Conceptual Data Model) Dr. Suleiman H. Mustafa Slide 1- 14 Example of a Database (with a Conceptual Data Model) Application area of the example: Part of a UNIVERSITY environment. Some entities in the example: STUDENTs COURSEs SECTIONs (of COURSEs) (academic) DEPARTMENTs INSTRUCTORs Dr. Suleiman H. Mustafa Slide 1- 15 Example of a Database (with a Conceptual Data Model) Some relationships between entities: SECTIONs are of specific COURSEs STUDENTs take SECTIONs 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 (see next slide), such as the ENTITY-RELATIONSHIP data model (discussed later) Dr. Suleiman H. Mustafa Slide 1- 16 Conceptual View of Entities and Relationships COURSEs have PREREQUISITEs STUDENT COURSE STUDENTs register SECTIONs relate in SECTIONs to COURSEs COURSEs are offered by DEPARTMENTSs SECTION DEPARTMENT STUDENTs major in DEPARTMENTs INSTRUCTORs teach INSTRUCTOR IINSTRUCTORs belong to SECTIONs DEPARTMENTs Dr. Suleiman H. Mustafa Slide 1- 17 Example of a simple database Slide 1- 18 Example of Queries and Updates 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 Examples of Update Transactions: 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 Dr. Suleiman H. Mustafa Slide 1- 19 Characteristics and Advantages of the Database Approach Dr. Suleiman H. Mustafa Slide 1- 20 Main Characteristics of the Database Approach for Storing Data 1. Self-describing nature of a database system: A DBMS catalog (or repository or data dictionary) stores the description of a particular database (e.g. schema descriptions, data structures, types, constraints, design decisions, application program descriptions, user information, etc.) This description is called meta-data (that is data about data). This allows the DBMS software to work with different database applications. See next slide: Dr. Suleiman H. Mustafa Slide 1- 21  Data Dictionary The information stored in the DD include: schema descriptions (see next slide) and other information such as design decisions, application program descriptions, user information, usage standards, etc. Dr. Suleiman H. Mustafa Slide 1- 22 Example of a Simplified Database Catalog (Data Dictionary) Slide 1- 23 Meta-Data Example Slide 1- 24 Main Characteristics of the Database Approach for Storing Data 2. Insulation between programs and data: This is called program-data independence. Allows changing data structures and storage organization without having to change the DBMS access programs. Logical data independence: The capacity to change the conceptual or logical schemas without having to change the requirements representations and their associated application programs. Physical data independence: The capacity to change the physical schema without having to change the conceptual or logical schema. (e.g., adding an index to the physical structure). Dr. Suleiman H. Mustafa Slide 1- 25 The Three-Levels Mapping Slide 1- 26 Main Characteristics of the Database Approach 3. Data Abstraction: A data model is used to hide storage details (see next slide) 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. Dr. Suleiman H. Mustafa Slide 1- 27 Internal Storage Format Slide 1- 28 Main Characteristics of the Database Approach (continued) 5. Sharing of data and multi-user transaction processing: Allowing a set of concurrent users to retrieve from and to update the 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. Dr. Suleiman H. Mustafa Slide 1- 29 Advantages of Using the Database Approach Controlling redundancy in data storage and in development and maintenance efforts. Representing complex relationships among data and providing storage structures (e.g. indexes) for efficient query processing. Providing backup and recovery services. Restricting unauthorized access to data Sharing of data among multiple users Optimizing queries for efficient processing. Enforcing integrity constraints on the database. Providing multiple interfaces to different classes of users. Dr. Suleiman H. Mustafa Slide 1- 30 Additional Implications of Using the Database Approach Potential for enforcing standards Reduced application development time Economies of scale across applications and departments Flexibility to change data structures Availability of current information Dr. Suleiman H. Mustafa Slide 1- 31 Database Users Dr. Suleiman H. Mustafa Slide 1- 32 Database System Environment and Users Slide 1- 33 Database Users Database Administrators (DBA): Responsible for authorizing access to the database, Coordinating, Controlling, and monitoring its use, acquiring software and hardware resources, monitoring efficiency of database operations. Database Analysts and Designers: Responsible for defining the content, the structure, the constraints, and functions or transactions against the database. They must communicate with the end-users and understand their needs. Dr. Suleiman H. Mustafa Slide 1- 34 Database Users Database Application Developers: This group includes: system analysts, application programmers, and business analysts. They write application programs. Database End-users: They use the data for queries, reports and some of them update the database content. System Administrator, Operators and Maintenance Personnel: They manage the actual running and maintenance of the database system hardware and software environment. Dr. Suleiman H. Mustafa Slide 1- 35 Data Models and Database Schemas Slide 1- 36 Data Models Data Model: A set of concepts to describe the structure of a database (its elements and relationships) the operations for manipulating these structures, and certain constraints that the database should obey. Constraints specify some restrictions on valid data; these constraints must be enforced at all times. Data model operations are used for specifying database retrievals and specifying updates (insert, delete, modify) See next slide Dr. Suleiman H. Mustafa Slide 1- 37 Data Models Slide 1- 38 Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Database Schemas Database Schema: The description of a database. Includes descriptions of the database structure, data types, and the constraints on the database. Schema Diagram: An illustrative display of (most aspects of) a database schema. Schema Construct: A component of the schema or an object within the schema, e.g., STUDENT, COURSE. Dr. Suleiman H. Mustafa Slide 1- 39 Categories of Data Models Conceptual (High-level) data models: Provide semantic concepts that are close to the way many users perceive data. e.g. Entity-relationship model (see figure) Logical (Intermediate-level) data models: Provide concepts for representing data in the database. e.g. relational data model used in many commercial database management systems. (see figure) Physical (low-level) data models: Provide concepts that describe details of how data is stored in the computer. e.g. DBDL (database definition language) (see figure) Dr. Suleiman H. Mustafa Slide 1- 40 Conceptual Data Model using ER (conceptual schema example) Slide 1- 41 Logical Data Modeling using the Relational Model (Logical database schema) Slide 1- 42 Physical Data Model using DBDL (Physical Database Schema ) Dr. Suleiman H. Mustafa Slide 1- 43 Database State Slide 1- 44 Database State Database State: The actual data (i.e., the collection of all the data ) stored in a database at a particular moment in time. Also called database instance (or occurrence or snapshot). The term instance is also applied to individual database components, e.g. record instance, table instance, entity instance Dr. Suleiman H. Mustafa Slide 1- 45 Database State Initial Database State: Refers to the database state when it is initially loaded into the system. Valid State: A state that satisfies the structure and constraints of the database. Database State Vs. Database Schema The database schema changes very infrequently. The database state changes every time the database is updated. Dr. Suleiman H. Mustafa Slide 1- 46 Example of a Database State (Instance) Slide 1- 47 Database Design Slide 1- 48 Database Design and Applications Design The process of designing a database system involves two main activities: Database design and Applications design Database design focuses on the steps of constructing the database structure starting from user requirements to the physical specification of data on the storage devices. Applications design focuses on the programs and interfaces that access the database Generally considered part of software engineering Dr. Suleiman H. Mustafa Slide 1- 49 Database System Design Phases The database design involves three phases that are mapped to each other in the following sequence (see next figure): Conceptual design, Logical design, and Physical design Dr. Suleiman H. Mustafa Slide 1- 50 Overview of the Database Design Process Slide 1- 51 DBMS Languages and Utilities Slide 1- 52  DBMS Languages DBMS Languages Data Definition Data Manipulation Languages (DDL) Languages (DML) High-Level Low-Level Slide 1- 53 DBMS Languages 1. Data Definition Languages (DDL) Used by the DBA and database designers to specify the physical schema of a database. Example: CREATE TABLE (….); in SQL 2. Data Manipulation Languages (DML) 2.1 High-Level or Non-procedural Languages: These include the relational language SQL Examples: SQL Plus, MySQL 2.2 Low Level or Procedural Languages: These must be embedded in a programming Language (Example: PL/SQL in Oracle DBMS) Dr. Suleiman H. Mustafa Slide 1- 54 Database System Utilities To perform certain functions such as: Loading data stored in files into a database. Including data conversion Backing up the database periodically on secondary storage devices Reorganizing database file structures. Performance monitoring Report generation Other functions, such as sorting, user monitoring, data compression, … etc. Dr. Suleiman H. Mustafa Slide 1- 55 ADE and CASE Tools Application Development Environments (ADE) and Computer-Aided Software Engineering (CASE) tools: Examples: PowerBuilder (Sybase) JBuilder (Borland) JDeveloper 10G (Oracle) Dr. Suleiman H. Mustafa Slide 1- 56 Summary Basic Definitions DBMS Functionality Example of a Database (UNIVERSITY) Main Characteristics and advantages of the Database Approach Types of Database Users Data Models, Schemas, Instances, and States DBMS Languages. Database System Utilities and Tools Database design phases Dr. Suleiman H. Mustafa Slide 1- 57