ZIMBABWE
EDUCATION,
MINISTRY OF HIGHER AND TERTIARY
INNOVATION,SCIENCE AND TECHNOLOGY
DEVELOPMENT
HIGHER EDUCATION EXAMINATIONS COUNCIL
(HEXC0)
NATIONAL CERTIFICATE
IN
INFORMATION TECHNOLOGY
Concepts (Practical) PAPER NO: 317/22/MO3B
MODULE: Database
DURATION:3 Hours
MAY/JUNE 2024 EXAMINATION
REQUIREMENTS
1. Computer with sgL compiler.
2. Printing facility
for each candidate.
3. Storage medium
4. Serialised script cover.
INSTRUCTIONS TO CANDIDATE
1. Answer ALL questions
output
2. Print both code and
3. Submit both hardcopy and softcopy
DAPER NO SU7/92/MO3B-
DATABASE CONCEPIS (PRACTICAL
QUESTION I
Chawatama School administratlon has
administrator destgn and requested that you as the
students enrolled,subjectsimplementa database that will matntainDatabase
data of the
and groups each student offered by the school, the teachers in the
tdentifles the
and teachers belong to. Tlhe scho01
following tables Logtcal Design Phase
Marks
Colunmn nanme
StudentId Data type
char(5) Descriptlon
Subjectld Primary kev
char (6)
DateCaptured NOT NULL
Mark date/time NOT NULL
Markld integer NOT NULL
char (5)
Subjects
Column Name
Datatype
subjectId Description
char(6)
Title Primary key
varchar(30) NOT NULL
Groups
Column Name
Datatype
Groupld Description
Char(5)
Name Primary kev
Varchar(30) NOT NULL
Teachers
Column Name Datatype Description
Teachersld
Integer(3) Primary key
Firstname Varchaar (30) NOT NULL
Lastname Varchar(30) NOT NULL
Subject Teacher
Column Name Datatype Description
SubjectId Char(6) Primary key
Teacherld Integer(3) NOT NULL
Groupld Char(5) NOT NULL
STUDENTS
Column Name Datatype Description
studentId Primary key
Char(6)
Firstname Varchar(30) NOT NULL
Lastname NIT NULL
Varchar (30)
Groupld NOT NULL
Char(5)
CONCEPTS (PRACTICAL
PAPER NO 31Z/22MO3B- DATABASE
QUESTION I
requested that you as the Database
Chawatama School administration has the
a database that will maintain data of
administrator design and implement the school
by the school, the teachers ín
students enrolled, subjects offered
to. The Logical Design Phase
and groups each student and teachers belong
identifies the following tables
Marks
Column name Data type Description
Studerntld char(5) Primary key
SubjectId char (6) NOT NULL
DateCaptured date/time NOT NULL
Mark integer NOT NULL
Markld char (5)
Subjects
Column Name Datatype Description
subjectId char(6) Primary key
Title varchar(30) NOT NULL
Groups
Column Name Datatype Description
Groupld Char(5) Primary key
Name Varchar(30) NOT NULL
Teachers
Column Name Datatype Description
Teachersld Integer(3) Primary key
Firstname Varchaar (30) NOT NÚLL
Lastname Varchar(30) NOT NULL
Subject Teacher
|Column Name Datatype Description
Subjectld Char(6) Primary key
Teacherld NOT NULL
Integer(3)
Groupld Char(5) NOT NULL
STUDENTS
Column Name
Datatype Description
studentld
Char(6) Primary key
Firstname
Varchar(30) NOT NULL
Lastname Varchar (30) NIT NULL
Groupld NOT NULL
Char(5)
DATABASE CONCEPTS (PRACTICAL)
PAPER NO: 317/22/MO3B -
Usng appropriate SQL commands
(2 marks)
(a) Create a database called Registratlondb.
attention to
(b) Create the tables given in the scenarlo above paying close
(30marks)
the data types and descriptionsgiven.
(c) Change the table name "students" to "Pupls". (3 marks)
(d) Remove thecolumn "markld" from the table "marks". (2 marks)
QUESTION 2
Using appropriate sgL commands:
(a) Populate the database with the followingdata
Marks
Studentld Subject Id Date Mark
F 1002 MA222 |Today'sDate 75
F2005 Ph404 Today's Date 60
F3447 EC300 Today's Date 52
F1003 MA222 Today's Date 83
(4 marks)
Subjects
Subject Id Title
MA222 Pure mathematics
Ph404 Physics
EC300 Econonmics
(3marks)
Pupils
StudentId Firstname Lastname Groupld
F1002 Joyce Munda SC100
F2005 Linda Ruoko SC100
F3447 Loyce Mago CO105
F1003 John Shuga SC100
(4 marks)
Teachers
Teachersld firstName Lastname
101 Godo
Binga
102 Zuze Mhaka
(3 marks)
NO: 317/22/MO3B - DATABASE CONCEPTS (PRACTICAL)
PAPER
Groups
Groupld Name
SC100 Sciences
CO105 Commercials
Ar405 Arts
(3 marks)
Subject Teacher
Teacherld Groupld
Subjectld
SC100
MA222 101
102 CO105
EC300 (3marks)
students taking
(b) (1) Display the studentId and subjectld for all
(3 marks)
"MA222"as a subject.
firstname, lastname.
(i) Using the table "Pupils" display studentId,
(4 marks)
groupld in descending order of studentld.
101's
(ii) Using the table named "Teachers", change Teacherld
(4 marks)
Lastname to Mbudzi.
(ív) Using the table "Subject" display all information of any subject title
that starts with the letter "P". (5marks)
(5 marks)
(v) Display the total number of students per subjectld.
(vi) Display thestudentld, subjectld and mark of students whose
(4 marks)
marks range fromn 50 to 70.
PAPER O17 MOR-DArABISE CONCEEIS (PRACTICAL
QUESTON 3
(a) Create the following usels
Username Password
Senior teacher lore2020
Headmaster Dall010g
(4 marks)
(b) (0 Write an SQLommand that gives the user named Senior Teacher
the permmissions toSELECT and UPDATE the mark column on the
table "Marks". (5marks)
() Write an SQL command that allows the user named headmaster
insert and update all columns anddelete data on the table named
"teachers" (5marks)
(iü1) Write an SQL command that disallows the user named
SeniorTeacher from updating any column on the table named
"marks" . (4 marks)
........cn
