Task 1:
A complete database design and implementation project in SQL for a real-world
business case
Task 2:
A written report that recognizes the business needs in a particular decision environment and demonstrates how to address these needs with suitable operational data analytics tools in order to create value for different stakeholders.
Task Details:
Home library service is a free delivery service of library materials(e.g. books, magazines, DVDs etc), offered by local council libraries to the registered, eligible home-bound library members (e.g. elderly citizens, disabled, a parent with young children, carers of people with illness and/or disability etc.). The service is provided by library volunteers and coordinated by the home library service coordinator, who is also a library volunteer. Further details of this valuable service, the service-registration form for the home-bound library members and the volunteer registration form are shown in Appendices 1-3 of this document. Once registered, the home-bound library members access and use the electronic library catalogue to find and check-out (borrow) the library resources. The selected resources are packed and delivered to the member’s home/aged care facilities by the volunteers, who also pick up the previously borrowed resources (if any) and return them to the library.
Task 1:
1. Based on the above specification and the provided documents, design and implement
a database in SQL for the home library service.
Your database is required to include
a. a complete ERD in Chen’s notation (for consistency do not use other notations) (8
marks)
b. a complete database project designed and implemented in SQL (8 marks)
c. a sufficient number of realistic dummy data records for each created table (5 or
more) to demonstrate data retrieval and further operational data analytics, using
SQL queries, as required by Task 2 (please see below). (4 marks)
Your database should focus on management of home library service. The electronic library catalogue used by all library members to borrow books is already in place and as such outside of the scope of your project. In other words, you are not asked to design and implement a library catalogue database. You are allowed to make assumptions, as long as they do not contradict the business rules of this service and the assignment specification.
Task 2:
In your role of BA professional, you are required to prepare a written data analytics report for the Library Manager to inform her decision making in the observed decision making environment, about the current issues and future opportunities for innovation of this valuable service.
In your interview you found out that the Library Manager is particularly concerned about the current inefficiencies caused by manual processing of service bookings. While the members use the web form to register for this service (for legal reasons of giving the entry permission), the subsequent requests for the delivery of library materials (i.e. service bookings) are made via email or over the phone. These inefficiencies have resulted in data errors (e.g. service bookings taken for the wrong dates or even for public holidays), disappointed home-bound members not getting the service because their requests were made by personal emails that got lost, frustrated volunteers who were contacted to deliver books on the days they were not available and overworked service coordinator who is also a volunteer and as such could not be expected to work full-time. The Library Manager also shared examples of home-bound library members calling-in to book a home delivery service of specific books and booking taken by the service coordinator, without knowing which volunteer would be available to deliver the books.
The Library Manager also explained the importance of maintaining valid/current police clearance for each volunteer to enable them to enter the homes of the home- bound members with member’s explicit permission taken at the time of their initial registration for the service. The police clearance needs to be renewed by volunteers themselves every two years. Volunteers are also expected to complete a Health & Safety training every two years. This training is offered by the library 6 times per year and all volunteers are expected to self-enroll in order to maintain the currency of their training.
Due to strict regulations, volunteers without a valid police clearance and/or up-to- date training record are not scheduled to provide a service, until they fulfill those mandatory requirements. Because volunteers’ records are kept in folders in a filing cabinet and updated manually, they are not reminded in time to renew their police clearance or enroll in the next available training. It is also time consuming for the service coordinator to keep checking the folders to confirm the validity of police clearance and Health & Safety training record for each volunteer, before confirming their weekly schedule.
Finally, the library manager also told you about their supportive organizational culture and the great service provided by their volunteers, who are treated and valued as their fellow staff members. She is keen to not only improve this service but to use it as opportunity for a greater societal impact, which is fully aligned with the library mission, vision, and people-focused strategy.
The database designed and implemented by you will serve the purpose of managing volunteers and valuable services they provide to home-bound library members in a more productive way. The data stored in your database will also provide new insights into the home library services and open new, yet-to-be invented opportunities to further engage home-bound library members who are often socially-isolated and lonely.
Based on your interview of the Library Manager, determine the type of decision environment she operates in, and outline 5 organizational issues and one new idea for her to consider. Then formulate the appropriate (non-trivial) SQL queries to support your “guesswork”. The SQL queries are required to be included in your database design package.
Your written report for Task 2 will contain a brief description of the identified decision environment (using the suitable framework covered in this class), a clear explanation how and why the SQL queries were selected and formulated, and your suggestions how the results of these queries could inform future value-adding actions for the service provider and the recipients of this valuable free service.
You are also required to identify a suitable business process and discuss how it could be used to determine/measure value proposition(s) of your analytics solutions. Be very specific. Statements such as “improved customer service” are not specific enough to demonstrate value of your analytics solutions to the Library Manager.
Finally, you are required to identify two potential data quality issues, classify them using the suitable framework covered in this class, and explain to what extent your database design and implementation can be used to prevent or mitigate the identified data quality issues.
Appendix 1: Information about Home Library Service
Source: Information provided here comes from the actual brochures offered by several Sydney council libraries. Details are omitted as they are not relevant for this assignment.
Appendix 2: Registration form for Home Library volunteers
Appendix 3: Initial Home Library service registration (note: this form is not used for service booking). The form is used by a Sydney council library.
Solution
Task 1:
ERD
Relationships:
Home-bound library users may borrow one or more resources from the library, but they may not borrow any resources at all.
A Volunteer delivers zero or more Library Resources to one or more Home-Bound Library Members, and a Home-Bound Library Member receives delivery from one or more Volunteers.
A Council Library provides zero or more Library Resources, and a Library Resource is provided by one and only one Council Library.
A Home Library Service Coordinator coordinates zero or more Volunteers, and a Volunteer is coordinated by one and only one Home Library Service Coordinator.
A Council Library offers zero or more Home Library Service Coordinators, and a Home Library Service Coordinator is offered by one and only one Council Library.
Table Design
Home-Bound Library Member
Attribute | Data Type | Description |
Full name | string | The full name of the home-bound library member. |
Library membership number | integer | The unique membership number of the home-bound library member. |
Contact number | string | The contact number of the home-bound library member. |
string | The email address of the home-bound library member. | |
Residential address | string | The address where the home-bound library member lives. |
Permission to enter the premises | boolean | Whether or not the home-bound library member grants permission for volunteers to enter their home. |
Emergency contact number | string | The contact number of a person to be contacted in case of emergency. |
Relationship to emergency contact | string | The relationship of the emergency contact person to the home-bound library member. |
About yourself | text | Any additional information provided by the home-bound library member about themselves. |
Library Resource
Attribute | Data Type | Description |
Resource ID | integer | The unique identifier of the library resource. |
Title | string | The title of the library resource. |
Author | string | The author of the library resource. |
Publication year | integer | The year the library resource was published. |
Type | string | The type of the library resource (e.g. book, DVD, magazine). |
Publisher | string | The publisher of the library resource. |
ISBN | string | The International Standard Book Number (ISBN) of the library resource. |
Availability | boolean | Whether or not the library resource is currently available for borrowing. |
Home Library Service Coordinator
Attribute | Data Type | Description |
Title | string | The title of the home library service coordinator. |
First name | string | The first name of the home library service coordinator. |
Surname | string | The surname of the home library service coordinator. |
Age bracket | string | The age bracket of the home library service coordinator. |
Contact phone number | string | The contact phone number of the home library service coordinator. |
string | The email address of the home library service coordinator. |
Volunteer
Attribute | Data Type | Description |
Title | string | The title of the volunteer. |
First name | string | The first name of the volunteer. |
Surname | string | The surname of the volunteer. |
Local resident | boolean | Whether or not the volunteer is a local resident. |
Age bracket | string | The age bracket of the volunteer. |
Contact phone number | string | The contact phone number of the volunteer. |
string | The email address of the volunteer. |
Council Library
Attribute | Data Type | Description |
Library ID | integer | The unique identifier of the council library. |
Name | string | The name of the council library. |
Address | string | The address of the council library. |
Phone Number | String | Stores the phone number |
String | Stores the email address |
Select Queries:
TASK 2
Introduction
The Home Library Service is offered by local council libraries to cater to the needs of library users who are unable to visit the library due to various reasons and are confined to their homes. The individual in charge of the Home Library programme is responsible for overseeing the volunteers who are tasked with executing the programme. The maintenance of volunteer records and service bookings in a manual manner leads to inaccuracies in data and difficulties in communication. The present study presents a database design and implementation plan for the Home Library Service aimed at addressing the identified issues. Additionally, the study provides recommendations for future value-adding activities and tactics.
Decision Environment:
The Library Manager operates within a semi-structured decision-making environment. There exist several decisions that lack organisation, including the management of service reservations and the resolution of member and volunteer concerns. Certain decisions, such as overseeing volunteers and ensuring the maintenance of their police clearance and training documentation, exhibit a high degree of organisation. The task of the library manager entails achieving equilibrium between the requisites of promptness and precision, while also ensuring adaptability to dynamic circumstances.
Organizational Issues:
The manual processing of service requests can result in inaccuracies in data, dissatisfaction among customers, and frustration among volunteers. Efficiency and precision are crucial in the administration of service reservations.
Insufficient communication exists between volunteers and members, as members are unable to determine which volunteer will be available to deliver reserved books when they make phone reservations. In order to ensure accurate fulfilment of requests, it is imperative for the library manager to establish a more robust communication mechanism between members and volunteers.
The process of manually managing volunteer records poses a difficulty in ensuring the currency of their training and police clearance documentation. The records of volunteers are stored in physical folders within a file cabinet and are updated through manual means. As per the library manager, there is a need for enhancement in the process of managing volunteer records.
The timely notification of police clearance renewals and upcoming training sessions for volunteer training and clearance renewals is inadequate, resulting in volunteers being uninformed. In order to ensure that volunteers maintain up-to-date training and police clearance, it is imperative for the library manager to establish a reminder system.
The library manager aims to leverage this service as a strategy for enhancing societal impact. The individual responsible for managing the library is required to devise tactics aimed at expanding the service's scope and engaging a wider audience, particularly those who are confined to their homes.
New Idea:
Implementing an online gateway for service bookings is a novel concept that the library manager should think about. Members could request service bookings simply in this way, and phone and email booking glitches and communication problems would also be resolved. The service coordinator may use the portal to monitor volunteer data and make sure that volunteers' police clearance and training records are up to date. Volunteers might visit the portal to view their weekly schedules.
SQL Queries:
To retrieve all service bookings for a particular date:
SELECT *
FROM ServiceBooking
WHERE ServiceDate = '2023-05-01';
To retrieve all service bookings for a particular volunteer:
SELECT *
FROM ServiceBooking
WHERE VolunteerID = 1;
To retrieve all volunteer records with expired police clearance:
SELECT *
FROM Volunteer
WHERE PoliceClearanceExpirationDate < CURRENT_DATE;
To retrieve all volunteer records with expired training:
SELECT *
FROM Volunteer
WHERE TrainingExpirationDate < CURRENT_DATE;
To retrieve all library resources that have not been borrowed:
SELECT *
FROM LibraryResource
WHERE Availability = 1;Business Process:
One suitable business process that could be used to determine the value proposition of the analytics solutions is a customer satisfaction survey. The Library Manager could design a survey that asks home-bound library members about their experiences with the home library service, including their satisfaction with the service, their experiences with the volunteers, and any suggestions they may have for improving the service. The results of the survey could be used to identify areas where the service is excelling and areas where there is room for improvement. This information could then be used to inform future value-adding actions for the service provider and the recipients of this valuable free service.
Data Quality Issues:
Incomplete or inaccurate volunteer records - this could lead to volunteers being scheduled to provide a service when they do not have a valid police clearance or up-to-date training, putting both the volunteers and the home-bound library members at risk.
Incomplete or inaccurate service booking records - this could result in errors or miscommunications that could disappoint home bound library members and frustrate volunteers.
To mitigate the first data quality issue, the database design includes fields for recording the expiration dates of volunteers' police clearance and training, and the database can be set up to send reminders to volunteers when their police clearance or training is due for renewal. Additionally, if a volunteer's police clearance or training has expired, the database may be configured to disallow scheduling them for services.
The database architecture contains areas for storing the specifics of service reservations, including the desired dates, the required resources, and the contact information of the home-bound library member, in order to address the second data quality issue. The database may be configured to send confirmation emails to both the volunteer assigned to deliver the materials and the home-bound library member after validating the booking details.
Overall, the design and implementation of the Home Library Service database can assist in resolving the Library Manager's data quality concerns as well as offer fresh perspectives and opportunities for innovation in the administration of volunteers and the distribution of library materials to home-bound library patrons. The Home Library Service can maintain its ability to deliver an important and significant service to the community by increasing the effectiveness and accuracy of service reservations and volunteer management.
Conclusion:
For library patrons who are housebound and unable to attend the library physically, the Home Library Service is a helpful service that gives them access to library materials. Currently, service bookings and volunteer records are managed manually, which results in inefficiencies and data inaccuracies. The database design and implementation presented in this report offers a solution to these challenges, providing a more efficient and accurate way to manage service bookings and volunteer records. By implementing the database and adopting the suggested strategies, the Home Library Service can continue to provide a valuable service to the community and engage home-bound library members in meaningful ways.
References
Dole, W. V. (2013). Strategic planning and assessment: Pigs of the same sow?. Journal of Library Administration, 53(4), 283-292.
Druin, A. (2005). What children can teach us: Developing digital libraries for children with children. The library quarterly, 75(1), 20-41.
Rotmianto, M., & Wahyudi, E. U. (2016). Developing Plugin e-DDC as an Additional Application for Senayan Library Management System with PHP Language Programming and MySQL Database. Arabic Alphabet Retrieval System for OPAC Using Digital Tree Method Maisyahtus Su’adaa Irfana, Moch Yasin 1-5 Big Data, Data Analyst, and Improving the Competence of Librarian Albertus Pramukti Narendra 6-11 Challenges and Strategies to Develop a Positive Image of the Library, 18.
Tochukwu, C., Nwachukwu-Nwokeafor, K. C., & Henrieta, U. (2015). Designing a web based digital library management system for institutions and colleges. International Journal of Innovative Science, Engineering and Technology, 2(3), 464-478.