Get premium membership and access revision papers, questions with answers as well as video lessons.

Bbit 232:Database Systems Question Paper

Bbit 232:Database Systems

Course:Bachelor Of Business Information Technology

Institution: Kenya Methodist University question papers

Exam Year:2014

DATABASE SYSTEM (BBIT 232) (CISY 221) 2ND TRIMESTER 2014
KENYA METHODIST UNIVERSITY

END OF 2'ND 'TRIMESTER 2014 (DAY) EXAMINATION

FACULTY : COMPUTING & INFORMATICS
DEPARTMENT : COMPUTER SCIENCE AND BUSINESS
INFORMATION
UNIT CODE : BBIT 232/CISY 221
UNIT TITLE : DATABASE SYSTEM
TIME : 2 HOURS

Instructions: Answer question one and any other two.

Question One

Define the following terms
Functional dependency
Data model
Normalization
Explain the difference between external, internal and conceptual schemes. How are these different schema layers related to the concept of conical and physical data independence?
(8mks)
Differentiate between a candidate key and a super key.
(2mks)
When is a table in 3NF?
(3mks)
What is an ERDM and what role does it play in the modern (production) database environment.
(4mks)
What three data anomalies are likely to be the result of data redundancy? How can such anomalies be eliminated?
(5mks)
Outline three benefits and two shortcomings of implementing a DBMS over the traditional file environment.

Question Two

Using the table below answer the questions that follow
Write the SQL code that will create the table structure for a table named EMP_l.
(5mks)

Attribute (field) name Data declaration
EMP_NUM
EMP-LNAME
EMP-FNAME
EMP-INITIAL
EMP-HIRADATE
JOB-CODE CHAR(3)
VARCHAR(15)
VARCHAR(15)
VCHAR(1)
DATE
CHAR(3)
The contents of the EMP-1 table are as shown below

EMP-NUM EMP-LNAME EMP-FNAME EMP-INITIAL EMP-HIRADATE JOB-CODE
101
102
103
104
105
106
107
108
109 News
Senior
Arbough
Ramoras
Johnson
Smitafield
Alonzo
Waslungton
Smith John
David
June
Ann
Alice
William
Maria
Ralph
Larry G
H
E
K
K

D
B
W
8-NOV-100
12-JUL-89
01-DEC-96
15-NOV-87
01-FEB-93
22-JUNE-04
10-OCT-93
22-AUG-91
18-JUL-97 502
501
500
501
502
500
500
501
501

Write the SQL code to enter the first two rows for the table.
(2mks)
Write the SQL code to diarge the job code to 50 for the person whose employee number (EMP-NUM) is job
(8mks)

Question Three

What is logical data independence and why is ti important.
(2mks)
Explain with examples what you understand by the terms referential integrity and entity integrity.
(4mks)
Use the following business rules to create crow’s foot ERD. Write all appropriate connectivity and cardinalities in the ERD.
(9mks)
A department employs many employees, but each employee is employed by one department.
Some employee known as "rovers" are not assigned to any department.
A division operates many departs but each department is operated by one division
An employee may be assigned many projects and a project may have many employees assigned to it.
a project must have at least one employee assigned to it
One of the employees manage each department and each department is managed by only one employee
One of the employees runs each division, each division is ruin by only on employee.
Question Four

Using the INVOICE table structure shown below
Write the relation schema, draw its dependency diagram, and identify all dependencies including all partial and transitive dependencies, you can assume that the table does not contain repeating groups and that an invoice number reference more than one product (hint: This table uses composite primary key)
(5mks)
Remove all partial dependencies write the relational schema, and draw the new dependency diagram. Identify the normal forms for each table structure you created.
(3mks)
Remove all transitive dependencies write the relational schema and draw the new dependency diagrams. Also identify the normal forms for each table structure you create.
(3mks)
Draw the crow’s foot. ERD.
(4mks)

Attribute names Sample value Sample value Sample value Sample value Sample value
INV-NUM 211347 211347 211347 211348 211349
PROD-NUM
AA-E3422QW QD-300932X Ru-995748G AA-E3422QW GH-778345p
SALE-DATE 15-JAN-2006 15-JAN-2006 15-JAN-2006 15-JAN-2006 16-JAN-2006
PROD-LABEL Roraty sander 0.25-in drill bit Band saw Rotary sander Power drill
VEND-CODE 211 211 309 211 157
VEND-NAME Neverfail,inc Neverfail,inc Begood,inc Neverfail,inc Toughgo,inc
QUANT-SOLD 1 8 1 2 1
PROD-PRICE $49.95 $3.45 $39.99 $49.95 $87.75

Write the SQL code that will save the charges made to the EMP-L table.
(1mk)
What to condition must be met before an entity can be classified as a weak entity? Give an example of weak entity.

Bbit 232:Database Systems Question Paper

More Question Papers

Popular Exams