Database Library System

 

CLO1	Explain advanced database concepts related to database design and administration  (C2,PLO1)
CLO2	Create database system using SQL Server for a given case study. (A3, PLO6)
CLO3	Apply the concepts and techniques related to database such as: Transaction management, optimization strategies, database Security, and advanced database programming for a given scenarios (C3, PLO2)

In this assessment you are required to:

• Design, develop and implement a solution to a business problem.

 

• Implement the solution in either MS SQL Server or Oracle.

 

• Document the solution as set out in the assignment requirements.

Case Study

E-Library Management System

A library in a university has approximately 100,000 reading materials available for reading. Everyone in university such as staff and students are given the privilege to loan the reading materials. The librarians are in-charge of handling the materials to ensure they are in good condition and available for loan. The librarian are staff of the university, but they could also be the students at the university.

The librarians ensure that the books will be available when someone wants to borrow them. Also, the librarians must know how many copies of each book are in the library or out on loan at any given time. The library database should keep information of books such as author, title, genre, and category. For each title in the library, a book description is kept in the database that ranges from one sentence to several paragraphs. The librarians on duty want to be able to access this database when anyone request information about a book.

Books are categorized based on tag colour such as yellow tag, red tag or green tag. The loan periods and fine rate for these books vary based on their categories. The library does not loan some books, such as reference books, journals, and student projects.  However, there is an exception whereby only lecturers are allowed to loan certain materials such as reference books. The librarians must be able to differentiate between books that can be loaned and those that cannot be loaned. Everyone in the university is allowed to borrow at any one time, a maximum of 10 books, they are required to pay fines for overdue books. The fines are charged based on the number of overdue days and varies for each category of books. In addition, the members are allowed to reserve books that are out on loan.  The library staff will notify those members when the books are available for loan. Reserved books will be put on-hold for 3 days for the person who reserved it, then will be release for others if the person who reserved it do not come over to loan it within 3 days. Each member is allowed to reserve a maximum of 3 books at one time.

The most active members of the library are defined as those who borrow at least ten times during the year. About 20 percent of the members are inactive in that they are members but seldom/never borrow any books.

Some books may have the same title; therefore, title cannot be used as a means of identification. Every book is identified by its International Standard Book Number (ISBN), a unique international code assigned to all books. Two books with the same title can have different ISBNs if they are written by different author or published by different publishers. Editions of the same book have different ISBNs. Each book can be written by more than 1 authors. In addition, since the library usually keeps multiple copies of the same book, each copy of the book must be uniquely identified despite having the same ISBN.

You are required to design a database covering following areas.

• Library System

Requirements:

 DESIGN

• Develop an ERD to support TSI’s activities. The ERD must shows entities, relationships and should be followed by logical design. Identify primary and foreign keys and show cardinality and optionality. Data must be in 3NF or higher unless it has been denormalized for performance reasons in which case an explanation must be given.
• Discuss an optimization strategy which is suitable for this case study

2. IMPLEMENTATION

• Implement the database according the ERD

 

• Implement One constraint which is suitable for this case study

 

• Implement One stored procedure which is suitable for this case study

 

• Implement One Trigger which is suitable for this case study
• Implement One optimization strategy which is suitable for this case study
• Create the following queries – this section is worth 10 marks in total. Student must be able to explain the queries and justify the approach taken. Marks will be reduced if student cannot explain the solutions.

Queries

 Find the category which has the highest number of books.

1. Show the books which have never been loaned by any person.

 

• List the person who had made more than 2 loans.

 

1. Create a query which provides, for each category and genre of book, the total number of books in the library.

Hint: you may wish to use rollup or cube statements with a query to show both detailed breakup & summary data.  Some marks will be awarded for the query structure, even if you cannot generate the totals.

1. Develop one additional query of your own which provides information that would be useful for the business. Marks will be awarded depending on the technical skills shown and the relevance of the query.

Assessment Criteria

 

No	Component
1	Design a)     ER Model b)     Optimisation Strategy
2	Implementation a)     Table design and constraints b)     Stored Procedure c)     Trigger d)     Optimisation strategy   Implementation e)     SQL Queries
3	Project Presentation