DBMS PDF - Database Management System Textbook

 1 QUANTUM SERIES For B.Tech Students of Third Year of All Engineering Colleges Affiliated to Dr. A.P.J. Abdul Kalam Technical University, Uttar Pradesh, Lucknow (Formerly Uttar Pradesh Technical University)...

 1 QUANTUM SERIES For B.Tech Students of Third Year of All Engineering Colleges Affiliated to Dr. A.P.J. Abdul Kalam Technical University, Uttar Pradesh, Lucknow (Formerly Uttar Pradesh Technical University) Database Management System By Anjana Gautam TM QUANTUM PAGE PVT. LTD. Ghaziabad New Delhi  2 PUBLISHED BY : Apram Singh Quantum Publications (A Unit of Quantum Page Pvt. Ltd.) Plot No. 59/2/7, Site - 4, Industrial Area, Sahibabad, Ghaziabad-201 010 Phone : 0120 - 4160479 Email : [email protected] Website: www.quantumpage.co.in Delhi Office : 1/6590, East Rohtas Nagar, Shahdara, Delhi-110032 © ALL RIGHTS RESERVED No part of this publication may be reproduced or transmitted, in any form or by any means, without permission. Information contained in this work is derived from sources believed to be reliable. Every effort has been made to ensure accuracy, however neither the publisher nor the authors guarantee the accuracy or completeness of any information published herein, and neither the publisher nor the authors shall be responsible for any errors, omissions, or damages arising out of use of this information. Database Management System (CS/IT : Sem-5) 1st Edition : 2010-11 2nd Edition : 2011-12 3rd Edition : 2012-13 4th Edition : 2013-14 5th Edition : 2014-15 6th Edition : 2015-16 7th Edition : 2016-17 8th Edition : 2017-18 9th Edition : 2018-19 10th Edition : 2020-21 (Thoroughly Revised Edition) Price: Rs. 80/- only Printed Version : e-Book.  3 CONTENTS KCS–501 : DATABASE MANAGEMENT SYSTEM UNIT-1 : INTRODUCTION (1–1 A to 1–33 A) Overview, 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, Overall Database Structure. Data Modeling Using the Entity Relationship Model: ER Model Concepts, Notation for ER Diagram, Mapping Constraints, Keys, Concepts of Super Key, Candidate Key, Primary Key, Generalization, Aggregation, Reduction of an ER Diagrams to Tables, Extended ER Model, Relationship of Higher Degree. UNIT-2 : RELATIONAL DATA MODEL (2–1 A to 2–43 A) Relational Data Model Concepts, Integrity Constraints, Entity Integrity, Referential Integrity, Keys Constraints, Domain Constraints, Relational Algebra, Relational Calculus, Tuple and Domain Calculus. Introduction on SQL: Characteristics of SQL, Advantage of SQL. SQl Data Type and Literals. Types of SQL Commands. SQL Operators and Their Procedure. Tables, Views and Indexes. Queries and Sub Queries. Aggregate Functions. Insert, Update and Delete Operations, Joins, Unions, Intersection, Minus, Cursors, Triggers, Procedures in SQL/PL SQL. UNIT-3 : DATA BASE DESIGN & NORMALIZATION (3–1 A to 3–19 A) Functional dependencies, normal forms, first, second, 8 third norma l forms, BCNF, inc lusion dependenc e, loss l ess join decompositi ons, normali zati on usi ng FD, MVD, and JD s, alternative approaches to database design. UNIT-4 : TRANSACTION PROCESSING CONCEPT (4–1 A to 4–34 A) Transaction System, Testing of Serializability, Serializability of Schedules, Conflict & View Serializable Schedule, Recoverability, Recovery from Transaction Failures, Log Based Recovery, Checkpoints, Deadlock Handling. Distributed Database: Distributed Data Storage, Concurrency Control, Directory System. UNIT-5 : CONCURRENCY CONTROL TECHNIQUES (5–1 A to 5–27 A) Concurrency Control, Locking Techniques for Concurrency Control, Time Stamping Protocols for Concurrency Control, Validation Based Protocol, Multiple Granularity, Multi Version Schemes, Recovery with Concurrent Transaction, Case Study of Oracle. SHORT QUESTIONS (SQ-1 A to SQ-18 A) SOLVED PAPERS (2015-16 TO 2019-20) (SP-1 A to SP-15 A)  B.TECH. (CSE & CS) FIFTH SEMESTER (DETAILED SYLLABUS) Database Management System (KCS501) Course Outcome ( CO) Bloom’s Knowledge Level (KL) At the end of course , the student will be able to: CO 1 Apply knowledge of database for real life applications. K3 CO 2 Apply query processing techniques to automate the real time problems of databases. K 3, K 4 CO 3 Identify and solve the redundancy problem in database tables using normalization. K 2, K 3 Understand the concepts of transactions, their processing so they will familiar with broad range K 2, K 4 CO 4 of database management issues including data integrity, security and recovery. CO 5 Design, develop and implement a small database project using database tools. K 3, K 6 DETAILED SYLLABUS 3-1-0 Unit Topic Proposed Lecture Introduction: Overview, 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, Overall Database Structure. Data Modeling Using the I 08 Entity Relationship Model: ER Model Concepts, Notation for ER Diagram, Mapping Constraints, Keys, Concepts of Super Key, Candidate Key, Primary Key, Generalization, Aggregation, Reduction of an ER Diagrams to Tables, Extended ER Model, Relationship of Higher Degree. Relational data Model and Language: Relational Data Model Concepts, Integrity Constraints, Entity Integrity, Referential Integrity, Keys Constraints, Domain Constraints, Relational Algebra, Relational Calculus, Tuple and Domain Calculus. Introduction on SQL: Characteristics of SQL, II Advantage of SQL. SQl Data Type and Literals. Types of SQL Commands. SQL Operators and 08 Their Procedure. Tables, Views and Indexes. Queries and Sub Queries. Aggregate Functions. Insert, Update and Delete Operations, Joins, Unions, Intersection, Minus, Cursors, Triggers, Procedures in SQL/PL SQL Data Base Design & Normalization: Functional dependencies, normal forms, first, second, 8 third III normal forms, BCNF, inclusion dependence, loss less join decompositions, normalization using 08 FD, MVD, and JDs, alternative approaches to database design Transaction Processing Concept: Transaction System, Testing of Serializability, Serializability of Schedules, Conflict & View Serializable Schedule, Recoverability, Recovery from Transaction IV 08 Failures, Log Based Recovery, Checkpoints, Deadlock Handling. Distributed Database: Distributed Data Storage, Concurrency Control, Directory System. Concurrency Control Techniques: Concurrency Control, Locking Techniques for Concurrency V Control, Time Stamping Protocols for Concurrency Control, Validation Based Protocol, Multiple 08 Granularity, Multi Version Schemes, Recovery with Concurrent Transaction, Case Study of Oracle. Text books: 1. Korth, Silbertz, Sudarshan,” Database Concepts”, McGraw Hill 2. Date C J, “An Introduction to Database Systems”, Addision Wesley 3. Elmasri, Navathe, “ Fundamentals of Database Systems”, Addision Wesley 4. O’Neil, Databases, Elsevier Pub. 5. RAMAKRISHNAN"Database Management Systems",McGraw Hill 6. Leon & Leon,”Database Management Systems”, Vikas Publishing House 7. Bipin C. Desai, “ An Introduction to Database Systems”, Gagotia Publications 8. Majumdar & Bhattacharya, “Database Management System”, TMH Database Management System 1–1 A (CS/IT-Sem-5) 1 Introduction CONTENTS Part-1 : Overview, Database.................................... 1–2A to 1–8A System vs File System, Database System Concept and Architecture Part-2 : Data Model Schema and.......................... 1–9A to 1–16A Instances, Data Independence and Database Language and Interfaces, Data Definition Language, DML, Overall Database Structure Part-3 : Data Modeling using............................... 1–16A to 1–22A the Entity Relationship Model : ER Model Concepts, Notation for ER Diagram, Mapping Constraints Part-4 : Keys, Concept of Super.......................... 1–22A to 1–27A Key, Candidate Key, Primary Key, Generalization, Aggregation Part-5 : Reduction of an ER Diagram................ 1–27A to 1–32A to Tables, Extended ER Model, Relationship of Higher Degree Introduction 1–2 A (CS/IT-Sem-5) PART-1 Overview, Database System vs File System, Database System Concept and Architecture. Questions-Answers Long Answer Type and Medium Answer Type Questions Que 1.1. What is database management system (DBMS) ? What are the tasks performed by users in DBMS ? Answer 1. Database management system (DBMS) is a software which is use to manage the database. For example, MySQL, Oracle, are commercial database which is used in different applications. 2. DBMS provides an interface to perform various operations like database creation, storing data, updating data, creating a table in the database etc. 3. It provides protection and security to the database. In case of multiple users, it also maintains data consistency. DBMS allows users the following tasks : 1. Data definition : It is used for creation, modification, and removal of database objects that defines the organization of data in the database. 2. Data updation : It is used for the insertion, modification, and deletion of the actual data in the database. 3. Data retrieval : It is used to retrieve the data from the database which can be used by applications for various purposes. 4. User administration : It is used for registering and monitoring users, maintaining data integrity, enforcing data security, dealing with concurrency control, monitoring performance and recovering information corrupted by unexpected failure. Que 1.2. What are the advantages and disadvantages of DBMS ? Answer Advantages of DBMS : 1. Database redundancy : It controls data redundancy because it stores all the data in one single database file and that recorded data is placed in the database. Database Management System 1–3 A (CS/IT-Sem-5) 2. Data sharing : In DBMS, the authorized users of an organization can share the data among multiple users. 3. Easy maintenance : It can be easily maintainable due to the centralized nature of the database system. 4. Reduce time : It reduces development time and maintenance need. 5. Backup : It provides backup and recovery subsystems which create automatic backup of data from hardware and software failures and restores the data if required. 6. Multiple user interface : It provides different types of user interfaces like graphical user interface, application program interface. Disadvantages of DBMS : 1. Cost of hardware and software : It requires high speed of data processor and large memory size to run DBMS software. 2. Size : It occupies a large space of disks and large memory to run efficiently. 3. Complexity : Database system creates additional complexity and requirements. 4. Higher impact of failure : Failure is highly impacted the database because in most of the organization, all the data stored in a single database and if the database is damaged due to electric failure or database corruption then the data may be lost forever. Que 1.3. What do you understand by database users ? Describe the different types of database users. Answer Database users are the one who use and take the benefits of database. The different types of users depending on the need and way of accessing the database are : 1. Application programmers : a. They are the developers who interact with the database by means of DML queries. b. These DML queries are written in the application programs like C, C++, JAVA, Pascal etc. c. These queries are converted into object code to communicate with the database. 2. Sophisticated users : a. They are database developers, who write SQL queries to select/ insert/delete/update data. b. They directly interact with the database by means of query language like SQL. c. These users can be scientists, engineers, analysts who thoroughly study SQL and DBMS to apply the concepts in their requirement. Introduction 1–4 A (CS/IT-Sem-5) 3. Specialized users : a. These are also sophisticated users, but they write special database application programs. b. They are the developers who develop the complex programs according to the requirement. 4. Standalone users : a. These users will have standalone database for their personal use. b. These kinds of database will have predefined database packages which will have menus and graphical interfaces. 5. Native users : a. These are the users who use the existing application to interact with the database. b. For example, online library system, ticket booking systems, ATMs etc. Que 1.4. Who are data administrators ? What are the functions of database administrator ? OR Discuss the role of database administrator. AKTU 2017-18, Marks 10 Answer Database administrators are the personnel’s who has control over data and programs used for accessing the data. Functions/role of database administrator (DBA) : 1. Schema definition : a. Original database schema is defined by DBA. b. This is accomplished by writing a set of definitions, which are translated by the DDL compiler to a set of labels that are permanently stored in the data dictionary. 2. Storage structure and access method definition : a. The creation of appropriate storage structure and access method. b. This is accomplished by writing a set of definitions, which are translated by the data storage and definition language compiler. 3. Schema and physical organization and modification : a. Modification of the database schema or the description of the physical storage organization. b. These changes are accomplished by writing a set of definition to do modification to the appropriate internal system tables. Database Management System 1–5 A (CS/IT-Sem-5) 4. Granting of authorization for data access : DBA grants different types of authorization for data access to the various users of the database. 5. Integrity cons traint s pecification : DBA carry o ut data administration in data dictionary such as defining constraints. Que 1.5. What is data abstraction ? Explain different levels of abstraction. Answer Data abstraction is the process of finding irrelevant details from user i.e., hiding the background details from the users. Different levels of data abstraction : View level View 1 View 1............ View n Logical level Physical level Fig. 1.5.1. The three levels of data abstraction. 1. Physical level : i. Physical level is the lowest level of abstraction and describes how the data are actually stored. ii. The physical level describes the complex low-level data structures in details. 2. Logical level : i. Logical level is the next-higher level of abstraction and it describes what data are stored in the database, and what relationship exists among those data. ii. The logical level thus describes the entire database in terms of a small number of relatively simple structures. 3. View level : i. View level is the highest level of abstraction; it describes only part of the entire database. ii. The view level of abstraction exists to simplify their interaction with the system. iii. The system may provide many views for the same database. Introduction 1–6 A (CS/IT-Sem-5) Que 1.6. Explain the differences between physical level, conceptual level and view level of data abstraction. AKTU 2016-17, Marks 10 Answer S. No. Physical level Conceptual/ View level Logical level 1. This is the lowest This is the middle level This is the highest le ve l of data of data abstraction. level of data abstraction. abstraction. 2. It describes how It describes what data It describes the user data is actually is stored in database. interaction with stored in database. database system. 3. It de scribe s the It de scribe s the It describes only those complex low-level structure o f whole part of the database data structures in database and hides in which the users are detail. de tails of physical interested and hides storage structure. rest of all information from the users. 4. A user is not aware A user is not aware of A user is aware of the of the complexity the co mple xity o f complexity of of database. database. database. Que 1.7. Explain the difference between database management system (DBMS) and file system. Answer S. No. DBMS File System 1. In DBMS, the user is not In this system, the user has to re quired to write the write the procedures for managing procedures. the file. 2. DBMS gives an abstract view File system provides the detail of of data that hides the details. the data repre sentation and storage of data. Database Management System 1–7 A (CS/IT-Sem-5) 3. DBMS pro vide s a crash File system do not have a crash re co ve ry mechanism, i.e., mechanism, i.e., if the system DBMS protects the data from crashes while entering some data, the system failure. then the content of the file will lost. 4. DBMS provides a goo d It is very difficult to protect a file protection mechanism. under the file system. 5. DBMS can efficiently store and File system cannot efficiently store retrieve the data. and retrieve the data. 6. DBMS takes care of In the file system, concurrent concurrent access of data using access has many problems like some form of locking. redirecting the file while other deleting some information or updating some information. Que 1.8. Discuss the architecture of DBMS. What are the types of DBMS architecture ? Answer 1. The DBMS design depends upon its architecture. The basic client/ server architecture is used to deal with a large number of PCs, web servers, database servers and other components that are connected with networks. 2. DBMS architecture depends upon how users are connected to the database to get their request done. Types of DBMS architecture : i. 1-Tier architecture : 1. In this architecture, the database is directly available to the user. 2. Any changes done are directly done on the database itself. It does not provide a handy tool for end users. 3. The 1-Tier architecture is used for development of the local application, where programmers can directly communicate with the database for the quick response. ii. 2-Tier architecture : 1. The 2-Tier architecture is same as basic client-server. 2. In the two-tier architecture, applications on the client end can directly communicate with the database at the server side. For this interaction, API’s such as : ODBC, JDBC are used. 2. The user interfaces and application programs are run on the client-side. Introduction 1–8 A (CS/IT-Sem-5) 3. The server side is responsible to provide the functionalities like query processing and transaction management. 4. To communicate with the DBMS, client-side application establishes a connection with the server side. Database system Server Application Client User Fig. 1.8.1. 2-Tier architecture. iii. 3-Tier architecture : 1. The 3-Tier architecture contains another layer between the client and server. In this architecture, client cannot directly communicate with the server. 2. The application on the client-end interacts with an application server which further communicates with the database system. 3. End user has no idea about the existence of the database beyond the application server. The database also has no idea about any other user beyond the application. 4. The 3-Tier architecture is used in case of large web application. Database Server Application server Application client Client User Fig. 1.8.1. 3-Tier architecture. Database Management System 1–9 A (CS/IT-Sem-5) PART-2 Data Model Schema and Instances, Data Independence and Database Language and Interfaces, Data Definition Language, DML, Overall Database Structure. Questions-Answers Long Answer Type and Medium Answer Type Questions Que 1.9. What are data models ? Briefly explain different types of data models. Answer Data models : 1. Data models define how the logical structure of the database is modeled. 2. Data models are a collection of conceptual tools for describing data, data relationships, data semantics and consistency constraints. 3. Data models define how data is connected to each other and how they are processed and stored inside the system. Types of data models : 1. Entity relationship model : a. The entity relationship (ER) model consists of a collection of basic objects, called entities and of relationships among these entities. b. Entities are represented by means of their properties, called attributes. Attribute Attribute Attribute Attribute Entity Relationship Entity Fig. 1.9.1. The ER model. 2. Relational model : a. The relational model represents data and relationships among data by a collection of tables, each of which has a number of columns with unique names. Introduction 1–10 A (CS/IT-Sem-5) b. Relational data model is used for data storage and processing. c. This model is simple and it has all the properties and capabilities required to process data with storage efficiency. 3. Hierarchical model : a. In hierarchical model data elements are linked as an inverted tree structure (root at the top with branches formed below). b. Below the single root data element are subordinate elements each of which in turn has its own subordinate elements and so on, the tree can grow to multiple levels. c. Data element has parent child relationship as in a tree. 4. Network model : a. This model is the extension of hierarchical data model. b. In this model there exist a parent child relationship but a child data element can have more than one parent element or no parent at all. 5. Object-oriented model : a. Object-oriented models were introduced to overcome the shortcomings of conventional models like relational, hierarchical and network model. b. An object-oriented database is collection of objects whose behaviour, state, and relationships are defined in accordance with object- oriented concepts (such as objects, class, etc.). Que 1.10. Describe data schema and instances. Answer 1. The description of a database is called the database schema, which specified during database design and is not expected to change frequently. 2. Most of the data models have certain convention for displaying schema as diagram which is called as schema diagram. 3. A schema diagram displays only some aspects of a schema, such as the names of record types and data items, and some types of constraints. For example : Schema diagram for studentinfo database Student (Name, Student_number, Class, Branch) Course (Course_name, Course_number, Department) Instances : 1. The data in the database at a particular moment is called a database state or snapshot. It is also called the current set of occurrences or instances in the database. Database Management System 1–11 A (CS/IT-Sem-5) 2. In a database state, each schema construct has its own current set of instances. 3. Many database states can be constructed to correspond to a particular database schema. Every time we insert or delete a record or change the value of a data item in a record, we change one state of the database into another state. Que 1.11. Describe data independence with its types. OR Explain data independence with its types. AKTU 2019-20, Marks 07 Answer Data independence : Data independence is defined as the capacity to change the schema at one level of a database system without having to change the schema at the next higher level. Types of data independence : 1. Physical data independence : a. Physical data independence is the ability to modify internal schema without changing the conceptual schema. b. Modification at the physical level is occasionally necessary in order to improve performance. c. It refers to the immunity of the conceptual schema to change in the internal schema. d. Examples of physical data independence are reorganizations of files, adding a new access path or modifying indexes, etc. 2. Logical data independence : a. Logical data independence is the ability to modify the conceptual schema without having to change the external schemas or application programs. b. It refers to the immunity of the external model to changes in the conceptual model. c. Examples of logical data independence are addition/removal of entities. Que 1.12. Describe the classification of database language. Which type of language is SQL ? OR Discuss the following terms (i) DDL Command (ii) DML command. AKTU 2019-20, Marks 07 Introduction 1–12 A (CS/IT-Sem-5) Answer Classification of database languages : 1. Data Definition Language (DDL) : a. DDL is set of SQL commands used to create, modify and delete database structures but not data. b. They are used by the DBA to a limited extent, a database designer, or application developer. c. Create, drop, alter, truncate are commonly used DDL command. 2. Data Manipulation Language (DML) : a. A DML is a language that enables users to access or manipulates data as organized by the appropriate data model. b. There are two types of DMLs : i. Procedural DMLs : It requires a user to specify what data are needed and how to get those data. ii. Declarative DMLs (Non-procedural DMLs) : It requires a user to specify what data are needed without specifying how to get those data. c. Insert, update, delete, query are commonly used DML commands. 3. Data Control Language (DCL) : a. It is the component of SQL statement that control access to data and to the database. b. Commit, rollback command are used in DCL. 4. Data Query Language (DQL) : a. It is the component of SQL statement that allows getting data from the database and imposing ordering upon it. b. It includes select statement. 5. View Definition Language (VDL) : 1. VDL is used to specify user views and their mapping to conceptual schema. 2. It defines the subset of records available to classes of users. 3. It creates virtual tables and the view appears to users like conceptual level. 4. It specifies user interfaces. SQL is a DML language. Que 1.13. Explain all database languages in detail with example. AKTU 2017-18, Marks 10 Database Management System 1–13 A (CS/IT-Sem-5) Answer Database languages : Refer Q. 1.12, Page 1–11A, Unit-1. Examples : DDL : CREATE, ALTER, DROP, TRUNCATE, COMMENT, GRANT, REVOKE statement DML : INSERT, UPDATE, DELETE statement DCL : GRANT and REVOKE statement DQL : SELECT statement VDL : 1. create view emp5 as select * from employee where dno = 5 ; Creates view for dept 5 employees. 2. create view empdept as select fname, lname, dno, dname from employee, department where dno=dnumber ; Creates view using two tables. Que 1.14. Explain DBMS interfaces. What are the various DBMS interfaces ? Answer DBMS interfaces : A database management system (DBMS) interface is a user interface which allows for the ability to input queries to a database without using the query language itself. Various DBMS interfaces are : 1. Menu-based interfaces for web clients or browsing : a. These interfaces present the user with lists of options (called menus) that lead the user through the formulation of a request. b. Pull-down menus are a very popular technique in Web-based user interfaces. c. They are also often used in browsing interfaces, which allow a user to look through the contents of a database in an exploratory and unstructured manner. Introduction 1–14 A (CS/IT-Sem-5) 2. Forms-based interfaces : a. A forms-based interface displays a form to each user. b. Users can fill out all of the form entries to insert new data, or they can fill out only certain entries, in which the DBMS will retrieve matching data for the remaining entries. 3. Graphical user interfaces (GUI) : a. A GUI typically displays a schema to the user in diagrammatic form. b. The user then can specify a query by manipulating the diagram. In many cases, GUIs utilize both menus and forms. 4. Natural language interfaces : a. A natural language interface has its own schema, which is similar to the database conceptual schema, as well as a dictionary of important words. b. The natural language interface refers to the words in its schema, as well as to the set of standard words in its dictionary to interpret the request. c. If the interpretation is successful, the interface generates a high- level query corresponding to the natural language request and submits it to the DBMS for processing; otherwise, a dialogue is started with the user to clarify the request. 5. Speech input and output : a. The speech input is detected using a library of predefined words and used to set up the parameters that are supplied to the queries. b. For output, a similar conversion from text or numbers into speech takes place. 6. Interfaces for the DBA : a. Most database systems contain privileged commands that can be used only by the DBA’s staff. b. These include commands for creating accounts, setting system parameters, granting account authorization, changing a schema, and reorganizing the storage structures of a database. Que 1.15. Briefly describe the overall structure of DBMS. OR Draw the overall structure of DBMS and explain its components in brief. AKTU 2018-19, Marks 07 Answer A database system is partitioned into modules that deal with each of the responsibilities of the overall system. The functional components of a database system can be broadly divided into two components : Database Management System 1–15 A (CS/IT-Sem-5) Application Sophisticated Database Naive Users Programmers users Administrator Use Write Use Use Application Application Administrative Query Tools Interfaces Programs Tools Compiler and DDL DML Queries Application Linker Interpreter Program Object Code DML Compiler and Organizer Query Evaluation Engine Query processor Buffer File Authorization and Transaction manager manager integrity manager manager Storage manager Indices Data Disk storage Dictionary Data Statistical data Fig. 1.15.1. Overall database structure. 1. Storage Manager (SM) : A 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 SM components include : a. Authorization and integrity manager : It tests for the satisfaction of integrity constraints and checks the authority of users to access data. b. Transaction manager : It ensures that the database remains in a consistent state despite of system failures and that concurrent transaction executions proceed without conflicting. Introduction 1–16 A (CS/IT-Sem-5) c. File manager : It manages the allocation of space on disk storage and the data structures are used to represent information stored on disk. d. Buffer manager : It is responsible for fetching data from disk storage into main memory and deciding what data to cache in main memory. The buffer manager is a critical part of the database system, since it enables the database to handle data sizes that are much larger than the size of main memory. 2. Query Processor (QP) : The Query Processor (Query Optimizer) is responsible for taking every statement sent to SQL Server and figure out how to get the requested data or perform the requested operation. The QP components are : a. DDL interpreter : It interprets DDL statements and records the definition in data dictionary. b. DML compiler : It translates DML statements in a query language into an evaluation plan consisting of low-level instructions that the query evaluation engine understands. c. Query optimization : It picks the lowest cost evaluation plan from among the alternatives. d. Query evaluation engine : It executes low-level instructions generated by the DML compiler. PART-3 Data Modeling using the Entity Relationship Model : ER Model Concepts, Notation for ER Diagram, Mapping Constraints. Questions-Answers Long Answer Type and Medium Answer Type Questions Que 1.16. What is ER model ? What are the elements of ER model ? OR What are the notations of ER diagram ? Answer An entity relationship model (ER model) is a way of representing the entities and the relationships between the entities in order to create a database. Database Management System 1–17 A (CS/IT-Sem-5) Elements/notation of ER model/diagram : 1. Entity : a. An entity is a real world object that can be easily identifiable. b. An entity can be abstract. c. An entity is an object that exists and is distinguishable from other objects. 2. Entity set : a. Entity set is a collection of similar type of entities. b. An entity set may contain entities with attribute sharing similar values. 3. Attribute : a. An attribute gives the characteristics of the entity. b. It is also called as data element, data field, a field, a data item, or an elementary item. 4. Relationship : a. A relationship is the association between entities or entity occurrence. b. Relationship is represented by diamond with straight lines connecting the entities. Que 1.17. 3.17. What do you understand by attributes and domain ? Explain various types of attributes used in conceptual data model. Answer Attributes : 1. Attributes are properties which are used to represent the entities. 2. All attributes have values. For example, a student entity may have name, class, and age as attributes. 3. There exists a domain or range of values that can be assigned to attributes. 4. For example, a student’s name cannot be a numeric value. It has to be alphabetic. A student’s age cannot be negative, etc. Domain : 1. A domain is an attribute constraint which determines the type of data values that are permitted for that attribute. 2. Attribute domains can be very large, or very short. Types of attributes used in conceptual data model : 1. Simple attribute : Simple attributes are atomic values, which cannot be divided further. For example, a student’s phone number is an atomic value of 10 digits. Introduction 1–18 A (CS/IT-Sem-5) 2. Composite attribute : Composite attributes are made of more than one simple attribute. For example, a student's complete name may have first_name and last_name. 3. Derived attribute : Derived attributes are the attributes that do not exist in the physical database, but their values are derived from other attributes present in the database. For example, average_salary in a department should not be saved directly in the database, instead it can be derived. 4. Single-value attribute : Single-value attributes contain single value. For example, Social_Security_Number. 5. Multi-value attribute : Multi-value attributes may contain more than one values. For example, a person can have more than one phone number, email_address, etc. Que 1.18. What is purpose of the ER diagram ? Construct an ER diagram for a University system which should include information about students, departments, professors, courses, which students are enrolled in which course, which professors are teaching which courses, student grades, which course a department offers. Answer Purpose of the ER diagram : 1. ER diagram is used to represent the overall logical structure of the database. 2. ER diagrams emphasis on the schema of the database and not on the instances because the schema of the database is changed rarely. 3. It is useful to communicate the logical structure of database to end users. 4. It serves as a documentation tool. 5. It helps the database designer in understanding the information to be contained in the database. ER diagram : Professor Course Student teaching Student name grade enrolled in Course Professors Department University Course duration type Course Course name department Fig. 1.18.1. ER diagram for University system.  Order ID No. of items Product ID Expiry date Order Answer Que 1.20. Que 1.19. location Product name Order Demand Product Price Price Availability Payee name Initiates Work Available Payment mode Database Management System Product Amount paid name Employee Promotion Employee Address Payment ID Invoice Product ID Salary Promo code Promo name Employee name Products purchased Fig.1.19.1. Confirmation Number of Product ID items Store No. database, assuming your own data requirements. Tracking No. Delivery Shipping Store Purchase No. Shipping type Date of Store name Store address delivery AKTU 2016-17, Marks 7.5 A university registrar’s office maintains data about the following entities (a) courses, including number, title, credits, syllabus and prerequisites; (b) course offerings, including course Draw an ER diagram for a small marketing company 1–19 A (CS/IT-Sem-5) Introduction 1–20 A (CS/IT-Sem-5) number, year, semester section number, instructor(s), timings and classroom; (c) students, including student-id, name and program; and (d) instructors, including identification number, name department and title. Further the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled. Construct an ER diagram for the registrar’s office. Document all assumption that you make about the mapping constraints. AKTU 2015-16, Marks 10 Answer In this ER diagram, the main entity sets are student, course, course offering and instructor. The entity set course offering is a weak entity set dependent on course. The assumptions made are : a. A class meets only at one particular place and time. This ER diagram cannot model a class meeting at different places at different times. b. There is no guarantee that the database does not have two classes meeting at the same place and time. s_id name time secno room i_id name enrolls course student offerings teaches instructor year semester deptt title program grade is offered syllabus courseno prerequisite requires course title maincourse credits Fig. 1.20.1. ER diagram for University. Que 1.21. Describe mapping constraints with its types. OR Describe mapping constraints with its types. AKTU 2019-20, Marks 07 Database Management System 1–21 A (CS/IT-Sem-5) Answer 1. Mapping constraints act as a rule followed by contents of database. 2. Data in the database must follow the constraints. Types of mapping constraints are : 1. Mapping cardinalities : a. Mapping cardinalities (or cardinality ratios) specifies the number of entities of which another entity can be associated via a relationship set. b. Mapping cardinalities are used in describing binary relationship sets, although they contribute to the description of relationship sets that involve more than two entity sets. c. For binary relationship set R between entity sets A and B, the mapping cardinality must be one of the following : i. One to one : An entity in A is associated with at most one entity in B and an entity in B is associated with at most one entity in A. A B a1 b1 a2 b2 a3 b3 a4 b4 Fig. 1.21.1. ii. One to many : An entity in A is associated with any number of entities in B. An entity in B, however, can be associated with at most one entity in A. A B b1 a1 b2 a2 b3 a3 b4 b5 Fig. 1.21.2. iii. Many to one : An entity in A is associated with at most one entity in B, and an entity in B, however, can be associated with any number of entities in A. Introduction 1–22 A (CS/IT-Sem-5) A B a1 a2 b1 a3 b2 a4 b3 a5 Fig. 1.21.3. iv. Many to many : 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. A B a1 b1 a2 b2 a3 b3 a4 b4 Fig. 1.21.4. 2. Participation constraints : It tells the participation of entity sets. There are two types of participations : i. Partial participation ii. Total participation PART-4 Keys, Concept of Super Key, Candidate Key, Primary Key, Generalization, Aggregation. Questions-Answers Long Answer Type and Medium Answer Type Questions Que 1.22. Discuss the candidate key, primary key, super key, composite key and alternate key. Database Management System 1–23 A (CS/IT-Sem-5) OR Explain the primary key, super key, foreign key and candidate key with example. AKTU 2017-18, Marks 10 OR Define key. Explain various types of keys. AKTU 2019-20, Marks 07 Answer 1. Key is a attribute or set of attributes that is used to identify data in entity sets. 2. Key is defined for unique identification of rows in table. Consider the following example of an Employee table : Employee (EmployeeID, FullName, SSN, DeptID) Various types of keys are : 1. Primary key : a. Primary key uniquely identifies each record in a table and must never, be the same for records. Here in Employee table we can choose either EmployeeID or SSN columns as a primary key. b. Primary key is a candidate key that is used for unique identification of entities within the table. c. Primary key cannot be null. d. Any table has a unique primary key. 2. Super key : a. A super key for an entity is a set of one or more attribute whose combined value uniquely identifies the entity in the entity set. b. For example : Here in employee table (EmployeeID, FullName) or (EmployeeID, FullName, DeptID) is a super key. 3. Candidate key : a. A candidate key is a column, or set of column, in the table that can uniquely identify any database record without referring to any other data. b. Candidate key are individual columns in a table that qualifies for uniqueness of all the rows. Here in Employee table EmployeeID and SSN are candidate keys. c. Minimal super keys are called candidate keys. Introduction 1–24 A (CS/IT-Sem-5) 4. Composite key : a. A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table. b. It is used when we cannot identify a record using single attributes. c. A primary key that is made by the combination of more than one attribute is known as a composite key. 5. Alternate key : a. The alternate key of any table are those candidate keys which are not currently selected as the primary key. b. Exactly one of those candidate keys is chosen as the primary key and the remainders, if any are then called alternate keys. c. An alternate key is a function of all candidate keys minus the primary key. d. Here in Employee table if EmployeeID is primary key then SSN would be the alternate key. 6. Foreign key : a. Foreign key represents the relationship between tables and ensures the referential integrity rule. b. A foreign key is derived from the primary key of the same or some other table. c. Foreign key is the combination of one or more columns in a table (parent table) at references a primary key in another table (child table). d. A foreign key value can be left null. For example : Consider another table : Project (ProjectName, TimeDuration, EmployeeID) a. Here, the ‘EmployeeID’ in the ‘Project’ table points to the ‘EmployeeID’ in ‘Employee’ table b. The ‘EmployeeID’ in the ‘Employee’ table is the primary key. c. The ‘EmployeeID’ in the ‘Project’ table is a foreign key. Que 1.23. What do you mean by a key to the relation ? Explain the differences between super key, candidate key and primary key. AKTU 2015-16, Marks 10 Answer Key : Refer Q. 1.22, Page 1–22A, Unit-1. Database Management System 1–25 A (CS/IT-Sem-5) Difference between super key, candidate key and primary key : S. No. Super key Candidate key Primary key 1. Super ke y is an Candidate key is a Primary key is a attribute (or set of minimal set of super minimal set of attributes) that is key. attributes that used to uniquely uniquely identifies identifies all rows in a relation. attributes in a relation. 2. All super keys All candidate keys are Primary key is a cannot be super keys but not subset of candidate candidate keys. primary key. key and super key. 3. It can be null. It can be null. It cannot be null. 4. A relation can have Number of candidate Number of primary any number of keys is less than keys is less than super keys. super keys. candidate keys. 5. For example, in For example, in For example, in Fig. 1.23.1, super Fig. 1.23.1, candidate Fig. 1.23.1, primary key are : key are : key is : (Registration) (Registration), (Registration, (Vehicle_id), Vehicle_id) ( Re gist ration, Vehicle_id), (Registration, Vehicle_id, Make) etc. Registration Vehicle_id Year CAR Model Colour Make Fig. 1.23.1. An entity CAR for defining keys. Introduction 1–26 A (CS/IT-Sem-5) Que 1.24. Explain generalization, specialization and aggregation. OR Compare generalization, specialization and aggregation with suitable examples. AKTU 2018-19, Marks 07 Answer Generalization : a. Generalization is a process in which two lower level entities combine to form higher level entity. b. It is bottom-up approach. c. Generalization is used to emphasize the similarities among lower level entity sets and to hide the differences. For example : Generalization Account IS A Saving Current Fig. 1.24.1. Specialization : a. Specialization is a process of breaking higher level entity into lower level entity. b. It is top-down approach. c. It is opposite to generalization. For example : Specialization Person IS A Employee Customer Fig. 1.24.2. Aggregation : a. Aggregation is an abstraction through which relationships are treated as higher level entities. For example : 1. The relationship works_on (relating the entity sets employee, branch and job) act as a higher-level entity set. 2. We can then create a binary relationship ‘Manages’, between works on and manager to represent who manages what tasks. Database Management System 1–27 A (CS/IT-Sem-5) Job Employee Works_on Branch Manages Manager Fig. 1.24.3. ER diagram with aggregation. Comparison : S. No. Generalization Specialization Aggregation 1. In generalization, In specialization, an Aggregation is an the common entity of higher-level abstraction through attributes of two or entity is broken down which relationships more lower-level into two or more are treated as higher entities combines entities of lower level. level entities. to form a ne w higher-level entity. 2. Generalization is a Specialization is a top- It allows us to bottom-up down approach. indicate that a approach. relationship set participates in another relationship set. 3. It helps in reducing It increases the size It also increases the the schema size. of schema. size of schema. 4. It is applied to group It can be applied to a It is applied to group of entities. single entity. of relationships. PART-5 Reduction of an ER Diagram to Tables, Extended ER Model, Relationship of Higher Degree. Questions-Answers Long Answer Type and Medium Answer Type Questions Introduction 1–28 A (CS/IT-Sem-5) Que 1.25. Explain the reduction of ER schema to tables. OR How to reduce an ER model into table ? Answer 1. In ER model, database are represented using the different notations or diagrams, and these notations can be reduced to a collection of tables. 2. In the database, every entity set or relationship set can be represented in tabular form. Consider following ER diagram : Subject_Name Course_Name Lecturer_ID Subject_ID Course_ID Course Subject M 1 Has Course_ID N N 1 Pin k es Attends Teaches State Ta Course_ID M M 1 City Address Lecturer Teaches Student Lecturer_Name M 1 Street Door_No Lecturer_ID Student_Name Student_ID DoB Age Hobby Database Management System 1–29 A (CS/IT-Sem-5) Basic rules for converting the ER diagrams into tables are : 1. Convert all the entities in the diagram to tables : a. All the entities represented in the rectangular box in the ER diagram become independent tables in the database. b. In the ER diagram, Student, Course, Lecturer and Subjects forms individual tables. 2. All single-valued attribute becomes a column for the table : a. All the attributes, whose value at any instance of time is unique, are considered as columns of that table. b. In the Student entity, Student_Name and Student_ID form the column of Student table. Similarly, Course_Name and Course_ID form the column of Course table and so on. 3. A key attribute of the entity is the primary key : a. All the attributes represented in the oval shape and underlined in the ER diagram are considered as key attribute which act as a primary key of table. b. In the given ER diagram, Student_ID , Course_ID, Subject_ID, and Lecture_ID are the key attribute of the Student, Course, Subjects and Lecturer entity. 4. The multivalued attribute is represented by a separate table : a. In the student table, a hobby is a multivalued attribute. b. So it is not possible to represent multiple values in a single column of Student table. Hence we create a table Stud_Hobby with column name Student_ID and Hobby. Using both the column, we create a composite key. 5. Composite attributes are merged into same table as different columns : a. In the given ER diagram, student address is a composite attribute. It contains City, Pin, Door_No, Street, and State. b. In the Student table, these attributes can merge as an individual column. 6. Derived attributes are not considered in the table : a. In the Student table, Age is the derived attribute. b. It can be calculated at any point of time by calculating the difference between current date and Date of Birth (DoB). Introduction 1–30 A (CS/IT-Sem-5) Table structure for given ER diagram is : Student Lecturer Subject Student_ID Lecturer_ID Subject_ID Student_Name Lecturer_Name Subject_Name DoB Course_ID Lecturer_ID Door_No Street City Course State Course_ID Pin Course_Name Course_ID Stud_Hobbey Student_ID Hobby Que 1.26. Discuss extended ER (EER) model. Answer 1. The ER model that is supported with the additional semantic concepts is called the extended entity relationship model or EER model. 2. The EER model includes all the concepts of the original ER model together with the following additional concepts : a. Specialization : Refer Q. 1.24, Page 1–26A, Unit-1. b. Generalization : Refer Q. 1.24, Page 1–26A, Unit-1. c. Aggregation : Refer Q. 1.24, Page 1–26A, Unit-1. 3. The super class/subclass entity types (or super type /subtype entities) is one of the most important modelling constructs that is included in the EER model. 4. This feature enables us to model a general entity and then subdivide it into several specialized entity types (subclasses or subtypes). 5. EER diagrams are used to capture business rules such as constraints in the super type/subtype relations. Thus, a super class is an entity type that includes distinct subclasses that require to be represented in a data model. 6. A subclass is an entity type that has a distinct role and is also a member of a super class. Database Management System 1–31 A (CS/IT-Sem-5) Shared attributes Superclass Subclass Subclass Unique attributes Unique attributes Fig. 1.26.1. Basic notation of the superclass/subclass relationship. Que 1.27. What is Unified Modeling Language ? Explain different types of UML. Answer 1. Unified Modeling Language (UML) is a standardized modeling language enabling developers to specify, visualize, construct and document artifacts of a software system. 2. UML makes these artifacts scalable, secure and robust in execution. 3. UML is an important aspect involved in object-oriented software development. 4. It uses graphic notation to create visual models of software systems. Types of UML : 1. Activity diagram : a. It is generally used to describe the flow of different activities and actions. b. These can be both sequential and in parallel. c. They describe the objects used, consumed or produced by an activity and the relationship between the different activities. 2. Use case diagram : a. Case diagrams are used to analyze the system’s high-level requirements. Introduction 1–32 A (CS/IT-Sem-5) b. These requirements are expressed through different use cases. 3. Interaction overview diagram : a. The interaction overview diagram is an activity diagram made of different interaction diagrams. 4. Timing diagram : a. Timing UML diagrams are used to represent the relations of objects when the center of attention rests on time. b. Each individual participant is represented through a lifeline, which is essentially a line forming steps since the individual participant transits from one stage to another. c. The main components of a timing UML diagram are : i. Lifeline ii. State timeline iii. Duration constraint iv. Time constraint v. Destruction occurrence 5. Sequence UML diagram : a. Sequence diagrams describe the sequence of messages and interactions that happen between actors and objects. b. Actors or objects can be active only when needed or when another object wants to communicate with them. c. All communication is represented in a chronological manner. 6. Class diagram : a. Class diagrams contain classes, alongside with their attributes (also referred to as data fields) and their behaviours (also referred to as member functions). b. More specifically, each class has three fields : the class name at the top, the class attributes right below the name, the class operations/ behaviours at the bottom. c. The relation between different classes (represented by a connecting line), makes up a class diagram. VERY IMPORTANT QUESTIONS Following questions are very important. These questions may be asked in your SESSIONALS as well as UNIVERSITY EXAMINATION. Q. 1. What is database management system ? Ans. Refer Q. 1.1. Database Management System 1–33 A (CS/IT-Sem-5) Q. 2. Explain the advantages of database management system over the simple file processing system. Ans. Refer Q. 1.2. Q. 3. What is data abstraction ? Describe different levels of data abstraction. Ans. Refer Q. 1.5. Q. 4. Describe the overall structure of DBMS. Ans. Refer Q. 1.15. Q. 5. Explain all database languages in detail with example. Ans. Refer Q. 1.13. Q. 6. Describe the different types of database user. Ans. Refer Q. 1.3. Q. 7. Describe the various types of attributes used in conceptual data model. Ans. Refer Q. 1.17. Q. 8. What is key ? Explain various types of key. Ans. Refer Q. 1.22. Q. 9. Explain extended ER model. Ans. Refer Q. 1.26.  Database Management System 2–1 A (CS/IT-Sem-5) 2 UNIT Relational Data Model and Language CONTENTS Part-1 : Relational Data Model................................ 2–2A to 2–6A Concept, Integrity Constraints, Entity Integrity, Referential Integrity, Keys Constraints, Domain Constraints Part-2 : Relational Algebra..................................... 2–6A to 2–10A Part-3 : Relational Calculus, Tuple..................... 2–10A to 2–12A and Domain Calculus Part-4 : Introduction on SQL :............................. 2–12A to 2–13A Characteristics of SQL, Advantage of SQL Part-5 : SQL Data Type and Literals,................. 2–13A to 2–22A Types of SQL Commands, SQL Operators and their Procedure Part-6 : Tables, Views and Indexes,.................... 2–22A to 2–25A Queries and Sub Queries Part-7 : Aggregate Functions, Insert,................ 2–25A to 2–29A Update and Delete Operations Part-8 : Joins, Unions,........................................... 2–29A to 2–33A Intersections, Minus Part-9 : Cursors, Triggers,.................................... 2–33A to 2–42A Procedures in SQL/PL SQL Relational Data Model & Language 2–2 A (CS/IT-Sem-5) PART-1 Relational Data Model Concept, Integrity Constraints, Entity Integrity, Referential Integrity, Keys Constraints, Domain Constraints. Questions-Answers Long Answer Type and Medium Answer Type Questions Que 2.1. What is relational model ? Explain with example. Answer 1. A relational model is a collection of conceptual tools for describing data, data relationships, data semantics and consistency constraints. 2. It is the primary data model for commercial data processing applications. 3. The relational model uses collection of tables to represent both data and the relationships among those data. 4. Each table has multiple columns and each column has a unique name. For example : 1. The tables represent a simple relational database. 2. The Table 2.1.1 shows details of bank customers, Table 2.1.2 shows accounts and Table 2.1.3 shows which accounts belong to which customer. Table 2.1.1 : Customer table cust_id c_name c_city C_101 Ajay Delhi C_102 Amit Mumbai C_103 Alok Kolkata C_104 Akash Chennai Table 2.1.2 : Account table acc_no. balance A-1 1000 A-2 2000 A-3 3000 A-4 4000 Database Management System 2–3 A (CS/IT-Sem-5) Table 2.1.3 : Depositor table cust_id acc_no. C_101 A-1 C_102 A-2 C_103 A-3 C_104 A-4 3. The Table 2.1.1, i.e., customer table, shows the customer identified by cust_id C_101 is named Ajay and lives in Delhi. 4. The Table 2.1.2, i.e., accounts, shows that account A-1 has a balance of ` 1000. 5. The Table 2.1.3, i.e., depositor table, shows that account number (acc_no) A-1 belongs to the cust whose cust_id is C_101 and account number (acc_no) A-2 belongs to the cust whose cust_id is C_102 and likewise. Que 2.2. Explain constraints and its types. Answer 1. A constraint is a rule that is used for optimization purposes. 2. Constraints enforce limits to the data or type of data that can be inserted/ updated/deleted from a table. 3. The whole purpose of constraints is to maintain the data integrity during an update/delete/insert into a table. Types of constraints : 1. NOT NULL : i. NOT NULL constraint makes sure that a column does not hold NULL value. ii. When we do not provide value for a particular column while inserting a record into a table, it takes NULL value by default. iii. By specifying NULL constraint, we make sure that a particular column cannot have NULL values. 2. UNIQUE : i. UNIQUE constraint enforces a column or set of columns to have unique values. ii. If a column has a unique constraint, it means that particular column cannot have duplicate values in a table. 3. DEFAULT : i. The DEFAULT constraint provides a default value to a column when there is no value provided while inserting a record into a table. 4. CHECK : i. This constraint is used for specifying range of values for a particular column of a table. Relational Data Model & Language 2–4 A (CS/IT-Sem-5) ii. When this constraint is being set on a column, it ensures that the specified column must have the value falling in the specified range. 5. Key constraints : i. Primary key : a. Primary key uniquely identifies each record in a table. b. It must have unique values and cannot contain null. ii. Foreign key : a. Foreign keys are the columns of a table that points to the primary key of another table. b. They act as a cross-reference between tables. 6. Domain constraints : i. Each table has certain set of columns and each column allows a same type of data, based on its data type. ii. The column does not accept values of any other data type. Que 2.3. Explain integrity constraints. Answer 1. Integrity constraints provide a way of ensuring that changes made to the database by authorized users do not result in a loss of data consistency. 2. A form of integrity constraint with ER models is : a. key declarations : certain attributes form a candidate key for the entity set. b. form of a relationship : mapping cardinalities 1-1, 1-many and many-many. 3. An integrity constraint can be any arbitrary predicate applied to the database. 4. Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Que 2.4. Explain the following constraints : i. Entity integrity constraint ii. Referential integrity constraint iii. Domain constraint Answer i. Entity integrity constraint : a. This rule states that no attribute of primary key will contain a null value. Database Management System 2–5 A (CS/IT-Sem-5) b. If a relation has a null value in the primary key attribute, then uniqueness property of the primary key cannot be maintained. Example : In the Table 2.4.1 SID is primary key and primary key cannot be null. Table 2.4.1 SID Name Class (semester) Age 8001 st 19 Ankit 1 8002 Srishti 2 nd 18 8003 Somvir 4 th 22 Sourabh 6 th 19 ii. Referential integrity constraint : a. This rule states that if a foreign key in Table 2.4.2 refers to the primary key of Table 2.4.3, then every value of the foreign key in Table 2.4.2 must be null or be available in Table 2.4.3. Foreign Key Table 2.4.2. ENO NAME Age DNO 1 Ankit 19 10 2 Srishti 18 11 3 Somvir 22 14 Not Allowed, as DNO 14 is not 4 Sourabh 19 10 defined as a primary key of Table 2.4.3, and in Table 2.4.2, DNO is a foreign key defined Relationship Table 2.4.3. DNO D.Location 10 Rohtak 11 Bhiwani 12 Hansi Primary Key iii. Domain constraints : a. Domain constraints specify that what set of values an attribute can take, value of each attribute X must be an atomic value from the domain of X. Relational Data Model & Language 2–6 A (CS/IT-Sem-5) b. The data type associated with domains includes integer, character, string, date, time, currency etc. An attribute value must be available in the corresponding domain. Example : SID Name Class (semester) Age 8001 Ankit 1st 19 8002 Srishti 1st 18 8003 Somvir 4th 22 8004 Sourabh 6th A A is not allowed here because Age is an integer attribute. PART-2 Relational Algebra. Questions-Answers Long Answer Type and Medium Answer Type Questions Que 2.5. What is relational algebra ? Discuss its basic operations. Answer 1. The relational algebra is a procedural query language. 2. It consists of a set of operations that take one or two relations as input and produces a new relation as a result. 3. The operations in the relational algebra are select, project, union, set difference, cartesian product and rename. Basic relational algebra operations are as follows : 1. Select operation : a. The select operation selects tuples that satisfies a given predicate. b. Select operation is denoted by sigma (). c. The predicate appears as a subscript to . d. The argument relation is in parenthesis after the . 2. Project operation : a. The project operation is a unary operation that returns its argument relation with certain attributes left out. Database Management System 2–7 A (CS/IT-Sem-5) b. In project operation duplicate rows are eliminated. c. Projection is denoted by pi (). 3. Set difference operation : a. The set difference operation denoted by  allows us to find tuples that are in one relation but are not in another. b. The expression r  s produces a relation containing those tuples in r but not in s. 4. Cartesian product operation : a. The cartesian product operation, denoted by a cross (×), allows us to combine information from any two relations. The cartesian product of relations r1 and r2 is written as r1 × r2. 5. Rename operation : a. The rename operator is denoted by rho (). b. Given a relational algebra expression E, x(E) returns the result of expression E under the name x. c. The rename operation can be used to rename a relation r to get the same relation under a new name. d. The rename operation can be used to obtain a new relation with new names given to the original attributes of original relation as xA1, xA2,......, xAn(E) Que 2.6. Consider the following relations : Student (ssn, name, address, major) Course (code, title) Registered (ssn, code) Use relational algebra to answer the following : a. List the codes of courses in which at least one student is registered (registered courses). b. List the title of registered courses. c. List the codes of courses for which no student is registered. d. The titles of courses for which no student is registered. e. Name of students and the titles of courses they registered to. f. SSNs of students who are registered for both database systems and analysis of algorithms. g. SSNs of students who are registered for both database systems and analysis of algorithms. h. The name of students who are registered for both database systems and analysis of algorithms. Relational Data Model & Language 2–8 A (CS/IT-Sem-5) i. List of courses in which all students are registered. j. List of courses in which all ‘ECMP’ major students are registered. AKTU 2015-16, Marks 10 Answer a. code (Registered) b. title (Course  Registered) c. code (Course) – code (Registered) d. name ((code (Course) – code (Registered))  Course) e. name, title (Student  Registered  Course)) f&g. ssn (Student  Registered  (title = ‘Database Systems’ Course))  ssn (Student  Registered  (title = ‘Analysis of Algorithms’ Course)) h. A = ssn (Student  Registered  (title = ‘Database System’ Course))  ssn (Student  Registered  (title = ‘Analysis of Algorithms’ Course)) name (A  Student) A = ( ) function i. code, ssn (Registered) / ssn (Student) j. code, ssn (Registered) / ssn (major = ‘ECMP’ Student) Que 2.7. What are the additional operations in relational algebra ? Answer The additional operations of relational algebra are : 1. Set intersection operation : a. Set intersection is denoted by  , and returns a relation that contains tuples that are in both of its argument relations. The set intersection operation is written as : r  s = r – (r – s) 2. Natural join operation : a. The natural join is a binary operation that allows us to combine certain selections and a cartesian product into one operation. It is denoted by the join symbol. b. The natural join operation forms a cartesian product of its two arguments, performs a selection forcing equality on those attributes that appear in both relation schemas and finally removes duplicate attributes. Database Management System 2–9 A (CS/IT-Sem-5) 3. Division operation : 1. In division operation, division operator is denoted by the symbol E (). 2. The relation r ÷ s is a relation on schema R – S. A tuple t is in r ÷ s if and only if both of two conditions hold : a. t is in  R–S (r). b. For every tuple ts in s, there is a tuple tr in r satisfying both of the following : i. tr[S] = ts[S] ii. tr[R – S] = t 3. The division operation can be written in terms of fundamental operation as follows : r ÷ s =  R–S (r) –  R–S (( R–S (r) × s) –  R–S, S (r)) 4. Assignment operation : The assignment operation, denoted by , works like assignment in a programming language. Que 2.8. Give the following queries in the relational algebra using the relational schema : student(id, name) enrolled(id, code) subject(code, lecturer) i. What are the names of students enrolled in cs3020 ? ii. Which subjects is Hector taking ? iii. Who teaches cs1500 ? iv. Who teaches cs1500 or cs3020 ? v. Who teaches at least two different subjects ? vi. What are the names of students in cs1500 or cs307 ? vii. What are the names of students in both cs 1500 and cs1200 ? AKTU 2019-20, Marks 07 Answer i. name(code = cs3020(student enrolledin)) ii. code(name = Hector(student enrolledin)) iii. lecturer(code = cs1500(subject)) iv. lecturer(code = cs1500  code = cs3020(subject)) v. For this query we have to relate subject to itself. To disambiguate the relation, we will call the subject relation R and S. lecturer(R.lecture = S.lecturer  R.code< >S.code(R S)) vi. name(code = cs1500(student enrolledin)) (name(code = cs307(student enrolledin))) Relational Data Model & Language 2–10 A (CS/IT-Sem-5) vii. name ( co de = cs1500 (stude nt e nro lle din))  name ( co de = cs1200(student enrolledin)) PART-3 Relational Calculus, Tuple and Domain Calculus. Questions-Answers Long Answer Type and Medium Answer Type Questions Que 2.9. What is relational calculus ? Describe its important characteristics. Explain tuple and domain calculus. OR What is tuple relational calculus and domain relational calculus ? AKTU 2019-20, Marks 07 Answer 1. Relational calculus is a non-procedural query language. 2. Relational calculus is a query system where queries are expressed as formulas consisting of a number of variables and an expression involving these variables. 3. In a relational calculus, there is no description of how to evaluate a query. Important characteristics of relati