DBMS Unit 1 PDF

UNIT-1  DATA:-  Data is collection of fact and figure.  Data is collection of number, alphabets, and special symbol.  Data is raw facts that can be proceed into information.  Raw fact: Raw fact means that the facts have not yet been p...

UNIT-1  DATA:-  Data is collection of fact and figure.  Data is collection of number, alphabets, and special symbol.  Data is raw facts that can be proceed into information.  Raw fact: Raw fact means that the facts have not yet been proceed to get their exact meaning. For example: the marks of different subject of students to compute result. Example: 502 Why we need Data: To derive some information from it.  INFORMATION:  Information is data that has been converted or proceed into more meaningful or useful form.  Information is organized or classified data, which has some meaningful values for the receiver.  Information is proceed data For example: 502 is a room number Example: Total marks for each students in an examination which are used to decide who is topper.  RECORDS  Record field of data are grouped together to form a record or collection of related field.  Record Collection of inter-related data  Inter-related data: That the data is connected and related to each other with respect to the given attributes.  Attributes: It is a quality/ characteristics of person, place or real things. Example: students record, employee record Employee id is related to the name, salary, department number, etc  FILE ORIENTED APPROACH:  File based systems were an early attempt to computerize the manual system.  It is also called a traditional based approach in which a decentralized approach was taken where each department stored and controlled its own data with the help of a data processing specialist. Example: Consider an example of a student's file system. The student file will contain information regarding the student (i.e. roll no, student name, course etc.). Similarly, we have a subject file that contains information about subject and result file which contains the information regarding the result. Some fields are duplicated in more than one file, which leads to data redundancy. So to overcome this problem, we need to create a centralized system, i.e. DBMS approach. Disadvantages of file oriented approach:  Data redundancy and inconsistency: File processing system leads to the usage of many copies of same data. If we need to change any of the data, then we need to change the data at all copies. If not, this will lead to inconsistency. Example: Assume a file for storing addresses of students. If we make three copies of the address file and store them in three different computers, we say that the data is redundant. If suppose one want to change the address of any students, then the change should be made at all the three computers failing which leads to inconsistent data.  Difficulty in accessing data : In a file processing system, to access data differently we need to have different programs. Example: If you want to access student names from a file, we need a program that does the job. If you want to view only address of all students from a specific city, then we need different program that does the required job. This list goes endless. Hence, it is difficult to access data.  Data isolation : Because data are scattered in various file and files may be in different formats, with new application programs to retrieve the  appropriate data is difficult. Example: At one location the student data may be stored in.txt format. In other location, the same file may be stored in.doc format.  Integrity Problems: Developers enforce data validation in the system by adding appropriate code in the various application program. However when new constraints are added, it is difficult to change the programs to enforce them.  Security problems: There is no security provided in file processing system to secure the data from unauthorized user access.  DATABASE:-  A database is a collection of data. In a database, data is stored in an organized manner.  The data in a database is stored systematically so that it can be easily accessed, managed, and updated when needed.  In other words, a database is an organized collection of data where users can easily insert, delete, and update data.  Data in a database is stored in tables. A database can contain one or more tables. For example, a library stores many books. In this context, the library is a database, and the books are the data.  A DBMS (Database Management System) is used to manage the database. In a database, a user can perform the following tasks:  The user can create, delete, and manage the database.  The user can easily store data in the database, make changes to the data, and delete data.  The user can access the data present in the database according to their needs. Nowadays, many websites on the internet are handled through databases. For example, with MakeMyTrip or OYO, we can easily book a hotel room. Both of these websites use databases.  Several databases are used today, including: MySQL, Oracle, MongoDB, SQL Server, Sybase, Informix, Amazon Relational Database Service Advantages of a Database:  It reduces data redundancy, meaning it does not store duplicate data.  It provides data integrity and security, ensuring that unauthorized users cannot access the database.  Multiple users can access the database simultaneously.  Data can be shared with other users.  Data can be easily accessed in a database.  Searching and retrieving data is very simple. In older databases, we needed to write a program for each search, but in modern databases, you can search any data with just a command.  No user can access the database without permission.  Backing up data in a database is very easy, and if the database gets corrupted, we can restore the data.  The data is consistent because there is no data redundancy. Disadvantages of a Database:  Designing a database system is very complex and time-consuming.  The cost of hardware and software is very high.  If the database is damaged, it negatively affects all application programs.  One needs to learn how to use it before using it; it cannot be used without learning.  The size of the database is usually very large.  DBMS:-  DBMS stands for Database Management System.  A DBMS is software used to create, delete, and manage databases.  In other words, "DBMS is a software application that is used to store, manage, delete, and access data in a database."  DBMS is a collection of programs that acts as an interface between the user and the database.  A DBMS has many commands that allow users to easily create, manage, and delete databases. With a DBMS, a user can do the following:  The user can create, delete, and manage databases.  The user can store data in the database, make changes to the data, and delete data.  The user can access the data in the database according to their needs.  It also allows users to be registered and monitored within the database.  A DBMS is software that acts as an interface between end users and the database. It helps users store data efficiently in the database and easily access the data whenever needed.  Some of the commonly used database management systems today are DBase, FoxPro, IMS, Oracle, MySQL, SQL, and DB2, among others. Advantages of DBMS  A Database Management System (DBMS) offers many advantages, which are listed below:  Data can be easily searched in a DBMS.  Data can be easily retrieved when needed.  Users can manage data more effectively.   DBMS provides strong security, making it difficult for hackers to access. The risk of viruses is also very low.  Duplicate data cannot be stored in a DBMS.  Users can easily share data with other users.  User privacy and data integrity are maintained.  Users can take data backups whenever they need.  Working with a DBMS is very easy; tasks can be performed effortlessly.  It can store a large amount of data.  Many users can work simultaneously. Disadvantages of DBMS There are some disadvantages of DBMS, which are mentioned below:  The hardware and software required for a DBMS can be very expensive.  Some DBMS can be difficult to use, so users may need training.  Sometimes data may be lost due to power failures.  The design of a DBMS can be quite complex.  When many users access the same program at the same time, there is a risk of some data being lost.  Characteristics of DBMS The characteristics of a Database Management System (DBMS) are as follows: Data Storage: It can store any type of data. All kinds of data on the internet can be stored using a DBMS. ACID Support: DBMS supports ACID properties, which stand for Atomicity, Consistency, Isolation, and Durability. Multi-user Access: Multiple users can access the database at the same time. Data Sharing: Data can be shared easily through DBMS. No Data Redundancy: there is any data redundancy, meaning there is no duplicate data.  Strong Security: DBMS provides strong security, so unauthorized users cannot access the data. Backup: If the database is deleted or corrupted, a backup can be taken.  Applications of DBMS DBMS is used in various fields, including: Banking: Used to store customer information, payments, account activities, deposits, and loan details. Airlines: Used for ticket booking and reservations. Education: Used in colleges and schools to store student information, course registration, and teacher information. Telecommunications: Used to store call records, billing, and balance information. Finance: Used to store information about stocks, sales, and purchases. Sales: Used to store customer, product, and sales information. Manufacturing: Used for supply chain management and product tracking. HR Management: Used to store employee information, salary details, payroll, and paychecks.  Components of DBMS HARDWARE PROCEDURE SOFTWARE COMPONENT OF DBMS USER DATA A DBMS consists of the following components: Hardware: This includes the computer system used to store and access the database. It involves physical electronic devices like computers, I/O channels, and storage devices, such as hard disks. Software: The most crucial component of DBMS, it is a collection of programs used to control and manage the entire database. This includes DBMS software, the operating system, and application programs. DBMS software sits between the database and the users, providing an easy interface for storing, updating, and retrieving data. Data: Data is a critical component of DBMS. It stores, processes, and accesses data, including old data, operational data, and metadata. Users: There are many users who access data according to their needs. Different users have different capabilities and requirements. The types of users include:  Database administrators  Database designers  End-users  Application programmers  Procedures: Procedures include the rules and instructions for using the DBMS. They explain how to use the system, such as installing and setting up the DBMS, logging in and out, taking database backups, and handling the database.  DBMS Languages 1. Data Definition Language (DDL)  DDL stands for Data Definition Language. It is used to define database structure or pattern.  It is used to create schema, tables, indexes, constraints, etc. in the database.  Using the DDL statements, you can create the skeleton of the database.  Data definition language is used to store the information of metadata like the number of tables and schemas, their names, indexes, columns in each table, constraints, etc. Here are some tasks that come under DDL-  Create: It is used to create objects in the database.  Alter: It is used to alter the structure of the database.  Drop: It is used to delete objects from the database. Deletes an entire table, database, or other objects.  Truncate: It is used to remove all records from a table. This command is used to delete all data from a table, but the structure of the table remains. It’s a fast way to clear large data from a table  Rename: It is used to rename an object. These commands are used to update the database schema that's why they come under Data definition language. For example Syntax for creating a table: CREATE TABLE table_name (column1 datatype, column2 datatype,...); 2.Data Manipulation Language (DML)  It is used for accessing and manipulating data in a database.  It handles user requests. Here are some tasks that come under DML:  Select: It is used to retrieve data from a database.  Insert: It is used to insert data into a table.  Update: It is used to update existing data within a table.  Delete: It is used to delete all records from a table. This command is used to remove one or more rows from a table  Merge: It performs UPSERT operation, i.e., insert or update operations.  INSERT: This command is used to add new rows (records) to a table. Syntax: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...); 3. Data Control Language (DCL)  It is used to retrieve the stored or saved data. Here are some tasks that come under DCL:  Grant: It is used to give user acces privileges/permission to a database. This command is used to give users access privileges to the database. These privileges can include the ability to select, insert, update, delete,  Revoke: It is used to take back permissions from the user. For example:- Syntax: GRANT privilege_name ON object_name TO user_name; For example, GRANT SELECT ON employees TO user123; gives user123 the permission to read data from the employees table.  4.Transaction Control Language (TCL)  TCL commands used to manage transactions in a database. Transactions are important for maintaining the integrity and consistency of data. Here are some tasks that come under TCL:  Commit: It is used to save the transaction on the database.  Rollback: It is used to restore the database to original since the last Commit.  Roles in the Database Environment There are four types of people that participate in DBMS environment :  Data and Database Administrator,  Database Designers,  Applications Developers and Users A database environment is a collection of components that regulates the use of data, management, and a group of data Database Administrators (DBA)  A person who has central control over the system is called database administrator.  A Database Administrator (DBA) is an IT professional who works on creating, maintaining, querying, and tuning the database of the organization.  They are also responsible for maintaining data security and integrity.  Installing and upgrading the DBMS Servers, Design and implementation, Performance Tuning  Backup & Recover  Documentation, Security  Granting of authorization for data access A good performing database is in the hands of DBA The Data Administrator (DA)  Data administrator is a person who is responsible for processing data into a convenient data model.  Data Administrator is less of a technical role and more of a business role with some technical knowledge.  This role is also known as Data Analyst  A data analyst is a technical professional who works with data to help businesses make strategic decisions: Database Designers  Logical Database Designers The logical database designer is concerned with identifying the data (that is, the entities and attributes), the relationships between the data, and the constraints on the data that is to be stored in the database.  The logical database designer must have a thorough and complete understanding of the organization’s data and any constraints on this data.  Physical Database Designers  The physical database designer decides how the logical database design is to be physically realized.  Mapping the logical database design into a set of tables and integrity constraints. Application Developers  Once the database has been implemented, the application programs that provide the required functionality for the end-users must be implemented. This is the responsibility of the application developers.  They are the developers who interact with the database by means of DML queries. End Users  The end-users are the ‘clients’ for the database.