Section A
Question One
a) Define the following terms:
i. A candidate key
ii. A superkey
b) Explain the differences between the notions of Database (DB), Database Management System (DBMS) and Database System (DBS).
c) List at least four responsibilities of a database management system. For each responsibility, explain the problems that would arise if the responsibility were not discharged, If these responsibilities were not met by a given DBM, what problems can Occur?
d) Explain the difference between a weak and a strong entity set.
e) We can convert any weak entity set to a strong entity set by simply adding appropriate attributes. Why, then, do we have weak entity sets?
f) What is the meaning of the term 'functional dependency'? Why is it important to establish functional dependencies during normalization?
Section B
Question Two
a) A Maseno university registrar's office maintains data about the following entities: (a) courses, including number, title, credits, syllabus, and prerequisites; (b) course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; (c) students, including student-id, name, and program; and (d) instructors, including identification number, name, department, and title. Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled.
i. Construct an E-R diagram for the registrar's office.
ii. Document all assumptions that you make about the mapping constraints.
b) In your own word explain how data integrity is maintained within the database when concurrent users access the database
Question Three
Consider the insurance database, where the primary keys are underlined. Construct the following SQL queries for this relational database.
person (driver-id. name, address)
car (license, model, year)
accident (report-number, date location)
owns (driver-id, license)
participated (driver-id. car. report-number, damage-amount)
a) Find the total number of people who owned cars that were involved in accidents in 2014.
b) Find the number of accidents in which the cars belonging to "Ivy Isaac" were involved.
c) Add a new accident to the database; assume any values for required attributes.
d) Delete the Mazda belonging to "Ivy Isaac".
e) Update the damage amount for the car with license number "KJE 539" in the accident with report number "AR2197" to KSh. 300,000.
Question Four
a) Consider the following table instance chart
Table name: EMPLOYEE
|
Column Name
|
EMP ID
|
EMP NAME
|
ADDRESS
|
JOIN DATE
|
|
Key Type
|
PK
|
|
|
|
|
Null/Unique
|
NN, U
|
NN
|
|
NN
|
|
Default Value
|
|
|
|
System Date
|
|
Check
|
1 to 999999
|
|
|
|
|
Data Type
|
Number
|
VARCHAR2
|
VARCHAR2
|
Date
|
|
Length
|
10
|
30
|
50
|
|
b) Create the EMPLOYEE table based on the table instance chart shown above. Choose the appropriate data types and be sure to add integrity constraints
C) You are asked to design a database system for a health club. The database would contain data about customers, their training, contact numbers, etc. Show the main steps you would perform for designing and implementing the database.
d) In your own word explain how data integrity is maintained within the database when concurrent users access the database
Question Five
Use the following table to answer the questions that follow:
WORK.RECORD
|
Emp
|
EmpName
|
Project
|
Project
|
Dept.
|
Dept.
|
Grade
|
Grade
|
Start
|
|
No
|
|
No.
|
Name
|
No.
|
Loc.
|
No
|
Title
|
Date
|
|
4321
|
Omcndi
|
PA2.32
|
Alpha
|
:A32
|
KS'''
|
SR:
|
Grinder
|
241/07
|
|
|
|
|
|
|
|
SR2
|
Grinder
|
241/09
|
|
7;67
|
Lucy
|
PA2.32
|
Alpha
|
LASS
|
NKR
|
SR:
|
Grinder
|
241/07
|
|
|
|
|
|
|
|
GR3
|
Grinder
|
241/09
|
|
6745
|
Omondi
|
PAS.90
|
Alpha
|
:A32
|
KSM
|
FN:
|
Packer
|
3/8106
|
|
4519
|
Mtua
|
PAS.19
|
Omega
|
:A19
|
KSM
|
. I (NI I n
I., (,) (4
|
Packer
|
30.34r
|
|
|
|
|
|
|
|
Grinder
|
212/09
|
|
|
|
|
|
|
|
Grinder
|
21/2/010
|
i. Give TWO reasons why the WORKRECORD table is not in third norma form.
ii. Fully identify one occurrence of redundant data.
iii. Write down the functional dependencies implied by the table.
iv. Normalise the table as far as third normal form showing the intermediate stages and the primary keys. Write down any assumptions you make.
v. Construct an entity relationship diagram to show the relationships between the structures you have identified. Write down any assumptions you make.