Unit 1 DBMS PDF

Unit I – DataBase Management System Dr. M. P. Sukassini Asst. Professor DDGD Vaishnav College  Need for DBMS Data is a collection of facts and figures The data collection was increasing day to day and they needed to be stored in a device...

Unit I – DataBase Management System Dr. M. P. Sukassini Asst. Professor DDGD Vaishnav College  Need for DBMS Data is a collection of facts and figures The data collection was increasing day to day and they needed to be stored in a device or a software which is safer. A database management system (DBMS) allows a person to organize, store, and retrieve data from a computer. It is a way of communicating with a computer’s “stored memory.” Database Management System (DBMS) is a software for storing and retrieving users’ data while considering appropriate security measures. It consists of a group of programs which manipulate the database.  History of DBMS 1960 – Charles Bachman designed first DBMS system 1970 – Codd introduced IBM’S Information Management System (IMS) 1976- Peter Chen coined and defined the Entity-relationship model also know as the ER model 1980 – Relational Model becomes a widely accepted database component 1985- Object-oriented DBMS develops. 1990s- Incorporation of object-orientation in relational DBMS. 1991- Microsoft ships MS access, a personal DBMS and that displaces all other personal DBMS products. 1995: First Internet database applications 1997: XML applied to database processing. Many vendors begin to integrate XML into DBMS products.  Characteristics of DBMS Provides security and removes redundancy Self-describing nature of a database system Insulation between programs and data abstraction Support of multiple views of the data Sharing of data and multiuser transaction processing Database Management Software allows entities and relations among them to form tables. It follows the ACID concept ( Atomicity, Consistency, Isolation, and Durability). DBMS supports multi-user environment that allows users to access and manipulate data in parallel.  DBMS vs File Basis FILE DBMS The file system is software that manages and DBMS is software for managing the Structure organizes the files in a storage medium within a database computer. In DBMS there is no redundant Data Redundancy Redundant data can be present in a file system. data. Backup and It doesn’t provide backup and recovery of data if it It provides backup and recovery of Recovery is lost. data even if it is lost. There is no efficient query processing in the file Efficient query processing is there in Query processing system. DBMS. DBMS has more security File systems provide less security in comparison to Security Constraints mechanisms as compared to file DBMS. systems.  DBMS vs RDBMS Parameters DBMS RDBMS Storage Stores data in the form of a file Stores data in the form of tables Database Structure Hierarchical arrangement of data Stores data in the form of rows and columns within tables Number of Users Allows one user at a time Allows more than one user at a time ACID Does not use the ACID form of data Uses the ACID model storage Type of Program Manages the data in a computer Maintains the relationships of tables in a database Hardware and Software Not many hardware and software Needs a good set of hardware Needs requirements and software requirements Integrity Constraints Does not support integrity Supports integrity constraints constraints  DBMS vs RDBMS Parameters DBMS RDBMS Normalization Cannot be normalized Supports normalization Distributed Databases No support for distributed Allows distributed databases databases Data Handling Capacity Cannot handle large amounts Able to handle high amounts of data of data Data Access Individual data access Easy and straightforward data access Data Relationship No relationships defined for Defines relationships using the data foreign keys Data Security Lack of data security Good data security due to several log files  Data Models How data is connected to each other logically? How they are processed? Data Model: Data modeling is the process of creating a conceptual view of the information a database contains or should contain. As a result of this process, a data model is created, giving form to data objects (all those entities for which information is to be stored), the associations or relationships among them, and rules or restrictions that govern the information that enters the database. Data Models Types of DBMS  Relational Model The relational data model was introduced by C. F. Codd in 1970. The relational data model describes the world as “a collection of inter-related relations (or tables).” A relational data model involves the use of data tables that collect groups of elements into relations These models work based on the idea that each table setup will include a primary key or identifier. Database administrators use Structured Query Language (SQL) to retrieve data elements from a relational database. Relational Model  Relational Model Database Design Objective Eliminate Data Redundancy: the same piece of data shall not be stored in more than one place. This is because duplicate data not only waste storage spaces but also easily lead to inconsistencies. Ensure Data Integrity and Accuracy: is the maintenance of, and the assurance of the accuracy and consistency of, data over its entire life-cycle, and is a critical aspect to the design, implementation, and usage of any system which stores, processes, or retrieves data.  Relational Model There are four stages of an RDM which are as follows − Relations and attributes − The various tables and attributes related to each table are identified. The tables represent entities, and the attributes represent the properties of the respective entities. Primary keys − The attribute or set of attributes that help in uniquely identifying a record is identified and assigned as the primary key. Relationships −The relationships between the various tables are established with the help of foreign keys. Foreign keys are attributes occurring in a table that are primary keys of another table. The types of relationships that can exist between the relations (tables) are One to one, One to many, and Many to many Normalization − This is the process of optimizing the database structure. Normalization simplifies the database design to avoid redundancy and confusion.  Network Model The network model was created to represent complex data relationships more effectively. It has entities which are organized in a graphical representation and some entities are accessed through several paths. Features of a Network Model are as follows − Ability to Merge Relationships − In this model, because of more relationships the data is more related. It has an ability to manage one-to-one relationships as well as many-to-many relationships. Many paths − There can be more than one path to the same record because of more relationships. It makes data access fast and simple. Circular Linked List − The operations in this model are done with the help of the circular linked list. The current position is maintained with the help of a program and navigates through the records based on relationships.  Network Model Advantages Network models represent complex data relationships better than the hierarchical models. It handles so many relationship types. Data access is more flexible than hierarchical models. Improved database performance. It includes Data Definition Language (DDL) and Data Manipulation Language (DML) commands.  Network Model Disadvantages Database contains a complex array of pointers. System complexity limits efficiency. Structural changes require changes in all application programs. Navigation systems yield complex implementation and management. Keep heavy pressure on programmers due to the complex structure. Any change like updating, deletion, insertion is very complex. Network Model  Hierarchical Model A hierarchical model represents the data in a tree-like structure in which there is a single parent for each record. To maintain order there is a sort field which keeps sibling nodes into a recorded manner. This model structure allows the one-to-one and a one-to-many relationship between two/ various types of data. The hierarchical structure is used as the physical order of records in storage. Advantages A user can retrieve data very quickly due to the presence of explicit links between the table structures. The referential integrity is built in and automatically enforced due to which a record in a child table must be linked to an existing record in a parent table, along with that if a record deleted in the parent table then that will cause all associated records in the child table to be deleted as well.  Hierarchical Model Disadvantages When a user needs to store a record in a child table that is currently unrelated to any record in a parent table, it gets difficulty in recording and user must record an additional entry in the parent table. This type of database cannot support complex relationships, and there is also a problem of redundancy, which can result in producing inaccurate information due to the inconsistent recording of data at various sites. Hierarchical Model  Object Oriented 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. Elements of Object oriented data model Objects : The real world entities and situations are represented as objects in the Object oriented database model. Attributes and Method :Every object has certain characteristics. These are represented using Attributes. The behaviour of the objects is represented using Methods. Class :Similar attributes and methods are grouped together using a class. An object can be called as an instance of the class. 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. Object Oriented Model  OODBMS Advntages Disadvantages Complex data sets can be saved and retrieved Object databases are not widely adopted. quickly and easily. Object IDs are assigned automatically. In some situations, the high complexity can cause performance problems. Works well with object-oriented programming Language Dependence: languages. Lack of Ad-Hoc Queries:  Instance and Schema Instances Schema Instances are the collection of information Schema is the overall description of the stored at a particular moment. database. The instances can be changed by certain The basic structure of how the data will be CRUD operations as like addition, deletion stored in the database is called schema. of data. It may be noted that any search query will not make any kind of changes in the instances. Let’s say a table teacher in our database a table - teacher whose name is School, suppose the table has 50 records so the instance of the teacher table require the name, dob, doj in database has 50 records for now and their table so we design a structure as : tomorrow we are going to add another fifty Teacher table records so tomorrow the instance have name: String total 100 records. This is called an doj: date instance. dob: date Instance and Schema Schema Instance It is the overall description of the database. It is the collection of information stored in a database at a particular moment. Data in instances can be changed using Schema is same for whole database. addition, deletion, updation. Does not change Frequently. Changes Frequently. Defines the basic structure of the database It is the set of Information stored at a i.e how the data will be stored in the particular time. database. Three Schema Architecture The three schema architecture is also called ANSI/SPARC architecture or three-level architecture. This framework is used to describe the structure of a specific database system. The three schema architecture is also used to separate the user applications and physical database. The three schema architecture contains three-levels. It breaks the database down into three different categories. Three Schema Architecture Three Schema Architecture Objective: The main objective of three level architecture is to enable multiple users to access the same data with a personalized view while storing the underlying data only once. Thus it separates the user's view from the physical structure of the database. This separation is desirable for the following reasons: – Different users need different views of the same data. – The approach in which a particular user needs to see the data may change over time. – The users of the database should not worry about the physical implementation and internal workings of the database such as data compression and encryption techniques, hashing, optimization of the internal structures etc. – All users should be able to access the same data according to their requirements. – DBA should be able to change the conceptual structure of the database without affecting the user's  Internal Level The internal level has an internal schema which describes the physical storage structure of the database. The internal schema is also known as a physical schema. It uses the physical data model. It is used to define that how the data will be stored in a block. The physical level is used to describe complex low-level data structures in detail.  Conceptual Level The conceptual schema describes the design of a database at the conceptual level. Conceptual level is also known as logical level. The conceptual schema describes the structure of the whole database. The conceptual level describes what data are to be stored in the database and also describes what relationship exists among those data. In the conceptual level, internal details such as an implementation of the data structure are hidden. Programmers and database administrators work at this level.  External Level At the external level, a database contains several schemas that sometimes called as subschema. The subschema is used to describe the different view of the database. An external schema is also known as view schema. Each view schema describes the database part that a particular user group is interested and hides the remaining database from that user group. The view schema describes the end user interaction with database systems. Conceptual / Internal Mapping There are basically two types of mapping in the database architecture: Conceptual/ Internal Mapping External / Conceptual Mapping Conceptual/ Internal Mapping The Conceptual/ Internal Mapping lies between the conceptual level and the internal level. Its role is to define the correspondence between the records and fields of the conceptual level and files and data structures of the internal level. External/ Conceptual Mapping The external/Conceptual Mapping lies between the external level and the Conceptual level. Its role is to define the correspondence between a particular external and the conceptual view.  Data Independence Logical Data Independence Logical data independence refers characteristic of being able to change the conceptual schema without having to change the external schema. Logical data independence is used to separate the external level from the conceptual view. If we do any changes in the conceptual view of the data, then the user view of the data would not be affected. Logical data independence occurs at the user interface level.  Data Independence Physical Data Independence Physical data independence can be defined as the capacity to change the internal schema without having to change the conceptual schema. If we do any changes in the storage size of the database system server, then the Conceptual structure of the database will not be affected. Physical data independence is used to separate conceptual levels from the internal levels. Physical data independence occurs at the logical interface level.  ER Data Model ER model stands for an Entity-Relationship model. It is a high-level data model. This model is used to define the data elements and relationship for a specified system. It develops a conceptual design for the database. It also develops a very simple and easy to design view of data. In ER modeling, the database structure is portrayed as a diagram called an entity-relationship diagram. Component of ER Diagram  ER Entity: An entity may be any object, class, person or place. In the ER diagram, an entity can be represented as rectangles.  ER Weak Entity : An entity that depends on another entity called a weak entity. The weak entity doesn't contain any key attribute of its own. The weak entity is represented by a double rectangle. It can be identified uniquely by considering the primary key of another entity. Note – Strong-Weak entity set always has parent-child relationship. Weak Entity Weak Entity Weak Entity Strong Entity vs Weak Entity Strong Entity Weak Entity Strong entity set always has a primary key. It does not have enough attributes to build a primary key. It is represented by a rectangle symbol. It is represented by a double rectangle symbol. It contains a Primary key represented by the It contains a Partial Key which is represented underline symbol. by a dashed underline symbol. Primary Key is one of its attributes which In a weak entity set, it is a combination of helps to identify its member. primary key and partial key of the strong entity set. In the ER diagram the relationship between The relationship between one strong and a two strong entity set shown by using a weak entity set shown by using the double diamond symbol. diamond symbol.  ER Attribute: The attribute is used to describe the property of an entity. Eclipse is used to represent an attribute.  ER Key Attribute The key attribute is used to represent the main characteristics of an entity. It represents a primary key. The key attribute is represented by an ellipse with the text underlined.  ER Composite Attribute An attribute that composed of many other attributes is known as a composite attribute. The composite attribute is represented by an ellipse, and those ellipses are connected with an ellipse.  ER Multivalued Attribute: An attribute can have more than one value. These attributes are known as a multivalued attribute. The double oval is used to represent multivalued attribute.  ER Derived Attribute: An attribute that can be derived from other attribute is known as a derived attribute. It can be represented by a dashed ellipse.  Relationship Relationship A relationship is used to describe the relation between entities. Diamond or rhombus is used to represent the relationship.  Types of Relationship Types Of Relationships Description One-to-one This type of relationship holds when a record of one table is related to only one record of another table. One-to-many This type of relationship holds when one record of a table is related to many records of another table. Many-to-Many This type of relationship holds when many records of one table are related to many records of another table.  Types of Relationship One-to-One Relationship: When only one instance of an entity is associated with the relationship, then it is known as one to one relationship.  Types of Relationship One-to-many relationship When only one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then this is known as a one-to-many relationship. For example, Scientist can invent many inventions, but the invention is done by the only specific scientist.  Types of Relationship Many-to-one relationship When more than one instance of the entity on the left, and only one instance of an entity on the right associates with the relationship then it is known as a many-to-one relationship. For example, Student enrolls for only one course, but a course can have many students.  Types of Relationship Many-to-many relationship; When more than one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then it is known as a many-to-many relationship. For example, Employee can assign by many projects and project can have many employees.  Cardinality Cardinality Ratio: The number of relationship instance that an entity can participate in. Cardinality  ER Exercise Identify the relationship and draw the ER diagram: Departments , identified by ID, operate different types of printers, each located in different room in a building. Printers are supplied by a number of supplier identified by name,with each supplier charging a different price for a given printer, but also providing different delivery delays, measured in days A given room can have any number of printers. ER Exercise Thank You