Database Management Systems

1. Database Management Systems Presentation by: Veselin Tsvetkov ID#: 100031146

2. Objectives • An overview of Database Management • Database • DBMS • Database Systems • Why use database? • Database architecture • An example of the Three Levels • Schema • Data Independence • Types of Database Models • Database design phases

3. Definitions • Data: Known facts that can be recorded and that have implicit meaning • Database: Collection of related data • Ex. the names, telephone numbers and addresses of all the people you know • Database Management System: A computerized record-keeping system

4. Database Management System • Goals of a Database Management System: • To provide an efficient as well as a convenient environment for accessing data in a database; • Enforce information security: database security, concurrency control, crash recovery • It is a general purpose facility for: • Defining database • Constructing database • Manipulating database

5. History of DBMS • 1960– First DBMS designed by Charles Bachman at GE. IBMs Information Management System (IMS) • 1970– Codd introduced the RDBMS • 1980– Relational model became popular and accepted as the main database paradigm. SQL, ANSI SQL, etc. • 1980 to 1990– New data models, powerful query languages, etc. Popular vendors are Oracle, SQL Server, IBMs DB2, Informix, etc.

6. Databases • Various types of data: Images, Text, complex queries, Data Mining, etc. • Enterprise Resource Planning (ERP) • Management Resource Planning (MRP) • Database in Web technologies Current Database trends: • Multimedia databases • Interactive video • Streaming data • Digital libraries

7. DBMS Functions Data Definition Data Manipulation Data Security and Integrity Data Recovery and Concurrency Data Dictionary Performance

8. Early File Systems vs. DBMS • Catalog in DBMS. Data definition in file systems is part of application programs. • Program-Data independence • Views • Sharing and Transaction processing

9. File-1 File-2 File-3 File System Approach

10. Database DBMS Approach

11. Benefits of Database Approach Redundancy can be reduced Inconsistency can be avoided Data can be shared Standards can be enforced Security restrictions can be applied Integrity can be maintained Data independence can be provided Backup and Recovery

12. Describing and storing Data in DBMS • Data Model • A data model is a collection of high-level data description constructs that hide many low-level storage details • Relational Data Model • Semantic Data Model – ER Model

13. Relational Model • A relation is a set of records and attributes. Also known as tuples and columns. • A schema is the description of data in terms of a data model • Eg. Student (RegNo: String, Name: String, Sem: Integer, Branch: String)

14. Student Relation

15. Other Data Models Relational Model – DB2, Oracle, Informix, Sybase, MS-Access, Foxbase, Paradox, etc. Hierarchical Model – IMS DBMS Network Model – IDS & IDMS Object-Oriented Model – ObjectStore & Versant Object-Relational Model – Products from IBM, Oracle, ObjectStore, Versant

16. Three-Layer Abstraction External Schema - 1 External Schema - 2 External Schema - 3 Conceptual Schema Physical Schema Disk

17. Conceptual Schema • Describes the stored data in terms of the data model of the DBMS. This leads to conceptual database design. • Examples: Student:(RegNo: Integer, Name: String, Sem: Integer, Branch: String) Faculty: (Fid: Integer, Fname: String, Salary: Float) Course: (SecID: Integer, CourseNo: Integer, Sem: Integer, Year: Integer, Instructor: String) GradeReport: (RegNo: Integer, SecID: Integer, Grade: Char)

18. Physical Schema Describes the actual storage details of the relations described in conceptual schema. Primary indexing, sequential, binary, secondary indexing, etc. This leads to the physical database design

19. External Schema • Describes several views of the database based on the database model. • Several external schemas are possible for a single database. • Each view is based upon the user requirements. Example: StdGrade: (RegNo: Integer, Name: String, Sem: Integer, Grade: Char)

20. Program-Data Independence • The data independence is the ability to change the schema at one level of a database system with out changing the schema at a higher level. • Logical data Independence It is the ability to change the schema at one level of a database system without changing the external schema or application programs, is called as the logical data independence. With out changing the application programs, one can change the logical schema.

21. Example Suppose the Faculty relation is modified as follows: Faculty_Public(Fid:Integer, FName:String, Office:Integer) Faculty_Private(Fid:Integer, Salary:Float) Any view designed before this modification can still retrieve the data with little modification (relation name) and obtain the same answer.

22. Physical Data Independence There are occasions for changing the internal structures for improved performance of the retrieval of data. Any change introduced to the internal schema or physical schema will not affect the other schemas.

23. Database Languages • DDL – Data Definition Language • SDL – Storage Definition Language • VDL – View Definition Language • DML – Data Manipulation Language (For data manipulations like insertion, deletion, update, retrieval, etc.)

24. Transaction Management Atomic operation – Handling concurrent execution of transactions from several users. Example: Reservation systems, Banking systems. Transaction failures and recovery. Locking protocols. Log (WAL – Write Ahead Log)

25. DBMS Architecture

26. Web Forms Front-End SQL I/f SQL Commands Plan Executor Parser SQL Engine Operator Evaluator Optimizer Tx Manager Files & Access Recovery Manager Buffer Manager Lock Manager DBMS Disk Space Manager Catalog Data File Database

27. THANK YOU FOR YOUR ATTENTION!