Please Prove the Currently, typed, Detail answer following the introduction blew, and make sure that all the querys can run sucessful in Mysql software, thank you so much!

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Introudction

Library database Case Description

A library has a collection of 120000 items. Most of them, but not all, may be borrowed. There are about 80000 different titles. Thus, some title has multiple copies. Each title has an identifier, a name and a type. There may be a lot of descriptive information attached to each title. These may include information about the authors, the publisher and the number of pages. Each descriptive piece of data has a name. For example, the name of the publisher information is PUBL. What descriptive data may be used in describing a title depends on the type of the title. The descriptive data used for books may not be the same than the ones used for movies. Some of the elements are mandatory, some are optional. Introduction of new descriptive data elements should be made possible without changing the structure of the database.

There are also items specific information that do not depend on the type of the item. They include where the item is located (department, compartment, self) and when and with what price it has been purchased. Each item has a unique number.

The library has about 20000 customers. Standard customer information like name and address should be registered of all customers. Each customer has been assigned a unique customer identifier. Customers may borrow items from the library. They may also make reservations for the titles. When an item is returned the system should inform the first customer in reservation queue of the return. When an item is borrowed the date of borrowing and the latest date of return is registered. If the item is returned a marking is made in the borrowing record. Records of the borrowing are never deleted.

The following needs to be taken care of for the project to be successfully implemented.

REQUIREMENT ANALYSIS

Write questions (at least 5) as a pagage that your data team should ask the client in order for the business rules of their library system to be fully understood.

CONCEPTUAL MODEL

Identify the entities, attributes and relationship between the entities and draw the conceptual model.

APPLICATION Requirement

Design and normalize the tables. Identify the column names, foreign keys and the data types.

Use the CREATE TABLE command to create tables in the database.

LOAD SAMPLE DATA

Insert at least 10 rows in all the tables created.

TEST SYSTEM(show the against queries you use)

Test your table entities individually and against each other by designing five database queries.Make use of simple and complex joins and subqueries between your tables.Use both valid and invalid data to perform your test queries. Also use HAVING, WHERE and GROUP BU clause

E-R requirement

Draw the E-R digram and write other project explanation.