Database Design and Development

This content provides a comprehensive guide to database design and development. It covers topics such as relational database systems, data normalization, data validations, and more. The content also includes study material and solved assignments for students to improve their skills in this field.

Database Design and
Development
Assignment 1 Relational Database Management System
Rashed Salem 12/9/23 ID: 2210065

1
Table of Contents
P1 ...................................................................................................................................................................................................... 8
RELATIONAL DATABASE SYSTEM .................................................................................................................................................................... 8
Introduction ...................................................................................................................................................................................... 8
In a relational database .................................................................................................................................................................................... 8
Tables .......................................................................................................................................................................................................... 8
Relationships ............................................................................................................................................................................................... 8
User Requirements ........................................................................................................................................................................... 8
System Requirements ....................................................................................................................................................................... 8
Design ............................................................................................................................................................................................... 9
Entity-Relationship (ER) Model: ........................................................................................................................................................................ 9
Mapping Design: ............................................................................................................................................................................................. 10
Logical Database Design: ................................................................................................................................................................................ 10
M1 ................................................................................................................................................................................................... 11
DATA NORMALIZATION & DATA VALIDATIONS & INTERFACE AND OUTPUT DESIGNS ............................................................................................. 11
Data Normalization ........................................................................................................................................................................ 11
Type of Normalization: ................................................................................................................................................................................... 11
1.First Normal Form (1NF): ........................................................................................................................................................................ 11
2. Second Normal Form (2NF): .................................................................................................................................................................. 11
3. Third Normal Form (3NF): ...................................................................................................................................................................... 11
4. Boyce-Codd Normal Form (BCNF): ........................................................................................................................................................ 11
Logical Design Analysis is normalized or not: ................................................................................................................................................. 11
⎯ Book Table: ..................................................................................................................................................................................... 11
⎯ Author Table: .................................................................................................................................................................................. 11
⎯ Publisher Table: .............................................................................................................................................................................. 12
⎯ Member Table:................................................................................................................................................................................ 12
⎯ Loan Table: ...................................................................................................................................................................................... 12
Normalization: ........................................................................................................................................................................................... 12
Data Validations ............................................................................................................................................................................. 12
Interface and Output Designs ......................................................................................................................................................... 14
Input Interface ................................................................................................................................................................................................ 14
1. Add New Book................................................................................................................................................................................. 14
2. Loan Book to Member .................................................................................................................................................................... 14
Output Interface ............................................................................................................................................................................................. 15
1. Member Information ...................................................................................................................................................................... 15
2. Book Information ............................................................................................................................................................................ 15
D1 .................................................................................................................................................................................................... 16
EFFECTIVENESS OF THE DATABASE .................................................................................................................................................. 16
Effectiveness of User Requirements ............................................................................................................................................... 16
1. Add New Books .................................................................................................................................................................................... 16
2. Update Book Information..................................................................................................................................................................... 16
3. Member Registration ........................................................................................................................................................................... 16
4. Loan Books ........................................................................................................................................................................................... 16
5. Generate Reports ................................................................................................................................................................................. 16
Effectiveness of System Requirements ........................................................................................................................................... 17
1. Data Integrity and Consistency ............................................................................................................................................................ 17
2. Role-Based Access ................................................................................................................................................................................ 17
3. Scalability ............................................................................................................................................................................................. 17

2
4. Regular Backups ................................................................................................................................................................................... 17
Evaluation Summary ...................................................................................................................................................................... 17
P2 .................................................................................................................................................................................................... 18
DEVELOP A RELATIONAL DATABASE SYSTEM .................................................................................................................................................. 18
Create Table ................................................................................................................................................................................... 18
Insert Table Value ........................................................................................................................................................................... 20
Select Tables ................................................................................................................................................................................... 21
simple interface to insert, update and delete data in the database .............................................................................................. 22
P3 .................................................................................................................................................................................................... 24
DATA MANIPULATION LANGUAGE (DML) .................................................................................................................................................... 24
Data Manipulation Language......................................................................................................................................................... 24
SELECT:............................................................................................................................................................................................................ 24
UPDATE: .......................................................................................................................................................................................................... 24
DELETE: ........................................................................................................................................................................................................... 25
M2 ................................................................................................................................................................................................... 26
SECURITY & DATABASE MAINTENANCE ........................................................................................................................................................ 26
Data Security .................................................................................................................................................................................. 26
Security Mechanisms Type ............................................................................................................................................................................. 26
1. User Authentication and Authorization: ......................................................................................................................................... 26
2. Access Control: ................................................................................................................................................................................ 26
3. Encryption: ...................................................................................................................................................................................... 26
4. Data Protection: .............................................................................................................................................................................. 27
5. Data Integrity: ................................................................................................................................................................................. 27
6. Compliance: .................................................................................................................................................................................... 27
7. User Accountability: ........................................................................................................................................................................ 27
8. Preventing SQL Injection: ................................................................................................................................................................ 27
9. Data Confidentiality: ....................................................................................................................................................................... 28
10. Preventing Unauthorized Modifications: ........................................................................................................................................ 28
M3 ................................................................................................................................................................................................... 29
SQL STATEMENTS .................................................................................................................................................................................... 29
Explanation of SQL Statements: ..................................................................................................................................................... 29
1. GROUP BY ............................................................................................................................................................................................. 29
2. ORDER BY ............................................................................................................................................................................................. 29
3. HAVING ................................................................................................................................................................................................ 30
4. BETWEEN.............................................................................................................................................................................................. 30
5. WHERE ................................................................................................................................................................................................. 31
D2 .................................................................................................................................................................................................... 32
EVALUATE THE EFFECTIVENESS OF THE DATABASE & SUGGEST IMPROVEMENTS .................................................................................................... 32
Effectiveness Evaluation ................................................................................................................................................................. 32
User Requirements: ........................................................................................................................................................................................ 32
1. Add New Books: .............................................................................................................................................................................. 32
2. Update Book Information: .............................................................................................................................................................. 32
3. Member Registration: ..................................................................................................................................................................... 32
4. Loan Books: ..................................................................................................................................................................................... 32
5. Generate Reports: ........................................................................................................................................................................... 32
System Requirements: .................................................................................................................................................................................... 33
1. Data Integrity and Consistency: ...................................................................................................................................................... 33

3
2. Role-Based Access: .......................................................................................................................................................................... 33
3. Scalability: ....................................................................................................................................................................................... 33
4. Regular Backups: ............................................................................................................................................................................. 33
Overall Suggestions for Improvement: ........................................................................................................................................... 33
1. User Interface Enhancement:............................................................................................................................................................... 33
2. Security Enhancements: ....................................................................................................................................................................... 33
3. Audit Trail Implementation: ................................................................................................................................................................. 33
4. Advanced Reporting: ............................................................................................................................................................................ 33
5. Feedback Mechanism: .......................................................................................................................................................................... 34
6. Performance Optimization: .................................................................................................................................................................. 34
7. Documentation: ................................................................................................................................................................................... 34
P4 .................................................................................................................................................................................................... 35
DATABASE TESTING .................................................................................................................................................................................. 35
Database Testing Test Plan ............................................................................................................................................................ 35
Functionality Testing: ...................................................................................................................................................................................... 35
Add New Books: ........................................................................................................................................................................................ 35
Update Book Information: ......................................................................................................................................................................... 35
Member Registration: ............................................................................................................................................................................... 35
Loan Books: ............................................................................................................................................................................................... 35
Performance Testing: ...................................................................................................................................................................................... 35
Scalability: .................................................................................................................................................................................................. 35
Transaction Speed: .................................................................................................................................................................................... 35
Security Testing: ............................................................................................................................................................................................. 35
Access Controls: ......................................................................................................................................................................................... 35
Data Encryption: ........................................................................................................................................................................................ 35
Usability Testing:............................................................................................................................................................................................. 35
User Interface: ........................................................................................................................................................................................... 35
Error Handling: .......................................................................................................................................................................................... 36
SQL Query Testing:.......................................................................................................................................................................................... 36
Update Statement: .................................................................................................................................................................................... 36
Order By Clause: ........................................................................................................................................................................................ 36
Joining Tables: ........................................................................................................................................................................................... 36
Conditions using WHERE Clause: ............................................................................................................................................................... 36
Grouping and Aggregation: ....................................................................................................................................................................... 36
Validation Processes: ...................................................................................................................................................................................... 36
Data Validation: ......................................................................................................................................................................................... 36
Query Output Validation: .......................................................................................................................................................................... 36
Test Cases for Database System ..................................................................................................................................................... 36
1. Add New Books: ................................................................................................................................................................................... 36
- Test Case: ........................................................................................................................................................................................ 36
- Expected Result:.............................................................................................................................................................................. 36
- Test Case: ........................................................................................................................................................................................ 36
- Expected Result:.............................................................................................................................................................................. 36
- Test Case: ........................................................................................................................................................................................ 37
- Expected Result:.............................................................................................................................................................................. 37
2. Update Book Information: ................................................................................................................................................................... 37
- Test Case: ........................................................................................................................................................................................ 37
- Expected Result:.............................................................................................................................................................................. 37
- Test Case: ........................................................................................................................................................................................ 37
- Expected Result:.............................................................................................................................................................................. 37
3. Member Registration: .......................................................................................................................................................................... 37
- Test Case: ........................................................................................................................................................................................ 37

4
- Expected Result:.............................................................................................................................................................................. 37
- Test Case: ........................................................................................................................................................................................ 37
- Expected Result:.............................................................................................................................................................................. 37
4. Loan Books: .......................................................................................................................................................................................... 37
- Test Case: ........................................................................................................................................................................................ 37
- Expected Result:.............................................................................................................................................................................. 37
- Test Case: ........................................................................................................................................................................................ 37
- Expected Result:.............................................................................................................................................................................. 37
M4 ................................................................................................................................................................................................... 38
ASSESSMENT OF TEST EFFECTIVENESS .......................................................................................................................................................... 38
Explanation of Test Data Selection ................................................................................................................................................. 38
1. Functionality Testing: ........................................................................................................................................................................... 38
2. Performance Testing: ........................................................................................................................................................................... 38
3. Security Testing: ................................................................................................................................................................................... 38
4. Usability Testing: .................................................................................................................................................................................. 38
5. SQL Query Testing: ............................................................................................................................................................................... 38
6. Validation Processes: ........................................................................................................................................................................... 38
1. Automated Testing: .............................................................................................................................................................................. 39
- Advantages: .................................................................................................................................................................................... 39
- Implementation: ............................................................................................................................................................................. 39
2. Diversify Test Data: .............................................................................................................................................................................. 39
- Advantages: .................................................................................................................................................................................... 39
- Implementation: ............................................................................................................................................................................. 39
3. User Feedback Integration: .................................................................................................................................................................. 39
- Advantages: .................................................................................................................................................................................... 39
- Implementation: ............................................................................................................................................................................. 39
4. Continuous Testing:.............................................................................................................................................................................. 39
- Advantages: .................................................................................................................................................................................... 39
- Implementation: ............................................................................................................................................................................. 39
5. Test Case Reviews and Collaboration: .................................................................................................................................................. 40
- Advantages: .................................................................................................................................................................................... 40
- Implementation: ............................................................................................................................................................................. 40
6. Exploratory Testing: ............................................................................................................................................................................. 40
- Advantages: .................................................................................................................................................................................... 40
- Implementation: ............................................................................................................................................................................. 40
7. Performance Testing with Realistic Scenarios: ..................................................................................................................................... 40
- Advantages: .................................................................................................................................................................................... 40
- Implementation: ............................................................................................................................................................................. 40
8. Regression Testing Suites: .................................................................................................................................................................... 40
- Advantages: .................................................................................................................................................................................... 40
- Implementation: ............................................................................................................................................................................. 40
9. Comprehensive Documentation: ......................................................................................................................................................... 41
- Advantages: .................................................................................................................................................................................... 41
- Implementation: ............................................................................................................................................................................. 41
10. Load Testing: ........................................................................................................................................................................................ 41
- Advantages: .................................................................................................................................................................................... 41
- Implementation: ............................................................................................................................................................................. 41
D2 .................................................................................................................................................................................................... 42
EVALUATE THE EFFECTIVENESS OF THE DATABASE & SUGGEST IMPROVEMENTS .................................................................................................... 42
Feedback interface ......................................................................................................................................................................... 42

5
P5 .................................................................................................................................................................................................... 43
USERS’ GUIDE AND A TECHNICAL DOCUMENTATION ....................................................................................................................................... 43
User's Guide .................................................................................................................................................................................... 43
Introduction .................................................................................................................................................................................................... 43
Table of Contents ............................................................................................................................................................................................ 43
1. Logging In ........................................................................................................................................................................................ 43
2. Dashboard Overview ....................................................................................................................................................................... 43
3. Managing Books .............................................................................................................................................................................. 43
4. Member Functions .......................................................................................................................................................................... 43
5. Reports ............................................................................................................................................................................................ 43
6. Settings ........................................................................................................................................................................................... 43
1. Logging In ............................................................................................................................................................................................. 43
- To access the system: ..................................................................................................................................................................... 43
2. Dashboard Overview ............................................................................................................................................................................ 44
- Upon logging in, you'll find a dashboard with key features: ........................................................................................................... 44
3. Managing Books ................................................................................................................................................................................... 44
- Adding New Books: ......................................................................................................................................................................... 44
- Updating Book Information: ........................................................................................................................................................... 44
- Viewing Book List: ........................................................................................................................................................................... 44
4. Member Functions ............................................................................................................................................................................... 44
- Registering New Members: ............................................................................................................................................................. 44
- Managing Member Information: .................................................................................................................................................... 44
- Handling Book Loans and Returns: ................................................................................................................................................. 44
5. Reports ................................................................................................................................................................................................. 44
- Generating Reports: ........................................................................................................................................................................ 44
6. Settings ................................................................................................................................................................................................. 44
- Customizing Preferences: ............................................................................................................................................................... 44
Technical Documentation ............................................................................................................................................................... 45
System Architecture ....................................................................................................................................................................................... 45
1. Presentation Tier: ........................................................................................................................................................................... 45
2. Application Tier: .............................................................................................................................................................................. 45
3. Database Tier: ................................................................................................................................................................................. 45
Technologies Used .......................................................................................................................................................................................... 45
1. Programming Language: Python ..................................................................................................................................................... 45
2. GUI Framework: Tkinter (for desktop application) ......................................................................................................................... 45
3. Database: SQLite ............................................................................................................................................................................. 45
4. Backend Framework: Flask (for web version) ................................................................................................................................. 45
Database Schema ........................................................................................................................................................................................... 45
1. Books............................................................................................................................................................................................... 45
2. Authors ........................................................................................................................................................................................... 45
3. Members ......................................................................................................................................................................................... 45
4. Loans ............................................................................................................................................................................................... 45
5. Publishers ........................................................................................................................................................................................ 45
Maintenance Procedures ................................................................................................................................................................................ 45
• Regular Backups: ............................................................................................................................................................................. 45
• Updates and Bug Fixes: ................................................................................................................................................................... 45
• Security Measures: .......................................................................................................................................................................... 45
• Performance Monitoring: ............................................................................................................................................................... 45
• User Training: .................................................................................................................................................................................. 46
• Documentation Maintenance: ........................................................................................................................................................ 46
Troubleshooting .............................................................................................................................................................................................. 46
• Common Issues: .............................................................................................................................................................................. 46

6
• Support Channels: ........................................................................................................................................................................... 46
M5 ................................................................................................................................................................................................... 47
USER DOCUMENTATION ............................................................................................................................................................................ 47
Flowchart for System: ..................................................................................................................................................................... 47
Flowchart ........................................................................................................................................................................................................ 47
- Start: ............................................................................................................................................................................................... 47
- Main Menu: .................................................................................................................................................................................... 48
 Options include: ......................................................................................................................................................................... 48
- Book Management:......................................................................................................................................................................... 48
- Member Registration: ..................................................................................................................................................................... 48
- Loaning Books: ................................................................................................................................................................................ 48
- Returning Books: ............................................................................................................................................................................. 48
- Exit: ................................................................................................................................................................................................. 48
- End: ................................................................................................................................................................................................. 48
Context Level DFD for Loaning a Book ............................................................................................................................................................ 49
Context Level DFD Description: ................................................................................................................................................................. 49
- Library System: .......................................................................................................................................................................... 49
- User: .......................................................................................................................................................................................... 49
- Librarian: .................................................................................................................................................................................... 49
- Interactions: ............................................................................................................................................................................... 49
- Data Flow: .................................................................................................................................................................................. 50
- Processes: .................................................................................................................................................................................. 50
Context Level 1 DFD for Loaning a Book ......................................................................................................................................................... 50
Context Level 1 DFD Description: .............................................................................................................................................................. 51
- Process Descriptions: ................................................................................................................................................................. 51
- Data Flows: ................................................................................................................................................................................ 51
- External Entities: ........................................................................................................................................................................ 51
UML Diagram ................................................................................................................................................................................. 52
Use case .......................................................................................................................................................................................................... 52
Use Case Diagram Description: .................................................................................................................................................................. 52
- Actors: ........................................................................................................................................................................................ 52
- Use Cases: .................................................................................................................................................................................. 52
- Relationships: ............................................................................................................................................................................ 53
Class Diagram .................................................................................................................................................................................................. 53
Class Diagram Description: ........................................................................................................................................................................ 53
D3 .................................................................................................................................................................................................... 54
EVALUATE THE DATABASE AND IDENTIFY IMPROVEMENTS FOR CONTINUED EFFECTIVENESS .................................................................................... 54
Several Areas to Assess and Potential Improvements .................................................................................................................... 54
1. Performance Optimization: .................................................................................................................................................................. 54
- Assessment: .................................................................................................................................................................................... 54
- Improvements: ................................................................................................................................................................................ 54
2. Security Enhancements: ....................................................................................................................................................................... 54
- Assessment: .................................................................................................................................................................................... 54
- Improvements: ................................................................................................................................................................................ 54
3. User Experience (UX) and Interface Improvements: ............................................................................................................................ 54
- Assessment: .................................................................................................................................................................................... 54
- Improvements: ................................................................................................................................................................................ 54
4. Data Validation and Integrity: .............................................................................................................................................................. 55
- Assessment: .................................................................................................................................................................................... 55
- Improvements: ................................................................................................................................................................................ 55
5. Scalability Considerations: ................................................................................................................................................................... 55

7
- Assessment: .................................................................................................................................................................................... 55
- Improvements: ................................................................................................................................................................................ 55
6. Backup and Recovery Mechanisms: ..................................................................................................................................................... 55
- Assessment: .................................................................................................................................................................................... 55
- Improvements: ................................................................................................................................................................................ 55
7. Documentation: ................................................................................................................................................................................... 55
- Assessment: .................................................................................................................................................................................... 55
- Improvements: ................................................................................................................................................................................ 55
8. Regulatory Compliance: ....................................................................................................................................................................... 56
- Assessment: .................................................................................................................................................................................... 56
- Improvements: ................................................................................................................................................................................ 56
9. Collaboration with Stakeholders: ......................................................................................................................................................... 56
- Assessment: .................................................................................................................................................................................... 56
- Improvements: ................................................................................................................................................................................ 56
10. Continuous Monitoring and Analysis: .................................................................................................................................................. 56
- Assessment: .................................................................................................................................................................................... 56
- Improvements: ................................................................................................................................................................................ 56
REFERENCES ............................................................................................................................................................................................ 57

8
P1
Relational Database System
Introduction
A relational database is a type of database that uses a structure that allows data to be defined,
organized, and accessed in a way that supports relationships between different pieces of data. It is
based on the principles of relational algebra and was first proposed by Edgar F. Codd in 1970.
In a relational database
Tables
• Data is stored in tables, which are two-dimensional structures with rows and columns. Each
row in a table represents a record, while each column represents an attribute or field.
Relationships
• Tables can be related to each other based on common attributes. This relationship is
established using keys, such as primary keys and foreign keys. A primary key uniquely
identifies each record in a table, while a foreign key links records in one table to records in
another.
User Requirements
- Users should be able to easily search and retrieve information about books.
- Users should be able to add, update, and delete member and book details.
- Users should be able to track the availability of books and their loan history.
- Users should be able to view reports and insights regarding the library's operations.
- Users should have different roles and access levels based on their responsibilities.
System Requirements
- The database should be able to efficiently store and retrieve large amounts of data.
- The system should handle concurrent transactions and ensure data integrity.
- The system should provide a user-friendly interface for data entry and retrieval.
- The database should support backups and data recovery mechanisms.
- The system should be scalable to accommodate future growth in the number of
members and books.

9
Design
To design a relational database system for the Royal College Dubai Library, we can create tables for
Books, Authors, Members, Loans, Publishers, and other related entities. Here's a basic structure for
the database, along with explanations of each table:
Books Table:
Fields: BookID (Primary Key), Title, PublishedYear, Genre, Quantity.
Authors Table:
Fields: AuthorID (Primary Key), FirstName, LastName.
Members Table:
Fields: MemberID (Primary Key), FirstName, LastName, Email, Phone, Address.
Loans Table:
Fields: LoanID (Primary Key), LoanDate, DueDate, ReturnDate.
Publishers Table:
Fields: PublisherID (Primary Key), PublisherName, Address, Phone.
Entity-Relationship (ER) Model:

10
Mapping Design:
Logical Database Design:
Entities Fields
Books BookID, Title, PublishedYear, Genre, Quantity.
Authors AuthorID, FirstName, LastName.
Members MemberID, FirstName, LastName, Email, Phone, Address.
Loans LoanID, LoanDate, DueDate, ReturnDate.
Publishers PublisherID, PublisherName, Address, Phone.

11
M1
Data Normalization & Data Validations & Interface and
Output Designs
Data Normalization
Data Normalization:
Data normalization is a process in database design that organizes data in a relational database to
reduce redundancy and dependency. The main goal is to ensure data integrity and minimize data
anomalies, such as update, insertion, and deletion anomalies. The normalization process involves
breaking down large tables into smaller, related tables and organizing the data to eliminate
redundancy.
The normalization process is typically divided into several normal forms (1NF, 2NF, 3NF, BCNF),
each addressing specific issues related to data redundancy and dependency.
Type of Normalization:
1.First Normal Form (1NF):
• Each column should contain atomic (indivisible) values.
• There should be no repeating groups or arrays.
2. Second Normal Form (2NF):
• Must be in 1NF.
• All non-key attributes are fully functionally dependent on the primary key.
3. Third Normal Form (3NF):
• Must be in 2NF.
• Eliminate transitive dependencies.
4. Boyce-Codd Normal Form (BCNF):
• Must be in 3NF.
• Further elimination of dependencies.
Logical Design Analysis is normalized or not:
Now, let's check whether the provided logical design is normalized:
⎯ Book Table:
o No repeating groups, each column is atomic.
o No partial dependencies.
⎯ Author Table:
o No repeating groups, each column is atomic.
o No partial dependencies.

12
⎯ Publisher Table:
o No repeating groups, each column is atomic.
o No partial dependencies.
⎯ Member Table:
o No repeating groups, each column is atomic.
o No partial dependencies.
⎯ Loan Table:
o All columns are directly related to the primary key (LoanID).
o No transitive dependencies.
Normalization:
The provided logical design appears to be in at least 3NF. However, to ensure BCNF, we need to
evaluate whether there are any non-trivial functional dependencies. Given the structure of the tables,
it seems that the design is already in BCNF. Therefore, no further normalization is required.
It's important to note that normalization depends on the specific use cases and requirements of the
application. Sometimes, denormalization may be acceptable for performance reasons, but this should
be a conscious decision based on the specific needs of the system.
Data Validations
Validation mechanism:
Validation is a process that ensures the delivery of a clear data to the program. It checks for the
integrity and validity of data that is being input to different software and its components. Data
validation is also known as input validation. Constraint Validation is a type of data validation.
Constraint Validation is algorithm browsers run when a form is submitted to determine validity.
There are methods of validations which are named below:
⎯ Primary Key Constraints:
o Example: In the ' Book ' table, the ' BookID ' serves as the primary key. This constraint
ensures that each book has a unique identifier, preventing the insertion of duplicate
records.
⎯ Unique Constraints:
o Example: In the ' Member ' table, the ' MemberID ' could have a unique constraint,
ensuring that each member has a distinct identifier, avoiding duplicate member entries.
⎯ Check Constraints:
o Example: In the ' Book ' table, a check constraint might be added to ensure that the '
PublicationYear ' is within a valid range (between 1800 and the current year).

13
⎯ Foreign Key Constraints:
o Example: In the ' Loan ' table, the ' BookID ' and ' MemberID ' columns are foreign
keys. These constraints ensure that a book cannot be loaned to a non-existent member
and vice versa.
⎯ Data Type Validation:
o Example: Ensure that columns containing dates, such as ' LoanDate ' and ' DueDate '
in the ' Loan ' table, only accept valid date formats and values.
⎯ Length Validation:
o Example: In the ' Member ' table, ensure that the length of the ' ContactInformation '
column does not exceed a predefined limit (20 characters).
⎯ Range Validation:
o Example: In the ' Book ' table, the ' Quantity ' column should not accept negative
values, ensuring that the quantity of books is always a non-negative integer.
⎯ Default Values:
o Example: Set default values for certain columns. For instance, the ' ReturnDate ' in the
' Loan ' table might have a default value of NULL, indicating that the book has not been
returned.
⎯ Triggers:
o Example: Implement a trigger to enforce additional business rules. For instance, a
trigger could be used to prevent loans if a member has exceeded their maximum
allowed loans.
⎯ Regular Expressions (Regex):
o Example: Use regular expressions to validate specific patterns in data. For instance,
ensuring that the ' ContactInformation ' in the ' Member ' table follows a valid email
format.
⎯ Custom Validation Rules:
o Example: Implement custom validation rules in the application logic or database
procedures. For instance, ensuring that the ' DueDate ' in the ' Loan ' table is always
later than the ' LoanDate '

14
Interface and Output Designs
According to the scenario the following are the functionalities of the system:
1. Add New Book
2. Loan Book to Member
3. Member Information
4. Book Information
Input Interface
1. Add New Book
2. Loan Book to Member

15
Output Interface
1. Member Information
2. Book Information

16
D1
Effectiveness Of the Database
Effectiveness of User Requirements
1. Add New Books
o Representation in Design: There's a "Book" table with attributes like BookID, Title,
Quantity. allowing librarians to add new books. The Book entity is appropriately related
to the Author and Publisher entities.
o Effectiveness: The design addresses the requirement effectively.
2. Update Book Information
o Representation in Design: The "Book" table allows librarians to update information like
Title, Author, Publisher.
o Effectiveness: The design caters to the requirement.
3. Member Registration
o Representation in Design: The "Member" table allows the registration of new
members with attributes like MemberID, Name, Contact Information, and Registration
Date.
o Effectiveness: The design meets the requirement.
4. Loan Books
o Representation in Design: The "Loan" table allows for loaning books to members,
tracking Loan Date, Due Date, and Return Date.
o Effectiveness: The design addresses the loaning requirement.
5. Generate Reports
o Representation in Design: Although not explicitly shown in the design, the logical
structure allows for potential extensions to include reporting functionality.
o Effectiveness: While not explicitly represented, the design allows for future expansion
to meet this requirement.

17
Effectiveness of System Requirements
1. Data Integrity and Consistency
o Representation in Design: The use of primary keys, foreign keys, and normalization
ensures data integrity and consistency.
o Effectiveness: The design effectively maintains data integrity.
2. Role-Based Access
o Representation in Design: Role-based access is not explicitly shown in the design but
can be implemented at the application level.
o Effectiveness: The design allows for the implementation of role-based access.
3. Scalability
o Representation in Design: The normalized structure supports scalability as the
database grows.
o Effectiveness: The design is effective for scalability.
4. Regular Backups
o Representation in Design: Regular backups are a procedural aspect and not explicitly
represented in the design.
o Effectiveness: The design doesn't inherently address regular backups but can be
implemented at the database management level.
Evaluation Summary
Positive Aspects
• The design effectively represents the core functionalities required by users (adding books,
updating information, member registration, loaning books).
• It maintains data integrity and consistency through primary keys, foreign keys, and
normalization.
• The design supports scalability.
Areas for Improvement
• Role-based access and regular backups, while not explicitly represented in the design, can be
implemented at the application and database management levels, respectively.
• The design could be extended to include more explicit representation of reporting functionality.
In conclusion, the design effectively represents the majority of user and system requirements, with
some areas for enhancement and future extension. It's essential to implement additional features
(reporting, backups) and consider security aspects during the application development and database
management phases.

18
P2
Develop a Relational Database System
Create Table
1. Loan Table
2. Publisher Table

19
3. Member Table
4. Author Table

20
5. Book Table
Insert Table Value
All Insertion Table

21
Select Tables
1. Retrieve Book Information with Author Details
2. Retrieve Loan Information with Member and Book Details
3. Retrieve Members with Overdue Books

22
4. Retrieve Publishers and the Books They Have Published
5. Retrieve the Total Quantity of Books Loaned by Genre
simple interface to insert, update and delete data in the database

23

24
P3
Data Manipulation Language (DML)
Data Manipulation Language
A data manipulation language (DML) is a family of computer languages including commands
permitting users to manipulate data in a database. This manipulation involves inserting data into
database tables, retrieving existing data, deleting data from existing tables and modifying existing
data. DML is mostly incorporated in SQL databases.
Data Manipulation Language (DML) in SQL is used for managing data within a relational database.
Here are explanations and examples for the SELECT, UPDATE, and DELETE statements using the
developed database:
SELECT:
Usage: The SELECT statement is used to retrieve data from one or more tables.
Example: Retrieve information about books along with the names of their authors.
UPDATE:
Usage: The UPDATE statement is used to modify existing records in a table.
Example: Update the quantity of a specific book in the Books table.

25
DELETE:
Usage: The DELETE statement is used to remove records from a table.
Example: Delete a loan record from the Loans table when the book has been returned.

26
M2
Security & Database Maintenance
Data Security
Security mechanisms
Database security is a top priority for today’s organizations. Database security concerns the use of a
broad range of information security controls to protect databases against compromises of their
confidentiality, integrity and availability. It involves various types or categories of controls, such as
technical, procedural/administrative and physical. Database security is a specialist topic within the
broader realms of computer security, information security and risk management.
Security Mechanisms Type
1. User Authentication and Authorization:
• Explanation: Users must authenticate themselves to access the database. Authorization
ensures that users have specific privileges and permissions based on their roles or user
groups.
• Implementation: Create user accounts with strong authentication mechanisms. Assign
roles to users and grant appropriate permissions to these roles.
2. Access Control:
• Explanation: Restrict access to sensitive data and operations to authorized users only.
Users should only be able to perform actions they are explicitly allowed to do.
• Implementation: Use access control mechanisms such as GRANT and REVOKE SQL
statements to manage user permissions. Assign specific privileges to users or roles.
3. Encryption:
• Explanation: Encrypt data at rest and during transmission to protect it from unauthorized
access. This is especially important for sensitive information.
• Implementation: Use encryption algorithms and protocols for secure data storage and
transmission. This can include SSL/TLS for network encryption and disk-level
encryption for data at rest.

27
4. Data Protection:
• Explanation: Data protection involves safeguarding sensitive information from
unauthorized access. This ensures that only authorized users have the privilege to view
or retrieve specific data, preserving its confidentiality.
• Implementation: Implement user authentication and authorization mechanisms to
control access to sensitive data. Encrypt sensitive data to provide an additional layer of
protection.
5. Data Integrity:
• Explanation: Data integrity ensures that data remains accurate and consistent
throughout its lifecycle. Access controls are essential to prevent unauthorized users
from making changes that could compromise the accuracy and reliability of the data.
• Implementation: Set up proper access controls and permissions to restrict modifications
to critical data. Implement referential integrity constraints to maintain relationships
between tables.
6. Compliance:
• Explanation: Compliance involves adhering to established security standards and best
practices to meet regulatory requirements and industry standards. It ensures that the
organization operates within legal frameworks.
• Implementation: Regularly review and update security policies to align with relevant
regulations. Conduct audits and assessments to ensure compliance with industry-
specific standards.
7. User Accountability:
• Explanation: User accountability refers to tracking and logging user activities within the
database system. It helps identify any suspicious or unauthorized actions, promoting
transparency and responsibility.
• Implementation: Implement audit trails and logging mechanisms to record user
activities. Associate actions with specific user accounts to trace any unauthorized or
suspicious behavior.
8. Preventing SQL Injection:
• Explanation: SQL injection is a common attack where malicious SQL code is injected
into input fields. Proper access controls and validation mechanisms prevent SQL
injection by limiting the actions users can perform.
• Implementation: Use parameterized queries and prepared statements to sanitize user
inputs. Implement input validation to ensure that only expected and valid data is
processed.

28
9. Data Confidentiality:
• Explanation: Data confidentiality ensures that sensitive information remains private and
secure. Encryption is a key mechanism to protect data confidentiality, especially during
transmission and storage.
• Implementation: Use encryption algorithms to encrypt sensitive data both at rest and in
transit. Implement secure communication protocols (e.g., SSL/TLS) to protect data
during transmission.
10. Preventing Unauthorized Modifications:
• Explanation: Unauthorized modifications can lead to data corruption or loss. Access
controls and role-based permissions prevent users from making unauthorized changes
to the database structure or altering critical data.
• Implementation: Define roles with specific permissions, and assign users to these roles
accordingly. Regularly review and update access controls to ensure they align with the
principle of least privilege.

29
M3
SQL Statements
Explanation of SQL Statements:
1. GROUP BY
• Usage: The GROUP BY statement is used to group rows that have the same values in
specified columns into summary rows. It is often used with aggregate functions like
COUNT, SUM, AVG.
• Example: Query to find the total quantity of books for each genre:
2. ORDER BY
• Usage: The ORDER BY statement is used to sort the result set of a query by one or
more columns in ascending (ASC) or descending (DESC) order.
• Example: Query to retrieve books sorted by their published year in descending order:

30
3. HAVING
• Usage: The HAVING statement is used in conjunction with the GROUP BY clause to
filter the results of aggregate functions based on specified conditions.
• Example: Query to find genres with a total quantity greater than 100:
4. BETWEEN
• Usage: The BETWEEN statement is used to filter the result set to include only values
within a specified range.
• Example: Query to find books published between the years 2010 and 2020:

31
5. WHERE
• Usage: The WHERE statement is used to filter rows based on specified conditions,
including comparisons and logical operators.
• Example: Query to retrieve members who registered after a certain date:

32
D2
Evaluate the Effectiveness of the Database & Suggest
Improvements
Effectiveness Evaluation
User Requirements:
1. Add New Books:
• Effectiveness: The database solution effectively supports adding new books with the
Books table, capturing essential information such as ISBN, Title, PublishedYear, Genre,
and Quantity.
• Improvement: Consider adding a mechanism to handle multiple authors for a single
book, as some books may have more than one contributor.
2. Update Book Information:
• Effectiveness: The UPDATE statement allows for the modification of existing records in
the Books table, ensuring that book information can be updated.
• Improvement: Implement a mechanism to track and log changes to book information for
audit purposes.
3. Member Registration:
• Effectiveness: The Members table facilitates member registration with essential
information like MemberID, FirstName, LastName, Email, Phone, and Address.
• Improvement: Enhance the registration process by incorporating additional member
details, such as membership type or category.
4. Loan Books:
• Effectiveness: The Loans table handles loan transactions, tracking LoanID, LoanDate,
DueDate, ReturnDate, BookISBN, and MemberID.
• Improvement: Introduce a status column to indicate whether a book is currently on loan
or available, allowing for more efficient tracking of book availability.
5. Generate Reports:
• Effectiveness: While not explicitly represented in the design, the logical structure allows
for potential extensions to include reporting functionality.
• Improvement: Develop a reporting module to generate common reports, such as loan
history, popular genres, or overdue books.

33
System Requirements:
1. Data Integrity and Consistency:
• Effectiveness: The use of primary keys, foreign keys, and normalization ensures data
integrity and consistency.
• Improvement: Regularly perform data integrity checks and consider implementing
constraints for additional business rules.
2. Role-Based Access:
• Effectiveness: Role-based access is not explicitly shown in the design but can be
implemented at the application level.
• Improvement: Integrate role-based access control directly into the database system for
better security.
3. Scalability:
• Effectiveness: The normalized structure supports scalability as the database grows.
• Improvement: Consider database sharing or partitioning strategies for handling larger
datasets and increased user loads.
4. Regular Backups:
• Effectiveness: Regular backups are not explicitly represented in the design but can be
implemented at the database management level.
• Improvement: Implement an automated backup system with regular schedules to
ensure data recovery in case of failures.
Overall Suggestions for Improvement:
1. User Interface Enhancement:
• Consider developing a user-friendly interface or application for easier interaction with
the database, making it more accessible for non-technical users.
2. Security Enhancements:
• Strengthen security mechanisms by incorporating advanced encryption methods,
securing communication channels, and implementing two-factor authentication.
3. Audit Trail Implementation:
• Develop an audit trail system to track changes to critical data, providing transparency
and accountability.
4. Advanced Reporting:
• Extend reporting capabilities to provide more insights into library operations and
member activities.

34
5. Feedback Mechanism:
• Implement a feedback mechanism to gather user input and continuously improve the
system based on user experiences and requirements.
6. Performance Optimization:
• Regularly monitor and optimize database performance to ensure quick response times,
especially as the database grows.
7. Documentation:
• Maintain comprehensive documentation for the database structure, relationships, and
procedures to facilitate future maintenance and enhancements.

35
P4
Database Testing
Database Testing Test Plan
Functionality Testing:
Add New Books:
o Verify that new books can be successfully added to the Books table.
o Confirm that mandatory fields (BookID, Title) are validated.
Update Book Information:
o Test the ability to update book information in the Books table.
o Validate those changes are reflected in the database.
Member Registration:
o Test member registration functionality.
o Validate that member information is correctly stored in the Members table.
Loan Books:
o Verify that books can be successfully loaned to members.
o Confirm that loan transactions are recorded in the Loans table.
Performance Testing:
Scalability:
- Test the system's scalability by adding a large dataset and measuring response times.
- Assess the impact of increased user loads on performance.
Transaction Speed:
- Evaluate the speed of critical transactions (e.g., adding books, loaning books).
- Ensure that transactions are processed efficiently.
Security Testing:
Access Controls:
- Test role-based access controls.
- Verify that users have appropriate permissions based on their roles.
Data Encryption:
- Validate that sensitive data is encrypted both at rest and during transmission.
- Confirm that decryption mechanisms are functioning correctly.
Usability Testing:
User Interface:
- Evaluate the user interface for ease of use and navigation.
- Collect user feedback on the intuitiveness of the system.

36
Error Handling:
- Test the system's response to invalid inputs.
- Verify that meaningful error messages are displayed.
SQL Query Testing:
Update Statement:
- Test the effectiveness of the UPDATE statement by modifying book information.
Order By Clause:
- Verify the correct ordering of query results using the ORDER BY clause.
Joining Tables:
- Test queries that involve joining multiple tables to retrieve information.
Conditions using WHERE Clause:
- Validate the use of the WHERE clause for conditional filtering in queries.
Grouping and Aggregation:
- Test queries that involve grouping and aggregation using the GROUP BY clause and
aggregate functions.
Validation Processes:
Data Validation:
- Validate that data input into the database adheres to defined constraints.
- Test the system's response to data that violates constraints.
Query Output Validation:
- Compare query results against expected outcomes to ensure accuracy.
- Verify that data retrieved from the database is consistent with user expectations.
Test Cases for Database System
1. Add New Books:
- Test Case:
o Add a new book to the Books table.
- Expected Result:
o Book is successfully added with accurate information.
- Test Case:
o Add a new book with missing mandatory information (e.g., ISBN).
- Expected Result:
o System should display an error, preventing the addition of the book.

37
- Test Case:
o Add a book with a negative quantity.
- Expected Result:
o System should display an error, preventing the addition of the book.
2. Update Book Information:
- Test Case:
o Update the quantity of an existing book.
- Expected Result:
o Quantity is successfully updated, and the database reflects the change.
- Test Case:
o Attempt to update a non-existing book.
- Expected Result:
o System should display an error, as the book does not exist.
3. Member Registration:
- Test Case:
o Register a new member with complete information.
- Expected Result:
o Member is successfully registered with accurate information.
- Test Case:
o Register a member with a duplicate email.
- Expected Result:
o System should display an error, preventing the registration.
4. Loan Books:
- Test Case:
o Loan a book to a registered member.
- Expected Result:
o Loan transaction is recorded, and the book's availability is updated.
- Test Case:
o Attempt to loan a non-existing book.
- Expected Result:
o System should display an error, as the book does not exist.

38
M4
Assessment of Test Effectiveness
Explanation of Test Data Selection
1. Functionality Testing:
- For adding new books, test data includes valid book information, missing mandatory
information (BookID), and attempting to add a book with negative quantity.
- For updating book information, test data includes modifying the quantity of an
existing book and attempting to update a non-existing book.
- Member registration testing includes valid member information and attempting to
register a member with a duplicate email.
- Loaning books involves testing the successful loan of a book to a registered member
and attempting to loan a non-existing book.
2. Performance Testing:
- Scalability testing involves adding a large dataset to assess the system's response
times and performance under increased data loads.
- Transaction speed testing evaluates the speed of critical transactions, such as
adding books and loaning books, to ensure efficiency.
3. Security Testing:
- Access controls are tested by checking role-based access controls, ensuring users
have appropriate permissions based on their roles.
- Data encryption testing involves validating the encryption of sensitive data at rest
and during transmission, along with confirming decryption mechanisms.
4. Usability Testing:
- User interface testing evaluates the ease of use and navigation of the system.
- Error handling testing assesses the system's response to invalid inputs, verifying the
display of meaningful error messages.
5. SQL Query Testing:
- Testing the UPDATE statement involves modifying book information.
- ORDER BY clause testing verifies the correct ordering of query results.
- Joining tables testing involves querying multiple tables to retrieve information.
- Conditions using WHERE clause testing validates conditional filtering in queries.
- Grouping and aggregation testing checks queries involving GROUP BY clause and
aggregate functions.
6. Validation Processes:
- Data validation testing ensures that data input adheres to defined constraints.
- Query output validation compares actual query results against expected outcomes to
ensure accuracy.

39
To improve the effectiveness of testing, consider implementing the following strategies and practices:
1. Automated Testing:
- Advantages:
o Faster and more efficient execution of repetitive tests.
o Enables continuous integration and continuous testing.
o Reduces the likelihood of human error in test execution.
- Implementation:
o Develop automated test scripts for repetitive and critical test cases.
o Integrate automated tests into the continuous integration pipeline.
2. Diversify Test Data:
- Advantages:
o Expands test coverage by including a variety of scenarios.
o Identifies potential edge cases and boundary conditions.
- Implementation:
o Create test data sets that cover a broad range of scenarios, including normal,
boundary, and extreme cases.
o Regularly update and expand the test data sets based on evolving requirements.
3. User Feedback Integration:
- Advantages:
o Incorporates real-world user experiences and expectations into the testing process.
o Identifies usability issues and user interface improvements.
- Implementation:
o Collect user feedback through surveys, usability testing sessions, or feedback forms.
o Integrate user feedback into test planning and prioritize improvements based on
user insights.
4. Continuous Testing:
- Advantages:
o Ensures ongoing testing as the system evolves.
o Identifies issues early in the development lifecycle.
- Implementation:
o Integrate testing into the continuous integration and continuous deployment (CI/CD)
pipeline.
o Automate regression tests to run after each code commit.

40
5. Test Case Reviews and Collaboration:
- Advantages:
o Enhances test case quality through peer reviews.
o Encourages collaboration between developers, testers, and other stakeholders.
- Implementation:
o Conduct regular reviews of test cases with the project team.
o Encourage collaboration and communication to refine test cases based on collective
insights.
6. Exploratory Testing:
- Advantages:
o Uncovers unexpected issues and scenarios.
o Mimics real-world user interactions and behavior.
- Implementation:
o Allocate time for exploratory testing sessions, allowing testers to freely explore the
application.
o Document findings and incorporate them into formal test cases or improvements.
7. Performance Testing with Realistic Scenarios:
- Advantages:
o Assesses system performance under conditions that mimic real-world usage.
o Identifies potential bottlenecks and performance issues.
- Implementation:
o Use realistic data sets and scenarios for performance testing.
o Simulate user loads and behaviors that reflect actual usage patterns.
8. Regression Testing Suites:
- Advantages:
o Ensures that new updates do not introduce regressions or break existing
functionality.
o Validates the overall stability of the system.
- Implementation:
o Develop and maintain comprehensive regression testing suites.
o Execute regression tests after each code change to catch potential regressions
early.

41
9. Comprehensive Documentation:
- Advantages:
o Provides clarity on test cases, expected results, and testing processes.
o Facilitates knowledge transfer among team members.
- Implementation:
o Document test cases, expected results, and testing procedures comprehensively.
o Regularly update documentation to reflect changes in requirements or system
behavior.
10. Load Testing:
- Advantages:
o Assesses how the system handles heavy concurrent loads.
o Identifies potential performance bottlenecks under high loads.
- Implementation:
o Conduct load testing with simulated high user loads and concurrent transactions.
o Optimize system components based on load testing results.

42
D2
Evaluate the Effectiveness of the Database & Suggest
Improvements
Feedback interface
Users can provide feedback by adjusting the rating, entering text in the usability, challenges, and
suggestions fields

43
P5
Users’ Guide and a Technical Documentation
User's Guide
Introduction
Welcome to the Royal College Dubai Library Management System! This user's guide provides you
with instructions on how to use the software efficiently. Whether you're a librarian managing the
collection or a member borrowing books, this guide will help you navigate through the system.
Table of Contents
1. Logging In
o Accessing the system with your credentials.
2. Dashboard Overview
o Understanding the main features available on your dashboard.
3. Managing Books
o Adding new books to the system.
o Updating book information.
o Viewing the list of available books.
4. Member Functions
o Registering new members.
o Managing member information.
o Handling book loans and returns.
5. Reports
o Generating reports for insights into the library's operations.
6. Settings
o Customizing personal preferences and account settings.
1. Logging In
- To access the system:
▪ Open the application and enter your username and password.
▪ Click the "Login" button.

44
2. Dashboard Overview
- Upon logging in, you'll find a dashboard with key features:
▪ Book Management
▪ Member Management
▪ Reports
- Use the navigation menu to explore each section.
3. Managing Books
- Adding New Books:
▪ Navigate to "Book Management" > "Add New Book."
▪ Fill in the required information (Title, Author).
▪ Click "Save" to add the book.
- Updating Book Information:
▪ Go to "Book Management" > "View Books."
▪ Select the book to update and click "Edit."
▪ Make the necessary changes and click "Save."
- Viewing Book List:
▪ Visit "Book Management" > "View Books" to see the list of available books.
4. Member Functions
- Registering New Members:
▪ Under "Member Management," select "Add New Member."
▪ Enter member details and click "Save."
- Managing Member Information:
▪ View and edit member details under "Member Management."
- Handling Book Loans and Returns:
▪ In "Member Management," select a member.
▪ Choose "Loan Book" to lend a book or "Return Book" to return a book.
5. Reports
- Generating Reports:
▪ Access "Reports" to generate insights into book availability, member activity.
6. Settings
- Customizing Preferences:
▪ Under "Settings," customize preferences like theme and notification settings.

45
Technical Documentation
System Architecture
The Royal College Dubai Library Management System is built on a three-tier architecture:
1. Presentation Tier:
- The user interface accessible via the desktop application or web interface.
2. Application Tier:
- The logic layer handling business rules, including book and member management.
3. Database Tier:
- The backend database storing information about books, members, loans, and more.
Technologies Used
1. Programming Language: Python
2. GUI Framework: Tkinter (for desktop application)
3. Database: SQLite
4. Backend Framework: Flask (for web version)
Database Schema
The database consists of the following tables:
1. Books
2. Authors
3. Members
4. Loans
5. Publishers
Maintenance Procedures
• Regular Backups:
• Periodically back up the database to prevent data loss.
• Updates and Bug Fixes:
• Keep the software up to date with the latest releases.
• Address bugs promptly to ensure smooth operation.
• Security Measures:
• Regularly review and update access controls.
• Ensure data encryption mechanisms are up to industry standards.
• Performance Monitoring:
• Implement performance monitoring tools to identify and address bottlenecks.

46
• User Training:
• Provide periodic training sessions for users to ensure they are aware of new features
and best practices.
• Documentation Maintenance:
• Keep technical documentation updated with any changes or enhancements made to the
system.
Troubleshooting
• Common Issues:
• If facing login issues, ensure the correct username and password are used.
• For unexpected errors, consult the error messages displayed and refer to the technical
documentation.
• Support Channels:
• Users can contact the support team through [support email/phone].

47
M5
User Documentation
Welcome to the Royal College Dubai Library Management System! I will show how the system work.
I use Flowchart, UML Diagram (Use case, Class diagram and Data Flow Diagram (DFD)).
Flowchart for System:
Flowchart
The provided flowchart outlines the main processes within a generic library management system.
- Start:
• The process begins at the "Start" point.

48
- Main Menu:
• Users are presented with a "Main Menu" offering various options.
 Options include:
• Manage Books
• Register Member
• Loan Book
• Return Book
• Exit
- Book Management:
• If the user selects "Manage Books," the system directs them to the "Book Management"
process.
• This process involves activities related to book administration, such as adding,
updating, or removing books from the library collection.
- Member Registration:
• If the user selects "Register Member," the system leads them to the "Member
Registration" process.
• This process involves capturing information to register new members, including their
names, contact details, and address.
- Loaning Books:
• If the user selects "Loan Book," the system guides them through the "Loaning Books"
process.
• This process includes handling transactions related to borrowing books, managing loan
dates, and updating book availability.
- Returning Books:
• If the user selects "Return Book," the system navigates them to the "Returning Books"
process.
• This process manages the return of borrowed books, updating the system with return
dates and making books available again.
- Exit:
• If the user selects "Exit," the system concludes at the "End" point.
- End:
• The process concludes at the "End" point.

49
Context Level DFD for Loaning a Book
Context Level DFD Description:
- Library System:
• Represents the entire library management system.
• Acts as the central processing unit that interacts with external entities.
- User:
• External entity representing individuals using the library system.
• Initiates actions such as borrowing books, returning books, and searching for books.
- Librarian:
• External entity representing the library staff responsible for managing the system.
• Performs tasks such as adding new books, updating book information, and handling
member registrations.
- Interactions:
• User to Library System:
▪ Requests to borrow a book.
▪ Requests to return a book.
▪ Searches for books in the library catalog.
• Librarian to Library System:
▪ Adds new books to the library catalog.
▪ Updates book information.
▪ Registers new library members.

50
- Data Flow:
• From User:
▪ User requests for book-related actions and searches.
• From Librarian:
▪ Librarian provides new book information, updates, and member registrations.
• To User:
▪ Results of book-related actions and searches.
• To Librarian:
▪ Confirmation of successful book additions, updates, and member registrations.
- Processes:
• Main System:
▪ Coordinates and processes requests from both users and librarians.
▪ Manages the database containing information about books, authors, members,
loans, and publishers.
▪ Sends responses to user queries and librarian operations.
Context Level 1 DFD for Loaning a Book

51
Context Level 1 DFD Description:
- Process Descriptions:
• Loan Book Process:
▪ This process is responsible for handling the loan request made by a user through
the librarian.
▪ It interacts with the database to update the loan status and maintain loan
records.
• Database:
▪ The database stores information related to books, members, loans, and other
relevant data.
▪ It is accessed by the Loan Book Process to update the loan status and retrieve
necessary information.
• Notification System:
▪ This process handles the communication of loan-related notifications to the user.
▪ Notifications may include due date reminders, overdue notices, or successful
loan confirmations.
- Data Flows:
• Loan Book Request:
▪ Initiated by the user through the librarian.
▪ Carries information about the book being loaned, the member making the
request, and loan details.
• Updated Loan Status:
▪ Sent from the Loan Book Process to the database.
▪ Contains updated information about the book's loan status, such as loan date,
due date, and return date.
- External Entities:
• User:
▪ Initiates the loan request by providing necessary information to the librarian.
• Librarian:
▪ Acts as an intermediary between the user and the system.
▪ Facilitates the loan request and interacts with the Loan Book Process.

52
UML Diagram
Use case
Use Case Diagram Description:
- Actors:
• User:
▪ Represents individuals who utilize the library system, such as patrons or
members.
• Librarian:
▪ Represents the library staff responsible for managing and maintaining the library
system.
- Use Cases:
• Add Book:
▪ Actor: User, Librarian
▪ Description: Allows users and librarians to add new books to the library system.
• Update Book:
▪ Actor: User, Librarian
▪ Description: Enables users and librarians to update information about existing
books, such as quantity or genre.
• Loan Book:
▪ Actor: User, Librarian
▪ Description: Facilitates the process of lending books to users. Handles loan
dates, due dates, and return dates.

53
• Register Member:
▪ Actor: User, Librarian
▪ Description: Permits the registration of new members into the library system,
capturing their personal information.
- Relationships:
▪ The "User" and "Librarian" actors are associated with each use case, indicating
their involvement in the corresponding functionalities.
Class Diagram
Class Diagram Description:
- Defines the essential entities and their relationships in the library management system (Book,
Author, Member, Loan, Publisher).
- Describes the attributes and associations between classes.

54
D3
Evaluate the Database and Identify Improvements for
Continued Effectiveness
Several Areas to Assess and Potential Improvements
1. Performance Optimization:
- Assessment:
▪ Monitor and analyze the database's performance, identifying any bottlenecks or
slow queries.
- Improvements:
▪ Indexing: Ensure that relevant columns are indexed to speed up query
performance.
▪ Query Optimization: Review and optimize complex queries for better efficiency.
▪ Database Cache: Utilize caching mechanisms to reduce the load on the
database.
2. Security Enhancements:
- Assessment:
▪ Evaluate the current security measures to protect sensitive data.
- Improvements:
▪ Encryption: Implement encryption for sensitive data both in transit and at rest.
▪ Access Controls: Review and tighten access controls to ensure that users have
appropriate permissions.
▪ Regular Security Audits: Conduct periodic security audits to identify and address
vulnerabilities.
3. User Experience (UX) and Interface Improvements:
- Assessment:
▪ Gather feedback from users regarding the usability and intuitiveness of the
system.
- Improvements:
▪ User Training: Provide additional training sessions to users to enhance their
understanding of the system.
▪ Interface Redesign: Consider redesigning the user interface for improved
navigation and user experience.
▪ Feedback Mechanism: Implement a feedback mechanism for users to report
issues and provide suggestions.

55
4. Data Validation and Integrity:
- Assessment:
▪ Review the current data validation mechanisms to ensure data accuracy and
integrity.
- Improvements:
▪ Implement Additional Validation Rules: Enforce more stringent validation rules to
prevent invalid data entry.
▪ Regular Data Audits: Conduct regular data audits to identify and correct
inconsistencies.
▪ Error Handling: Improve error handling to provide meaningful error messages to
users.
5. Scalability Considerations:
- Assessment:
▪ Evaluate the system's scalability to handle increased data and user loads.
- Improvements:
▪ Scalability Testing: Perform scalability testing to identify potential bottlenecks and
plan for future growth.
▪ Database Sharding: Consider implementing database sharding to distribute data
across multiple servers for improved scalability.
6. Backup and Recovery Mechanisms:
- Assessment:
▪ Review the current backup and recovery procedures.
- Improvements:
▪ Regular Backups: Ensure regular and automated backups of the database.
▪ Test Recovery Procedures: Periodically test the recovery procedures to ensure
data can be restored successfully in case of an issue.
7. Documentation:
- Assessment:
▪ Assess the completeness and accuracy of documentation.
- Improvements:
▪ Update Documentation: Keep documentation up-to-date with any changes or
enhancements to the system.
▪ Include Troubleshooting Guides: Provide troubleshooting guides for common
issues.

56
8. Regulatory Compliance:
- Assessment:
▪ Ensure compliance with relevant regulations and standards.
- Improvements:
▪ Regular Compliance Audits: Conduct regular audits to ensure ongoing
compliance.
▪ Stay Informed: Stay informed about changes in regulations and update the
system accordingly.
9. Collaboration with Stakeholders:
- Assessment:
▪ Evaluate the level of collaboration with stakeholders, including users and
developers.
- Improvements:
▪ Regular Meetings: Schedule regular meetings with stakeholders to gather
feedback and discuss potential improvements.
▪ User Involvement: Encourage user involvement in testing and provide
opportunities for them to suggest enhancements.
10. Continuous Monitoring and Analysis:
- Assessment:
▪ Ensure that there are mechanisms in place for continuous monitoring of the
system.
- Improvements:
▪ Implement Monitoring Tools: Utilize monitoring tools to track system
performance, security, and user activities.
▪ Proactive Issue Resolution: Address potential issues proactively based on
monitoring results.

57
References
Anon., 2018. Database management systems. [Online]
Available at: https://www.ibm.com/docs/en/zos-basic-skills?topic=zos-what-is-database-management-
system
[Accessed 10 December 2023].
Anon., 2019. What is a data flow diagram. [Online]
Available at: https://www.lucidchart.com/pages/data-flow-
diagram#:~:text=A%20data%20flow%20diagram%20(DFD,the%20routes%20between%20each%20d
estination.
[Accessed 12 December 2023].
Anon., 2023. Database Modeling in UML. [Online]
Available at: https://sparxsystems.com/resources/tutorials/uml/datamodel.html
[Accessed 12 December 2023].
Anon., 2023. What is a relational database?. [Online]
Available at: https://cloud.google.com/learn/what-is-a-relational-
database#:~:text=A%20relational%20database%20(RDB)%20is,relationship%20between%20various
%20data%20points.
[Accessed 10 December 2023].
Ismael, D., 2014. Database Managment System. Cairo: MTI Unvirsty.
Peterson, R., 2023. What is Normalization. [Online]
Available at: https://www.guru99.com/database-normalization.html
[Accessed 12 December 2023].