Royal College Dubai Library Database Design and Development

This project details the design and development of a relational database management system, focusing on a library system for Royal College Dubai. It covers key aspects such as user and system requirements, entity-relationship modeling, logical database design, and data normalization to 3NF. The project includes data validation techniques, interface and output designs, and an evaluation of the database's effectiveness in meeting the specified requirements. Furthermore, it demonstrates practical implementation with SQL for table creation, data manipulation, and security measures. The project also addresses database testing strategies, including functionality, performance, security, and usability testing, along with an assessment of test effectiveness and suggestions for improvements. Finally, it provides user guides, technical documentation, flowcharts, DFDs, and UML diagrams to ensure maintainability and usability.

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.