UNIVERSITY EXAMINATIONS: 2012/2013
SECOND YEAR EXAMINATION FOR THE BACHELOR OF
SCIENCE IN INFORMATION TECHNOLOGY
BIT 2104 DATABASE DESIGN AND DEVELOPMENT
DATE: DECEMBER, 2012 TIME: 2 HOURS
INSTRUCTIONS: Answer Question ONE and any other TWO
QUESTION ONE
a) Define the following terms, giving an example in each case
i) Database (2 Marks)
ii) Database system (2 Marks)
iii) Database Management System (2 Marks)
b) We want to represent information about products in a database. Each product has
a description, a price and a supplier. Suppliers have addresses, phone numbers,
and names. Each address is made up of a street address, a city, and a postcode.
Draw an entity relationship diagram for the above information (6 Marks)
c) Outline the four categories of database users (4 Marks)
d) Explain what a data dictionary is, and give three examples of how one may be
used in a relational database management system. (5 Marks)
e) Using an example in each case explain the three ways you can refer to a table
(6 Marks)
f) State the three types of relationships used in database design (3 Marks)
i) One to one
ii) One to many
iii) Many to many
QUESTION TWO
a) Explain the concept of the master databases (6 Marks)
b) Write a SQL code for creating a database called NationalCensus and log file
called NationalCensus (4 Marks)
c) What is the basic formula to programmatically deny one or more permissions
(4 Marks)
d) What is the purpose of the following operators
i) Select (1 Mark)
ii) Unary (1 Mark)
iii) Binary (1 Mark)
e) Elaborate on the three variants of creating a function in Transact-SQ (3 Marks)
QUESTION THREE
a) i) Which are the rules you would use in naming variables in SQL server
(4 Marks)
ii) Write the SQL code for initializing a variable (2 Marks)
b) Explain the output that will be given by the following statements (4 Marks)
DECLARE @EmplStatus int,
@IsMarried bit,
@WeeklyHours Decimal(6,2),
@HourlySalary SmallMoney,
@WeeklySalary SmallMoney;
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @EmplStatus AS [Empl Status],
@IsMarried AS [Married?],
@WeeklyHours AS Hours,
@HourlySalary AS Hourly,
@WeeklySalary AS Weekly;
GO
c) Examine the following conditional statements as used in transact SQL
i) IF (2 Marks)
ii) CASE…WHEN…THEN (3 Marks)
iii) WHILE (3 Marks)
d) What is the purpose of the NOT Operator (2 Marks)
QUESTION FOUR
a) Explain using an appropriate syntax how you would call a function (4 Marks)
b) Define the term macro (2 Marks)
c) Explain the purpose of the following statement (4 Marks)
d) Exception handling is the ability to deal with errors that occur or can occur on a
database. The error is called an exception. Explain the general formula provided by
transact-SQL (4 Marks)
e)
i) Elaborate on the functionalities provided by a stored procedure (4 Marks)
ii) What is the purpose of a trigger (2 Marks)
QUESTION FIVE
a) Using an example explain the simplest syntax to create a stored procedure in
Transact-SQL (4 Marks)
b) Explain using a relevant example the meaning of a Parameterized Function
(6 Marks)
c) What is a run time error? Give any four examples of such an error (6 Marks)
d) Examine the properties of a transaction (4 Marks)
