Joshua Nato's team blog

Activity 8: Normalize the Denormalize table

Joshua Nato's photo
Joshua Nato
·

3 min read

You're describing a normalized database design, which is a good practice for managing data effectively. Let's break down each table and its purpose:

1. Student Table (Basic Student Details)

  • Purpose: This table stores core information about each student.

  • Columns:

    • studentId (PK): Integer, Primary Key, uniquely identifies each student.

    • studentName: VARCHAR(255), Stores the student's full name.

    • gender: VARCHAR(255), Stores the student's gender.

    • dateOfbirth: DATE, Stores the student's date of birth.

    • address: VARCHAR(255), Stores the student's address.

    • city: VARCHAR(255), Stores the student's city.

    • postalCotle: INT, Stores the student's postal code.

    • phoneNumber: VARCHAR(255), Stores the student's phone number.

    • email: VARCHAR(255), Stores the student's email address.

2. Course Table (Course-Related Data)

  • Purpose: This table manages information about the courses offered.

  • Columns:

    • courseId (PK): Integer, Primary Key, uniquely identifies each course.

    • courseName: VARCHAR(255), Stores the name of the course.

    • courseCredits: INT, Stores the number of credits for the course.

    • departmentId (FK): Integer, Foreign Key referencing the Department Table.

3. Department Table (Department Information)

  • Purpose: This table stores details about the academic departments.

  • Columns:

    • departmentId (PK): Integer, Primary Key, uniquely identifies each department.

    • departmentName: VARCHAR(255), Stores the name of the department.

4. Project Table (Student Project Details)

  • Purpose: This table tracks information about student projects.

  • Columns:

    • projectId (PK): Integer, Primary Key, uniquely identifies each project.

    • projectTitle: VARCHAR(255), Stores the title of the project.

    • studentId (FK): Integer, Foreign Key referencing the Student Table.

    • projectSupervisor: VARCHAR(255), Stores the name of the project supervisor.

5. Scholarship Table (Scholarship Information)

  • Purpose: This table manages scholarship details.

  • Columns:

    • scholarshipId (PK): Integer, Primary Key, uniquely identifies each scholarship.

    • scholarshipType: VARCHAR(255), Stores the type of scholarship.

    • scholarshipStatus: VARCHAR(255), Stores the scholarship status.

    • studentId (FK): Integer, Foreign Key referencing the Student Table.

6. ExtracurricularActivities Table (Extracurricular Activities)

  • Purpose: This table tracks student participation in extracurricular activities.

  • Columns:

    • extracurricularId (PK): Integer, Primary Key, uniquely identifies each extracurricular activity.

    • extraCumicularName: VARCHAR(255), Stores the name of the extracurricular activity.

    • studentId (FK): Integer, Foreign Key referencing the Student Table.

7. Advisor Table (Advisor Information)

  • Purpose: This table stores information about advisors.

  • Columns:

    • advisorId (PK): Integer, Primary Key, uniquely identifies each advisor.

    • advisorName: VARCHAR(255), Stores the name of the advisor.

8. Attendance Table (Student Attendance Records)

  • Purpose: This table stores student attendance records.

  • Columns:

    • attendanceId (PK): Integer, Primary Key, uniquely identifies each attendance record.

    • studentId (FK): Integer, Foreign Key referencing the Student Table.

    • courseId (FK): Integer, Foreign Key referencing the Course Table.

    • attendancePercentage: FLOAT, Stores the student's attendance percentage.

    • date: DATE, Stores the date of the attendance record.

Key Concepts:

  • Normalization: A database design technique that reduces data redundancy and improves data integrity by breaking down large tables into smaller, related tables.

  • Primary Keys (PK): Uniquely identify each record within a table.

  • Foreign Keys (FK): Establish relationships between tables, ensuring data consistency.

Benefits of Normalization:

  • Reduced Data Redundancy: Saves storage space and prevents inconsistencies.

  • Improved Data Integrity: Foreign keys maintain relationships, ensuring data accuracy.

  • Enhanced Data Flexibility: Changes in one table don't affect others, making updates easier.

  • Better Performance: Queries are more efficient as they access only relevant data.