Lab 1Consider the following set of requirements for a university database that is used tokeep track of students' transcripts. (a) The university keeps track of each student's name, student number, socialsecurity number, current address and phone, permanent address and phone,birthdate, sex, class (freshman, sophomore, ..., graduate), major department,minor department (if any), and degree program (B.A., B.S., ..., Ph.D.). Some userapplications need to refer to the city, state, and zip of the student's permanentaddress, and to the student's last name. Both social security number and studentnumber have unique values for each student. (b) Each department is described by a name, department code, office number, officephone, and college. Both name and code have unique values for each department. (c) Each course has a course name, description, course number, number of semesterhours, level, and offering department. The value of course number is unique foreach course. (d) Each section has an instructor, semester, year, course, and section number. Thesection number distinguishes different sections of the same course that are taughtduring the same semester/year; its values are 1, 2, 3, ...; up to the number ofsections taught during each semester. (e) A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3,4 for F, D, C, B, A, respectively). Design an ER schema for this application; i.e., draw an ER diagram for that schema.Specify key attributes of each entity type and structural constraints (multiplicity) on each relationship type.Note any unspecified requirements, and make appropriate assumptions to make the specification complete. Lab 2Given the following SQL DDL statements for declaring the LIBRARY relational database schema. 1) Create and populate the tables in Oracle. 2) Write the SQL queries for the LIBRARY database listed below the DDL statements (Letters a-g). Upload these queries in a .zip file under the menu choice for "Lab 2 - Upload Here"; but first run them against your database...make sure you include the necessary attribute values when you insert "dummy" data into the tables. CREATE TABLE BOOK ( BookId CHAR(20) NOT NULL,Title VARCHAR(30) NOT NULL,PublisherName VARCHAR(20),PRIMARY KEY (BookId),FOREIGN KEY (PublisherName) REFERENCES PUBLISHER (PublisherName) ); CREATE TABLE BOOK_AUTHORS ( BookId CHAR(20) NOT NULL,AuthorName VARCHAR(30) NOT NULL,PRIMARY KEY (BookId, AuthorName),FOREIGN KEY (BookId) REFERENCES BOOK (BookId)ON DELETE CASCADE ); //PublisherName was added, as it was referenced by another table but did not exist as an attribute.CREATE TABLE PUBLISHER ( PublisherName VARCHAR(20) NOT NULL,Address VARCHAR(40) NOT NULL,Phone CHAR(12),PRIMARY KEY (PublisherName) );CREATE TABLE BOOK_COPIES ( BookId CHAR(20) NOT NULL,BranchId INTEGER NOT NULL,No_Of_Copies INTEGER NOT NULL,PRIMARY KEY (BookId, BranchId),FOREIGN KEY (BookId) REFERENCES BOOK (BookId)ON DELETE CASCADE,FOREIGN KEY (BranchId) REFERENCES BRANCH (BranchId)ON DELETE CASCADE); CREATE TABLE BORROWER ( CardNo INTEGER NOT NULL,Name VARCHAR(30) NOT NULL,Address VARCHAR(40) NOT NULL,Phone CHAR(12),PRIMARY KEY (CardNo) ); CREATE TABLE BOOK_LOANS ( CardNo INTEGER NOT NULL,BookId CHAR(20) NOT NULL,BranchId INTEGER NOT NULL,DateOut DATE NOT NULL,DueDate DATE NOT NULL,PRIMARY KEY (CardNo, BookId, BranchId),FOREIGN KEY (CardNo) REFERENCES BORROWER (CardNo)ON DELETE CASCADE,FOREIGN KEY (BranchId) REFERENCES BRANCH (BranchId)ON DELETE CASCADE,FOREIGN KEY (BookId) REFERENCES BOOK (BookId)ON DELETE SET NULL ); CREATE TABLE BRANCH ( BranchId INTEGER NOT NULL,BranchName VARCHAR(20) NOT NULL,Address VARCHAR(40) NOT NULL,PRIMARY KEY (BranchId) ); (a) How many copies of the book titled The Lost Tribe are owned by the library branchwhose name is "Sharpstown"? (b) How many copies of the book titled The Lost Tribe are owned by each librarybranch? (c) Retrieve the names of all borrowers who do not have any books checked out. (d) For each book that is loaned out from the "Sharpstown" branch and whose DueDateis today, retrieve the book title, the borrower's name, and the borrower's address. (e) For each library branch, retrieve the branch name and the total number of books loaned out from that branch. (f) Retrieve the names, addresses, and number of books checked out for all borrowerswho have more than five books checked out. (g) For each book authored (or co-authored) by "Stephen King", retrieve the title andthe number of copies owned by the library branch whose name is "Central". Lab 3Normalization:Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set offunctional dependencies F = { {A, B} -> {C}, {A} -> {D, E}, {B} -> {F}, {F} ->{G, H}, {D} -> {I, J} }. What is the key for R? Decompose R into 2NF, then 3NFrelations. Lab 4Short answer essay. Please, keep your answers short and to the point.1. Explain what is meant by a transaction. Why are transactions important units of operation in a DBMS?2 The consistency and reliability aspects of transactions are due to the ‘ACIDity’ properties of transactions(the definition of "ACID" comes directly from your text). Discuss each of these properties and how they relate to the concurrency control and recovery mechanisms. Give examples to illustrate your answer.3 What are some of the recovery mechanisms for system and media failure in a DBMS.4 Describe, with examples, the types of problem that can occur in a multi-user environment when concurrent access to the database is allowed.5 Give full details of a mechanism for concurrency control that can be used to ensure the types of problems discussed in Question 4 cannot occur. Show how the mechanism prevents the problems illustrated from occurring. Discuss how the concurrency control mechanism interacts with the transaction