Determine the multiplicity constraints for each relationship described
The purpose of this assignment is to provide you with experience in analysing and designing a solution for a Library database. This assignment will help you to understand the nature and purpose of data analysis and conceptual design.
Modelling (Total Marks 55)
Read the description of the library and its business operations then answer the questions.
A university library system requires you to design a database system for the library. The library database will record and store the data about each student including their first and last name, postal address and home address (street number, street name, suburb, post-code and city) and gender. The name (first and last name), contact telephone number and address (street number, street name, suburb, post-code and city), of student’s next-of-kin is also stored. For each student the system records and stores all the books that each student has on loan and any overdue books.
When a student enrols in the university, he or she is assigned a student number and his/her details are recorded. Every student is provided with a sheet of paper showing the library’s rules relating to borrowing, return procedures, charges for late return of books etc.
Staff and Sections
The library has several staff members. For each staff member the following data is stored in the library system: staff first and last name, staff number, position, gender, date of birth, name of the section he/she works in, internal telephone number and office number.
The library consists of six sections. These are information-desk, loan-desk, return-desk, cataloguing, administration, and book-order sections. The information about each section is also stored in the library database. The information about each section is: section name, location, telephone number, number of staff in that section and their staff number.
To borrow a book a student browses the library catalogue. Each book is assigned a catalogue number. Several copies of a book may be available in the library. Books are grouped on shelves according to their field of study and contents, namely: Art, Science, History, Business, Commerce, Economics, Education, Law, Medicine and Dentistry etc.
Titles fall into 3 different categories, each with different borrowing rules, namely, Short term (3 hours), Medium term (2 weeks) and Long term (4 weeks) borrowing. See the table below.
|Category||Type||Borrowing Limits||Late fee Charges|
|1||Short term||3 books at a time||$0.30 cents per hour|
|2||Medium term||6 books||$0.05 per day per book|
|3||Long term||10 books||$0.02 cents per book|
A student selects one or more books (they are limited to borrowing a maximum of eleven books at any particular time) and takes them to the loan-desk. The student’s identity is established with the student card and their student number is used to access their records. If there are any late charges noted in the record, these must be paid before additional books can be borrowed. If there are no outstanding late charges, details of the books being borrowed are entered into the computer that calculates the return date of each book.
The details of each book (book title, author name/s, publisher name and address, catalogue number, type of book (Short, Medium, or Long term) as well as the return date and borrower’s id number is stored in the database.
The books are then handed to the student, along with a receipt that lists details of the books borrowed with their due-dates for return.
When books are returned they are placed in a return bin at the return-desk. Details of the books that have been returned are entered into the computer. If the books are returned late, a late charge is calculated by library computer system and it is recorded in the student’s account.
The library stocks 190000 books covering about 150000 titles. About 130 new books are added to the collection every month. The name and address of book suppliers are also recorded in the system.
The library system also provides a reservation facility where a student can browse the library catalogue and put reservation requests for one or more books that are on loan. For reservation a student enters his/her student number and the catalogue number and name of the book that needs to be reserved. The book is then reserved by the library system.
Reports (use these to identify possible relationships)
Three types of computer-generated reports are produced in the library on a weekly basis. The first shows all overdue books. This report is sorted by student number and it is used to contact people with overdue items by e-mail. People are reminded to return the specified items and of the late-return charges that will be incurred. If the items are not returned after a period of two months from the due date legal action is usually taken and a report is lodged to the student administration of the university. Also the student’s record is marked with a special notation to prevent further borrowings.
The second report shows the total number of books that are reported to be missing, so that new copies can be ordered.
The third report shows the details of the books that are borrowed in the last three months. This report is used to order more copies of popular books.
- Identify the main entities of the Library. (6 Marks)
- Identify the main relationships between the entities described in (a)
- Determine the multiplicity constraints for each relationship described in (b).
- Identify attributes and associate them with entity or relationships.
- Determine candidate and primary key attributes for each strong entity.
- Using your answers (a) to (e) attempt to represent the data requirements of the Library as a Conceptual data model (ERD and Data Dictionary). State any assumptions necessary to support your design. (28 Marks)
For a custom-written paper, place your order now
What We Offer
• On-time delivery guarantee
• PhD-level professionals
• Automatic plagiarism check
• 100% money-back guarantee
• 100% Privacy and Confidentiality
• High Quality custom-written papers