Download Dbms effective notes for xam and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!
UNIT - I
INTRODUCTION TO DBMS:
What is data?
- Data is nothing but facts and statistics stored or free flowing over a network, generally it's raw and unprocessed.
- Data becomes information when it is processed, turning it into something meaningful.
- What is database: The database is a collection of inter-related data which is used to retrieve, insert and delete the data efficiently.
- It is also used to organize the data in the form of a table, schema, views, and reports, etc.
- Using the database, you can easily retrieve, insert, and delete the information.
- For example: The college Database organizes the data about the admin, staff, students and faculty etc. What is dbms? File System DBMS DBMS is a collection of data. In DBMS, the user is not required to write the procedures. File system is a collection of data. In this system, the user has to write the procedures for managing the database. Searching data is easy in Dbms Searching is difficult in File System Dbms is structured data Files are unstructured data No data redundancy in Dbms Data redundancy is there in file system
Memory utilisation well in dbms Memory utilisation poor in file system No data inconsistency in dbms Inconsistency in file system DBMS gives an abstract view of data that hides the details. File system provides the detail of the data representation and storage of data. DBMS provides a crash recovery mechanism, i.e., DBMS protects the user from the system failure. File system doesn't have a crash mechanism, i.e., if the system crashes while entering some data, then the content of the file will lost. DBMS provides a good protection mechanism. It is very difficult to protect a file under the file system. DBMS contains a wide variety of sophisticated techniques to store and retrieve the data. File system can't efficiently store and retrieve the data. DBMS takes care of Concurrent access of data using some form of locking. In the File system, concurrent access has many problems like redirecting the file while other deleting some information or updating some information.
- A DBMS is software that allows creation, definition and manipulation of database, allowing users to store, process and analyse data easily.
- DBMS provides us with an interface or a tool, to perform various operations like creating database, storing data in it, updating data, creating tables in the database and a lot more.
- DBMS also provides protection and security to the databases.
- It also maintains data consistency in case of multiple users. Here are some examples of popular DBMS used these days:
- MySql
- Oracle
- SQL Server
- IBM DB
- Support Multiple user and Concurrent Access: DBMS allows multiple users to work on it(update, insert, delete data) at the same time and still manages to maintain the data consistency.
- Query Language: DBMS provides users with a simple Query language, using which data can be easily fetched, inserted, deleted and updated in a database. Advantages of DBMS
- Controls database redundancy: It can control data redundancy because it stores all the data in one single database file and that recorded data is placed in the database.
- Data sharing: In DBMS, the authorized users of an organization can share the data among multiple users.
- Easily Maintenance: It can be easily maintainable due to the centralized nature of the database system.
- Reduce time: It reduces development time and maintenance need.
- Backup: It provides backup and recovery subsystems which create automatic backup of data from hardware and software failures and restores the data if required.
- multiple user interface: It provides different types of user interfaces like graphical user interfaces, application program interfaces Disadvantages of DBMS
- Cost of Hardware and Software: It requires a high speed of data processor and large memory size to run DBMS software.
- Size: It occupies a large space of disks and large memory to run them efficiently.
- Complexity: Database system creates additional complexity and requirements.
- Higher impact of failure: Failure is highly impacted the database because in most of the organization, all the data stored in a single database and if the database is damaged due to electric failure or database corruption then the data may be lost forever. View of Data in DBMS
- Abstraction is one of the main features of database systems.
- Hiding irrelevant details from user and providing abstract view of data to users, helps in easy and efficient user-database interaction.
- the three level of DBMS architecture, The top level of that architecture is “view level”. The view level provides the “view of data” to the users and hides the irrelevant
- The design of a database at physical level is called physical schema, how the data stored in blocks of storage is described at this level.
- Design of database at logical level is called logical schema, programmers and database administrators work at this level, at this level data can be described as certain types of data records gets stored in data structures, however the internal details such as implementation of data structure is hidden at this level (available at physical level).
- Design of database at view level is called view schema. This generally describes end user interaction with database systems. Definition of instance : The data stored in database at a particular moment of time is called instance of database. Database schema defines the variable declarations in tables that belong to a particular database; the value of these variables at a moment of time is called the instance of that database. DBMS ARCHITECTURE:
- Database management systems architecture will help us understand the components of database system and the relation among them.
- The architecture of DBMS depends on the computer system on which it runs.
- the basic client/server architecture is used to deal with a large number of PCs, web servers, database servers and other components that are connected with networks.
- The client/server architecture consists of many PCs and a workstation which are connected via the network.
- DBMS architecture depends upon how users are connected to the database to get their request done. TYPES OF DBMS ARCHITECTURE There are three types of DBMS architecture:
- Single tier architecture
- Two tier architecture 3.Three tier architecture
1 - Tier Architecture
- In this type of architecture, the database is readily available on the client machine, any request made by client doesn’t require a network connection to perform the action on the database.
- Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for end users.
- The 1-Tier architecture is used for development of the local application, where programmers can directly communicate with the database for the quick response. 2. Two tier architecture
- In two-tier architecture, the Database system is present at the server machine and the DBMS application is present at the client machine, these two machines are connected with each other through a reliable network.
- Whenever client machine makes a request to access the database present at server using a query language like sql, the server perform the request on the database and returns the result back to the client.
- The application connection interface such as JDBC, ODBC are used for the interaction between server and client. 3 - Tier Architecture
- In three-tier architecture, another layer is present between the client machine and server machine.
- In this architecture, the client application doesn’t communicate directly with the database systems present at the server machine, rather the client application
Network Model The network database model allows each child to have multiple parents. It helps you to address the need to model more complex relationships like as the orders/parts many-to-many relationship. In this model, entities are organized in a graph which can be accessed through several paths. Relational model Relational DBMS is the most widely used DBMS model because it is one of the easiest. This model is based on normalizing data in the rows and columns of the tables. Relational model stored in fixed structures and manipulated using SQL. Entity-Relationship Model Entity-Relationship (ER) Model is based on the notion of real-world entities and relationships among them. While formulating real-world scenario into the database model, the ER Model creates entity set, relationship set, general attributes and constraints. DBMS languages
Database languages are used to read, update and store data in a database. There are several such languages that can be used for this purpose; one of them is SQL (Structured Query Language).
- DDL – Data Definition Language: (CREATE,DROP,ALTER,TRUNCATE,COMMENT,RENAME)
- DML – Data Manipulation Language: (INSERT, UPDATE,DELETE)
- DCL – Data Control Language: (GRANT,REVOKE)
- TCL-Transaction Control Language: (COMMIT,ROLLBACK) 1. DDL(Data Definition Language) : DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. CREATE – it is used to create the database or its objects (like table, index, function, views, store procedure and triggers). There are two CREATE statements available in SQL:
- CREATE DATABASE
- CREATE TABLE CREATE DATABASE A Database is defined as a structured set of data. So, in SQL the very first step to store the data in a well structured manner is to create a database. The CREATE DATABASE statement is used to create a new database in SQL. Syntax: CREATE DATABASE database_name; Example: SQL> CREATE DATABASE Employee;
Syntax: DROP object object_name; Examples: DROP TABLE table_name; table_name: Name of the table to be deleted. DROP DATABASE database_name; database_name: Name of the database to be deleted. TRUNCATE It is used to remove all records from a table, including all spaces The TRUNCATE TABLE mytable statement is logically (though not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause). Syntax: TRUNCATE TABLE table_name; DROP vs TRUNCATE
- Truncate is normally ultra-fast and its ideal for deleting data from a temporary table.
- Truncate preserves the structure of the table for future use, unlike drop table where the table is deleted with its full structure.
- Table or Database deletion using DROP statement cannot be rolled back, so it must be used wisely. To delete the whole database DROP DATABASE student_data; After running the above query whole database will be deleted. To truncate Student_details table from student_data database. TRUNCATE TABLE Student_details; ALTER (ADD, DROP, MODIFY) ALTER TABLE is used to add, delete/drop or modify columns in the existing table. It is also used to add and drop various constraints on the existing table. ALTER TABLE – ADD: ADD is used to add columns into the existing table. Sometimes we may require to add additional information, in that case we do not require to create the whole database again, ADD comes to our rescue. Syntax: ALTER TABLE table_name
ADD (Columnname_1 datatype, Columnname_2 datatype, … Columnname_n datatype); ALTER TABLE – DROP DROP COLUMN is used to drop column in a table. Deleting the unwanted columns from the table. Syntax: ALTER TABLE table_name DROP COLUMN column_name; ALTER TABLE-MODIFY It is used to modify the existing columns in a table. Multiple columns can also be modified at once. ALTER TABLE table_name MODIFY column_name column_type; QUERY: To ADD 2 columns AGE and COURSE to table Student. ALTER TABLE Student ADD (AGE number(3),COURSE varchar(40)); MODIFY column COURSE in table Student ALTER TABLE Student MODIFY COURSE varchar(20); Comments As is any programming languages comments matter a lot in SQL also. In this set we will learn about writing comments in any SQL snippet. Comments can be written in the following three formats:
- Single line comments.
- Multi line comments
- In line comments DML(Data Manipulation Language) : The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements. SELECT Statement select statement is used to fetch data from relational database. A relational database is organized collection of data. As we know that data is stored inside tables in a database. SQL select statement or SQL select query is used to fetch data from one or more than one tables.
INSERT INTO Student (ROLL_NO, NAME, Age) VALUES (‘5′,’PRATIK’,’19’); UPDATE Statement The UPDATE statement in SQL is used to update the data of an existing table in database. We can update single columns as well as multiple columns using UPDATE statement as per our requirement. Basic Syntax: UPDATE TableName SET column_name1 = value, column_name2 = value.... WHERE condition; EX1: SQL> UPDATE EMPLOYEES SET EMP_SALARY = 10000 WHERE EMP_AGE > 25; EX2; SQL> UPDATE EMPLOYEES SET EMP_SALARY = 120000 WHERE EMP_NAME = 'Apoorv'; DELETE Statement The DELETE Statement in SQL is used to delete existing records from a table. We can delete a single record or multiple records depending on the condition we specify in the WHERE clause. Basic Syntax: DELETE FROM table_name WHERE some_condition; Deleting single record: Delete the rows where NAME = ‘Ram’. This will delete only the first row. DELETE FROM Student WHERE NAME = 'Ram'; Deleting multiple records: Delete the rows from the table Student where Age is 20. This will delete 2 rows(third row and fifth row). DELETE FROM Student WHERE Age = 20; Delete all of the records: There are two queries to do this as shown below, query1: "DELETE FROM Student"; query2: "DELETE * FROM Student"; DCL(Data Control Language) : DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system. Examples of DCL commands:
GRANT-gives user’s access privileges to database. REVOKE-withdraw user’s access privileges given by using the GRANT command. TCL(transaction Control Language ) : TCL commands deals with the transaction within the database. Examples of TCL commands: COMMIT– commits a Transaction. ROLLBACK– rollbacks a transaction in case of any error occurs. SAVEPOINT–sets a savepoint within a transaction. SET TRANSACTION–specify characteristics for the transaction. What is the Procedure for Database Access?
- Any access to the stored data is done by the data manager. A user’s request for data is- received by the data manager, which detern1ines the physical record required. The decision as 10 which physical record is needed may require some preliminary consultation of the database and/or the data dictionary prior to the access of the actual data itself.
- The data manager sends the request for a specific physical record to the file manager. The file manager decides which physical block of secondary storage devices contains the required record and sends the request for the appropriate block to the disk manager. A block is a unit of physical input/output operations between primary and secondary storage. The disk manager retrieves the block and sends it to the file manager, which sends the required record to the data manager. DATA BASE USERS AND ADMINISTRATORS: Database users are the persons who interact with the database and take the benefits of database.
- Monitoring performance Routine Maintenance Transaction Management?
- A Database Transaction is a logical unit of processing in a DBMS which entails one or more database access operation. In a nutshell, database transactions represent realworld events of any enterprise.
- All types of database access operation which are held between the beginning and end transaction statements are considered as a single logical transaction in DBMS. During the transaction the database is inconsistent. Only once the database is committed the state is changed from one consistent state to another. What are ACID Properties? ACID Properties are used for maintaining the integrity of database during transaction processing. ACID in DBMS stands for Atomicity, Consistency, Isolation, and Durability.
- Atomicity : A transaction is a single unit of operation. You either execute it entirely or do not execute it at all. There cannot be partial execution.
- Consistency : Once the transaction is executed, it should move from one consistent state to another.
- Isolation: Transaction should be executed in isolation from other transactions (no Locks). During concurrent transaction execution, intermediate transaction results from simultaneously executed transactions should not be made available to each other. (Level 0,1,2,3)
- Durability: · After successful completion of a transaction, the changes in the database should persist. Even in the case of system failures. Storage Manager In DBMS
- A storage manager is a program module that provides the interface between the lowlevel data stored in the database and the application programs and queries submitted to the system.
- The storage manager is responsible for the interaction with the file manager.
- The raw data are stored on the disk using the file system, which is usually provided by a conventional operating system.
- The storage manager translates the various DML statements into low-level file- system commands. Thus, the storage manager is responsible for storing, retrieving, and updating data in the database. The storage manager components include:
- 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 remains in a consistent (correct) state despite system failures, and that concurrent transaction executions proceed without conflicting.
- 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, and deciding what data to cache in main memory. The buffer manager is a critical part of the database system, since it enables the database to handle data sizes that are much larger than the size of main memory.The storage manager implements several data structures as part of the physical system implementation: Query Processing in DBMS A query processor is one of the major components of a relational database or an electronic database in which data is stored in tables of rows and columns. It complements the storage engine, which writes and reads data to and from storage media.
