1. Create the database by using SQL DDL commands and fill the tables with at least...

  

 

Transcribed Image Text:

1. Create the database by using SQL DDL commands and fill the tables with at least 3 rows. (5marks) 2. Add a new column to the Blood bank table and name it as bloodBankName with varchar(25) as data type. (1marks) 3. Code solutions to the following problems as a relational- algebraic expression: (2mar ks) a. Retrieve the phone number of Blood Bank located in Suhar. b. Retrieve the donor's first name and last name that has blood type "A-". Section B: DML (4marks) 1. Code the following problems as sql commands: (2 marks) a. Retrieve the type of the blood which is highly requested by the hospitals. b. Retrieve the total number of donors according to the gender. 2. Assume that the following data are used to be inserted: Hospital( 'H123', 'Suhar hospital', 'Albatinah Governate', '+96884270911') Albatinah Governate','+96844442222','11233345') BloodRequest(222, '1-1-2020', 'AB', 'H123','BB1') Explain what would be violated by executing the following SQL statement: (2 BloodBank('BB1', marks) a. UPDATE BloodRequest set reqDate=112020; b. Delete from Hospital where hospitalld='H123'; Section C: PL/SQL (8marks) 1. Create a trigger to check the donor's dateOfBirth to be "before 1-1-2002 and not after the current date" before inserting or updating the values in Donor's table. marks ) 2. Create a cursor to hold the first name, last name and the role of Blood Bank Employees. (4 marks) is for PK Blood Sample bloodID:char(15) BloodAmount decimal(92) donationDate:date bloodType:char(3) FK1 donoriD char(5) FK2 bloodBankID char(5) PK Receptionist recepID:char(5) recepFirstName varchar(25) recepLastName: varchar(25) address: varchar(50) phone: varchar(14) has donates deals with has PK Donor donoriD:char(5) donorFirstName varchar(25) donorLastName varchar(25) dateOfBirth date gender varchar(7) address varchar(50) address varchar(50) phone:varchar(14) FK1 recep/D char(5) BloodBank PK blood BankID:char(5) address varchar(50) phone varchar(14) faxvarchar(14) receives has BloodRequest PK BloodReaID:char(9) regDate:date bloodType char(3) FK1 hospitallD char(5) FK2 bloodBankID char(5) BloodBankEmployee PK BBEID:char(5) 68EFirstName: varchar(25) BBELastName: varchar(25) address varchar(50) phone varchar(14) role varchar(50) FK1 bloodBankID char(5) hospital PK hospitallD:char(5) name varchar(50) address varchar(50) phone varchar(14) phone varchar(14) sends 1. Create the database by using SQL DDL commands and fill the tables with at least 3 rows. (5marks) 2. Add a new column to the Blood bank table and name it as bloodBankName with varchar(25) as data type. (1marks) 3. Code solutions to the following problems as a relational- algebraic expression: (2mar ks) a. Retrieve the phone number of Blood Bank located in Suhar. b. Retrieve the donor's first name and last name that has blood type "A-". Section B: DML (4marks) 1. Code the following problems as sql commands: (2 marks) a. Retrieve the type of the blood which is highly requested by the hospitals. b. Retrieve the total number of donors according to the gender. 2. Assume that the following data are used to be inserted: Hospital( 'H123', 'Suhar hospital', 'Albatinah Governate', '+96884270911') Albatinah Governate','+96844442222','11233345') BloodRequest(222, '1-1-2020', 'AB', 'H123','BB1') Explain what would be violated by executing the following SQL statement: (2 BloodBank('BB1', marks) a. UPDATE BloodRequest set reqDate=112020; b. Delete from Hospital where hospitalld='H123'; Section C: PL/SQL (8marks) 1. Create a trigger to check the donor's dateOfBirth to be "before 1-1-2002 and not after the current date" before inserting or updating the values in Donor's table. marks ) 2. Create a cursor to hold the first name, last name and the role of Blood Bank Employees. (4 marks) is for PK Blood Sample bloodID:char(15) BloodAmount decimal(92) donationDate:date bloodType:char(3) FK1 donoriD char(5) FK2 bloodBankID char(5) PK Receptionist recepID:char(5) recepFirstName varchar(25) recepLastName: varchar(25) address: varchar(50) phone: varchar(14) has donates deals with has PK Donor donoriD:char(5) donorFirstName varchar(25) donorLastName varchar(25) dateOfBirth date gender varchar(7) address varchar(50) address varchar(50) phone:varchar(14) FK1 recep/D char(5) BloodBank PK blood BankID:char(5) address varchar(50) phone varchar(14) faxvarchar(14) receives has BloodRequest PK BloodReaID:char(9) regDate:date bloodType char(3) FK1 hospitallD char(5) FK2 bloodBankID char(5) BloodBankEmployee PK BBEID:char(5) 68EFirstName: varchar(25) BBELastName: varchar(25) address varchar(50) phone varchar(14) role varchar(50) FK1 bloodBankID char(5) hospital PK hospitallD:char(5) name varchar(50) address varchar(50) phone varchar(14) phone varchar(14) sends