NATIONAL OPEN UNIVERSITY OF NIGERIA

UNIVERSITY VILLAGE, PLOT 91, CASDASTRAL ZONE, NNAMDI AZIKIWE EXPRESS WAY, JABI, ABUJA.
FACULTY OF SCIENCES
OCTOBER 2019 EXAMINATION

COURSE CODE: CIT843
COURSE TITLE: INTRODUCTION TO DATABASE MANAGEMENT SYSTEM
COURSE CREDIT: 2 UNITS
TIME ALLOWED: 2 HOURS
INSTRUCTION: ANSWER QUESTION 1 AND ANY OTHER THREE (3) QUESTIONS

1a) (i) Identify three (3) problems associated with multi-user database environment (3 marks)
(ii) What is a database? (2 marks)
b) (i) What is an ER diagram ? (2 marks)
(ii) Give five (5) properties of relational tables (5 marks)
c) What are the four (4) overhead costs that can be incurred for using a DBMS (4 marks)
d) Present the notation and function of the following SQL operations
(i) SELECT (ii) PROJECT (iii) INTERSECTION (6 marks)
e) When is a relational table said to be in:
(i) 1NF (ii) 2NF (iii) 3NF (3 marks)

2a) Distinguish between FIRST and COUNT aggregate functions in SQL, giving their syntax (4 marks)
b) Sketch a diagram to indicate the following concepts:
(i) an overlapping generalization hierarchy for COLOUR as supertype entity with RED, GREEN, and BLUE as subtypes. (3 marks)
(ii) a disjoint generalization hierarchy for VICE_CHANCELLOR as supertype entity with ACADEMIC STAFF and NASU_CHAIRMAN as subtypes (3 marks)
c) (i) Explain the concept of NULL values in relational database (2 marks)
(ii) Describe three (3) advantages of DBMS over file system (3 marks)

3a) (i) Explain the concept of normalisation in database (3 marks)
(ii) Distinguish between one-to-one and one-to-many connectivity for database relations (3 marks)
b) Using three appropriate examples each, analyse the term database management system (DBMS) (5 marks)
c) From the CAR table below, compose SQL relational algebra statements, with outputs to:
(i) Select cars whose manufacturer is TOYOTA (2 marks)
(ii) Select those cars with Camry model having price of less than 500000 (2 marks)
CAR
Serial_number
Model
Manufacturer
Price (N)’000
A651
CAMRY
TOYOTA
720
C439
VENTO
MERCEDES
250
B385
MUSTANG
FORD
950
D672
CAMRY
TOYOTA
350

4a) (i) What are surrogate keys in database tables? (3 marks)
(ii) Examine the process of transforming a relational table from 1NF to 2NF (4 marks)
b) (i) What is a database transaction (2 marks)
(ii) State whether the SALES table below is in 1NF. Give reasons for your answer (2 marks)
c) State what these SQL scalar functions perform
(i) UCASE ( ) (ii) LCASE ( ) (iii) MID ( ) (iv) NOW ( ) (4 marks)

SALES
Suppl_id
City_code
City
Part_number
Quantity
S1
10
Uyo
ZK1
15
S4
40
Lagos
BM5
10
S1
10
Uyo
ZK1
15
S2
20
Abuja
AB5
25
S2
20
Abuja
BM5
40
S3
20
Abuja
ZK1
30

5a) (i) Distinguish between relational algebra and relational calculus (4 marks)
(ii) What are stored procedures used for in database system (2 marks)
b) Examine the concept of deadlock and explain two (2) ways of handling it (4 marks)
c) Explain how SQL implements the following data types for its attributes
(i) Date (ii) Time (iii) Boolean (5 marks)