logo

Database Design and Development

58 Pages10295 Words296 Views
   

Added on  2024-01-17

About This Document

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

   Added on 2024-01-17

ShareRelated Documents
Database Design and
Development
Assignment 1 Relational Database Management System
Rashed Salem 12/9/23 ID: 2210065
Database Design and Development_1
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
Database Design and Development_2
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
Database Design and Development_3
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
Database Design and Development_4
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
Database Design and Development_5
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
Database Design and Development_6
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
Database Design and Development_7
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
Database Design and Development_8

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Database Design & Development for Shoengalleric Art Gallery
|49
|6097
|126

Database Design and Development for Shoengalleric Art Gallery
|66
|5608
|329

The assignment submission is entirely my own work and I understand the consequences of plagiarism
|75
|6901
|487

Database Design & Development Assignment 2
|75
|6962
|200

Database Design and Development PDF
|124
|18220
|692

Relational Database Management System (RDBMS
|43
|8083
|154