Introduction to Database (CSCT2211)
Computer Science - COS
Semester: Second Semester
Level: 200
Year: 2017
SCHOOL:
H.T.T.T.C
DEPARTMENT:
CSC
LECTURER(S):
Mr. N CHUNK WE
COURSE CODE:
ICT124
COURSE TITLE: DATABASE DESIGN OPTION:
ICT100
DATE:
JULY 2017
HALL: SOWETO TIME: 02 hrs NATURE:
SEMESTER EXAM
Instructions: NO DOCUMENTS ALLOWED
Exercise 1: MCQ ON LECTURE NOTES (1+1+1+1+1) =Q5marks
1.
DBMS stands for
a) Database Monitoring System b) Database Management System c) Database Marketing
System
2.
CSV is the abbreviation of
a} Comma Sensitive Value b) Comma Separated Value c) Character Separated
Value
3.
A computerised database is stored in a computer in the form of
a) Software b) File c) Application Program
4.
The fact that several different users can access the same database at the same moment is
a) Data integrity b) Concurrent access c) Data independence
5.
Which of the following models is not system dependent
a) Conceptual model b) Relational model c) Logical model
Exercise 2: DATABASE DESIGN (1+6+8) =15marks
1. Which name is given to the diagram below?
2. From the different constraints that follow, complete the diagram above:
Each individual always signs a contract that bears the beginning date of the contract and its
REPUBLIC OF CAMEROON
Peace - Work - Fatherland
THE UNIVERSITY OF BAMENDA
HIGHER TECHNICAL TEACHER TRAINING
COLLEGE (H.T.T.T.C.) BAMBILI – BAMENDA
DIRECTION OF STUDIES
P.0 BOX 39 BAMBILI TEL : 33 36 36 94
REPUBLIQUE DU CAMEROUN
Paix - Travail -Patrie
UNIVERSITE DE BAMENDA
ECOLE NORM ALE SUPERIEURE DE
L 'ENSEIGNEMENT TECHNIQUE (ENSET)
DE BAMBILI - BAMENDA
DIRECTION
DES
ETUDES
FAX / 33 05 10 69
www.schoolfaqs.net
duration. Many different individuals can sign many contracts on many different lodgings.
A township can contain many lodgings and is located in a single district but each lodging
belongs to a unique township. The same district can host several townships.
A lodging corresponds to a unique type and but several lodgings can belong to the same type.
3) Derive the corresponding relational model of the diagram above.
Exercise 3: DATABASE QUERIES AND SQL (3+3+1+1+1+1+1+1+1+1) =15marks
Consider the following relations that constitute a database called CORPORATION. EMPLOYEE
(EmploveeCode, EmployeeName, EmployeeStreet, EmployeeCity)
COMPANY (CompanyName, CompanyTown, CompanyCapital)
WORKS (≠EmploveeCode. ≠CompanyName, salary, Recrutement_Date)
An instance of the database is given below.
EMPLOYEE
EmployeeName
EmployeeCode
EmployeeStreet
EmplyeeCity
NGABA
EMP001
DEIDO
DOUALA
TABI
EMP004
ESSOS
YAOUNDE
KAMDEM
EMP022
KAMKOP
BAFOUSSAM
EPIE
EMP020
MOLYKO
BUEA
ABDOU
EMP007
DOMAYO
MAROUA
1.
Give the Entity/Relation diagram corresponding to the relations above.
2.
Give the degree and the cardinality of each table above.
3.
Write SQL instructions for the following queries:
a.
Create the database called CORPORATION,
b.
Create the relational tables from above.
c.
Name and city of residence of all employ who have worked for at least 10 years.
d.
d. Names and town of localisation of all companies having a capital above 30 billion.
e.
Add the Email and gender as attributes of an employee.
f.
Remove all employees who live in the same city as the company in which they work
g. Add the email and gender as attributes of an employee
h. Add two newly recruited employees with these attributes
COMPANY
CompanyName
CompanyTown
CompanyCapital
SONARA LIMBE 4.5 BILLIONA
CDC
LIMBE
5 BILLIONS
CIMENCAM
GAROUA
55 BILLIONS
SOSUCAM
MBANDJOCK
25 BILLIONS
CAMTEL
YAOUNDE
75 BILLIONS
ALUCAM
EDEA
85 BILLIONS
ENEO
DOUALA
5.5 BILLIONS
WORK
EmployeeCode
CompanyName
Salary
Recruitment Date
EMP020 SONARA 350000 12/07/1990
EMP007
ENEO
125000
01/03/2005
EMP001
ENEO
275000
12/11/2000
EMP001
CIMENCAM
400000
30/01/2010
www.schoolfaqs.net
www.schoolfaqs.net
