UNIVERSITY EXAMINATIONS: 2017/2018
EXAMINATION FOR THE DEGREE OF BACHELOR OF SCIENCE IN
INFORMATION TECHNOLOGY
BIT 1207A: PRINCIPLES OF DATABASE DESIGN
FULLTIME/PART TIME/DISTANCE LEARNING
DATE: DECEMBER, 2017 TIME: 2 HOURS
INSTRUCTIONS: Answer Question One & ANY OTHER TWO questions.
QUESTION ONE [30 MARKS]
a) Define a database (1 Marks)
b) Discuss the concept of data independence, explaining the importance in a database
environment (4 Marks)
c) Compare and contrast the three levels of the ANSI-SPARC model. Make use of a diagram
in your discussion (9 Marks)
d) The term integrity refers to the accuracy or correctness of the data in the database. Define
the following terms
i) Domain integrity
ii) Entity integrity
iii) Referential integrity (6 Marks)
e) Briefly discuss the three parts of a relational model (6 Marks)
f) Explain the following terms
i) Attribute
ii) Tuple (4 Marks)
QUESTION TWO
a) Consider the following relations for keeping track of customers and the products they
have purchased
Customers(custid, custname, age,address)
Products(prodid, prodname, manufacturer, price)
Buys(custid, prodid)
Write the following queries in SQL
i) Find the names of the customers who are younger than 18 years and who live in
Nakuru (3 Marks)
ii) Find the highest price among all the products (3 Marks)
iii) For the product manufactures by IBM, find the product id and the average age of
the customer who have bought the products (4 Marks)
iv) Write the statement to create the table Buys (4 Marks)
b) Explain the following database concepts:
i) view (in a relational database)
ii) multi-valued attribute
iii) transaction (6 Marks)
QUESTION THREE
The following form represents a view of data across more than one table
The above form represents one of a number for each sailing of the range of boats. A passenger
may go on many sailings and will have the same passenger number for each. The cruise fee
varies according to the sailing details (e.g date of sailing)
a) Define normalization (2 Marks)
b) Give the definitions of the first, second and the third normal forms (6 Marks)
c) Represent the form above as a relational schema and normalize to third normal form.
(12
Marks)
QUESTION FOUR
Modern database systems provide a mechanism for enforcing a password policy that has the
following features:
Complexity
Failed attempts
Expired passwords
Password reuse
a) Describe each of the above features and explain how they help protect the database.
(8 Marks)
b) Backups of the database should be taken in order to protect data. Describe five features of
a good backup strategy. (5 Marks)
c) Describe the role, and content, of the data dictionary (metadata) in a DBMS.
(4 Marks)
d) State THREE advantages of a database management system (3 Marks)
QUESTION FIVE
Study the following scenario then attempt the question parts that follow:-
Scenario AFC United
AFC United is a football club that host matches (also known as Fixtures) against an opposing
team. A database is required by the club to hold information to support the booking of seats to
watch a match at the club’s stadium over the course of a season.
Prior to the start of a season a set of fixtures are arranged between AFC United (the home team)
and an opposing team (the away team). Matches are watched by spectators who have registered
with the club. These are called PassHolders. PassHolders must book seats in advance for any of
the 20 home fixtures that season.
Once a Seat is booked (and payment made), the Pass Holder is issued with one or more Tickets.
A particular seat can have restricted occupancy. This is recorded as the seating type.
A PassHolder can purchases one or more tickets for seats for each fixture.
Important attributes (highlighted in bold font)
TicketNo This uniquely identifies which seat has been purchased on a particular date (the
Purchase Date) by a particular Passholder for attending a particular fixture.
FixtureID This attribute uniquely identifies a particular football match that is played on a
particular date (the Fixture date) between the home team and an opponent (called the away
team). All games are played at the same stadium – the home ground of ABC United.
AwayTeam a unique 4 character code identifies the Team. Examples are
CHEL,MANC,SUND,BORO
SeatID uniquely identifies a seat. Examples MM59,H105
SeatingArea is a seating area within the stadium. There are five distinct seating areas called
North, South, East, West and Upper West Stands.
SeatType: Over the course of a season a fixed number of seats have restricted and exclusive
occupancy. A restricted seat is recorded as one of four seat types Family; Reserved; Disabled;
Away Supporters.
PassHolderID identifies a particular Pass Holder of AFC United, a person who has registered
prior to purchasing seats. Passholder details such as name and contact details (PHAddress,
PhoneNumber) are recorded.
a) Derive an Entity Relationship data model for the scenario above showing :- The entity Types
PassHolders, Fixtures, Seats and Tickets and the Relationships between them.
Relationship constraints:
For each relationship show the degree (1:Many;many :many or 1:1) and participation
(mandatory or optional).
Attributes assigned to entity types using the listed attributes shown above in bold font. Underline
the primary key attributes
You may choose any modelling notation but you must state the notation you have used.
State any necessary assumptions you make, on the understanding they do not contradict the
scenario. (12 Marks)
b) Design a set of Tables, derived from your ERD and illustrate them with a small amount of
sample data. That is, data that represents all of the degrees of the relationships.
Note: You are not required to include data types or check constraints, but you must
specify all columns and state which are primary/foreign keys.
(8 Marks)
