INFOAP2 Database Design Study Block Project The University Boole Library Database Modelling Project The University Boole Library has approximately 19,000, members, 200,000 book titles, and 500,000 volumes (an average of 2 copies per book). It has an additional 150,000 titles as ebooks, 30,000 audiobooks and music CDs. The library is replacing its current system, which is no longer meets the needs of librarians or borrowers. The following are the basic requirements for the new system. Library members are categorised as staff members (professors, lecturers, research, professional, administration) and student members (undergraduate, postgraduate, and PhD). Librarians are also members of the library. When a new staff/faculty member joins the university, his or her information is extracted, transformed and loaded from the employee records, and they are automatically enrolled as members. The same applies to new students, with their details coming from the student records system. Contact details include campus and home mailing addresses, phone numbers and emails. However, for online access, staff and students fill out an online form using their staff and student numbers, email address and provide a password, which is stored in the system. Staff and student cards are used for library access and lending as they are electronic versions of staff and student numbers. Library staff include the chief librarian, departmental associate librarians, reference librarians, check- out staff, and library assistants. These users have administrative privileges in using the system: However, they may perform the same search and borrowing processes on behalf of university staff and students if required, particularly where items cannot be borrowed. They borrow books themselves using their staff cards. The library's catalogue of books, ebooks, audio book CDs, and music CDs must be available online: This online catalogue may be accessed through any device and using computers provided for that purpose in the library. The catalogue lists books by author, title, year, language, and subject area and other data presented below. The system must record details for each item borrowed from the library. Each book is uniquely identified by its International Standard Book Number (ISBN), which is a unique international code assigned to all books. Two books with the same title can have different ISBNs if they are in different languages or have different bindings (hardcover or softcover). Editions of the same book have different ISBNs. As the library will house multiple copies of the same book (e.g. textbooks, essential readings etc.) this must be captured. Some books that have the same title can have different author names. Books also have multiple authors. Books also have different publishers, years of publication, edition numbers and page numbers. Books also have a short description, which ranges from one sentence to several pages and an accompanying graphic image of the cover page. Borrowers and reference librarians want to be able to access this description when members request information about a book. These descriptions are also available online. Audible books in CD format have an additional attribute of the readerarrator. Finally, books and book CDs have a unique barcode to enable electronic borrowing and return. Music scores are catalogued by genre, title, composer, year, while music recordings in CD format also have performer, company, year and so on. Score and CDs have a unique barcode to enable electronic borrowing and return Books are purchased and included in the library on the recommendation of individual academic departments. These are discipline-based (e.g. computer science, business information systems, psychology, management, finance, accounting, economics, and so on). Each department is allocated an annual budget to purchase new books. The books for each department/discipline are located in sections of the library under more general categorisation according to the Dewey Decimal System. Page 1 of 4 INFOAP2 Database Design Study Block Project 000 - Computer science, information, and general works 100 - Philosophy and psychology 200 - Religion 300 - Social sciences 400 - Language 500 - Science 600 - Technology 700 - Arts and recreation 800 - Literature 900 - History and geography As indicated above, the number range 000 - 099 accommodates many general disciplines. The full range using in libraries being 000.00xx to 999.99xx. The xx involves extensions to accommodate other categories of objects such as audiobooks or music CDs. In computer science and information systems, books on database design are catalogued in the library as 005.75. Thus the numbering system 000.00 - 999.99 is used to catalogue and locate each book physically, as each discipline has a section of the library in which related books are shelved and located according to this scheme. However, book location description such as main library name and floor number, branch library name and location, or off-campus storage needs to be added to Dewey Decimal numbers for librarians and borrowers to physically locate a book. Figure one provides an example: Database design for mere mortals: a hands-on guide to relational database design by Hemandez Michael DETALS 2013, Third edition. A guide to relational database design covers such topics as setting objectives, establishing table structures, and identifying and establishing business rules Book CHECK SHELF. 005.75 HERN, Boole Q+2 Floor Less CHECK SHELF 005.75 HERN, Boole Q+2 Floor CHECK SHELF, 005.75 HERN, Boole 0+2 Floor Basic data on this book is presented. The CHECK SHELF attribute indicates that the book is in the library and should be on shelf 005.75. However, also indicated is a 4 character abbreviation of the author's surname Hernandez, as HERN. All books in a category e.g. 005.75 are listed alphabetically by author surname, e.g. 005.75 HERN. Also note the library name Boole and the floor number Q+2. In the system, author ID may be based on the variable character unique identifier with the first four letter sof the authors surname and three numbers in the range 0-9, e.g. XXXX000. Online ebooks (ePub, pdf) are simply linked via hypertext to the publisher or service provider using the ISBN, Title and Author (s). Downloads and printing in ePub or PDF are limited and this is managed by the publisher/service provider. Music, including scores and recordings, are also available for borrowing. These also use the Dewey Decimal Classification system under the category 780. In addition to this, each composer is allocated a code which is the first letter of the surname and then a number eg. Mozart is M.81. This permits all the works by a single composer to be kept together regardless of whether other composers share the same surname e.g. CPE Bach B.04, JS Bach B.08. The only exception to this i pop music scores. They are allocated four letters as books are e.g. Billie Eilish would be EILI. A music CD (or MP3 file) would be categorised similarly under 780, with a pop music CD by Billie Eilish catalogued as 781.63 EILI. To accommodate music genres and subgenres, the numbering scheme runs from 781.0000 to 789.9999, as indicated above. Page 2 of 4 INFOAP2 Database Design Study Block Project The book acquisition process (physical or online) is triggered by requests from academics in various departments. Each department has a fixed annual budget by the library. As library space is limited, as newer editions and publications are acquired, older volumes not borrowed within a 12 month period are placed in an off-campus location from which they may be retrieved and loaned by special request. This is recorded in the book location description, which is a separate field or attribute as indicated in the above figure. Book titles not in the library may be borrowed using the interlibrary loan process. This helps librarians using the system to ensure that the books that members want to borrow are available when members wish 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. Figure 1 indicates three copies are available of this book. In addition, with course texts, a number of copies are marked for short-term loan of 3 days. This must also be recorded in the system and indicated to borrowers. Student members can borrow books out for 21 days, except certain books in high demand have a 3 day limit. Student members are allowed five books out at a time, while staff members can have up to 8 books. This rule needs to be captured in the system. Members usually return books within three to four weeks. Most members know that they have one week of grace before a notice is sent to them, so they try to return books before the grace period ends. About 5% of the members have to be sent reminders by email to return books. Faculty and other staff may borrow books for up to three-month period: They have a two-week grace period for returns. Renewal notices to staff is also by email. Books that are returned late, not returned, lost, or destroyed are subject to fines and or replacement costs. Borrowers must pay these costs within a month or them being incurred. This may be done in person at the library or online. Failure to do so within 6 months, may incur a loss of library borrowing privileges. The library does not lend some books, such as reference books, rare books, and maps. These must be read in the library. Thus, the system and librarians must differentiate between books that can be lent and those that cannot. In addition, the librarians need to have access to a list of some books they need to acquire but cannot obtain commercially, such as rare or out-of-print books and books that were lost or destroyed that have not been replaced. Such books may be accessed through inter-library loans with other universities. Thus, the system must capture information on certain books and articles that are requested by academics or students regularly but held in other libraries as inter-library loans. In summary, the proposed database system must be designed to keep track of the members (borrowing history, fines etc.), books and other articles such as CDs, book/article status, location, and book acquisition with respect to on-campus limits. Librarians are also users of the system as they enter new acquisitions and administer the library catalogue. The following project deliverables are required. ERD Data Model Due 24/10/2021 100% 1. An analysis of the requirements in the form of predicate logic/semantic triples (subject, predicate, object): these will be in the form of noun, verb, noun, representing the relationship between entity sets. (in Project Word document). See the following example: 1 salesperson manage M salespeople 1-M recursive 1 salesperson agent for M customers 1-M mandatory 1 customer place M orders. 1-M mandatory . Merder lists N inventory items M-N => order lists 1-M inventory item details M-1 for inventory items Page 3 of 4 INFOAP2 Database Design Study Block Project Salesperson has a name, identification number, address, salary and birthdate. (Note name and address are composite attributes and need to be made atomic.) 30% 2. An entity-relationship diagram (ERD) data model in Third Normal Form with data definition columns (as a PNG or JPG clear file). Entity attribute values must be atomic. The model should use the Crows Foot notation in style employed in LucidChart, with all primary, foreign keys, relationship labels, and cardinality clearly indicated the following figure illustrates this: move HA 1 Department PK Det D INT Name VARCHAR(20) ) Location VARCHARGE Employees PK EID INT(S) Name VARCHAR (20) ) Address VARCHAR (20) Salary LINT (5) Duu DATE Rule VARCHAR(10) + Dept DINT + Dependent PK Nam VARCHAR(20) Do Date Relationship VARCHAR(10) Od Emp DING) F many layed by work on cony worked w PROJECT PK ProjectID INTO Name VARCHAR(20) BUDGET INT(0) ordon by Praject_Fmp KPR FK Projects INTO) PK.FK Empo INT(4 Key Son Date DATE key Lae Dale DATE 70% Page 4 of 4