Database Design
This section describes the database design for Sao Viet. The database is designed using MS SQL Server. The database is designed to be normalized to 3NF.
Entity Relationship Diagram
Database Schema
Table: Student
Description: Student is a person who is studying at Sao Viet. A student can enroll in many courses. Each student has a unique student ID.
Normal form: BCNF
| Column | Type | Required | Description |
|---|---|---|---|
| Id | Char(10) | An unique id for each student Length must be 10 character long | |
| FullName | Nvarchar(50) | Full name of the student Length must be less than 50 character long | |
| Gender | Bit | Sex of the student True: Male False: Female | |
| Address | Nvarchar(80) | The student's address may include the ward, district, city, country, and other pertinent information. Length must be less than 80 character long | |
| Dob | Date | The student's date of birth Date of birth must be in the past | |
| Pod | Nvarchar(80) | The student's place of birth may include the ward, district, city, country, and other pertinent information. Length must be less than 80 character long | |
| Occupation | Nvarchar(80) | The student's occupation Length must be less than 80 character long | |
| Social | Nvarchar(max) | The student's social information may include their phone number, email, and social media profiles such as Facebook. This information should be in JSON format and must contain the following fields:
|
Table: CourseEnrollment
Description: Course enrollment is a relationship between a student and a course. A student can enroll in many courses. A course can have many students. Each CourseEnrollment has StudentId and CourseId as primary foreign keys.
Normal form: 3NF
| Column | Type | Required | Description |
|---|---|---|---|
| StudentId | Char(10) | An unique identifier for each student, used as a foreign key to reference the Student table. Must be 10 characters long and match an existing value in the Student table. | |
| CourseId | Char(10) | An unique identifier for each course, used as a foreign key to reference the Course table. Must be 10 characters long and match an existing value in the Course table. |
Table: Course
Description: Course is a class that students can enroll in. A course can have many students. Each course has a unique course ID.
Normal form: BCNF
| Column | Type | Required | Description |
|---|---|---|---|
| Id | Varchar(10) | An unique id for each course Length must be 10 character long | |
| Name | Nvarchar(50) | Name of the course Length must be less than 50 character long | |
| Description | Nvarchar(max) | A description of the course |
Table: CourseRegistration
Description: Course registration is a relationship child of course enrollment. A student can register for many courses. A course can have many students. Each course registration has StudentId and CourseId as foreign keys.
Normal form: BCNF
| Column | Type | Required | Description |
|---|---|---|---|
| Id | Uniqueidentifier | An unique id for each student Generated Guid from application | |
| Status | Nvarchar(10) | Indicates the registration status of the student in the course. Must be one of the following values: Chốt, Hẹn, Huỷ | |
| ReregistrationDate | Date | The date the student registered for the course. | |
| AppointmentDate | Date | The date the student made an appointment for the registration of the course. AppointmentDate ≥ ReregistrationDate | |
| Fee | Float | The fee the student has to pay for the course. Fee ≥ 0 | |
| Discount | Decimal(4,2) | The discount the student has for the course. Discount ≥ 0 and Discount ≤ 100 | |
| StudentId | Char(10) | An unique identifier for each student, used as a foreign key to reference the Student table. Must be 10 characters long and match an existing value in the Student table. | |
| ClassId | Char(10) | An unique identifier for each class, used as a foreign key to reference the Class table. Must be 10 characters long and match an existing value in the Class table. | |
| PaymentMethodId | Tinyint | An unique identifier for each payment method, used as a foreign key to reference the PaymentMethod table. Must be integer and match an existing value in the PaymentMethod table. |
Table: PaymentMethod
Description: Payment method is a lookup table that contains the payment methods that students can use to pay for the course. Each payment method has a unique payment method ID.
Normal form: BCNF
| Column | Type | Required | Description |
|---|---|---|---|
| Id | Tinyint | An unique id for each payment method Must be integer and auto increment | |
| Name | Nvarchar(50) | Name of the payment method Length must be less than 50 character long |
Table: Class
Description: Class is a lookup table that contains the classes that students can register for. Each class has a unique class ID.
Normal form: BCNF
| Column | Type | Required | Description |
|---|---|---|---|
| Id | Char(10) | An unique id for each class Length must be 10 character long | |
| StartDate | Date | The date the class starts | |
| EndDate | Date | The date the class ends EndDate ≥ StartDate | |
| CourseId | Varchar(10) | An unique identifier for each course, used as a foreign key to reference the Course table. Must be less than 10 characters long and match an existing value in the Course table. | |
| BranchId | Char(8) | An unique identifier for each branch, used as a foreign key to reference the Branch table. Must be 8 characters long and match an existing value in the Branch table. |
Table: Branch
Description: Branch is a lookup table that contains the branches that students can register for. Each branch has a unique branch ID.
Normal form: BCNF
| Column | Type | Required | Description |
|---|---|---|---|
| Id | Char(8) | An unique id for each branch Length must be 8 character long | |
| Name | Nvarchar(50) | Name of the branch Length must be less than 50 character long | |
| Address | Nvarchar(80) | The branch's address may include the ward, district, city, country, and other pertinent information. Length must be less than 80 character long | |
| Phone | Char(10) | The branch's phone number Length must be 10 character long Regex: \d{10} |
Table: StudentProgress
Description: Student progress is a lookup table that contains the progress of students in a class. Each student progress has a unique student progress ID.
Normal form: BCNF
| Column | Type | Required | Description |
|---|---|---|---|
| Id | Uniqueidentifier | An unique id for each student progress Generated Guid from application | |
| Name | Nvarchar(80) | A lesson name of the student progress Length must be less than 80 character long | |
| Content | Nvarchar(max) | The content of the lesson | |
| LessonDate | Date | The date of the lesson LessonDate ≥ StartDate and LessonDate ≤ EndDate and must be in the past | |
| Status | Char(10) | The status of the lesson Must be one of the following values: Vắng học, Miễn học, Hoàn thành | |
| Ratings | Tinyint | The rating of the lesson Must be between 0 and 10 | |
| TeacherId | Varchar(20) | An unique identifier for each teacher, used as a foreign key to reference the Teacher table. Must be less than 20 characters long and match an existing value in the Teacher table. | |
| StudentId | Char(10) | An unique identifier for each student, used as a foreign key to reference the Student table. Must be 10 characters long and match an existing value in the Student table. | |
| ClassId | Char(10) | An unique identifier for each class, used as a foreign key to reference the Class table. Must be 10 characters long and match an existing value in the Class table. |
Table: Postion
Description: Position is a lookup table that contains the position of teachers. Each position has a unique position ID.
Normal form: BCNF
| Column | Type | Required | Description |
|---|---|---|---|
| Id | Int | An unique id for each position Must be integer and auto increment | |
| Name | Nvarchar(30) | Name of the position Length must be less than 30 character long |
Table: Staff
Description: Staff is a lookup table that contains the information of staffs. Each staff has a unique staff ID.
Normal form: BCNF
| Column | Type | Required | Description |
|---|---|---|---|
| Id | VarChar(20) | An unique id for each staff Must be less than 20 character long | |
| FulName | Nvarchar(55) | Name of the staff Length must be less than 55 character long | |
| Dob | Date | The staff's date of birth Date of birth must be in the past | |
| Address | Nvarchar(80) | The staff's address may include the ward, district, city, country, and other pertinent information. Length must be less than 80 character long | |
| Dsw | Date | The staff's date of start working | |
| PositionId | Int | An unique identifier for each position, used as a foreign key to reference the Position table. Must be integer and match an existing value in the Position table. | |
| BranchId | Char(8) | An unique identifier for each branch, used as a foreign key to reference the Branch table. Must be 8 characters long and match an existing value in the Branch table. | |
| ManagerId | Varchar(20) | An unique identifier for each manager, used as a foreign key to reference the Manager table. Must be less than 20 characters long and match an existing value in the Manager table. |
Table: ReceiptExpense
Description: ReceiptExpense is a lookup table that contains the information of receipt expenses. Each receipt expense has a unique receipt expense ID.
Normal form: BCNF
| Column | Type | Required | Description |
|---|---|---|---|
| Id | UniqueIdentifier | An unique id for each receipt expense Generated Guid from application | |
| Type | Bit |
| |
| Date | Date | The date of the receipt expense | |
| Amount | Float | The amount of the receipt expense Must be greater than 0 | |
| Note | Nvarchar(max) | The note of the receipt expense | |
| BranchId | Char(8) | An unique identifier for each branch, used as a foreign key to reference the Branch table. Must be 8 characters long and match an existing value in the Branch table. |
ASP.NET Core Identity Schema
ASP.NET Core Identity is an API, which provides a framework for implementing authentication and authorization in .NET Core applications. It is an open-source replacement for the previous ASP.NET Membership system. ASP.NET Core Identity allows you to add login features to your application and makes it easy to customize data about the logged-in user.
ASP.NET Core Identity uses a SQL Server database to store user names, passwords, and profile data. The schema for the database is automatically created when you create the project. The schema is based on the ASP.NET Core Identity Entity Framework Core Code First default schema. The schema is designed to support the default ASP.NET Core Identity UI, which you can scaffold into your project.
The ASP.NET Core Identity schema is designed to support the default ASP.NET Core Identity UI, which you can scaffold into your project. The schema is based on the ASP.NET Core Identity Entity Framework Core Code First default schema. The schema is automatically created when you create the project.
Learn more about ASP.NET Core Identity schema at ASP.NET Core Identity schema.