UNIVERSITY EXAMINATIONS: 2017/2018
EXAMINATION FOR DEGREE OF BACHELOR SCIENCE/BUSINESS
BBIT 107&1207A- PRINCIPLES OF DATABASE DESIGN
MODE: FULL TIME/PART TIME/DISTANCE LEARNING
DATE: JULY, 2018 DURATION: 2 HOURS
INSTRUCTIONS: Answer Question ONE and any other TWO questions
QUESTION ONE [30 MARKS]
a). Describe any TWO disadvantages of file based systems.
b). Describe any FOUR database models
c). Describe the following data storage approaches used in distributed database.
i). Replication 2 Marks
ii). Fragmentation 2 Marks
d). Describe the difference between logical data independence and physical data independence.
e). Describe FOUR properties of a relation in a relational database.
f). Describe the following ways of classifying relationships in a database. Give example for
i). Cardinality 2 Marks
ii). Degree. 2 Marks
iii). Optionalities. 2 Marks
g). Outline the activities involved in database development life cycle.
QUESTION TWO [20 MARKS]
a). Describe FOUR activities involved in logical database design.
b). Consider the flat database below:
i). Identify and discuss three anomalies that may occur in this data base. 6 Marks
ii). Convert this table into third normal form. 6 Marks
iii). Store the records into the resultant table(s). 4 Marks
QUESTION THREE [20 MARKS]
a). Database integrity ensures that data entered in to the database is accurate, valid and consistent. Any
applicable integrity constraints and data validation rules must be satisfied before permitting a change
to the database. In this regard, describe the following types of database integrity, giving an example in
FOUR each case.
b). Discuss the ACID properties as used in database transactions.
c). Describe any security threats that a shared database can be subjected to.
QUESTION FOUR [20 MARKS]
a) Fastcom technologies, a company that sells Nokia mobile phones has implemented a database that
tracks details about the phones. The following is one of the table named Phone is located in a
database called NokiaCellPhones. Use this information to answer the questions that follow.
i. Write down a SQL DDL statement that was used to create the table. 4 Marks
ii. Write down a SQL DML statement to display Nokia X series. 3 Marks
iii. Write down a SQL DML statement to display total for Nokia X7 phones. 3 Marks
iv. Write down a SQL DML statement to remove all Nokia 2690 phones from the table. 3 Marks
v. Write down a SQL DML statement to remove all Nokia 2690 phones from the table. 3 Marks
vi. Write down a SQL DML statement to display the standard deviation on the price. 2 Marks
vii. Write down a SQL DML statement to display total price by PhoneType. 2 Marks
QUESTION FIVE [20 MARKS]
a). Database management system systems (DBMS) can be classified as open source or proprietary. In
this regard briefly describe the following DBMS products indicate whether each product is open
source or proprietary in your description.
i). Oracle 11g.
iii). Microsoft SQL Server.
b) Describe the following types of attributes used in databases
i. Surrogate key
ii. Primary key
iii. Single valued attribute
iv. Simple attribute
c) Discuss FOUR software components of a relational database management system
b). Describe the key steps followed when selecting a DBMS product. 4 Marks