UNIVERSITY EXAMINATIONS: 2014/2015
ORDINARY EXAMINATION FOR THE BACHELOR OF SCIENCE
IN INFORMATION TECHNOLOGY
BIT 2104 DATABASE DESIGN AND DEVELOPMENT
DATE: APRIL, 2015 TIME: 2 HOURS
INSTRUCTIONS: Answer Question ONE and any other TWO
QUESTION ONE [30 marks]
(a) Explain what is meant by a transaction and why it is an important unit of
operation in a DBMS? (4 marks)
(b) The consistency and reliability aspects of transactions are due to their “ACID”
properties. Discuss each of these properties and how they relate to the
concurrency control and recovery mechanisms in a database. (12 marks)
(c) Describe, using an example, one type of problem that can occur in a multi-user
environment when concurrent access to the database is allowed. (5 marks)
d) The Northstar Hotel’s website allows potential guests to make a room reservation,
specifying the dates and type of room. If they have registered with the website
previously their stored details are used to speed up the process, otherwise they are
required to register as a new customer. Each reservation is given a unique
reservation code. Before the date of their stay they may enter this reservation code
into the website to amend or cancel the reservation. Amendments can include
altering the dates, changing the room type or the number of guests in each room.
When the guests arrive at the hotel the reservation id is used by the receptionist to
quickly find the reservation to check them in with. At the end of their stay the
receptionist checks the guests out, at this point the hotel system validates their
payment through the card payment system; a printed invoice may be requested by
the guest at this point. The hotel has many room types available, each with a
room-type name, number of guests and additional facility information. Each room
in the hotel has a room number and is of one specific type. The maintenance of
this data is performed using a separate system out of scope of this exercise.
Monthly reports are prepared by the system which may be viewed on request by
the Hotel Manager.
Required
Produce a System Use Case diagram for the above scenario. (9 marks)
QUESTION TWO [20 marks]
(a) Describe the various interfaces, tools and techniques that a developer and DBA
may employ when interacting with a database. (10 Marks)
(b) Explain what the term data validation means (in the sense of checking the quality
and accuracy of inputted data). Using your own examples, describe the various
data validation techniques that may be embedded into a forms based interface to a
database – for example, ensuring that the correct type and range of data values are
entered. (10 Marks)
QUESTION THREE [20 marks]
(a) Explain the phrase ‘three-tier architecture’ with respect to the following two
distinct systems:
(i) Databases and the ANSI-SPARC model (4 Marks)
(ii) Databases and the Web (4 Marks)
(b) Explain, using your own examples, how the following database concepts are
implemented and how they relate to each other:
(i) Data integrity (4 Marks)
(ii) Data validation (4 Marks)
(iii) Data security (4 Marks)
QUESTION FOUR [20 marks]
a) Examine the following ERD (using UML notation) which is used to model the
voyages of ships and their movement during a voyage from port to port. Then answer
the question parts that follow.
i) With reference to the ER model above, explain the concept of relationship participation
involving the Visits relationship and the Employs relationship. (5 Marks)
ii) Identify and explain the difference between a weak entity type and a strong entity type.
(4 Marks)
iii) Explain how you would assign the following attributes to the appropriate
relationships in the ER model
DestinationPort,
VisitStartDate
VisitEndDate
VoyageStartDate (4 Marks)
iv) d) Explain how you would translate the ERD above to an equivalent relational model.
Identify the primary keys and foreign keys for each relation. (hint remember to
reconcile the many to many relationships and assign attributes to appropriate
relations) (7 Marks)
QUESTION FIVE [20 marks]
a) The table below records orders for items. Each order is placed on a given date,
and may include a variety of items in different quantities.
(i) Give an example of an insertion anomaly and an example of modification
(update) anomaly that might occur in the above table. (4 Marks)
(ii) Explain why this table is not in 2nd Normal Form. (3 Marks)
(iii) Transform the table into 2nd Normal Form (6 Marks)
b) A student table has the following information
a) Write the SQL command to create the table. (1 Mark)
b) Write the SQL command to list all students in Kisumu campus.(3 Marks)
c) Write the SQL command to list all students born before 1980 with a fee
balance of 6000. (3 Marks)
