Unit 1.2.pptx

Database Management System Class : - TY CSE – I Dr. Deepa Deshpande Database Management System (DBMS) Collection of interrelated data Set of programs to access the data DBMS contains information about a particular enterprise DBMS provides an environment that is b...

Database Management System Class : - TY CSE – I Dr. Deepa Deshpande Database Management System (DBMS) Collection of interrelated data Set of programs to access the data DBMS contains information about a particular enterprise DBMS provides an environment that is both convenient and efficient to use. Database Applications: ◦ Banking: all transactions ◦ Airlines: reservations, schedules ◦ Universities: registration, grades ◦ Sales: customers, products, purchases ◦ Manufacturing: production, inventory, orders, supply chain ◦ Human resources: employee records, salaries, tax deductions Databases touch all aspects of our lives Department of Computer Science & Engineering 8/4/2020 2  Purpose of Database System In the early days, database applications were built on top of file systems Drawbacks of using file systems to store data: ◦ Data redundancy and inconsistency Multiple file formats, duplication of information in different files ◦ Difficulty in accessing data Need to write a new program to carry out each new task ◦ Data isolation — multiple files and formats ◦ Integrity problems Integrity constraints (e.g. account balance > 0) become part of program code Hard to add new constraints or change existing ones Department of Computer Science & Engineering 8/4/2020 3 Purpose of Database Systems (Cont.) Drawbacks of using file systems (cont.) ◦ Atomicity of updates Failures may leave database in an inconsistent state with partial updates carried out E.g. transfer of funds from one account to another should either complete or not happen at all ◦ Concurrent access by multiple users Concurrent accessed needed for performance Uncontrolled concurrent accesses can lead to inconsistencies E.g. two people reading a balance and updating it at the same time ◦ Security problems Database systems offer solutions to all the above problems Department of Computer Science & Engineering 8/4/2020 4  Examples of Databases Web indexes Library catalogues Medical records Bank accounts Stock control Payroll systems Product catalogues Telephone directories Train timetables Airline bookings Credit card details Student records Customer histories Stock market prices Discussion boards Department of Computer Science & Engineering 8/4/2020 5  Database Management Systems Database Management systems allow users to ◦ Store ◦ Update ◦ Retrieve ◦ Organise ◦ Protect their data. Examples: ◦ Oracle ◦ DB2 (IBM) ◦ MS SQL Server ◦ MS Access ◦ Ingres ◦ PostgreSQL ◦ MySQL Department of Computer Science & Engineering 8/4/2020 6  Major components of a database system Data: integrated and shared. Hardware: disk, CPU, Main Memory,... Software: DBMS Users: 1. Application programmers-Write software to allow end users to interface with the database system 2. End users -Use the database system to achieve some goal 3. Database systems programmer - Writes the database software itself 4. Database administrator (DBA) - Designs & manages the database system Defining external schema Defining conceptual schema Defining internal schema Liaison with users Defining security and integrity checks Defining backup and recovery procedures Monitoring performance and changing requirements Department of Computer Science & Engineering 8/4/2020 7  Functions of the DBMS Data Definition Language (DDL) Data Manipulation Language (DML) Data Security and Integrity Data Recovery and Concurrency Data Dictionary Performance Department of Computer Science & Engineering 8/4/2020 8  View of Data An architecture for a database system Department of Computer Science & Engineering 8/4/2020 9  Three Level Architecture User 1 User 2 User 3 Extern Extern al al External Level View 1 View 2 External/Conceptual Mappings Concept DBA ual Conceptual/Logical Level View Conceptual/Internal Mapping Stored Data Internal/Physical Level Department of Computer Science & Engineering 8/4/2020 10  Internal/Physical Level It is lowest level of data abstraction that deals with physical representation of the database on the computer and thus is also known as physical level. Deals with physical storage of data ◦ Structure of records on disk - files, pages, blocks ◦ Indexes and ordering of records ◦ Used by database system programmers Internal Schema RECORD EMP LENGTH=44 HEADER: BYTE(5) OFFSET=0 NAME: BYTE(25) OFFSET=5 SALARY: FULLWORD OFFSET=30 DEPT: BYTE(10) OFFSET=34 Department of Computer Science & Engineering 8/4/2020 11  Conceptual/Logical Level This level of abstraction deals with logical structure of entire database and thus also known as logical level. Deals with the organisation of the data as a whole It describes what data are stored in the database ,without any concern for the physical implementation Used by DBAs and application programmers Conceptual Schema CREATE TABLE Employee ( Name VARCHAR(25), Salary REAL,Dept_Name VARCHAR(10)) Department of Computer Science & Engineering 8/4/2020 12  External/ View Level It is highest level of abstraction that deals with user’s view & thus also known as view level Provides a view of the database tailored to a user ◦ Parts of the data may be hidden ◦ Data is presented in a useful form ◦ Used by end users and application programmers Department of Computer Science & Engineering 8/4/2020 13  Mappings Mappings translate information from one level to the next External/Conceptual Conceptual/Internal These mappings provide data independence Physical data independence Changes to internal level shouldn’t affect conceptual level It is ability to change the internal schema without affecting the conceptual schema and external schema Logical data independence Conceptual level changes shouldn’t affect external levels It is ability to change the conceptual schema without affecting the external schema and application programs Department of Computer Science & Engineering 8/4/2020 14  Data Abstraction In general, the interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others Database users are provided with an abstract view of the data by hiding certain details of how data are physically stored. This enables users to manipulate the data without worrying about where they are located or how they are actually stored. Department of Computer Science & Engineering 8/4/2020 15 Thank You Department of Computer Science & Engineering 8/4/2020 16