DBMS architecture &; system structure

DBMS architecture &; system structure

1. D BMS Architecture & System structure of DBMS Asst.Prof . Rupali Lohar Dept. of Computer Science & Engineering B. R. Harne College Of Engineering & Technology,   Karav , Post Vangani (W Tal Ambernath , Mumbai, Maharashtra 421503

2. Database Users Naive users- who interact with the system by invoking one of the application programs Application programmers-computer professionals who write application programs. Application programmers . Sophisticated users -interact with the system without writing programs. Instead, they form their requests in a database query language. Specialized users- who write specialized database applications that do not fit into the traditional data-processing framework . database administrator (DBA)- A person who has such central control over the system .

3. Levels of abstraction in a DBMS Physical Level- “How” the data are actually stored. Logical Level- what relationships exist among those data View Level- highest level of data Abstracts

4. DBMS Architecture

5. 1-Tier Architecture User Interface The 1-Tier architecture is used for development of the local application, where programmers can directly communicate with the database for the quick response.

6. 2-Tier Architecture client-server architecture client can directly communicate with the database For this interaction, API's like: ODBC(open database connectivity)

7. 3-Tier Architecture used web application . Client/External Level/View Level Web Server/Conceptual Level/Logical level Database Server/Internal Level/Physical Level

8. External level Highest or Top level of data abstraction ( No knowledge of DBMS S/W and H/W or physical storage). This level is concerned with the user. Each external schema describes the part of the database that a particular user is interested in and hides the rest of the database from user. There can be n number of external views for database where n is the number of users. For example, a accounts department may only be interested in the student fee details. It would not be expected to have any interest in the personal information about students. All database users work on external level of Dbms .

9. Conceptual Level This level is in between the user level and physical storage view. There is only one conceptual view for single database. It hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints.

10. Internal Level It is the lowest level of data abstraction. (it has the knowledge about s/w and h/w) At this level, it keeps the information about the actual representation of the entire database i.e. the actual storage of the data on the disk in the form of records or blocks. It is close to the physical storage method. The internal view is the view that tells us what data is stored in the database and how. At least the following aspects are considered at this level: Storage allocation, Access paths etc. The internal view does not deal with the physical devices directly. Instead it views a physical device as a collection of physical pages and allocates space in terms of logical pages.

11. Level of DBMS Architecture :

12. Example: University Database Course_info(cid:string,cname:string) Students(sid: string name: string, login: string, age: integer) Courses(cid: string, cname:string, credits:integer) Enrolled(Eid:string, cid:string, gr a d e :s t ri n g ) Relations stored as unordered files. Index on first column of Students. Conceptual schema: Physical schema: student_info(cid:string, name:string) External Schema (View 1 ): External Schema (View 2):

13. External view 2 (COBOL) 01 EMPC. 02 EMPNO PIC X(6). 02 DEPTNO PIC X(4). Internal STORED_EMP BYTES=20 PREFIX EMP# DEPT# PAY BYTE=6 , OFFSET=0 BYTE=6, OFFSET=6, INDEX=EMPX BYTES=4, OFFSET=12 BYTES= 4, ALIGN= FULLWORD,OFFSET=16 External view 1 (C++) DCL 1 EMPP, 2 EMP# CHAR(6) 2 SAL FIXEDBIN(31) Example: employee database Conceptual EMPLOYEE EMPLOYEE_NUMBER CHARACTER(6) DEPARTMENT_NUMBER CHARACTER(6) SALARY DECIMAL(5)

14. Three Levels of Architecture B_no E_no F_name L_name Age Salary E m pl_No F_name L_name DOB Salary Branch_No Conceptual level Internal level struct EMPLOYEE { int Empl_No; int Branch_No; char F_name [15]; char L_name [15]; struct date Date_of_Birth; float Salary; struct EMPLOYEE *next; //pointer to next employee record }; index Empl_No; index Branch_No; //define indexes for employees Syntax Example: External view 1 External view 2 E m pl_No L_ n a m e

15. Data independence Data independence is the capacity to change the schema at one level without having to change the schema at the next higher level Two types of data independence are Logical data independence Physical data independence

16. Logical data independence Whenever, there is a change or modification at the conceptual level without affecting the user level or external level, it is known as logical data independence. E.g . The name field in conceptual view is stored as first name, middle name and last name whereas in external view, it remains to be as a single name field.

17. Physical data independence Whenever, the changes are made at the internal level without affecting the above layers, it is known as physical data independence. E.g . The location of the database, if changed from C drive to D drive will not affect the conceptual view or external view as the commands are independent of the location of the database.

18. Database users and user interfaces Naive Users Application Programmers Sophisticated Users Specialized Users DBA

19. Responsibilities of DBA Installation, configuration and upgrading of Database server software and related products. Evaluate Database features and Database related products. Establish and maintain sound backup and recovery policies and procedures. Take care of the Database design and implementation. Implement and maintain database security (create and maintain users and roles, assign privileges). Database tuning and performance monitoring. Application tuning and performance monitoring. Setup and maintain documentation and standards. Plan growth and changes (capacity planning). Work as part of a team and provide 24x7 support when required. Do general technical troubleshooting and give cons. Database recovery.

20. Storage manager The storage manager is important because databases typically require a large amount of storage space. Storage Manager Components: Authorization and integrity manager which tests for the satisfaction of integrity constraints and checks the authority of users to access data. Transaction manager which ensures that the database itself remains in a consistent state despite system failures, and that concurrent transaction executions proceed without conflicting.

21. File manager: which manages the allocation of space on disk storage and the data structures used to represent information stored on disk. Buffer manager which is responsible for fetching data from disk storage into main memory. Storage manager implements several data structures as part of the physical system implementation. Data files are used to store the database itself. Data dictionary is used to stores metadata about the structure of the database, in particular the schema of the database.

22. Query processor The query processor also very important because it helps the database system simplify and facilitate access to data. So quick processing of updates and queries is important.

23. Query Processor Components: DDL interpreter: It interprets DDL statements and records the definitions in the data dictionary. DML compiler: It translates DML statements in a query language into an evaluation plan consisting of low-level instructions that the query evaluation engine understands. Query evaluation engine: It executes low-level instructions generated by the DML compiler.

24. DBMS Functions- Data Dictionary Management Data Storage Management Data transformation and Presentation Security Management Multiple Access Control Backup and Recovery Management Data Integrity Management Database Access Languages Databases Communication Interfaces

25. Database Administrator(DBA) One of the main reasons for using DBMSs is to have central control of both the data and the programs that access those data. A person who has such central control over the system is called a database administrator (DBA).

26. Responsibilities of DBA Software installation and Maintenance Data Extraction, Transformation, and Loading Database Backup and Recovery Performance Monitoring Security authentication

27. THANK YOU