 |
DBF510S - DATABASE FUNDAMENTALS - 2ND OPP - JULY 2024 |
|
|
1 Page 1 |
▲back to top |
n Am I BI A u n IVER s I TY
OFSCIEnCE Ano TECHn OLOGY
FACULTY OF COMPUTING AND INFORMATICS
DEPARTMENTOF SOFTWAREENGINEERING
QUALIFICATION:BACHELOROF COMPUTERSCIENCE,BACHELOROF INFORMATICS,BACHELOR
OF CYBERSECURITY,BACHELOROF GEOINFORMATICS,BACHELOROF LAND ADMINISTRATION
QUALIFICATIONCODE:07BCMS, 07BAIT, 07BCCS,07BGEI,
07BLAM, 06DPRS
LEVEL:5
COURSE:DATABASEFUNDAMENTALS
COURSECODE: DBFSl0S
DATE:JULY2024
PAPER:THEORY
DURATION: 2 HOURS
MARKS: 70
SECONDOPPORTUNITY/SUPPLEMENTARYEXAMINATION QUESTION PAPER
EXAMINER:
MRS SHILUMBECHIVUNO-KURIA
MODERATOR:
DR GEREONKOCHKAPUIRE
THIS QUESTION PAPER CONSISTS OF 5 PAGES
(Including this front page)
INSTRUCTIONS TO STUDENTS
1. Attempt all Questions.
2. All questions have to be answered in the Answer Booklet. Clearly indicate the section and
question number for each answer.
3. The allocation of marks is an indication of the extent of the expected answer. Answering more
than expected does not result in higher marks. Keywords alone are not enough.
4. There are no books, notes or any other additional aids allowed in the examination.
|
|
2 Page 2 |
▲back to top |
Answer ALLthe questions in this paper.
SECTION A:
Question 1:
[Marks: 20]
Multiple Choice Questions
[10 Marks]
• Answer all the questions in the answer booklet that has been provided.
• Select the best answer.
1. Which of the following is a data-oriented methodology used to create information systems?
a) Data modelling.
b) Information engineering.
c) Information architecture.
d) System analysis.
2. The SDLCphase in which functional data specifications and processing rules are created is
called:
a) Planning phase.
b) Design phase.
c) Analysis phase.
d) Implementation phase.
3. A relation that contains no multivalued attributes, has nonkey attributes solely dependent
on the primary key, but contains transitive dependencies is in which normal form?
a) First
b) Second
c) Third
d) Fourth
4. Which of the following is NOT a cost and/or risk of the database approach?
a) Specialised personnel.
b) Cost of conversion.
c) Improve responsiveness.
d) Organisational conflict.
5. In a relational database, what is a primary key?
a) A key used to unlock the database.
b) A unique identifier for each record in a table.
c) A key used to establish relationships between tables.
d) A key used for sorting records in a table.
6. Which type of join returns all records when there is a match in either left or right table?
a) Inner join.
b) Left outer join.
c) Right outer join.
d) Full outer join.
Page 11
|
|
3 Page 3 |
▲back to top |
7. What is the purpose of the SQLSELECTDISTINCTstatement?
a) It is used to delete duplicate records from a table.
b) It is used to select only unique values from a specified column.
c) It is used to select records based on a specific condition.
d) It is used to sort the records in a table in descending order.
8. Which of the following is NOT a type of SQLjoin?
a) Inner join.
b) Full outer join.
c) Left outer join.
d) Parallel join.
9. What is the purpose of the SQLGROUPBYclause?
a) To sort the results of a query.
b) To filter records based on a condition.
c) To perform aggregate functions on groups of data.
d) To join multiple tables together.
10. Which SQLcommand is used to modify existing data in a database?
a) ALTER
b) UPDATE
c) MODIFY
d) CHANGE
Question 2
[10 marks]
Indicate whether the following statements are True or False in your answer booklet.
ID StatemenVDescrlptlon
:,
,;
"·
;
True
1 DCLis used to update the database with new records.
2 A referential integrity constraint specifies that the existence of
check constraints in the same or another table.
3 Character strings and dates in the WHERE clause must be
enclosed in single quotation marks.
4 In SQL, the UNION operator is used to combine the results of
two or more SELECTstatements into a single result set.
5 In a relational database, a table is also known as a tuple.
6 Data redundancy is beneficial in a database because it
improves data integrity.
7 A transaction in a database is a single unit of work that must be
completed entirely or not at all.
8 All aggregate functions except COUNT(*) ignore null values in
their input collection.
9 The NATURALJOIN clause is based on columns in the two
tables that have the same name and datatype.
10 DESCcan be used with the order by clause to sort a table in
descending order.
False
Page I 2
|
|
4 Page 4 |
▲back to top |
,I
SECTION B: CONCEPTS
1. Explain the following terms with an example.
a) Alias
b) Primary Key
2. List any 2 disadvantages of the file processing system.
[Marks: 10]
[2 Marks]
[2 Marks]
[1 Mark]
3. Correct the following code so that it joins the tables employees and Job_history based on
common column emp_id which is found in both tables?
[3 Marks]
SELECTemp_id, job_id, dept_id
FROM employees
JOIN
SELECTemp_id, job_id, dept_id
FROMjob_history;
4. What is the purpose of the ROLLBACKstatement in SQL?
[2 Marks]
SECTION C: COMPREHENSION
[Marks: 40]
1. Given the following scenario create an appropriate ERdiagram. Include at least 3
attributes and 1 primary Key for each entity. Include the cardinality and relationships. Use Crow's
Foot Notation.
[15 Marks]
A hospital has multiple departments, such as Cardiology, Paediatrics, and Orthopaedics. Each
department has a department ID and name. Doctors work in one or more departments and have
a doctor ID, name, specialty, and contact information. Patients visit the hospital and are assigned
to one or more doctors for treatment. Each patient has a patient ID, name, date of birth, and
medical history.
2. Write an SQLquery to create a table called Athletes using the information provided in the
table below. Choose relevant datatypes and include a primary key.
[8 Marks]
Athlete_id
101
111
121
131
141
Last_name
Mboma
Fredericks
Johannes
Velho
Benson
First_name
Christine
Frankie
Helalia
Merylese
Johanna
Sporting Event
speciality
200m runner
100m runner
Marathon runner
Sprinter
Paralympian
Page I 3
|
|
5 Page 5 |
▲back to top |
3.
Write a query to insert all the 5 rows into the table Athlete as shown in Q. 2.
[5 Marks]
4.
Write a query to check if all the rows have been added to the table Athlete.
[2 Marks]
5. Frankie Fredricks has requested that his name be replaced with Harry Simon who is a
boxer. Write a query that modify Frankie Fredricks to Harry Simon using the same Athlete_id.
[5 Marks]
6. Jahanna Benson has retired frotn athletic activities and would like to be removed from the
table. Write a query that will make this change.
[2 Marks]
7. Write a query to find all the sporting events that have the word runner in the Athlete
table.
[3 Marks]
**************************** End of the Paper ********* ***************** *********
Page I 4