COMP3205 DB Ch1 (FL24) (1).pptx

COMP 3205 INTRODUCTION TO DATABASE SYSTEMS TEXTBOOK: DATABASE SYSTEMS (MODELS, L ANGUAGES, DESIGN, AND APPLICATION PROGRAMMING), 6 T H EDITION BY ELMASRI AND NAVATHE Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 1 ENCE Chapter 1: Introduction t...

COMP 3205 INTRODUCTION TO DATABASE SYSTEMS TEXTBOOK: DATABASE SYSTEMS (MODELS, L ANGUAGES, DESIGN, AND APPLICATION PROGRAMMING), 6 T H EDITION BY ELMASRI AND NAVATHE Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 1 ENCE Chapter 1: Introduction to Database What is Database (DB) What is Database Management System (DBMS) An Example (Illustration) Qualities of the Database Approach Advantages of using the DBMS approach Actors on the Scene Workers behind the Scene A Brief History of Database Applications When Not to Use a DBMS Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 2 ENCE  Introduction: “Database” definition Database is: a Collection of related data, such data that can be recorded and that have implicit meaning Essential properties of a Database:  Represents some aspect of the real world (Mini-world or Universe of Discourse (UoD)); i.e. has some degree of interaction with events in real world  Logically coherent collection of data with inherent meaning. Can not be random data; there must be some source from which the data are derived.  Designed and Built for a specific purpose; i.e. there are audience (users) who are actively interested in the Database contents. Examples: Amazon.com, Muscat Bank, SQU Library Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 3 ENCE Database Types 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 Data warehouses and online analytical processing (OLAP) systems ◦ Extract and analyze useful business information from very large databases to support decision making Real-time and active database technology ◦ Control industrial and manufacturing processes Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 4 ENCE Database Management System (DBMS) Definition: is a Collection of programs (software systems) that enables users to define, create, maintain, manipulate and share a database. Defining a database: ◦ Identify the data types, structures, and restrictions of the data to be stored Constructing a database: ◦ Process of storing the data such away it can be controlled by the DBMS Manipulating a database: ◦ Query, update the database, and generate reports from the data Maintain the database system ◦ Allow the system to develop as requirements change over time Sharing a database ◦ Allow multiple users and programs to access the database at the same time Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 5 ENCE DBMS Concepts Meta-data ◦ Is Database definition or descriptive information Stored by the DBMS in the form of a database catalog or dictionary Application program ◦ Accesses database via queries or transactions Query ◦ Request for specific data from a database. Causes some data to be retrieved Transaction ◦ May cause some data to be read and some data to be written into the database Protecting a database including: ◦ System protection (against Hardware/Software crashes) ◦ Security protection (against unauthorized access) Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 6 ENCE Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 7 ENCE Example: University Database Information concerning students, courses, and grades in a university environment Data records Specify structure of records of each ◦ STUDENT file by specifying data type for each ◦ COURSE data element; e.g. ◦ SECTION ◦ Student names: String of alphabetic ◦ GRADE_REPORT characters ◦ Student numbers: integer ◦ PREREQUISITE ◦ Grades: single character {‘A’, ‘B’, ‘C’, ‘D’, ‘F’} Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 8 ENCE Example: University Database (cont’d) Construct UNIVERSITY database ◦Store data to represent each student, course, section, grade report, and prerequisite as a record in appropriate file ◦Identify relationships among the records Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 9 ENCE Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 10 ENCE Example: University Database (cont’d) Manipulation involves querying and updating  Query examples: Retrieve the transcript of ‘Brown’ List the names of students who took the ‘Database’ course offered in fall 2017 with their grades in that course What are the prerequisites of the ‘Database’ course  Update example:  transaction  Enter grade ‘A’ for ‘Smith’ in the ‘MATH2410’ course  Change the class (year) of ‘Brown’ to ‘junior’  Add a new section for the ‘Database’ course of this semester Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 11 ENCE Designing a Database Phases for designing a database application:  Requirements specification and analysis ◦ Requirements are documented in detail and then transformed into conceptual design  Conceptual design ◦ Data are represented using computerized tools for easy modification and transformed into a database implementation (concept sketches/flowchart)  Logical design ◦ Expressed in a (Relational) data model (provide data’ properties that easier to understand by users)  Physical design ◦ Additional specifications are provided to store and access the database Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 12 ENCE Requirements Specification Example Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 13 ENCE Conceptual design Example Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 14 ENCE Logical design Example Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 15 ENCE File Processing vs Database TRADITIONAL FILE DATABASE APPROACH PROCESSING Each user defines and implements Single repository maintains data that the files needed for a specific is defined once and then accessed by software application various users When data is updated Characteristics: ◦ Cause inconsistency of the data ◦ Self-describing nature of a database ◦ Difficult to keep up to date (scalability) system ◦ Other users may not be aware of last ◦ Insulation between programs and data, changes and data abstraction ◦ Data redundancy ◦ Support of multiple views of the data ◦ Duplication of effort ◦ Sharing of data and multiuser transaction processing ◦ Storage space is wasted Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 16 ENCE  Self-Describing Nature of a Database System Database system contains complete definition of structure and constraints Meta-data  Information describes the structure of each file/record, their types and storage format of each data item of the database.  Such information is stored in a database catalog which is used by: ◦ DBMS software ◦ Database users who need information about database structure ◦ DBMS software: ◦ Work equally well with any number of database applications as long as their definitions are provided. ◦ Able to extract database definition from the catalog and use these definitions. Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 17 ENCE Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 18 ENCE Insulation Between Programs and Data Program-data independence ◦Structure of data files is stored in DBMS catalog separately from access programs; thus it is easier to update the structure of files without effecting the programs. Program-operation independence ◦Operations (functions/methods) specified in two parts: ◦ Interface includes operation name and data types of its arguments ◦ Implementation can be changed without affecting the interface Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 19 ENCE Data Abstraction Data abstraction ◦ Allows program-data independence and program-operation independence DBMS provides users with a Conceptual representation of data Does not include details of how data is stored or how operations are implemented Data model (type of data abstraction) it is a way to structure and represent data conceptually, focusing on the relationships between data objects (like entities, attributes, and their relationships) without exposing the low-level storage details or implementation. ◦ Example: Student as object with attributes (ID, name, enrollment, major) ◦ course as object with attributes (code, course name, credit hours) Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 20 ◦ Student-course ENCE relationship: A Student can enroll in many Courses, and a Conceptual Representation Example Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 21 ENCE Support of Multiple Views of the Data View can be ◦Subset of the database ◦Contains virtual data derived from the database files but is not explicitly stored (e.g. output of a query ) In Multiuser DBMS ◦Users have a variety of distinct applications must provide facilities for defining multiple views (e.g. different view for students’ advisors than for finance department) Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 22 ENCE 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 can be done in a controlled manner (Result of the updates is correct) Online transaction processing (OLTP) application ◦Examples: Travel agents, Hotel reservations ◦Ensure simultaneous transactions operate efficiently Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 23 ENCE Sharing of Data and Multiuser Transaction Processing (cont'd.) Transaction ◦Is essential to many database applications ◦Is an executing program or process that includes one or more database accesses, e.g. reading or updating of database records. ◦Isolation property ◦ Each transaction appears to be executed (separately) in isolation from other transactions ◦Atomicity property ◦ Either all the database operations in a transaction are executed or none are. To ensure no transaction failure occur. ◦ Example: bank: transferring money from account1 to account2 Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 24 ENCE Advantages of Using the DBMS Approach 1. Controlling Redundancy ◦Data normalization ◦ Store logical data items (e.g. student’s name/birth date) in single place to ensure consistency and save storage ◦Denormalization ◦ Some fields values are repeated in multiple files (e.g. having student name and course number in GRADE_REPORT records) ◦ Sometimes it is necessary to use controlled redundancy to improve the performance of queries. ◦ To maintain records consistency, DBMS automatically enforce such redundancy checks. Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 25 ENCE Advantages of Using the DBMS Approach (cont’d) 2. Restricting unauthorized access of a multi- users large database  Type of access operation (retrieval/update) are controlled, DBMS provides: ◦Security and authorization subsystem: DBA uses “Privileged software” to create users accounts and specify account restrictions. DBMS enforce such restrictions automatically Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 26 ENCE Advantages of Using the DBMS Approach (cont’d) 3. Providing persistent storage for program objects ◦ Applicable for the usage of Object-oriented database systems ◦ Complex object or data structure in C++ can be stored permanently in an object-oriented DBMS  e.g. using classes in OOP ◦ Impedance mismatch problem ◦ Such problem exist in traditional database systems as the data structure were incompatible with programming language’s data. ◦ To avoid the problem: Object-oriented database systems typically offer data structure compatibility Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 27 ENCE Advantages of Using the DBMS Approach (cont’d) 4. Providing storage structures and search techniques for efficient query processing ◦Indexes files are based on tree/hash data structure to speed up disk search ◦DBMS has a Buffering and caching module to mantain parts of database in main memory ◦Query processing and optimization module to select an efficient query execution plan for each query based on the existing storage structure. Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 28 ENCE Advantages of Using the DBMS Approach (cont’d) 5. Providing backup and recovery ◦ Backup and recovery subsystem of the DBMS is responsible for recovery from Hardware/software failures ◦ Ensure the transaction is resumed from the interruption point ◦ The database is restored to the state it was in before the interruption ◦ Disk backup is essential in case of disastrous disk failure Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 29 ENCE Advantages of Using the DBMS Approach (cont’d) 6. Providing multiple user interfaces Variety of types of users with different levels of technical knowledge and needs of a database, DBMS provide a variety of users interfaces using Graphical user interfaces (GUIs) Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 30 ENCE Advantages of Using the DBMS Approach (cont’d) 7. Representing complex relationships among data ◦A database may include numerous varieties of data that are interrelated in many ways (e.g. instructor offers n different courses in each semester) ◦DBMS capable of: ◦ Representing a variety of complex relationships among data ◦ Defining new relationships when needed ◦ Easily retrieve and update related data Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 31 ENCE Advantages of Using the DBMS Approach (cont’d) 8. Enforcing integrity constraints ◦ Simple Constraint Example: specifying a data type for each data item (Grade {‘A’, ‘B’, ‘C’, ‘D’, ‘F’, ‘I’}) ◦ Referential integrity constraint ◦ A record in one file must be related to records in other files (e.g. every section record must be related to a course record) ◦ Key or uniqueness constraint ◦ Specifies uniqueness on data item values. (e.g. every student record must have a unique value for student_number) ◦ Business rules ◦ Constraints to be checked by update programs or at the time of data entry ◦ Inherent rules to guarantee the validity of the data model (e.g. grade ‘Z’ is not valid value) Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 32 ENCE Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 33 ENCE Advantages of Using the DBMS Approach (cont’d) 9. Permitting inferencing and actions using rules ◦Deductive database systems ◦ Provide capabilities for defining deduction (inference/conclusion) rules ◦ Inferencing new information from the stored database facts ◦ Example: determining a student is under probation ◦Trigger (DBMS able to associate trigger with tables) ◦ A form of a rule activated by updates to the table that results in performing additional operations to some other tables. ◦ Example: as soon student add a course, number of current/remaining credit hours calculated Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 34 ENCE Advantages of Using the DBMS Approach (cont’d) Stored 9. Permitting inferencing and actions using rules Procedure ◦Stored procedures manually invoked (explicitly called) ◦ More involved procedures to enforce rules. by user/application ◦ Invoked (called) appropriately when certain conditions are met Used for repeated tasks Trigger automatically invoked by specific events(e.g. insert) enforce rules ensure data consistency (REACT TO CHANGED IN A TABLE) Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 35 ENCE Advantages of Using the DBMS Approach (cont’d) Permitting inferencing and actions using rules ◦Active database systems ◦ Automatically responds to active rules when conditions are met ◦ Example: ◦ Suppose the system monitors a stock table, and when the stock quantity of a product falls below 10 units, it automatically triggers a restock order by calling an external system or function. This whole process happens without user intervention, based on predefined rules Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 36 ENCE Advantages of Using the DBMS Approach (cont’d) 10. Additional implications (effects) of using the database approach ◦Potential for Enforcing Standards; permits the DBS to define standards among database users. This allows cooperation among different departments, projects, and users ◦ Standard can be defined for example: report structure, display format, and terminology. ◦ Each user group has control of its own data files and software. ◦Reduced application development time; less time required to create new applications using DBMS facilities; Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 37 ◦ such as retrievalENCE of certain data from database for printing a new report.  Direct access to Actors on the Scene DB System analysts ◦Determine requirements of end users and develop specifications for their standard transactions Application programmers ◦Implement and test these specifications as programs. ◦Also known as “software developers” or “software engineers” ◦They must have full knowledge and understanding of the DBMS capabilities Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 39 ENCE Actors on the Scene (cont’d) Database designers are responsible for: ◦Identifying the data to be stored ◦Choosing appropriate structures to represent and store this data Database administrators (DBA) are responsible for: ◦Authorizing access to the database ◦Coordinating and monitoring its use ◦Acquiring (obtaining) software and hardware resources Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 40 ENCE Actors on the Scene (cont’d) End users ◦People whose jobs require access to the database for querying, updating, and generating reports (actual users of the data) ◦Types of end users: ◦ Casual end users: occasionally access the database but may need different information each time ◦ Naive or parametric end users: constantly querying and updating the database (e.g. bank tellers) ◦ Sophisticated end users: engineers, scientists, and business analysts (implement their own applications to meet their complex requirements) ◦ Standalone users: use ready-made packages that are easy-to-use menu-based to maintain personal database (e.g. tax package) Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 41 ENCE  Not interested on the DB Workers behind the Scene content itself DBMS system designers and implementers ◦ Design and implement the DBMS modules and interfaces as a software package. (including implementing the catalog, query language processing, interface processing, accessing and buffering data, controlling concurrency, handling data backup and recovery and security) Tool developers ◦ Design and implement tools. Tools are optional software packages. (includes packages for database design, graphical interfaces, and test data generation) Operators and maintenance personnel ◦ Responsible for running and maintenance of hardware and software environment for database system Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 42 ENCE  https:// A Brief History of Database www.youtube.com/ watch?v=- Applications bMiKvZRzwk Early database applications using hierarchical and network systems (around mid 1960s) ◦ Large numbers of records of similar structure ◦ Main problems: ◦ Lack of data abstraction and program-data independent ◦ Expensive to implement as they provided only programming language interfaces ◦ These databases were on large expensive mainframe computers Providing data abstraction and application flexibility with relational databases (late 1970s – early 1980s) ◦ Separates physical storage of data from its conceptual representation ◦ Provides a mathematical foundation for data representation and querying ◦ Relational systems were initiated Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 43 ENCE A Brief History (cont’d) Object-oriented applications and the need for more complex databases (1980s) ◦Used in specialized applications: engineering design, multimedia publishing, and manufacturing systems Interchanging data on the Web for e-commerce using XML (1990s) ◦Extended markup language (XML) primary standard for interchanging data among various types of databases and Web pages ◦Documents can be linked via hyperlinks Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 44 ENCE A Brief History (cont’d) Extending database capabilities for new applications (currently) ◦ Extensions to better support specialized requirements for applications ◦ Scientific applications, Images and videos storage and retrieval, data mining, Spatial applications for weather, maps and geographical information. ◦ Enterprise resource planning (ERP) software ◦ Used to combine a variety of functional areas within an organization ◦ Customer relationship management (CRM) software ◦ Provide a variety of Web-portal interfaces to interact with back-end databases. (for marketing and customer support functions) Databases versus information retrieval ◦ Information retrieval (IR): deals with books, manuscripts, and various forms of library-based articles. It concern with keyword search mechanism Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 45 ENCE A Brief History (cont’d) The 2010s – distributed databases and cybersecurity Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 46 ENCE When Not to Use a DBMS More recommendable to use regular files for: ◦Simple, well-defined database applications not expected to change at all ◦Stringent (strict), real-time requirements that may not be met because of DBMS overhead ◦Embedded systems with limited storage capacity ◦No multiple-user access to data Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 47 ENCE Ethics Related to Database System Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 48 ENCE  Summary Database ◦ Collection of related data (recorded facts) that reflects some aspect of real-world and used for specific purposes. DBMS ◦ Generalized software package for implementing and maintaining a computerized database. (Database + Software = DBMS) Several categories of database users Characteristics and advantages of using DB and DBMS When DBMS is not suitable Database applications have evolved ◦ Current trends: IR, Web Chapter 01 COPYRIGHT@SQU 2018, COLLEGE OF SCIENCE, DEPARTMENT OF COMPUTER SCI 49 ENCE