Introduction to DBMS PDF

INTRODUCTION TO DBMS SUBJECT NAME: ADVANCE DATABASE MANAGEMENT SYSTEMS (3130703) SEM: III PREPARED BY: PROF. SMIT NAYAK SWAMINARYAN UNIVERSITY WHAT IS DATA? Data is a collection of a distinct small unit of information. It...

INTRODUCTION TO DBMS SUBJECT NAME: ADVANCE DATABASE MANAGEMENT SYSTEMS (3130703) SEM: III PREPARED BY: PROF. SMIT NAYAK SWAMINARYAN UNIVERSITY WHAT IS DATA? Data is a collection of a distinct small unit of information. It can be used in a variety of forms like text, numbers, media, bytes, etc. it can be stored in pieces of paper or electronic memory, etc. Word 'Data' is originated from the word 'datum' that means 'single piece of information.' It is plural of the word datum. In computing, Data is information that can be translated into a form for efficient movement and processing. WHAT IS DATABASE? A database (DB), in the most general sense, is an organized collection of data. More specifically, a database is an electronic system that allows data to be easily accessed, manipulated and updated. OR A Database is a collection of interrelated data that can be easily accessed and managed. You can organize data into tables, rows, columns, and index it to make it easier to find relevant information. DATABASE MANAGEMENT SYSTEM (DBMS) Database management system (DBMS) ○ Collection of programs ○ Enables users to create and maintain a database ○ Allows multiple users and programs to access and manipulate the database concurrently ○ Provides protection against unauthorized access and manipulation Provides means to evolve database and program behaviour as requirements change over time DBMS = Database + Set of programs CONTI.. Examples of DBMS IBM’s DB2 Microsoft’s Access and SQL Server, Oracle, MySQL, SAP’s SQL Anywhere Define the following terms. METADATA: Metadata is data about data. Data such as table name, column name, data type, authorized user, user access privileges for any table is called metadata for that table. DATA DICTIONARY: Data dictionary is an information repository which contains metadata. DATA WAREHOUSE: Data warehouse is an information repository which stored data. It is design to facilitate reporting and analysis. FIELD: A field is a character or group of characters that have a specific meaning. It is represented in the database by a value. For Example customer id, name, society and city are all fields for  RECORD: A record is a collection of logically related fields. For examples, collection of fields (id, name, society & city) forms a record for customer.  FILE SYSTEM VS DBMS Data redundancy: Data No redundant data: redundancy refers to the Redundancy removed by data duplication of data. Let's say normalization. No data we are managing the data of a duplication saves storage and college where a student is improves access time. enrolled for two courses, the same student details in such case will be stored twice, which will take more storage than needed. Data redundancy often leads to higher storage costs and poor access time. FILE SYSTEM VS DBMS Data inconsistency: Data Data Consistency and redundancy leads to data Integrity: The root cause of inconsistency. For eg. A student data inconsistency is data is enrolled for two courses and redundancy, since data we have student address stored normalization takes care of the twice, now lets say student data redundancy, data requests to change his address, inconsistency also been taken if the address is changed at one care of as part of it place and not on all the records then this can lead to data inconsistency. FILE SYSTEM VS DBMS Data Sharing: Because data No such case of difficulty in are scattered in various files, retrieving data. and files may be in different formats, writing new application programs to retrieve the appropriate data is difficult. FILE SYSTEM VS DBMS Data Security: Data should be Data Security: It is easier to secured from unauthorised apply access constraints in access, for example a student in database systems so that only a college should not be able to authorized user is able to access see the payroll details of the the data. Each user has a teachers, such kind of security different set of access thus, data constraints are difficult to apply is secured from the issues such in file processing systems. as identity theft, data leaks and misuse of data. FILE SYSTEM VS DBMS Atomicity of a transaction refers to “All or Atomicity issues: It is difficult nothing”, which means either all the to achieve atomicity in file operations in a transaction executes or none. For example: Let's say Ram transfers 100$ to processing systems. Shyam’s account. This transaction consists multiple operations such as debit 100$ from Ram’s account, credit 100$ to Shyam’s account. Like any other device, a computer system can fail let's say it fails after first operation then in that case Ram’s account would have been debited by 100$ but the amount was not credited to Shyam’s account, in such case the rollback of operation should occur to maintain the atomicity of transaction. APPLICATIONS OF DBMS Airlines and railways: Airlines and railways use online databases for reservation, and for displaying the schedule information. Banking: Banks use databases for customer inquiry, accounts, loans, and other transactions. Education: Schools and colleges use databases for course registration, result, and other information. Telecommunication: Telecommunication departments use databases to store information about the communication network, telephone numbers, record of calls, for generating monthly bills, etc. APPLICATIONS OF DBMS Credit card transactions: Databases are used for keeping track of purchases on credit cards in order to generate monthly statements. E-commerce: Integration of heterogeneous information sources (for example, catalogs) for business activity such as online shopping, booking of holiday package, consulting a doctor, etc. Health care information systems and electronic patient record: Databases are used for maintaining the patient health care details in hospitals. APPLICATIONS OF DBMS Digital libraries and digital publishing: Databases are used for management and delivery of large bodies of textual and multimedia data. Finance: Databases are used for storing information such as sales, purchases of stocks and bonds or data useful for online trading. Human resources: Organizations use databases for storing information about their employees, salaries, benefits, taxes, and for generating salary checks. FUNCTIONS(ROLES/RESPONSIBILITIES/DUTIES) OF DBA DBA(Database Administrator): Database Administrator is a person in the organization who controls the design and the use of database. DBA defines the logical schema of the database. SCHEMA: A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data. FUNCTIONS(ROLES/RESPONSIBILITIES/DUTIES) OF DBA Defining Security and Integrity Constraints: DBA decides various security and integrity constraints. DDL provides facilities to specifying such constraints. Granting of Authorization for Data Access: DBA determines which user needs access to which part of the database. This is required to prevent unauthorized access of a database. Monitoring Performance: DBA monitors performance of the system. It ensure that better performance is maintained by making change in physical or logical schema if required. FUNCTIONS(ROLES/RESPONSIBILITIES/DUTIES) OF DBA Backup and Recovery Database should not be lost or damaged. The task of DBA is to backing up the database on some storage devices such as DVD, CD or Magnetic Tape or remote servers. In case of failures, such as flood or virus attack, Database is recovered from this backup. INSTANCES AND SCHEMAS Similar to types and variables in programming languages Schema – the logical structure of the database. For example: the database consists of information about a set of customers and accounts and the relationship between them. Analogous to type information of a variable in a program Physical schema: Database design at the physical level Logical schema: Database design at the logical level SCHEMA INSTANCES AND SCHEMAS 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. For example, we have a single table student in the database, today the table has 100 records, so today the instance of the database has 100 records. Let's say we are going to add another 100 records in this table by tomorrow, so the instance of database tomorrow will have 200 records in table. In short, at a particular moment, the data stored in database is called the instance, that changes over time when we add or delete data from the database. 3-TIER DATABASE SYSTEM ARCHITECTURE EXTERNAL VIEW It is also called view level. The reason this level is called “view” is because several users can view their desired data from this level which is internally fetched from database with the help of conceptual and internal level mapping. The user doesn’t need to know the database schema details such as data structure, table definition etc. user is only concerned about data which is what returned back to the view level after it has been fetched from database (present at the internal level). CONCEPTUAL VIEW It is also called logical level. The whole design of the database such as relationship among data, schema of data etc. are described in this level. Database constraints and security are also implemented in this level of architecture. This level is maintained by DBA (database administrator). It describes what data are stored in the database and what relationships exist among those data. INTERNAL VIEW This level is also known as physical level. This level describes how the data is actually stored in the storage devices. This level is also responsible for allocating space to the data. This is the lowest level of the architecture. DATA INDEPENDENCE Data independency is the ability to modify a schema definition in one level without affecting a schema definition in the next higher level. Types of data independence Physical data independence Logical data independence PHYSICAL DATA INDEPENDENCE Physical data independence allows changing in physical storage devices or organization of file without change in the conceptual view or external view. Physical data independence separates conceptual level from the internal level. LOGICAL DATA INDEPENDENCE Logical data independence is the ability to modify the conceptual schema without requiring any change in application programs. Conceptual schema can be changed without affecting the existing external schema. Modifications at the logical level are necessary whenever the logical structure of the database is altered. Logical data independence separates external level from the conceptual view. DIFFERENT DATABASE USERS Application programmers: Application Programmers are the back end programmers who writes the code for the application programs. They are the computer professionals. These programs could be written in Programming languages such as Visual Basic, Developer, C, FORTRAN, COBOL etc. Database Designers : Database Designers are the users who design the structure of database which includes tables, indexes, views, constraints, triggers, stored procedures. He/she controls what data must be stored and how the data items to be related. DIFFERENT DATABASE USERS Sophisticated users: These users interact with system without writing program. Sophisticated users can be engineers, scientists, business analyst, who are familiar with the database. They can develop their own database applications according to their requirement. They don’t write the program code but they interact the database by writing SQL queries directly through the query processor. DIFFERENT DATABASE USERS Naive / Parametric End Users : Parametric End Users are the unsophisticated who don’t have any DBMS knowledge but they frequently use the database applications in their daily life to get the desired results. For examples, Railway ticket booking users are naive users. Clerks in any bank is a naive user because they don’t have any DBMS knowledge but they still use the database and perform their given task. DBMS LANGUAGES A DBMS has appropriate languages and interfaces to express database queries and updates. Database languages can be used to read, store and update the data in the database. The following are the database languages − ○ Data Definition Language(DDL) ○ Data Manipulation Language(DML) ○ Data Control Language(DCL) ○ Transaction Control Language(TCL) DATA DEFINITION LANGUAGE(DDL) DDL is a set of commands used to create, modify and delete database structure but not data. It is used to store the information of metadata like the number of tables and schemas, their names, indexes, columns in each table, constraints, etc. DDL commands are as follows: ○ CREATE: Create new database, table. ○ ALTER: Alter existing database, table. ○ DROP: Drop the database ○ RENAME: Set a new name for the table. ○ TRUNCATE: used to delete complete data from an existing table. CREATE COMMAND Syntax: Example : CREATE command CREATE TABLE CREATE TABLE employee ( column_name1 datatype, ( column_name2 datatype, empid INT,. ename CHAR(30),. age INT,. city CHAR(25), column_name_n datatype phone_no VARCHAR(20) ); ); DROP COMMAND Syntax: Example : DROP TABLE ; DROP TABLE employee; OR DROP DATABASE OR ; DROP DATABASE employees; ALTER COMMAND (you can add additional column, drop existing column and even change the data type of columns.) Example : ALTER Command ALTER TABLE ALTER TABLE employee ADD ; ADD (address varchar2(50)); OR OR ALTER TABLE ALTER TABLE employee CHANGE CHANGE (phone_no) ; (contact_no); OR OR ALTER TABLE ALTER TABLE employee DROP COLUMN DROP COLUMN age; ; RENAME COMMAND It renames a database table. Syntax: RENAME TABLE TO ; Example: RENAME TABLE emp TO employee; DESCRIBE COMMAND To view the changed structure of table, use 'DESCRIBE' command. For example: DESCRIBE TABLE employee; DATA MANIPULATION LANGUAGE(DML) The language used to manipulate the database like inserting data, updating table, retrieving record from a table, etc. is known as Data Manipulation Language − ○ SELECT: Retrieve data from the database ○ INSERT: Insert data ○ UPDATE: update/Modify existing records ○ DELETE: Delete all records SELECT COMMAND Syntax: Example : SELECT * FROM SELECT * FROM ; employee; OR SELECT * FROM employee where salary >=10,000; INSERT COMMAND Example: Syntax: INSERT INTO TABLE_NAME INSERT INTO student (reg_no, (column1, column2, first_name, last_name, column3,...columnN) VALUES dob,address, (value1, value2, pincode)VALUES('A101', 'Mohd', value3,...valueN); 'Imran', '01-MAR-89','Allahabad', 211001); UPDATE COMMAND Syntax: Example: UPDATE UPDATE employee SET SET salary=20000 WHERE condition; WHERE ename='ABC'; DELETE COMMAND Syntax: Example : DELETE FROM DELETE FROM student; WHERE ; OR DELETE FROM employee WHERE emp_id = '001'; DATA CONTROL LANGUAGE DCL stands for Data Control Language. DCL is used to control user access in a database. This command is related to the security issues. It allows or restricts the user from accessing data in database schema. DCL commands are as follows: 1. GRANT 2. REVOKE GRANT COMMAND GRANT command gives Example : GRANT Command user's access privileges to GRANT ALL ON employee the database. TO ABC; This command allows In the above example, user specified users to perform 'ABC' has been given specific tasks. permission to view and modify Syntax: the records in the 'employee' table. GRANT REVOKE COMMAND Syntax: REVOKE REVOKE command is used ON or denied permissions. This command withdraw FROM ; access privileges given with Example : the GRANT command. REVOKE UPDATE ON employee FROM ABC; TRANSACTIONAL CONTROL LANGUAGE TCL stands for Transaction Control Language. This command is used to manage the changes made by DML statements. TCL allows the statements to be grouped together into logical transactions. TCL commands are as follows: ○ COMMIT ○ SAVEPOINT ○ ROLLBACK COMMIT & SAVEPOINT SAVEPOINT command is used for saving all the current point in the COMMIT command saves processing of a transaction. all the work done. It marks and saves the current point It ends the current in the processing of a transaction. transaction and makes It is used to temporarily save a permanent changes transaction, so that you can rollback during the transaction. to that point whenever necessary. Syntax: commit; Syntax: SAVEPOINT ROLLBACK COMMAND Syntax: ROLLBACK command restores database to original ROLLBACK TO SAVEPOINT since the last COMMIT. ; It is used to restores the Example: database to last committed state. ROLLBACK TO SAVEPOINT no_update; SET TRANSACTION SET TRANSACTION is used for Syntax: placing a name on a SET TRANSACTION [Read Write | transaction. Read Only]; This command is used to initiate a database transaction. You can specify a transaction to be read only or read write. DATA MODELS Data Models define how data is connected to each other and how they are processed and stored inside the system. Data Models are fundamental entities to introduce abstraction in a DBMS. Different types of data models ○ Entity relationship model ○ Relational model ○ Hierarchical model ○ Network model ○ Object oriented model ○ Object relational model ENTITY RELATIONSHIP MODEL Entity-Relationship Model or simply ER Model is a high-level data model diagram. This model represent the real-world problem in the pictorial form to make it easy for the stakeholders to understand. We use the ER diagram as a visual tool to represent an ER Model. It is also very easy for the developers to understand the system by just looking at the ER diagram. ER diagram has the following three components: Entity, Attribute and relationship Components of ER diagram Entity: Entity is a real-world thing. It can be a person, place, or even a concept. Example: Teachers, Students, Course, Building, Department, etc are some of the entities of a School Management System. Attributes: An entity contains a real-world property called attribute. This is the characteristics of that attribute. Example: The entity teacher has the property like teacher id, salary, age, etc. Relationship: Relationship tells how two attributes are related. RELATIONAL MODEL Relational Model is the most widely used model. In this model, the data is maintained in the form of a two- dimensional table. All the information is stored in the form of row and columns. The basic structure of a relational model is tables. So, the tables are also called relations in the relational model. Example: Employee table. FEATURES OF RELATIONAL MODEL Tuples: Each row in the table is called tuple. A row contains all the information about any instance of the object. In the above example, each row has all the information about any specific individual like the first row has information about John. Attribute or field: Attributes are the property which defines the table or relation. The values of the attribute should be from the same domain. In the above example, we have different attributes of the employee like Salary, Mobile_no, etc. HIERARCHICAL MODEL This is one of the oldest models in a data model which was developed by IBM, in the 1950s. In a hierarchical model, data are viewed as a collection of tables, or we can say segments that form a hierarchical relation The data is organized into a tree-like structure where each record consists of one parent record and many children. Network Model The network model is the extension of the hierarchical structure because it allows many-to-many relationships to be managed in a tree-like structure that allows multiple parents. The network model is based on direct graph theory. Example: an employee working for two departments. OBJECT ORIENTED DATA MODEL Object oriented data model is based upon real world situations. These situations are represented as objects, with different attributes. All these object have multiple relationships between them. Object: An object is an abstraction of a real world entity or we can say it is an instance of class. ○ Objects encapsulates data and code into a single unit which provide data abstraction by hiding the implementation details from the user. For example: Instances of student, doctor, engineer OBJECT ORIENTED DATA MODEL Attribute: An attribute describes the properties of object. For example: Object is STUDENT and its attribute are Roll no, Branch, Semester in the Student class. Methods: Method represents the behavior of an object. Basically, it represents the real-world action. For example: Finding a STUDENT marks in above as Setmarks(). Class: A class is a collection of similar objects with shared structure i.e. attributes and behavior i.e. methods. An object is an instance of class. For example: Person, Student, Doctor, Engineer Inheritance: A new class can be derived from the original class. The derived class contains attributes and methods of the original class as well as its own. In the chart above, we have different objects linked to one another using methods; one can get the address of the Person (represented by the Person Object) using the livesAt() method. Furthermore, these objects have attributes which are in fact the data elements that need to be defined in the database. The objects Circle, Rectangle and Triangle inherit from the object Shape. OBJECT RELATIONAL MODEL An Object relational model is a combination of a Object oriented database model and a Relational database model. It supports objects, classes, inheritance etc. just like Object Oriented models and has support for data types, tabular structures etc. like Relational data model. An object-relational database may also be known as an object relational database management systems (ORDBMS). Some of the object relational systems available in market are IBM’s DB2 universal server, oracle corporation oracle 8, Microsoft Corporation SQL server 7. INTEGRITY CONSTRAINTS Integrity constraints are a set of rules. It is used to maintain the quality of information. Integrity is usually expressed in terms of constraints, which are consistency rules that the database is not permitted to violate. Constraints may apply to each attribute or they may apply to relationships between tables. Integrity constraints ensure that changes (update deletion, insertion) made to the database by authorized users do not result in a loss of data consistency. EXAMPLE- A brood group must be ‘A’ or ‘B’ or ‘AB’ or ‘O’ only (can DOMAIN CONSTRAINT Domain constraints can be defined as the definition of a valid set of values for an attribute. The data type of domain includes string, character, integer, time, date, currency, etc. The value of the attribute must be available in the corresponding domain. ENTITY INTEGRITY CONSTRAINT The entity integrity constraint states that primary key value can't be null. This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can't identify those rows. A table can contain a null value other than the primary key field. REFERENTIAL INTEGRITY CONSTRAINT The Referential integrity constraints is specified between two relations or tables and used to maintain the consistency among the tuples in two relations. The rules are: a. You can't delete a record from a primary table if matching records exist in a related table. b. You can't change a primary key value in the primary table if that record has related records. c. You can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table. d. However, you can enter a Null value in the foreign key, specifying that the records are unrelated. KEY CONSTRAINT A relation can have multiple keys or candidate keys(minimal superkey), out of which we choose one of the keys as primary key, we don’t have any restriction on choosing the primary key out of candidate keys. Null values are not allowed in the primary key, hence Not Null constraint is also a part of key constraint.