Unit-1 DBMS PDF

Unit-1 DBMS key characteristics of a database Self-Describing Nature A database contains not only the data but also metadata, which describes the structure, format, and constraints of the data. Example: Tables, columns, data types, and relationships are stored as part of the database schema...

Unit-1 DBMS key characteristics of a database Self-Describing Nature A database contains not only the data but also metadata, which describes the structure, format, and constraints of the data. Example: Tables, columns, data types, and relationships are stored as part of the database schema. Data Persistence Data in a database is stored permanently unless explicitly deleted. Ensures long-term storage and retrieval of data, surviving system shutdowns and crashes. Data Integrity Ensures that data is accurate, valid, and consistent across the database. Integrity constraints (e.g., primary keys, foreign keys) maintain correctness and logical coherence. Data Redundancy and Minimization A good database design minimizes redundancy to save storage and avoid inconsistencies. Through techniques like normalization, data duplication is reduced while maintaining necessary references. Data Sharing Databases allow multiple users or applications to access and share data concurrently. Provides controlled access for different users based on permissions. Data Security A database protects data against unauthorized access or manipulation. Includes features like access control, encryption, and role-based permissions. Data Consistency Ensures that data values are consistent across the database. Enforces business rules and constraints to avoid conflicting or contradictory data entries. Support for Transactions Databases support transactions to ensure reliable execution of operations. Transactions must satisfy ACID properties: Atomicity: Transactions are all-or-nothing. Consistency: Transactions bring the database from one valid state to another. Isolation: Concurrent transactions do not interfere with each other. Durability: Once a transaction is committed, changes are permanent. Data Independence Data is independent of the application programs that use it, meaning changes in database structure do not require changes in the applications. Data Abstraction Data abstraction in databases hides the complexity of data from users, allowing them to interact with data without knowing its physical structure. Levels of Data Abstraction: Physical Level Describes how data is stored physically (e.g., data blocks, indexes). Logical Level Defines what data is stored and the relationships between data (schemas). View Level Offers a user-specific view of the database, simplifying interaction. Benefits of Data Abstraction: Simplifies user interaction with the database. Provides data independence by separating data from application logic. Enhances security by limiting data exposure to authorized views. Multi-User Environment Supports multiple users to access and manipulate data simultaneously without compromising data integrity or performance. Includes mechanisms to handle concurrency issues like locking and version control. Entity Relationship Model  Cardinality Cardinality refers to the number of entity instances that can be associated with another entity instance in a relationship. It defines the quantitative aspect of the relationship. Types of cardinality in between tables are: One-to-One One-to-Many Many-to-One Many-to-Many One-to-One In this type of cardinality mapping, an entity in A is connected to at most one entity in B. Or we can say that a unit or item in B is connected to at most one unit or item in A. Example: In a particular hospital, the surgeon department has one head of department. They both serve one-to-one relationships. One-to-Many In this type of cardinality mapping, an entity in A is associated with any number of entities in B. Or we can say that one unit or item in B can be connected to at most one unit or item in A. Example: In a particular hospital, the surgeon department has multiple doctors. They serve one-to-many relationships. Many-to-One In this type of cardinality mapping, an entity in A is connected to at most one entity in B. Or we can say a unit or item in B can be associated with any number (zero or more) of entities or items in A. Example: In a particular hospital, multiple surgeries are done by a single surgeon. Such a type of relationship is known as a many-to-one relationship. Many-to-Many In this type of cardinality mapping, an entity in A is associated with any number of entities in B, and an entity in B is associated with any number of entities in A. In a particular company, multiple people work on multiple projects. They serve many-to-many relationships.  The appropriate mapping cardinality for a particular relation set obviously depends on the real-world situation in which the relation set is modeled. If we have cardinality one-to-many or many to one then, we can mix relational tables with many involved tables. If the cardinality is many-to-many we cant mix any two tables. If we have a one-to-one relation and we have total participation of one entity then we can mix that entity with a relation table and if we have total participation of both entities then we can make one table by mixing two entities and their relation. What is Participation Constraints? Participation Constraints in database management refer to rules that determine the minimum and maximum participation of entities or relationships in a given relationship set. While partial participation permits discretionary involvement, total participation requires every entity in one set to take part in a relationship in another set. By maintaining consistency and enforcing business standards, these restrictions guarantee data integrity.  There are two types of participation constraints in database management systems, such as : Total Participation Partial Participation Total Participation Each entity in the entity set is involved in at least one relationship in a relationship set i.e. the number of relationship in every entity is involved is greater than 0. Consider two entities Employee and Department related via Works_For relationship. Now, every Employee works in at least one department therefore an Employee entity exist if it has at least one Works_For relationship with Department entity. Thus the participation of Employee in Works_For is total relationship. Total Participation is represented by double line in ER diagram. Partial Participation Each entity in entity set may or may not occur in at least one relationship in a relationship set. For example: Consider two entities Employee and Department and they are related to each other via Manages relationship. An Employee must manage a Department, he or she could be the head of the department. But not every Employee in the company manages the department. So, participation of employee in the Manages relationship type is partial i.e. only a particular set of Employees will manage the Department but not all. Enhanced Entity-Relationship (EER) Model ER Diagram ER diagram stands for Entity Relationship diagram. When we draw the relationships between entities using a diagram, then it is called an entity relationship diagram. The ER diagram is just for understanding the purpose of the database administrator. We cannot use the ER diagram directly on the computer. ER diagrams are converted into the tabular form then they are inserted into the computer using any query language. In ER diagrams, we use attributes, entities, and the relationships between entities. We use an oval shape to represent the entity and a diamond shape to represent the relationships between entities. Enhanced ER Diagram It is getting harder and harder to apply the conventional ER paradigm for database modeling as data complexity rises today. The existing ER model needs to be enhanced or improved in order for it to better handle the complicated application in order to reduce the modeling complexity. The requirements and complexity of complicated databases are represented using enhanced entity-relationship diagrams, which are sophisticated database diagrams very similar to standard ER diagrams. The SubClass and SuperClass, Specialization and Generalization, Union or Category, Aggregation, etc., are displayed using this diagrammatic style. Generalization Generalization is the process of extracting common properties from a set of entities and creating a generalized entity from it. It is a bottom-up approach in which two or more entities can be generalized to a higher-level entity if they have some attributes in common. For Example, STUDENT and FACULTY can be generalized to a higher-level entity called PERSON as shown in Figure 1. In this case, common attributes like P_NAME, and P_ADD become part of a higher entity (PERSON), and specialized attributes like S_FEE become part of a specialized entity (STUDENT). Generalization is also called as ‘ Bottom-up approach”. Specialization In specialization, an entity is divided into sub-entities based on its characteristics. It is a top-down approach where the higher-level entity is specialized into two or more lower-level entities. For Example, an EMPLOYEE entity in an Employee management system can be specialized into DEVELOPER, TESTER, etc. as shown in Figure 2. In this case, common attributes like E_NAME, E_SAL, etc. become part of a higher entity (EMPLOYEE), and specialized attributes like TES_TYPE become part of a specialized entity (TESTER). Specialization is also called as ” Top-Down approch”.  Inheritance: It is an important feature of generalization and specialization  Generalization and Specialization These are two normal kinds of relationships that were added to the normal ER model for enhancement. These are inspired by the object-oriented paradigm, where we divide the code into classes and objects, and in the same way, we have divided entities into subclass and superclasses. Specialized classes are called subclasses, and generalized classes are called superclasses or base classes. We can learn the concept of subclass by 'IS-A' analysis. For example, 'Laptop IS-A computer.' Or 'Clerk IS-A employee.'  In this relationship, one entity is a subclass or superclass of another entity. For example, in a university, a faculty member or clerk is a specialized class of employees. So an employee is a generalized class, and all others are its subclass. We can draw the ER diagram for these relationships. Let's suppose we have a superclass Employee and subclasses as a clerk, engineer, and lab assistant.  Aggregation In aggregation, the relation between two entities is treated as a single entity. In aggregation, relationship with its corresponding entities is aggregated into a higher level entity. For example: Center entity offers the Course entity act as a single entity in the relationship which is in a relationship with another entity visitor. In the real world, if a visitor visits a coaching center then he will never enquiry about the Course only or just about the Center instead he will ask the enquiry about both. EER DIAGRAM EXAMPLE What is Entity? An entity is referred to as an object or thing that exists in the real world. For example, customer, car, pen, etc. Attributes An entity has some attributes which depict the entity's characteristics. For example, an entity "Student" has attributes such as "Student_roll_no", "Student_name", "Student_subject", and "Student_marks". Kinds of Entity: There are two kinds of entities, which are as follows: 1. Tangible Entity: It is an entity in DBMS, which is a physical object that we can touch or see. In simple words, an entity that has a physical existence in the real world is called a tangible entity. For example, in a database, a table represents a tangible entity because it contains a physical object that we can see and touch in the real world. It includes colleges, bank lockers, mobiles, cars, watches, pens, paintings, etc. 2. Intangible Entity: It is an entity in DBMS, which is a non-physical object that we cannot see or touch. In simple words, an entity that does not have any physical existence in the real world is known as an intangible entity. For example, a bank account logically exists, but we cannot see or touch it. Entity Type: An entity type is a general classification or category that defines a group of similar entities. It is an abstraction that represents the properties and structure that all instances of the type share. Example: "Student" and "Course" are entity types that define characteristics (attributes) like: Student: Name, ID Course: Code, Title, Credits  An entity type acts as a template or blueprint for the entities. Entities are specific instances of an entity type.  Example Entity Type: "Car" (with attributes like Brand, Model, Year) Entity: A specific car, such as "Toyota Camry, 2020" Entity Set An entity set is a group of entities of the same entity type. For example, an entity set of students, an entity set of motorbikes, an entity of smartphones, an entity of customers, etc. Entity sets can be classified into two types: 1. Strong Entity Set: In a DBMS, a strong entity set consists of a primary key. For example, an entity of motorbikes with the attributes, motorbike's registration number, motorbike's name, motorbike's model, and motorbike's colour. Example of Entity Relationship Diagram representation of the above strong entity set: 2. Weak Entity Set: In a DBMS, a weak entity set does not contain a primary key. For example, An entity of smartphones with its attributes, phone's name, phone's colour, and phone's RAM. Below is the representation of a weak entity set in tabular form: Example of Entity Relationship Diagram representation of the above weak entity set: