DE COMPUTER SCIENCE
DATABASE DESIGN AND APPLICATIONS
THEORY ASSIGNMENT ONE DUE DATE: 10 SEPTEMBER 2024
QUESTION 1
a) Explain the following terms as they are used in databases:
i. Data independence [2 marks]
ii. Data redundancy [2 marks]
iii. Data inconsistency [2 marks]
iv. Metadata [2 marks]
v. Checkpoint [2 marks]
b) Describe any four properties of a relational database model. [4 marks]
c) Differentiate between network and hierarchical model [6 marks]
QUESTION 2
a) Given the following scenario:
A company is organised into departments. Each department has a unique name and
number. A department controls a number of projects, each of which has a unique name,
number and single location.
The company stores its employees’ name, employee number, address and salary. An
employee is assigned to one department but may work on several projects, which are not
necessarily controlled by the same department.
They also keep track of the dependents of each employee for insurance purposes. Each
dependent’s name and relationship to the employee are kept.
Draw an ERD for the above scenario [10 marks]
b) Explain the three types of relationships found in an ERD [9 marks]
c) Compare and contrast between hierarchical and network data models [6 marks]
DE COMPUTER SCIENCE
DATABASE DESIGN AND APPLICATIONS
THEORY ASSIGNMENT TWO DUE DATE: 17 SEPTEMBER 2024
QUESTION 1
a) With the aid of a diagram, explain the various level of data abstraction. [12 marks]
b) Describe any four criteria for classifying databases. [8 marks]
c) Describe the stages of the database life cycle. [10 marks]
QUESTION 2
a) There are three types of Structured Query Languages (SQL) used in databases. For each
of the following groups of SQL below give a brief description and an example as you
understand them.
i. Data Manipulation Languages (DML);
ii. Data Definition Languages (DDL); and
iii. Data Control Languages (DCL) [15 marks]
b) Describe concurrency problems associated with databases and the appropriate measures that
are put in place so as to solve a respective concurrency problem. [10
marks]
DE COMPUTER SCIENCE
DATABASE DESIGN AND APPLICATIONS
PRACTICAL ASSIGNMENT ONE DUE DATE: 24 SEPTEMBER 2024
QUESTION 1
a) Explain deferred and immediate update techniques. [8 marks]
b) Normalize the following relation
Branch (Branch #, BranchAddress, {ISBN, Title, Author, Publisher, Num-Copies})
[12
marks]
QUESTION 2
ACCOUNT TABLE
Account No. Name Account Type Balance
00003Q Nikau Current $100.78
00012R Ping Savings $203.45
00056Z Sijamula Student $156.23
00078S Banda Current $216.12
i. Assuming the base table above is already in 3NF, write SQL program statements to
create and put values into the table. [10 marks]
ii. Create a view to show all customers who operate a current account and have their
balance greater than or equal to $150.00. [10 marks]
DE COMPUTER SCIENCE
DATABASE DESIGN AND APPLICATIONS
PRACTICAL ASSIGNMENT TWO DUE DATE: 01 OCTOBER 2024
QUESTION 1
a) With the information given in the table below:
Roon No. Room Type No. of Occupants
HC100 Single
HC101 Shared
HC102 Single
HC103 single
i. Write a statement to display single rooms. [5 marks]
ii. Write an SQL statement to delete Room HC103 [5 marks]
b) Describe the following measures to deal with threats to database security:
i. Physical Security [2 marks]
ii. Logical security [2 marks]
iii. Behavioural Security [2 marks]
c) Describe any two database deployment techniques. [4 marks]
QUESTION 2
b) There are three types of Structured Query Languages (SQL) used in databases. For each
of the following groups of SQL below give a brief description and an example as you
understand them.
iv. Data Manipulation Languages (DML);
v. Data Definition Languages (DDL); and
vi. Data Control Languages (DCL) [15 marks]
b) Describe concurrency problems associated with databases and the appropriate measures that
are put in place so as to solve a respective concurrency problem. [10
marks]
