DBMS Unit 1 Question Bank.pdf

Database Management System Lecture_1_Database System Applications and Lecture 2_Purpose: Multiple-Choice Questions (MCQs): Q1. A multinational e-commerce company has recently implemented a Database Management System (DBMS) to streamline its operations. The DBMS stores product details, customer information, order history, and transaction records. What application of a DBMS is exemplified in this scenario? A) Data Warehousing B) Customer Relationship Management (CRM) C) Online Transaction Processing (OLTP) D) Content Management System (CMS) Answer: C) Q2. A database management system (DBMS) is a a) hardware system used to create, maintain and provide controlled access to a database b) hardware system used to create, maintain and provide uncontrolled access to a database c) software system used to create, maintain and provide uncontrolled access to a database d) software system used to create, maintain and provide controlled access to a database Answer: d) Q3. A multimedia-sharing platform allows users to upload various types of content, including text, images, videos, and links. Users create posts with varying structures and content types, ranging from simple text posts to multimedia-rich content. Which type of data application is represented by this scenario, where data exhibits limited common structure, variable formats, and a wide range of content types? A) University Course Registration System B) Library Catalog Management System C) Social Networking Application 1 Database Management System D) Structured Data Management System Answer: C) Q4. Which of the following is not a feature of DBMS? a) Minimum Duplication and Redundancy of Data b) High Level of Security c) Single-user Access only d) Support ACID Property Answer: c) Q5. A company has developed a comprehensive database to store and manage information about its employees, including their personal details, job roles, salaries, and performance reviews. The database is designed to support the company's human resources department in managing employee data efficiently. Which characteristic of a database is exemplified in this scenario, where data is organized with inherent meaning and structure for a specific purpose? A) Random Data Collection B) Incoherent Data Organization C) Coherent Data Collection D) Indiscriminate Data Management Answer: C) Q6. Which of the following is not a disadvantage of a file-processing system? A) Lack of data redundancy B) Data inconsistency C) Difficulty in sharing data between applications D) Limited data security Answer: A) 2 Database Management System Two-Mark Questions: Q1. What is a Database Management System (DBMS), and explain how it facilitates various processes related to databases. Answer: Definition: A general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications Processes: Defining a database involves specifying the data types, structures, and constraints of the data to be stored in the database. Constructing the database is the process of storing the data on some storage medium that is controlled by the DBMS. Manipulating a database includes functions such as querying the database to retrieve specific data, updating the database to reflect changes in the mini world, and generating reports from the data. haring a database allows multiple users and programs to access the database simultaneously, provided they have the necessary permissions Q2. List at least 4 database applications with explanation. Answer: (Any 4) Universities: ○ Student information, course registrations, and grades. Airlines: ○ Reservations and schedule information. Telecommunication: ○ Call, text, and data usage records, billing, prepaid card balances, and communication network data. Web-based Services: ○ Social media records, online retailers' sales data, and orders, click history for targeted advertisements, etc. Document Databases: ○ Collections of articles, patents, research papers, etc. Navigation Systems: ○ Locations of places of interest, routes of roads, trains, buses, 3 Database Management System Four-Mark Questions: Q1. What is the purpose of database systems? (Hint: List the problems and solutions to them) Answer: Data Redundancy & Inconsistency: Store information in centralized location thus Minimizes redundancy Changes can be made at a single spot, thus ensuring consistency Difficulty in accessing the data: Central information store for fast retrieval Use a powerful query language for answering new queries Efficient ways to search and filter data for speeding up data retrieval Data Isolation: Centralized system Uniform and standardized way to access the data through queries so as to Simplifies complex programs Integrity problems: Specify integrity constraints at the database level to specify rules directly in the schema System should enforce constraints automatically For a new constraint, Updation of the schema should be sufficient and the rest of the things would be taken care of automatically Atomicity problems: Atomicity should be ensured Automatic rollback in case of failure DBMS would ensure this and makes it reliable and consistent even if failure occurs Concurrent Access Anomalies: Transaction and Locking to be implemented Transactions ensure atomicity Locking prevents conflicting changes. The DBMS would provide these and maintains data consistency and integrity 4 Database Management System Security problems: Create provision for administrator to define access controls and permissions for each user or group. These permissions should enforced at the database level, regardless of the application programs used. DBMS supports various methods for introducing security like User Authentication, Data Encryption, Auditing and Logging, Centralized security management Q2. What are the characteristics of database systems? Answer: Self-describing nature of a database system ○ The database system contains not only the database itself but also a complete definition or description of the database structure and constraints (Metadata). Insulation between programs and data, and data abstraction ○ The structure of data files is stored in the DBMS catalog separately from the access programs (program-data independence) Support multiple views of the data ○ DBMSs support different types of users, each of whom may require a different perspective or view of the database. A view may be a subset of the database or it may contain virtual data that is derived from the database files but is not explicitly stored. Sharing of data and multiuser transaction processing ○ A multiuser DBMS, allows multiple users to access the database at the same time. It ensures that data changes are effected in a controlled manner so that the result of the changes are correct 5 Database Management System Lecture_3_View of data, Data Model: Multiple-Choice Questions (MCQs): Q1. What defines the structure of a database and includes tools to describe data, relationships, semantics, and consistency constraints? A) Database Management System (DBMS) B) Data Query Language (DQL) C) Data Model D) Data Warehouse Answer: C) Q2. A database is designed to manage employee information for a large corporation. The database contains a table named "Employee" with columns such as "EmployeeID," "FirstName," "LastName," and "Salary." Another table, "Department," includes details like "DepartmentID," "DepartmentName," and "Location." Which data model is best suited for representing this database? A) Semi-structured Data Model B) Object-Based Data Model C) Relational Data Model D) Entity-Relationship Data Model Answer: C) Q3. A social media platform stores user profiles, each containing a variable number of attributes such as "Name," "Age," "Location," and "Interests." Users can add custom attributes to their profiles, resulting in different sets of attributes for each user. Which data model is most appropriate for representing this type of variable and semi-structured data? A) Relational Data Model B) Object-Based Data Model C) Entity-Relationship Data Model D) Semi-structured Data Model 6 Database Management System Answer: D) Q4. Which database model is an example of a record-based model, structured in fixed-format records of several types? A) Relational Model B) Hierarchical Model C) Object-Oriented Model D) NoSQL Model Answer: A) Q5. In a relational database table, what do the columns correspond to? A) Data types B) Relationships C) Attributes of the record type D) Primary keys Answer: C) Q6. A software development company maintains a database of project details, including project names, developers, tasks, and project timelines. The company's database also includes stored procedures and methods that perform various data-related operations. Which data model extension is likely integrated into their relational database for storing procedures and methods, as well as extending the relational model with object-oriented features? A) Relational Data Model B) Object-Based Data Model C) Entity-Relationship Data Model D) Semi-structured Data Model Answer: B) 7 Database Management System Two-Mark Questions: Q1. Explain Entity-Relationship Model with an example. Answer: The entity-relationship (E-R) data model uses a collection of basic objects, called entities, and relationships among these objects. An entity is a “thing” or “object” in the real world that is distinguishable from other objects. The entity-relationship model is widely used in database design. Example (any): Entities: ○ Book: Attributes include ISBN, Title, Author, Genre, etc. ○ Author: Attributes include AuthorID, Name, Birthdate, etc. ○ Library Member: Attributes include MemberID, Name, Address, etc. Relationships: ○ Author Wrote Book: Connects authors to books. ○ Member Borrowed Book: Connects library members to borrowed books, with Borrow Date and Return Date attributes. Q2. Explain relational data model with an example. Answer: In the relational model, data are represented in the form of tables. Each table has multiple columns, and each column has a unique name. Each row of the table represents one piece of information. Figure 1 presents a sample relational database comprising two tables: one shows details of university instructors and the other shows details of the various university departments. The first table, the instructor table, shows, for example, that an instructor named Einstein with ID 22222 is a member of the Physics department and has an annual salary of $95,000. The second table, department, shows, for example, that the Biology department is located in the Watson building and has a budget of $90,000. 8 Database Management System Four-Mark Questions: Q1. Explain the 4 types of data models. Answer: Relational model ○ Uses a collection of tables to represent both data and the relationships among those data. ○ Each table has multiple columns 9 Database Management System ○ The relational model is an example of a record-based model. Record-based models are so named because the database is structured in fixed-format records of several types ○ Each row of the table represents one piece of information ○ The columns of the table correspond to the attributes of the record type. ER model ○ The entity-relationship (E-R) data model uses a collection of basic objects, called entities, and relationships among these objects. ○ An entity is a “thing” or “object” in the real world that is distinguishable from other objects. ○ The entity-relationship model is widely used in database design Semi structured data model ○ The semi-structured data model permits the specification of data where individual data items of the same type may have different sets of attributes ○ JSON and Extensible Markup Language (XML) are widely used semi-structured data representations ○ A database model where there is no separation between the data and the schema, and the amount of structure used depends on the purpose. Object-based data model ○ Object-oriented programming (especially in Java, C++, or C#) has become the dominant software-development methodology. ○ Standards exist to store objects in relational tables. Database systems allow procedures to be stored in the database system and executed by the database system. ○ This can be seen as extending the relational model with notions of encapsulation, methods, and object identity. 10 Database Management System Lecture_4_Relational Data Model, Data Abstraction, Instances, and Schema: Multiple-Choice Questions (MCQs): Q1. Which of the following scenarios illustrates logical data independence in a database system? A) Changing the table structure (e.g., adding a new column) without affecting existing queries. B) Modifying an SQL query to retrieve data from a different table. C) Upgrading the database server's hardware to improve performance. D) Adding new user accounts to the database system. Answer: A) Q2. Which of the following scenarios illustrates physical data independence in a database system? A) Changing the table structure (e.g., adding a new column) without affecting existing queries. B) Modifying an SQL query to retrieve data from a different table. C) Upgrading the database server's hardware to improve performance. D) Adding new user accounts to the database system. Answer: C) Q3. A company's database contains a table named "Employee" with columns for employee ID, name, salary, and department. The HR department decides to reorganize and add a new "Division" column to the "Employee" table to track employee divisions. They make this change without affecting any of the existing queries or reports that rely on the "Employee" data. Question: In the provided scenario, what type of data independence is demonstrated? A) Physical Data Independence B) Logical Data Independence 11 Database Management System Answer: B) Q4. A company's database contains customer information, including names, addresses, and contact details. The database administrator needs to add a new index to improve query performance. Which type of data independence is being demonstrated in this scenario? A) Physical Data Independence B) Logical Data Independence Answer: A) Q5. What is the term used to describe the collection of information stored in a database at a particular moment? A) Database Schema B) Database Instance C) Database Modification D) Database Design Answer: B) Q6. In the context of a database, what is analogous to variable declarations along with associated type definitions in a programming language? A) Database Modification B) Database Schema C) Database Instance D) Database Design Answer: B) 12 Database Management System Two-Mark Questions: Q1. Explain physical and logical data independence. Answer: Physical data independence ○ It refers to the characteristic of being able to modify the physical schema without any alterations to the conceptual or logical schema, done for optimization purposes Logical data independence ○ It refers characteristic of being able to modify the logical schema without affecting the external schema or application program. The user view of the data would not be affected by any changes to the conceptual view of the data. Q2. Explain why physical data independence is important with an example. Answer: Example: Consider university database Without physical data independence: Minor changes to the storage system or hardware upgrades could potentially break existing queries, applications, and interfaces. As a result, any update to the storage system would require extensive modifications to the application layer, leading to higher maintenance costs and longer development cycles. With physical data independence The database administrators can make these changes without altering the logical schema, which means users' queries and applications will continue to work seamlessly. It allows for easier maintenance, upgrades, and performance enhancements without disrupting the application layer. Four-Mark Questions: Q1. What is a schema? Explain the 3 different types of schemas with examples. Answer: 13 Database Management System The overall design of the database is called the database schema. It is variable declarations along with the associated type definitions. Physical Schema: ○ This describes how the data is stored in the database physically. It includes details like data storage format, file organization, and indexing methods. Changes in the physical schema should not affect the applications using the database. ○ Example: How data is stored on the hard disk, like using B-trees or hash indexes for efficient retrieval. Logical Schema: ○ This defines the database design at a higher level, focusing on the structure and relationships between the tables. It is essential for application developers as they build software based on logical schema. ○ Example: Defining the relationships between tables, such as the one-to-many relationship between students and courses, where one student can be enrolled in many courses, but each course can have multiple students. View Schema (Subschema): ○ This describes different views of the database that are tailored to specific user needs or applications. Views allow users to see only relevant information and protect sensitive data. ○ Example: Creating a view that shows only the names of enrolled students and their corresponding course names, hiding other details like grades and personal information. Q2. What is data abstraction? Explain the different levels of data abstraction. Answer: Data abstraction refers to the process of simplifying complex data structures and operations by providing a high-level, user-friendly interface while hiding the underlying details of how data is stored and managed. Physical Level : ○ It is the lowest level of data abstraction ○ It describes how the data is actually stored in the computer Logical Level : ○ This describes what kind of data is stored in the database and how different pieces of information are related to each other ○ The logical level thus describes the entire database in terms of a small number of relatively simple structures. 14 Database Management System ○ This level provides a more organized and simpler view of the data View Level : ○ This is the highest level of abstraction that individual users interact with and that only displays a part of the entire database ○ Even though the logical level uses simpler structures, complexity remains because of the variety of information stored in a large database. Many users of the database system do not need all this information; instead, they need to access only a part of the database. ○ At this level, different users might see different parts of the database, depending on their needs and permissions 15 Database Management System Lecture_5_Database Languages: Multiple-Choice Questions (MCQs): Q1. 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 Answer: a) Q2. Which type of SQL language is represented by the following statement: sql Copy code CREATE TABLE department ( dept name CHAR(20), building CHAR(15), budget NUMERIC(12,2) ); Options: A) DDL (Data Definition Language) B) DML (Data Manipulation Language) C) DCL (Data Control Language) D) TCL (Transaction Control Language) Answer: A) Q3. Which SQL language is used to enforce constraints like UNIQUE, PRIMARY KEY, and FOREIGN KEY on database tables? Options: A) DDL (Data Definition Language) B) DML (Data Manipulation Language) C) DCL (Data Control Language) 16 Database Management System D) TCL (Transaction Control Language) Answer: A) Q4. ______________ is a set of one or more attributes taken collectively to uniquely identify a record. a) Primary Key b) Foreign key c) Super key d) Candidate key Answer: a) Q5.Which of the following is typically NOT found in a data dictionary? A) Database schema B) Integrity constraints C) Authorization D) Backup and recovery procedures Answer: D) Q6. What is the primary purpose of a Domain Constraint in a database? A) It enforces referential integrity between tables. B) It defines the relationships between entities. C) It specifies the data type of an attribute and restricts the range of valid values. D) It ensures that each row in a table has a unique identifier. Answer: C) Two-Mark Questions: Q1. What is DML? What are the two types of data manipulation language? Answer: A data manipulation language (DML) is a language that enables users to access or manipulate data as organized by the appropriate data model. There are basically two types of data-manipulation language: 17 Database Management System Procedural DMLs - require a user to specify what data are needed and how to get those data. Declarative DMLs - (also referred to as nonprocedural DMLs) require a user to specify what data are needed without specifying how to get those data. Q2. Is SQL a procedural or non-procedural query language? Explain the limitations of SQL compared to general-purpose programming languages. Answer: SQL is a non-procedural query language. Non-procedural query languages such as SQL are not as powerful as a universal Turing machine; that is, there are some computations that are possible using a general-purpose programming language but are not possible using SQL. SQL does not support actions such as input from users, output to displays, or communication over the network. Such computations and actions must be written in a host language, such as C/C++, Java, or Python, with embedded SQL queries that access the data in the database. Four-Mark Questions: Q1. Explain about the 4 database languages in detail. Answer: Data Definition Language (DDL): ○ Purpose: DDL is used to define and manage the structure and schema of a database. It focuses on creating, altering, and deleting database objects such as tables, indexes, views, and constraints. ○ Examples: SQL statements like CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, CREATE VIEW, etc. ○ Use Case: Database administrators and designers use DDL to establish the database's logical and physical structure. Data Manipulation Language (DML): ○ Purpose: DML is used for querying and manipulating data within a database. It allows users to insert, retrieve, update, and delete data stored in tables. ○ Examples: SQL statements like SELECT, INSERT, UPDATE, DELETE, etc. 18 Database Management System ○ Use Case: Application developers and end-users use DML to interact with the data in a database, fetching information or making changes as needed. Data Control Language (DCL): ○ Purpose: DCL is responsible for controlling access to the database. It defines and manages user permissions, privileges, and security settings to ensure data integrity and protect sensitive information. ○ Examples: SQL statements like GRANT, REVOKE, and DENY to control who can perform specific actions on database objects. ○ Use Case: Database administrators use DCL to grant or restrict access rights to users and roles, ensuring data security and compliance with privacy regulations. Transactional Control Language (TCL): ○ Purpose: TCL is used to manage transactions within a database. It provides commands for explicitly starting, committing, or rolling back transactions to maintain data consistency and integrity. ○ Examples: SQL statements like COMMIT, ROLLBACK, and SAVEPOINT. ○ Use Case: Developers and database administrators use TCL to ensure that database transactions are executed reliably and that the data remains in a consistent state even in the event of failures. Q2. a) Explain referential integrity with an example. What is the normal procedure if referential integrity is violated? b) Explain the different types of authorization. Answer: a) There are cases where we wish to ensure that a value that appears in one relation for a given set of attributes also appears in a certain set of attributes in another relation (referential integrity). For example, the department listed for each course must be one that actually exists in the university. More precisely, the dept name value in a course record must appear in the dept name attribute of some record of the department relation. Database modifications can cause violations of referential integrity. When a referential integrity constraint is violated, the normal procedure is to reject the action that caused the violation. b) read authorization: which allows reading, but not modification, of data; 19 Database Management System insert authorization: which allows insertion of new data, but not modification of existing data; update authorization: which allows modification, but not deletion, of data; delete authorization: which allows deletion of data. 20 Database Management System Lecture_6_Database Design. Database Engine: Multiple-Choice Questions (MCQs): Q1. Which of the following is not a functional component of a database system A) The storage manager B) The query processor component C) The transaction management component D) The user interface Answer: D) Q2. Which of the following is not the responsibility of the storage manager in a database system? A) Interaction with the OS file manager B) Efficient storing, retrieving, and updating of data C) Query optimization D) Managing disk space allocation Answer: C) Q3. Which of the following is not a component of the query processor in a database system? A) DDL interpreter B) DML compiler C) Query optimization engine D) Storage manager Answer: D) Q4. What is one of the primary responsibilities of the buffer manager in a storage manager? A) Managing database transactions B) Interpreting Data Definition Language (DDL) C) Caching data pages in memory D) Optimizing query execution plans 21 Database Management System Answer: C) Q5. In a database system, a user attempts to execute a SQL query that retrieves sensitive financial information about customers. However, the user does not have the necessary permissions to access this data. In this scenario, which component of the storage manager is primarily used to handle the user's request and ensure data security? A) Authorization and integrity manager B) Transaction manager C) File manager D) Buffer manager Answer: A) Q6. A series of database transactions are being executed, involving the insertion of new records into a table, updating existing records, and ensuring that these changes are consistent and durable. In this scenario, which component of the storage manager is primarily responsible for coordinating and ensuring the ACID (Atomicity, Consistency, Isolation, Durability) properties of these transactions? A) Authorization and integrity manager B) Transaction manager C) File manager D) Buffer manager Answer: B) Two-Mark Questions: Q1. Explain the various data structures implemented by the storage manager. Answer: Data files -- store the database itself Data dictionary -- stores metadata about the structure of the database, in particular the schema of the database. 22 Database Management System Indices -- can provide fast access to data items. A database index provides pointers to those data items that hold a particular value. Q2. What is a transaction? Explain what the transaction management component does. Also, mention properties of a transaction. Answer: A transaction is a collection of operations that performs a single logical function in a database application Transaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures. Transaction properties: A transaction is a logical unit of work that must maintain atomicity (all-or-none execution), consistency (database remains valid throughout), and durability (changes are permanent even after system failure). Consistency and atomicity: During the execution of a transaction, temporary inconsistency may be allowed, but the database must return to a consistent state after the transaction completes successfully. Four-Mark Questions: Q1. Explain the various steps involved in the database design process. Answer: User requirement specification ○ Interact with domain experts and users to fully understand the data needs and functional requirements of the database. ○ Identify the operations or transactions that users will perform on the data. ○ Document and review the user requirements to ensure clarity and accuracy. Conceptual design phase ○ Choose a high-level data model (eg. entity-relationship model) to conceptualize the data requirements and relationships. ○ Develop a conceptual schema that provides a detailed overview of the enterprise's data structure. ○ Review the schema to ensure all data requirements are met and remove any redundant features. Logical design phase 23 Database Management System ○ Map the high-level conceptual schema to the implementation data model of the database system that will be used (eg. relational model) ○ Use normalization algorithms to generate a set of tables that properly group attributes and reduce data redundancy. ○ Confirm that the logical design meets the functional requirements specified in the user requirements phase. Physical design phase ○ Specify the physical features of the database, including file organization and internal storage structures. ○ Consider performance optimization and choose appropriate indexing techniques to enhance data retrieval speed. ○ Review and refine the physical design to ensure it aligns with the requirements and constraints of the database system and hardware. Q2. Mention the components of the query processor. Explain the internal structure of the query processor with a diagram. Answer: The query processor components include: DDL interpreter DML compiler ○ performs query optimization, i.e. it picks the lowest cost evaluation plan from among the various alternatives. Query evaluation engine Query processor: 24 Database Management System Application Program Object Code: ○ Application programs, such as software applications or web services, generate SQL queries as part of their functionality. ○ The application program object code contains the SQL statements generated by the program for interaction with the database. Compiler and Linker: ○ The compiler takes the source code of the application program and translates it into machine code or intermediate code. ○ The linker combines various object files, libraries, and modules to create the executable code. Query Evaluation Engine: ○ The query evaluation engine is a critical component of the query processor. ○ It takes SQL queries from the application program object code and executes them against the database. ○ The engine performs tasks like query parsing, query optimization, data retrieval, and result presentation. DML Queries: ○ Data Manipulation Language (DML) queries are SQL statements used to interact with and manipulate data within the database. DML Compiler and Organizer: ○ The DML compiler is responsible for parsing DML queries and creating an execution plan. ○ It translates SQL queries into an internal format that the query evaluation engine can understand. ○ The DML organizer is responsible for determining the most efficient sequence of operations to execute the query, considering indexes, data retrieval methods, and join strategies. DDL Interpreter: ○ Data Definition Language (DDL) statements are used for defining and modifying the database schema, such as creating tables, indexes, and constraints. ○ The DDL interpreter processes DDL statements, validates them, and modifies the database schema accordingly. 25 Database Management System Lecture_7_Introduction to databases,Database application architecture: Multiple-Choice Questions (MCQs): Q1. In a database application, the client machine directly invokes database system functionality at the server machine through SQL query statements. The client handles both the user interface and database communication. Which architecture is being described in this scenario? A) Two-tier architecture B) Three-tier architecture C) Single-tier architecture D) Four-tier architecture Answer: A) Q2. In a database application, the client machine serves as a front end, communicating with an application server. The application server, in turn, communicates with the database system to access data. The business logic of the application is embedded in the application server. Which architecture is being described in this scenario? A) Two-tier architecture B) Three-tier architecture C) Client-server architecture D) Peer-to-peer architecture Answer: B) Q3. Which are the two main measures of performance of a Database Management System (DBMS)? A) Scalability and latency B) Throughput and response time C) Availability and security D) Data integrity and redundancy 26 Database Management System Answer: B) Q4. Which of the following statements is true regarding centralized databases and their architecture? A) Centralized databases are highly scalable for larger data volumes and processing speeds. B) Centralized databases primarily use distributed server architectures. C) Centralized databases typically have one to a few cores and shared memory. D) Centralized databases are most suitable for parallel processing on thousands of cores. Answer: C) Two-Mark Questions: Q1. Explain about centralized databases Answer: Suitable for shared-memory server architectures with multiple CPUs accessing a common shared memory. Limited scalability for larger data volumes and higher processing speeds. One to a few cores, shared memory Q2. Explain about client-server database architecture Answer: Client-server database architecture separates database functionality into two components: the client and the server. The client handles user interaction, sends requests to the server, and presents data, while the server hosts the database, manages data storage, processing, security, and concurrency control. This architecture provides scalability, centralized data management, data security, support for concurrent access, and remote access capabilities. Four-Mark Questions: Q1. Explain the following database architectures: a) Centralized database b) Client-server c) Parallel databases 27 Database Management System d) Distributed databases Answer: a) Suitable for shared-memory server architectures with multiple CPUs accessing a common shared memory. Limited scalability for larger data volumes and higher processing speeds. One to a few cores, shared memory b) Client-server database architecture separates database functionality into two components: the client and the server. The client handles user interaction, sends requests to the server, and presents data, while the server hosts the database, manages data storage, processing, security, and concurrency control. c) Designed to run on a cluster of multiple machines, enables better scalability and higher processing capabilities. Many cores shared memory Shared disk d) Allow data storage and query processing across geographically separated machines, facilitating large-scale data management. Schema/data heterogeneity Q2. Explain two-tier and three-tier architecture in detail with a diagram. Answer. Earlier-generation database applications used a two-tier architecture, where the application resides at the client machine, and invokes database system functionality at the server machine through query language statements. In contrast, modern database applications use a three-tier architecture, where the client machine acts as merely a front end and does not contain any direct database calls; web browsers and mobile applications are the most commonly used application clients today. The front end communicates with an application server. The application server, in turn, communicates with a database system to access data. The business logic of the application, which says what actions to carry out under what conditions, is embedded in the application server, instead of being distributed across multiple clients. Three tier applications provide better security as well as better performance than two-tier applications. 28 Database Management System 29 Database Management System Lecture_8_Users and Administrators: Multiple-Choice Questions (MCQs): 1. What is the primary user interface for naıve users interacting with a database? A) Command-line interface B) Forms interface C) Graphical user interface D) Query language interface Answer: B) Forms interface 2. Which category of users interacts with a database by submitting queries or using data analysis software? A) Naıve users B) Application programmers C) Sophisticated users D) Database administrators Answer: C) Sophisticated users 3. What is one of the key functions of a database administrator (DBA)? A) Writing application programs B) Running database queries C) Creating web interfaces D) Schema definition and modification Answer: D) Schema definition and modification 4. What does the DBA do to reflect the changing needs of the organization regarding the database schema? A) Execute data analysis software B) Create new database tables C) Modify the schema and physical organization D) Grant authorization for data access Answer: C) Modify the schema and physical organization 5. What is the purpose of granting authorization for data access by the DBA? A) To create new database users B) To regulate which parts of the database users can access C) To optimize database performance D) To define database schemas 30 Database Management System Answer: B) To regulate which parts of the database users can access 6. Which routine maintenance activity is performed by the DBA to prevent data loss in case of disasters? A) Creating database backups B) Upgrading disk space C) Monitoring jobs running on the database D) Granting data access authorization Answer: A) Creating database backups Two-Mark Questions: 1. Explain the role of naıve users in a database system and provide an example of their interaction with the system. Answer: Naıve users are unsophisticated users who interact with the system using predefined user interfaces, such as web or mobile applications. They typically use form interfaces to input information into the database. For example, a student registering for a class during the registration period can connect to a web application, fill in a form with class information, and submit it. The web application, in turn, interacts with the database to check class availability and add the student's information to the class roster if there is space. 2. Describe the key functions of a database administrator (DBA) in a database management system. Answer: The key functions of a database administrator (DBA) include: Schema definition: Creating and defining the original database schema using data definition statements (DDL). Storage structure and access-method definition: Specifying parameters related to the physical organization of data and the creation of indices. Schema and physical-organization modification: Adapting the schema and physical organization to meet changing organizational needs or improve performance. Granting authorization for data access: Regulating user access to different parts of the database by granting various types of authorization. Routine maintenance: Performing activities like database backups, ensuring disk space availability, and monitoring database jobs to maintain data integrity and performance. 31 Database Management System Four-Mark Questions: 1. Compare and contrast naıve users and sophisticated users in a database system. Provide examples of each. Answer: Naıve users and sophisticated users differ in their interaction with a database system: Naıve users are unsophisticated and use predefined user interfaces like forms. For example, a student uses a web interface to register for a class. Sophisticated users interact without writing programs, or using queries or data analysis tools. For example, analysts query a database to explore data. Naıve users rely on intuitive interfaces for data input, while sophisticated users have a deeper understanding of database querying and analysis tools. Sophisticated users have more control over their queries, while naıve users follow structured forms for data entry. 2. Explain the role of the database administrator (DBA) in managing a database system's performance and security. Provide examples of DBA tasks related to each aspect. Answer: Performance Management: The DBA monitors database performance to ensure efficient operations. Example Task: Identifying and optimizing slow-running database queries. The DBA ensures that system resources are allocated effectively. Example Task: Adjusting database parameters to optimize resource utilization. Routine maintenance activities like regular backups and disk space management prevent performance degradation. Example Task: Periodically backing up the database onto remote servers. Security Management: The DBA grants and manages user access rights and permissions. Example Task: Granting specific access privileges to different user roles. 32 Database Management System The DBA maintains authorization information and enforces data access controls. Example Task: Reviewing and updating access control policies. The DBA protects data integrity and confidentiality. Example Task: Monitoring and responding to security breaches or unauthorized access attempts 33 Database Management System Lecture_9_Structure of relational databases, Database schema: Multiple-Choice Questions (MCQs): 1. In the relational model, what does a tuple represent? A) A table B) A column C) A relationship among a set of values D) A domain Answer: C) A relationship among a set of values 2. What mathematical concept closely corresponds to the concept of a table in the relational data model? A) Set B) Tuple C) Domain D) Attribute Answer: A) Set 3. In the relational model, what does the term "attribute" refer to? A) A row in a table B) A column of a table C) A relationship among values D) A domain of values Answer: B) A column of a table 4. What is a specific instance of a relation called in the relational model? A) Tuple B) Attribute C) Domain D) Schema Answer: A) Tuple 5. To ensure atomicity of domains in a relation, what does it mean for an attribute's domain to be atomic? A) It contains multiple subparts. B) Each element within the domain is considered one indivisible unit. C) It can store complex data structures. D) It allows null values. 34 Database Management System Answer: B) Each element within the domain is considered one indivisible unit. 6. What does a null value represent in a database attribute? A) An empty string B) An undefined or missing value C) A default value D) A value of zero Answer: B) An undefined or missing value Two-Mark Questions: 1. Explain the concept of a database schema and how it differs from a database instance. Answer: A database schema is a logical design that defines the structure of a database, including tables, columns, and their relationships. It specifies the attributes and their domains. In contrast, a database instance represents the actual data stored in the database at a specific moment in time. The schema defines the structure, while the instance contains the data. 2. Discuss the importance of minimizing the use of null values in a database and the challenges associated with them. Answer: Minimizing the use of null values in a database is essential for maintaining data consistency and integrity. Null values represent missing or undefined data, making it challenging to perform calculations, comparisons, and queries accurately. They can lead to unexpected results and hinder data analysis. Proper data validation and the use of default values help avoid nulls and ensure data completeness. Four-Mark Questions: 1. How can common attributes in relation schemas facilitate the establishment of relationships between tuples in distinct relations within the relational model? Provide an example. Answer: Common attributes in relation schemas play a crucial role in establishing relationships between tuples in distinct relations. They act as keys or linkages between related data. For example, consider two relations: "Employees" and "Departments." If both relations have a common attribute, such as "DepartmentID," it can be used to create a relationship between employees and their respective departments. By matching "DepartmentID" values, you can determine which employees belong to which departments, enabling efficient data retrieval and analysis. This relationship is 35 Database Management System established through the use of joins or relational algebra operations based on the common attribute values. 36 Database Management System Lecture_10_Keys, Schema Diagrams, Relational Query Languages: Multiple-Choice Questions (MCQs): 1. What is the primary purpose of constraints in a database? A) To define the structure of the database schema B) To determine the order of tuples in a relation C) To specify how tuples are distinguished within a relation D) To ensure the uniqueness and integrity of data values Answer: D) To ensure the uniqueness and integrity of data values 2. Which type of constraint is expressed using the facilities provided by the data model and can be inherent or explicit? A) Inherent constraint B) Schema-based constraint C) Application-based constraint D) Superkey constraint Answer: B) Schema-based constraint 3. What is the main purpose of a primary key in a relation schema? A) To allow null values for identification B) To identify the smallest candidate key C) To uniquely identify each tuple in the relation D) To establish relationships between multiple relations Answer: C) To uniquely identify each tuple in the relation 4. What is a superkey in the context of a relation schema? A) A subset of attributes that uniquely identifies a tuple within the relation B) The smallest candidate key in a relation C) An extraneous attribute that distinguishes tuples within a relation D) A primary key value used for tuple identification Answer: A) A subset of attributes that uniquely identifies a tuple within the relation 5. What does a referential integrity constraint require in a database? A) That all primary keys are used as foreign keys B) That referenced attributes form the primary key of the referenced relation C) That the values in the referencing relation exist in the referenced relation D) That null values are allowed in foreign key columns Answer: C) That the values in the referencing relation exist in the referenced relation 37 Database Management System 6. In a foreign-key constraint, what is the purpose of allowing null values in the foreign-key column? A) To make the primary key of the referenced relation optional B) To establish a relationship between the referencing and referenced relations C) To create an extraneous attribute in the referencing relation D) To define a candidate key for the referencing relation Answer: A) To make the primary key of the referenced relation optional Two-Mark Questions: 1. Explain the difference between a superkey and a candidate key in the context of a relation schema. Answer: A superkey is a set of one or more attributes that, taken collectively, allow us to uniquely identify a tuple in the relation. It may contain extraneous attributes. In contrast, a candidate key is a minimal superkey, meaning it is a superkey for which no proper subset is also a superkey. Candidate keys are chosen based on their ability to uniquely identify tuples while minimizing the number of attributes involved. 2. What is the purpose of a primary key in a relational database, and why should primary keys be chosen with care? Answer: The primary key in a relational database serves the purpose of uniquely identifying each tuple in a relation. It provides the tuple identity and is used to reference the tuple from other tuples. Primary keys should be chosen with care because they play a crucial role in maintaining data integrity and consistency. The attributes selected as primary keys should ideally never change or rarely change, and they should be unique to ensure accurate identification of tuples. Choosing an inappropriate primary key can lead to data management challenges and inaccuracies in the database. Four-Mark Questions: 1. Describe the concept of referential integrity constraints in a relational database. Provide an example of a scenario where a referential integrity constraint is needed. Answer: Referential integrity constraints in a relational database ensure that the values appearing in specified attributes of a referencing relation also appear in specified 38 Database Management System attributes of the referenced relation. This constraint relaxes the requirement that the referenced attributes form the primary key of the referenced relation. Example: Consider two relations, "Students" and "Courses." In the "Courses" relation, there is an attribute called "Instructor_ID," which represents the instructor teaching the course. The "Instructor_ID" should be a value that exists in the "Instructors" relation, ensuring that only valid instructor IDs are associated with courses. This is an example of a referential integrity constraint, as it enforces a relationship between the "Instructors" and "Courses" relations based on the "Instructor_ID" attribute. 2. Discuss the importance of entity integrity constraints in a relational database. Explain how entity integrity constraints ensure data integrity. Answer: Entity integrity constraints in a relational database specify that the primary key attributes of each relation schema cannot have null values in any tuple of that relation. These constraints are crucial for data integrity and consistency because they ensure that the primary key attributes, which are used to uniquely identify tuples, are never left undefined or missing. Entity integrity constraints help maintain data integrity by ensuring the following: Primary key attributes are always populated with meaningful values. Tuples can be accurately and uniquely identified using the primary key. Relationships between tuples in different relations can be established based on the primary key. By enforcing entity integrity constraints, a relational database prevents the creation of tuples with missing or null primary key values, thereby enhancing the reliability and accuracy of data stored in the database. 39 Database Management System Lecture_11_Database design- Overview: Multiple-Choice Questions (MCQs): 1. Why is the design phase important in database development? A) To easily change the physical schema B) To avoid redundancy in the logical schema C) To determine the order of queries in an application D) To address changes in the application code Answer: B) To avoid redundancy in the logical schema 2. What is the main challenge associated with redundancy in a database design? A) Inefficient storage of data B) Inconsistent data updates C) Difficulty in modeling new information D) Loss of data integrity Answer: B) Inconsistent data updates 3. In a university database, which entity would represent an instructor, student, or department? A) Entity integrity B) Redundancy entity C) Logical entity D) Entity definition Answer: D) Entity definition 4. What is the primary drawback of incompleteness in a database design? A) Difficulty in modeling new courses B) Inefficient storage of course offerings C) Inconsistent course titles D) Loss of data integrity Answer: A) Difficulty in modeling new courses 5. What does redundancy in a database design often lead to? A) Incomplete data representation B) Inefficient storage C) Loss of data integrity D) Improved data consistency Answer: C) Loss of data integrity 40 Database Management System 6. Why is it essential to carefully consider design alternatives in database design? A) To avoid redundancy in all cases B) To minimize the complexity of design choices C) To rely solely on scientific principles D) To accommodate all possible entities Answer: B) To minimize the complexity of design choices Two-Mark Questions: 1. Explain why redundancy in a database design can lead to inconsistent data. Answer: Redundancy in a database design can lead to inconsistent data because when the same information is repeated unnecessarily in multiple places, updates to one copy of the data may not be reflected in other copies. For example, if a course title is stored redundantly for each course offering, updating the title in one place may be forgotten or overlooked in other places, leading to inconsistent course titles across different sections. This inconsistency compromises data integrity and accuracy. 2. What is the significance of the entity integrity constraint in a database design, and how does it contribute to data quality? Answer: The entity integrity constraint in a database design ensures that the primary key attributes of each relation cannot have null values in any tuple. This constraint is significant because it enforces the presence of meaningful values in primary key attributes, which are used to uniquely identify tuples. By adhering to this constraint, data quality is improved because it guarantees that each tuple can be accurately identified and referenced, preventing incomplete or missing information that would otherwise lead to data inconsistency and inaccuracies. Four-Mark Questions: 1. Discuss the challenges associated with incompleteness in a database design, using an example scenario. Answer: Incompleteness in a database design can pose challenges when trying to represent new information that is not currently included in the database. Let's consider an example scenario involving a university course management system. If the database design only includes entities for course offerings (sections) but does not have a 41 Database Management System separate entity for courses themselves, it becomes challenging to represent new courses that are not currently offered. Any course that does not have an associated section in the database cannot be represented, making it difficult to maintain a comprehensive course catalog. 2. Explain the design alternatives (any 4) Answer: Avoiding Poor Designs is Insufficient: ○ Simply avoiding bad designs is not enough in database design. ○ Multiple good design options might be available, creating the need to select wisely. The Complexity of Design Choices: ○ For instance, consider a simple scenario where a customer buys a product. ○ A key decision arises: Is the sale a relationship between customer and product, or is the sale itself a distinct entity connected to both customer and product? Impact of Design Choices: ○ This choice can significantly influence the effective modeling of various enterprise aspects. ○ Different design approaches might lead to different insights and representations of business operations. Scale of Decision-Making: ○ In real-world scenarios, numerous entities and relationships require similar decisions. ○ This makes database design a challenging task, demanding careful consideration. Database Design Challenge: ○ The complexity of design choices highlights the intricate nature of database design. ○ It involves determining suitable structures for various entities and relationships. Combination of Science and Aesthetic Judgment: ○ Database design necessitates a balanced approach of scientific principles and intuitive "good taste." ○ Successful design involves a mix of methodical analysis and informed decision-making. 42 Database Management System Lecture_12_The ER model: Multiple-Choice Questions (MCQs): 1. What is the primary purpose of the Entity-Relationship (E-R) model in database design? A) To define the physical structure of a database B) To capture the logical structure of a database C) To manage database queries and updates D) To implement database security features Answer: B) To capture the logical structure of a database 2. In the E-R model, what is an entity set? A) A group of related attributes B) A collection of similar entities C) An association between entities D) A set of database tables Answer: B) A collection of similar entities 3. What do primary key attributes represent in an E-R diagram? A) Entity relationships B) Descriptive attributes C) Unique identification for entities D) Relationships between entities Answer: C) Unique identification for entities 4. What is the purpose of descriptive attributes in the E-R model? A) To define the logical structure of a database B) To capture unique identification for entities C) To represent characteristics of entity sets D) To describe the role of entities in relationships Answer: C) To represent characteristics of entity sets 5. How are relationships represented in an E-R diagram? A) By rectangles B) By ovals C) By diamonds D) By lines Answer: C) By diamonds 43 Database Management System 6. In an E-R diagram, what is the function of role indicators? A) To indicate the degree of a relationship set B) To specify the order of entity sets in a relationship C) To clarify the meaning of a relationship when entity sets are not distinct D) To represent entity sets participating in a relationship Answer: C) To clarify the meaning of a relationship when entity sets are not distinct Two-Mark Questions: 1. What are recursive relationships? Answer: The entity sets of a relationship set are not distinct; that is, the same entity set participates in a relationship set more than once, in different roles, this type of relationship set is called a recursive relationship set. 2. What are relationship and relationship sets? How do we depict relationships in ER diagrams? Answer: A relationship signifies connections between entities. A relationship set contains relationships of the same type. In an E-R diagram, relationships are depicted by diamonds. Four-Mark Questions: 1. Explain the following terms- entity, entity set, relationship sets, and attributes. Answer: Entity Sets: Represent groups of similar entities from the real world. Each entity set corresponds to a table in a relational database. Relationship Sets: Capture associations between different entity sets. These relationships establish connections between data elements. Attributes: Describe characteristics or properties of entities or relationships, helping to define their attributes and properties. What is an Entity? An entity is a distinct "thing" or "object" in the real world. It's different from other objects and can be a person, course, or more. 44 Database Management System 2. What is an ER diagram? What are the notations for relationships, entities, attributes? Answer: E-R diagrams provide a graphical representation of the database's logical structure. They use symbols to depict entities, relationships, and attributes, making complex relationships easier to understand. Notations for relationships- diamonds, entities- rectangle and attributes-.ellipse 45 Database Management System Lecture_13_Complex Attributes: Multiple-Choice Questions (MCQs): 1. An attribute used in E-R model can be characterized as: A) Simple / Composite attribute B) Single-valued / Multivalued attributes C) Derived attributes D) All of the above Answer: D) All of the above 2. Let us say the address of the student is: "123 Main Street, Vasundhara, Delhi". What kind of attribute is this? A) Multivalued B) Composite C) Compound D) Atomic Answer: B) Composite 3. We can have a hierarchy of composite attributes. A) True B) False Answer: A) True 4. Suppose the university has decided to add the phone number to the instructor relation. What kind of an attribute is it? A) Multivalued B) Composite C) Compound D) Atomic Answer: B) Multivalued 5. Consider the following statements: Statement 1: Composite attributes cannot be broken down into subparts Statement 2: Composite attributes are formed by nesting complex and multivalued attributes Statement 3: Derived attributes are attributes whose values are derived from existing attributes 46 Database Management System A) Statement 1 is false, Statement 2 & 3 are true B) All of them are true C) Statement 3 is true, 2 & 1 are false D) Only statement 2 is true Answer: A) Statement 1 is false, Statement 2 & 3 are true 6. What does a null value indicate? A) Value is missing B) Value is unknown C) Value not applicable D) all of the above Answer: D) all of the above Two-Mark Questions: 1. What are multivalued attributes? Give an example Answer: These types of attributes can have multiple values are called as multivalued Attributes Examples for the same are : ○ Instructor Phone numbers ○ Instructors mail_id 2. What are derived and stored attributes? Answer: The stored attribute are those attributes that are actually stored in the database. ○ Example: Date_of_Birth is a stored attribute An attribute that can be derived from other attributes is derived attributes. ○ Example: the Age attribute of the instructor is a derived attribute. Four-Mark Questions: 1. Explain any kinds of attributes with examples. Answer: The attributes that cannot be further subdivided into components is a simple attribute ○ Ex: The ID of the student or Instructor, Roll number of the student, phone number, email id, etc. 47 Database Management System On the contrary, those attributes that can be split into components are called a composite attribute ○ Ex: The address can be further split into house number, street number, city, state, country, and pin code. The attribute which takes up more than a single value for each entity instance is a multi-valued attribute. ○ Example: Phone number of the instructor, Mail id, etc. The attribute that takes up only a single value for each entity instance is a single-valued attribute. ○ Example: The name of the instructor, age of the student, etc. The stored attribute are those attributes that are actually stored in the database. ○ Example: Date_of_Birth is a stored attribute An attribute that can be derived from other attributes is derived attributes. ○ Example: the Age attribute of the instructor is a derived attribut 48 Database Management System Lecture_14_Mapping Cardinalities: Multiple-Choice Questions (MCQs): 1.What does a one-to-many cardinality ratio in an E-R diagram signify? a) An entity in A can be associated with many entities in B b) An entity in B can be associated with many entities in A c) An entity in A can be associated with at most one entity in B d) An entity in B can be associated with at most one entity in A Answer: c) An entity in A can be associated with at most one entity in B A one-to-many cardinality ratio means that an entity in A can be associated with multiple entities in B, while an entity in B can be associated with at most one entity in A. 2.In the context of E-R diagrams, what does a directed line (→) from a relationship set to an entity set indicate? a) Total participation of the entity in the relationship b) Partial participation of the entity in the relationship c) One-to-one mapping cardinality d) Many-to-many mapping cardinality Answer: a) Total participation of the entity in the relationship A directed line (→) from a relationship set to an entity set indicates total participation, meaning that every entity in the entity set must participate in at least one instance of the relationship. 3.If an E-R diagram shows a double line connecting a student to the advisor relationship set, what does it signify? a) A student must have multiple advisors b) A student must have exactly one advisor c) A student may or may not have an advisor d) An advisor must advise multiple students Answer: b) A student must have exactly one advisor A double line connecting a student to the advisor relationship set signifies total participation of the student, implying that every student must have exactly one advisor. 4.What is the purpose of using minimum and maximum cardinalities (e.g., 1..1, 0..*) in an E-R diagram? a) To indicate total participation of entities in a relationship b) To specify the number of attributes in an entity c) To define the data type of an attribute d) To represent primary key constraints 49 Database Management System Answer: a) To indicate total participation of entities in a relationship Minimum and maximum cardinalities are used to specify the degree of participation of entities in a relationship set. For example, 1..1 indicates total participation, while 0..* means no limit. 5.In a university database, if there is an arrow pointing from "proj guide" to "instructor," what does this indicate? a) An instructor can have multiple project guides b) An instructor can guide multiple projects c) A student can have multiple project guides d) A project can have multiple instructors as guides Answer: d) A project can have multiple instructors as guides An arrow pointing from "proj guide" to "instructor" implies that a project can have multiple instructors serving as guides, indicating a many-to-many relationship between projects and instructors. 6.In an Entity-Relationship (ER) model, suppose is a many-to-one relationship from entity set E1 to entity set E2. Assume that E1 and E2 participate totally in and that the cardinality of E1 is greater than the cardinality of E2. Which one of the following is true about ? A. Every entity in E1 is associated with exactly one entity in E2 B. Some entity in E1 is associated with more than one entity in E2 C. Every entity in E2 is associated with exactly one entity in E1 D. Every entity in E2 is associated with at most one entity in E1 Answer: A)Every entity in E1 is associated with exactly one entity in E2 Since it is a many to one relationship from E1 to E2,therefore: 1.No entity in E1 can be related to more than one entity in E2 [hence B is incorrect] 2.An entity in E2 can be related to more than one entity in E1 [hence C and D are in correct] 50 Database Management System Two-Mark Questions: Imagine you are designing a database for a music streaming service. In this service, users can create playlists and add songs to them. Each song can belong to multiple playlists, and each playlist can contain multiple songs. Determine the cardinality between the "User" entity and the "Playlist" entity, as well as between the "Song" entity and the "Playlist" entity in this scenario. 1.What is the cardinality between the "User" entity and the "Playlist" entity in the music streaming service database? Answer: Many-to-many Explanation: A user can create and manage multiple playlists, and a playlist can be created by multiple users (e.g., collaborative playlists). 2.What is the cardinality between the "Song" entity and the "Playlist" entity in the music streaming service database? Answer: Many-to-many Explanation: A song can be included in multiple playlists, and a playlist can contain multiple songs. Users can create diverse playlists with various songs. Four-Mark Questions: 1.In a retail management system, a company wants to design a database to track its inventory and supplier relationships. They have two primary entity sets: "Product" and "Supplier." Each product can be supplied by one or more suppliers. Each supplier can supply multiple products. The company wants to ensure that every product is supplied by at least one supplier. Suppliers are not obligated to supply any specific product. Based on this scenario, determine the mapping cardinality and participation constraints between the "Product" and "Supplier" entity sets. Answers: Mapping Cardinality: The mapping cardinality between "Product" and "Supplier" is many-to-many because each product can be supplied by multiple suppliers, and each supplier can supply multiple products. 51 Database Management System Participation Constraints: For the "Product" entity set, the participation constraint is total participation because the company wants to ensure that every product is supplied by at least one supplier. Therefore, every product must participate in at least one supplier relationship. For the "Supplier" entity set, the participation constraint is partial participation because suppliers are not obligated to supply any specific product. Some suppliers may not participate in any product relationships. 2.Imagine a university database where students take courses, and each course can have multiple instructors. However, the university imposes certain constraints. A student can enroll in multiple courses, and an instructor can teach multiple courses. However, each student can have at most one primary instructor for a course, and each course must have at least one instructor. 1. Determine the cardinality ratio between the "Student" entity and the "Course" entity in this scenario. 2. Specify the minimum and maximum cardinalities for the relationship between "Student" and "Course." 3. Describe the participation constraints for both "Student" and "Course" entities in the relationship. Answers: 1.Cardinality Ratio: The cardinality ratio between the "Student" entity and the "Course" entity is many-to-many (M:N). This means that each student can enroll in multiple courses, and each course can have multiple students. 2. Minimum and Maximum Cardinalities: For the relationship between "Student" and "Course," the minimum cardinality for both sides is 1. This indicates that each course must have at least one student enrolled, and each student must be enrolled in at least one course. The maximum cardinality for both sides is * (unlimited), allowing students to enroll in multiple courses and courses to have multiple students. 3. Participation Constraints: - Total participation for "Student" in the relationship means that every student must be enrolled in at least one course. This ensures that no student is left without a course. - Total participation for "Course" in the relationship means that every course must have at least one student enrolled. This ensures that no course is left without students. These constraints and cardinalities ensure that students are actively enrolled in 52 Database Management System courses, and courses have instructors as well as students. 53 Database Management System Lecture_15_Primary Keys: Multiple-Choice Questions (MCQs): 1.In a database, what is the primary key? a. A key used to open the database. b. A set of attributes that distinguishes entities. c. The most important attribute of an entity. d. A key used to link tables in a database. Answer: b. A set of attributes that distinguishes entities. The primary key is a set of one or more attributes that uniquely identify each entity within a database table. It is used to distinguish one entity from another. 2.In a Many-to-Many relationship in a database, what is chosen as the primary key? a. The union of the primary keys of all participating entities. b. The first attribute of the first entity. c. The attribute with the highest value. d. The attribute with the lowest value. Answer: a. The union of the primary keys of all participating entities. In a Many-to-Many relationship, the primary key typically consists of the union of the primary keys of all participating entities, ensuring that each relationship instance is uniquely identified. 3.When representing a Many-to-Many relationship, what attributes are included in the primary key? a. Only the attributes from one of the participating entities. b. The attributes from both participating entities. c. Only the attributes from the first participating entity. d. None of the attributes from the participating entities. Answer: b. The attributes from both participating entities. The primary key of a Many-to-Many relationship includes attributes from both participating entities to uniquely identify the relationship instances. 4.In a One-to-One relationship, which entity's primary key can be chosen as the primary key for the relationship set? a. Only the entity with fewer attributes. b. Either of the participating entities. c. Only the entity with more attributes. d. Neither of the participating entities. 54 Database Management System Answer: b. Either of the participating entities. In a One-to-One relationship, you can choose the primary key of either of the participating entities as the primary key for the relationship set because each entity instance is uniquely associated with one instance of the other entity. 5.What is a superkey in the context of a database? a) A set of attributes that uniquely identifies an entity b) A minimal set of attributes that distinguishes entities in a relationship set c) A combination of primary keys from different entity sets d) A set of attributes used to describe relationships between entities Answer: c) A superkey is a set of attributes that can be used to uniquely identify entities in a database. It may consist of attributes from different entity sets. 6.In a one-to-one relationship, how is the primary key for the relationship set typically chosen? a) It is always the primary key of the first participating entity b) It is always the primary key of the second participating entity c) It can be either the primary key of the first or second entity d) It is a combination of attributes from both participating entities Answer: c) In a one-to-one relationship, the primary key for the relationship set can be chosen from either participating entity's primary key. 55 Database Management System Two-Mark Questions: Imagine you are designing a database for a library system. In this system, you have two main entity sets: "LibraryBranch" and "BookCopy." The "BookCopy" entity represents individual copies of books available at different library branches. Each copy of a book is uniquely identified by a barcode. Now, consider the "BookCopy" entity as a weak entity and the "LibraryBranch" entity as the identifying entity. Question: 1. Define what a weak entity is in the context of this scenario and explain why "BookCopy" is considered a weak entity. 2. Identify and describe the constraints associated with the weak entity "BookCopy" and its identifying entity "LibraryBranch." Specifically, mention the following constraints: a) Identifying relationship b) Discriminator attribute c) Total participation d) Primary key of the weak entity Answers : 1. A weak entity is an entity in a database that does not have a primary key attribute of its own and relies on another entity, known as the identifying entity, to provide a unique identity. In this scenario, "BookCopy" is considered a weak entity because it does not have a primary key attribute on its own; it depends on the "LibraryBranch" entity to uniquely identify each book copy. 2. Constraints associated with the weak entity "BookCopy" and its identifying entity "LibraryBranch" are as follows: a) Identifying relationship: The identifying relationship is a many-to-one relationship from "BookCopy" to "LibraryBranch." It signifies that each book copy is associated with one library branch. b) Discriminator attribute: The discriminator attribute in "BookCopy" is typically the "barcode." This attribute, along with the relationship to the identifying entity, helps uniquely identify a book copy. c) Total participation: "BookCopy" has total participation in the identifying relationship with "LibraryBranch," meaning that every book copy must be associated with a library branch. d) Primary key of the weak entity: The primary key of "BookCopy" consists of the "barcode" attribute and a foreign key reference to the primary key of "LibraryBranch," 56 Database Management System typically "branchID." Four-Mark Questions: 1.Draw an Entity-Relationship (ER) diagram based on the following problem statement: You are tasked with designing an ER diagram for a system that manages products, trainers, and their associated parts. The system has the following components: Products: Each product has a unique identifier (part_id) and a name (part name). Trainers: Each trainer is uniquely identified by a trainer ID (trainer_id) and has a name (trainer name). Parts: Parts are associated with products and trainers through specific relationships. Each part has a unique identifier (part_id). Each part belongs to one product, and each product can have multiple parts. Each part is maintained by one trainer, and each trainer can maintain multiple parts. Your task is to create an ER diagram that represents these entities and their relationships. Make sure to clearly indicate the entities, attributes, relationships, and cardinalities. Answer 57 Database Management System In this ER diagram, Products, trainers are the strong entities and the Parts entity is a weak entity. Because Parts entity depends on Products entity on a relationship of Belongs to for its existence. And also parts entity related with trainer entity through a relationship named maintained by part_id and part name are two attributes of parts entity and its key attributes are part_id. Part_id and part name are the attributes of the product entity and its key attribute is part-id. Similarly, trainer_id and trainer name are the attributes of a trainer and its key attribute is trainer_id. 2.You are tasked with designing an Entity-Relationship (ER) diagram for a university's course registration system. The system includes information about students, courses, and course enrollments. Additionally, there's a need to track course prerequisites. Your goal is to create an ER diagram that captures the relationships between these entities. Entities: 1. Students: Each student has a unique student ID, a name, and an email address. 2. Courses: Each course is uniquely identified by a course code and has a title, a department, and a credit value. 3. Enrollments: Students can enroll in courses, and each enrollment is recorded with a unique enrollment ID. It also includes the date of enrollment. 58 Database Management System 4. Prerequisites: Some courses have prerequisites, meaning that students must complete specific courses before enrolling. Prerequisites are linked to courses and specify which courses are required. Now, your task is to create an ER diagram based on this scenario. Identify any weak entities, primary keys, and specify the cardinality and participation constraints for the relationships. Questions: 1. Identify the weak entity or entities in the given scenario. 2. Determine the primary key for each entity in the ER diagram. 3. Define the cardinality and participation constraints for the following relationships: a) The relationship between Students and Enrollments. b) The relationship between Courses and Enrollments. c) The relationship between Courses and Prerequisites. Answers: 1. The weak entity in this scenario is "Prerequisites" because its existence depends on the "Courses" entity. It relies on the "Courses" entity to uniquely identify each prerequisite. 2. Primary keys for each entity: - Students: Student ID - Courses: Course Code - Enrollments: Enrollment ID - Prerequisites: No primary key since it's a weak entity. 3. Cardinality and participation constraints: a) The relationship between Students and Enrollments: - Cardinality: One student can have many enrollments (1 to Many). - Participation: Every student must be associated with at least one enrollment (Total participation on the student side). b) The relationship between Courses and Enrollments: - Cardinality: One course can have many enrollments (1 to Many). - Participation: A course may have zero enrollments, but when it does, each enrollment corresponds to one course (Partial participation on the course side). c) The relationship between Courses and Prerequisites: - Cardinality: One course can have many prerequisites (1 to Many). - Participation: A course may have zero prerequisites, but when it does, each 59 Database Management System prerequisite corresponds to one course (Partial participation on the course side). 60 Database Management System Lecture_16_Removing attributes: Multiple-Choice Questions (MCQs): 1.Which of the following is an example of redundancy in an ER diagram? a. Having multiple entities to represent the same real-world object. b. Using appropriate relationships to connect entities. c. Normalizing attributes to minimize data duplication. d. Using primary and foreign keys to establish relationships. Answer: a. Having multiple entities to represent the same real-world object. 2.Which of the following is an example of a composite attribute with redundancy? A. EmployeeID B. DateOfBirth C. Address D. ProductID Answer: C. Address (with Street, City, and Postal Code) 3.Which attribute type is most likely to introduce redundancy in an ER diagram? A. Composite attributes B. Derived attributes C. Simple attributes D. Multivalued attributes Answer: A. Composite attributes 4.Suppose we have an E-R model with two entity sets, "Employee" and "Department." Both entities have an attribute called "Location" for storing the department's physical location. What action should be taken to eliminate redundancy? A. Remove the "Location" attribute from both entities. B. Keep the "Location" attribute as it is; redundancy is not an issue. C. Remove the "Location" attribute from either "Employee" or "Department." D. Rename the "Location" attribute to a different name in one of the entities. Answer: C 5.Which of the following statements is true about eliminating redundant attributes in the E-R model? A. Redundant attributes should always be deleted from all entities. B. Redundant attributes should be renamed to avoid conflicts. 61 Database Management System C. Redundant attributes should be removed only if they are not required. D. Redundant attributes should be duplicated for data redundancy. Answer: C 6.In the ER model, when might an attribute exist in multiple entity sets? A. When it is an entity identifier. B. When it is a foreign key. C. When it is a derived attribute. D. When it is a composite attribute. Answer: B Two-Mark Questions: 1.Imagine you are designing a database for a university. In this university, you have two main entity sets: "Instructor" and "Course." Each instructor has attributes like "Instructor ID," "Instructor Name," "Department ID," and "Department Name." Each course has attributes like "Course ID," "Course Name," "Instructor ID," and "Instructor Name." Identify the redundancy in the given scenario and propose a revised database design that eliminates the redundancy. Explain your rationale for the changes. Answer: Original Scenario with Redundancy: Classroom: Building, Room Number, Capacity Department: Dept Name, Building, Budget Course: Course ID, Title, Credits, Instructor ID (Foreign Key) Instructor: ID (Primary Key), Name, Salary Revised Database Design: Classroom: Building, Room Number, Capacity Department: Dept Name, Building, Budget Course: Course ID, Title, Credits Instructor: ID, Name, Salary To eliminate redundancy, we have removed the "Instructor Name" attribute from the "Course" entity since it can be derived from the "Instructor" entity by joining on the "Instructor ID." We retain the "Instructor ID" in the "Course" entity as a foreign key to establish a relationship between instructors and the courses they teach. 2.In a university database, we have two entity sets: "Student" and "Instructor." Each entity set contains attributes related to the person's department. However, some of these attributes are redundant and need to be eliminated. 62 Database Management System Student Entity Attributes: Student_ID, Student_Name, Student_Dept_ID, Student_Dept_Name, Student_Major Instructor Entity Attributes: Instructor_ID, Instructor_Name, Instructor_Dept_ID, Instructor_Dept_Name, Instructor_Specialty Identify the redundancy in the attributes of the "Student" and "Instructor" entity sets, and propose a revised schema that eliminates this redundancy while maintaining the necessary information. Answer: To eliminate redundancy, we can create a separate entity set for the department, and both "Student" and "Instructor" entities can reference this shared entity. Here's the revised schema: Entity Sets: 1. Student - Attributes: Student_ID, Student_Name, Student_Major - Relationship: Department (Reference to Department entity) 2. Instructor - Attributes: Instructor_ID, Instructor_Name, Instructor_Specialty - Relationship: Department (Reference to Department entity) 3. Department - Attributes: Dept_ID (Primary Key), Dept_Name With this schema, we have removed the redundant attributes (e.g., Student_Dept_ID, Student_Dept_Name, Instructor_Dept_ID, Instructor_Dept_Name) from both the "Student" and "Instructor" entity sets by creating a separate "Department" entity set and establishing a relationship between students, instructors, and their respective departments. This ensures efficient data representation and avoids duplication of department-related information. 63 Database Management System Four-Mark Questions: 1.In a company database, we have several entity sets, including "Employee," "Department," and "Project." However, there is redundancy in the attributes related to department information in both the "Employee" and "Project" entity sets. Employee Entity Attributes: Employee_ID, Employee_Name, Employee_Salary, Employee_Department, Employee_Location Project Entity Attributes: Project_ID, Project_Name, Project_Department, Project_Location, Project_Budget Question: 1.Define Redundancy? 2.Identify the redundancy in the attributes of the "Employee" and "Project" entity sets, and propose a revised schema that eliminates this redundancy while maintaining the necessary information. Answer: 1.Redundancy refers to the inclusion of duplicate or unnecessary data in a database system. It occurs when the same information is stored multiple times within the database, which can lead to several issues, including increased storage requirements, data inconsistency, and maintenance challenges. 2.To eliminate redundancy, we can create a separate entity set for the department and have both "Employee" and "Project" entities reference this shared entity. Here's the revised schema: Entity Sets: Employee: Employee_ID, Employee_Name, Employee_Salary Relationship: Department (Reference to Department entity) Project: Project_ID, Project_Name, Project_Budget Relationship: Department (Reference to Department entity) Department: Dept_ID (Primary Key), Dept_Name, Dept_Location With this schema, we have removed the redundant attributes related to department information (e.g., Employee_Department, Employee_Location, Project_Department, Project_Location) from both the "Employee" and "Project" entity sets by creating a separate "Department" entity set and establishing a relationship between employees, 64 Database Management System projects, and their respective departments. This ensures efficient data representation and avoids duplication of department-related information. 65 Database Management System Lecture_17_Reducing ER to a relational schema and Lecture_18_Reducing ER to a relational schema: Multiple-Choice Questions (MCQs): 1.How are composite attributes handled when converting an E-R diagram to a relational schema? A) They are represented as separate attributes for each component. B) They are stored as a single attribute. C) They are eliminated from the schema. D) They are stored as stored procedures. Answer: A) They are represented as separate attributes for each component. Explanation: Composite attributes are handled by creating separate attributes for each component attribute. Each component attribute becomes a distinct attribute in the schema. 2.What happens to derived attributes when converting an E-R diagram to a relational schema? A) They are represented as stored procedures. B) They are eliminated from the schema. C) They become primary keys. D) They are stored as separate relations. Answer: B) They are eliminated from the schema. Explanation: Derived attributes are not explicitly represented in the relational data model and are typically eliminated from the schema. 3.How are multivalued attributes represented in a relational schema? A) They are stored as separate relations. B) They are eliminated from the schema. C) They become primary keys. D) They are represented as attributes along with the primary key of the entity set. Answer: A) They are stored as separate relations. Explanation: Multivalued attributes are represented by creating a separate relation schema with an attribute corresponding to the multivalued attribute along with attributes corresponding to the primary key of the entity set. 66 Database Management System 4.For binary 1:N relationships, how are they typically represented in a relational schema? A) The relationship is merged into a single relation. B) A cross-reference relation is created. C) The "N" side of the relationship includes a foreign key to the primary key of the "1" side. D) Both A and B. Answer: C) The "N" side of the relationship includes a foreign key to the primary key of the "1" side. Explanation: In a binary 1:N relationship, the "N" side includes a foreign key that references the primary key of the "1" side. 5.How are binary M:N relationships represented in a relational schema? A) Each entity type is merged into a single relation. B) A cross-reference relation is created. C) A new relationship relation is created with foreign keys to the participating entity types. D) The relationship is eliminated. Answer: C) A new relationship relation is created with foreign keys to the participating entity types. Explanat

DBMS Unit 1 Question Bank.pdf

Document Details

Tags

Related

Full Transcript

Upgrade to continue