Scribd
Upload
153 views

Cit 3201 Database Systems

This document contains instructions for the second year first semester examinations for various computer science and information technology degree programs at Meru University of Science and Technology. It includes five exam questions related to database systems, with sub-questions testing knowledge of conceptual and physical database models, normalization, database management system components, SQL commands, and entity relationship diagrams. Students are instructed to answer question one and any other two questions in the two hour exam.

Uploaded by

Wicklife
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
153 views

Cit 3201 Database Systems

This document contains instructions for the second year first semester examinations for various computer science and information technology degree programs at Meru University of Science and Technology. It includes five exam questions related to database systems, with sub-questions testing knowledge of conceptual and physical database models, normalization, database management system components, SQL commands, and entity relationship diagrams. Students are instructed to answer question one and any other two questions in the two hour exam.

Uploaded by

Wicklife
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

MERU UNIVERSITY OF SCIENCE AND TECHNOLOGY

P.O. Box 972-60200 – Meru-Kenya.


Tel: +254 (0)799529958, +254 (0)799529959, +254 (0)712524293
Website: www.must.ac.ke Email: [email protected]

UNIVERSITY EXAMINATIONS 2020/2021

SECOND YEAR FIRST SEMESTER EXAMINATIONS FOR DEGREE BACHELOR OF


SCIENCE IN INFORMATION TECHNOLOGY, BACHELOR OF SCIENCE IN COMPUTER
SCIENCE, BACHELOR OF SCIENCE COMPUTER SECURITY AND FORENSICS,
BACHELOR OF SCICENCE COMPUTER TECHNOLOGY, BACHELOR OF SCIENCE IN
MATHEMATICS AND COMPUTER SCIENCE, BACHELOR OF EDUCATION SCIENCE(
MATHS/COMPUTER, GEOGRAPHY/COMPUTER), BACHELOR OF EDUCATION ARTS
(MATHS/COMPUTER, GEOGRAPHY/COMPUTER)

CIT 3201: DATABASE SYSTEMS

DATE: MARCH 2021 TIME: 2 HOURS

INSTRUCTIONS: Answer question one and any other two questions.

QUESTION ONE (30 MARKS)

a) Differentiate conceptual database model and physical model (4 marks)


b) Define normalization (2 marks)
c) Explain how referential integrity is enforced in a relational database management system
(2 marks)
d) Describe four components of a database management system (4 marks)
e) Explain the importance of normalization (4 marks)
f) Differentiate primary key and foreign key (2 marks)
g) Differentiate between hierarchical and network database models (4 marks)
h) Explain any three limitations of the file system approach that were resolved by database
management systems. (6 marks)
i) Explain the use of Grant and Revoke SQL commands? (2 marks)

Meru University of Science & Technology is ISO 9001:2015 Certified


Foundation of Innovations Page 1
QUESTION TWO (20 MARKS)

a) Explain the three categories of anomalies that may be experienced when working with
tables that are not normalized (6 marks)
b) Explain the purpose of indexes in a database management system (2 marks)
c) Early Morning Chemist is a pharmaceutical outlet company that offers drug dispensing
services to the public. The company has a database that includes a record of all of the
drugs in its inventory i.e. thedrugs table, with the following details of drugs;
drugid, name,manufacturer,form, route.
Write SQL expressions to.
i. Create the products table, including a primary key. (3marks)
ii. Insert the following details into the products table.
name = Argumentin 500mg, manufacturer=GSK, form = suspension, route=oral
(3marks)
iii. Insert a column called substitute to store information on probable replacement
drug in case the subject drug is out of stock. (3marks)
iv. Write an expression that would extract only those drugs whose name start with the
letters “br” (3marks)
QUESTION THREE (20 MARKS)

a) Database management systems (DBMS) performs several functions in order to ensure


data integrity and consistency. State and explain any five functions of a DBMS
(10 marks)
a) A customer can make many payments, but each payment is made by only one customer.
A customer can make many orders and can be served by different sales persons.
Salespersons are attached to a specific region
i. Use the above business rules to design an entity relation model indicating
probable attributes for each entity and relationship between entities (4marks)
ii. Use SQL statements to implement the ERD in question (i) above (6marks)

QUESTION FOUR (20 MARKS)

a) Consider the following relational schema:


student (regno, studentname, program, campus)
registers (regno, coursecode, semester, regdate)
courses (coursecode, coursetitle, credits,status)

Write SQL statements to achieve the following;


i) Find the regno, studentname, and program for all students who have registered
for a course code “CIT 3201” and in a semester named “SEM3_2020” (3marks)
Meru University of Science & Technology is ISO 9001:2015 Certified
Foundation of Innovations Page 2
ii) Find the regno, studentname, and number of courses registered by students who
have registered for less than six courses in a semester named “SEM3_2020”
(3 marks)
iii) A summary of course codes, course titles, and the number of students who have
registered for each course in a semester named “SEM3_2020”. (3 marks)
iv) Set the status of all courses that have never been selected (ie. registered by any
student) to “inactive” (3 marks)
b) Write a SQL statementto create user called “john” with password “pw0rd1” (2 marks)
c) Explain the purpose of the following DBMS languages (6 marks)
i) DDL
ii) DML
iii) DCL

QUESTION FIVE (20 MARKS)

a) Describe any two wildcard characters used in structured query language (4 marks)
b) Consider an online company selling a wide range of products to its customers.
The customer table should have customerID, name, address, phone fields; while
the products table should include ProductID, name, type, and price fields. Your
tables should have primary keys. For each field determine the “null” constraint
too.
i) Write SQL queries to create tables for customers and products of the company.
(4 Marks)
Further, assuming that customers can make orders for different products, whereby
each order can only includeitems referring to only one product. Eg. an order for
bags of cement, and a separate order for several tins of paint. The number of items
(qty) ordered and the order date (odate) should also be stored.
ii) Using an ER diagram, explain how the orders table can be added to the database.
(4 Marks)
iii) Write necessary SQL commands to add the orders table to the database.
(4 Marks)
c) Use SQL statements grant the following permission to a user names “john”
i) Insert records to the products table (2 marks)
ii) View records in the products table (2 marks)

Meru University of Science & Technology is ISO 9001:2015 Certified


Foundation of Innovations Page 3

You might also like