Database Management System

1. Database Management System Lecture 2 Introduction to Database management

2. Learning Outcomes • Understanding the definition of a Database management System (DBMS) • Grasping an outline of DBMS architectures • Understanding DBMS’s main functionalities • Get introduced to a number of commercial DBMSs which are widely used.

3. List of Topic • DBMS definition • DBMS functionalities • DBMS Examples

4. Terminology

5. Terminology

6. DBMS definition • DBMS is a collection of software for creating, managing and effectively searching a database. DBMS Role Illustration

7. DBMS definition (con.) • In addition a DBMS is responsible for managing application requests to a database

8. DBMS Three-Schema Architecture

9. DBMS Three-Schema Architecture (con.) • The architecture ensures: • the independence of logical and physical data • allowing the manipulation of data • ensure data integrity • optimize data access.

10. DBMS Three-Schema Architecture (con.) 1- External layer or External Schema • It covers all the possibilities of access to data by different users. • Access of different user groups can be done via different applications.

11. DBMS Three-Schema Architecture (con.) 2- Each external schema describes • the part of the database that a particular user is interested in • hides the rest of the database from user.

12. DBMS Three-Schema Architecture (con.) 2- Logical Layer or Logical Schema • It is the general view of the data independent of individual applications and how data is stored. • It hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints.

13. DBMS Three-Schema Architecture (con.) 3- Internal layer or Internal Schema • Describes the physical storage structure of the database. • The internal schema describes the complete details of data storage and access paths for the database.

14. DBMS Functionalities 1- Support the concepts defined in the data model in order to represent data properties. • It usually includes the definition of specific types and the definition of consistency rules.

15. DBMS Functionalities (con.) 2- Make transparent data sharing between different users. • This means that multiple users should be able to use the database concurrently and transparently

16. DBMS Functionalities (con.) 3- Ensure confidentiality of data • to specify who has the right to access or modify all or part of a database. • first requires a user identification • second requires a specification of privileges (add, delete, update).

17. DBMS Functionalities (con.) 4- Ensure compliance with the consistency rules defined on the data • after each modification on the database, all rules of consistency must be checked on all data

18. DBMS Functionalities (con.) 5- Being resistant to failure. • this is to protect data against hardware failure or software whether intentional or accidental.

19. DBMS Functionalities (con.) 6- Possess a high storage capacity • Thus enabling data management up to several billion bytes • The storage units are moved from the megabyte (106) to gigabyte (109), then to terabyte (1012), peta-byte (1016), and we start talking about exabytes (1018) even zettabyte (1021).

20. DBMS Functionalities (con.) 7- To answer queries with a level of acceptable performance • A query requires searching data in a database based on some conditions • Computer power is not the only possible answer to the problems of performance • Query optimization can be very effective to ensure

21. Database Examples • Is widely used due to its ease of integration with the Apache/PHP and its good response time. • MySQL supports only part of the standard SQL-92.

22. Database Examples (con.) • MySQL: • It does not support native transactions, subqueries, triggers and stored procedures. • Lacks some features compared to other DBMSs

23. Database Examples (con.) • Oracle • is a RDBMS which supports remote access to the database hosted in it. • It is one of the best performing DBMS with powerful tools. • Oracle is a RDBMS-oriented environments with heavy constraints (availability, integrity, stability, large volumes of data and high number of users, etc..).

24. Database Examples (con.) • PostgreSQL • PostgreSQL is usable under Linux. • Is an excellent choice to manage databases of medium size. • It is free.

25. Database Examples (con.) • Borland Interbase • One of its main strengths is its availability in an open source version • A good choice to manage databases for small and medium size

26. Database Examples (con.) • Microsoft SQL Server • SQL Server is a good choice for databases of medium size

27. Database Examples (con.) • Access • Developed by Microsoft • Suitable for supporting small databases (personal).

28. Summary In this lesson, the following has been discussed: • The definition of DBMS and its role. • The DBMS architecture based on the three-schema architecture. • The main features that a robust and reliable DBMS must have. • An outline of the most widely used DBMSs.

29. Exercises • What are the three layers constituting a DBMS architecture ? • Which layer in DBMS architecture interacts with end users or user applications ? • List the most important features which must belong to a DBMS? • List four widely used DBMSs?

30. Self-assessment 1 Search the web and find a good reference for the SQL language. Identify the SQL command that could be used to: • Create a table. • Insert a record in a table. • Modify a data field in a table record.