Download Database Design And Development Asignment 2 (P+M) (Btec level 5) and more Essays (university) Information Technology in PDF only on Docsity!
ASSIGNMENT 2 FRONT SHEET
Qualification BTEC Level 5 HND Diploma in Computing Unit number and title Unit 04: Database Design & Development Submission date Date Received 1st submission Re-submission Date Date Received 2nd submission Student Name Mai Van Thai Student ID BHAF Class BH-AF2005-2.2 Assessor name Ngo Thi Mai Loan Student declaration I certify that the assignment submission is entirely my own work and I fully understand the consequences of plagiarism. I understand that making a false declaration is a form of malpractice. Student’s signature Grading grid
P2 P3 P4 P5 M2 M3 M4 M5 D2 D
Summative Feedback: Resubmission Feedback:
Grade: Assessor Signature: Date: Signature & Date:
- I. Introduction
- II. System requirements
- Real world scenario:
- Data requirements for storage
- Relationship between entities:
- III. Design relational database system for a substantial problem
- ER Diagram
- Convert ER Diagram to Relation Diagram
- Normalization
- Relational database system
- Implement a query language.
- IV. Database development
- Create User Interface
- Coding
- Evaluate data meaning through query tools
- V. Testcase and test log
- Testing
- Assess the effectiveness of the testing:
- VI. Technical user documentation......................................................................................................
- Form Login:
- Form SinUp:
- Menu:
- Exit the program
- Functions
- Click data in the table
- VII. System security and maintenance
- Security
- Maintenance
- Password encryption
- VIII. What improvements should the software be made.
- IX. Conclusion
- X. Reference list
- Figure 1. ER diagram. List of figures:
- Figure 2. Relational database system.
- Figure 3. Insert into data.
- Figure 4. Select table.
- Figure 5. Update data.
- Figure 6. Delete data.
- Figure 7. Search data.
- Figure 8. Query data on multiple tables.
- Figure 9. Login interface.
- Figure 10. Sign up interface.
- Figure 11. Room entity interface.
- Figure 12. Student entity interface.
- Figure 13. Class entity interface.
- Figure 14. RentalContract entity interface.
- Figure 15. DormitoryManager entity interface.
- Figure 16. Contract_Manager entity interface.
- Figure 17. Room_Manager entity interface.
- Figure 18. Connecting Form to SQL.
- Figure 19. Display SQL on the Form Student.
- Figure 20. Coding menu Form Student.
- Figure 21. Coding Exit Form Student.
- Figure 22. Set gender Form Student.
- Figure 23. Coding delete function Form Student.
- Figure 24. Coding search function Form Student.
- Figure 25. Coding add function Form Student.
- Figure 26. Coding update function Form Student.
- Figure 27. Coding click data in DataGridView Form Student...............................................................
- Figure 28. Form Login.
- Figure 29. Sign Up.
- Figure 30. menu.
- Figure 31.Exit the program.
- Figure 32. Functions.
- Figure 33. Click data in the table.
- Figure 34. Create sql server login account (1).
- Figure 35. Create sql server login account (2).
- Figure 36. Create user (1).
- Figure 37. Create user (2).
- Figure 38. Authorization for users (1).
- Figure 39. Authorization for users (2).
- Figure 40. Back up (1).
- Figure 41. Back up (2).
- Figure 42. Encrypt the password in the database...............................................................................
- Figure 43. Coding Login Button.
- Figure 44. Coding Sign Up Button.
- Table 1. Data requirements for storage. List of tables:
- Table 2. Convert ER Diagram to Relation Diagram.
- Table 3. Test Form Login.
- Table 4. Test Form SignUp.
- Table 5. Test Form Room.
- Table 6. Test Form Student.
- Table 7. Test Form Class.
- Table 8. Test Form RentalContract.....................................................................................................
- Table 9. Test Form DormitoryManager.
- Table 10. Test Form Contract_Manager.
- Table 11. Test Form Room_Manager.
II. System requirements
1. Real world scenario: I am employed as a Database Developer for a large IT consultancy company. The company has been approached by FPT university which is expanding due to the growth of the number of students. FPT is currently facing difficulties in dealing with managing the dormitory. The construction of dormitory management software is implemented with the following subjects: Room, student, class, dormitory management, room rental contract with the following requirements: + The dormitory of FPT University includes many rooms. + Room's information includes: Room ID, room name, area, number of beds, rental price. + Each room can accommodate many students. + Student information including: Student ID, name, age, gender, address, phone number. + Each student can come from different classes. + A dormitory has many managers. + Each manager can manage multiple rooms and multiple room rentals. + Room rental details include: Contract code, check-in date, check-out date. 2. Data requirements for storage Table 1. Data requirements for storage. Entity Description of Entity Attribute Description of Attribute Room All of rooms in the dorm RoomID It is a unique identifier for each room in the dorm RoomName Name of the room RoomArea Area of the room NumberOfBeds Number of beds in the room RoomRates Room rent Class All of classes in the school ClassID It is a unique identifier for each class ClassName Name of the class
Student All of students in the school StudentID It is a unique identifier for each student in the class StudentName Name of student Age Age of student Gender Age of student Address Address of student PhoneNumber PhoneNumber of student DormitoryManager All of managers in dormitory ManagerID It is a unique identifier for each dormitory manager ManagerName Name of manager RetalContract All of rental contracts in dormitory ContractID It is a unique identifier for each contract CkeckInDay Check in date CkeckOutDay Check out date
3. Relationship between entities: + Relationship between entity Student and entity Class: a student belongs to only one class, a class may have many students.
- Relationship between entity Student and entity Room: a student can rent only one room, a room may have one or more students.
- Relationship between entity Student and entity RentalContract: a student can sign multiple contract rental, a rentals contract only sign with a student.
- Relationship between entity DormitoryManager and entity Room: a room has multiple managers, a manager can manage multiple rooms
- Relationship between entity DormitoryManager and entity RentalContract: a manager can manage multiple room rental contracts, a room rental contract can have multiple managers.
2. Convert ER Diagram to Relation Diagram Table 2. Convert ER Diagram to Relation Diagram. Relation Primary Key Foreign Key Other Attribute Room RoomID RoomName RoomArea NumberOfBeds RoomRates Class ClassID ClassName Student StudentID ClassID StudentName RoomID Age Gender PhoneNumber Address RentalContract ContractID StudentID CheckInDay CheckOutDay DormitoryManager ManagerID ManagerName Room_Manager RoomID RoomID ManagerID ManagerID Contract_Manager ContractID ContractID ManagerID ManagerID
3. Normalization + Definition and purpose of database normalization: Normalization is the process of splitting tables (decomposing) into smaller tables based on functional dependencies. Standard formats are guidelines for designing tables in a database. The purpose of standardization is to eliminate data redundancy and redundancy errors and data manipulation errors (Insert, Delete, Update). + Normalization statement for each of relation: Room (RoomID, RoomName, RoomArea, NumberOfBeds, RoomRates) RoomID RoomName, RoomArea, NumberOfBeds, RoomRates Already in 3NF format because: Does not contain repetition, does not depend on partial functions, does not contain transitive dependencies. Class (ClassID, ClassName) ClassID ClassName Already in 3NF format because: Does not contain repetition, does not depend on partial functions, does not contain transitive dependencies. Student (StudentID, StudentName, Age, Gender, PhoneNumber, Address, ClassID, RoomID) StudentID StudentName, Age, Gender, PhoneNumber, Address, ClassID, RoomID Already in 3NF format because: Does not contain repetition, does not depend on partial functions, does not contain transitive dependencies. RentalContract (ContractID, CheckInDay, CheckOutDay, StudentID) ContractID CheckInDay, CheckOutDay, StudentID Already in 3NF format because: Does not contain repetition, does not depend on partial functions, does not contain transitive dependencies. DormitoryManager (ManagerID, ManagerName) ManagerID ManagerName Already in 3NF format because: Does not contain repetition, does not depend on partial functions, does not contain transitive dependencies. Room_Manager (RoomID,ManagerID) Already in 3NF format because: Does not contain repetition, does not depend on partial functions, does not contain transitive dependencies. Contract_Manager (ContractID, ManagerID) Already in 3NF format because: Does not contain repetition, does not depend on partial functions, does not contain transitive dependencies.
5. Implement a query language. + Insert into data: Figure 3. Insert into data. + Select table: Figure 4. Select table.
- Update data: Figure 5. Update data.
- Delete data: Figure 6. Delete data.
IV. Database development
1. Create User Interface
- Login interface: Figure 9. Login interface.
- Sign up interface: Figure 10. Sign up interface.
- Room entity interface: Figure 11. Room entity interface.
- Student entity interface: Figure 12. Student entity interface.
- DormitoryManager entity interface: Figure 15. DormitoryManager entity interface.
- Contract_Manager entity interface: Figure 16. Contract_Manager entity interface.
- Room_Manager entity interface: Figure 17. Room_Manager entity interface.
2. Coding
- Connecting Form to SQL: Figure 18. Connecting Form to SQL.
