Extra Marks

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)

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

Answer 1

Entity Integrity:
ALTER TABLE STUDENT
ADD PRIMARY KEY (cisNum);

ALTER TABLE BOOK
ADD PRIMARY KEY (bookId);

ALTER TABLE BORROW
ADD PRIMARY KEY (cisNum, bookId);

Referential Integrity:
ALTER TABLE BORROW
ADD FOREIGN KEY (cisNum) REFERENCES STUDENT(cisNum);

ALTER TABLE BORROW
ADD FOREIGN KEY (bookId) REFERENCES BOOK(bookId);

Question 2

Given the following database Schema answer the questions that
follow:
STUDENT(cisNum, name, gender,age )
BOOK(bookId, title)
BORROW (cisNum, bookId, dateBorrowd, dueDate)

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

Answer 2

Entity integrity ensures unique primary keys, rejecting insertion attempts with duplicate primary key values, preserving uniqueness.

Question 3

Given the following database Schema answer the questions that
follow:
STUDENT(cisNum, name, gender,age )
BOOK(bookId, title)
BORROW (cisNum, bookId, dateBorrowd, dueDate)

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

Answer 3

Referential integrity ensures foreign key values match primary key values in referenced tables, rejecting insertions without matching keys.

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;
END LOOP;
CLOSE abc;
END;

Answer 4

The procedure fetches and outputs the TutorName of female students using a cursor, iterating through the result set and printing the TutorName for each row.

Question 5

Explain what this trigger does.
CREATE OR REPLACE PROCEDURE upadareMaleAges
As
CURSOR abc IS select * from student where gender =
‘M’ ;
Females abc%ROWTYPE;
BEGIN
OPEN abc
LOOP
FETCH abc into Males;
Update student set age = Males.age + 2 where cis
= Males.cis;
EXIT WHEN abc%NOTFOUND;
END LOOP;
CLOSE abc;
END;

Answer 5

The procedure updates the ages of male students by adding 2 to their current age in the STUDENT table.

Question 6

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
a) Name the strong entities featured on the above logical tables

Answer 6

Strong entities are Client and Account.

Question 7

Write SQL statements to do the following
i. write an SQL to create the table withdrawal

Answer 7

CREATE TABLE Withdrawal (
acc_no INT,
client_Id INT,
amt_withdrawn DECIMAL(10, 2),
date DATE,
PRIMARY KEY (acc_no),
FOREIGN KEY (client_Id) REFERENCES Client(client_Id)
);

Question 8

Write SQL statements to do the following
SQL Statement to List Clients who Made Withdrawals on 23 September 2020:

Answer 8

SELECT * FROM Client
WHERE client_Id IN (
SELECT client_Id FROM Withdrawal
WHERE date = ‘2020-09-23’
);

Question 9

Write SQL statements to do the following
SQL Statement to Give Total Number of Clients:

Answer 9

SELECT COUNT(*) AS TotalClients FROM Client;

Question 10

Write SQL statements to do the following
SQL Statement to Add City Column to Client Table:

Answer 10

ALTER TABLE Client
ADD city VARCHAR(50);

Question 11

Write SQL statements to do the following
Trigger to Impose Maximum Withdrawal Amount:

Answer 11

CREATE OR REPLACE TRIGGER maxWithdrawal
BEFORE INSERT ON Withdrawal
FOR EACH ROW
BEGIN
IF :NEW.amt_withdrawn > 4000.00 THEN
RAISE_APPLICATION_ERROR(-20000, ‘Maximum withdrawal amount exceeded’);
END IF;
END;