Download Database Design & Development_Assignment1 Merit and more Exams Information Technology in PDF only on Docsity!
ASSIGNMENT 1 FRONT SHEET
Qualification TEC Level 5 HND Diploma in Computing Unit number and title Unit 04: Database Design & Development Submission date 7 /10/2023 Date Received 1st submission Re-submission Date Date Received 2nd submission Student Name Truong Van Diep Student ID BH Class SE06203 Assessor name Ha Ngoc Linh 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 Diep Grading grid P1 M1 D
❒ Summative Feedback: ❒ Resubmission Feedback: Grade: Assessor Signature: Date: Signature & Date:
- I. Introduction
- II. Body
- STATEMENTS OF USER AND SYSTEM REQUIREMENT
- Statements of user
- System Requirement Specification (SRS)
- a. Requirements and Expectation
- b. Objectives (Things intended to achieve)........................................................................................................................................................
- De sign the relational database system (P1)...............................................................................................................................................................
- De sign the relational database
- a, Identify entities
- b, Identify relationship
- c, Identify and associate attributes with entities
- d, ER Diagram
- 2, Explanation about ER diagram
- Data validation and normalization...........................................................................................................................................................................
- 1, Normalization...................................................................................................................................................................................................
- a, What is Database Normalization?
- b, Advantages of Normal Form
- c, Normalization in report
- 2.Data Validation
- 3, Physical design
- a, Create database and Create tables.
- b, Create Database Diagram
- User Interfaces of your system
- III. Conclusion
- IV. References
I. Introduction
I currently design and create software for FPT schools in my capacity as a database developer for huge corporations. In fact, FPT schools are enrolling an increasing number of students, making it essential for them to manage and provide prompt and practical services. Therefore, my primary goal this time is to design a simple method for students to monitor their grades. The major objective of this book management system is to make it simple for students to use, locate subject scores, student information, and for teachers to enter student scores with ease. In order to achieve the aforementioned requirements, we will also need to plan on using the proper tools, software, and programming languages in the project. Administrators and users who rely on the software for support will both exist in this program. And from those two items, I need to design and construct a unified database system that is closely related to one another without compromising user experience. The final product should be a functional system that manages digital assets and documents efficiently while adhering to operational rules and requirements.
users accessing the database. The database system should support data validation and ensure the integrity of the data entered into the system. It should have sufficient storage capacity to store all student and academic recordsfeatures and utilities and saves time in management. b. Objectives (Things intended to achieve) The design meets the following needs: information will be carefully managed, providing actions such as update, add, delete information for each data object, helping to quickly look up data on each object. The system has user roles, including business processes and statistical reports to be able to meet user needs. Easy data retrieval with user-friendly and easy-to-use interface
De sign the relational database system (P1)
1. De sign the relational database a, Identify entities No. Entity Description 1 Students Contains student information 2 Class Contains class information
- Teacher NO. Attribute Description 1 TeacherID ID of teacher 2 Name Name of Teacher 3 Gender Gender of Teacher 4 PhoneNumber Phone Number of Teacher 5 Email Email of Teacher 6 Birth Birth of Teacher Primary key is TeacherID NO. Attribute Description 1 CurriculumnID ID of Curriculumn 2 Curriculumn Name of Curriculumn Primary key is CurriculumnID
- Subject NO. Attribute Description 1 SubjectID ID of Subject 2 Name Name of Subject 3 Theory Number of theoretical periods 4 Lab Number of practice periods Primary key is SubjectID
- Student NO. Attribute Description 1 Student ID ID of student 2 Name Name of student 3 ClassID ID of Class of Student 4 Birth Birth of Student 5 Email Email of Student 6 Gender Email Gender of Student 7 Phone Number Phone Number of Student Primary key is StudentID
d, ER Diagram
2, Explanation about ER diagram The database consists of 6 entities namely student, teacher, class, subject, transcript and subject. In an ER (Entity-Relationship) diagram, entities represent the main objects within a system or database. Here's a breakdown of each entity:
- Curriculum : This entity represents the curriculumn or course program followed by the educational institution. It include CurriculumID, Name.
- Class : This entity represents a specific class or section within a curriculum. It have attributes such as a unique class identifier, a curriculum it belongs to, class name.
- Student: This entity represents individual students who are enrolled in classes. It may have attributes like student ID, name, birthdate, contact information, email.
- ExamMark: This entity represents the marks or grades obtained by students in exams. It may have attributes like the exam ID, student ID, marks achieved, and the date of the exam, teacherid , subjectID , teacher.
- Teacher : This entity represents the teachers or instructors who conduct classes. It may have attributes such as teacher ID, name, specialization, contact details, birth.
- Subject : This entity represents the different subjects or courses being taught. It may have attributes like subject ID, name, ... Here are the relationships between them:
Data validation and normalization
1, Normalization Normalization is the process of minimizing redundancy from a relation or set of relations. Redundancy in relation may cause insertion, deletion, and update anomalies. So, it helps to minimize the redundancy in relations. Normal forms are used to eliminate or reduce redundancy in database tables. (geeksforgeeks, 2015) a, What is Database Normalization? In database management systems (DBMS), normal forms are a series of guidelines that help to ensure that the design of a database is efficient, organized, and free from data anomalies. There are several levels of normalization, each with its own set of guidelines, known as normal forms. Important Points Regarding Normal Forms in DBMS
- First Normal Form (1NF): This is the most basic level of normalization. In 1NF, each table cell should contain only a single value, and each column should have a unique name. The first normal form helps to eliminate duplicate data and simplify queries.
- Second Normal Form (2NF): 2NF eliminates redundant data by requiring that each non-key attribute be dependent on the primary key. This means that each column should be directly related to the primary key, and not to other columns.
- Third Normal Form (3NF): 3NF builds on 2NF by requiring that all non-key attributes are independent of each other. This means that each column should be directly related to the primary key, and not to any other columns in the same table.
- Boyce-Codd Normal Form (BCNF): BCNF is a stricter form of 3NF that ensures that each determinant in a table is a candidate key. In other words, BCNF ensures that each non-key attribute is dependent only on the candidate key.
- Fourth Normal Form (4NF): 4NF is a further refinement of BCNF that ensures that a table does not contain any multi- valued dependencies.
- Fifth Normal Form (5NF): 5NF is the highest level of normalization and involves decomposing a table into smaller tables to remove data redundancy and improve data integrity.
Normal forms help to reduce data redundancy, increase data consistency, and improve database performance. However, higher levels of normalization can lead to more complex database designs and queries. It is important to strike a balance between normalization and practicality when designing a database. b, Advantages of Normal Form
- Reduced data redundancy: Normalization helps to eliminate duplicate data in tables, reducing the amount of storage space needed and improving database efficiency.
- Improved data consistency: Normalization ensures that data is stored in a consistent and organized manner, reducing the risk of data inconsistencies and errors.
- Simplified database design: Normalization provides guidelines for organizing tables and data relationships, making it easier to design and maintain a database.
- Improved query performance: Normalized tables are typically easier to search and retrieve data from, resulting in faster query performance.
- Easier database maintenance: Normalization reduces the complexity of a database by breaking it down into smaller, more manageable tables, making it easier to add, modify, and delete data. Overall, using normal forms in DBMS helps to improve data quality, increase database efficiency, and simplify database design and maintenance. (geeksforgeeks, 2015) c, Normalization in report The ER diagram has met the 3NF standard by dividing the original large entity tables into smaller entity tables and linking those tables using relationships to reduce data redundancy and eliminate data redundancy, undesirable characteristics such as Insert, Update and Delete anomalies Er diagram above includes 6 entities and most of them have a one-to-many relationship. Each entity has one or more primary keys to identify each entity in the entity set. At the same time, entities also contain foreign keys to help refer information to other tables, ensuring data integrity. Thereby, the needs of data entry and data retrieval are guaranteed to meet the basic needs of users.
5 Birth Date Not null Birth of Teacher 6 Email Varchar(30) Not null Email of Teacher
- Subject NO. Attribute Data type Constraint Description 1 SubjectID Char(4) Primary ID of Subject 2 Name Varchar(30) Not null Name of Subject 3 Theory Int Not null Number of theoretical periods 4 Lab Int Not null Number of practice periods
- Student NO. Attribute Data type Constraint Description 1 StudentID Char(9) Primary key ID of student 2 Name Varchar(30) Not null Name of student 3 ClassID Char(9) Foreign key references Class ID of Class of Student
4 Birth Date Not null Birth of Student 5 Email Varchar(30) Not null Email of Studnet 6 Gender Varchar(10) Not null, only accept ‘male’ or ‘female’ value Gender of studnet 7 PhoneNumber Char(10) Not null Phone Number of Student
- Class NO. Attribute Data type Constraint Description 1 ClassID Char(9) Primary key Course code 2 ClassName Char(9) Not null ID of Teacher 3 CurriculumnI D Char(4) Foreign key references Curriculumn ID of subject 4 Size Int Not null Time of Course
