UNIVERSITY OF COLOMBO, SRI LANKA

UNIVERSITY OF COLOMBO SCHOOL OF COMPUTING

DEGREE OF BACHELOR OF INFORMATION TECHNOLOGY (EXTERNAL)

Academic Year 2007/2008 – 1st Year Examination – Semester 2

IT2303 : Database Systems I

10th August, 2008

(TWO HOURS)

Important Instructions :

• The duration of the paper is 2 (two) hours.

• The medium of instruction and questions is English.

• The paper has 50 questions and 16 pages.

• All questions are of the MCQ (Multiple Choice Questions) type.

• All questions should be answered.

• Each question will have 5 (five) choices with one or more correct answers.

• All questions will carry equal marks.

• There will be a penalty for incorrect responses to discourage guessing.

• The mark given for a question will vary from 0 (All the incorrect choices are
marked & no correct choices are marked) to +1 (All the correct choices are
marked & no incorrect choices are marked).

• Answers should be marked on the special answer sheet provided.

• Note that questions appear on both sides of the paper.

If a page is not printed, please inform the supervisor immediately.

• Mark the correct choices on the question paper first and then transfer them
to the given answer sheet which will be machine marked. Please
completely read and follow the instructions given on the other side
of the answer sheet before you shade your correct choices.

1
1) A database management software (DBMS) is

(a) an application program that is used to provide information to users.

(b) a software application that is used to define, create, maintain and provide controlled access to
user databases.
(c) an automated tool (CASE) used to design databases and application programs.
(d) a database which contains occurrences of logically organized data or information.
(e) a repository of metadata, which is a central storehouse for all data definitions, data
relationships, screen and report formats and other system components.

2) A DBMS helps one to manage data better by

(a) providing a logical and orderly way to organize data.

(b) communicating directly with non-related data structures which may be shared by several
programs.
(c) allowing great versatility in selecting the data one wants by modifying, searching and
organizing it in various ways.
(d) providing many ways to format and print the data in reports.
(e) only creating and maintaining department specific file structures and programs.

3) Which of the following is/are true in relation to Data Management?

(a) Recovery of data in case of an accidental or purposeful damage to the data is part of data
management.
(b) Management of data is critical as data is a corporate resource.
(c) Authentication procedures of users are established using data management techniques.
(d) The database administrator is the only person who carries out all the data management tasks
of a database.
(e) Data Management provides procedures which specify how and when data is to be entered
into a system.

4) In addition to database management software, what is/are the other possible type(s) of software
which a database application will have ?

(a) Languages used for coding user interfaces and application programs
(b) Presentation software for documenting the design of the database
(c) Computer-aided software engineering tools (CASE)
(d) A fourth generation language such as Structured Query Language
(e) Tools to monitor performance, allowing a human expert to make the necessary adjustments
after reviewing the statistics collected

5) Which of the following is /are correct in relation to database applications?

(a) An enterprise database is intended to support organization-wide operations and decision-

making.
(b) An organization can have multiple databases which are separate departmental databases
or enterprise databases.
(c) Usually work group databases are larger than departmental databases.
(d) Work group databases are also called cooperate databases.
(e) A Data warehouse is an integrated decision support database whose content is derived
from the various operational databases.

2
6) Consider the following statements about three levels ANSI/SPARC architecture.

(i) External-Various user views exist and each of these views gives a user-oriented
description of the data elements and relationships of which the view is composed.
These views are defined using sub schema data definition language.
(ii) Logical- The result of the logical design which involves analysis of important needs of
users
(iii) Physical-The precise physical structure and the exact physical location of the database
which is defined by the database administrator using data definition language (DDL) or
statements of SQL

Which of the above statements is/are correct?

(a) (i)only (b) (iii) only (c) (i) and (ii) only
(d) (i) and (iii) only (e) All

7) Which of the following is/are correct with respect to entities and attributes?

(a) An entity may be an object with a physical existence like a car, a house or an
Employee.
(b) One cannot consider something which has conceptual existence like a course in a
degree program as an entity.
(c) Age can be considered as a single value attribute of a person.
(d) The attribute Date_of_birth can be considered as a derived attribute and similarly, the
Age can be considered as a stored attribute.
(e) An entity type describes the schema or intension for a set of entities which share the
same structure.

8) Which of the following is/are correct with respect to integrity rules?

(a) An Entity integrity constraint states that no primary key value can be null.
(b) The referential integrity constraint is specified between two relations.
(c) A foreign key cannot be used to refer to its own relation.
(d) The domain integrity constraints are used to specify the valid values which a column
defined over the domain can take.
(e) Referential integrity is used to maintain consistency among tuples in the relations.

9) Which of the following is/are not a basic operation in relational algebra?

(a) Selection (b) Projection (c) Join

(d) Intersection (e) Set Difference

10) Consider the following statements.

(i) CREATE keyword is only used to create a new table for an entity in a relation model.
(ii) TRUNCATE TABLE command will remove the data and the table.
(iii) The ALTER TABLE statement allows renaming an existing table which can also be used to
add, modify or drop a column from an existing table.

Which of the above statements is/are correct?

(a) (i) only

3
 (b) (ii) only
(c) (iii) only
(d) (i) and (iii) only
(e) (ii) and (iii) only
Based on the following relations, answer question 11-14
Sailors (Sid, Sname, rating, age)
Boats (Bid, Bname, color)
Reserves (Sid, Bid, date)

Note: The Symbol* denotes the natural join

11) Find the names of sailors who have reserved Bid 911.

(a) πSname((σBid = 911 Reserves) *Sailors)

(b) πSname((σBid = 911 Boats) *Reserves)
(c) σSname((πBid = 911 Reserves) * Sailors)
(d) σSname((πBid = 911 Boats)* Reserves)
(e) πSname((σBid = 911) Sailors*Boats)

12) Find the colours (colors) of boats reserved by ‘Wasantha’.

(a) πSname=‘Wasantha’ (Sailors)*((σcolor) Boats*Reserves)

(b) πSname=‘Wasantha’ (Sailors) ∪ ((σcolor) Boats*Reserves)
(c) πcolor ((σSname = ‘Wasantha’ Sailors) Reserves* Boats)
(d) πcolor ((σSname = ‘Wasantha’ Sailors) ∩ (Reserves*Boats ))
(e) πSname=‘Wasantha’ (Sailors) ∩ πcolor,Sid (Boats*Reserves)

13) Find the Sid’s of sailors with age over 20 who have not reserved a green boat

(a) πSid (σcolor=’green’ (Boats)*Reserves)- πSid (σage>20 (Sailors))

(b) πSid (σcolor=’green’ (Boats))- πSid (σage>20 (Sailors)*Reserves*Boats)
(c) πSid (σage>20 (Sailors))*Reserves*Sailors) – πSid (σcolor=’green’ (Boats))
(d) πSid (σage>20 (Sailors)) – πSid (σcolor=’green’ (Boats) * Reserves)
(e) πSid (σcolor=’green’ (Boats)) − πSid (σage>20 (Sailors))

14) Find the names of sailors who have reserved a red or green boat

(a) Result ßπSname((σcolor=’red’ (Boats)) ∪ (σcolor=’green’ (Boats)))*Sailors

(b) Temp ß((σcolor=’red’ (Boats)) ∪ (σcolor=’green’ (Boats)))
Result ßπSname(Temp*Reserves * Sailors)
(c) Result ßπSname((σcolor=’red’ (Boats)) ∩ (σcolor=’green’ (Boats)))* Sailors)
(d) Temp ß((σcolor=’red’ (Boats)) ∩ (σcolor=’green’ (Boats)))
Result ßπSname(Temp*Reserves*Sailors)
(e) Temp1 ß(((σcolor=’ red’ (Boats))*Reserves*Sailors))
Temp2 ß(((σcolor=’ green’ (Boats))*Reserves*Sailors))
ResultßπSname(Temp1∪Temp2)

15) Which of the following is/are correct with respect to SQL?

(a) The HAVING clause is used in a SELECT statement to filter the records which a GROUP
BY clause returns.

4
 (b) The IN operator assists one in providing multiple values in the WHERE clause.
(c) The COUNT returns the number of columns which match the given criteria.
(d) The JOIN keyword is used to query data from two tables.
(e) The DISTINCT keyword is used to find out how many unique values there are in a table.

16) Consider the following statements

(i) SQL is divided into two main categories; Data Definition Language (DDL) and Data
Manipulation Language (DML)
(ii) Data Definition Language (DDL) is used to insert, select, update and delete records in the
database.
(iii) CREATE TABLE is an example of Data Manipulation Language (DML).

Which of the above statements is/are correct?

(a) (i) only

(b) (ii) only
(c) (iii) only
(d) (ii) and (iii) only
(e) All

17) Which of the following is/are in correct SQL syntax with respect to creating a customers table that
stores customer ID, name and address? The customer ID should be the primary key of the table.

(a) CREATE TABLE CUSTOMERS

( customer_id integer (3) not null,
customer_name varchar(50) not null,
address varchar(50),
CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

(b) CREATE CUSTOMERS TABLE

(customer_id integer (3) not null,
customer_name varchar(50) not null,
address varchar(50),
PRIMARY KEY (customer_id)
);

(c) CREATE TABLE CUSTOMERS

(customer_name varchar(50) not null,
customer_id integer (3) not null,
address varchar(50),
PRIMARY KEY (customer_id)
);

(d) CREATE TABLE CUSTOMERS

(customer_id varchar (3) PRIMARY KEY,
address varchar(50) not null,
customer_name integer (50) not null
);

5
 (e) CREATE CUSTOMERS TABLE
(customer_id integer (3) not null,
customer_name varchar(50) not null,
address varchar(50),
PRIMARY KEY (customer_id) ON DELETE CASCADE
);

18) Consider the Department table below.

Assume that there is a departments table with the primary key department_id. How would you add a
foreign key to the employee table that references the department table based on the department_id
field. It should not be possible to delete a department tuple when there are employees working for it.
Which of the following SQL statements is/are in correct SQL syntax in order to achieve this ?

(a) ALTER EMPLOYEE TABLE

CONSTRAINT fk_departments FOREIGN KEY (department_id)
REFERENCES DEPARTMENTS ON DELETE RESTRICT ;

(b) ALTER TABLE EMPLOYEE

ADD CONSTRAINT fk_departments FOREIGN KEY (department_id)
REFERENCES DEPARTMENTS ON DELETE RESTRICT ;

(c) ALTER TABLE EMPLOYEE

ADD CONSTRAINT fk_departments FOREIGN KEY (department_id)
REFERENCES DEPARTMENTS ON DELETE CASCADE;

(d) ALTER TABLE EMPLOYEE

ADD FOREIGN KEY (department_id)
REFERENCES DEPARTMENTS ON DELETE RESTRICT;

(e) ALTER TABLE EMPLOYEE

FOREIGN KEY (department_id) REFERENCES DEPARTMENTS(department_id)
ON DELETE CASCADE;

19) Which of the following operators cannot be used with the WHERE clause ?

(a) = = (b) >= (c) BETWEEN

(d) LIKE (e) <>

Consider the following example of a table called "Persons" defined by figure 1 and answer
question 20 and 21.

EmpId FirstName LastName Address City

1 Ajith Jayasuriya No 24, Main Street Kandy
2 Thepul Ginige No 46, First Lane Ambalangoda Figure-1
3 Kapila Dias “Jayasiri”, Second Cross Lane Maharagama
4 Jeevani Goonathillake 6A, Flower road Nugegoda
5 Anuradha Gunathilake No 254/3A,Galle Road Dehiwala
6 Kanchana Welivitiya 389A, Jaya Road Borella

6
 FirstName LastName City FirstName LastName Address
Thepul Ginige Ambalangoda Thepul Ginige No 46, First Lane
Kanchana welivitiya Borella Jeevani Goonathillake 6A, Flower road
Anuradha Gunathilake Dehiwella Anuradha Gunathilake No 254/3A,Galle Road
Ajith Jayasuriya Kandy Figure-3
Kapila Dias Maharagama
Jeevani Goonathillake Nugegoda

Figure-2

20) Which of the following SQL statements is/are in correct SQL syntax in order to generate an output
from figure-1 to figure-2?

(a) SELECT * FROM employee;

(b) SELECT FirstName,LastName,City FROM employee;
(c) SELECT FirstName,LastName,City FROM employee;
ORDER BY FirstName;
(d) SELECT FirstName,LastName,City FROM employee;
ORDER BY LastName;
(e) SELECT FirstName,LastName,City FROM employee
ORDER BY City;

21) Which of the following SQL statements is/are in correct SQL syntax in order to generate an output
from figure-1 to figure-3?

(a) SELECT FirstName,LastName,Address FROM employee

WHERE Address LIKE "%E%";
(b) SELECT FirstName,LastName,Address FROM employee
WHERE LastName LIKE "%G%"AND Address LIKE "%L%";
(c) SELECT FirstName,LastName,Address FROM employee
WHERE FirstName LIKE "%T%"AND Address LIKE "%E%";
(d) SELECT FirstName,LastName,Address FROM employee
WHERE LastName LIKE "%E%" AND Address LIKE "%A%";
(e) SELECT FirstName,LastName, Address FROM employee
WHERE LastName LIKE "%N%"AND Address LIKE "%E%";

22) Consider the three relations given below.

Participants(pno,name,address,telephone,email)
Categories(code,category_name,description)
Winners(pno,code,year,award)

Which of the following SQL-query/queries can be used to retrieve all Gold award winners with
each winner’s name, category and year?

(a) SELECT p.name, c.category_name

FROM Participants p, Categories c, Winners w
WHERE p.pno=w.pno and c.code=w.code
IN
(SELECT w.year
FROM Winners w
WHERE w.award="Gold");

7
 (b) SELECT p.name, c.category_name, w.year
FROM Participants p, Categories c, Winners w
WHERE p.pno=w.pno and c.code=w.code and award="Gold";

(c) SELECT p.name,c.category_name

FROM Participants p, Categories c, Winners w
WHERE p.pno=w.pno and c.code=w.code
EXISTS
(SELECT w.year
FROM Winners w
WHERE w.award="Gold");

(d) SELECT p.name, c.category_name,w.year

FROM Participants p, Categories c, Winners w
WHERE p.pno=w.pno OR c.code=w.code OR award="Gold";

(e) SELECT p.name, c.category_name, w.year

FROM Participants p, Categories c, Winners w
WHERE p.pno=w.pno UNION c.code=w.code UNION award="Gold";

Consider the following two tables and answer question 23 and 24

Store_Sales Internet_Sales
store_name Sales Date Date Sales
Colombo Rs450,000 Jan-01-2008 Jan-01-2008 Rs50,000
Kandy Rs250,000 Jan-03-2008 Jan-04-2008 Rs35,000
Colombo Rs375,000 Jan-05-2008 Jan-05-2008 Rs15,000
Kandy Rs150,000 Jan-08-2008 Jan-07-2008 Rs25,000

23) The following incomplete SQL statement is written to find out all the dates where there are both
store_sales and internet_sales.

SELECT Date FROM Store_Sales

…………… à (A)
SELECT Date FROM Internet_Sales

Which of the following SQL keywords is correct to fill the above blank in (A)?

(a) UNION (b) UNION ALL (c) INTERSECT

(d) MINUS (e) ADD

24) Which of the following SQL statements is/are in correct SQL syntax in order to find overall sales
of each store, where the total sale is more than Rs 500,000?

8
 (a) SELECT store_name , SUM (Sales) (b) SELECT store_name , SUM (Sales)
FROM Store_Sales FROM Store_Sales
HAVING SUM(Sales) > 500,000; GROUP BY store_name
HAVING SUM(Sales) > 500,000;

(c) SELECT store_name , SUM (Sales) (d) SELECT store_name , SUM (Sales)
FROM Store_Sales FROM Store_Sales
WHERE Sales > 500,000; WHERE Sales > 500,000
GROUP BY store_name;

(e) SELECT DISTINCT store_name , SUM (Sales)

FROM Store_Sales
WHERE Sales > 500,000;
GROUP BY store_name;

Based on the following three relations, answer question 25-28

Sailors (Sid, Sname, rating, age)

Boats (Bid, Bname, color)
Reserves (Sid, Bid, date)

25) Which of the following SQL statements is/are in correct SQL syntax in order to find the names of
sailors who have reserved Bid 911?

(a) SELECT Sname

FROM Sailors
WHERE Bid=”911”;

(b) SELECT S.Sname

FROM Sailors S, Boats B
WHERE B.Bid=”911”;

(c) SELECT S.Sname

FROM Sailors S, Reserves R
WHERE S.Sid=R.Sid AND R.Bid=”911”;

(d) SELECT S.Sname

FROM Sailors S, Reserves R, Boats B
WHERE S.Sid=R.Sid AND B.Bid=”911”;

(e) SELECT S.Sname

FROM Sailors S, Reserves R, Boats B
WHERE S.Sid=R.Sid AND R.Bid=”911”;

26) Which of the following SQL statements is/are in correct SQL syntax in order to find the colors of
boats reserved by ‘Wasantha’?

9
 (a) SELECT B.color
FROM Sailors S, Reserves R, Boats B
WHERE S.Sid=R.Sid AND R.Bid=B.Bid AND S.Sname="Wasantha”;

(b) SELECT B.color

FROM Reserves R, Boats B, Sailors S
WHERE R.Bid=B.Bid AND S.Sname="Wasantha”;

(c) SELECT B.color

FROM Sailors S, Boats B
WHERE S.Sid= B.Bid AND S.Sname="Wasantha”;

(d) SELECT B.color

FROM Sailors S, Reserves R, Boats B
WHERE S.Sid=R.Sid AND R.Bid=B.Bid AND S.Sname LIKE "%W”;

(e) SELECT B.color

FROM Reserves R, Boats B, Sailors S
WHERE R.Bid=B.Bid AND S.Sname LIKE "%W”;

27) Which of the following SQL statements is/are in correct SQL syntax in order to find the names of
sailors who have reserved a red or green boat?

(a) SELECT S.Sname

FROM Sailors S, Boats B
WHERE B.color=”red” OR B.color=”green”;

(b) SELECT S.Sname

FROM Sailors S, Boats B
WHERE B.color=”red” AND B.color=”green”;

(c) SELECT S.Sname

FROM Sailors S, Reserves R, Boats B
WHERE S.Sid=R.Sid AND R.Bid=B.Bid
AND B.color=”red” AND B.color=”green”;

(d) SELECT S.Sname

FROM Sailors S, Reserves R, Boats B
WHERE S.Sid=R.Sid AND R.Bid=B.Bid
AND (B.color=”red” OR B.color=”green”);

(e) SELECT S.Sname

FROM Sailors S, Reserves R, Boats B
WHERE S.Sid=R.Sid AND R.Bid=B.Bid
AND (B.color LIKE “%E%”);

28) Consider the following SQL statement.

SELECT S.Sname
FROM Sailors S
WHERE NOT EXISTS

10
 ((SELECT B.Bid
FROM Boats B)
EXCEPT
(SELECT R.Bid
FROM Reserves R
WHERE R.Sid=S.Sid) );

Which of the following is/are in correct with respect to the output of above statement?

(a) Names of sailors and Bid’s of the reserved Boats

(b) Bid’s of Boats which have not been reserved
(c) Names of sailors each of whom not reserved some Boats.
(d) Names of sailors each of whom reserved all Boats.
(e) Names of sailors each of whom not reserved any Boats.

29) Consider the following statements.

(i) Views are considered as virtual tables.
(ii) A view has a set of definitions, which is built on top of (a) table(s) or other view(s).
(iii) A view does not physically store data.

Which of the above statements is/are correct with respect to VIEWS in SQL?

(a) (i) only. (b) (ii) only. (c ) (i) and (ii) only.
(d) (ii) and (iii) only. (e) All.
30) Which of the following is a GRANT/REVOKE privilege(s) for a user of a table ?

(a) Select (b) Update (c) References

(d) Alter (e) Index

31) Consider the two tables, Department and Employee. If many employees are working for a
department and only one employee can work for one and only one department, in which table
should the corresponding foreign key be placed?
(a) Foreign key needed only in Department table
(b) Foreign key needed only in Employee table
(c) Foreign key needed in both tables
(d) A new table has to be defined including the primary keys of both Employee and
Department tables.
(e) No foreign keys should be used.

32) Consider the following two tables.

Store_Sales Region _Sales
store_name Sales Date region_name store_name
Colombo Rs450,000 Jan-01-2008 West Colombo
Kandy Rs250,000 Jan-03-2008 Central Kandy
Colombo Rs375,000 Jan-05-2008 West Colombo
Matara Rs150,000 Jan-08-2008 South Matara

Which of the following SQL statements is/are in correct SQL syntax in creating a view to store
overall sales by region?

11
 (a) CREATE VIEW REGION_SALES (REGION, SALES) AS
SELECT R.region_name , S.Sales
FROM Region _Sales R, Store_Sales S
WHERE R.store_name = S.store_name
GROUP BY R.region_name;

(b) CREATE VIEW REGION_SALES (REGION, SALES) AS

SELECT R.region_name, SUM(S.Sales)
FROM Region _Sales R, Store_Sales S
WHERE R.store_name = S.store_name
GROUP BY R.region_name;

(c) CREATE VIEW REGION_SALES (REGION, SALES) AS

SELECT DISTINCT R.region_name, SUM(S.Sales)
FROM Region _Sales R, Store_Sales S
WHERE R.store_name = S.store_name
GROUP BY R.region_name;

(d) CREATE VIEW REGION_SALES (REGION, SALES) AS

SELECT R.region_name, S.Sales
FROM Region _Sales R, Store_Sales S
WHERE R.store_name = S.store_name
GROUP BY S.store_name;

(e) CREATE VIEW REGION_SALES (REGION, SALES) AS

SELECT R.region_name, SUM(S.Sales)
FROM Region _Sales R, Store_Sales S
WHERE R.store_name = S.store_name
GROUP BY S.store_name;
Based on the following relations diagram, answer question 33-36

Customer
OrderTbl
OrdLine
Product

Employee

33) If a new customer calls for the first time and places an order for 5 different products (all with
distinct ProdNo values), then how many rows are needed to be added to the database to store the
required information about this customer and the relevant order?

(a) One (b) Two (c) Five

(d) Six (e) Seven

12
34) Consider the following incomplete statement.
For a specific row in the OrdLine table, there should be ____(i)____ corresponding row(s) in the
OrderTbl table, and ___(ii)____ corresponding row(s) in the Product table.

Which of the following is/are correct with respect to filling the above blanks ?

(a) (i) One, (ii) One (b) (i) One, (ii) Many (c) (i) Many, (ii) One
(d) (i) One, (ii) Many (e) (i) One, (ii) No

35) Which of the following is/are required to be inserted as a new entry to ‘OrderTbl’ table ?

(a) CustNo should exist in the ‘Customer’ table.

(b) EmpNo should exist in the ‘Employee’ table.
(c) OrdNo should exist in the ‘OrdLine’ table.
(d) ProdNo should exist in the ‘OrdLine’ table.
(e) ProdNo should exist in the ‘Product’ table.

36) Which of the following is/are correct when a command is given to delete a row from ‘OrderTbl’
table ?

(a) It will delete a row from ‘OrderTbl’ table and the relevant row(s) from ‘OrdLine’ table.

(b) It will delete a row from ‘OrderTbl’ table and the relevant row from ‘Product’ table.
(c) It will delete a row from ‘OrderTbl’ table and the relevant row from ‘Employee’ table.
(d) It will delete a row from ‘OrderTbl’ table, the relevant row(s) from ‘OrdLine’ table and
the relevant row from ‘Product’ table.
(e) It will delete a row from ‘OrderTbl’ table, the relevant row(s) from ‘OrdLine’ table and
the relevant row from ‘Employee’ table.

37) Consider the following statements about mapping associative entities in an Entity Relationship
Diagram (ERD) to Relations.
(i) Create a relation for the two entity types participating in the relationships.
(ii) Create a new associative relation for the associative entity and include primary key of each of
the two participating entity types as foreign key.
(iii) The relational model does not directly support mapping associative entities in an ERD to
Relations.

Which of the above statements is/are correct?

(a) (i) only (b) (ii) only (c) (iii) only

(d) (i) and (ii) only (e) None.

38) Consider the following diagram:

13
 Which is a/are correct statement(s) with respect to information in the above diagram?
(a) Name is a multi-valued attribute.
(b) Employee is week entity.
(c) Age is a derived attribute.
(d) Phone is a composite attribute.
(e) Supervision is a Unary Relationship.

39) Consider the following ERD diagram illustrating the relationship between employees and projects.

Select from among the following, candidates for relations, if the above ERD is mapped into a
relational model.
(a) Employee(Emp_No, First_Name, Mid_Initials, Last_Name)
(b) Employee(Emp_No,Name)
(c) Project(Proj_ No, Proj_Name)
(d) Works_On(Emp_No, Proj_No,Hours)
(e) Works_On(Hours)

40) Which of the following statements is/are true with respect to data types?
(a) VARCHAR data type often reduces disk storage wastage when compared to CHAR data
type.
(b) BLOB data is a set of streams of bytes of fixed length.
(c) BYTE data type cannot store any type of binary data.

14
 (d) Both INTEGER and FLOAT are Numeric data types.
(e) The DATE type has eight positions.

Use the following schema to answer the questions 41 and 42.

Employee
(EmpNo,EmpName,{Telephone(Home,Mobile)},{Project(ProNo,ProName,ProHours)},NIC)
Note: NF denotes Normal Form

41) Which of the following statements is/are correct with respect to1NF of the above schema?
(a) The above schema is already in 1NF.
(b) Employee (EmpNo,EmpName, NIC)
Telephone (Home,Mobile)
Project (ProNo,ProName,ProHours)
(c) Employee (EmpNo,EmpName, NIC)
Telephone (EmpNo, Home,Mobile)
Project (EmpNo, ProNo,ProName,ProHours)
(d) Employee (EmpNo,EmpName, Telephone,
{Project(ProNo,ProName,ProHours)},NIC)
(e) The above schema has completed the 1NF requirements and now it is in 2NF

42) Which of the following statements is/are correct with 2NF of above schema?
(a) The above schema is already in 2NF.
(b) Employee (EmpNo,EmpName, NIC)
Telephone (EmpNo,Home,Mobile)
Project1 (EmpNo, ProNo)
Project2 (ProNo, ProName,ProHours)

(c) Emp (EmpNo,EmpName, NIC)

Telephone (EmpNo ,Home,Mobile)
Project1 (EmpNo, ProNo,ProName,ProHours)
Project2 (ProNo, ProName,ProHours)

(d) Employee (EmpNo,EmpName,{ Telephone (Home,Mobile)},

{Project(ProNo,ProName,ProHours)},NIC)

(e) The above schema has completed the 2NF requirements and now it is in 3NF.

43) Which of the following is/are correct with respect to normalization ?

(a) 0NF contains nested groups.

(b) 1NF contains multi values and no repeating groups.
(c) 2NF does not contain multi values and contains partial dependency.
(d) 3NF does not contain any transitive dependence.
(e) Demoralization produces a lower normal form.

44) Which of the following statements is/are true with respect to embedded SQL?

15
 (a) It writes an SQL statement to retrieve data from more than one relation
(b) Static and dynamic are the two types of embedded SQL.
(c) It does not have flag statements to signal the beginning or end of a set of SQL statements
in application programs
(d) It specifies a condition and action to be taken in case the given condition is satisfied.
(e) Embedded SQL has cursor facility where the result of an SQL query is stored for
subsequent processing.

45) Which of the following statements is/are true with respect to triggers?

(a) Trigger is a program which automatically executes when a query is attempted on a

specified table.
(b) For a trigger, there is an ‘action’ that should be taken.
(c) The ‘action’ for a trigger has to always be an update operation.
(d) It is a rule which is used to structure the relations to eliminates anomalies .
(e) It is a technique for specifying active rules.

46) Triggers can be used to

(a) alert users to reflect an unusual events in the database update.

(b) generate a log of events and support auditing and security.
(c) gather statistics on table access and modification.
(d) reduce database integrity in a more flexible manner.
(e) manage workflow and to enforce business rules.

47) Which of the following statements is/are true with Stored procedures?

(a) Stored procedures are SQL programs which are compiled the first time they are executed and
then stored for later use.
(b) Stored procedures can get executed very fast as they have already been compiled.
(c) Stored procedures are part of the DBMS that defines the structure of user data and how they
are to be used.
(d) A stored procedure is a named group of SQL statements which has been previously created
and stored in the server database.
(e) Stored procedures increase network traffic since they would be used over the network by
several clients.

48) Which of the following statements is/are true with respect to ODBC?

(a) It is a standard definition of an application-programming interface (API) which used to

access data in relational databases.
(b) It defines a call-level interface which is defined as a set of function calls and their
associated parameters.
(c) Driver Manager calls ODBC functions to communicate with an ODBC data source,
submits SQL statements and processes result sets.

16
 (d) It enables SQL statements to be incorporated into the application allowing the application
to retrieve and update values from a database.
(e) It is a driver that contains all information to access a specific instance of data in a DBMS.

49) Consider the following statements about Data warehousing:

(i) Data warehousing is combining data from multiple and usually varied sources into
one comprehensive and easily manipulated database.
(ii) Companies use this technique to analyse trends over time.
(iii) Common accessing systems of data warehousing include analysis and reporting and it
does not include queries.

Which of the above statements is/are correct?

(a) (i) and (ii) only (b) (i) and (iii) Only (c) (ii) and (iii) only
(d) (i) only (e) All

50) Which of the following statements is/are true with respect to a distributed database?

(a) It is a database that consists of two or more data files located at different sites on a
computer network.
(b) Different users can access it without interfering with one another.
(c) The DBMS must periodically synchronize the scattered databases to make sure that they
all have consistent data.
(d) A distributed database allows faster local queries and can reduce network traffic.
(e) A fault in one database system will affect the entire database.

********

17