lOMoARcPSD|57368375
DDD Assessment TEST2014
Database Design (Botswana Accountancy College)
Scan to open on Studocu
Studocu is not sponsored or endorsed by any college or university
Downloaded by Kat Zero (zerokat587@gmail.com)
lOMoARcPSD|57368375
Botswana Accountancy College
Computing and Information Systems
B.Sc. Computer Systems Engineering Route Year 2
B.Sc. Applied Business Computing Route Year 2
Database Design & Development
Assessment Examinations
25 September 2014
Start time: 1400
Duration: 2 hrs.
Instructions to candidates:
Candidates must attempt all questions
Each question is worth 25 marks
Candidates attempting to gain an unfair advantage or colluding in any
way whatsoever are liable to be disqualified
Do NOT open the question paper until you are told to do so
This question paper consists Four (4) printed pages including this
page
Downloaded by Kat Zero (zerokat587@gmail.com)
lOMoARcPSD|57368375
Question 1
a) In SQL language, describe and give two examples of each of the following
i) DDL [3marks]
ii)DML [3marks]
iii)
DCL [3marks]
b) State any four (4) properties of a relation. [4marks]
c) A database management software (DBMS) supports the organisation data storage,
management and retrieval of data in the database which is basically CRUD functions.
Identify and explain other four (4) functions that a DMBS can support. [8marks]
d) Given the following LAB table.
LAB table
CLASS_CODE LABNAME TOTALCAPACITY
1 LAB1 45
2 LAB3 30
3 LAB2 50
4 LAB4 40
i) Define the terms cardinality and degree [2marks]
ii) What is the degree and cardinality of the table above? [2marks]
Question 2
a) With the aid of clearly labeled diagram, explain the three tier architecture using the
ANSI/SPARC model for database architecture. [6marks]
b) Write brief notes on the following
i) Referential integrity [2marks]
ii) Entity integrity [2marks]
iii) Domain integrity [2marks]
iv) Candidate key [2marks]
c) Consider the following two relations
PRODUCT
PRODUCT_CODE PRODUCTNAME PRICE
1 HOTDOG 12
2 ICECREAM 40
3 CAKE 30
4 FISH 17
Page 2of 4
Downloaded by Kat Zero (zerokat587@gmail.com)
lOMoARcPSD|57368375
SALES
CLASS_CODE DATESOLD QUANTITY
1 5/8/2014 5
7 6/8/2014 20
9 22/8/2014 6
4 7/9/2014 45
Suppose one student types the following SQL command:
select * from PRODUCT left join SALES
on PRODUCT.CLASS_CODE=SALES.CLASS-CODE
When you execute this query what results do you expect to get?
[3marks]
d) Suppose you have two tables: STUDENT1 and STUDENT2. The
STUDENT1 table contains the records for three students: Lebang
Eugene, Ontlametse Moeti and Fiona Pono. The STUDENT2 table
contains records for students Maatla Kaone and Tshepo Lorato. If a
UNION query is executed, what output do we expect to see?
[2marks]
e) The student wrote a query to list the students who stay in Gaborone or Francistown as
follows:
Select * from student where city = ‘Gaborone’ OR city = ‘Francistown’
Rewrite the above SQL statement using the IN operator. [2marks]
f) Tshepo wanted to design a database using object oriented approach. He has asked you
to differentiate between an object and a class in object oriented data model. What is
the difference between these two? [4marks]
Question 3
Consider the following business rules for employees working on various
projects in an organisation:
A department employs many employees, but each employee is
employed by one department.
Some employees, known as “mischief” are not assigned to any
department.
Page 3of 4
Downloaded by Kat Zero (zerokat587@gmail.com)
lOMoARcPSD|57368375
A division operates many departments, but each department is
operated by one division.
An employee may be assigned to more than one project, and many
employees work on a project for it run successfully.
A project must have at least one employee assigned to it.
One of the employees manages each department, and each
department is managed by only one employee.
One of the employees runs each division, and each division is run by
only one employee.
Using the provided business rules, come-up with an ER Diagram which
includes the following
Appropriate entities [5
marks]
Attributes [2.5
marks]
Primary keys [2.5
StudentId StudentName CellNumber Course
CIS13-125 Laone 74112233 CSE
CIS12-134 Bright 75856596 ABC
CIS13-041 Thabiso 72445588 ABC
CIS13-0034 Kamogelo 3957852 ABC
marks]
Relationships [5
marks]
Optionalities [5
marks]
Cardinalities [5
marks]
Question 4
The following is a STUDENT relation.
a) Write an SQL code to create a table has the attributes listed above, including the
primary key [3marks]
b) Draft SQL statements to do the following:
i) Add an attribute/ column age to the student table [2marks]
ii) Update the student name Thabiso to Goitsemodimo for StudentID CIS13-041
Page 4of 4
Downloaded by Kat Zero (zerokat587@gmail.com)
lOMoARcPSD|57368375
[3marks]
iii) After observing the pattern of StudentID , you have seen that it follows the format
CISXX-XXX where X is an integer from 0 and 9. Add a constraint named ustid to
the table to effect such a format. [3marks]
iv) Write brief notes on the storage manager and query processor database
subsystems. [4marks]
v) List all students whose surname starts with M. [3marks]
vi) A student can either be male or female. Suppose that you have included gender
attribute in the student relation, add a constraint named ckgen to specify domain
values for the gender of the students table. [3marks]
c) The following are some symbols that are used in relational algebra, δ and Π, write
brief notes on the use of these symbols in relational algebra. [4marks]
END OF EXAMINATION
Page 5of 4
Downloaded by Kat Zero (zerokat587@gmail.com)
