2022 ass

Question 1

Given the following database Schema answer the questions that
follow:

STUDENT(cisNum, name, gender,age )
BOOK(bookId, title)
BORROW (cisNum, bookId, dateBorrowd, dueDate)

Write SQL statement snippets that would enforce entity integrity and
referential integrity for all three tables

Answer 1

– Entity Integrity for STUDENT table
CREATE TABLE STUDENT (
cisNum INT PRIMARY KEY, – cisNum is the primary key, unique and not null
name VARCHAR(100),
gender CHAR(1),
age INT
);

– Entity Integrity for BOOK table
CREATE TABLE BOOK (
bookId INT PRIMARY KEY, – bookId is the primary key, unique and not null
title VARCHAR(255)
);

– Entity Integrity for BORROW table
CREATE TABLE BORROW (
cisNum INT, – cisNum references STUDENT table
bookId INT, – bookId references BOOK table
dateBorrowed DATE,
dueDate DATE,
PRIMARY KEY (cisNum, bookId) – Composite primary key ensures a unique borrowing record for each student and book
);

– Enforcing Referential Integrity on the BORROW table
ALTER TABLE BORROW
ADD CONSTRAINT fk_student
FOREIGN KEY (cisNum) REFERENCES STUDENT(cisNum)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE BORROW
ADD CONSTRAINT fk_book
FOREIGN KEY (bookId) REFERENCES BOOK(bookId)
ON DELETE CASCADE ON UPDATE CASCADE;

Question 2

Explain how entity integrity may prevent the insertion of a row of data
into a table.

Answer 2

Entity integrity prevents the insertion of a row into a table if the primary key value is either NULL or not unique. Since the primary key must uniquely identify each row and cannot contain null values, any attempt to insert a row that violates these rules will fail, ensuring data consistency and uniqueness.

Question 3

Explain how referential integrity may prevent the insertion of a row of data into a
table.

Answer 3

Referential integrity prevents the insertion of a row if the foreign key value does not match an existing primary key in the referenced table, ensuring valid relationships between tables.

Question 4

Explain what this procedure does.
CREATE OR REPLACE PROCEDURE ladystudents
As
CURSOR abc IS select * from student where gender
= ‘F’ ;
Females abc%ROWTYPE;
BEGIN
OPEN abc
LOOP
FETCH abc into Females;
DBMS_OUTPUT.PUT_LINE(Females.TutorName);
EXIT WHEN abc%NOTFOUND;
[2 marks]
Page 3 of 9
END LOOP;
CLOSE abc;
END;

Answer 4

Declares a cursor abc that selects all rows from the student table where the gender is ‘F’ (female).

Opens the cursor and enters a loop to process each row that matches the query.

Fetches each row into the Females variable, which holds a record from the student table.

Prints the TutorName of each female student using DBMS_OUTPUT.PUT_LINE.

Exits the loop when there are no more rows to fetch (abc%NOTFOUND).
Closes the cursor once all records have been processed.

The procedure selects all female students from the student table, loops through them, and prints each student’s TutorName.

Question 5

Analyse the logical tables below and answer the questions that follow.
Client(client_Id, name, address,gender, age)
Account(acc_no, acc_name, balance)
Withdrawal (acc_no, client_Id,amt_withdrawn, date)
Note: Primary keys are underlined and the primary key for withdrawal
table is foreign

Name the strong entities featured on the above logical tables.

Draw the ER diagram to indicate the relationship amongst the
participating entities

Question 6

Client(client_Id, name, address,gender, age)
Account(acc_no, acc_name, balance)
Withdrawal (acc_no, client_Id,amt_withdrawn, date)
Note: Primary keys are underlined and the primary key for withdrawal
table is foreign

Write an SQL statement to list the clients details who had made
withdrawals on 23 September 2020.

Question 7

Client(client_Id, name, address,gender, age)
Account(acc_no, acc_name, balance)
Withdrawal (acc_no, client_Id,amt_withdrawn, date)
Note: Primary keys are underlined and the primary key for withdrawal
table is foreign

Write an SQL statement to give the total number of clients a bank has.

Question 8

Client(client_Id, name, address,gender, age)
Account(acc_no, acc_name, balance)
Withdrawal (acc_no, client_Id,amt_withdrawn, date)
Note: Primary keys are underlined and the primary key for withdrawal
table is foreign
To add an extra column called city to the table client.

Question 9

Client(client_Id, name, address,gender, age)
Account(acc_no, acc_name, balance)
Withdrawal (acc_no, client_Id,amt_withdrawn, date)
Note: Primary keys are underlined and the primary key for withdrawal
table is foreign

To create a trigger to impose the maximum amount a client can
withdraw. The maximum allowed is P4000.00

Question 10

Client(client_Id, name, address,gender, age)
Account(acc_no, acc_name, balance)
Withdrawal (acc_no, client_Id,amt_withdrawn, date)
Note: Primary keys are underlined and the primary key for withdrawal
table is foreign

Question 11

Client(client_Id, name, address,gender, age)
Account(acc_no, acc_name, balance)
Withdrawal (acc_no, client_Id,amt_withdrawn, date)
Note: Primary keys are underlined and the primary key for withdrawal
table is foreign

To create a stored procedure for inserting record to the Account Table