PL/SQL language
STATEMENTS :
A Bank is characterized by:
• Bank code: character on 10 positions,
• Company name: character on 10 positions
• Capital: Numeric with 3 positions after the comma,
• Address: character on 10 positions.
It has a set of Agencies. Each Agency is characterized by:
• Agency code: character in 10 positions,
• Label: character in 10 positions,
• Address: character on 10 positions,
• Latitude: character on 10 positions,
• Longitude: character in 10 positions.
An Agency has several clients. A Client is characterized by:
• Customer code: character on 10 positions,
• Name: character in 30 positions,
• Address: character in 30 positions,
• Telephone: character on 10 positions
A Client may have 1 or more Accounts. An Account is characterized by:
• Account number: character on 10 positions,
• Balance: Numerical with 3 positions after the comma,
• Flow rate threshold: Numeric with 3 positions after the decimal point,
• Balance date: Date.
• State: character on 10 positions.
There are two types of Account
• Current account
• Savings account.
An Account can have one or more Cards attached to it. A Card is characterized by:
• Card number: character on 16 positions
• Type of card: character on 10 positions
• Ceiling: Numerical with 3 positions after the decimal point
• Expiration date: Date
QUESTIONS :
1. Give the class diagram.
2. Give the different tables indicating the relationships between the different Tables.
3. Write a Withdraw function in PL/SQL that takes an account number as an argument and a withdrawal amount then performs the withdrawal operation by modifying the balance in the Account table. Note the withdrawal can only be made if balance – amount > threshold. The function returns the new balance after the withdrawal.
4. Write a Payment function in PL/SQL that takes as argument a number of the account and an amount then updates the balance in the Account table. The function returns the new balance after withdrawal.
5. Write a trigger to raise an exception in the event of a decrease in the balance of an account whose status is “blocked”.
6. To archive the history of Account table update operations, create a History table (NumCpt, operation_date, operation_type). Write a trigger allowing, after the execution of each operation on Account, to add a tuple regarding this operation to the History table.
7. Write a Withdrawal_Card function in PL/SQL which takes as argument a card number card and an amount then updates the balance in the Account table. Function returns the new balance after the withdrawal. The withdrawal can only be made if the balance allows him and the ceiling.
8. Write a trigger allowing to raise an exception in the case of an expiration of the date of a card.
9. Write a procedure that allows each Bank to display the branch code, wording of the Agency, name and balance of the Client having the largest balance in this Agency.
10. Write a PL/SQL procedure to display for all Agencies the three first highest Account balances, if there is not only one balance then display for the second and third, the same value as the first; and if he only two different balances exist, so display the same value for the third than the second.
PL/SQL language
STATEMENTS :
A Bank is characterized by:
• Bank code: character on 10 positions,
• Company name: character on 10 positions
• Capital: Numeric with 3 positions after the comma,
• Address: character on 10 positions.
It has a set of Agencies. Each Agency is characterized by:
• Agency code: character in 10 positions,
• Label: character in 10 positions,
• Address: character on 10 positions,
• Latitude: character on 10 positions,
• Longitude: character in 10 positions.
An Agency has several clients. A Client is characterized by:
• Customer code: character on 10 positions,
• Name: character in 30 positions,
• Address: character in 30 positions,
• Telephone: character on 10 positions
A Client may have 1 or more Accounts. An Account is characterized by:
• Account number: character on 10 positions,
• Balance: Numerical with 3 positions after the comma,
• Flow rate threshold: Numeric with 3 positions after the decimal point,
• Balance date: Date.
• State: character on 10 positions.
There are two types of Account
• Current account
• Savings account.
An Account can have one or more Cards attached to it. A Card is characterized by:
• Card number: character on 16 positions
• Type of card: character on 10 positions
• Ceiling: Numerical with 3 positions after the decimal point
• Expiration date: Date
QUESTIONS :
1. Give the class diagram.
2. Give the different tables indicating the relationships between the different Tables.
3. Write a Withdraw function in PL/SQL that takes an account number as an argument and a withdrawal amount then performs the withdrawal operation by modifying the balance in the Account table. Note the withdrawal can only be made if balance – amount > threshold. The function returns the new balance after the withdrawal.
4. Write a Payment function in PL/SQL that takes as argument a number of the account and an amount then updates the balance in the Account table. The function returns the new balance after withdrawal.
5. Write a trigger to raise an exception in the event of a decrease in the balance of an account whose status is “blocked”.
6. To archive the history of Account table update operations, create a History table (NumCpt, operation_date, operation_type). Write a trigger allowing, after the execution of each operation on Account, to add a tuple regarding this operation to the History table.
7. Write a Withdrawal_Card function in PL/SQL which takes as argument a card number card and an amount then updates the balance in the Account table. Function returns the new balance after the withdrawal. The withdrawal can only be made if the balance allows him and the ceiling.
8. Write a trigger allowing to raise an exception in the case of an expiration of the date of a card.
9. Write a procedure that allows each Bank to display the branch code, wording of the Agency, name and balance of the Client having the largest balance in this Agency.
10. Write a PL/SQL procedure to display for all Agencies the three first highest Account balances, if there is not only one balance then display for the second and third, the same value as the first; and if he only two different balances exist, so display the same value for the third than the second.
