Plus Two Computer Science Chapter Wise Previous Questions Chapter 8 Database Management System

Kerala Plus Two Computer Science Chapter Wise Previous Questions and Answers Chapter 8 Database Management System

Question 1.
Components of DBMS [MARCH – 2008] (3)
Answer:
a) databases – database is an organized collection of information.
b) Data Definition Language(DDL) – Data Definition Language is used to specify the definitions of Da-tabase Schema. The result of compilation of DDL statements is a set of tables stored in a special file called data dictionary.
c) Data Manipulation Language(DML) – It is a language that enable users to access or manipulate data in the database. It also provides interfaces with programming languages.
d) Users – users interact with DBMS indirectly via ; application programs or directly via simple query language.

Question 2.
What are the advantages of DBMS? [MARCH – 2008] (5)
OR
Consider there is a strike against the introduction of computer in your firm. How can you convince the ben-efits of computerization to the employees by pointing out the benefits of database over manual system?
Answer:
1) Data Redundancy .Duplication of data is known as redundancy, i.e., the same data may be repeated in many files. It leads to higher storage and access cost. So data redundancy should be eliminated.

2) Inconsistency can be avoided: If data redundancy exist, it may lead to data inconsistency. If redun-dancy is removed then inconsistency can be avoided.

3) Data can be shared: If the computers are connected to a network, the data stored in the database can be shared among several users or programs.

4) Standards can be enforced: There is a standard BIS (Bureau of Indian Standards) in the field of Gold and ISBN (International Standard Book Number) in the field of publication. Similarly here is also some standards like ANSI(American National Standards Institute), ISO (International Organization for standardization), etc.. For example a filed “Name” should have 40 characters is a standard.

5) Security restrictions can be applied: The database is of great value to a company or Organization. Data security means protection of data against accidental or intentional disclosure or unauthorized destruction or modification by un-authorized persons.

6) Integrity can be maintained: It ensures that the data entered in the database is correct. The data may contain incorrect information for example a date of birth can be 30/30/2001. To avoid this DBMS defines integrity check.

7) Efficient data access: It stored huge amount of data efficiently and can be retrieved whenever a need arise.

8) Crash recovery: Sometimes all ora portion of the data is lost when a system crashes. A good DBMS helpsto recover data afterthe system crashed.

Question 3.
The repetition of same data in a file system is known as ……….. . [MARCH – 2009]
Answer:
Data Redundancy

Question 4.
What is a Cartesian product? [MARCH – 2009]
Answer:
Cartesian product returns a relation consisting of all possible combinations of the tuple from two relations. Which is a binary operation. It is denoted by X. It is also called Cross Product.

Question 5.
Explain the different levels of data independence. [MARCH – 2009] (2)
Answer:
Data independence is the ability to modify the schema definition in one level without affecting the schema definition at the next higher level. There are two levels Of data independence, Physical data independence and. Logical data independence.

Question 6.
Name the different data models available for database systems. Which of them is the most preferred one? Briefly explain with a suitable example. [FEBRUARY – 2009] (S)
Answer:
There are three models:-
a) Hierarchical Model:
It assigns records to different levels of hierarchy. A lower level record is called the child and the higher level is called the parent. Data redundancy is the disadvantage of this model.

b) Network Model : It is similar to hierarchical model, but each child record can have more than one parent record. This model eliminates the re-dundancy encountered in the hierarchy model, but it has a high degree of complexity. It was introduced by CODASYL-DBTG in 1971.

c) Relational Model : It is based on the concept introduced by E F Godd. It composed of one or more tables. Here tables are called relations, rows are called tuples and the columns are called at-tributes. It has no data redundancy and complex-ity. So this is the best model.

Question 7.
Explain two unary operations in RDBMS using an example. [FEBRUARY – 2009] (3)
Answer:
1) Select: It is used to select tuples in a relation that satisfy a selection condition. To denote selection the lowercase Greek letters are used. The condition appears as a subscript to a. The relation is given in the parenthesis.
Eg:
To select the tuples whose salary > 5000 from Employee relation Employee)

2) Project: It selects certain columns from the table and discardsthe other columns. It is denoted by the Greek letter n.
Eg:
To select name and salary from the relation Employee.
nna™,salary (Employee)

Question 8.
Select a terminology which is not a part of DBMS. [JUNE – 2009] (1)
a) Relation b) Function c) Domain d) Tuple
Answer:
Function

Question 9.
Explain the different levels of data abstraction. [JUNE – 2009] (3)
Answer:
1) Physical Level is the lowest level. It describes how the data is actually stored in the storage medium. At physical level complex low-level data structures are described in detail.

2) Logical level describes what data are stored in the database and what relationships exist among data. Here database is described in terms of simple structures. Records are defined in this level. Programmers work at this level.

3) View level is the highest level of data abstraction. It is concerned with the way in which the users view the database. It describes only part of the database.

Question 10.
What are the components of DBMS? Explain. [JUNE – 2009] (3)
Answer:
a) databases – database is an organized collection of information.
b) Data Definition Language(DDL) – Data Definition Language is used to specify the definitions of Da-tabase Schema. The result of compilation of DDL statements is a set of tables stored in a special file called data dictionary.
c) Data Manipulation Language(DML) – It is a lan-guage that enable users to access or manipulate data in the database. It also provides interfaces with programming languages.
d) Users – users interact with DBMS indirectly via application programs or directly via simple query language.

Question 11.
Explain the structure of a file. [JUNE – 2009] (2)
Answer:
The structure of a file
Field: A field is the smallest unit of data. Ex: roll number, name.

Record : The collection of related fields is called a record. Ex: The information about a student File: A file is a collection of related records. Ex: The students details of a class.

Question 12.
__________ is the smallest unit of data. [JUNE – 2009] (1)
a) Record
b) file
c) key
d) field
Answer:
d. Field

Question 13.
Consider the database of Online Ticket reservation. Explain the classification of the users of this database. [MARCH – 2010] (3)
Answer:

Question 14.
There is an attribute Roll number in the table STUDENT. This attribute is also the candidate key in the table EXAM. So the Roll number is considered as key in the table EXAM. [MARCH – 2010]
(a) Foreign
(b) Primary
(C) Alternate
(d) Super
Answer:
(a) Foreign

Question 15.
Consider the following table and write relational al-gebra operations for the following: [MARCH – 2010] (3)
Student

a) To display those tuples from STUDENT relation where total is greater than 90.
b) To display cnly Regno and Name of all students.
Answer:
a) σ_Total>90 (Student)
b) π_{Regno, Name} (Student)

Question 16.
Briefly explain the different keys that are used in RDBMS. [MARCH – 2010] (4)
Answer:

1. Candidate Key:lt is a set of attributes that uniquely identifies a row. There may be more than candidate key and may be a combination of more than one attribute.
2. Primary Key : A primary key is one of the Candidate Keys. It is a set of one or more attributes that can uniquely identify tuples in a relation.
3. Alternate Key: The Candidate key that is not the primary key is called the alternate key.
4. Super Key : A combination of a primary key with any other attribute or group of attributes is called a super key.
5. Foreign Key : Asingle attribute or a set of attributes, which is a candidate key in another table, is called foreign key.

Question 17.
Match the following with the levels of abstraction to which the users are primarily associated: [MARCH -2011] (3)
Levels of Abstraction – Users
1. Physical level – A. Naive users
2. Logical level – B. Database administrator
3. View level – C. Application programmer
Answer:
1) Physical Level – Database Administrator
2) Logical Level – Application programmer
3) View Level – Naive users

Question 18
A data model provides a way to represent data and their relationships. [MARCH -2011] (3)
a) Which data model assigns records to diferent levels of hierarchy?
b) Briefly explain the relational data model.
Answer:
a) Hierarchical Data Model
b) In relational data model data are stored as tables. Here the term relation means table. A table consists of rows and columns. Columns mean attributes and rows means tuples.

Question 19.
Explain how data is organized for effective storage and retrieval of information. [MARCH -2011] (2)
Answer:
There are three data models used for effective storage and retrieval of information. They are hierarchical, network and relational data models.

Question 20.
Which is not an RDBMS package? [MARCH -2011] (1)
a) Oracle
b) SQL Server
e) Mysql
d) HTML
Answer:
(d) HTML

Question 21.
The smallest unit of stored data in a database is [MARCH -2011] (1)
Answer:
Field

Question 22.
Mini created a relation in which two of the tuples have the same combination of values for all their at tributes.
Is this allowed? Give reason. [MARCH -2011] (2)
Answer 1.
No. It is not allowed because a good DBMS does not allow duplication of the same data. i.e. data redundancy is not allowed.

Answer 2.
Yes. Same values of data is allowed if primary key or unique key is not defined in the relation.

Question 23.
What ¡s the importance of primary key? [MARCH -2011] (2)
Answer:
Key: A pnmary key is one of the Candidate Keys. It is a set of one or more attributes that can uniquely identify tiiples in a relation.

Question 24.
Discuss the levels of data abstraction in DBMS. [March – 2016] (3)
Answer:
1) Physical Level is the lowest level.lt describes how ‘ the data is actually stored in the storage medium. At physical level complex low-level data structures are described in detail.

2) Logical level describes what data are stored in the database and what relationships exist among data. Here database is described in terms of simple structures. Records are defined in this level. Programmers work at this level.

3) View level is the highest level of data abstraction. It is concerned with the way in which the users view the database. It describes only part of the database.

Question 25.
In the ACCOUNT relation shown below [March – 2016]

a) Identify the primary keys and candidate keys.
b) Select all account holders with balance greater than Rs. 2,00,000. (2)
Answer:
a) Primary key-ACC. Number
Candidate keys – Name and Balance

b) σ_{2Balance>200000} (ACCOUNT)
OR
Select * from Account where Balance>200000

Question 26.
What are the major advantages of the relational model over other data models? [Say – 2016] (1)
Answer:
The relational data model has no redundancy and no complexity.

Question 27.
a) Classify the following operations in relational algebra into unary and binary operations:
(1) UNION
(2) SELECT
(3) SET DIFFERENCE
(4) PROJECT (1)

b) Explain about SELECT, INTERSECTION and SET DIFFERENCE operations with example. [Say – 2016] (3)
Answer:
a) Unary Binary
(2) SELECT
(1) UNION
(4) PROJECT
(3) SET DIFFERENCE

b) SELECT operation
SELECT operation is used to select tuples in a relation that satisfy a selection condition. Greek letter cr (sigma) is used to denote the operation.

Syntax,
σ_Condition (relation)

eg. σ_{Salary<100000} (EMPLOYEE)-selects tuple whose salary is less than 10000 from EMPLOYEE relation.

Intersection operation
This operation returns a relation consisting of all the tuples appearing in both of the specified rela-tions. It is denoted by n. It can takes place only on compatible relations, e.g. FOOTBALL ∩ CRICKET returns the players who are in both football and cricket teams.

Set difference operation (-)
All tuples appearing in the first relation and not in the second.

Question 28.
a) Discuss the advantages of DBMS. (3)
b) Create a database schema for the relation VEHICLE. [MARCH -2017] (2)
Answer:
a) Advantages of DBMS
1) Data Redundancy-It means duplication of data. DBMS eliminates redundancy. DBMS does not store more than one copy of the same data.

2) Inconsistency can be avoided – If redundancy occurs there is a chance to inconsistency. If redundancy is removed then inconsistency cannot occur.

3) Data can be shared – The data stored in the database can be shared by the users or programs.

4) Standards can be enforced – The data in the database follows some standards. Eg: a field ‘Name’ should have 40 characters long. Some standards are ANSI, ISO, etc.

5) Security restrictions can be applied – The data is of great value so it must be kept secure and private. Data security means the protection of data against accidental or intentional disclosure or unauthorized destruction or modification by unauthorized person.

6) Integrity can be maintained – It ensures that the data is to be entered in the databse is correct.

7) Efficient data access – It stored huge amount of data efficiently and can be retrieved whenever a need arise.

8) Crash recovery – Sometimes all or a portion of the data is lost when a system crashes. A good DBMS helps to recover data after the system crashed.

b)

Question 29.
_________ in a table gives the complete data of a particular entity. [Say – 2017]
a) Tuple
b) Attribute
c) Domain
d) Schema (1)
Answer:
d) Schema

Plus Two Computer Science Chapter Wise Assess Questions and Answers

Question 1.
Who is responsible for managing and controlling the activities associated with the database? (1 Mark)
a) Database administrator
b) Programmer
c) Native user
d) End user
Answer:
a) Database administrator

Question 2.
In the relational model, cardinality is the _______ . (1 Mark)
a) numberoftuples
b) number of attributes
c) number of tables
d) number of constraints
Answer:
a) numberoftuples

Question 3.
Cartesian product in relational algebra is _______ . (1 Mark)
a) a Unary operator
b) a Binary operator
c) a Ternary operator
d) not defined
Answer:
b) a Binary operator

Question 4.
Abstraction of the database can be viewed as _________ . (1 Mark)
a) two levels
b) four levels
c) three levels
d) one level
Answer:
c) three level

Question 5.
In a relational model, relations are termed as __________ . (1 Mark)
a) tuples
b) attributes
c) tables
d) rows
Answer:
c) tables

Question 6.
In the abstraction of a database system the external level is the ___________ . (1 Mark)
a) physical level
b) logical level
c) conceptual level
d) view level
Answer:
d) view level

Question 7 (1 Mark)
Related fields in a database are grouped to form a ___________ .
a) data file
b) data record
c) menu
d) bank
Answer:
b) data record

Question 8.
A relational database developer refers to a record as ___________ . (1 Mark)
a) criteria
b) relation
c) tuple
d) attribute
Answer:
c) tuple

Question 9.
An advantage of the database management approach is ___________ . (1 Mark)
a) data is dependent on programs
b) data redundancy increases
c) data is integrated and can be accessed by multiple programs
d) none of the above
Answer:
c) data is integrated and can be accessed by multiple programs

Question 10.
Data independence means (1 Mark)
a) data is defined separately and not included in programs
b) programs are not dependent on the physical at-tributes of data
c) programs are not dependent on the logical at-tributes of data
d) both (b) and (c)
Answer:
d) both (b) and (c)

Question 11.
Key to represent relationship between tables is called ___________ . (1 Mark)
a) primary key
b) candidate Key
c) foreign Key
d) alternate Key
Answer:
c) foreign key

Question 12.
Which of the folowing operations is used if we are interested only in certain columns of a table? ___________ . (1 Mark)
a) Projection
b) Selection
c) Union
d) Select
Answer:
a) Projection

Question 13.
Which of the following operations need the partici-pating relations to be union compatible? ___________ . (1 Mark)
a) UNION
b) INTERSECTION
c) SET DIFFERENCE
d) All of the above
Answer:
d) All of the above

Question 14.
Which database level is closest to the users? ___________ . (1 Mark)
a) External
b) Internal
c) Physical
d) Conceptual
Answer:
a) View level (External)

Question 15.
The result of the UNION operation between R1 and R2 is a relation that includes ___________ . (1 Mark)
a) all the tuples of R1
b) all the tuples of R2
c) all the tuples of R1 andR2
d) all the tuples of R1 and R2 which have common columns
Answer:
(d) All the tuples of R1 and R2 (eliminating the duplication)

Question 16.
A file manipulation command that extracts some of the records from a file is called ___________ . (1 Mark)
a) Select
b) Project
c) Join
d) Product
Answer:
a) select

Question 17.
An instance of relational schema R (A, B, C) has distinct values of A including NULL values. Which one of the following is true? ___________ . (1 Mark)
a) A is a candidate key
b) A is not a candidate key
c) A is a primary key
d) Both (a) and (c)
Answer:
a) A is a candidate key

Question 18.
How many distinct tuples are there in relation instance with cardinality 22? ___________ . (1 Mark)
a) 22
b) 11
c) 1
d) none
Answer:
a) 22

Question 19.
A set of possible data values is called ___________ . (1 Mark)
a) Attribute
b) Degree
c) Tuple
d) Domain
Answer:
d) Domain

Question 20.
Why should you choose a database system instead of simply storing data in conventional files? (5 Mark)
Answer:
Advantages of DBMS over conventional files Data Redundancy – It means duplication of data. DBMS eliminates redundancy. DBMS does not store more than one copy of the same data. Inconsistency can be avoided – If redundancy occurs there is & chance to inconsistency. If redundancy is removed then inconsistency cannot occur.

Efficient data access -: It stored huge amount of data efficiently and can be retrieved whenever a need arise.

Data can be shared – The data stored in the database can be shared by the users or programs. Standards can be enforced – The data in the database follows some standards. Eg: a field ‘Name’ should have 40 characters long. Some standards are ANSI, ISO, etc.

Security restrictions can be applied – The data is of great value so it must be kept secure and private. Data security means the protection of data against accidental of intentional disclosure or unauthorized destruction or modification by unauthorized person.

Integrity can be maintained – It ensures that the data is to be entered in the database is correct.

Crash recovery- Some times all ora portion of the data is lost when a system crashes. A good DBMS he’psto recover data after the system crashed.

Question 21.
Explain the different levels of data abstraction in DBMS? (3 Mark)
Answer:
Levels of Database Abstraction –
1) Physical Level (Lowest Level) – It describes how the data is actually stored in the storage medium.
2) Logical Level (Next Higher Level) – It describes what data are stored in the database.
3) View Level (Highest level) – It is closest to the users. It is concerned with the way in which the individual users view the data.

Question 22.
How are schema layers related to the concepts of logical and physical data independence? (3 Mark)
Answer:
Data Independence – It is the ability to modify the scheme definition in one level without affecting the scheme definition at the next higher level.
a) Physical Data independence – If is the ability to modify the physical scheme without causing application programs to be rewritten.
b) Logical Data Independence – It is the ability to modify the logical scheme without causing application programs to be rewritten.

Question 23.
Consider the instance of the EMPLOYEE relation shown in the following table. Identify the attributes, degree, cardinality and domain of Name and Emp_code. (3 Mark)

Answer:
Attributes- These are column names, i.e, Emp_Code, Name, Department, Designation and Salary Degree(CD) -the number of Columns is the Degree
i. e Degree is 5(Here 5 columns)
Cardinality (RC)-: the number of Rows is the Cardinality
i. e. Cardinality is 4(Here 4 rows)
Domain is the pool of possible values
Domain of Name is a String(Sudheesh, Dhanya,
Fathima, Shajan.etc)
Domain of Emp_Code is a number (1000,1001,1002, 1003, etc)

Question 24.
Identify primary key, candidate keys and alternate keys in the instance of EMPLOYEE relation in Question 23. (3 Mark)
Answer:
Candidate key – It is used to uniquely identify the row.
Emp_code and Emp_Code + Department (Composite) are the candidate keys
Primary key – It is a set of one or more attributes used to uniquely identify a row.
Empjcode is the primary key
Alternate key – A candidate key other than the primary key.
We set Emp_code as the primary key then Emp_code+ Department is the alternate key

Question 25.
Consider the instance of the STUDENT relation shown in the following table Assume Reg_no as the primary key. (3 Mark)
a) Identify the candidate keys and alternate keys in the STUDENT relation
b) How are the primary key and the candidate key-related?

Answer:
a) Reg_no and Reg_no+Batch are the candidate keys. We set Reg_no as the primary key hence Reg_no+Batch is the alternate key
b) Candidate Key :lt is a set of attributes that uniquely identifies a row. There may be more than candidate key and may be a combination of more than one attribute.

Primary Key: A primary key is one of the Candidate Keys. It is a set of one or more attributes that can uniquely identify tuples in a relation.

Question 26.
What is a database? Describe the advantages and disadvantages of using DBMS. (5 Mark)
Answer:
A Database is a collection of large volume of data.

Advantages of DBMS
Data Redundancy – It means duplication of data. DBMS eliminates redundancy. DBMS does not store more than one copy of the same data.
Inconsistency can be avoided – If redundancy occurs there is a chance to inconsistency. If redundancy is removed then inconsistency cannot occur. Efficient data access It stored huge amount of data efficiently and can be retrieved whenever a need arise.

Data can be shared – The data stored in the database can be shared by the users or programs.

Standards can be enforced – The data in the database follows some standards. Eg: a field ‘Name’ should have 40 characters long. Some standards are ANSI, ISO, etc.

Security restrictions can be applied – The data is of great value so it must be kept secure and private. Data security means the protection of data against accidental or intentional disclosure or unauthorized destruction or modification by unauthorized person.

Integrity can be maintained – It ensures that the data is to be entered in the database is correct.

Crash recovery- Sometimes all ora portion of the data is lost when a system crashes . A good DBMS helps to recover data after the system crashed.

Question 27.
What is data independence? Explain the difference between physical and logical data independence. (3 Mark)
Answer:
Data Independence – It is the ability to modify the scheme definition in one level without affecting the scheme definition at the next higher level.
a) Physical Data Independence – It is the ability to modify the physical scheme without causing application programs to be rewritten.
b) Logical Data Independence – It is the ability to modify the logical scheme without causing application programs to be rewritten.

Question 28 (3 Mark)
Enforcement of standard is an essential feature of DBMS. How are these standards applicable in a da-tabase?
Answer:
There is a standard BIS (Bureau of Indian Standards) in the field of Gold and ISBN (International Standard Book Number) in the field of publication. Similarly here is also some standards like ANSI(American National Standards Institute), ISO (International Organization for standardization), etc.. For example a filed “Name” should have 40 characters is a standard.

Question 29.
Cardinality of a table T1 is 10 and of table T2 is 8 and the two relations are union compatible. If the cardi-nality of result T1 ∪ T2 is 13, then what is the cardi-nality of T1 ∩ T2? Justify your answer. (3 Mark)
Answer:
Cardinalty of table T1 is 10 means it has 10 rows Cardinalty of table T2 is 8 means it has 8 rows Normally T1 ∪ T2 is 10+8 = 18 But Here T1 ∪ T2 is 13 means after eliminating duplication of 5 rows this happened.

This means 5 rows are common. That is T1 ∩ T2 is 5

Question 30.
CardinalityofatableTI is10andoftableT2is8and the two relations are union compatible. (3 Mark)
a) What will be the maximum possible cardinality of T1 ∪ T2?
b) What will be the minimum possible cardinality of T1 ∩ T2?
Answer:
a) Degree(CD) -the number of Columns is the Degree Cardinality (RC)-: the number of Rows is the Cardinality T1 ∪ T2 = Sum of cardinalities of Table 1 and Table 2
i. e.T1 ∪ T2 = 10 + 8 = 18

b) T1 ∩ T2 is the common rows(tuples) in T1 and T2 If there is no common tuples then T1 ∩ T2 is 0 hence the cardinality is 0.

Question 31.
Conside the relations, City (city_name, state) and Hotel (name, address, city_name). Answer the following queries in relational algebra (5 Mark)
a) Find the names and address of hotels in Kochi.
b) List the details of cities in Kerala state.
c) List the names of the hotels in Thrissur.
d) Find the names of different hotels.
e) Find the names of hotels in Kozhikode or Munnar.
Answer:

Question 32.
Using the instance of the EMPLOYEE relation shown in question 23, write the result of the following relational algebra expressions. (5 Mark)

Answer:
a)

b)

c)

d)

e)

f) No rows selected

Question 33.
Consider the instance of the BORROWER and DE- POSlTOR relations shown in following figure which stores the details of customers in a Bank. Answer the following queries in relational algebra. (5 Mark)
a) Display the details of the customers who are either a depositor or a borrower.
b) Display the name of customers who are both a depositor and a borrower.
c) Display the details of the customers who are d positors but not borrowers.
d) Display the name and’amount of customer who is a borrower but not depositor.

Answer:
a)

b)

c)

d)

Question 34.
Consider the instance of the CUSTOMER and BRANCH relations shown in the following table. Write the Cartesian Product of the two relations. (3 Mark)

Answer:

Plus Two Computer Science Chapter Wise Previous Questions