PLEASE HELP TO ANSWER THIS PROJECT

Database and Tools - Database: Oracle Database 19. Same DB that we use in class. The DB and user information already provided to each student. All work should be done in the provided DBluser account. There is no need to install the DB. 0 All work needs to be done and delivered in the class DB provisioned and running on AWE. Environment host is ec2-3-15-1J'9-95.us- east-2 .compute.anazonaws.com . This is same environment that hosts class data-sets. Your user is UTD NETID. - ER Diagrams: MS Visio Tables Names - Naming Convention We will be using grading scripts to validate correctness of the data in your tables. All tables should be created by following naming convention below: You should narne yourTempi'lnitial load Table names as following: 1. project1_books_load 2. project1_book_copies_|oad 3. project1_borrower5_load 4. project1_|ibrary_branch_load Final tables (see DB schema for full list of names}: 1. project1_hooks 2. project1_hook_authors 3. ......................... 4. project1_nes Final tables columns should have names as specied in the DB design part. Initial Load Files There are four les provided for the initial load. The les will be available on eLearning. By loading data into application tables. we will practice following important concepts: . Ability to load various le formats into DB using SQL developer guided import interface 0 Ability to understand "raw\" data I Ability to translate given data into normalized version and match application DB design Files: - Books le: Books information, such as ISBN. authors. title. etc. - Book copies le: number of copies of each book per library location - Borrowers le: borrowers information. such as name. SSN {dummy}. address. etc. - Library branches le: branches address information Important: when opening raw les. use simple text editors {for example Notepad++i, don't use Excel. Excel might modify raw data to match its understanding of how data is delimited. Functional Requirements and grading Design and DB Architecture [105 points] The initial import of the dataset is considered to be an administration task and can be performed by utilizing SQL Developer Import functionality while data normalization should be performed using SQL scripts. Dellverahles: 1. ER diagram 2. Database objects create statements: create table. foreign keys. indexes. etc. Data Load. Normalization. data generation [135 points] 1. Load dataset files [provided with the project requirements] utilizing SOL Developer guided interface. Pay attention to the le formati'delimiter. SQL Developer is able to recognize proper delimiter when selecting "text\" in input le options. 2. Write 50L scripts to populate target application tables while normalizing the data loaded in #1. 3. Write 50L to generate: 1. At least 500 books check-outs for at least 200 different borrowers and 100 different books 2 At least 50 lines records for 20 different borrowers Deliverables: 1. Initial load and normalized tables should be present in your DB account 2. SQL statements Book Search and Availability [30 points] Book search functionality should allow setting a specific library branch location context or performing it globally for all locations. Write SQL that will provide a single search functionality to locate a book given any combination of ISBN, title, and/or Author(s). Your query should support substring matching. For example, search for "will" should return results whose title include "will" or "willing", or whose author name contains "will", "Willy", "William", etc. Important: the SQL statement should search normalized tables, don't search tables created to load original files. Search should display the following information for each book in the result set: . ISBN . Book title . All book author(s) . Availability status at the currently selected branch (if branch is selected) Deliverables: SQL statements, Screen Shots with output of the search for 3 search inputs.Reports [30 poi nts] Design and write SQL for 3 reports. The reports can accept input parameters, for example branch or date. - Each report will have information presented from at least 3 tables . Each report will have aggregation and accumulation functionality Examples of the reports {don't use example reports]: a Top it) most popular books in the last month - Top 1D books that were checked in late Deliverable-s: SQL statement for each report High Level Data Model The high level schema for this project is provided below. Not all attributes are listed, you will need to create ER diagram matching high level design below. BOOK ll 8 OK AUTHORS EH LIBRARY_BRANCH Branch Id Branch_name Adda\" BOOKJZOPIES Lmu Ian-- BOOK_LOANS Project report Submit project report listing all deliverables into eLeaming. All data should be present in your BB accounts by project due date, you should not modify data in the project related tables after project due date has passed. I strongly recommend using markdown editor to generate your report. One example of such editor is Typora. Alternatively you can use Microsoft Word. Both. project report and ER diagram should be packaged into single le and uploaded into eLeaming by due date. There will be 6 hours grace period to upload the deliverables, it would give you plenty of time to deal with unexpected issues