Describing and storing data in a DBMS

1. Describing and storing data in a DBMS

2. Describing and storing data in a DBMS • RDBMS stands for RelationalDatabase Management System. • RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. • Table • The data in RDBMS is stored in database objects called tables. • The table is a collection of related data entries and it consists of columns and rows. • Following is the example of a CUSTOMERS table:

3. Describing and storing data in a DBMS • CUSTOMERS table

4. Describing and storing data in a DBMS • Field • Every table is broken up into smaller entities called fields. • The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY. • A field is a column in a table.

5. Describing and storing data in a DBMS • Record • A record, also called a row of data. • Individual entry that exists in a table. • For example there are 7 records in the above CUSTOMERS table. • Following is a single row of data or record in the CUSTOMERS table: • A record is a horizontal entity in a table.

6. Describing and storing data in a DBMS • column • A column is a vertical entity in a table. • For example, a column in the CUSTOMERS table is ADDRESS, which represents location description and would consist of the following:

7. Describing and storing data in a DBMS • NULL • A NULL value in a table is a value in a field that appears to be blank, • which means a field with a NULL value is a field with no value. • NULL value is different than a zero value or a field that contains spaces. • A field with a NULL value is one that has been left blank during record creation.

8. Describing and storing data in a DBMS • Levels of Abstraction in a DBMS • Data Abstraction • For efficient retrieval of complex data, database need a good data structures to represent data . • Developers hide the complexity from users through several levels of abstraction, to simplify users’ interactions with the system: • Physical Level :  • Logical Level : • View Level :

9. Describing and storing data in a DBMS • Levels of Abstraction in a DBMS • Data Abstraction • Physical Level : ( The lowest level )  • Logical Level : (The next-higher level ) • View Level : (The highest level)

10. Describing and storing data in a DBMS • Data Abstraction • Physical Level : The lowest level of abstraction describes how the data are actually stored. • The physical level describes complex low-level data structures in detail.

11. Describing and storing data in a DBMS • Data Abstraction • Logical (Conceptual )Level : The next-higher level of abstraction describes what data are stored in the database. • And what relationships exist among those data. • The logical level describes the entire database in terms of simple structures. • The user of the logical level does not need to be aware of this complexity. • Database administrators, who must decide what information to keep in the database, use the logical level of abstraction.

12. Describing and storing data in a DBMS • Data Abstraction • Conceptual Schema • The conceptual schema (logical schema) describes the stored data in terms of the data model of the DBMS. • In a relational DBMS, the conceptual schema describes all relations that are stored in the database. • Eg • Students(sid: int, name: char(20), age: int)

13. Describing and storing data in a DBMS • Data Abstraction • View (External)Level : The highest level of abstraction describes only part of the entire database. • Many users of the database system do not need all this information; instead, they need to access only a part of the database. • The view level of abstraction exists to simplify users interaction with the system. • The system may provide many views for the same database.

14. Describing and storing data in a DBMS • Data Abstraction • External Schema • Allow data access to be customized at the level of individual users or groups of users. • Each external schema consists of a collection of one or more views and relations from the conceptual schema.

15. Describing and storing data in a DBMS • Data Independence • Physical data independence • Logical data independence

16. Describing and storing data in a DBMS • Data Independence • Physical data independence • The capacity to change the internal schema without change the conceptual (or external) schema • Internal schema may change to improve the performance • (e.g., creating additional access structure)

17. Describing and storing data in a DBMS • Data Independence • Logical data independence • The capacity to change the conceptual schema without change external schema or application progms • ex: Employee (E#, Name, Address, Salary) • A view including only E# and Name is not affected by changes in any other attributes.

18. Describing and storing data in a DBMS • QUERIES IN A DBMS • In order to obtain information from a database a DBMS provides a specialized language, called the query language • Relational calculus is a formal query language based on mathematical logic. • SQL

19. Describing and storing data in a DBMS • TRANSACTION MANAGEMENT • A transaction is anyone execution of a user program in a DBMS. (Executing the same program several times will generate several transactions.) • Partial transactions are not allowed, and the effect of a group of transactions is equivalent to some serial execution of all transactions. • Transactions provide concurrency control • Transactions support crash recovery

20. Describing and storing data in a DBMS • TRANSACTION MANAGEMENT • Four important properties (ACID): • Atomic: Database ensures that all actions are carried out, or none • Consistency: Users ensure that transactions leave the data in a consistent state • Isolation: Users to not have to worry about concurrently executing transactions • Durability:Completed transactions persist after a crash even if the database has not been updated

21. Describing and storing data in a DBMS • TRANSACTION MANAGEMENT • Concurrent Execution of Transactions • DBMS is to schedule concurrent accesses to data so that each user can safely ignore the fact that others are accessing the data concurrently.

22. Describing and storing data in a DBMS • TRANSACTION MANAGEMENT • Concurrent Execution of Transactions • A locking protocol is a set of rules to be followed by each concurrent transaction to ensure that, the transaction are in serial order. • A lock is a mechanism used to control access to database objects. • Two kinds of locks are commonly supported by a DBMS: • Shared locks :-for two different transactions at the same time. • Exclusive lock :- for ensures that no other transactions hold any lock on this object.

23. Describing and storing data in a DBMS • STRUCTURE OF A DBMS • The DBMS accepts SQL commands and returns the answers. • SQL commands can be embedded in host-language application programs, e.g., Java or PHP programs. • When a user issues a query, the query is passed to a query optimizer. • It produces efficient execution plan about the stored data.

24. Describing and storing data in a DBMS • STRUCTURE OF A DBMS • An execution plan is a blueprint for evaluating a query, usually represented as a tree of relational operators • Which bring the needed data to the main memory

25. Describing and storing data in a DBMS • STRUCTURE OF A DBMS • The lowest layer of the DBMS software deals with management of space ondisk, where the data is stored. • Higher layers allocate, deal locate, read, and write pages through this layer, called the disk space manager. • The DBMS maintain a log file, information about user, changes to the database… • DBMS controls the concurrency control and crashrecovery in the transactions

26. Describing and storing data in a DBMS • STRUCTURE OF A DBMS • Transactions request and release locks according to a suitable locking protocol • The lock manager, which keeps track of requests for locks and grants • The recovery manager, which is responsible for maintaining a log and restoring the system to a consistent state after a crash.

28. People who work with Databases • The people who work with databases include  • database users, • system analysts, • application programmers, and  • database administrator (DBA).

29. People who work with Databases • Database users are those who interact with the database in order to query and update the database, and generate reports. • Database users are further classified into • Naive users: • Sophisticated users: • Specialized users:

30. People who work with Databases • Naive users: The users who maintain the database by invoking some already written application programs. • For example, super market s/w user. • Sophisticated users: The users, who are interact DBMS without writing any application programs. • For example writing SQL • Specialized users: The users who write specialized database programs, • Specialized users write applications such as computer-aided design systems, knowledge-base, expert systems that store data having complex data types.

31. People who work with Databases • System analysts determine the requirements of the database users (especially naive users) to create a solution for their business need, and focus on non-technical and technical aspects. • The non-technical aspects involve defining system requirements, interaction between business users and technical staff, etc. • Technical aspects involve developing the specification for user interface (application programs).

32. People who work with Databases • Application programmers • are the computer professionals • who implement the specifications given by the system analysts, • and develop application programs. • For the native users

33. People who work with Databases • Database administrator (DBA) is a person who has central control over both data and application programs. • Some of the responsibilities of DBA are • Design of the Logical and Physical Schemas: • The overall structure of the database is known as database schema. • Data base schema is maintained by DDL.

34. People who work with Databases • Security and Authorization: • DBA is responsible for monitoring the security of the database system. • It involves adding and removing users, auditing, and checking for security problems. • New software installation: It is the responsibility of the DBA to install new DBMS software, application software, and other related software. • After installation, the DBA must test the new software.

35. People who work with Databases • Data analysis: DBA is responsible for analyzing the data stored in the database, and studying its performance and efficiency in order to effectively use indexes, parallel query execution, etc. • Routine maintenance checks: The DBA is responsible for taking the database backup periodically in order to recover from any hardware or software failure (if occurs). Other routine maintenance checks that are carried out by the DBA are checking data storage and ensuring the availability of free disk space for normal operations, upgrading disk space as and when required, etc.

36. People who work with Databases • Data Availability and Recovery from Failures:

37. People who work with Databases