DBMS UNIT-1_Pdf PDF

Noida Institute of Engineering and Technology, Greater Noida Introduction Unit: 1 DBMS ACSE0506 Sana Anjum Assistant Professor CSE...

Noida Institute of Engineering and Technology, Greater Noida Introduction Unit: 1 DBMS ACSE0506 Sana Anjum Assistant Professor CSE Course Details (B Tech Vth Sem) Department 6/15/2024 Sana Anjum DBMS Unit-1 1  Faculty Introduction Sana Anjum is an Assistant Professor at Computer Science and Engineering Department, Noida Institute of Engineering and Technology, Gr Noida. She has done MTech from Dr. APJ Abdul Kalam Technical University, Lucknow Her area of research includes distributed system, Database, Computer Networks and Internet of Things. She has published research paper in reputed journal and conference proceedings. Teaching Experience : 5 years 9/29/2024 Sana Anjum DBMS Unit-1 2  Evaluation Scheme 9/29/2024 3 Sana Anjum DBMS Unit-1  Syllabus 9/29/2024 Sana Anjum DBMS Unit-1 4  Syllabus 9/29/2024 Sana Anjum DBMS Unit-1 5  Branch wise Applications There are various application of DBMS in different fields like Railway Reservation System, Library Management System, Banking, Universities and colleges, Credit card transactions etc 9/29/202 Sana Anjum DBMS Unit-1 6 4  Course Objective The objective of course is to present an introduction to database management systems, with an emphasis on how to organize, maintain and retrieve-efficiently, and effectively –information in relational and non-relational Database. List and explain the fundamental concepts of a relational database system Knowledge of DBMS, both in terms of use and implementation/design. Experience with SQL and Manipulate a database using SQL Increased proficiency with the programming language C++. Experience working as part of team v Experience with analysis and design of (DB) software Assess the quality and ease of use of data modeling and diagramming tools. 9/29/2024 Sana Anjum DBMS Unit-1 7  Course Outcomes 9/29/2024 Sana Anjum DBMS Unit-1 8  Program Outcomes Engineering Graduates will be able to: 1. Engineering knowledge: Apply the knowledge of mathematics, science, engineering fundamentals, and an engineering specialization to the solution of complex engineering problems. 2. Problem analysis: Identify, formulate, review research literature, and analyze complex engineering problems reaching substantiated conclusions using first principles of mathematics, natural sciences, and engineering sciences. 3. Design/development of solutions: Design solutions for complex engineering problems and design system components or processes that meet the specified needs with appropriate consideration for the public health and safety, and the cultural, societal, and environmental considerations. 4. Conduct investigations of complex problems: Use research-based knowledge and research methods including design of experiments, analysis and interpretation of data, and synthesis of the information to provide valid conclusions. 9/29/202 Sana Anjum DBMS 9 4  Program Outcomes Engineering Graduates will be able to: 5. Modern tool usage: Create, select, and apply appropriate techniques, resources, and modern engineering and IT tools including prediction and modeling to complex engineering activities with an understanding of the limitations. 6. The engineer and society: Apply reasoning informed by the contextual knowledge to assess societal, health, safety, legal and cultural issues and the consequent responsibilities relevant to the professional engineering practice. 7. Environment and sustainability: Understand the impact of the professional engineering solutions in societal and environmental contexts, and demonstrate the knowledge of, and need for sustainable development. 8. Ethics: Apply ethical principles and commit to professional ethics and responsibilities and norms of the engineering practice. 9/29/202 Sana Anjum DBMS 10 4  Program Outcomes Engineering Graduates will be able to: 9. Individual and team work: Function effectively as an individual, and as a member or leader in diverse teams, and in multidisciplinary settings. 10. Communication: Communicate effectively on complex engineering activities with the engineering community and with society at large, such as, being able to comprehend and write effective reports and design documentation, make effective presentations, and give and receive clear instructions. 11. Project management and finance: Demonstrate knowledge and understanding of the engineering and management principles and apply these to one’s own work, as a member and leader in a team, to manage projects and in multidisciplinary environments. 12. Life-long learning: Recognize the need for, and have the preparation and ability to engage in independent and life-long learning in the broadest context of technological change. 9/29/202 Sana Anjum DBMS 11 4  COs and POs Mapping Database Management Systems(ACSE0506) Year of Study: 2023-24 CO PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO12 ACSE0506.1 3 3 3 3 2 1 - 1 2 - 2 2 ACSE0506.2 3 2 3 3 2 2 - 2 1 2 2 2 ACSE0506.3 3 3 2 3 3 2 - 2 1 - 2 2 ACSE0506.4 3 2 3 2 2 2 - - 2 2 2 3 ACSE0506.5 3 2 2 2 2 2 - - 1 2 1 2 Average 3.0 2.4 2.6 2.6 2.2 1.8 - 1.8 1.4 2.0 1.8 2.2 9/29/202 Sana Anjum DBMS 12 4  Program Specific Outcomes (PSOs) On successful completion of graduation degree the Computer Science & Engineering graduates will be able to: PSO1: Identify, analyze real world problems and design their ethical solutions using artificial intelligence, robotics, virtual/augmented reality, data analytics, block chain technology, and cloud computing. PSO2: Design and develop the hardware sensor devices and related interfacing software systems for solving complex engineering problems. PSO3: Understand inter-disciplinary computing techniques and to apply them in the design of advanced computing. PSO4: Conduct investigation of complex problems with the help of technical, managerial, leadership qualities, and modern engineering tools provided by industry-sponsored laboratories 9/29/202 Sana Anjum DBMS 13 4  COs and PSOs Mapping Program SpecificOutcomes PSO1 PSO2 PSO3 PSO4 ACSE0506.1 3 1 3 3 ACSE0506.2 2 3 1 3 ACSE0506.3 2 3 2 2 ACSE0506.4 2 3 2 2 ACSE0506.5 2 3 2 2 Average 3 1.6 2.4 2.2 9/29/202 Sana Anjum DBMS 14 4  Program Educational Objectives (PEOs) The graduates will be: PEO1: To have an excellent scientific and engineering breadth so as to comprehend, analyze, design and provide sustainable solutions for real-life problems using state-of-the- art technologies. PEO2: To have a successful career in industries, to pursue higher studies or to support entrepreneurial endeavors and to face the global challenges. PEO3: To have an effective communication skill, professional attitude, ethical values and a desire to learn specific knowledge in emerging trends, technologies for research, innovation and product development and contribution to society. PEO4: To have life-long learning for up-skilling and re-skilling for a successful professional career as an engineer, scientist, entrepreneur or bureaucrat for the betterment of the society. 9/29/202 Sana Anjum DBMS 15 4  Result Analysis 9/29/202 Sana Anjum DBMS 16 4  Question Paper Template 9/29/202 Sana Anjum DBMS 17 4  Question Paper Template SECTION – A CO 1. Attempt all parts- [10×1=10] 1-a. Question- (1) 1-b. Question- (1) 1-c. Question- (1) 1-d. Question- (1) 1-e. Question- (1) 1-f. Question- (1) 1-g. Question- (1) 1-h. Question- (1) 1-i. Question- (1) 1-j. Question- (1) 2. Attempt all parts- [5×2=10] CO 2-a. Question- (2) 2-b. Question- (2) 2-c. Question- (2) 2-d. Question- (2) 2-e. Question- (2) SECTION – B CO 3. Answer any five of the following- [5×6=30] 3-a. Question- (6) 3-b. Question- (6) 3-c. Question- (6) 3-d. Question- (6) 3-e. Question- (6) 3-f. Question- (6) 3-g. Question- (6) 9/29/202 Sana Anjum DBMS 18 18 4  Question Paper Template SECTION – C CO 4 Answer any one of thefollowing- [5×10=50] 4-a. Question- (10) 4-b. Question- (10) 5. Answer any one of thefollowing- 5-a. Question- (10) 5-b. Question- (10) 6. Answer any one of thefollowing- 6-a. Question- (10) 6-b. Question- (10) 7. Answer any one of thefollowing- 7-a. Question- (10) 7-b. Question- (10) 8. Answer any one of thefollowing- 8-a. Question- (10) 8-b. Question- (10) 9/29/202 Sana Anjum DBMS 19 4  Prerequisite and Recap There is No prerequisite for learning DBMS from scratch although having basic knowledge of discrete mathematics and data structure is added advantage. Having knowledge of basic mathematics like - SUM, DIFFERENCE, AVERAGE, MEAN, MEDIAN, MODE, etc will definitely be a plus point. Having knowledge on Set Theory will help. The proper understanding of data structures (B and B+ trees) will help you to understand the DBMS quickly. 9/29/202 Sana Anjum DBMS 20 4  Brief Introduction about the Subject 1. A database management system (DBMS) refers to the technology for creating and managing databases. DBMS is a software tool to organize (create, retrieve, update, and manage) data in a database. 2. The main aim of a DBMS is to supply a way to store up and retrieve database information that is both convenient and efficient. By data, we mean known facts that can be recorded and that have embedded meaning. 3. Usually, people use software such as DBASE IV or V, Microsoft ACCESS, or EXCEL to store data in the form of a database. A datum is a unit of data. Meaningful data combined to form information. Hence, information is interpreted data - data provided with semantics. MS. ACCESS is one of the most common examples of database management software. Video Link: www.youtube.com/watch?v=3EJlovevfcA&t=436s 9/29/202 Sana Anjum DBMS 21 4  Content – Unit 1 Introduction: An overview of database management system, database system Vs file system, Database system concept and architecture, data model schema and instances, data independence and database language and interfaces, data definitions language, DML. Data Modeling using the Entity Relationship Model: ER model concepts, notation for ER diagram, mapping constraints, Concepts of Super Key, candidate key, primary key, Generalization, aggregation, reduction of an ER diagrams to tables, extended ER model, relationship of higher degree. 9/29/202 Sana Anjum DBMS 22 4  Unit Objective 1. The main objectives of database management system are data availability, data integrity, data security, and data independence. 2. DDL stands for Data Definition Language. DML stands for Data Manipulation Language. DDL statements are used to create database, schema, constraints, users, tables etc. DML statement is used to insert, update or delete the records. 3. ER diagrams are used to analyze existing databases to find and resolve problems in logic or deployment. Drawing the diagram should reveal where it's going wrong. Business information systems: The diagrams are used to design or analyze relational databases used in business processes. 9/29/202 Sana Anjum DBMS 23 4  Introduction Data is an unprocessed set of inputs, which is not self-explanatory. We need to comprehend the idea of data. Data is a collection of facts about the object of interest. For e.g. data about a student would include information like name, address, roll no, subjects, marks etc. Information causes us with answers to questions like who, when, what, where and so forth. Union of data and information drives us to address the how question and take business choices. This is referred to as Knowledge. 9/29/202 Sana Anjum DBMS 24 4  Introduction A Database is a shared collection of logically related data and description of these data, designed to meet the information needs of an organization. A Database Management System(DBMS) is a software system that enables users to define, create, maintain, and control access to the database. Database Systems typically have high costs and require high-end hardware configurations. An Application Program communicates with a database by issuing an appropriate request (typically a SQL statement) 9/29/2024 Sana Anjum DBMS 25  What is DBMS A very large, integrated collection of related data. Models real-world enterprise. – Entities (e.g., students, courses) – Relationships (e.g., Madonna is taking CS564) A Database Management System (DBMS) is a software package designed to define,construct and manipulate database. Database + Database Management System= Database System. 9/29/202 Sana Anjum DBMS 26 4  Advantage of DBMS ⚫ Controlling Redundancy ⚫ Restricting Unauthorized Access ⚫ Provide storage structures for efficient query processing ⚫ Backup and Recovery ⚫ Support concurrent access ⚫ Multiple user interfaces ⚫ Represent complex relationship among data ⚫ Enforcing Integrity constraints 9/29/2024 Sana Anjum DBMS 27  Files vs. DBMS FILE SYSTEM DBMS Used to manage and organise the files stored in A software to store and retrieve the user’s data the hard disk of the computer Redundant data is present No presence of redundant data Query processing is not so efficient Query processing is efficient Data consistency is low Due to the process of normalisation, the data consistency is high Less complex, does not support complicated More complexity in managing the data, easier to transactions implement complicated transactions Less security Supports more security mechanisms Less expensive in comparison to DBMS Higher cost than the File system Does not support crash recovery Crash recovery mechanism is highly supported 9/29/2024 Sana Anjum DBMS 28  Purpose of Database Systems In the early days, database applications were built directly on top of file systems Drawbacks of using file systems to store data: Data redundancy and inconsistency Difficulty in accessing data Data isolation — multiple files and formats Integrity problems Integrity constraints (e.g. account balance > 0) become “buried” in program code rather than being stated explicitly. Hard to add new constraints or change existing ones 9/29/2024 Sana Anjum DBMS 29  Database Users and Administrator Users are differentiated by the way they expect to interact with the system Application programmers – interact with system through DML calls Sophisticated users – form requests in a database query language Specialized users – write specialized database applications that do not fit into the traditional data processing framework Naive users – invoke one of the permanent application programs that have been written previously Database Administrator Coordinates all the activities of the database system. Has a good understanding of the enterprise’s information resources and needs. 9/29/202 Sana Anjum DBMS 30 4  Structure of DBMS Structure of Database Management System is also referred to as Overall System Structure or Database Architecture but it is different from the tier architecture of Database. The database system is divided into three components: Query Processor Storage Manager Disk Storage Query Processor: It interprets the requests (queries) received from end user via an application program into instructions. Query Processor contains the following components – 9/29/2024 Sana Anjum DBMS 31  Structure of DBMS Storage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system.  The storage manager is responsible to the following tasks:  Interaction with the file manager  Efficient storing, retrieving and updating of data Disk Storage: It contains the following components – Data Files, Data Dictionary, Indices 9/29/202 Sana Anjum DBMS 32 4  Structure of DBMS The structure of a DBMS can be divided into three main components: The Internal Level - This level represents the physical storage of data in the database. The Conceptual Level - This level represents the logical view of the database. The External Level - This level represents the user’s view of the database. 9/29/202 Sana Anjum DBMS 33 4  Database Architecture (CO1) The architecture of a database systems is greatly influenced by the underlying computer system on which the database is running:  Centralized  Client-server  Parallel (multiple processors and disks)  Distributed 9/29/202 Sana Anjum DBMS 34 4  DBMS Architecture There are several types of DBMS Architecture that we use according to the usage requirements. Types of DBMS Architecture are: 1-Tier Architecture (database directly available to users) 2-Tier Architecture 3-Tier Architecture DBMS 1-Tier Architecture 9/29/202 Sana Anjum DBMS 35 4  DBMS Architecture Advantages of 1-Tier Architecture: Simple Architecture Cost-Effective Easy to Implement 2-Tier Architecture The 2-tier architecture is similar to a basic client-server model. DBMS 2-Tier Architecture 9/29/202 Sana Anjum DBMS 36 4  DBMS Architecture Advantage of 2-Tier Architecture is that maintenance and understanding are easier, and compatible with existing systems. Easy to Access Scalable Low Cost Easy Deployment Simple 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 server. 9/29/202 Sana Anjum DBMS 37 4  DBMS Architecture Advantages of 3-Tier Architecture Enhanced scalability Data Integrity Security Disadvantages of 3-Tier Architecture More Complex Difficult to Interact DBMS 3-Tier Architecture 9/29/202 Sana Anjum DBMS 38 4  Short Quiz 1. What is the full form of DBMS? a) Data of Binary Management System b) Database Management System c) Database Management Service d) Data Backup Management System 2. What is a database? a)Organized collection of information that cannot be accessed, updated, and managed b) Collection of data or information without organizing c)Organized collection of data or information that can be accessed, updated, and managed d) Organized collection of data that cannot be updated 9/29/202 Sana Anjum DBMS 39 4  Topic – Data models Objective 1. Data Model gives us an idea that how the final system will look like after its complete implementation. 2. It defines the data elements and the relationships between the data elements. 3. Data Models are used to show how data is stored, connected, accessed and updated in the database management system. 9/29/202 Sana Anjum DBMS 40 4  Data Models A data model is a collection of concepts for describing data. Categories of Data Models :- – High level or Conceptual Data Model Concept of entities, attributes and relationships – Low level or physical Data Model Details of how data is stored in computer – Representational Data Model Traditional commercial DBMS so called legacy data models- network or hierarchical models. 9/29/202 Sana Anjum DBMS 41 4  Database Design (CO1) Conceptual design: (ER Model is used at this stage.) – What are the entities and relationships in the enterprise? – What information about these entities and relationships should we store in the database? – What are the integrity constraints or business rules that hold? – A database `schema’ in the ER Model can be represented pictorially (ER diagrams). – Can map an ER diagram into a relational schema. 9/29/202 Sana Anjum DBMS 42 4  Schemas and Instances Similar to types and variables in programming languages Schema – the logical structure of the database – 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 Instance – the actual content of the database at a particular point in time – Analogous to the value of a variable 9/29/202 Sana Anjum DBMS 43 4  Levels of Abstraction Physical Schema/Level: describes how a record (e.g., customer) is stored. Logical Schema/Level: describes data stored in database, and the relationships among the data. type customer = record customer_id : string; customer_name : string; customer_street : string; customer_city : string; end; External Schema/Level: application programs hide details of data types. Views can also hide information (such as an employee’s salary) for security purposes. 9/29/202 Sana Anjum DBMS 44 4  Levels of Abstraction Three Schema Architecture for a Database System 9/29/202 Sana Anjum DBMS 45 4  Example: University Database  Physical schema:  Relations stored as unordered files.  Index on first column of Students.  Conceptual schema:  Students(sid: string, name: string, login: string, age: integer, gpa:real)  Courses(cid: string, cname:string, credits:integer)  Enrolled(sid:string, cid:string, grade:string)  External Schema (View):  View1- Course_info(cid:string,enrollment:integer)  View2-studeninfo(id:int. name:string) 9/29/202 Sana Anjum DBMS 46 4  Data Independence Data Independence is defined as a property of DBMS that helps you to change the Database schema at one level of a database system without requiring to change the schema at the next higher level. In DBMS there are two types of data independence – Physical data independence – Logical data independence. One of the most important benefits of using a DBMS! 9/29/202 Sana Anjum DBMS 47 4  Short Quiz 1. Which of the following is known as a set of entities of the same type that share same properties, or attributes? a) Relation set b) Tuples c) Entity set d) Entity Relation model 2. The values appearing in given attributes of any tuple in the referencing relation must likewise occur in specified attributes of at least one tuple in the referenced relation, according to integrity constraint. a) Referential b) Primary c) Referencing d) Specific 9/29/202 Sana Anjum DBMS Unit-1 48 4  Topic- Database Language Objective 1. DDL stands for Data Definition Language. DML stands for Data Manipulation Language. DDL statements are used to create database, schema, constraints, users, tables etc. DML statement is used to insert, update or delete the records. 2. DCL (Data Control Language) includes commands like GRANT and REVOKE, which are useful to give “rights & permissions.” 3. Transaction control language or TCL commands deal with the transaction within the database. 4. Data Query Language (DQL) is used to fetch the data from the database. 9/29/202 Sana Anjum DBMS Unit-1 49 4  DATA BASE LANGUAGE A DBMS has appropriate languages and interfaces to express database queries and updates. Two classes of languages – Procedural – user specifies what data is required and how to get those data – Declarative (nonprocedural) – user specifies what data is required without specifying how to get those data SQL is the most widely used query language 9/29/202 Sana Anjum DBMS 50 4  DATA BASE LANGUAGE 9/29/202 Sana Anjum DBMS 51 4  Data Definition Language (DDL) 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. – Truncate: It is used to remove all records from a table. – Rename: It is used to rename an object. – Comment: It is used to comment on the data dictionary. 9/29/202 Sana Anjum DBMS 52 4  Data Manipulation Language (DML) DML stands for Data Manipulation Language. 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. – Merge: It performs UPSERT operation, i.e., insert or update operations. 9/29/202 Sana Anjum DBMS 53 4  Data Control Language (DCL) DCL stands for Data Control Language. It is used to retrieve the stored or saved data. The DCL execution is transactional. It also has rollback parameters. Here are some tasks that come under DCL: – Grant: It is used to give user access privileges to a database. – Revoke: It is used to take back permissions from the user. There are the following operations which have the authorization of Revoke: CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT. 9/29/202 Sana Anjum DBMS 54 4  Transaction Control Language (TCL) TCL is used to run the changes made by the DML statement. TCL can be grouped into a logical transaction. 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. 9/29/202 Sana Anjum DBMS 55 4  Short Quiz 1. The ability to query data, as well as insert, delete, and alter tuples, is offered by a) TCL (Transaction Control Language) b) DCL (Data Control Language) c) DDL (Data Definition Langauge) d) DML (Data Manipulation Langauge) 2. Which command is used to remove a relation from an SQL? a) Drop table b) Delete c) Purge d) Remove 9/29/202 Sana Anjum DBMS 56 4  Topic- Relational Model and SQL Objective 1. The purpose of the relational model is to provide a declarative method for specifying data and queries: users directly state what information the database contains and what information they want from it. 2. One of the major goals of Object relational data model is to close the gap between relational databases and the object oriented practises frequently used in many programming languages such as C++, C#, Java etc. 3. Be able to write SQL statements that edit existing data. Be able to write SQL statements that create database objects. 4. Understand the structure and design of relational databases. Understand the importance and major issues of database security and the maintenance of data integrity. 9/29/202 8/24/2022 Sana Anjum DBMS 57 53 4  Relational Model Example of tabular data in the relational model Attributes 9/29/202 Sana Anjum DBMS 58 4  Introduction of ER Model A database can be modeled as: – a collection of entities, – relationship among entities. An entity is an object that exists and is distinguishable from other objects. – Example: specific person, company, event, plant Entities have attributes – Example: people have names and addresses An entity set is a set of entities of the same type that share the same properties. – Example: set of all persons, companies, trees, holidays 9/29/202 Sana Anjum DBMS 59 4  Entity Sets customer and loan customer_id customer_ customer_ customer_ loan_ amount name street city number 9/29/202 Sana Anjum DBMS 60 4  Attributes An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set. Example: customer = (customer_id, customer_name, customer_street, customer_city ) loan = (loan_number, amount ) In ER diagram, attribute is represented by an oval. Domain – the set of permitted values for each attribute 9/29/202 Sana Anjum DBMS 61 4  Attribute Type Key Attribute – The attribute which uniquely identifies each entity in the entity set is called key attribute. For example, Roll_No will be unique for each student. Composite Attribute – An attribute composed of many other attribute is called as composite attribute. For example, Address attribute of student Entity type consists of Street, City, State, and Country. 9/29/202 Sana Anjum DBMS 62 4  Attribute Type Multivalued Attribute – An attribute consisting more than one value for a given entity. For example, Phone_No (can be more than one for a given student). In ER diagram, multivalued attribute is represented by double oval. Derived Attribute – An attribute which can be derived from other attributes of the entity type is known as derived attribute. e.g.; Age (can be derived from DOB). In ER diagram, derived attribute is represented by dashed oval. 9/29/202 Sana Anjum DBMS 63 4  Attribute Type The complete entity type Student with its attributes can be represented as: 9/29/202 Sana Anjum DBMS 64 4  Relationship Type A relationship type represents the association between entity types. For example, ‘Enrolled in’ is a relationship type that exists between entity type Student and Course. In ER diagram, relationship type is represented by a diamond and connecting the entities with lines. 9/29/202 Sana Anjum DBMS 65 4  Relationship Set A set of relationships of same type is known as relationship set. The following relationship set depicts S1 is enrolled in C2, S2 is enrolled in C1 and S3 is enrolled in C3. 9/29/202 Sana Anjum DBMS 66 4  Degree of Relationship Set The number of different entity sets participating in a relationship set is called as degree of a relationship set. – Unary Relationship – Binary Relationship – n-ary Relationship 9/29/202 Sana Anjum DBMS 67 4  Unary Relationship When there is only ONE entity set participating in a relation, the relationship is called as unary relationship. For example, one person is married to only one person. 9/29/202 Sana Anjum DBMS 68 4  Binary Relationship When there are TWO entities set participating in a relation, the relationship is called as binary relationship. For example, Student is enrolled in Course. 9/29/202 Sana Anjum DBMS 69 4  n-ary Relationship When there are n entities set participating in a relation, the relationship is called as n-ary relationship. 9/29/202 Sana Anjum DBMS 70 4  Mapping Cardinality The number of times an entity of an entity set participates in a relationship set is known as cardinality. Cardinality can be of different types: – One to one – One to many – Many to one – Many to many 9/29/202 Sana Anjum DBMS 71 4  One to one When each entity in each entity set can take part only once in the relationship, the cardinality is one to one. Let us assume that a male can marry to one female and a female can marry to one male. So the relationship will be one to one. Using Sets, it can be represented as: 9/29/202 Sana Anjum DBMS 72 4  Many to one When entities in one entity set can take part only once in the relationship set and entities in other entity set can take part more than once in the relationship set, cardinality is many to one Using Sets, it can be represented as: 9/29/202 Sana Anjum DBMS 73 4  Many to many When entities in all entity sets can take part more than once in the relationship cardinality is many to many. Let us assume that a student can take more than one course and one course can be taken by many students. So the relationship will be many to many. Using sets, it can be represented as: 9/29/202 Sana Anjum DBMS 74 4  Participation Constraint Total Participation – Each entity in the entity set must participate in the relationship. Partial Participation – The entity in the entity set may or may NOT participate in the relationship. 9/29/202 Sana Anjum DBMS 75 4  Participation Constraint (contd..) Using set, it can be represented as Every student in Student Entity set is participating in relationship but there exists a course C4 which is not taking part in the relationship. 9/29/202 Sana Anjum DBMS 76 4  Weak Entity Type & Identifying Relationship An entity type has a key attribute which uniquely identifies each entity in the entity set. But there exists some entity type for which key attribute can’t be defined. These are called Weak Entity type. For example, A company may store the information of dependents (Parents, Children, Spouse) of an Employee. But the dependents don’t have existence without the employee. So Dependent will be weak entity type and Employee will be Identifying Entity type for Dependent. 9/29/202 Sana Anjum DBMS 77 4  Weak Entity Type & Identifying Relationship A weak entity type is represented by a double rectangle. The participation of weak entity type is always total. The relationship between weak entity type and its identifying strong entity type is called identifying relationship and it is represented by double diamond. 9/29/202 Sana Anjum DBMS 78 4  Example of ER Diagram 9/29/202 Sana Anjum DBMS 79 4  Example of ER Diagram Draw an ER Diagram: Consider the following set of requirements for a Bank Database: "A large bank has several branches at different places. Each branch maintains the account details of the customers. The customers may open joint as well as single accounts. The bank also provides the loan to the customer for different purposes. Bank keeps record of each transaction by the customer to his account. All of the branches have employees and some employees are managers." 9/29/2024 Sana Anjum DBMS 80  Keys in DBMS Keys help you uniquely identify a row in a table by a combination of one or more columns in that table. Example:- Employee ID FirstName LastName 11 Andrew Johnson 22 Tom Wood 33 Alex Hale In the above-given example, employee ID is a primary key because it uniquely identifies an employee record. In this table, no other employee can have the same employee ID. 9/29/2024 Sana Anjum DBMS 81  Why we need a Key? Here are some reasons for using SQL key in the DBMS system. Keys help you to identify any row of data in a table. In a real-world application, a table could contain thousands of records. Moreover, the records could be duplicated. Keys in RDBMS ensure that you can uniquely identify a table record despite these challenges. Allows you to establish a relationship between and identify the relation between tables Help you to enforce identity and integrity in the relationship. 9/29/202 Sana Anjum DBMS 82 4  Types of Keys in DBMS There are mainly Eight different types of Keys in DBMS and each key has it’s different functionality: – Super Key – A super key is a group of single or multiple keys which identifies rows in a table. – Primary Key – is a column or group of columns in a table that uniquely identify every row in that table. – Candidate Key – is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. – Alternate Key – is a column or group of columns in a table that uniquely identify every row in that table. 9/29/202 Sana Anjum DBMS 83 4  Types of Keys in DBMS (contd..) – Foreign Key – is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. – Compound Key – has two or more attributes that allow you to uniquely recognize a specific record. – Composite Key – is a combination of two or more columns that uniquely identify rows in a table. – Surrogate Key – An artificial key which aims to uniquely identify each record is called a surrogate key. These kind of key are unique because they are created when you don’t have any natural primary key. 9/29/202 Sana Anjum DBMS 84 4  Super key A superkey is a group of single or multiple keys which identifies rows in a table. A Super key may have additional attributes that are not needed for unique identification. Example: EmpSSN EmpNum Empname 9812345098 AB05 Shown 9876512345 AB06 Roslyn 199937890 AB07 James In the above-given example, EmpSSN and EmpNum name are superkeys. 9/29/202 Sana Anjum DBMS 85 4  Primary Key PRIMARY KEY in DBMS is a column or group of columns in a table that uniquely identify every row in that table. The Primary Key can’t be a duplicate meaning the same value can’t appear more than once in the table. A table cannot have more than one primary key. Example: StudID Roll No First Name LastName Email 1 11 Tom Price [email protected] 2 12 Nick Wright [email protected] 3 13 Dana Natan [email protected] In the following example, StudID is a Primary Key. 9/29/202 Sana Anjum DBMS 86 4  Primary Key (contd..) Rules for defining Primary key: Two rows can’t have the same primary key value It must for every row to have a primary key value. The primary key field cannot be null. The value in a primary key column can never be modified or updated if any foreign key refers to that primary key. 9/29/202 Sana Anjum DBMS 87 4  Alternate key ALTERNATE KEYS is a column or group of columns in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called an Alternate Key. Example: StudID Roll No First Name LastName Email 1 11 Tom Price [email protected] 2 12 Nick Wright [email protected] 3 13 Dana Natan [email protected] In this table, StudID is the primary key, Roll No, Email becomes the alternative key. 9/29/202 Sana Anjum DBMS 88 4  Candidate Key Properties of Candidate key: It must contain unique values Candidate key in SQL may have multiple attributes Must not contain null values It should contain minimum fields to ensure uniqueness Uniquely identify each record in a table 9/29/202 Sana Anjum DBMS 89 4  Foreign key FOREIGN KEY is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity Example: DeptCode DeptName 001 Science 002 English 005 Computer Teacher ID Fname Lname B002 David Warner B017 Sara Joseph B009 Mike Brunton 9/29/202 Sana Anjum DBMS 90 4  Foreign key (contd..) In this example, we have two tables, teacher and department in a school. However, there is no way to see which teacher work in which department. In this table, adding the foreign key Deptcode to the Teacher, we can create a relationship between the two tables. Teacher ID DeptCode Fname Lname B002 002 David Warner B017 002 Sara Joseph B009 001 Mike Brunton This concept is also known as Referential Integrity. 9/29/202 Sana Anjum DBMS 91 4  Compound key COMPOUND KEY has two or more attributes that allow you to uniquely recognize a specific record. However, when combined with the other column or columns the combination of composite keys become unique. Example: OrderNo PorductID Product Name Quantity B005 JAP102459 Mouse 5 B005 DKT321573 USB 10 B005 OMG446789 LCD Monitor 20 B004 DKT321573 USB 15 B002 OMG446789 Laser Printer 3 Here a compound key of Order ID and Product ID could be used as it uniquely identified each record. 9/29/202 Sana Anjum DBMS 92 4  Composite key COMPOSITE KEY is a combination of two or more columns that uniquely identify rows in a table. The combination of columns guarantees uniqueness, though individually uniqueness is not guaranteed. Hence, they are combined to uniquely identify records in a table. The difference between compound and the composite key is that any part of the compound key can be a foreign key, but the composite key may or maybe not a part of the foreign key. 9/29/202 Sana Anjum DBMS 93 4  Short Quiz Q. Given the basic ER and relational models, which of the following is INCORRECT? answer choices 1. An attribute of an entity can have more than one value. 2. An attribute of an entity can be composite. 3. In a row of a relational table, an attribute can have more than one value. 4. In a row of a relational table, an attribute can have exactly one value or a NULL value. Q. Which type of entity cannot exist in the database unless another type of entity also exists in the database, but does not require that the identifier of that other entity be included as part of its own identifier? 1. Weak entity 2. Strong entity 3. ID-dependent entity 4. ID- independent entity 9/29/202 Sana Anjum DBMS Unit -1 94 4  Generalization Generalization is the process of extracting common properties from a set of entities and create 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. 9/29/202 Sana Anjum DBMS Unit-1 95 4  Example: 9/29/202 Sana Anjum DBMS Unit-1 96 4  Specialization In specialization, an entity is divided into sub-entities based on their characteristics. It is a top-down approach where higher level entity is specialized into two or more lower level entities. For Example, EMPLOYEE entity in an Employee management system can be specialized into DEVELOPER, TESTER etc. as shown in Figure. 9/29/202 Sana Anjum DBMS Unit-1 97 4  Example: 9/29/202 Sana Anjum DBMS Unit-1 98 4  Aggregation An ER diagram is not capable of representing relationship between an entity and a relationship which may be required in some scenarios. In those cases, a relationship with its corresponding entities is aggregated into a higher level entity. For Example, Employee working for a project may require some machinery. So, REQUIRE relationship is needed between relationship WORKS_FOR and entity MACHINERY. Using aggregation, WORKS_FOR relationship with its entities EMPLOYEE and PROJECT is aggregated into single entity and relationship REQUIRE is created between aggregated entity and MACHINERY. 9/29/202 Sana Anjum DBMS Unit-1 99 4  Example: 9/29/202 Sana Anjum DBMS Unit-1 100 4  Reduction of an ER diagrams to tables Objectives 1. Mapping an Entity Relationship (ER) model gives a good overview of the design of a system with the goal of making the system easier to understand at a technical level. 2. The ER diagrams can be mapped to a relation schema, which means we can clearly display the relationship between its members. 3. An Entity Relationship (ER) Diagram is a type of flowchart that illustrates how “entities” such as people, objects or concepts relate to each other within a system. 9/29/2024 Sana Anjum DBMS Unit-1 101  Reduction of an ER diagrams to tables Rule-01 Rule-01: For Strong Entity Set With Only Simple Attributes A strong entity set with only simple attributes will require only one table in relational model. – Attributes of the table will be the attributes of the entity set. – The primary key of the table will be the key attribute of the entity set. Schema : Student ( Roll_no , Name , Sex ) 9/29/2024 Sana Anjum DBMS Unit-1 102  Rule-02 Rule-02: For Strong Entity Set With Composite Attributes A strong entity set with any number of composite attributes will require only one table in relational model. While conversion, simple attributes of the composite attributes are taken into account and not the composite attribute itself. Schema : Student ( Roll_no , First_name , Last_name , House_no , Street , City ) 9/29/2024 Sana Anjum DBMS Unit-1 103  Rule-02 Rule-03: For Strong Entity Set With Multi Valued Attributes A strong entity set with any number of multi valued attributes will require two tables in relational model. – One table will contain all the simple attributes with the primary key. – Other table will contain the primary key and all the multi valued attributes. Roll_no City Roll_no Mobile_no 9/29/2024 Sana Anjum DBMS Unit-1 104  RULE-04 Rule-04: Translating Relationship Set into a Table A relationship set will require one table in the relational model. Attributes of the table are- – Primary key attributes of the participating entity sets – Its own descriptive attributes if any. – Set of non-descriptive attributes will be the primary key. Schema : Works in ( Emp_no , Dept_id , since ) 9/29/202 Sana Anjum DBMS Unit-1 105 4  RULE-05 Rule-05: For Binary Relationships With Cardinality Ratios The following four cases are possible- – Case-01: Binary relationship with cardinality ratio m:n – Case-02: Binary relationship with cardinality ratio 1:n – Case-03: Binary relationship with cardinality ratio m:1 – Case-04: Binary relationship with cardinality ratio 1:1 9/29/202 Sana Anjum DBMS Unit-1 106 4  RULE-05 (contd..) Case-01: For Binary Relationship With Cardinality Ratio m:n Here, three tables will be required- 1. A ( a1 , a2 ) 2. R ( a1 , b1 ) 3. B ( b1 , b2 ) 9/29/202 Sana Anjum DBMS Unit-1 107 4  RULE-05 (contd..) Case-02: For Binary Relationship With Cardinality Ratio 1:n Here, two tables will be required- 1. A ( a1 , a2 ) 2. BR ( a1 , b1 , b2 ) NOTE- Here, combined table will be drawn for the entity set B and relationship set R. 9/29/202 Sana Anjum DBMS Unit-1 108 4  RULE-05 (contd..) Case-03: For Binary Relationship With Cardinality Ratio m:1 Here, two tables will be required- 1. AR ( a1 , a2 , b1 ) 2. B ( b1 , b2 ) NOTE- Here, combined table will be drawn for the entity set A and relationship set R. 9/29/202 Sana Anjum DBMS Unit-1 109 4  RULE-05 (contd..) Case-04: For Binary Relationship With Cardinality Ratio 1:1 Here, two tables will be required. Either combine ‘R’ with ‘A’ or ‘B’ Way-01: 1. AR ( a1 , a2 , b1 ) 2. B ( b1 , b2 ) Way-02: 1. A ( a1 , a2 ) 2. BR ( a1 , b1 , b2 ) 9/29/202 Sana Anjum DBMS Unit-1 110 4  RULE-06 Rule-06: For Binary Relationship With Both Cardinality Constraints and Participation Constraints- Case-01: For Binary Relationship With Cardinality Constraint and Total Participation Constraint From One Side Case-02: For Binary Relationship With Cardinality Constraint and Total Participation Constraint From Both Sides- 9/29/202 Sana Anjum DBMS Unit-1 111 4  RULE-06 (contd..) Case-01: For Binary Relationship With Cardinality Constraint and Total Participation Constraint From One Side Because cardinality ratio = 1 : n , so we will combine the entity set B and relationship set R. Then, two tables will be required- 1. A ( a1 , a2 ) 2. BR ( a1 , b1 , b2 ) Because of total participation, foreign key a1 has acquired NOT NULL constraint, so it can’t be null now. 9/29/202 Sana Anjum DBMS Unit-1 112 4  RULE-06 (contd..) Case-02: For Binary Relationship With Cardinality Constraint and Total Participation Constraint From Both Sides If there is a key constraint from both the sides of an entity set with total participation, then that binary relationship is represented using only single table. Here, Only one table is required. 1. ARB ( a1 , a2 , b1 , b2 ) 9/29/202 Sana Anjum DBMS Unit-1 113 4  RULE-07 Rule-07: For Binary Relationship With Weak Entity Set Weak entity set always appears in association with identifying relationship with total participation constraint. Here, two tables will be required- 1. A ( a1 , a2 ) 2. BR ( a1 , b1 , b2 ) 9/29/202 Sana Anjum DBMS Unit-1 114 4  ER Diagram Examples 9/29/202 Sana Anjum DBMS Unit-1 115 4  ER Diagram For Department 9/29/202 Sana Anjum DBMS Unit-1 116 4  Extended ER Model Enhanced ERD are high level models that represent the requirements and complexities of complex database. In addition to ER model concepts EE-R includes −  Subclasses and Super classes.  Specialization and Generalization.  Category or union type.  Aggregation. 9/29/202 Sana Anjum DBMS Unit-1 117 4  Extended ER Model Subclasses and Super Class 9/29/202 Sana Anjum DBMS Unit-1 118 4  Extended ER Model Specialization and Generalization 9/29/202 Sana Anjum DBMS Unit-1 119 4  Extended ER Model Category or Union Relationship of one super or sub class with more than one super class. 9/29/202 Sana Anjum DBMS Unit-1 120 4  Extended ER Model Aggregation Represents relationship between a whole object and its component. 9/29/202 Sana Anjum DBMS Unit-1 121 4  Relationship of higher degree Degree of Relationship A degree of relationship represents the number of entity types that are associated with a relationship. Types of degree based on the number of linked entity types, we have 4 types of degrees of relationships.  Unary  Binary  Ternary  N-ary 9/29/202 Sana Anjum DBMS Unit-1 122 4  Relationship of higher degree Unary (Degree 1) in a relation only one entity set is participating then such type of relationship is known as a unary relationship. Example: In a particular class, we have many students, there are monitors too. So, here class monitors are also students. Thus, we can say that only students are participating here. So the degree of such type of relationship is 1. 9/29/2024 Sana Anjum DBMS Unit-1 123  Relationship of higher degree Binary (Degree 2) In a relation when two entity sets are participating then such type of relationship is known as a binary relationship. This is the most used relationship and one can easily be converted into a relational table. Example: We have two entity types ‘Student’ and ‘ID’ where each ‘Student’ has his ‘ID’. So, here two entity types are associating, it is a binary relationship. Also, one ‘Father’ can have many ‘daughters’ but each ‘daughter’ should belong to only one ‘father. it is a one-to-many binary relationship. 9/29/2024 Sana Anjum DBMS Unit-1 124  Relationship of higher degree Ternary (Degree 3) Ternary relationship exists when there are three types of entity and we call them a degree of relationship is 3. Example: We have three entity types ‘Teacher’, ‘Course’, and ‘Class’. The relationship between these entities is defined as the teacher teaching a particular course, also the teacher teaches a particular class. So, here three entity types are associating we can say it is a ternary relationship. 9/29/2024 Sana Anjum DBMS Unit-1 125  Relationship of higher degree N-ary (n Degree) an N-ary relationship exists when there are n types of entities. There is one limitation of the N-ary relationship, as there are many entities so it is very hard to convert into an entity, rational table. Example: We have 5 entities Teacher, Class, Location, Salary, Course. So, here five entity types are associating we can say an n-ary relationship is 5. 9/29/2024 Sana Anjum DBMS Unit-1 126  Codd’s 12 Rule 9/29/202 Sana Anjum DBMS Unit-1 127 4  Use Cases Videos and Topics Links https://youtu.be/M4qZ5i1LL2o?si=M-YvPZ1Vp9KZK5Kn https://youtu.be/010_f2HKzJY?si=82Z1Qleud-bBduii https://www.geeksforgeeks.org/last-minute-notes-dbms/ https://www.geeksforgeeks.org/quiz-corner-gq/#DBMS%20Mock%20Tests https://www.geeksforgeeks.org/commonly-asked-dbms-interview- questions/ https://www.geeksforgeeks.org/commonly-asked-dbms-interview- questions- set-2/ 9/29/2024 Sana Anjum DBMS Unit-1 128  Daily Quiz Define Database System. What do you understand by data independence? Explain types of attributes in ER diagram. What is generalization. What do you understand by primary key? Explain participation cardinality. Explain Types of keys. Differentiate between primary key and candidate key. 9/29/202 Sana Anjum DBMS Unit-1 129 4  MCQ s The entity relationship set is represented in E-R diagram as a) Double diamonds b) Undivided rectangles c) Dashed lines d) Diamond The Rectangles divided into two parts represents a) Entity set b) Relationship set c) Attributes of a relationship set d) Primary key 9/29/202 Sana Anjum DBMS Unit-1 130 4  MCQ s An entity set that does not have sufficient attributes to form a primary key is termed a __ a) Strong entity set b) Variant set c) Weak entity set d) Variable set What term is used to refer to a specific record in your music database; for instance; information stored about a specific album? a) Relation b) Instance c) Table d) Column 9/29/202 Sana Anjum DBMS Unit-1 131 4  Glossary Questions 1. What are the features of Database language? 2. What is Data Independence in a database system? 3. Define database model. 4. What is SQL? 5. Enlist the various relationships of database. 6. Define DDL and DML. 7. Enlist some commands of DDL. 8. What are the key concepts of the Entity-Relationship (ER) Model in data modelling? 9/29/202 Sana Anjum DBMS Unit-1 132 4  Weekly Assignment Define: Data, Database, and Database System. What is data independence? What are differences between Logical Data Independence and Physical Data Independence? Give example of Simple, Composite, Single –valued and Multi- valued attributes of an entity. What is the difference between Generalization & Specialization with respect to Database? What is difference between total and partial participation? Explain by suitable example. Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associated with each patient, a log of the various tests and examinations conducted. 9/29/202 Sana Anjum DBMS Unit-1 133 4  Old Question Papers http://www.aktuonline.com/papers/btech-cs-5-sem-data-base- management-system-KCS501-2020.pdf http://www.aktuonline.com/papers/btech-cs-5-sem-database- management-system-KCS-501-2018-19.pdf http://www.aktuonline.com/papers/btech-cs-5-sem-database- management-system-ncs-502-2017-18.pdf http://www.aktuonline.com/papers/btech-cs-5-sem-database- management-system-ncs-502-2016-17.pdf 9/29/202 Sana Anjum DBMS Unit-1 134 4  Expected Questions for University Exam Explain types of attributes in ER diagram. Explain the difference between a weak and a strong entity set with example. Discuss three level of abstractions or schemas architecture of DBMS. Define constraint and its types in DBMS. Compare Generalization, Specialization and aggregation with suitable examples. Draw overall structure of DBMS and explain its components in brief. Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associated with each patient, a log of the various tests and examinations conducted. 9/29/202 Sana Anjum DBMS Unit-1 135 4  Recap of Unit Knowledge of database architecture. Knowledge of Entity Relationship model and its concepts. Databse V/S File system. 9/29/202 Sana Anjum DBMS Unit-1 136 4 9/29/202 Sana Anjum DBMS Unit-1 137 4