KINGDOM OF SAUDI ARABIA Ministry of Higher Education Al-Imam Muhammad Ibn Saud Islamic University College of Computer and Information Sciences

‫ا ر ا‬ ‫ا‬ ‫وزارة ا م ا‬ ‫د ن ود ا‬ ‫ا م‬ ‫ب وا و ت‬ ‫وم ا‬

‫ود‬

Lab on PL/SQL Introduction to Databases (CS 340)

Problem 1 (20 points)
Table Client ClientID
10000 10001 10002 10003 10004

FirstName
Ahmad Ghazi Mohamed Al-Imam Mohamed University King Saud University

LastName
Abd Allah Al-Amri Al-Hamed

Phone
2134657 2345676 5467698 1020000

Address
Riyadh Riyadh Riyadh Riyadh

Type
Professor Professor Professor University University

Table Bank BankID
30001 30002

BankName
Al-Rajhi AL-Balad

Table Branch BranchID
A100 R100 R101

BankID
30002 30001 30001

BranchName BranchAddress
Olaya Olaya King Fahd Riyadh Riyadh Riyadh

Table Account AccountID BranchID
AC100 AC101 AC107 AC108 A100 A100 R101 R100

ClientID
10000 10001 10003 10004

Balance
20,000 120,000 300,000,000 200,000,000

Table Salary ClientID
10000 10001 10002

Salary
10000 9800 6500

UniversityID
10003 10003 10004 1/7
21 January 2008 at 10H

Dr. Anis Koubâa and Habeeb Allah Vulla

Question 1. Draw the relational

model of the database (2 points)

Question 2. Write a PL/SQL procedure ShowAccountInfo (AccountID) that takes as a parameter AccountID of an account and displays the Balance and the BranchID of this account. (4 points)

Dr. Anis Koubâa and Habeeb Allah Vulla

2/7

21 January 2008 at 10H

Question 3. Write a PL/SQL procedure ShowAllClients () that displays the list of all clients. (4 points)

Question 4. Write a PL/SQL function TransferSalary that takes as input parameter
• • First input: the Account ID of a University, Second input: the Account ID of a professor

and adds the salary to the balance of the account of the professor. You must check that the first Account ID