Modules of Wonder Library
Wonder Library is a library for all ages. Wonder Library would like one relational database to be able to smoothly carry out their work in an organized way. The library has following important modules: Person, Employee, Member, Books, Publishers, Authors and Payment.
A Person can be an Employee or a Member. Employee can also be a Member. Details of a person such as Person ID, Name (First, Middle, Last), Address, Gender, Date of Birth, and Phone number (one person can have more than one phone number) are recorded. The Person ID should have the format “PXXX” where X is a number from 0 to 9 (Hint: you can use regexp_like() function).
Each member is issued a library card. The library card details such as card ID, date of issue, membership level (Silver or Gold) and other information are stored. Employee can be one of three classes: Library Supervisors, Cataloging Managers or Receptionists. The start date of employment is recorded.
Each member is classified as a Silver or Gold. A Guest log is maintained for the Gold members, which stores information such as the Gold member’s Card ID, guest ID, guest name, guest address, and guest contact information. There are temporary IDs that a person gets when they visit as a guest of a Gold member. Each guest ID is not unique in whole system, and only unique among all guest of a Gold member.
Books details such as book ID, book title and other information are stored. Books are classified as Class 1 books or Class 2 books. A publisher can publish more than one book, but a book is assumed to be published by a single publisher. The publisher details such as publisher ID and publisher name and other information (you can add assumptions) are stored. Author details such as author ID, author name and other information is stored. One book can have multiple authors and one author can write more than one book.
A receptionist maintains records of borrow details. Borrowing details are stored containing information about the borrowed book, the date of issue and due date of return, the details about the person borrowing the book, details of the receptionist and payment detail. Borrowed details are stored only when a person borrows a book. Payment detail such as Payment ID, payment method (cash, debit/credit card), payment time and amount are stored.
Answer 1
The superclass / Subclass relationship is important for the Wonder Library management system. This is because many superclasses such as Borrow are connected with the subclasses such as member, book, and many others. Another superclass example would be a book with subclasses such as publisher and author.
Answer 2
The other 5 rules that can be considered while designing the database are as follows:
- While populating the database, avoiding data duplication or non-uniform data is necessary. It creates confusion for the reader and other developers.
- The database must be designed to 3NF. It will help identify the fields dependent on the primary keys.
- It is not necessary to always avoid redundancy. If there is a need for performance, the database can be created using denormalization. Because, in normalization, the table is used using many tables, and in denormalization, the join gets reduced, increasing the performance.
- It is recommended to use primary and foreign keys while defining the tables. In addition, it can also be said that all the data constraints need to be followed.
- The database description must be simplified into simpler values while creating the logical database. That is; initially, the database must begin from 1NF
Answer 3
The Wonder Library management system is recommended for the used relational database because it allows the user to store the data organized. It is also used to track inventories, processes and transactions. It also manages a huge amount of customer information. The wonder library management system is a huge database system. Here, all the information needs to be stored so that there should be data consistency across all applications. These criteria can only be fullfed by the relational databases system such as oracle.
Details of Person, Employee, and Member
A relational database such as Oracle follows four crucial properties. These are Atomicity, consistency, isolation and Durability.
Publisher → Publisher ID , Publisher Name
Author → Author ID , Author Name
Book → Book ID, Publisher ID, Author ID, Author Name, Genre, Class
Payment → Payment ID, Payment Method, Payment Date, Amount
Person → Person ID, First Name, Middle Name, Last Name, Address, Gender, Date of Birth, Phone Number
Employee → Employee ID, Class, Date of Employment
Member → Member ID, Title
Borrow → Borrow ID , Book ID, Member ID, Employee ID, Payment ID, Date of Issue, Date of Return
Library Card → Card ID, Member ID, Issue Date, Membership Level
Guest → Guest ID, Guest Name, Address, Phone Number
a. Dependency diagram (Solution for Phase 3 B).
Answer C
create database Wonder_Library;
use Wonder_Library;
Create table Publisher(
Publisher_ID varchar(10) Primary Key,
Publisher_Name varchar(55)
);
Create table Author (
Author_ID varchar(10) Primary Key,
Author_Name varchar(50)
);
create table Book(
Book_ID varchar(10) Primary Key,
Publisher_ID varchar(10),
Author_ID varchar(10),
Author_Name varchar(55),
Genre varchar(10),
Class varchar(15),
FOREIGN KEY (Publisher_ID) REFERENCES Publisher(Publisher_ID),
FOREIGN KEY (Author_ID) REFERENCES Author(Author_ID)
);
create table Payment(
Payment_ID varchar(10) Primary Key,
Payment_Method varchar(20),
Payment_Date date,
Amount decimal(4,2)
);
Create table Person(
Person_ID varchar(10) Primary Key,
First_Name varchar(55),
Middle_Name varchar(55),
Last_Name varchar(55),
Address varchar(85),
Gender varchar(55),
Date_of_Birth date,
Phone_Number int
);
create table Employee(
Employee_ID varchar(10) Primary Key,
Class varchar(25),
Date_of_Employment date
);
create table Member(
Member_ID varchar(10) Primary Key,
Title varchar(55)
);
Borrow_ID varchar(10) Primary Key,
Book_ID varchar(10),
Member_ID varchar(10),
Employee_ID varchar(10),
Payment_ID varchar(10),
Date_of_Issue date,
Date_of_Return date,
FOREIGN KEY (Book_ID) REFERENCES Book(Book_ID),
FOREIGN KEY (Member_ID) REFERENCES Member(Member_ID),
FOREIGN KEY (Employee_ID) REFERENCES Employee(Employee_ID),
FOREIGN KEY (Payment_ID) REFERENCES Payment(Payment_ID)
);
Create table Library_Card(
Card_ID varchar(10) Primary Key,
Member_ID varchar(10) ,
Issue_Date date,
Membership_Level varchar(25),
FOREIGN KEY (Member_ID) REFERENCES Member(Member_ID)
);
Guest_ID varchar(10) Primary Key,
Guest_Name varchar(55) ,
Address varchar(85) ,
Phone_Number int
);
Answer D
- Top Gold Member
CREATE VIEW `TopGoldMember` AS
SELECT P.First_name, P.Last_name, E.Date_of_Employment as Membership_Enrollment
FROM Person P
join Employee E on P.Person_ID = E.Employee_Id
join Borrow BR on E.Employee_ID = BR.Employee_ID
WHERE BR.book_ID >(Select count(BR.Book_ID) from borrow);
Select * from `TopGoldMember`;
- Popular Books
CREATE VIEW `PopularBooks` AS
Select B.Book_ID, B.publisher_Id, B.Author_id, B.Author_name, B.Genre, B.Class from Book B
join Borrow BR on B.Book_ID = BR.Book_ID
where BR.Borrow_ID = (Select (Borrow_ID) from Borrow group by Book_ID order by count(Borrow_ID) desc Limit 1);
Select * from `PopularBooks`;
- Potential Gold Member
Create View `PotentialGoldMember` AS
Select concat(P.First_name, ”, P.Last_name) as name, P.Phone_Number, P.Person_Id, LC.Member_ID from person P
join Member M on P.Person_ID = M.Member_ID
join library_card LC on M.Member_ID = LC.Member_id
where LC.Membership_Level = ‘Silver’;
select * from `PotentialGoldMember`;
- Popular Author
create view `PopularAuthor` as
select A.Author_ID, A.Author_Name from Author A
join Book B on A. Author_ID = B.Author_id
join Borrow BR on B.Book_ID = BR.Book_ID
Where BR.Borrow_ID = (Select (Borrow_ID) from Borrow group by Book_ID order by count(Borrow_ID) desc Limit 1);
select * from `PopularAuthor`;
Answer E
- SQL query
SELECT *
FROM library_card
WHERE Issue_Date >= DATE_ADD(NOW(),INTERVAL -90 DAY) ;
- SQL query
Select concat(P.First_name, ”, P.last_name) as name from Person P
join Employee E on P.Person_ID = E.Employee_ID
join borrow BR on E.Employee_ID = BR.Employee_ID
where BR.Date_of_issue = DATE_ADD(NOW(),INTERVAL -30 DAY) ;
- SQL Query
select (count(BR.Borrow_ID)/5) as Average_no_of_book_borrowed from Borrow BR
join Member M on BR.Member_ID = M.Member_ID
Where M.Title = ‘gold’
group by BR.Book_ID;
- SQL query
Select P.Publisher_Name , B.Book_Id from Publisher P
join Book B on P.Publisher_ID = B.Publisher_ID
group by P.Publisher_Name;
- SQL Query
Select Borrow_ID from Borrow where Date_of_issue != DATE_ADD(NOW(),INTERVAL -150 DAY) ;
- SQL query
Select BR.Member_ID from Borrow BR
join book b on BR.BOOK_ID = B.BOOK_ID
join Publisher P on B.Publisher_ID = P.Publisher_ID
Where P.Publisher_Name = “Leonard”;
- SQL query
Select count(Member_ID) as total_Guest from library_card where Membership_Level = ‘Gold’;
- SQL Query
Select year(date_of_issue)as year, count(Borrow_ID) as books_borrowed from Borrow group by year(Date_of_issue) order by count(Borrow_ID) desc limit 1;
- SQL Query
Select BR.Member_ID from Borrow BR
join book b on BR.BOOK_ID = B.BOOK_ID
Where b.Author_Name = ‘Eva’;
- SQL Query
Select LC.Member_ID from Library_card LC
join Member M on LC.Member_id = M.Member_ID
join Borrow BR on M.Member_ID = BR.Member_ID
join employee e on BR.Employee_ID = E.Employee_ID
Where month(E.Date_of_Employment) = Month(LC.issue_date) and LC.membership_level = ‘gold’;
- SQL Query
Select Member_ID from library_card where Issue_Date = DATE_ADD(NOW(),INTERVAL -5 YEAR);
- SQL Query
Select LC.Member_ID, count(BR.Borrow_ID)as number_of_books_borrowed from Library_card LC
join Member M on LC.Member_id = M.Member_ID
join Borrow BR on M.Member_ID = BR.Member_ID
join employee e on BR.Employee_ID = E.Employee_ID
Where LC.membership_level = ‘gold’ and Date_of_Issue =DATE_ADD(NOW(),INTERVAL -1 YEAR);
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
