MURANG’A UNIVERSITY OF TECHNOLOGY
SCHOOL OF COMPUTING AND INFORMATION
TECHNOLOGY
DEPARTMENT OF INFORMATION TECHNOLOGY
2023/2024 ACADEMIC YEAR
FIRST YEAR, FIRST SEMESTER EXAMINATION FOR MASTER OF
SCIENCE IN INFORMATION TECHNOLOGY
SIT 603: DATABASE DESIGN AND MANAGEMENT
DURATION: 3 HOURS
INSTRUCTIONS TO CANDIDATES:
1. Answer ANY FOUR questions.
2. Mobile phones are not allowed in the examination room.
3. You are not allowed to write on this examination question paper.
1
QUESTION ONE (25 MARKS)
a) Define the following concepts and provide an example for each
i. Attribute (2 marks)
ii. Tuple (2 marks)
iii. One-to-many relationship (2 marks)
iv. Functional dependency (2 marks)
b) A fundamental feature of a Database Management System (DBMs) is to support data
independence. Using examples, explain how physical and logical independence is
achieved in the three level ANSI SPARC database architecture. Discuss briefly why one
of the two types of data dependence is harder to achieve than the other type. (8 marks)
c) A data dictionary holds information that is crucial for the correct operation of a database.
If the data dictionary is lost, the database cannot operate. Describe the contents of a
typical data dictionary. (5 marks)
d) Use relevant examples to distinguish between referential integrity and entity integrity.
(4 marks)
QUESTION TWO (25 MARKS)
a) Consider the following scenario for a garage selling and servicing cars and answer
questions that follow.
The following rules describe the garage in more details:
A customer engages with the garage either as a buyer by having their car(s) serviced
(or both)
A sales person may sell many cars, but each car is sold by only one salesperson
Each salesperson or mechanic has a first name and last name
Cars have a number plate, make and model as well as registration year.
Customers have a first and last name as well as an address
A customer may buy many cars, but each car is bought by only one customer. A sale
takes place on specific date
A salesperson writes a single invoice for each car he or she sells
A customer receives an invoice for each car he or she buys
2
When a customer takes one or more cars in for repair or service, one service ticket is
written for each car. A service takes place on a specific date and customer receives
comments on what has been done
A car brought in for service can be worked on by many mechanics, and each
mechanic may work on many cars. Each mechanic spends a certain amount of time on
a job called an assignment, and has an associated rate
Required:
Draw an entity relationship diagram for car dealership scenario. Your answer must show the
entities and their relationships. Cardinalities and optionality between entities must be shown.
State any additional assumptions you make. (15 marks)
b) Explain how data is physically stored and accessed in the following approaches
i. Relational databases (5 marks)
ii. Object-oriented databases (5marks)
QUESTION THREE (25 MARKS)
a) Explain the concept of checkpoints and describe, using a timeline diagram, how
checkpoints are used to recover a database following failure of a transaction sequence.
(7 marks)
b) Briefly describe how databases can recover from the following types of failure:
i. System failure (loss of entire machine/servers). Often due to power outage. (4 marks)
ii. Catastrophic failure such as loss of entire installation/building. (4 marks)
c) Transactions that apply the two phase locking (2PL) protocol can be affected by a
situation known as deadlock.
i. Using an example, explain how deadlock can occur. (2 marks)
ii. Briefly explain how a deadlock situation can be resolved. (2 marks)
d) Describe the three security measures and precautions that a DBA (Database
administrator) must have in place to prevent the database from being subject to hacking,
for example, someone trying to steal data. (6 marks)
3
QUESTION FOUR (25 MARKS)
a) A company sells garden furniture. It has decided to create a relational database. At first,
incomplete design includes two tables PRODUCT and ORDER
PRODUCT (ProductID, Producttype, size, price, ….)
ORDER (OrderID, OrderDate, ProductID, …)
For example, the product which has ProductID 12345 is a large bench which has a price
of Ksh. 1500
i. State one additional piece of data which should be included in PRODUCT and give
one reason why it is needed. (2 marks)
ii. A customer table is added. An entity-relationship (E-R) diagram is shown
PRODUCT ORDER CUSTOMER
Explain why this design would be inefficient for customer. (3 marks)
iii. Some of the structured query language for this database is
SELECT surname, title, PhoneNo
FROM CUSTOMER
WHERE Town = ‘Coventry’
ORDER BY Surname
Describe the purpose of this code and give one situation in which it may be used. (6 marks)
b) Every bank account has an account number sort code. The sort code identifies the bank
branch (location of the bank) with which the account is held and the account number
uniquely identifies the bank account. An extract from the bank’s database table is as
shown below
Customer ID First name Surname Acc. No Sort code Branch
name
145204 Elaine Kyalo 14725200 67-54-56 Thika
657875 Jordan Ouma 62703441 67-45-67 Juja
735951 Monim Njeri 96385547 67-00-11 Ruiru
744078 Tom Mwangi 45623929 67-00-11 Ruiru
i. Explain why the above table is not in the third normal form. (2 marks)
ii. Describe how the database could be put into third normal. (6 marks)
iii. Write an SQL command to create a table student (RNo, name, marks, dept.) with
proper data types and RNo as primary key. (6 marks)
4
QUESTION FIVE (25 MARKS)
a) It is widely acknowledged that relational database management systems cannot always
support the rapid growth in data storage. Give brief comments on why this view is often
taken. (5 marks)
b) One method of storing big data is within key-value pairs. Explain key-value pairs and
give one scenario in which the use of key-value pairs would be applicable. (5 marks)
c) Explain the following three characteristics of Brewer’s CAP theorem and show how they
relate to big data management.
i. Consistency (3 marks)
ii. Availability (3 marks)
iii. Partition tolerance (3 marks)
d) Discuss any three challenges for data processing and storage management in big data.
(6 marks)
