05 May In this project, you will use the knowledge you acquired throughout the course to build a simple database and query it to extract information from it. You will create tables a

About this Assignment

This is a Database Programming course. This course covers advanced topics in databases. It starts by reviewing basic knowledge on databases and ends with advanced database concepts like security.

In this project, you will use the knowledge you acquired throughout the course to build a simple database and query it to extract information from it. You will create tables and relationships among them, in addition to the necessary keys and indexes. The next step will be to populate the database with suitable data. Populating the tables with sufficient and appropriate example data is an important step in testing and validating your design. When your database is ready, you will write SQL queries to retrieve information.

Note: This assignment was created with  MySQL in mind. Therefore, dates, numbers, etc. have been set up with that tool in mind.

Upon completion of this project, you will be able to:

· Write SQL queries to create tables

· Write SQL queries to create relationships among tables

· Identify indexes and create them in a database

· Write queries to extract important information from a database

Prompt

In this project you will build a database for a public library. This database is aimed to collect and analyze information about the clients' reading interests. The project concentrates only on books and the clients' interests in books. The analyses that will result from this project will be used by the library's management to decide on the future purchasing policy.

A. Write the SQL statements in order to create the tables for the database. Use the Entity Relationship Diagram (ERD) of the database shown in Figure 1. For simplicity, we are assuming in this project that a book cannot be written by more than one author. You need to create the tables as well as the required constraints, including the keys (primary and foreign), and the relationships between tables.

B. Populate your database with the sample set of data given to you in the tables below the assignment prompts.

C. Write the following queries to retrieve the information detailed below.

1. Display all contents of the Clients table

2. First names, last names, ages and occupations of all clients

3. First and last names of clients that borrowed books in March 2018

4. First and last names of the top 5 authors clients borrowed in 2017

5. Nationalities of the least 5 authors that clients borrowed during the years 2015-2017

6. The book that was most borrowed during the years 2015-2017

7. Top borrowed genres for client born in years 1970-1980

8. Top 5 occupations that borrowed the most in 2016

9. Average number of borrowed books by job title

10. Create a VIEW and display the titles that were borrowed by at least 20% of clients

11. The top month of borrows in 2017

12. Average number of borrows by age

13. The oldest and the youngest clients of the library

14. First and last names of authors that wrote books in more than one genre

As you work on these queries, create indexes that will increase your queries' performance.

You must include comments in your code that address the purpose of your query and explains each step. Save your queries and results in a plain-text file that you will submit as your assignment.

Author table:

Book table:

Client table:

ClientID	ClientFirstName	ClientLastName	ClientDoB	Occupation
1	Kaiden	Hill	2006	Student
2	Alina	Morton	2010	Student
3	Fania	Brooks	1983	Food Scientist
4	Courtney	Jensen	2006	Student
5	Brittany	Hill	1983	Firefighter
6	Max	Rogers	2005	Student
7	Margaret	McCarthy	1981 Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline. Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax. Do you need an answer to this or any other questions? Order Plagiarism Free Answer Here About Wridemy We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers. How It Works To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline. Are there Discounts? All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure. Hire a tutor today CLICK HERE to make your first order Related Tags Academic APA Writing College Course Discussion Management English Finance General Graduate History Information Justify Literature MLA