Pleae help to prove the full typed, currently sql query(sucess to run in Mysql),
ID: 3735534 • Letter: P
Question
Pleae help to prove the full typed, currently sql query(sucess to run in Mysql), E-R digram, and some word explain following the Introduction and requirement blew, thank you so much.
Intriduction:
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.
Project Requirements:
1. MODEL REQUIREMENT
Identify the entities, attributes and relationship between the entities and draw the conceptual model.
2. Table 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.
3. DATA (created by yourself) LOAD requirement
Insert at least 10 rows in all the tables created.
4. TEST SYSTEM requirement(show answer as five sql queries that used)
Test your table entities individually and against each other by designing five database queries (including simple and complex joins and subqueries, HAVING Key word, WHERE Key word and GROUP BY clause). Use both valid and invalid data to perform your test queries.
5. ER-digram requirement
Draw a E-R digram about the schma you use for the project
Project anlysis explanation
Explanation / Answer
1] Model Requirements of Library :-
Authorization and Authentication Module This module is used by user which means librarian in the library. They need to login to the system using their id and password. In order to distinguish the user’s level, user can access to different module when successfully login. For example, only admin level users are able to access the report module.
Book Maintenance Module :- Book Module can access by any user from all levels. This module can used to maintain the book inventory record such as search, add and edit. In addition, we can generate the barcode for particular book and print it out so that librarian can stick the barcode on the book cover.
Publisher Maintenance Module This module allows user to add and edit the book’s publisher. Publisher is used when register a new book.
Book Transaction Module Book Transaction module is a main module in Library Management System.
When member wants to borrow books, return books or they want to register lost book, it is all under book Transaction module. This module can be accessed by normal user or admin user. When member wants to borrow a book, librarian needs to scan in their member id. After that, librarian will scan their
book’s barcode id. If the book is under reservation, the book is not available to rent. For return module, librarian just needs to scan the book’s barcode id, and confirm the rental detail with user. If the rental detail is correct, return module can be complete if no any fine issued.
Report Module Report module is the main module for admin user. It is because normal user is not allowed to view the report. The report divided into 3 types. First one is transaction report which can let admin views the book transaction happen on particular date such as rental report and return report.
Top10 Report is the top rental rate’s book. Activity Log File is a log which records every process in the Library Management System such as login / logout activity, register new book, new member or edit information or a member. All the activity done by every user will be record so that when system crash, admin or system admin are able to check the activity that may crash the system.
Data Flow Diagram
Context free Diagram
size exceeds
LEVEL 0 DFD
size exceeds
LEVEL 1 DFD BOOK rental Module
size exceeds
Table Requirements
Normalization :- 1st Normalization
RentalInfo (UserID, ISBN, BookTitle, Author, PublisherID, PublisherName, Language, Category, Description, BookCover, BarCodeID, Status, PurchasePrice, PurchaseDate, UserName, UserAddress,
UserPhone, UserIC,UserRegDate, AvailableBook, UserPass, UserPhoto, UserEmail, UserExpiredDate, DateRented, DateReturned, DateDue, TotalFine)
Reservation (UserID, BarCodeID, DateRented)
LibraryDetail (Libno, libname, libadd1, libadd2,libposcode,libstate, libtown, libphone, libfax, libemail, libweb)
ActivityLog (LogID, LogEmp, LogContent)
LostBook (ID, BarCodeID*, UserID*, ISBN*, LostDate)
Admin (Admin_ID, AdminName, AdminLevel, Password, AdminID, AdminContact, AdminEmail, AdminAddress)
2 nd Normalization
RentalInfo (UserID, ISBN, BookTitle, Author, PublisherID, PublisherName, Language, Category, Description, BookCover, BarCodeID, Status, PurchasePrice, PurchaseDate, DateRented, DateReturned, DateDue, TotalFine)
User (UserID, UserName, UserAddress, UserPhone, UserIC,UserRegDate, AvailableBook, UserPass, UserPhoto, UserEmail, UserExpiredDate)
Reservation (UserID, BarCodeID, DateRented)
LibraryDetail (Libno, libname, libadd1, libadd2,libposcode,libstate, libtown, libphone, libfax, libemail, libweb)
ActivityLog (LogID, LogEmp, LogContent)
LostBook (ID, BarCodeID*, UserID*, ISBN*, LostDate)
Admin (Admin_ID, AdminName, AdminLevel, Password, AdminID, AdminContact, AdminEmail, AdminAddress)
3 rd Normalization
RentalInfo (UserID, ISBN, BarCodeID, DateRented, DateReturned, DateDue, TotalFine)
Book (ISBN, BookTitle, Author, PublisherID, Language, Category, Description,
BookCover)
Publisher (PublisherID, PublisherName)
BookCopy (BarCodeID, ISBN, Status, PurchasePrice, PurchaseDate)
User (UserID, UserName, UserAddress, UserPhone, UserIC,UserRegDate, AvailableBook, UserPass, UserPhoto, UserEmail, UserExpiredDate)
Reservation (UserID, BarCodeID, DateRented)
LibraryDetail (Libno, libname, libadd1, libadd2,libposcode,libstate, libtown, libphone, libfax, libemail, libweb)
News(ID, date, content)
ActivityLog (LogID, LogEmp, LogContent)
LostBook (ID, BarCodeID*, UserID*, ISBN*, LostDate)
Admin (Admin_ID, AdminName, AdminLevel, Password, AdminID, AdminContact, AdminEmail, AdminAddress)