On this page, you will find a collection of important questions and answers for Unit , specifically designed for Gauhati University FYUGP BCom 1st Semester Information Technology in Business. These questions and answers are carefully prepared to help you score well in your exams. Use this guide as a valuable reference for your exam preparation.
![Unit-3 Introduction to Computer the Networks [GU BCom 1st Sem Information Technology in Business]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrRlZtD09fnJWaG00Ljhch4RZWY85K5xG3IwH-jjZseJpo4d3Cs0KcWynYPtH3UGJbL6c0oka5dy8QOFsyIeqaJ6eK1aQ5FCfAQ3KLQBg2C6h-1yN2eRfwochSbt1RT-jmPjBitkYCKsAmdQM0rA9dCGN1WypMxlQ4GyuA82-C80pSyxBBQZ9s5TwgHD1q/s16000-rw/Information%20Technology%20in%20Business.webp "Unit-3 Introduction to Computer the Networks [GU BCom 1st Sem Information Technology in Business]")
Gauhati University FYUGP BCom 1st Semester Information Technology in Business
UNIT-5
DATABASE FUNDAMENTALS
MCQs on Database Fundamentals
1. ________offers the ability to query the data m insert, alter, and delete tuples.
a. Transaction Control Language (TCL)
b. Data Control Language (DCL)
c. Data Definition Language (DDL)
d. Data Manipulation Language (DML)
Answer: (d) Data Manipulation Language (DML)
2. We can add or remove the user IDs using which of the fixed roles?
a. db_setupadmin
b. db_securityadmin
c. db_accessadmin
d. db_sysadmin
Answer: (c) db_accessadmin
3. Section (sect name, wing, expenses) and Student (student_ID, name, sect name, fees).
a. Tuple of common
b. Attributes of common
c. Attributes of distinct
d. Tuple of distinct
Answer: (d) Tuple of distinct
4. SELECT title
FROM teacher
WHERE fees <= 200000 AND fees >= 80000;
We can replace this query by which of these?
a.
SELECT title
FROM teacher
WHERE fees BETWEEN 200000 AND 80000;
b.
SELECT title
FROM staff
WHERE fees BETWEEN 80000 AND 200000;
c.
SELECT title
FROM staff
WHERE fees <= 80000 AND fees >=200000;
d.
SELECT title
FROM teacher
WHERE fees BETWEEN 80000 AND 200000;
Answer: (d)
SELECT title
FROM teacher
WHERE fees BETWEEN 80000 AND 200000;
5. Which symbol do we use in place of the except?
a.~
b.¬
c. V
d.^
Answer: (b) ¬
6. What is an alternative name for a weak entity?
a. Dominant
b. Owner
c. Child
d. All of the above
Answer: (c) Child
7. Assume a relation X (M, N, O, P, Q) that has the following functional dependencies:
MNO -> PQ and
P>MN
The total number of superkeys of X would be:
a. 12
b. 10
c. 7
d. 2
Answer: (b) 10
8. Fifth Normal Form is concerned with:
a. Join dependency
b. Domain-key
c. Multivalued dependency
d. Functional dependency
Answer: (a) Join dependency
9. The system does not require a password to travel across the internet.
a. Response
b. Challenge-response
c. Manipulation
d. Readable
Answer: (b) Challenge-response
10. We can force a log switch by using:
a. ALTER SYS LOGFILES
b. ALTER SYSTEM SWITCH LOGS
c. ALTER SYSTEM LOG
d. ALTER SYSTEM SWITCH LOGFILE
Answer: (d) ALTER SYSTEM SWITCH LOGFILE
VERY SHORT QUESTIONS WITH ANSWERS:
1. When recovering from a failure:
Answer: Examination of each pair of physical blocks occurs.
2. The Optical disk technology utilizes:
Answer: RAID
3. The node pointing towards another node in a B+ tree is known as:
Answer: Internal node
4. Identify the statement among the following that is FALSE:
Answer: The prime attribute can depend transitively on any key in the case of a relation that is in its BCNF
5. A transaction enters into its final state when it finishes: Answer: Partially committed state
6. An operator shall provide the transaction file's name, and the file consisting of the edited transactions (that are ready for execution) must be called:
Answer: Opt. Exe
7. What is a Data Model?
Answer: A collection of conceptual tools for describing data, data relationships data semantics and constraints.
8. What is an Entity?
Answer. It is a 'thing' in the real world with an independent existence.
SHORT QUESTION
02. Explain the terms database and DBMS. Also, mention the different types
Ans: A software application that interacts with databases, applications, and users to capture and analyze the required data. The data stored in the database can be retrieved, deleted based on the client's requirement.
The different types of DBMS are as follows:
Relational DBMS (RDBMS): This type of DBMS, uses a structure which allows the users to access data in relation to another piece of data in a database. In this type of DBMS, data is stored in the form of tables.
Hierarchical DBMS: As the name suggests, this type of DBMS has a structure similar to that of a tree, wherein the nodes represent records and the branches of the tree represent fields.
Network DBMS: This type of DBMS supports many-to-many relations wherein multiple member records can be linked. Object-oriented DBMS: Uses small individual software called object to store pieces of data and the instructions for the actions to be done with the data.
Q3. What are the advantages of DBMS?
Ans: The advantages of DBMS are as follows:
Sharing of Data: Multiple users can use data from the same database simultaneously.
Integrity constraints: These constraints allow the data to be stored in a database in a refined manner.
Redundancy control: Supports a mechanism to control the redundancy of data by integrating all the data into a single database.
Data Independence: Allows to change the structure of the data without affecting the structure of any of the running application programs.
Provide backup and recovery facility: Provides a feature of 'backup and recovery to automatically create the data backup and restore the data as and when required.
4. What are the different languages present in DBMS?
Ans:
The different languages present in DBMS are as follows:
1. DDL (Data Definition Language): Consists of commands that are used to define the database.
2. DML (Data Manipulation Language): Consists of commands that are used to manipulate the data present in the database.
3. DCL (Data Control Language): Consists of commands that deal with user permissions and controls.
4. TCL (Transaction Control Language): Consists of commands that deal with the transaction management of the database.
5. What do you understand by query optimization?
Ans: Query optimization is the phase that identifies a plan for executing a query that has the least estimated cost. This phase comes into the picture when there are a lot of algorithms and methods to evaluate a query.
The advantages of query optimization are as follows:
- The output is provided faster.
- A larger number of queries can be executed in less time.
- Reduces time and space complexity.
6. Do we consider NULL values the same as that of blank space or zero?
Ans: A NULL value is not at all the same as zero or a blank space. The NULL value represents a value that is unavailable, unknown, not assigned, or not applicable. On the other hand, zero is a number and a blank space is a character.
8. What are the different levels of abstraction in the DBMS?
Ans: There are three levels of data abstraction in DBMS:
1. Physical Level: It is the lowest level of abstraction and describes how the data is stored.
2. Logical Level: This is the next level of abstraction after the Physical level. This layer determines what data is stored in the database, and what is the relationship between the data points.
3. View Level: This is the highest level of abstraction which describes only part of the entire database. It provides multiple views of the same database for different users.
10. What do you understand by an entity and an entity set in DBMS?
Ans:
- Entity: An entity is an object that exists and is distinguishable from other objects. For example, each employee in a company is an entity.
- Entity Type: An entity type defines a collection of similar entities. For example, the set of all employees can be an entity type where each employee has attributes such as empid, name, and department.
- Entity Set: An entity set is a collection of all entities of a particular entity type in the database. For example, all employees in the company form the entity set for the Employee entity type.
11. What is a checkpoint in DBMS and when does it occur?
Ans: A checkpoint is a mechanism where all the previous logs are removed from the system and are permanently stored on the storage disk. So, basically, checkpoints are those points from where the transaction log record can be used to recover all the committed data up to the point of crash.
Q12. What do you understand by Proactive, Retroactive and Simultaneous Update?
Ans:
Proactive Update: These updates are applied to the database before it becomes effective in the real-world environment.
Retroactive Update: These retroactive updates are applied to a database after it becomes effective in the real-world environment.
Simultaneous Update: These updates are applied to the database at the same instance of time as it becomes effective in a real-world environment
13. What do you understand by Data Independence?
Ans: Data Independence implies that the application is independent of the storage structure and data access strategies of data.
14. What are the different integrity rules present in DBMS?
Ans: The different integrity rules present in DBMS are as follows:
1. Entity Integrity: This rule states that the value of the primary key can never be NULL. So, all the tuples in the column identified as the primary key should have a value.
2. Referential Integrity: This rule states that either the value of the foreign key is NULL or it should be the primary key of any other relation.
15. What does Fill Factor concept mean with respect to indexes?
Ans: Fill Factor is used to mention the percentage of space left on every leaf-level page, which is packed with data. Usually, the default value is 100.
16. What is Index hunting and how does it help in improving query performance?
Ans: The process of boosting a collection of indexes is known as Index hunting. This is done because indexes improve the query performance and the speed at which they are processed.
It helps in improving query performance in the following ways:
- The best queries are suggested using the query optimizer.
- Index, query distribution, and their performance are used as metrics to check the effect.
- Databases are tuned into a small collection of problem queries.
Q17. Explain what is a deadlock and mention how it can be resolved?
Ans: A deadlock occurs when two or more processes are stuck waiting for each other to release a resource. This creates a standstill, and the system becomes unresponsive until one of the processes is killed.
Example
Process A needs resource R2 held by Process B, while Process B needs resource R1 held by Process A.
Real-world analogy
A traffic jam in a database where transactions are stopped because they are waiting for each other to move.
Q18. What is SQL and where is it used?
Ans: SQL aka Structured Query Language is the core of the relational database which is used for accessing and managing the databases. This language is used to manipulate and retrieve data from astructured data format in the form of tables and holds relationships Between those tables. So, in layman terms, you can use SQL to communicate with the database.
Q19. What do you understand by sub-queries in SQL?
Ans: A subquery is a query inside another query where a query is defined to retrieve data or information back from the database. In a subquery, the outer query is called as the main query whereas the inner query is called subquery. Subqueries are always executed first and the result of the subquery is passed on to the main query. It can be nested inside a SELECT, UPDATE or any other query. A subquery can also use any comparison operators such as >,< or =.
20. What do you understand by CLAUSE in SQL?
Ans: CLAUSE in SQL is used to limit the result set by mentioning a condition to the query. So, you can use a CLAUSE to filter rows from the entire set of records.
Example: WHERE HAVING clause.
21.Mention the differences between HAVING and WHERE clause?
Ans:
Note: Whenever GROUP BY is not used, HAVING behaves like a WHERE clause.
21. How can you perform pattern matching in SQL ?
Ans: In SQL, pattern matching can be performed using the `LIKE` operator along with wildcard characters. The `LIKE` operator is used in a `WHERE` clause to search for a specified pattern in a column. Here are the two main wildcard characters used:
1. `%` - Represents zero or more characters.
2. `_` - Represents a single character.
Examples
1. **Using `%` wildcard:**
```sql
SELECT * FROM employees
WHERE name LIKE 'J%';
```
This query will return all employees whose names start with 'J'.
2. **Using `_` wildcard:**
```sql
SELECT * FROM employees
WHERE name LIKE 'J_n';
```
This query will return all employees whose names are three characters long, start with 'J', and end with 'n'.
3. **Combining `%` and `_` wildcards:**
```sql
SELECT * FROM employees
WHERE name LIKE 'J_n%';`
This query will return all employees whose names start with 'J', have any single character in the second position, and then any sequence of characters following.
Case Sensitivity
The case sensitivity of the `LIKE` operator can depend on the database system:
- MySQL: By default, `LIKE` is case-insensitive.
- PostgreSQL: `LIKE` is case-sensitive by default, but you can use `ILIKE` for case-insensitive pattern matching.
- SQL Server: `LIKE` is case-insensitive by default, but this can be changed based on the collation settings of the database.
Example with Case-Insensitive Matching in PostgreSQL:
```sql
SELECT * FROM employees
WHERE name ILIKE 'j%';
This query will return all employees whose names start with 'J' or 'j'.
Using `LIKE` and its variations allows for flexible pattern matching in SQL queries, making it easier to search and filter data based on partial matches.
Q22. Mention few case manipulation functions in SQL
Ans: There are three case manipulation functions in SQL namely:
LOWER: This function returns the string in lowercase. It takes a string as an argument and returns it by converting it into lower case. Syntax: LOWER('string')
UPPER: This function returns the string in uppercase. It takes a string as an argument and returns it by converting it into uppercase.
Syntax: UPPER('string')
INITCAP: This function returns the string with the first letter in uppercase and the rest of the letters in lowercase.
Syntax: INITCAP('string')
Q23. What are joins in SQL and what are the different types of joins?
Ans: A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there.
There are 4 joins in SQL namely:
Inner Join
Right Join
Left Join
Full Join
LONG QUESTION
(01) What is DBMS used for?
Answer: DBMS, commonly known as Database Management System, is an application system whose main purpose revolves gement the data. This is a system that allows its user to store the data, delind retrieve it and update the information about the data inside the database.
(2) What is meant by a Database?
Answer: In simple terms, Database is a collection of data in some organized way to facilitate its user's to easily access, manage and upload the data.
(3) Why is the use of DBMS recommended? Explain by listing some of its major advantages.
Answer: Some of the major advantages of DBMS are as follows:
Controlled Redundancy: DBMS supports a mechanism to control the redundancy of data inside the database by integrating all the data into a single database and as data is stored at only one place, the duplicity of data does not happen.
Data Sharing: Sharing of data among multiple users simultaneously can also be done in DBMS as the same database will be shared among all the users and by different application programs.
Backup and Recovery Facility: DBMS minimizes the pain of creating the backup of data again and again by providing a feature of 'backup and recovery' which automatically creates the data backup and restores the data whenever required.
Enforcement of Integrity Constraints: Integrity Constraints are very important to be enforced on the data so that the refined data after putting some constraints are stored in the database and this is followed by DBMS.
Independence of data: It simply means that you can change the structure of the data without affecting the structure of any of the application programs.
4) What is the purpose of normalization in DBMS?
Answer: Normalization is the process of analyzing relational schemas based on their functional dependencies and primary keys to achieve specific properties. These properties include minimizing data redundancy and avoiding Insert, Delete, and Update anomalies.
5) What are the different types of languages available in DBMS?
Answer: There are basically three types of languages in DBMS:
- DDL: Data Definition Language, used to define database and schema structures using SQL queries like CREATE, ALTER, TRUNCATE, DROP, and RENAME.
- DCL: Data Control Language, used to manage user access to the database through SQL queries like GRANT and REVOKE.
- DML: Data Manipulation Language, used to manipulate data within the database through SQL queries like SELECT, INSERT, DELETE, and UPDATE.
Q6) What is the purpose of SQL?
Answer: SQL (Structured Query Language) is primarily used to interact with relational databases for tasks such as inserting, updating, and querying data.
7) Explain the concepts of Primary Key and Foreign Key
Answer: A Primary Key uniquely identifies records in a database table, while a Foreign Key links two or more tables together by referencing a primary key from another table. For example, in tables like Employee and Department, 'ID' could be the primary key in Employee and a foreign key in Department, linking the two tables.
8) What are the main differences between Primary key and Key?
Answer: Given below are few differences:
The main difference between the Primary key and Unique key is that the Primary key can never have a null value while the Unique key may consist of null value. In each table, there can be only one primary key while there can be more than one unique key in a table.
9) What is the concept of sub-query in terms of SQL?
Answer: Sub-query is basically the query which is included inside some other query and can also be called as an inner query which is found inside the outer query.
10). What is the use of DROP command and what are the differences between DROP, TRUNCATE and DELETE commands?
Answer: DROP command is a DDL command which is used to drop/delete the existing table, database, index or view from the database.
The major difference between DROP, TRUNCATE and DELETE commands are:
DROP and TRUNCATE commands are the DDL commands which are used to delete tables from the database and once the table gets deleted, all the privileges and indexes that are related to the table also get deleted. These 2 operations cannot be rolled back and so should be used only when necessary.
DELETE command, on the other hand, is a DML Command which is also used to delete rows from the table and this can be rolled back.
Note: It is recommended to use the 'WHERE' clause along with the DELETE command else the complete table will get deleted from the database
11) What is the main difference between UNION and UNION ALL?
Answer: UNION and UNION ALL are used to combine data from two or more tables. UNION removes duplicate rows and returns only distinct rows after combining the data, while UNION ALL does not remove duplicates and returns all rows from the tables.
12) Explain the concept of ACID properties in DBMS?
Answer: ACID properties refer to Atomicity, Consistency, Isolation, and Durability. These properties ensure data reliability and integrity in database transactions.
- Atomicity: Ensures that a transaction is "all or nothing". Either all operations within the transaction succeed, or none of them are applied to the database.
- Consistency: Guarantees that the database remains in a consistent state before and after any transaction. It prevents transactions from leaving the database in an inconsistent state.
- Isolation: Ensures that transactions operate independently of each other. Each transaction should appear as if it is executed in isolation, even when multiple transactions are running concurrently.
- Durability: Ensures that once a transaction is committed, changes are permanently stored in the database, even in the event of system failure.
These properties collectively provide a secure and reliable way to manage data in multi-user environments.
13. What is Correlated Subquery in DBMS?
Answer: A Subquery is also known as a nested query ie, a query written inside some query. When a Subquery is executed for cach of the rows of the outer query then it is termed as a Correlated Subquery.
Example of Non-Correlated Subquery is:
SELECT* fromEMP WHERE'RIYA' IN (SELECT Name from WHEREEMP.EMPID-DEPT.EMPID); DEPT
Here, the inner query is not executed for each of the rows of the outer query.
14. Explain Entity, Entity Type, and Entity Set in DBMS?
Answer: Entity is an object, place or thing which has its independent existence in the real world and about which data can be stored in a database. For Example, any person, book, etc.
Entity Type is a collection of entities that have the same attributes. For Example, the STUDENT table contains rows in which each row is an entity holding the attributes like name, age, and id of the students, hence STUDENT is an Entity Type which holds the entities having the same attributes. Entity Set is a collection of entities of the same type. For Example, A collection of the employees of a firm.
15) What are the different levels of abstraction in the DBMS?
Answer: There are 3 levels of data abstraction in the DBMS. They include:
Physical Level: This is the lowest level of the data abstraction which states how the data is stored in the database.
Logical Level: This is the next level of the data abstraction which states the type of the data and the relationship among the data that is stored in the database.
View Level: This is the highest level in the data abstraction which shows/states only a part of the database.
17)What is E-R model in the DBMS?
Answer: E-R model is known as an Entity-Relationship model in the DBMS which is based on the concept of the Entities and the relationship that exists among these entities.
18) What is a functional dependency in the DBMS?
Answer: This is basically a constraint which is useful in describing the relationship among the different attributes in a relation Example: If there is some relation 'R' which has 2 attributes as and Z then the functional dependency among these 2 attributes can be shown as Y->Z which states that Z is functionally dependent on y
19) What is 1NF in the DBMS?
Answer: INF is known as the First Normal Form. This is the easiest form of the normalization process which states that the domain of an attribute should have only atomic values. The objective of this is to remove the duplicate columns that are present in the table
20) What is 2NF in the DBMS?
Answer: 2NF is the Second Normal Form.
Any table is said to have in the 2NF if it satisfies the following 2 Conditions:
A table is in the INF.
Each non-prime attribute of a table is said to be functionally dependent in totality on the primary key.
21) What is 3NF in the DBMS?
Answer: 3NF is the Third Normal Form.
Any table is said to have in the 3NF if it satisfies the following 2 conditions:
A table is in the 2NF.
Each non-prime attribute of a table is said to be non-transitively dependent on every key of the table.
-0000-
Explore all units: