+2]9. Structured Query Language - Previous Questions chapter wise

                   PLUS TWO COMPUTER APPLICATION

         (+2 Computer Application / Computer Science , 
Previous Questions Chapter wise ..

                                         Chapter  9. Structured Query Language   

                                             

1. ..…. keyword is used to in SELECT query to eliminate duplicate values in a column.

(a) UNIQUE

 (b) DISTNICT

 (c) NOT NULL

  (d) PRIMARY

Ans.b 

2.  How will you add a new column to an existing table using SQL  Statement?  

      If we want to add a column at the first position, use the clause FIRST. If we want to add a column at a specified position, use the clause ALTER <column _name> . Note that if we do not specify the position of the new column, then by default, it will be added as the last column of the table. Remember that the newly added column contains only NULL values.

syntax: ALTER TABLE <table name>  ADD <column name> <datatype >[<size>] [<constraint>] [FIRST | AFTER <column name>];

3. .Explain the SQL statements used to insert and delete data from a table.

        The DML command INSERT INTO is used to insert tuples into tables.

 The syntax is:
 INSERT INTO <TABLE_NAME>  [<column1><column2>,,...<columnN>,] VALUES(<value1><value2>,,..<valueN);
 Here <table_name>  is the name of the table into which the tuples are to be inserted;<column1>,<column2> , , …<columnN> indicate the name of columns in the table into which values are to be inserted;<value1>,<value2> , …<valueN> are the values that are inserted into the columns specified in the  <column list>.

Sometimes we need to remove one or more records from the table. The DML command DELETE is used to remove individual or a set of rows from a table. The rows which are to be deleted are selected by using the WHERE clause. If the WHERE clause is not used, all the rows in the table will be deleted. The DELETE command removes only records and not the individual field values.

 The syntax of the DELETE command is:

 DELETE FROM <TABLE_NAME>  [WHERE<condition> ];

 4. Explain any two DDL commands. 

     CREATE TABLE  and  ALTER TABLE

      The DDL command CREATE TABLE is used to define a table by specifying the name of the table and giving the column definitions consisting of name of the column, data type and size, and constraints if any, etc. Remember that each table must have at least one column.
The syntax of CREATE TABLE command is:

CREATE TABLE <table_ name> (<column_name> <data_type> [<constraint>] [,<column_name><data_type>[<constraint>,] .............................. .............................. );

 If we want to add a column at the first position, use the clause FIRST. If we want to add a column at a specified position, use the clause ALTER <column _name> . Note that if we do not specify the position of the new column, then by default, it will be added as the last column of the table. Remember that the newly added column contains only NULL values.

syntax: ALTER TABLE <table name>  ADD <column name> <datatype >[<size>] [<constraint>] [FIRST | AFTER <column name>];
                
  5. What is a view ?How can we create a view using SQL statement ?

        MySQL supports the concept of views, which is a feature of RDBMS. A view is a virtual table that does not really exist in the database, but is derived from one or more tables. The table(s) from which the tuples are collected to constitute a view is called base table(s). These tuples are not physically stored anywhere, rather the definition of the view is stored in the database. Views are just like windows through which we can view the desired information that is actually stored in a base table.

A view can be created with the DDL command CREATE VIEW.

 The syntax is: CREATE VIEW <view name>  AS SELECT <column name1> [column name2]......FROM <table name> [WHERE <condition> ] ;  

6……….. clause of SELECT query is used to apply condition to form   
  groups of records.

(a) Order by

 (b) group  by 

(c) having

 (d) where  

Ans. c 

7.   a. Define constraints in SQL.

      b. explain any 4 constraints

      c. Compare column constraints and table constraints.

  Constraints are the rules enforced on data that are entered into the column of a table. When we create a table, we can apply constraints on the values that can be entered into its fields. If this is specified in the column definition, SQL will not accept any values that violate the criteria concerned. This ensures the accuracy and reliability of the data in the database. The constraints ensure database integrity and hence they are often called data base integrity constraints. Constraints could be column level or table level.

b. Column constraints are applied only to individual columns. They are written immediately after the data type of the column. The following are column constraints: 

i. NOT NULL This constraint specifies that a column can never have NULL values. NULL is a keyword in SQL that represents an empty value. It is important to remember that NULL does not equate to a blank or a zero; it is something else entirely. Though a blank is equal to another blank and a zero is equal to another zero, a NULL is never equal to anything, not even another NULL. Two NULL values cannot be added, subtracted or compared. 

ii. AUTO_INCREMENT MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. If no value is specified for the column with AUTO_INCREMENT constraint, then MySQL will assign serial numbers automatically and insert the newly assigned value in the corresponding column of the new record. By default, the starting value for AUTO_INCREMENT is 1, and it will be incremented by 1 for each new record. This special behavior also occurs if we explicitly assign the value NULL to the column. The AUTO_INCREMENT feature makes it easy to assign a unique ID to each new row, because MySQL generates the values for us. The auto increment column must be defined as the primary key of the table. Only one AUTO_INCREMENT column per table is allowed.

 iii. UNIQUE It ensures that no two rows have the same value in the column specified with this constraint.

 iv. PRIMARY KEY This constraint declares a column as the primary key of the table. This constraint is similar to UNIQUE constraint except that it can be applied only to one column or a combination of columns. The primary keys cannot contain NULL values. In other words, it can be considered as a combination of UNIQUE and NOT NULL constraints. A PRIMARY KEY constraint is used to enforce a rule that a column should contain only unique, non-NULL data.                              
c. Column constraints are applied only to individual columns. They are written immediately after the      data type of the column.
      Table constraints are similar to column constraints; the main difference is that table constraints          can be used not only on individual columns, but also on a group of columns. When a constraint is        to be applied on a group of columns of a table, it is called table constraint. The table constraint            appears at the end of the table definition. 

 8.Define the following

   DDL (Data definition language)

   DML (Data manipulation language)

   DCL (Data control language)  

DDL is a component of SQL that provides commands to deal with the schema (structure) definition of the RDBMS. The DDL commands are used to create, modify and remove the database objects such as tables, views and keys. The common DDL commands are CREATE, ALTER, and DROP.

DML is a component of SQL that enhances efficient user interaction with the database system by providing a set of commands. DML permits users to insert data into tables, retrieve existing data, delete data from tables and modify the stored data. The common DML commands are SELECT, INSERT, UPDATE and DELETE.

Data Control Language (DCL) is used to control access to the database, which is very essential to a database system with respect to security concerns. DCL includes commands that control a database, including administering privileges and committing data. The commands GRANT and REVOKE are used as a part of DCL.

 GRANT : Allows access privileges to the users to the database.
 REVOKE : Withdraws user's access privileges given by using GRANT command.

 9.   Write the result of the following

      ALTER TABLE <table_name> DROP <column_name>

      DELETE * from <table_name>

       DROP TABLE <table_name>

Ans. Removing column from a table. If we want to remove an existing column from a table, we can           use DROP clause along with ALTER TABLE command.

      Delete rows from a table.That is all the rows in the table will be deleted. The DELETE command removes only records and not the individual field values.

  Removing table from a database.If we do not need a table, it can be removed from the database using DROP TABLE command.

                                                                                                     

    

Read More

+2] 9. Structured Query Language - Solved Questions from text book

           Chapter 9. Structured  Query Language

(+2 Computer Application / Computer Science , 
Text book Questions and Answers)

1. SQL stands for __________.

Ans. Structured Query Language

 2. Which are the three components of SQL?

 Ans. Data definition language(DDL),Data manipulation language(DML),Data control language(DCL)

 3. SQL can be used to:

 a. create database structures only.

 b. query database data only.

c. modify database data only.

d. All of these can be done by SQL.

Ans d

 4. SQL is: 

a. a programming language.

 b. an operating system.

c. a data sublanguage.

d. a DBMS.

Ans a

 5. Which of the following is not an RDBMS package?

 a. ORACLE

 b. SQL SERVER

c. MySQL

d. HTML

Ans.d

6.. Which of the following commands is used to display the structure of a table?

 a. LIST

 b. SHOW

c. DESCRIBE

 d. STRUCT

Ans.c

 7. Write the syntax of CREATE TABLE command.

CREATE TABLE <table name>( <column name>  <datatype> [<constraints>], [<column name> <datatype> <Constraints>],.............................. .............................. );

8. Name the different column constraints.

•  NOT NULL
•  UNIQUE
• AUTO INCREMENT
• PRIMARY KEY
• DEFAULT       

 9. What is the difference between primary key constraint and unique constraint?

    This constraint declares a column as the primary key of the table. This constraint is similar to UNIQUE constraint except that it can be applied only to one column or a combination of columns. The primary keys cannot contain NULL values. In other words, it can be considered as a combination of UNIQUE and NOT NULL constraints. A PRIMARY KEY constraint is used to enforce a rule that a column should contain only unique, non-NULL data.

10. What are the features of AUTO_INCREMENT constraint?

    MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. If no value is specified for the column with AUTO_INCREMENT constraint, then MySQL will assign serial numbers automatically and insert the newly assigned value in the corresponding column of the new record. By default, the starting value for AUTO_INCREMENT is 1, and it will be incremented by 1 for each new record. This special behavior also occurs if we explicitly assign the value NULL to the column.

 11. Write down the rules for naming a table.

•  The name may contain letters (A - Z, a - z), digits (0 - 9), under score ( _ ) and dollar ($) symbol.

 • The name must contain at least one character. (Names with only digits are invalid).

 • The name must not contain white spaces, special symbols.

 • The name must not be an SQL keyword.

• The name should not duplicate with the names of other tables in the same data base and with other columns in the same table.

12. How many columns in a table can be specified as primary key of the table?

  Ans.1

13. Which of the following is used to add a row into a table?

 a. ADD

b. CREATE

c. INSERT

d. MAKE

Ans. c

14. Which statement is used to insert new data into a table?

 a. ADD RECORD

b. INSERT RECORD

c. INSERT INTO

d. INSERT ROW

Ans. c

15. Write the essential keywords used along with INSERT command.

  INSERT INTO  TABLE NAME [<column1>,<column2>,....<columnn>]  VALUES(<value 1>,<value2>,,..<valuen>);

16.  Name the keyword used with SELECT command to avoid duplication in the values of a column. 

  This duplication can be eliminated using the keyword DISTINCT

17. Which is the essential clause for SELECT query? 

   The name of the table is given with the keyword FROM, which is an essential clause with SELECT command

18. Which of the following operators is used to check for NULL value in a column?

 a. IN

 b. LIKE

 c. IS 

d. NOT 

Ans. c

19. The operator used for checking pattern matching is _________. 

Ans. LIKE operator

20. What is wrong with the following statement? 

SELECT * FROM emp WHERE grade = NULL;

 21. The command that extracts records from a table is ______

 Ans. WHERE clause command.

22. What is the meaning of ORDER BY clause ?

    It is possible to display the records in a specific order - ascending or descending, of some column values? Yes. The result of a query can be sorted in the ascending or descending order by making use of ORDER BY clause. The order is to be specified by using the keyword ASC (for ascending) or DESC (for descending) along with the column name that is used with ORDER BY clause. By default, the display will be in the ascending order.

 23. Which keyword is used for sorting the data in descending order in MySQL?

 a. DESC

 b. ASC

c. SORT

d. MODIFY

Ans a

 24. In which order are the records displayed in the absence of ORDER BY clause.

 25. In SQL, what is the default sort order of the ORDER BY clause?

 Ans. Ascending order(ASC).

26.  List the aggregate functions of SQL.

 SUM():  Total of the values in the column specified as argument.

AVG() :  Average of the values in the column specified as argument.

MIN(): Smallest value in the column specified as argument.

MAX() : Largest of the values in the column specified as argument.

COUNT(): Number of non NULL values in the column specified as argument.

 27. How do COUNT(*) and COUNT(column_name) differ?

   COUNT(*) and it gives the number of records having the value in column . Note that the * (asterisk) symbol stands for the collection of all the columns in the table. So, if there is at least one field in a record, that record will be taken into consideration for COUNT(*).

 COUNT ( column_name)  counts only the non-NULL values in column.

 28. What is the difference between WHERE clause and HAVING clause?

   WHERE clause, which places conditions on individual rows. We can apply conditions to form groups with the help of HAVING clause.This clause is used along with GROUP BY clause. The condition in HAVING clause is applied to form groups of records, not individual rows.

 29. The usage SUM(*) or MAX(*) is invalid. Why?

   SUM(*) and MAX(*)  are invalid  function.SUM( coumn name) and MAX( column name) are the valid one.

30. What will be the result of the following query?

SELECT COUNT(DISTINCT course) FROM student;

   The result of the query is counts the non null values of the distinct course column of a student table

31. The command used to edit the structure of a table is ________.

 Ans.  ALTER TABLE

 32. Restructuring of a column affects the values in it. State whether true or false.

33. How will you remove a column from a table?

      If we want to remove an existing column from a table, we can use DROP clause along with ALTER TABLE command

34. Name the command used to remove a row from a table.

Ans. DELETE

35. The command to remove rows from a table 'CUSTOMER' is:

 a. REMOVE FROM CUSTOMER

b. DROP TABLE CUSTOMER

 c. DELETE FROM CUSTOMER

d. UPDATE CUSTOMER

Ans. c

36. If values for some columns are unknown, how is a row inserted?

 37. Distinguish between CHAR and VARCHAR data types of SQL.

        String is a group of characters. The most commonly used string data types in MySQL are CHARACTER or CHAR and VARCHAR.
 CHAR or CHARACTER: Character includes letters, digits, special symbols etc. The CHAR is a fixed length character data type. The syntax of this data type is CHAR(x), where x is the maximum number of characters that constitutes the data. The value of x can be between 0 and 255. CHAR is mainly used when the data in a column are of the same fixed length and small in size. For example, if we want to store data like 'M' for male and 'F' for female, in the column Gender of a table, it is better to declare that column as of type CHAR.

  VARCHAR(size) : VARCHAR represents variable length strings. It is similar to CHAR, but the space allocated for the data depends only on the actual size of the string, not on the declared size of the column. For example, if we want to store data in the column Name of a table, it is better to declare that column as of type VARCHAR, because the data in the column may contain different number of characters. The length of the string can vary from 0 to 65535 characters (MySQL version dependent)

38. What happens when we use DELETE FROM command without a WHERE clause?

         If the WHERE clause is not used, all the rows in the table will be deleted. The DELETE command removes only records and not the individual field values.

39. What do you mean by NULL value in SQL?

        NULL is a keyword in SQL that represents an empty value. It is important to remember that NULL does not equate to a blank or a zero; it is something else entirely. Though a blank is equal to another blank and a zero is equal to another zero, a NULL is never equal to anything, not even another NULL. Two NULL values cannot be added, subtracted or compared.

40. Which of the following is the correct order of clauses for the SELECT statements? 

a. SELECT, FROM, WHERE, ORDER BY 

b. SELECT, FROM, ORDER BY, WHERE 

c. SELECT, WHERE, FROM, ORDER BY 

d. SELECT, WHERE, ORDER BY, FROM

Ans. a

41. Read the following strings: (i) 'Sree Kumar' (ii) 'Kumaran' (iii) 'Kumar Shanu' (iv) 'Sreekumar'

Choose the correct option that matches with the pattern '%Kumar', when used with LIKE operator in a SELECT statement.

 a. Strings (i) and (ii) only

 b. Strings (i), (iii) and (iv) only 

 c. Strings (i) and (iii) only

  d. All the strings

Ans. b

42. Pick the odd one out and give reason:

a. CREATE

b. SELECT

c. UPDATE

d. INSERT

Ans. a

43. Write any four DML commands in SQL.

   SELECT,UPDATE,INSERT and DELETE

44.  A table is created in SQL with 10 records. Which SQL command is used to change the values in a column of specified rows? Write the format.

 UPDATE command used.

UPDATE SET <TABLE NAME> SET <column name>  = value [<column name>=<value>,.....] [WHERE  <condition>]; 

  45. Distinguish between DISTINCT and UNIQUE in SQL

     This duplication can be eliminated using the keyword DISTINCT.

      example: SELECT DISTINCT course FROM student;

       In the output, there are no duplicate values. If the column used with DISTINCT keyword                   contains more than one NULL value.

  UNIQUE: It ensures that no two rows have the same value in the column specified with this constraint.It is a column constraint.

Read More

+2] 8. Database Management System - Previous Questions chapter wise

                     PLUS TWO COMPUTER APPLICATION

 (+2 Computer Application / Computer Science , 
Previous Questions Chapter wise ..

                                         Chapter  8. Database Management System

1...................... level describes only a part of a database

(a) View

(b) Physical 

(c) Logical 

(d) None of these                                                                        

2. What is relational algebra ?Explain any three relational algebra operations

 Once the database is designed and data is stored, the required information is to be retrieved. A variety of operations are provided by RDBMS. The collection of operations that is used to manipulate the entire relations of a database is known as relational algebra.

1.Selection operation

SELECT operation is used to select rows from a relation that satisfies a given predicate. The predicate is a user defined condition to select rows of user's choice. This operation is denoted using lower case letter sigma (σ ). The general format of select is as follows:

 σ condition (Relation)

 The result of SELECT operation is another relation containing all the rows satisfying the given predicate (or conditions).

2.Project operation

The PROJECT operation selects certain attributes from the table and forms a new relation. If the user is interested in selecting the values of a few attributes, rather than all the attributes of the relation, then use PROJECT operation. It is denoted by lower case letter π . The general format of project operation is as follows:

 π A1, A2,…., An (Relation)

 Here A1, A2, . . . ., An refer to the various attributes that would make up the relation specified. 

3.Union operation

 UNION operation is a binary operation and it returns a relation containing all tuples appearing in either or both of the two specified relations. It is denoted by ∪ . The two relations must be union-compatible, and the schema of the result is defined to be identical to the schema of the first relation. If two relations are union-compatible, then they have the same number of attributes, and corresponding attributes, taken in order from left to right, have the same domain. Note that attribute names are not used in defining union-compatibility.

3. The number of attributes in a relation is called ………… 

(a) tuple 

(b) degree 

(c) cardinality

 (d) domain 

Ans. b

4.  Explain the components of DBMS

DBMS have several components, each performing very significant tasks in its environment. The components are 

• Hardware • Software • Data • Users • Procedures

Hardware: The hardware is the actual computer system used for storage and retrieval of the database. This includes computers (PCs, workstations, servers and supercomputers), storage devices (hard disks, magnetic tapes), network devices (hubs, switches, routers) and other supporting devices for keeping and retrieval of data.

 Software: The software part consists of the actual DBMS, application programs and utilities. DBMS acts as a bridge between the user and the database. In other words, DBMS is the software that interacts with the users, application programs, and databases. All requests from users for access to the database are handled by the DBMS

Data: It is the most important component of DBMS environment from the end users point of view. The database contains operational data and the meta-data (data about data). The database should contain all the data needed by the organization. The major feature of databases is that the actual data and the programs that uses the data are separated from each other. For effective storage and retrieval of information, data is organized as fields, records and files.

Fields: A field is the smallest unit of stored data. Each field consists of data of a specific type

Record: A record is a collection of related fields.

File: A file is a collection of all occurrences of same type of records.

Users: There are a number of users who can access data on demand using application programs. The users of a database system can be classified depending on the mode of their interactions with DBMS. The different categories of users are Database Administrator (DBA), Application Programmers, Sophisticated users and Naive Users.

 Procedures: Procedures refer to .the instructions and rules that govern the design and use of the database. The users of the system and the person that manages the database require documented procedures on how to use or run the system.

5. A candidate key that is not a primary key is called -----------------

Ans. Alternate key

6. Define the term

   a. Degree

    b. Cardinality

 Ans. The number of attributes in a relation determines the degree of a relation.

        The number of rows or tuples in a relation is called cardinality of the relation.

6. Define data abstraction and explain any two levels of abstraction

    For the system to be usable, it must retrieve data efficiently. The need for efficiency has led designers to use complex data structures to represent data in the database. Since many database system users are not computer trained, developers hide the complexity from users through several levels of abstraction. The data in a DBMS is described at three levels of abstraction.

  a. Physical level

  The lowest level of abstraction describes how data is actually stored on secondary storage devices such as disks and tapes. The physical level describes complex lowlevel data structures in detail. We must decide what file organisations are to be used to store the relations and create auxiliary data structures, called indexes, to speed up data retrieval operations.

b. Logical level

  The next-higher level of abstraction describes what data is stored in the database, and what relationships exist among those data. The logical level thus describes the entire database in terms of a small number of relatively simple structures.Although mplementation of the simple structures at the logical level may involve complex physical-level structures, the user of the logical level does not need to be aware of this complexity. Database administrators, who must decide what information to keep in the database, use the logical level of abstraction. Logical level is also referred as conceptual level.

7. Define the terms

  1. Fields

   2. Record.

Ans.  Fields: A field is the smallest unit of stored data. Each field consists of data of a specific type

            Record: A record is a collection of related fields.

8. In RDBMS contain 10 rows and 5 columns.what is the degree of the relation ?

 Ans.  5

9.Is it possible SELECT and PROJECT operations of relational algebra in single statement?Explain     with example?

  Not possible SELECT and PROJECT operations of relational algebra in single statement.

Select Operation : This operation is used to select rows from a table (relation) that specifies a given logic, which is called as a predicate. The predicate is a user defined condition to select rows of user's choice.

Project Operation : If the user is interested in selecting the values of a few attributes, rather than selection all attributes of the Table (Relation), then one should go for PROJECT Operation.

Example  in SELECT operation

σ sales > 50000 (Customers)

Output - Selects tuples from Customers where sales is greater than 50000

Example in PROJECT operation

CustomerID	CustomerName	Status
1	Google	Active
2	Amazon	Active
3	Apple	Inactive
4	Alibaba	Active

Here, the projection of CustomerName and status will give

Π CustomerName, Status (Customers)

CustomerName	Status
Google	Active
Amazon	Active
Apple	Inactive
Alibaba	Active

Read More

+2] 8. Database Management System - Solved Questions from textbook

            Chapter 8. Database Management System

(+2 Computer Application / Computer Science ,  
Text book Questions and Answers)

1. Storing of the same data in different places is called _______.

Ans. Database

 2. Address of a particular student is stored in two ways in a school record; this situation is known as _______. 

Ans. Redundancy

3. Unauthorised accessing of data can be prevented from using ______.

Ans. Data security 

4. Sharing of data can reduce data redundancy. State whether true or false?

Ans. False

 5. Data redundancy will reduce data consistency. State whether true or false

Ans. True

6. Data about data is called ________. 

Ans. Metadata

7. List the name of major components of a database system?

• Hardware
•   Software 
•    Data 
•   Users
•   Procedures

8. Categorize the following components of DBMS environment. Hard disk, Switch, DBA, Payroll system, End user, railway reservation system

Hard disk --Hardware

Switch ,Payroll system ,Railway reservation system  --- Software

DBA,End user ----- User

9. The person who interacts with the database through query language is called ________

Ans. Database Administrator

 10. The billing clerk in a Supermarket is a ________ user. 

Ans. Naive user

11. Who provides data security in a database? 

Ans.Database Administrator

12. Who changes the structure of a database?

Ans. Application Programmer

 13. ________ interacts with the database through the prewritten application program.

Ans. Naive user

14. Organization of data in terms of rows and columns is called ________. 

Ans.Table

15. ________ in a table gives the complete data of a particular entity.

Ans. Relation 

16. Number of rows in a relation is called ________. 

Ans. Cardinality

17. Number of ________ in a relation is called degree of the relation.

Ans. columns

 18. In the relational model, data is organised as ________. 

 Ans. Rows and columns

19. The minimal set of attributes that uniquely identifies a row in a relation is ________

Ans. Candidate key

 20. In a table Employee Emp_code, Pan_no are the candidate keys. If Emp_code is taken as the primary key then Pan_no is ________ key.

Ans. Alternate key

 21. How many primary keys are possible in a relation?

Ans. 1

 22. If a key consists of more than one attributes then it is called

Ans. Composite key

23. Who is responsible for managing and controlling the activities associated with the database?

a. Database administrator

 b. Programmer

 c. Naive user

 d. End user

Ans. a

 24. In the relational model, cardinality is the

 a. number of tuples

b. number of attributes

c. number of tables

d. number of constraints

Ans. a

25. Cartesian product in relational algebra is

 a.  a Unary operator

b. a Binary operator

c. a Ternary operator

d. not defined

Ans. b

 26. Abstraction of the database can be viewed as

 a. two levels

 b. four levels

c. three levels

 d. one level

Ans. c

27. In a relational model, relations are termed as

 a. tuples

 b. attributes

 c. tables

d. rows

Ans. c

 28. In the abstraction of a database system the external level is the

 a. physical level

 b. logical level

c. conceptual level

 d. view level

Ans. d

 29. Related fields in a database are grouped to form a

 a. data file

 b. data record

 c. menu

 d. bank

Ans. b

 30. A relational database developer refers to a record as

 a. criteria

 b. relation

c. tuple

d. attribute

Ans. b

31. An advantage of the database management approach is

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

Ans. c

32.Data independence means

a. data is defined separately and not included in programs

 b. programs are not dependent on the physical attributes of data

 c. programs are not dependent on the logical attributes of data

d. both (b) and (c)

Ans. d

 33. Key to represent relationship between tables is called

a. primary key

 b. candidate Key

 c. foreign Key

 d. alternate Key

Ans. c

 34. Which of the following operations is used if we are interested only in certain columns of a table?

 a. PROJECTION

 b. SELECTION

 c. UNION

d. SELECT

Ans. a

 35. Which of the following operations need the participating relations to be union compatible?

a. UNION

 b. INTERSECTION

 c. SET DIFFERENCE

 d. All of the above

Ans. d

36. Which database level is closest to the users?

 a. External

b. Internal

 c. Physical

 d. Conceptual

Ans. a

 37. The result of the UNION operation between R1 and R2 is a relation that includes

 a. all the tuples of R1

 b. all the tuples of R2

c. all the tuples of R1 and R2

d. all the tuples of R1 and R2 which have common columns

Ans. d

 38. A file manipulation command that extracts some of the records from a file is called

 a. SELECT

 b. PROJECT

 c. JOIN

d. PRODUCT

Ans. a

39. An instance of relational schema R (A, B, C) has distinct values of A including NULL values. Which one of the following is true?

 a. A is a candidate key

b. A is not a candidate key

 c. A is a primary Key

d. Both (a) and (c)

Ans.b

40. How many distinct tuples are there in a relation instance with cardinality 22?

 a. 22

 b. 11

 c. 1

 d. None

Ans. 22

41. A set of possible data values is called

 a. Attribute

b. Degree

c. Tuple

d. Domain

Ans. d

42. Explain the different levels of data abstraction in DBMS?

 The database description consists of a structure at each of these three levels of abstraction: the physical level, conceptual level and view level

a. Physical level

  The lowest level of abstraction describes how data is actually stored on secondary storage devices such as disks and tapes. The physical level describes complex lowlevel data structures in detail. We must decide what file organisations are to be used to store the relations and create auxiliary data structures, called indexes, to speed up data retrieval operations.

b. Logical level

  The next-higher level of abstraction describes what data is stored in the database, and what relationships exist among those data. The logical level thus describes the entire database in terms of a small number of relatively simple structures.Although mplementation of the simple structures at the logical level may involve complex physical-level structures, the user of the logical level does not need to be aware of this complexity. Database administrators, who must decide what information to keep in the database, use the logical level of abstraction. Logical level is also referred as conceptual level.

c. View level

 View level is the highest level of database abstraction and is the closest to the users. It is concerned with the way in which individual users view the data. It describes only a part of the entire database. Most of the users of the database are not concerned with all the information that is contained in the database. Instead they need only a part of the database that is relevant to them. This simplifies their interaction with the system.

43. How are schema layers related to the concepts of logical and physical data

Logical data independence refers to the ability to modify a conceptual schema without causing any changes in the schema followed at view (external) level. The logical data independence ensures that the application programs remain the same. It is more difficult to achieve logical data independence than physical data independence because the application programs are heavily dependent on the logical structure of the database.

Physical data independence refers to the ability to modify the schema followed at the physical level without affecting the schema followed at the conceptual level. That is, the application programs remain the same even though the schema at physical level gets modified.

44. What is a database? Describe the advantages and disadvantages of using DBMS.

  Database is an organized collection of inter-related data stored together with minimum redundancy, in a manner that makes them accessible for multiple applications

1. Controlling data redundancy:  In file management systems, data may be placed in many files. The storing of the same data in multiple locations (may be in the same file or different files) or duplication of data is known as data redundancy. Redundancy leads to higher cost in storage and data access. Database systems do not maintain redundant data, instead all the data is kept at one place in a centralized manner.

2.Data consistency: Data redundancy may lead to data inconsistency; that is, the various copies of the same data show different values in different files. Assume that your class teacher and Principal maintain separate copies of the address list of all students admitted in your class. During periodic address change a few students report to the Principal and a few students report to the changes to the class teacher. After a certain period of time both the address lists become irrelevant and inconsistent since total corrections are not updated in both. By controlling data redundancy, data consistency is obtained.

3.Efficient data access: A DBMS utilises a variety of techniques to store and retrieve data efficiently.

4. Data integrity: Data integrity refers to the overall completeness, accuracy and consistency of data in the database. This can be indicated by an absence of any alteration in data between two updates of a data record. Data integrity is imposed within a database at its design stage through the use of standard rules and procedures. Data integrity can be maintained through the use of error checking and validation routines.

5.Data security: The information inside a database is valuable to any company or organization. Therefore it must be kept secure and private. Data security refers to the protection of data against accidental or intentional disclosure or unauthorized destruction or modification by unauthorized persons.

6. Sharing of data: The data stored in the database can be shared among several users or programs even simultaneously and each may use it for different purposes.

7.Crash recovery: When a system crashes, all or a portion of the data can become unusable. DBMS provides some mechanism to recover data from the crashes. Thus the DBMS protects data from the effects of system failures.

8.Enforcement of standards: With central control of the database, a Database Administrator (DBA) defines and enforces the necessary standards. Standards can be defined for data formats to facilitate exchange of data between systems. Applicable standards might include naming conventions, display formats, report structures, terminology, documentation standards, update procedures, access rules and so on. This facilitates communication and cooperation among various departments, projects and users within the organization

45. What is data independence? Explain the difference between physical and logical data independence.

     A database may be viewed through three levels of abstraction, any change in the database structure at a particular level may affect the schema of other levels. The frequent changes made on database should not lead to the redesigning and reimplementation of the database. The ability to modify the schema definition (data structure definition) in one level without affecting the schema definition at the next higher level is called data independence. There are two levels of data independence, physical data independence and logical data independence.

a. Physical data independence:  Physical data independence refers to the ability to modify the schema followed at the physical level without affecting the schema followed at the conceptual level. That is, the application programs remain the same even though the schema at physical level gets modified.

 b. Logical data independence :Logical data independence refers to the ability to modify a conceptual schema without causing any changes in the schema followed at view (external) level. The logical data independence ensures that the application programs remain the same. It is more difficult to achieve logical data independence than physical data independence because the application programs are heavily dependent on the logical structure of the database.

46. Enforcement of standard is an essential feature of DBMS. How are these standards applicable in a database?

With central control of the database, a Database Administrator (DBA) defines and enforces the necessary standards. Standards can be defined for data formats to facilitate exchange of data between systems. Applicable standards might include naming conventions, display formats, report structures, terminology, documentation standards, update procedures, access rules and so on. This facilitates communication and cooperation among various departments, projects and users within the organization.

47. Cardinality of a table T1 is 10 and of table T2 is 8 and the two relations are union compatible. If the cardinality of result T1∪ T2 is 13, then what is the cardinality of T1∩ T2? Justify your answer.

 Ans. T1∪ T2  =T1+T2-T1∩ T2

           13= 10+8-T1∩ T2
            13=18- T1∩ T2
            T1∩ T2=18-13=5
48. Cardinality of a table T1 is 10 and of table T2 is 8 and the two relations are union compatible

 a. What will be the maximum possible cardinality of T1∪ T2?

b. What will be the minimum possible cardinality of T1∩ T2?

Read More