Download Database Design And Development Asignment 1 (P+M) (Btec level 5) and more Essays (university) Information Technology in PDF only on Docsity!
ASSIGNMENT 1 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
P1 M1 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
- IV. System interface design........................................................................................................................................
- V. Conclusion
- VI. Reference list
- Figure 1. ER diagram. List of figures:
- Figure 2. Create the database.
- Figure 3. Database display location........................................................................................................................
- Figure 4. Create the tables.
- Figure 5. Create Relation Diagram in SQL Server Management (1).
- Figure 6. Create Relation Diagram in SQL Server Management (2).
- Figure 7. Relational database system.
- Figure 8. Student table.
- Figure 9. Room table.
- Figure 10. RentalContract table.
- Figure 11. Class table.
- Figure 12. DormitoryManager table.
- Figure 13. Room_Manager table.
- Figure 14. Contract_Manager table.........................................................................................................................
- Figure 15. Login interface.
- Figure 16. Sign up interface.
- Figure 17. Room entity interface.
- Figure 18. Student entity interface.
- Figure 19. Class entity interface.
- Figure 20. RentalContract entity interface.
- Figure 21. DormitoryManager entity interface.
- Table 1. Data requirements for storage. List of tables:
- Table 2. Convert ER Diagram to Relation Diagram.
I. Introduction This report will discuss, design database about dormitory management scenario of FPT University. Describe the entities, attributes, and relationships between entities. Supply system design involves an exact problem: designing ER diagrams, converting ER diagrams into relational diagrams, normalization, creating relational database systems. Finally, the report will show the system interface design for the above scenario. To do this, I chose Microsoft SQL Server Management Studio 18 to design.
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.
III. Design relational database system for a substantial problem
1. ER Diagram Figure 1. ER diagram.
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.
4. Relational database system + Create the database: - Open SQL Server Management, select "New Query" and write the command to create the database, then press F5. Figure 2. Create the database.
- The newly created database is displayed in "Object Explorer" Figure 3. Database display location.
- Create Relation Diagram in SQL Server Management:
- In the database "Dormitory Management", right-click on "Database Diagrams", select "New Database Diagram". Figure 5. Create Relation Diagram in SQL Server Management (1).
- Then, select the tables created and click "Add" to create Relation Diagram in SQL Server Management. Figure 6. Create Relation Diagram in SQL Server Management (2).
- The result will be displayed as follows: Figure 7. Relational database system.
Figure 11. Class table. Figure 12. DormitoryManager table.
Figure 13. Room_Manager table. Figure 14. Contract_Manager table.
- Room entity interface: Figure 17. Room entity interface.
- Student entity interface: Figure 18. Student entity interface.
- Class entity interface: Figure 19. Class entity interface.
- RentalContract entity interface: Figure 20. RentalContract entity interface.
