It demonstrates practical database design, normalisation, and implementation using SQL Server features.
The objective was to design and implement the database to support all client needs. Key tasks included:
- Designing a relational database schema and normalising it to third normal form.
- Creating related database objects such as stored procedures, user defined functions, views, and triggers.
- Applying constraints to ensure data accuracy and integrity across the system.
The library needed a system that supports the following:
- Member registration with full name, address, date of birth, username, and password.
- Optional details including email address and telephone number.
- Tracking overdue fines, repayments, and outstanding balances.
- Retaining member information after membership ends for marketing and audit purposes.
- ull online sign up and login capability.
- Detailed recording of repayments including date, amount, and payment method.
- A complete catalogue of items, including:
- Title, type (Book, Journal, DVD, or Other Media), author, publication year
- Date added, status (On Loan, Overdue, Available, or Lost/Removed)
- ISBN tracking for books only
- Date marked as lost or removed where applicable
- Full loan history with:
- Member, item, loan date, due date, return date
- Overdue fee calculation at 10p per day
The ERD below illustrates the final database structure designed for this project.
