Unit 4 Database Design & Development, Higher National Certificate/Diploma In Computing

Assignment - Database Design and Development

Learning Outcome 1: Use an appropriate design tool to design a relational database system for a substantial problem.

Learning Outcome 2: Develop a fully functional relational database system, based on an existing system design.

Learning Outcome 3: Test the system against user and system requirements.

Learning Outcome 4: Produce technical and user documentation.

After completion of this assignment, learners will be able to:
- Use RDBMS tools to build a database system which satisfy business requirements.
- Create database in a RDBMS product.
- Manipulate data against the database server using SQL queries
- Test if the requirements are satisfied
- Create documentations for the developed database system

Vocational scenario

Assignment brief
As a database developer in the iNet College, you are assigned to design and develop a database system for library management system of iNet College. The system should be designed to support the following functions:
• User Registration: The library management system should allow students, faculty, and staff to register for library access.
• Book Cataloging: The system should allow librarians to add, edit, and delete books from the library's collection. There can be multi-copies of a book but each will have its unique Id. Students and staff are allowed maximum of 3 books on loan while faculty is allowed 5 books concurrently.
• Loan: The system should allow users to borrow books from the library's collection. At a time, a user can borrow one or more books. The loan period is 7 days for student & staff, 1 month for faculty. When a book is borrowed, its status must be updated. If a user exceeds his/her maximum loan limit, he/she will not be able to borrow until their borrowed books are returned.
• Returning Books: The system should allow users to return books to the library. When a user returns a book, the system should update the book's availability status and the date of the transaction.
• Book Reservation: The system should allow users to reserve a book that is currently checked out. When the book is returned, the system should notify the user who has reserved it. The serving should be first-in first-out according to their reservation date. Once a reserved book is available, Liberian will contact user if they still wish to proceed. Otherwise Liberian will cancel their reservation and will proceed to the next reservation.
• Fine Calculation: Late returns will be fined 100 Ks per day per book. The system should automatically calculate and track any fines incurred by users who return books late.

• Renewing Books: The system should allow users to renew books that they have checked out, provided the book has not been reserved by another user.
• Book Search: The system should allow users to search for books by title, author, or subject, or ISBN.
• Reporting: The system should generate reports for librarians to track the status of books, including the number of copies available, the number of books checked out, and the number of fines incurred.
• Notifications: The Liberian should notify users when their borrowed books are due, when their reservati1ons are available. System should generate a report for the Liberian to do the job of notification.

Security Requirements

• Librarian should be able to enter new books, update existing books.
• Librarian should not be able to delete books from books catalogue.
• Librarian should be able to register, and modify borrowers but he/she should not be allowed to delete borrowers.
• Librarian should be able to enter loan information, and book returns but she/she should not be able to delete loan information.
• Librarian should be able to enter book reservations, change reservations but he/she should not be able to delete reservations.
• Librarian Manager should be able to do everything. But it is recommended that data should not be deleted.
• But it is highly recommended every data should allow soft deletion. Example, changing deletion flag rather than actual deletion of a record.

Your task is to design, develop, and test the Library Management System using appropriate design tools and techniques. You should produce a relational database. The database should support the functions outlined above.
You are required to produce technical and user documentation, including a user interface, output, and data validations. You should implement a query language into the relational database system and test the system against user and system requirements.

Assignment activity

The project will be assessed based on the following criteria:
Design: You should design a relational database system using appropriate design tools and techniques. The design should contain at least four interrelated tables and have clear statements of user and system requirements.
Development: You should develop the database system with evidence of user interface, output, and data validations. The system should support the functions outlined above and should be developed using appropriate programming languages.
Query Language: You should implement a query language into the relational database system.
Testing: You should test the system against user and system requirements. You should demonstrate that the system works correctly and meets the needs of users.
Documentation: You should produce technical and user documentation that describes the system's design, development, and testing. The documentation should be clear, concise, and well-organized.

Part 1:
1. Design library database system.
2. You should provide conceptual design, logical design, & physical design with normalized to 3NF.
3. Identify and point out data validation rules such as entity integrity, domain integrity, and referential integrity.
4. Then, evaluate how your provided solution will solve the business requirements given above.

Part 2:
1. Develop Library database system in Microsoft SQL Server.
2. Insert provided data into the database.
3. Write queries to fulfil given requirements.
4. Provide evidence of screenshots, output result-set and data validations, and querying across multiple tables to generate required reports.

Part 3:
1. You should test the system against user and system requirements. You should demonstrate that the system works correctly and meets the needs of users. Provide screenshots.
2. Produce a brief report assessing the effectiveness of the testing, including an explanation of the choice of test data used.

Part 4:
1. Lastly, produce technical and user documentation such as Provide data dictionary. Provide SQL script to create database, tables, and constraints.
2. You should produce technical and user documentation for a fully functional system, including diagrams showing movement of data through the system, and flowcharts describing how the system works. The documentation should be clear, concise, and well-organized.

Attachment:- Database Design and Development.rar