Valeriano Fugoso Public Library Integrated Library System A Case Study Presented to the Department of Information & Computer Studies Faculty of Engineering University of Santo Tomas In Partial Fulfillment of the Requirements in Database Management Systems October 2010 Title Page Table of Contents 1. Introduction. 2. About the Company 1. Background and History 2. Organizational Chart 3. Contact Person/s 3. The Current System 1. Detailed Description of the Current System 2. Problem Areas 3. Functional Decomposition 4. Enterprise Data Model 1. Business Rules 2.
Preliminary ER Model 3. Enhanced ER Model 4. Entity Cluster 4. Analysis and its Proposed Solution 1. Description and Analysis of the Proposed 2. Objectives 1. General Objective 2. Specific Objectives 3. Scope Of Work 4. Workflow Model 5. Enterprise Data Model 1. Proposed Business Rules 2. New ER Model 3. New Enhanced ER Model 4. Entity Cluster 5. Database Design 1. Current Relations and Assumptions based on the Existing Database 2. Logical Database Design 1. Database Normalization 2. Data Dictionary / Metadata for the UNF 3. Physical Database Design and its Implementation . Database Implementation 1. Final Database Design 1. Relational Tables 2. Metadata 2. Database Relation and Relationship Instances 3. User Interface and Forms 4. Queries and Result 5. Reports 6. Codes (Major 7. Recommendation (Support and Maintenance) Summary and Conclusion Appendix A. Work Breakdown Structure and Schedule B. Current System Documentation B. 1 Sample Forms and Reports B. 2 Transcript of Interviews 1. Introduction A library is an organization which collects, processes, preserves, safeguards books for the benefit for the community.
It is commonly called the People’s University because it serves as a catalyst in planning for community development and in the continuous education of the people. We decided to implement a library system because we, ourselves have already become the end users of it, either manually or in a computerized system. We have experienced how to use the manual processes of a library and also experienced the benefits of its automation. We based our research project on a public library because these are non-profit organization, funded only by the local government.
Due to the low allocation of budget for library funding, some public libraries could not afford to implement solutions for the automation of their current system. An ILS (Integrated Library System) is defined as an automated library system containing several library modules or functions that are integrated together so that data can be accessed from one function to another without the problem of data redundancy. Implementation of an ILS benefits patrons, the staff and the entire community through improved services, more efficient library procedures and processes
Our objective is to develop an automated system of the different library modules of Valeriano E. Fugoso Public Library. Based on our observation of their current system, there are encountered problems in regard to the accession of the library collections and risks regarding bibliographic data integrity. Also, if the library implements their Circulation Function in their manual system, there might be risks in regards to library collections tracking that the library may face in the future.
Our group believes that the most valuable asset any library has is its ability to meet the information needs of its users. Our objective is to develop an automated system which can speed the process of library functions and may reduce the time needed access the library collections. Compared to a manual processing system, ILS is proved to be considerably more efficient and provides faster user response. Another benefit from implementing ILS is the improvement of labor-intensive library operations like filing, sorting and manual searching. 2. About the Company 2. 1 Background and History
The Manila City Library was transformed into a division and has its legal of existence by Ordinance Ni. 2982s, s. 1946. Its transformation to Reference Division was brought about through the issuance of officials orders namely: Administrative Order No. 43 dated September 30, 1965 and Memorandum Circular dated December 28, 1971 which departmentalized the Main Library into two separate service: the Reference Division which is now the Main Manila City Library and the Branch Library Division which handles all the libraries designated in the different districts in Manila.
Named after the 13th Mayor of Manila, the Fugoso Library was first inaugurated on January 25, 1994 by virtue of City Council Resolution No. 4 dated January 20, 1994. It officially opened up to the public on the 25th of January, 1994 with the initial inventory of 5,620 books. In 2006, As part of the education component of the Buhayin ang Maynila program of Mayor Atienza and thru the assistance of Congressman Miles Roces, the library underwent major renovation to provide modern and responsive library facilities and services for the residents of the third district of Manila.
Today, the library is proud of its comprehensive collections, some of which were acquired from the Manila City Library and the National Library. This is the second library that the city government has redeveloped. The first one was the Manila-Sacramento Public Library located at the corner of Canonigo and Zamora Sts. in Paco, Manila which was inaugurated on Aug. 21, 2004. 2. 2 Organizational Chart 2. 3Contact Person/s Valeriano E. Fugoso Public Library Corner Lacson & Aragon Streets, Sta. Cruz (South), Manila Contact Number: 742 0627 Lani C. Balatay
Librarian I Email: [email protected] com 2. 4Goal and Critical Success Factors Vision We aspire to become a premier public library imbued with sound values and a strong commitment to lifelong learning and information literacy. Mission Develop, sustain and preserve updated Library Resources available and useful to the community and to the Filipino people. Goals 1. Serve as an information and knowledge center of the community; 2. Provide sufficient, substantial and accurate updated information for the informal education of the people; 3.
To provide library and information service responsive to the needs of the community 4. Maintain a provision of at least 10% annual increase of the collections Critical Success Factors 1. Make sure that information in the card catalog is accurate and correct. 2. Relevant and up-to-date information resources 3. Convenience in accessing the library collection 4. Allocation for the library budget and its relevance with the current times 5. Business Functions The key advantage of an automated library system to the manual processing system is that library modules are integrated together.
Integration of library modules occurs when functional modules or the business functions shares a common bibliographic repository. Analyzing Fugoso’s Library Modules is essential in modeling their current system and determining which library modules are necessary to automate. 1. Acquisitions The addition of library collection is dependent on the allocation of the Branch Library Services Division. Manages the logging of the delivery transactions from Distributors. 2. Bibliographic Listing Assignment of book call number to the newly acquired item using Dewey Decimal Classification System and a unique Accession Number.
Recording of item information in a Bibliographic Master File. 3. Cataloging Create card catalogs based on author index, title index and subject index from an item’s information from the Bibliographic Master File and the shelving of the item in a particular section. 4. Patron Visit Tracker Records patron information and visit time details. Serves as the attendance log sheet for visiting patrons 5. Inventory Shelf Listing Determines the status of library materials by checking the availability of each material per shelf and its current condition. 3. The Current System . 1Detailed Description of the Current System Currently, Fugoso Public Library currently houses around 6500 volumes which are deemed to be more than sufficient for the recommended collection of 5000 for district public libraries. According to Ms. Lani Balatay, the Head Librarian, weekly patron visits ranges from 70-100. The Fugoso Public Library is open to the general public – students, professionals, out of school youth, children, senior citizens, officials and others who may wish to come to conduct research or spend some reading time in the library.
All materials in the library collection are for room use only therefore patrons (library users) do not have borrowing privileges. The library is currently using a manual process in their cataloging using 3×5 index cards stored in a filing cabinet that is indexed according to the author, title and subject. Based on our observation with the operations in the library, we have identified forms that they are currently using. The patron attendance sheet is used to record patron visit information and his/her visit details. The bibliographic master file is essential in monitoring the current library collection.
It also contains the acquisition information about the item. The acquisition record is a log sheet upon delivery of the items. The library collection is currently shelved on these sections: General Reference – offers wide variety of reference books for Encyclopedias, Dictionaries, Health and Sciences, Technology, English and Communication and Social Sciences. Filipiniana Collections – offers Filipiniana collections including Rizaliana and local history and events Arts and Literature Collections – offers wide variety of books for Music, Arts and fiction/non-fiction literature
Children’s Collection – offers books of interests for children of all ages. The classification of library items is based on the Dewey Decimal Classification System,. The system is made up of ten main classes or categories, each divided into ten secondary classes or subcategories, each containing ten subdivisions. • 000 – Computer science, information & general works • 100 – Philosophy and psychology • 200 – Religion • 300 – Social sciences • 400 – Language • 500 – Science (including mathematics) • 600 – Technology/Applied Science • 700 – Arts and recreation 800 – Literature • 900 – History, geography, and biography Figure 3. 1 Library Acquisition Flow The size of the library collection is dependent on the allocation from its parent library and its budget funds are provided by the local government unit in the annual appropriations. The public library does not have the capability to purchase books from vendors/suppliers because they don’t have the allocated budget for purchasing operations. The frequency of distribution varies from time to time depending on the volumes of books received by the Branch Library Services Division.
Due to the increasing volumes of their collections and patron weekly visits, the library wants to implement a “circulation system” wherein patrons are able to lend books from the library. The system must also be able to keep track of the transactions of the patron as well as the fines he/she will be incurring for not returning the item on a particular duration. The library monitors its inventory monthly, checking the availability of the library collection to support the needs of the community. A problem that the library faces is that when a particular item was delinquent/missing, they have no way to track down the person who used that item.
Since the library does not have the ability to purchase books on their own, they rely only on the distribution of the Library Services Division. Every year, the library has to submit their annual inventory report to the Library Services Division to assess their distribution to the library. Current Business Process Acquisition Function Figure 3. 2 Library Acquisition Module Process Bibliographic Listing Function Figure 3. 3 Bibliographic Listing Module Process Cataloging Figure 3. 3 Cataloging Module Process Patron Visit Tracker Figure 3. 4 Patron Visit Tracker Module Process Inventory Shelf List Figure 3. Inventory Shelf List Process Figure 3. 6 Patron Usage System Flowchart As depicted in the figure, the patron has to access the catalogs manually to know the location of the item. The patron may consult the master file when he could not find the item in the catalogs or does not have any information. A problem when searching through the master file is that it is sorted in accession number. Accession number is assigned when an item was acquired therefore, it may be slower compared to the catalog process. When the patron would like to access more books, he will have to go back to the process again. . 2Problem Areas Aside from the disadvantages of the manual cataloging process, most libraries face risks in regard to their bibliographic information maintenance. Updating, removing records tends to be tedious since library modules (cataloging, bibliographic listing etc. ) have separate record maintenance. Changes that are made from a module must also be applied to all modules in the library or there may be discrepancy or accuracy in the part of the library collection. 1. Bibliographic Information Duplication In the cataloging business function, a library item is cataloged in 3 indices.
One card is for the author card and the other is for the title card and subject card. The more general the subject of the item is, the more cards needed for the subject card. This may pose a problem when modifying/deleting particular item information because one has to find each index card to apply the changes for that particular item. This also applies in withdrawing/weeding library materials from the collection 2. Limited Bibliographic Information Access and Retrieval Using a manual cataloging system limits the searching capability of the patron to just a particular search index.
Also, searching tends to be labor-intensive because the patron has to flip each index card manually just to know the location of the library material. 3. Bibliographic Information Module-Dependence There is an increased risk regarding data integrity when information has to transfer from modules to modules. For example, the clerk may commit typographical errors when encoding item information from the bibliographic file to the card catalogs. This may lead to the problem in duplication 4. Patron Information Duplication
Every time a patron enters the library, he/she must fill up the log record and before he/she leaves the library. There is also a risk in data integrity because the information might not be accurate or time out was not filled up. 5. Manual Report Generation Accuracy Based on a given report, how will the librarian know create inventory sub reports without manually going through the list? Also, due to the duplication of patron information, the library does not have a track on the number of patrons visiting the branch in a particular time frame. ` 3. 3Functional Decomposition
Figure 3. 6 Valeriano Fugoso Public Library Functional Decomposition [pic] Figure 3. 7 Acquisitions Functional Decomposition [pic] Figure 3. 8 Cataloging Functional Decomposition [pic] Figure 3. 9 Bibliographic Listing Functional Decomposition Figure 3. 10 Patron Tracker Functional Decomposition Figure 3. 11 Inventory Shelf Listing Functional Decomposition 3. 4Enterprise Data Model 3. 4. 1Business Rules 1. A patron is required to log his personal details (name, age, sex, address) and date and time entered the library. The patron should also log out before exiting the library 2.
An item is shelved in a section depending on the item’s call number. There are multiple sections located in the library 3. An item has an assigned accession number which will be its unique identifier 4. A library item may have the same accession number (in case of book copy) but no two items may have the same accession number 5. A distributor is the organization/library who donated library items to the Branch Library Services Division to be distributed to the library. Their unique identifier is their Location_ID. 6. An item may have multiple keywords (related topics) 7.
A patron can visit the library more than once in a particular date. 8. Two patrons may have the same visit date and time but must have different visit number. 9. An item’s call number consists of its: Section ID, Copyright and Classification Number. Classification Number is obtained using the Dewey Decimal Classification System. 10. A donor may donate one or more items to the library 11. Upon delivery of the item, a log has to be made which indicates the accession number of the book, the distributor location ID and name and the date delivered/acquired 12. A library does not purchase items from vendors 13.
An item is distributed by one and only one distributor though a distributor may provide the library with any number of items. 14. An item has a shelf status which indicates its availability: On-Shelf, Missing and Withdrawn 15. In case of missing books, the library does not replace the accession number with another book. Instead, it changes its status into missing state 3. 4. 2Preliminary ER Model Figure 3. 11 Preliminary ER Model 3. 4. 5 Planning Matrix |Business Functions |Data Entity Types | |SECTION |ITEM |DISTIBUTOR |PATRON | |Acquisition | |X |X | | |Bibliographic Listing |X |X |X | | |Cataloging |X |X | | | |Patron Tracker | | | |X | |Inventory Shelf List |X |X | | | |X= Data entity (column) is used with business function (row) | Figure 3. 12 Business Function to Data Entity Matrix 4. Analysis and its Proposed Solution 4. 1Description and Analysis of the Proposed Solution In developing our database system, we enumerate solutions that our system can solve the problems of the current system of Fugoso Library that we have observed: 1. Bibliographic Information Module Independence The separation from the modules and bibliographic information created date independence. With the database approach, it allows the data to change and evolve without altering/modifying the modules that accesses the data.
For example updating the bibliographic information of a book also affects how the information will display in OPAC. 2. Catalog Labor-Intensive Process Elimination The duplication of effort to create and maintain multiple copies of bibliographic records is eliminated in an integrated system because querying is done within the book relation. There is no creation of card catalogs with different headers and yet same bibliographic information. User can query information faster and more convenient that in a manual system. The bibliographical information of the item is stored in one repository only. Compared in a manual system, there is no need to encode records per index like author, title etc.
Using OPAC (Online Public Access Catalog), accessing information is much more convenient for the patron compared to a manual system. 3. Implementation of Membership Policy In developing a database for the library, we can store the records of the members in one repository instead of multiple entries of a log sheet. Member information accessing and modifying are much more convenient. Time in and time out will also be automated. 4. Integration of an Automated Circulation System Based on the request of the head librarian, we are to implement a circulation system for the library. Automating circulation makes the entire circulation process faster, more efficient and accurate.
It creates the ability to automatically charge and discharge items and produce circulation reports and statistics. Also, monitoring inventory will be more accurate since transaction recording are real-time. 5. Benefits of OPAC (Online Public Access Catalog) Automating the catalog process eliminates card production and manual catalog maintenance. It keeps the catalog more updated and can display the real-time loan status of the circulation item The staff can also benefit by not having to perform all manual work related activities like filing, updating and maintain traditional card catalogs. For the OPAC, the librarian requested to provide users the capability to query using multiple indices and use Boolean operators like AND, NOT and OR.
We will be applying different principles we learned in ICS7(Interface Design) Course to develop a user-friendly interface for our OPAC and at the same time, generate correct SQL query statements for the database that will provide substantial and accurate records for the users. As shown in the figure, we have modeled a system flowchart for our proposed circulation system for the library. We have assumed that other library modules are already integrated in our system. Several considerations must be queried first before an item will be loaned. Does the patron have a valid account? Is the item the patron will be borrowing a loanable item? Figure 4. Book Lending System Flowchart Figure 4. 2 Book Returning System Flowchart 4. 2 Objectives It is essential in developing a system is to meet the client’s needs or demands. In our case, we must set our goals in order to know which areas we need to focus on and give priority. 4. 2. 1General Objective To develop an Integrated Library System with the Integration of Circulation Module to Valeriano E. Fugoso Public Library 4. 2. 2Specific Objectives 1. To integrate library modules in one automated, integrated system 2. To track the real-time status of the loanable items and automate calculation of fines incurred by the patron automatically 3.
Develop a cataloging system for convenient records insertion and updating. 4. To develop report generators like inventory list, delinquent books, current members list, etc. 5. To implement OPAC (Online Public Access Catalog) 4. 2. 3Scope of Work Based on our interview with Ms. Lani Balatay, she explained to us the problems that she faces in managing the operations of the library using a manual system as stated in our Problem Areas ( Refer to Chapter 3. 2 ). She would also like to integrate a Circulation Function in the implementation of our Integrated Library System. She provided us with details and requirements when we implement our system: 1.
The system shall be fully integrated with the library’s current modules with the addition of the addition of Circulation Module. 2. The system shall be able to expand to accommodate up to twice the current collection size and number of users without major hardware/software redesign 3. All modules shall be based on the standard formats of the current system 4. The system shall have the following operational functions a. Bibliographic Listing The system shall contain functions required to create, update bibliographic records as part of the master file. Expected Deliverables: Bibliographic List, Inventory Reports b. Circulation Availability of materials shall be shown in the OPAC. The system shall use patron records to keep track of loans, fines.
The librarian shall be able to view patron complete information on patron’s transactions. Expected Deliverables: Transaction Record (Library Card) c. OPAC The system shall provide a means of public access to the OPAC through network. The system shall indicate the status of items displayed as a result of a search. d. Acquisitions The system shall include a vendor file that shall contain record for each distributor used as a source for acquiring an item 5. The limit of our system will be the specific business functions that Ms. Lani would like us to automate. We will not be dealing with financial operations like allocation or budgeting of library finances.
Other operations that are beyond our system are Human Resource Management and Payroll operations. 6. We will be basing database input through the standard input devices. We will not be implementing our ILS in RFID mechanisms 7. Another feature of an ILS is MARC (Machine Access Readable Catalog )Tagging. A useful cataloging standard for interlibrary systems. Since we will be developing our system in a particular Branch only, we will not be implementing MARC Tagging though we will be recommending it for future studies. 4. 4Workflow Model [pic] The system that we will be implementing will be a Transaction Process System mainly between the interactions between the library’s collection with the Library System we will use.
As shown in the diagram, every transaction performed in the library collection are being stored in the database. In an event of a delivery, the delivery details are also encoded. Whenever a patron loans/returns a library item, the database will also keep track of the transaction details. A patron may access the schema but with limited privileges through accessing the OPAC. In OPAC, they can check the availability of the item through querying . Inventory reports like the bibliographic master file are also generated with the library collection 4. 5Enterprise Data Model 4. 5. 1Proposed Business Rules 1. Only members are allowed to enter and use library materials. 2.
A member is person who has submitted necessary documents for membership and is eligible for loaning library items. 3. A member can loan one ore more circulation item. A loan includes transaction details: Date Issued, Date Returned, Due Date and Fine. 4. A Fine can be derived from this equation (Date Returned-Date Issued – MaxLoanPeriod) 5. An item can be further classified as a Circulation Item which will contain its status and maximum loaning period and Consultation Item which will is not for lending. 6. Readers are required to be a member when borrowing library items 7. As of the agreed rule, all books are for room use only, except for fiction/pocket books, which can be borrowed for one week (7 days). 8.
A reader is allowed to borrow two (2) pocket books at a time 9. A fine of P1. 00/ day will be collected for all overdue pocket books 10. Each member has one or more visit and each instance of visit belongs to exactly one member 11. Members with Delinquent Accounts are not permitted to loan items from the library 12. Lost books must be replaced with the same title, edition & author. If the first condition is not possible, a book on the same subject or closely related subject may serve as a replacement provided that it is hardbound and the most recent edition. 13. In case the lost book is no longer available, the decision to accept the replacement book is determined by the Acquisition Librarian. 14.
If the patron found the book after being declared or reported lost and returns it, and if the book is in acceptable condition, the patron will be required to pay only the accumulated overdue fine. 15. No borrower is allowed to use the library and its resources unless he/she has settled all accounts due to the library. 16. Attributes associated with one or more library item like authors, publishers and keywords are restricted to deletion. 5. Database Design 5. 1Current Relations and Assumptions Based On The Existing Database It is essential to have a plan before implementing the system physically. We based the relations that we will be creating on our Entity Relationship Diagram. We made also our assumptions like added attributes and entities. |ITEM | |Accession_ID | ISBN | |Publisher | |Publication_Place | |Keywords | |Author | |Section_ID | |Classification_Num | |Classification_Description | |Copyright | |Title | |Type | |Acquisition_Date | |Location_ID | |Author_FirstName | |Author_LastName | |LOAN | |Loan_ID | Due_Date | |Penalty | |Date_Issued | |Date_Returned | |Penalty | |Member_ID | |Accesion_ID | Relations |MEMBER | |Member_ID | |Member_FirstName | |Member_MiddleName | |Member_LastName | |Member_Address | |Member_Sex | |Member_Age | |Member_Phone | |Member_Email | |Member_Status | |Member_Password | Visit_ID | |Visit_Date | |Time_In | |Time_Out | |SECTION | |Section_ID | |Section_Name | |DISTRIBUTOR | |Location_ID | |Donor | |Donor_Phone | |LOANABLE_ITEM | |Accession_ID | |Max_LoanPeriod | |Loan_Status | |CONSULTATION_ITEM | |Accession_ID | |Shelf_Status | Relations Assumptions 1.
It is not recommended to include the entity BRANCH in the model since it is clearly understood that the branch we are referring to is only Fugoso Library (one instance throughout the modeling). This may pose an issue of data redundancy especially in the normalization phase. This is the very reason why we have removed the entity Branch from the model 2. We will be assuming that circulation has been integrated in our system so there are additional entities and attributes in our relations. We have modified the relation patron to member and its attributes. We have added the associative entity loan which will keep track of transactions in the system 5. 2Logical Database Design 5. 2. 1Database Normalization
Normalizing our relations for our logical design increases the system’s maintainability and data stability and integrity. Throughout the normalization phase, we determined anomalies that the user may encounter when interacting with the system. Normalization is a formal process which examines data and simplifies data item groupings to better accommodate future data manipulation. Unnormalized Form (UNF) |Member_ID | |Member_FirstName | |Member_MiddleName | |Member_LastName | |Member_Address | |Member_Sex | |Member_Age | |Member_Phone | Member_Email | |Member_Status | |Member_Password | |Visit_ID | |Visit_Date | |Time_In | |Time_Out | |Accession_ID | |ISBN | |Publisher | |Publication_Place | |Keywords | |Author | |Section_ID | |Classification_Num | |Classification_Description | |Copyright | Title | |Type | |Acquisition_Date | |Location_ID | |Author_FirstName | |Author_LastName | |Loan_ID | |Due_Date | |Date_Issued | |Date_Returned | |Section_ID | |Section_Name | |Location_ID | |Donor | |Donor_Phone | |Shelf_Status | |Max_LoanPeriod | |Loan_Status |
First, this table is subject to several anomalies: we cannot list publishers or authors without having a book because the Accession ID is a primary key which cannot be NULL (referred to as an insertion anomaly). Similarly, we cannot delete a book without losing information on the authors and publisher (a deletion anomaly). Finally, when updating information, such as an author’s name, we must change the data in every row, potentially corrupting data (an update anomaly). First Normal Form (1NF) An entity in the First Norma lForm if all itsattributesa re single valied and non-decomposable and there a re little or no repeating groups |MEMBER | |Member_ID | |Member_FirstName | Member_MiddleName | |Member_LastName | |Member_Address | |Member_Sex | |Member_Age | |Member_Phone | |Member_Email | |Member_Status | |Member_Password | |Visit_Date | |Time_In | |Visit_ID | |Time_Out | |ITEM | |Member_ID | |Accession_ID | |ISBN | |Publisher | Publication_Place | |Author | |Classification_Num | |Classfication_Description | |Copyright | |Title | |Type | |Acquisition_Date | |Loan_ID | |Due_Date | |Date_Issued | |Date_Returned | |Section_ID | |Section_Name | |Donor | |Donor_Phone | |Shelf_Status | Max_LoanPeriod | |Loan_Status | |KEYWORD | |Accession_ID | |Keyword | Second Normal Form (2NF) An entty is in the Second Normal Form if 1) It is in the First Normal Form and 2) All the nonkey attributes are functionally dependent on the whole prmary key (full functional dependency ) |MEMBER | |Member_ID | |Member_FirstName | |Member_MiddleName | |Member_LastName | |Member_Address | |Member_Sex | Member_Age | |Member_Phone | |Member_Email | |Member_Status | |Member_Password | |ITEM | |Accession_ID | |ISBN | |Publisher | |Publication_Place | |Author | |Classification_Num | |Classification_Description | |Copyright | |Title | |Type | |Acquisition_Date | Location_ID | |Section_ID | |Section_Name | |Donor | |Donor_Phone | |LOAN | |Accession_ID | |Member_ID | |Loan_ID | |Due_Date | |Date_Issued | |Date_Returned | |CONSULTATION_ITEM | |Accession_ID | |Shelf_Status | |LOANABLE_ITEM | |Accession_ID | Max_LoanPeriod | |Loan_Status | |VISIT | |Visit_ID | |Member_ID | |Visit_Date | |Time_In | |Time_Out | The Author, Subject, and Publisher tables use what is known as a surrogate primary key — an artificial primary key used when a natural primary key is either unavailable or impractical. In the case of author we cannot use the combination of first and last name as a primary key because there is no guarantee that each author’s name will be unique so we use a surrogate key.
By separating the data into different tables according to the entities each piece of data represents, we can now overcome some of the anomalies mentioned earlier: we can add authors who have not yet written books, we can delete books without losing author or publisher information, and information such as author names are only recoded once, preventing potential inconsistencies when updating. Third Normal Form (3NF) An entty is in the Third Normal Form if 1) It is in the Second Normal Form and 2) No nonley attributes is functionally dependent on any other nonkey attributes (no nonkey dependencies) |MEMBER | |Member_ID | |Member_FirstName | |Member_MiddleName | |Member_LastName | |Member_Address | |Member_Sex | |Member_Age | |Member_Phone | |Member_Email | Member_Status | |Member_Password | |ITEM | |Accession_ID | |ISBN | |Publisher_ID | |Author_ID | |Classification_Num | |Copyright | |Title | |Type | |Acquisition_Date | |Section_ID | |Location_ID | |LOAN | |Accession_ID | |Member_ID | Loan_ID | |Due_Date | |Date_Issued | |Date_Returned | |CONSULTATION_ITEM | |Accession_ID | |Shelf_Status | |LOANABLE_ITEM | |Accession_ID | |Max_LoanPeriod | |Loan_Status | |VISIT | |Visit_ID | |Member_ID | |Visit_Date | |Time_In | |Time_Out | SECTION | |Section_ID | |Section_Name | |Author_Lookup | |Author_ID | |Author_Firstname | |Author_Lastname | |DISTRIBUTOR | |Donor | |Donor_Phone | |Location_ID | |CLASSFICATION | |Classification_Num | |Classification_Desc | |PUBLISHER | |Publisher_ID | |Publisher | |Publication_Place | 2.
Data Dictionary/ Metadata for the UNF |Accession_ID |Unique identity of a library item |int(5) | |Acquisition_Date |The date the book was acquired |date | |Author_ID |Id of the Author |int(5) | |Classification_Num |Dewey decimal classification system of a book |varchar(20) | |Copyright |The copyright of an item. |int(4) | |Date_Issued |The date of when an item was loaned. date | |Date_Returned |The date of when an item was returned. |date | |Donor |Name of a person or company who donate books |varchar(50) | |Donor_Phone |Contact number of the Donor |varchar(20) | |Due_Date |The due date of a particular loan. |date | |ISBN |The internal Standard Book Number of the book |varchar(13) | |Item_Type |Classification if an item is lonalble or for consultation only. varchar(25) | |Keyword |Words that best describe or summarize a particular item or its |int(5) | | |contents. | | |Loan_ID |Unique Id of a particular loan |int(5) | |Loan_Status |Status of a particular loan |enum | |Location_ID |The unique ID given to a branch |int(5) | |Location_ID |The unique ID given to a distributor |int(5) | |Max_LoanPeriod |The maximum period of time an item can be loaned. int(2) | |Member_Address |Home address of the member |varchar(50) | |Member_Age |Current Age of the member |int(3) | |Member_Email |Email address of the member |varchar(30) | |Member_FirstName |First name of the member |varchar(30) | |Member_ID |Id of the member |int(5) | |Member_ID |Primaty indetfier Id of the member |int(5) | |Member_LastName |Last name of the member |varchar(30) | |Member_MiddleName |Middle name of the member varchar(30) | |Member_Password |Desired password of the member used in logging in |varchar(40) | |Member_Phone |Phone number of the member |varchar(20) | |Member_Sex |Gender of the member |char(1) | |Member_Status |Determines the privilege of the member |varchar(10) | |Penalty |Fine accumulated for late returning of loans |double(6,2) | |Publication_Place |location or area of the publisher |varchar(30) | |Publisher |The corresponding names of the publisher of a book/item. |varchar(30) | |Publisher_ID |Unique id given to a particular publisher |int(5) | |Section_ID |The unique ID given to the different sections of the library. |varchar(5) | |Section_ID |The unique ID given to the different sections of the library. |varchar(5) | |Section_Name |The names of the sections. varchar(30) | |Shelf_Status |The availability status of an item |enum | |Time_In |Time the member enters |time | |Time_Out |Time the member exits |time | |Title |The title of an Item |varchar(30) | |Visit_Date |Date the member visits |date | 2.
Physical Database Design and Its Implementation 1. Composite Usage Map ITEM entity in the diagram is the current books in the library collection. As depicted in the diagram, there is an estimate volume of 6000 books in the library collection. 60% of these is classified as loanable item while 40 % are consultation. In a per hour frequency access notation, we are expecting that there will be 100 access to the book because of the OPAC and admin access. Since loanable item is used for transactions, it has higher frequency access compared to consultation. If a member wants to return, borrow the member relation and loanable item relation are accessed and manipulated. Ms.
Balatay estimated that average loaning transactions per hour is around 50 per hour. 2. Database Server Architecture In the implementation of our system, we will be implementing a two-tiered client-server network. In this system, the client workstation is responsible for managing the user interface, including presentation logic and data processing. We will be using MySQL Server 5. 1 as our RDBMS on our database server. With this approach, maintenance, update and query are all performed at one location, on the database server. Our client program is designed using Netbeans 6. 9 implementing JAVA Graphical User Interface (GUI) Figure 5. 1 2 Tiered Database Server Architecture ———————–
Manila City Administration Main City Library Divison Helen Grace S. Cacho, Librarian V Manila City Librarian Branch Library Services Divion Grace Gargantial Library IV Technical Services Divison Valeriano Fugoso Library Lani C. Balatay Librarian I Branch Head Editha Pacheco Clerk Diosely Umali Clerk Rex P. Mendoza Clerk Janica D. Alvarado Clerk Leonora S. Elfa Administrative Aide Maria Concepcion Administrative Aide Administrative Services Divison Donating Organizations/ Foundations National Library of the Philippines (Public Libraries Division) City Libraries (Manila City Library, Quezon City Library, Marikina City Library) Branch Library Services Division Allocation of acquired materials to different branches Valeriano E. Fugoso Public Library Collections Event: Branch Services Division will distribute library items to the different public libraries in Manila Process: Clerk receives the delivery and check item’s condition Process: Logs Delivery Transaction Delivery Log Record Event: Librarian receives books that are results from the Acquisition Process: Assigning book section, accession ID, call num to the item and classification number Bibliographic Master File Process: Encodes book information in the Bibliographic Master File Process: Recording information according to title, author and subject headings
Event: Clerk fills out 3×5 index cards based in the updated master file Subject Card Catalog Author Card Catalog Process: Inserting index cards in the catalog shelf Title Card Catalog Event: Patron enters in Fugoso Library Process: Patron logs in the date and time of endtry and person information Patron Visit Log Record Process: Before leaving the library, patron must log in the time of exit Process: Visit each shelf and locate the book Event: Librarian monitors the availability of the collection Process: Determine if the books is on-shelf, missing or damaged. Monthly Inventory Report Annual Inventory Report START Access Book Location/Information Enter Branch
Patron Attendance Sheet Author Card Catalog Subject Card Catalog Title Card Catalog Bibliographic Master File Does the book exist? Note Down Accession Number/Call Number Locate and Use Book Does the user want to access other books? Exit Branch Patron Attendance Sheet END Y N Y N Library Modules 1. 0 Acquisition 2. 0 Cataloging 3. 0 Bibliographic Listing 4. 0 Patron Tracker 5. 0 Inventory Shelf Listing 1. 0 ACQUISTIONS 1. 1 Logging of Acquisition Details 1. 2 Storage of delivery receipts and invoices 1. 1. 1 Taking note of the bibliographic details of the item 1. 1. 2 Taking note of the time and date of the acquisition of the material 2. 0 Cataloging 2. Encoding of Bibliographic Records to Card Catalogs 2. 1. 1 Title Header Card 2. 1. 3 Author Header Card 2. 2 Filing of Index Cards to the Catalog Cabinet 2. 1. 2 Subject Header Card 2. 3 Shelving of Library Item in their respective sections 3. 0 Bibliographic Listing 3. 1 Maintenance of Bibliographic Master File 3. 1. 1 Accessing and Modifying Bibliographic Information 3. 2 Classification of Library Collections 3. 1. 2 Assigning of Item Identifier, Call Number 4. 0 Patron Tracker 4. 1 Log of Patron Personal Information 4. 2 Logs Visit Date and Time of Access in the Library 5. 0 Inventory Shelf Listing 5. 1 Determines the current availability of the library material 5. Generate inventory reports about the collection 5. 3Performs weeding operations/ collections monitoring DISTRIBUTOR delivers Is in SECTION ITEM PATRON uses START Input Member ID Search Database Library. Member Member Information Input Accesion ID Library. Item Item Information Search Database Valid Loan? Reject Loan Update Database Library. Transaction Library. Item FINISH Y N Library Card Log Details to Library Card START Input Accesion ID Search Database Library. Item Item Information Library. Item Update Database Book Overdue? e Returning Succesful Calculate Fine Y N Library. Transaction Log Details to Library Card Library Card FINISH Admin LAN Visit Log OPAC Users