unit 1 dbms.pdf

Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11...

Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11 UNIT-1 INTRODUCTION TO DATABASE What is Database? A database is a collection of interrelated data which helps in the efficient retrieval, insertion, and deletion of data from the database and organizes the data in the form of tables, views, schemas, reports, etc. For Example, a university database organizes the data about students, faculty, admin staff, etc. which helps in the efficient retrieval, insertion, and deletion of data from it. Database Languages  Data Definition Language  Data Manipulation Language  Data Control Language  Transactional Control Language Data Definition Language DDL is the short name for Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.  CREATE: to create a database and its objects like (table, index, views, store procedure, function, and triggers)  ALTER: alters the structure of the existing database  DROP: delete objects from the database  TRUNCATE: remove all records from a table, including all spaces allocated for the records are removed Prepared By:- Janhavee Mistry(SOCCA) 1  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11  COMMENT: add comments to the data dictionary  RENAME: rename an object Data Manipulation Language DML is the short name for Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database. Data query language(DQL) is the subset of “Data Manipulation Language”. The most common command of DQL is SELECT statement. SELECT statement help on retrieving the data from the table without changing anything in the table.  SELECT: retrieve data from a database  INSERT: insert data into a table  UPDATE: updates existing data within a table  DELETE: Delete all records from a database table  MERGE: UPSERT operation (insert or update)  CALL: call a PL/SQL or Java subprogram  EXPLAIN PLAN: interpretation of the data access path  LOCK TABLE: concurrency Control Data Control Language DCL is short for Data Control Language which acts as an access specifier to the database.(basically to grant and revoke permissions to users in the database  GRANT: grant permissions to the user for running DML(SELECT, INSERT, DELETE,…) commands on the table Prepared By:- Janhavee Mistry(SOCCA) 2  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11  REVOKE: revoke permissions to the user for running DML(SELECT, INSERT, DELETE,…) command on the specified table Transactional Control Language TCL is short for Transactional Control Language which acts as an manager for all types of transactional data and all transactions. Some of the command of TCL are  Roll Back: Used to cancel or Undo changes made in the database  Commit: It is used to apply or save changes in the database  Save Point: It is used to save the data on the temporary basis in the database What is DBMS? The software which is used to manage databases is called Database Management System (DBMS). For Example, MySQL, Oracle, etc. are popular commercial DBMS used in different applications. DBMS allows users the following tasks:  Data Definition: It helps in the creation, modification, and removal of definitions that define the organization of data in the database.  Data Updation: It helps in the insertion, modification, and deletion of the actual data in the database.  Data Retrieval: It helps in the retrieval of data from the database which can be used by applications for various purposes.  User Administration: It helps in registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity, dealing Prepared By:- Janhavee Mistry(SOCCA) 3  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11 with concurrency control, and recovering information corrupted by unexpected failure. Applications of DBMS:  Enterprise Information: Sales, accounting, human resources, Manufacturing, online retailers.  Banking and Finance Sector: Banks maintaining the customer details, accounts, loans, banking transactions, credit card transactions. Finance: Storing the information about sales and holdings, purchasing of financial stocks and bonds.  University: Maintaining the information about student course enrolled information, student grades, staff roles.  Airlines: Reservations and schedules.  Telecommunications: Prepaid, postpaid bills maintance. Purpose of DBMS File System manages data using files on a hard disk. Users are allowed to create, delete, and update the files according to their requirements. Let us consider the example of file-based University Management System. Data of students is available to their respective Departments, Academics Section, Result Section, Accounts Section, Hostel Office, etc. Some of the data is common for all sections like Roll No, Name, Father Name, Address, and Phone number of students but some data is available to a Prepared By:- Janhavee Mistry(SOCCA) 4  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11 particular section only like Hostel allotment number which is a part of the hostel office. Let us discuss the issues with this system:  Redundancy of data: Data is said to be redundant if the same data is copied at many places. If a student wants to change their Phone number, he or she has to get it updated in various sections. Similarly, old records must be deleted from all sections representing that student.  Inconsistency of Data: Data is said to be inconsistent if multiple copies of the same data do not match each other. If the Phone number is different in Accounts Section and Academics Section, it will be inconsistent. Inconsistency may be because of typing errors or not updating all copies of the same data.  Difficult Data Access: A user should know the exact location of the file to access data, so the process is very cumbersome and tedious. If the user wants to search the student hostel allotment number of a student from 10000 unsorted students’ records, how difficult it can be.  Unauthorized Access: File Systems may lead to unauthorized access to data. If a student gets access to a file having his marks, he can change it in an unauthorized way.  No Concurrent Access: The access of the same data by multiple users at the same time is known as concurrency. The file system does not allow concurrency as data can be accessed by only one user at a time. Prepared By:- Janhavee Mistry(SOCCA) 5  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11  No Backup and Recovery: The file system does not incorporate any backup and recovery of data if a file is lost or corrupted. Data Independence In the context of a database management system, data independence is the feature that allows the schema of one layer of the database system to be changed without any impact on the schema of the next higher level of the database system.” Through data independence, we can build an environment in which data is independent of all programs, and through the three schema architectures, data independence will be more understandable. Data via two card stencils along with centralized DBMS data is a form of transparency that has value for someone. It can be summed up as a sort of immunity of user applications that adjusts correctly and does not change addresses, imparting the class of data and their order. I want the separate applications not to be forced to deal with data representation and storage specifics because this decreases quality and flexibility. DBMS permits you to see data with such a generalized sight. It actually means that the ability to change the structure of the lower- level schema without presenting the upper-level schema is called data Prepared By:- Janhavee Mistry(SOCCA) 6  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11 independence. Types of Data Independence There are two types of data independence.  logical data independence  Physical data independence Logical Data Independence Prepared By:- Janhavee Mistry(SOCCA) 7  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11  Changing the logical schema (conceptual level) without changing the external schema (view level) is called logical data independence.  It is used to keep the external schema separate from the logical schema.  If we make any changes at the conceptual level of data, it does not affect the view level.  This happens at the user interface level.  For example, it is possible to add or delete new entities, attributes to the conceptual schema without making any changes to the external schema. Physical Data Independence  Making changes to the physical schema without changing the logical schema is called physical data independence.  If we change the storage size of the database system server, it will not affect the conceptual structure of the database.  It is used to keep the conceptual level separate from the internal level.  This happens at the logical interface level.  Example – Changing the location of the database from C drive to D drive. Types of Data base System Architecture (1-tier,2- tier,3-tier) Prepared By:- Janhavee Mistry(SOCCA) 8  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11 A Database stores a lot of critical information to access data quickly and securely. Hence it is important to select the correct architecture for efficient data management. DBMS Architecture helps users to get their requests done while connecting to the database. We choose database architecture depending on several factors like the size of the database, number of users, and relationships between the users. There are two types of database models that we generally use, logical model and physical model. Several types of architecture are there in the database which we will deal with in the next section. Types of DBMS Architecture There are several types of DBMS Architecture that we use according to the usage requirements. Types of DBMS Architecture are discussed here.  1-Tier Architecture  2-Tier Architecture  3-Tier Architecture 1-Tier Architecture In 1-Tier Architecture the database is directly available to the user, the user can directly sit on the DBMS and use it that is, the client, server, and Database are all present on the same machine. For Example: to learn SQL we set up an SQL server and the database Prepared By:- Janhavee Mistry(SOCCA) 9  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11 on the local system. This enables us to directly interact with the relational database and execute operations. The industry won’t use this architecture they logically go for 2-tier and 3-tier Architecture. DBMS 1-Tier Architecture Advantages of 1-Tier Architecture Below mentioned are the advantages of 1-Tier Architecture. Prepared By:- Janhavee Mistry(SOCCA) 10  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11  Simple Architecture: 1-Tier Architecture is the most simple architecture to set up, as only a single machine is required to maintain it.  Cost-Effective: No additional hardware is required for implementing 1-Tier Architecture, which makes it cost- effective.  Easy to Implement: 1-Tier Architecture can be easily deployed, and hence it is mostly used in small projects. 2-Tier Architecture The 2-tier architecture is similar to a basic client-server model. The application at the client end directly communicates with the database on the server side. APIs like ODBC and JDBC are used for this interaction. The server side is responsible for providing query processing and transaction management functionalities. On the client side, the user interfaces and application programs are run. The application on the client side establishes a connection with the server side to communicate with the DBMS. An advantage of this type is that maintenance and understanding are easier, and compatible with existing systems. However, this model gives poor performance when there are a large number of users. Prepared By:- Janhavee Mistry(SOCCA) 11  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11 DBMS 2-Tier Architecture Advantages of 2-Tier Architecture  Easy to Access: 2-Tier Architecture makes easy access to the database, which makes fast retrieval.  Scalable: We can scale the database easily, by adding clients or upgrading hardware.  Low Cost: 2-Tier Architecture is cheaper than 3-Tier Architecture and Multi-Tier Architecture.  Easy Deployment: 2-Tier Architecture is easier to deploy than 3-Tier Architecture.  Simple: 2-Tier Architecture is easily understandable as well as simple because of only two components. 3-Tier Architecture In 3-Tier Architecture, there is another layer between the client and the server. The client does not directly communicate with the Prepared By:- Janhavee Mistry(SOCCA) 12  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11 server. Instead, it interacts with an application server which further communicates with the database system and then the query processing and transaction management takes place. This intermediate layer acts as a medium for the exchange of partially processed data between the server and the client. This type of architecture is used in the case of large web applications. DBMS 3-Tier Architecture Types of Database Model A Database model defines the logical design and structure of a database. It defines how data will be stored, accessed, and updated in a database management system.  As per your application's requirement, you can use a database model to define your database. Prepared By:- Janhavee Mistry(SOCCA) 13  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11  The database model sets the rule, relationships, constraints, etc. to define how data is stored in the database.  It's like creating a blueprint of your Database.  There are different types of Database models and each one has its own set of features.  You can define how you want to structure the application data using a database model. In this tutorial you will learn about the 7 database model that are popularly used. Type of Database models There are several different Database model types, some of them are old, while some of them are new, to cater to the new age requirements. Here is a list of the 7 popular Database models: 1. Hierarchical Model 2. Network Model 3. Entity-relationship Model 4. Relational Model 5. Object-oriented Model 6. NoSQL Model 7. Graph Model Let's learn about the different types of database models along with their main features and when should you use them. 1. Hierarchical Model  The hierarchical database model organizes data into a tree-like structure, with a single root, to which all the other data is linked.  The hierarchy starts from the Root data, and expands like a tree, adding child nodes to the parent nodes.  In this model, a child node will only have a single parent node.  This model efficiently describes many real-world relationships like the index of a book, etc.  IBM's Information Management System (IMS) is based on this model. Prepared By:- Janhavee Mistry(SOCCA) 14  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11  Data is organized into a tree-like structure with a one-to-many relationship between two different types of data, for example, one department can have many courses, many teachers, and of course many students(like shown in the diagram below). Advantages/Disadvantages of the Hierarchical Model Here are a few points to mark the advantages and disadvantages of the Hierarchical database model: 1. Because it has one-to-many relationships between different types of data so it is easier and fast to fetch the data. 2. But the Hierarchical model is less flexible. 3. And it doesn't support many-to-many relationships. 2. Network Model  The Network Model is an extension of the Hierarchical model.  In this model, data is organized more like a graph, and allowed to have more than one parent node.  In the network database model, data is more related as more relationships are established in this database model.  Also, as the data is more related, hence accessing the data is also easier and fast.  This database model uses many-to-many data relationships. Prepared By:- Janhavee Mistry(SOCCA) 15  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11  Integrated Data Store (IDS) is based on this database model.  This was the most widely used database model before Relational Model was introduced.  The implementation of the Network model is complex, and it's very difficult to maintain it.  The Network model is difficult to modify also.  You may want to explore this if you are developing some social networking applications, although the Graph Database model is new and is far better than the Network Database model. Advantages of the Network Model 1. It supports complex relationships 2. It allows more flexibility 3. Entity-relationship Model Prepared By:- Janhavee Mistry(SOCCA) 16  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11  In this database model, relationships are created by dividing objects of interest into entities and their characteristics into attributes.  Different entities are related using relationships.  ER Models are defined to represent the relationships in pictorial form to make it easier for different stakeholders to understand.  This model is good to design a database, which can then be turned into tables in a relational model (explained below).  Let's take an example, If we have to design a School Database, then the Student will be an entity with attributes name, age, address, etc. As an Address is generally complex, it can be another entity with attributes street, pincode, city, etc, and there will be a relationship between them.  Relationships can also be of different types. You can learn about ER Diagrams in detail if you want to learn about entities and relationships. Prepared By:- Janhavee Mistry(SOCCA) 17  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11 Advantages of the ER Model 1. It is easy to understand and design. 2. Using the ER model we can represent data structures easily. 3. As the ER model cannot be directly implemented into a database model, it is just a step toward designing the relational database model. 4. Relational Model  In this model, data is organized in two-dimensional tables and the relationship is maintained by storing a common field.  This model was introduced by E.F Codd in 1970, and since then it has been the most widely used database model. Prepared By:- Janhavee Mistry(SOCCA) 18  Course Code: 3040237103 Course Name: Database Management System SEMESTER: 11  The basic structure of data in the relational model is tables. All the information related to a particular type is stored in rows of that table.  Hence, tables are also known as relations in the relational model.  You can design tables, normalize them to reduce data redundancy, and use Structured Query language or SQL to access data from the tables.  Some of the most popular databases are based on this database model. For example, Oracle, MySQL, etc. Advantages of the Relational Model 1. It's simple and easy to implement. 2. Poplar database software is available for this database model. 3. It supports SQL using which you can easily query the data. Prepared By:- Janhavee Mistry(SOCCA) 19