Computer Science BIEK XII Notes: DBMS Complete Notes XII BIEK , Computer Science Past Paper Question With Answer

PREPARED BY: SIR ZUBAIR KHAN 

The Textbook of COMPUTER SCIENCE For Class XII – Class 12 – Sindh Textbook Board 

SHORT QUESTIONS

Q1: What are the steps involved in creating a report in MS Access? (2012)

Ans: The steps involved in creating a report in MS Access are as follows:

• Define the report's purpose: Determine the objective of the report and the information it needs to convey.

• Design the report layout: Decide on the structure and appearance of the report, including the placement of fields, headers, footers, and any other desired elements.

• Select the data source: Identify the table or query from which you want to retrieve data for the report.

• Create the report: Use the Report Design view to build the report by adding the necessary fields, labels, and controls. Format the report elements as needed.

• Organize and group data: Arrange the data in a logical manner by grouping related information together, such as by category or date.

• Apply sorting and filtering: Specify any sorting or filtering criteria to control the order and content of the report.

• Add calculations and summaries: Include calculations or summary functions, such as totals or averages, to provide a comprehensive analysis of the data.

• Customize the report appearance: Enhance the visual appeal of the report by applying themes, colors, fonts, and other formatting options.

• Preview and modify the report: Review the report in Print Preview to ensure it meets your requirements. Make any necessary adjustments to the layout or formatting.

• Save and distribute the report: Save the report for future use and distribute it to others as needed, either by printing it or sharing it electronically.

Q2: What do DBMS and RDBMS stand for? State the advantages of DBMS? (2012) OR List some advantages of the database management system? (2015)

Ans: DBMS stands for Database Management System, while RDBMS stands for Relational Database Management System.

Advantages of DBMS include:

• Data Centralization: DBMS allows for the central storage and management of data, eliminating the need for multiple copies of the same data in different locations. This ensures data consistency and reduces data redundancy.

• Data Integrity: DBMS provides mechanisms to enforce data integrity rules, such as primary key constraints, foreign key relationships, and data validation rules. These constraints ensure that the data remains accurate and consistent.

• Data Security: DBMS offers security features to protect the data from unauthorized access or modifications. User authentication, access control, and encryption techniques can be implemented to safeguard sensitive data.

• Data Consistency: DBMS enforces consistency in the data by implementing ACID (Atomicity, Consistency, Isolation, Durability) properties. Transactions ensure that data modifications are either fully completed or fully rolled back, maintaining data integrity.

• Data Sharing and Collaboration: DBMS enables multiple users to access and share data concurrently. It provides concurrent access control mechanisms to handle simultaneous transactions and maintains data consistency across multiple users.

• Data Backup and Recovery: DBMS offers backup and recovery mechanisms to protect against data loss. Regular backups and transaction logs allow for restoring data in the event of hardware failures, software errors, or other disasters.

• Data Independence: DBMS provides a layer of abstraction between the physical storage and the logical representation of data. This allows for modifications in the physical storage structure without affecting the applications that use the data.

• Data Scalability and Performance: DBMS can handle large amounts of data and scale to accommodate increasing demands. It optimizes query execution, indexing, and caching techniques to improve performance and ensure efficient data retrieval.

• Data Query and Reporting: DBMS provides query languages, such as SQL (Structured Query Language), to retrieve and manipulate data. It also supports report generation and data analysis tools, facilitating decision-making processes.

• Application Development Support: DBMS offers features and tools to support application development, including data modeling, data definition, and data manipulation capabilities. It simplifies the development process and allows for efficient application integration with the database.

Q3: Describe various data types supported by MS-Access? (2013)

Ans: MS Access supports various data types that can be used to define the structure and properties of fields in database tables. Here are the commonly used data types in MS Access:

• Text: Used to store alphanumeric characters, such as names, addresses, or descriptions. It has a maximum storage capacity of 255 characters.

• Memo: Similar to the Text data type but with a much larger storage capacity, allowing for longer text entries. It can store up to 65,536 characters.

• Number: Used to store numeric values, including integers, decimals, and floating-point numbers. It supports different subtypes, such as Byte, Integer, Long Integer, Single, Double, and Decimal, depending on the range and precision required.

• Date/Time: Used to store date and time values. It includes both date and time components and allows for various formatting options.

• Currency: Specifically designed for storing monetary values. It ensures precision and handles currency symbols and decimal separators according to the system settings.

• Yes/No: Represents boolean or logical values, allowing for only two states: Yes/True or No/False.

• AutoNumber: Automatically generates a unique numeric value for each new record. It is often used as a primary key to ensure uniqueness and facilitate record identification.

• OLE Object: Enables the storage of embedded objects, such as images, audio files, or documents, within a field.

• Hyperlink: Stores hyperlinks to websites, files, or other external resources.

• Attachment: Introduced in later versions of MS Access, it allows for storing multiple attachments within a single field.

• Lookup: Enables the creation of a drop-down list of predefined values to choose from. It can be based on a predefined set of values or values from another table.

• Calculated: A field that derives its value based on calculations or expressions involving other fields or constants. The result is dynamically calculated at runtime.

• GUID: Stands for Globally Unique Identifier. It stores a unique identifier value that is generated using an algorithm and is typically used as a primary key for replication purposes.

Q4: What is the relationship? State the kinds of relationships used in MS-Access? (2014) OR In what Condition one too many relationships is used? (2017) OR How can we define one-to-one relationships in the database? (2018)

Ans: A relationship refers to the association or connection between two or more tables based on common fields or keys. Relationships establish the logical links between tables, allowing data to be efficiently and accurately stored, organized, and retrieved. MS Access supports different types of relationships to define how tables are related to each other. The types of relationships used in MS Access are:

• One-to-One (1:1): In a one-to-one relationship, each record in one table is associated with only one record in another table, and vice versa. This relationship is not commonly used in MS Access, but it can be useful in certain scenarios where data needs to be split across multiple tables for performance or security reasons.

• One-to-Many (1:N): The most common type of relationship, a one-to-many relationship exists when a record in one table can be associated with multiple records in another table, but each record in the second table is associated with only one record in the first table. For example, a customer can have multiple orders, but each order belongs to only one customer.

• Many-to-One (N:1): A many-to-one relationship is the reverse of a one-to-many relationship. It occurs when multiple records in one table are associated with a single record in another table. For instance, many orders can be associated with a single customer.

• Many-to-Many (N: N): A many-to-many relationship exists when multiple records in one table are associated with multiple records in another table. This type of relationship is implemented using a junction or bridge table that connects the two tables. For example, in a music library database, multiple songs can be associated with multiple genres, so a junction table is used to link the songs and genres.

Q5: What is the key field? Why is it important in DBMS? (2015)

Ans:  key field, also known as a key or a primary key, is a field or a combination of fields in a database table that uniquely identifies each record within that table. It provides a way to uniquely identify and differentiate one record from another.

The primary importance of a key field in DBMS (Database Management System) is as follows:

• Uniqueness: A key field ensures that each record in a table has a unique identifier. This uniqueness is crucial for accurately identifying and distinguishing records, as well as maintaining data integrity. It prevents duplicate or redundant data from being stored in the table.

• Data Integrity: The key field plays a vital role in enforcing data integrity rules. It ensures that each record in the table has a unique identifier, thereby preventing data inconsistencies or anomalies. It guarantees the integrity of relationships between tables, as foreign keys in related tables typically reference primary keys in other tables.

• Data Retrieval and Manipulation: The key field serves as a reference point for data retrieval and manipulation operations in the database. It allows for efficient searching, sorting, updating, and deleting of records based on their unique identifiers. By indexing the key field, the DBMS can optimize the performance of queries and improve overall system efficiency.

• Relationship Establishment: Primary keys and foreign keys are used to establish relationships between tables in a relational database. By using the primary key of one table as a foreign key in another table, related records can be linked together. This enables data consistency, referential integrity, and efficient retrieval of related data across multiple tables.

• Data Aggregation: In some cases, the key field may also serve as an aggregating factor for data. For example, in a sales database, the primary key of the sales order table can be used to group and aggregate sales data, allowing for analysis and reporting at a higher level.

Q6: Write the full form of the following acronyms?

DML, DDL, SQL, IDE, RDBMS, DBA (2015)(2017)(2018)(2019)

Ans: 

• DML: Data Manipulation Language
• DDL: Data Definition Language
• SQL: Structured Query Language
• IDE: Integrated Development Environment
• RDBMS: Relational Database Management System
• DBA: Database Administrator
• DCL: Data Control Language

Q7: What are the responsibilities of DBA?(2016)

Ans: A Database Administrator (DBA) is responsible for managing and maintaining an organization's database systems. Their responsibilities may vary depending on the specific organization and the complexity of the database environment, but generally include the following:

• Database Design: DBAs participate in the design and development of the database structure, including tables, relationships, and data integrity constraints. They work closely with application developers and system architects to ensure efficient and optimized database designs.

• Database Installation and Configuration: DBAs are responsible for installing and configuring database software on servers or cloud platforms. This involves setting up database instances, configuring storage parameters, and establishing security measures.

• Performance Monitoring and Tuning: DBAs monitor the performance of database systems and identify and resolve performance bottlenecks. They analyze query execution plans, optimize SQL queries, and fine-tune database configurations to enhance performance and ensure optimal response times.

• Data Backup and Recovery: DBAs develop and implement backup and recovery strategies to protect data from loss or corruption. They regularly schedule backups, verify their integrity, and perform database restores when needed. They may also establish disaster recovery plans to minimize downtime in case of system failures.

• Security Management: DBAs enforce data security and access control policies. They set up user accounts and permissions, manage database roles and privileges, and implement encryption and authentication mechanisms to safeguard sensitive data. DBAs also monitor database activity to detect and respond to any security breaches or unauthorized access attempts.

• Database Maintenance and Upgrades: DBAs perform routine maintenance tasks such as database reorganization, index rebuilding, and data purging to ensure optimal performance and efficient data storage. They also plan and execute database upgrades and patch installations to keep the database software up-to-date and secure.

• Troubleshooting and Issue Resolution: DBAs diagnose and resolve database-related issues, including errors, performance problems, and data inconsistencies. They investigate and troubleshoot database failures, analyze logs and diagnostic data, and collaborate with other teams to resolve issues promptly.

• Capacity Planning: DBAs forecast future database growth based on business needs and historical data trends. They estimate storage requirements, CPU usage, and memory usage to ensure adequate resources are provisioned to support the database workload.

Q8: What is Foreign Key? Where do we use it? (2017)

Ans: A foreign key is a column or a set of columns in a relational database table that references the primary key or a unique key of another table. It establishes a relationship between two tables, known as the parent table and the child table.

The foreign key constraint ensures referential integrity, meaning that the values in the foreign key column(s) must exist in the referenced table's primary key or unique key column(s). This constraint helps maintain data consistency and enforce relationships between related tables.

Here are some key points about foreign keys:

• Purpose: Foreign keys are used to establish relationships between tables in a relational database. They define the dependencies and associations between data in different tables.

• Relationship Types: Foreign keys typically represent one of the following relationship types:

1. One-to-One: Each record in the child table references exactly one record in the parent table.
2. One-to-Many: Each record in the child table can reference one record in the parent table, but a record in the parent table can have multiple related records in the child table.
3. Many-to-Many: Multiple records in the child table can reference multiple records in the parent table, usually achieved through an intermediary table.

• Usage Scenarios: Foreign keys are used in various scenarios, including:

• Maintaining data integrity: Foreign keys ensure that relational database systems enforce referential integrity, preventing the creation of orphaned records or invalid data relationships.
• Establishing associations: Foreign keys help define relationships between related data entities, enabling the use of JOIN operations to retrieve data from multiple tables.
• Cascading actions: Foreign key constraints can be configured to perform cascading actions, such as automatically deleting child records when a parent record is deleted or updating child records when a parent record is updated.
• Query optimization: Foreign keys provide metadata that helps the database optimizer generate efficient execution plans for queries involving multiple tables.

Q9: What is Data Dictionary in DBMS? (2017)

Ans: In a database management system (DBMS), a data dictionary, also known as a data repository or metadata repository, is a centralized repository that stores metadata and information about the database objects within the system. It serves as a reference source for both users and administrators of the database.

The data dictionary contains detailed information about the structure, organization, and characteristics of the data stored in the database. It typically includes the following types of metadata:

• Data Definitions: The data dictionary stores the definitions of all the tables, views, indexes, and other database objects. It provides information about the names, data types, sizes, and constraints of each attribute or column in the database.

• Relationships and Constraints: The data dictionary maintains information about the relationships between tables, such as foreign key constraints and referential integrity rules. It helps in understanding and enforcing the integrity of the database relationships.

• Access Controls: The data dictionary may store information about the access controls and permissions set on various database objects. It provides details about who can access or modify specific data and what level of privileges they have.

• Data Usage and Statistics: It can contain statistics and usage information, such as the number of records in each table, the frequency of data modifications, and query performance metrics. This information is useful for query optimization and performance-tuning purposes.

• System Metadata: The data dictionary may also store system-level metadata, including information about the database version, the creation date of the database, and other administrative details.

Benefits of a Data Dictionary:

• Centralized Information: The data dictionary provides a centralized location to access and manage metadata, ensuring consistency and accuracy in the database definitions.

• Data Integrity and Consistency: By storing information about relationships and constraints, the data dictionary helps maintain data integrity and consistency across the database.

• Database Maintenance and Administration: The data dictionary assists database administrators in managing the database schema, analyzing database performance, and making informed decisions about database modifications.

• Application Development: Developers can utilize the data dictionary to understand the database structure and design appropriate queries and data manipulation operations.

• Documentation and Communication: The data dictionary serves as a documentation tool, allowing users, developers, and administrators to understand and communicate the structure and semantics of the database.

Q10: What is Composite Key? Where do we use it? (2018)

Ans: A composite key, also known as a composite primary key, is a combination of two or more columns that uniquely identify a row in a database table. Unlike a single-column primary key, which uses a single attribute to identify a row, a composite key uses multiple attributes to define uniqueness.

Composite keys are used in situations where a single attribute alone cannot guarantee uniqueness. By combining multiple attributes, the composite key ensures that each combination of values in the key is unique within the table.

Q11: What is a relational database model? (2018)

Ans: A relational database model, often referred to as RDBMS (Relational Database Management System), is a system for organizing and managing data using a set of tables that are interrelated through common data elements. It is a widely used database model that provides a structured and efficient approach to storing and retrieving data.

The core concepts of the relational database model include:

• Tables: Data is organized into tables, also known as relations, which consist of rows (tuples) and columns (attributes). Each table represents a specific entity or relationship in the real world. For example, in a library database, there might be tables for books, borrowers, and transactions.

• Rows and Columns: Each row in a table represents a unique record or instance of an entity, while each column represents a specific attribute or property of that entity. For example, a "books" table might have columns for book ID, title, author, and publication year.

• Primary Keys: A primary key is a unique identifier for each row in a table. It ensures that there are no duplicate records and provides a way to access and manipulate data. Typically, a primary key is defined as one or more columns that have unique values in the table.

• Relationships: Relationships are established between tables through the use of keys, specifically primary keys and foreign keys. A foreign key in one table refers to the primary key in another table, creating a link between related data. Relationships can be one-to-one, one-to-many, or many-to-many.

• Query Language: Relational databases use a structured query language (SQL) to interact with the data. SQL provides a set of commands and syntax for querying, updating, and manipulating the database. It allows users to retrieve specific data based on conditions, perform calculations, join tables, and more.

• Relational databases provide several advantages, including data integrity, flexibility, scalability, and ease of use. They allow for efficient data storage, retrieval, and manipulation. Additionally, the relational database model supports various data operations like filtering, sorting, aggregation, and complex queries.

Popular RDBMS systems include Oracle, MySQL, Microsoft SQL Server, PostgreSQL, and SQLite. These systems implement the relational database model and provide tools and utilities for managing databases and performing operations on the data stored within them.

Q12: Write the name of the object in MS Access? (2018)

Ans: In Microsoft Access, the primary objects used to organize and manage data are:

• Tables: Tables are used to store and organize data in a tabular format, consisting of rows and columns. Each table represents a specific entity or concept in the database, such as Customers, Products, Orders, etc.

• Queries: Queries are used to retrieve, filter, and manipulate data stored in tables. They allow you to specify criteria and conditions to extract specific information or perform calculations on the data.

• Forms: Forms provide a user-friendly interface to view, enter, and edit data in the tables. They allow you to create custom input screens and facilitate data entry and validation.

• Reports: Reports are used to generate formatted and printable output based on the data stored in tables. They provide options for grouping, summarizing, and displaying data in a visually appealing manner.

• Macros: Macros are used to automate repetitive tasks or perform a sequence of actions within the database. They can be triggered by events, buttons, or other user interactions.

• Modules: Modules allow you to write and execute custom VBA (Visual Basic for Applications) code to extend the functionality of the database. Modules provide advanced capabilities for data manipulation, business logic implementation, and integration with external systems.

Q13: Write any three advantages of DBMS? (2019)

Ans: Advantages Of DBMS

• Data Centralization: DBMS centralizes data storage, reducing scattered files and promoting organization.

• Data Integrity: DBMS enforces integrity constraints, ensuring data consistency and accuracy.

• Concurrent Access and Security: DBMS supports simultaneous access, manages conflicts, and provides robust security measures for data protection.

• Data Accessibility: DBMS provides quick and efficient access to data through queries, forms, and reports. It ensures data availability and offers backup and recovery mechanisms.

• Scalability and Performance: DBMS can handle large data volumes and accommodate growing needs. It optimizes query execution and offers scalability, enabling faster data retrieval and analysis.

Q14: What is the role of DBA in DBMS? (2019)

Ans: The role of a Database Administrator (DBA) in a DBMS (Database Management System) is crucial for the effective management and maintenance of databases. The DBA is responsible for various tasks related to the design, implementation, security, performance, and integrity of the database system. Here are some key responsibilities of a DBA:

• Database Design: DBAs participate in designing the database structure and data models.
• Installation and Configuration: DBAs install and configure the DBMS software and database environment.
• Security and Access Control: DBAs implement security measures and define user permissions.
• Performance Monitoring and Tuning: DBAs optimize database performance and resolve bottlenecks.
• Backup and Recovery: DBAs develop backup strategies and restore data in case of loss or corruption.
• Maintenance and Upgrades: DBAs perform routine maintenance tasks and manage software upgrades.
• Troubleshooting and Problem Resolution: DBAs diagnose and resolve database-related issues.

Q16: Write the steps to create a table in MS Access? (2019)

Ans: Following are the steps to create a table in MS Access in a concise format:

• Open MS Access and create a new database.
• Enter the table design view.
• Define the table structure by adding columns and setting their properties.
• Optionally, set a primary key for the table.
• Save the table in the database.
• Enter data into the table in the datasheet view.
• Optionally, customize table properties like validation rules, default values, relationships, and indexes.

Q17: Define any one of the following. (2022)

(a) DBMS                    (b) Data Dictionary

Q18: Define the following. (2022)

(a) TABLE                    (b) Query

Q19: What is data redundancy? (2022) 

Q20: What is the difference between primary and secondary Keys? (2022)

Primary Key	Secondary Key
A primary key is a unique identifier for each row in a table. It ensures that there are no duplicate values in the column or combination of columns designated as the primary key.	A secondary key is an optional key used to provide additional indexing and organization of data, but it does not guarantee uniqueness.
Primary keys are used to enforce entity integrity and ensure data uniqueness within a table.	Secondary keys are used to improve query performance by creating indexes and allowing for faster data retrieval based on specific attributes.
Each table must have a primary key defined, and it can consist of one or multiple columns.	Secondary keys are not mandatory and can be defined based on the requirements of the database and its queries. They can also be single-column or multi-column keys.
Primary keys are typically used as references (via foreign keys) in other tables to establish relationships between tables.	Secondary keys are not typically used as references in relationships between tables. They serve as additional means of accessing and organizing data.
Primary keys provide the basis for data integrity constraints, such as uniqueness and referential integrity.	Secondary keys do not enforce uniqueness or referential integrity constraints. They primarily support data access and retrieval.
Changing the value of a primary key is often a complex operation that requires careful consideration and potential updates to dependent records and relationships.	Changing the value of a secondary key does not have such significant implications and can be performed more easily.

Q21: What is the difference between DBMS and RDBMS? (2018)

DBMS (Database Management System)	RDBMS (Relational Database Management System)
DBMS is a software system that manages databases. It provides tools and utilities to create, store, retrieve, and manipulate data.	RDBMS is a type of DBMS that specifically manages relational databases. It follows the relational database model and uses tables, rows, and columns to organize and structure data.
DBMS can manage various types of databases, including relational, hierarchical, network, and object-oriented databases.	RDBMS is designed to manage relational databases and enforces relationships and integrity constraints through the use of keys, such as primary keys and foreign keys.
DBMS does not necessarily enforce the relational model or support SQL (Structured Query Language).	RDBMS adheres to the relational model and provides support for SQL, a standard language for querying and manipulating relational databases.
DBMS may or may not support transactions and ACID (Atomicity, Consistency, Isolation, Durability) properties.	RDBMS typically supports transactions and enforces ACID properties to ensure data consistency and integrity.
DBMS may not have built-in mechanisms for data integrity, referential integrity, or data normalization.	RDBMS provides mechanisms for enforcing data integrity, and referential integrity (through foreign key relationships), and supports normalization techniques to eliminate redundancy and improve data organization.
Examples of DBMS include MongoDB, Cassandra, and Redis.	Examples of RDBMS include Oracle, MySQL, Microsoft SQL Server, PostgreSQL, and SQLite.