Information
AI Chat

3714 ST1 2021 QQ - Test

Test

Course

Introduction to Databases and Databases Management Systems:Part 1 (CSIQ2634)

37 Documents

Students shared 37 documents in this course

University

University of the Free State

Academic year: 2023/2024

Uploaded by:

Anonymous Student

This document has been uploaded by a student, just like you, who decided to remain anonymous.

University of the Free State

Comments

Please sign in or register to post comments.

Preview text

1 | P a g e

UNIVERSITY OF THE FREE STATE

CSIQ

DEPARTMENT OF COMPUTER SCIENCE AND INFORMATICS

Semester Test 1 2021

ASSESSORS:

INTERNAL MODERATOR:

1. Mr G. Dollman

2. Prof T. Stott

Prof P. Kogeda

EXTERNAL MODERATOR:

MARKS: 60

TIME: 120 minutes

Instructions Answer all the questions. Number your answers clearly.

You must use UML notation. No Crow’s foot notation allowed.

You must use T-SQL (as used in SQL Server) for all SQL queries.

Description

You are writing a small application for the neighbourhood association to keep track of crime incidents in the

area. You have designed the ERD as shown in Appendix A.

Question 1 {Total:5}

Write a SQL stored procedure to insert a new IncidentCategory. The new category name must not match any

existing categories.

(5)

Question 2 {Total: 7}

Write an SQL query to determine how many incidents have occurred based on the incident severities. Ensure

that you can specify a start and end date for the report. Ensure that your columns are named appropriately.

(7)

2 | P a g e

Question 3 {Total: 20}

Suppose you have write a stored procedure called CaptureIncident. The stored procedure must capture all data

related to a crime incident that has occurred. The following specifications apply:

All required data is sent through as parameters unless specified
The date captured is not sent through as a parameter and must be captured as the current date and time. No hard-coding allowed.
The category is passed only as a description, determine whether the category exists using “LIKE”. If the category does not exist, insert a new category.
The database must be left in a consistent state after execution of the stored procedure.
Use the given ERD to ensure you account for all fields and take note of the meta-data of the given fields as well.

Write the SQL for the whole stored procedure as per the specifications given here and the ERD in Appendix A.

(20)

Question 4 {Total: 12}

a) Briefly outline the three phases that occur when a SQL query is processed. (6)

b) Briefly discuss the bottlenecks common within query processing. (2)

c) Describe two modes that the query optimizer can operate in. (4)

Question 5 {Total: 16}

The database is being used by more and more users day by day, users are reporting problems. Concurrency

control has now become a problem for the neighborhood watch database.

a) Describe the three main problems the occur when there is a failure with concurrency control and give an example for each. (6)

b) Deadlocks are occurring within the database, describe the techniques that can be used to control deadlocks. (6)

c) There are two major approaches to use with concurrency control using time stamping. Briefly describe them. (4) ooOOoo

Was this document helpful?