Scribd
Upload
8 views3 pages

City Library Database System Design

The document describes the requirements for a database system to manage the operations of a city library. It outlines the key entities like documents, branches, readers and transactions. Documents can be books, journals or conference proceedings with additional attributes. Branches hold document copies that can be reserved and borrowed by readers. Readers are registered with attributes and can have 10 documents checked out at a time. Transactions include reservations, borrowing, returning and fines for late returns. The task is to design, implement and document the database and application programs to manage the library system.

Uploaded by

Vignesh V
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views3 pages

City Library Database System Design

The document describes the requirements for a database system to manage the operations of a city library. It outlines the key entities like documents, branches, readers and transactions. Documents can be books, journals or conference proceedings with additional attributes. Branches hold document copies that can be reserved and borrowed by readers. Readers are registered with attributes and can have 10 documents checked out at a time. Transactions include reservations, borrowing, returning and fines for late returns. The task is to design, implement and document the database and application programs to manage the library system.

Uploaded by

Vignesh V
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

CS 631 - DATA MANAGEMENT SYSTEMS DESIGN

PROJECT

Purpose of this project

Analyze, design, implement, and document a database system application. You will use
the methodology for database development learned in class. The system must be
implemented on a DBMS with any language as a host-language for the application (use
of Java and accessing the database using JDBC is one possibility.) The system must be
menu-driven and include the basic functionality described below.

The City Library


The following specifications are intended as a guide; they are not the complete
specifications. These are intended to be a basis for you to get started in the right
direction in designing your system. You as the designer must analyze and decide what
other details or features should be specified for your system. Thus, individual group
implementations will differ in terms of design and implementation styles. Every group has
to mention clearly in its report what other specifications are assumed.

Data and Functional Requirements


Consider the operations of a library system in the different neighborhoods of a city. The
library has many readers who borrow books from one of its many branches.

1. The library records information about all documents that are available in its system.
Each document is identified by a unique number (DId.) It also has a title, a publisher,
a publication date, and can be one of three different types: book, journal volume, or
conference proceedings.

2. For each document type we need to store additional information. For instance, for
books we need to record their ISBN, and for conference proceedings we need to
record the date and location of the conference, and the proceedings chair(s). A journal
volume has a number and can have several issues (up to 10). A journal issue has a
number (1, 2, 3, …) that uniquely identifies the journal issues of the same journal
volume. A journal has a single chief editor who usually lasts for many years and
supervises many consecutive journal volumes. Journal issues might have one or more
guest editors and a scope that need to be recorded.

3. Each document has a single publisher and the publisher address is also recorded.
4. Books have authors. Information about the authors of books is maintained.

5. Book authors, editor (both, journal chief editors and journal issue guest editors), and
conference proceedings chairs are identified by a number (person Id---PId) and their
name is also recorded in the database

6. The library system contains several branches, which are identified by a number (BId).
We also need to store the name and the location of each branch. Each branch of the
library holds a number of copies of a particular document. Each copy of the same
document kept by the same library branch is numbered from 1 to n. The total number
of copies of each document in the library is needed. We also need to record the
position of every copy in a branch. It is encoded by a string of 6 alphanumeric
characters (e.g. 001A03 means the third shelve of bookcase A03).

7. The library system keeps track of all readers who are uniquely identified by Reader Id
(RId). Each reader has a name, an address, a phone number and a type ("student",
"senior citizen", "staff" etc.) A reader has to be registered in the database before
borrowing a document.

8. Readers have access to the online catalogue of documents and may reserve specific
copies of documents if they are available. A reserved copy has to be picked up before
6 pm; otherwise, the reservation is cancelled. A reader cannot borrow or reserve more
than 10 documents. Documents are reserved through a reservation transaction. With
one reservation transaction a reader can reserve multiple documents. Each reservation
transaction has a unique reservation number (RNum).

9. Borrowing is defined as taking out a copy of a document on one date and time
(BDateTime) and returning it a maximum of 20 days later. RDateTime is the date and
time on which the copy of the borrowed document is actually returned. (RDateTime
is NULL if the document has not yet been returned). Documents have to be returned
to the branch from which they are borrowed. Documents are borrowed through a
borrowing transaction. With one borrowing transaction a reader can reserve multiple
documents. Each borrowing transaction has a unique borrowing number (BNum).

10. The same copy of a document can be reserved and/or borrowed by the same reader
several times.

11. Documents that are not returned on time are fined at a rate of 20 cents for each day
after the due date.

12. A copy of a document cannot be lent to more than one reader at a time, but a reader
can borrow multiple copies of documents.

Your task is to design the database and application programs that will help manage the
document inventory and the day-to-day processing. Note that many functions are left out
in order to reduce the size and the complexity of the project.
There are a number of processes that are relevant:

_________________

[Query] This process allows library employees and readers to query the database with
regard to documents. It also allows library employees to retrieve information about the
readers and the documents they have borrowed.

[UpdateReader] This process modifies the database appropriately regarding new reader
registration and old reader registration cancellation.

[UpdateDoc] This process modifies the database appropriately regarding new document
copies obtained by the different branches of the library and old documents that are being
lost, destroyed or donated.

[ReserveDoc] This process modifies the database appropriately, regarding the documents
being reserved. It is typically the operation done when a reader reserves one or more
documents.

[BorrowDoc] This process modifies the database appropriately, regarding the documents
being borrowed. It is typically the operation done when a reader borrows one or more
documents.

[ReturnDoc] This process modifies the database appropriately regarding the documents
being returned. It is typically the operation done when a reader returns documents. It also
computes the fine to be paid by the reader if a returned document is delayed.

[Admin] This process comprises management report features (aggregated information


and statistics, popular documents etc.).

_________________

You can make further assumptions but: (a) they should not be in contradiction with the
assumptions described above, and (b) they have to be clearly stated in your report.

Common questions

Powered by AI

[Query] allows database searches for documents and reader information. [UpdateReader] deals with reader registration updates, [UpdateDoc] with document additions, removals, and updates. [ReserveDoc] handles document reservations, and [BorrowDoc] processes borrowing transactions. Together, these processes ensure accurate and efficient updates to the library's database, allowing it to function smoothly .

Each entity in the library database is assigned a unique identifier: documents are identified by a DId, branches by a BId, readers by an RId, and personnel such as authors and editors by a PId. This approach ensures data integrity and uniqueness across various entities, facilitating ease in tracking, updating, and managing records within the database system, crucial for operational efficiency .

The system computes a fine of 20 cents per day for each day a document is overdue, which integrates with the return transaction in the database management architecture. The ReturnDoc process accounts for the actual return date and calculates fines accordingly, ensuring readers are notified of their dues and the system maintains accurate financial records related to overdue fines .

The library system differentiates documents by categorizing them into book, journal volume, and conference proceedings. For books, the ISBN is recorded. Journal volumes have a volume number and can have multiple issues, each uniquely identified by a number. Journal issues may have guest editors and a scope, and journals have a chief editor noted in the system. Conference proceedings record the conference's date, location, and chair(s).

Challenges include ensuring unique and consistent position encoding while managing multiple branches. The 6-character alphanumeric string must accommodate practical sorting and retrieval within branch specifications. Addressing these involves setting a standard encoding format per branch (e.g., 001A03), ensuring that position data integrates seamlessly with database inventory queries and updates .

Constraints include that a reader must be registered in the database to borrow documents, and they cannot reserve more than 10 documents at a time. Reservations must be picked up before 6 pm. For borrowing, each transaction is uniquely identified by a borrowing number and must be returned to the original branch within 20 days to avoid fines. The system tracks reservation and borrowing transactions to enforce these constraints .

The library database requires the name and a unique PId for authors and editors. This information is necessary to keep track of individuals who contribute to the library's document repository, manage contributions, and facilitate communication regarding editorial processes and author management, enhancing organizational efficiency .

The library system tracks the number of copies per document at each branch using unique copy numbers, ensuring that each is correctly positioned and accounted for. Potential data integrity issues include incorrect synchronization of copy counts and potential duplication or loss of records without proper checks. To mitigate this, robust transaction controls and periodic audits are necessary to maintain accuracy across the database .

The [Admin] process incorporates management report features, offering aggregated information and statistics such as popular documents. This process aids in strategic planning by providing insights into trends, resource usage, and operational efficiency, enabling informed decision-making to improve library services .

Differences in design and implementation could result in variations in user interface, system efficiency, database normalization, and feature richness. Group projects could lead to diverse approaches in handling data integrity, error management, and user experience, potentially affecting the system's reliability and maintainability, depending on how closely the group adheres to robust design principles .

You might also like