Database Management System PDF

Database Management System Class : - TY CSE – I Dr. Deepa Deshpande  View of Data An architecture for a database system Department of Computer Science & Engineering 8/5/2020 2  Three Level Architecture U U...

Database Management System Class : - TY CSE – I Dr. Deepa Deshpande  View of Data An architecture for a database system Department of Computer Science & Engineering 8/5/2020 2  Three Level Architecture U U U se se se r r r 1 2 3 Extern Extern al al External Level View 1 View 2 External/Conceptual Mappings D Conceptu B al Conceptual/Logical Level A View Conceptual/Internal Mapping Stored Data Internal/Physical Level Department of Computer Science & Engineering 8/5/2020 3  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/5/2020 4  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/5/2020 5  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/5/2020 6 Example of Schema Department of Computer Science & Engineering 8/5/2020 7  Example of Schema View 1 View 2 Employee : Name External Level Employee : SSN Employee : Address Employee : Name Employee Employee :Address Name string Employee : Salary SSN number Address string Conceptual/Logical Level Salary number Attribute Length Name 0-24 SSN 25-26 Address 27-76 Internal/Physical Level Salary 77-78 Department of Computer Science & Engineering 8/5/2020 8  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/5/2020 9  Mapping 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/5/2020 10  Mapping (contd.) User specifies a request to generate new external view DBMS must transform request specified at external level into request at conceptual level And then into physical level This process of transforming the requests & results between various levels of DBMS architecture is known as mapping Department of Computer Science & Engineering 8/5/2020 11  Instances & Schema Schema ◦ is a description of database structure ◦ logical structure of database Instance : ◦ is set of data that fits in description ◦ actual contents of database at a particular point of time ◦ also called as database state or occurrence One schema may have many instances Difference between schema & state ◦ At design time schema is defined and state is empty state ◦ State changes each time data is inserted or updated, schema remains the same Department of Computer Science & Engineering 8/5/2020 12  Examples Roll Number Name Contact Instance at time T1 506101 Deepa Deshpande 9822475431 Roll Number Name Contact Instance at time T2 506101 Deepa Deshpande 9822475431 506102 aaaaa 9833442261 506103 bbbbb 8687543216 Roll Number Name Contact Instance at time T3 506101 Deepa Deshpande 9822475431 506102 aaaaa 9833442261 506105 cccccc 8688985654 Department of Computer Science & Engineering 8/5/2020 13 Database Schema Department of Computer Science & Engineering 8/5/2020 14  Database Schema Bad Design Student_ Student_ Student Course_i Course_ Marks id Name _Address d name Good Design Student_ Student_ Student id Name _Address Course_i Course_n Student_i Course_ Marks d ame d id Department of Computer Science & Engineering 8/5/2020 15  DBA : Database Administrator DBA is a person who has central control over both data & application programs Schema definition & modification Overall structure of database is known as database schema. It is responsibility of DBA to create the database schema Security enforcement & administration Adding removing users, auditing & checking for security problems Data Analysis Routine maintenance checks Periodically take backups in order to be able to recover from any H/W or S/W failure Department of Computer Science & Engineering 8/5/2020 16  Summary What is DBMS? Database Management System Why Database? File processing System Vs DBMS Examples of Database Applications Major components of Database System Data, Hardware, Software, Users Types of Users Application Programmers, End Users, Database Programmers, DBA Functions of DBMS DDL,DML, Data Security & Integrity, Data Recovery & Concurrency, Data Dictionary, Performance Department of Computer Science & Engineering 8/5/2020 17  Summary(cont.) Architecture for a database system External /view level Conceptual /logical level Internal/Physical level Mapping Data independence Logical data independence Physical data independence Data Abstraction Schema Vs Instance Role of DBA Department of Computer Science & Engineering 8/5/2020 18 Thank You Department of Computer Science & Engineering 8/5/2020 19