Microsoft Access: Student Course Registration Database

The goal of this project was to design and implement a database management system (DBMS) in Microsoft Access for handling student course registrations, ensuring proper data structure, relationships, and retrieval of information.

Database Design and Table Creation

Four key tables were created to represent the course registration system:

  • Courses – stores details of each course (Course ID, Course Name, Days, and Professor).

  • Students – stores student information (Student ID, Name, Email, Address, Phone Number).

  • Registration – records registration transactions (Registration Number, Student ID, Registration Date).

  • RegistrationLn – stores detailed registration lines linking students’ registrations to courses (Registration Number, Course ID).

 

Relationships and Referential Integrity

Relationships between the tables to ensure referential integrity:

  • Students → Registration (one-to-many).

  • Registration → RegistrationLn (one-to-many).

  • Courses → RegistrationLn (one-to-many).

Cascade update and cascade delete rules were applied to maintain consistency (e.g., deleting a registration automatically deletes its related registration lines).

Primary keys were assigned for unique identification (e.g., Student_ID, Course_ID), and foreign keys were defined to link related tables.

Data Entry

Sample data was entered to populate the tables:

  • Courses such as IS in Management, Database Design, Project Management were added.

  • Students John, Merry, and Peter were registered with corresponding contact details.

  • Registration records were created with unique registration no.  linked to students.

  • RegistrationLn entries connected students to the courses they enrolled in.

Query Development

A query (Query1) was designed to retrieve specific information from the database.
For example, it extracted all students who were registered in the course 2KA3 by joining the Registration and RegistrationLn tables.

FIG: E-R Diagram (Conceptual Modeling)

Entities

  1. Students: Attributes: Student_ID (PK), Name, Email, Address, Phone_Number

  2. Courses: Attributes: Course_ID (PK), Course_Name, Course_Days, Prof_Name

  3. Registration: Attributes: Registration_Number (PK), Student_ID (FK), Registration_Date

  4. RegistrationLn (composite/bridge entity): 
    Attributes: Registration_Number (FK), Course_ID (FK)
    Serves as a junction table to resolve the many-to-many relationship between Students and Courses.

Relationships

  • Students → Registration: One student can have many registrations (1:M).

  • Registration → RegistrationLn: One registration can include multiple courses (1:M).

  • Courses → RegistrationLn: One course can appear in many registration lines (1:M).

Cardinalities

  • Student - (1:M) - Registration

  • Registration - (1:M) - RegistrationLn

  • Course - (1:M) - RegistrationLn

This structure essentially captures:

  • Each student can register many times (different semesters or transactions).

  • Each registration can include multiple courses.

  • Each course can be linked to many students through registration lines.

PROJECT FILE:

Please download the Microsoft Access file from the Dropbox link provided to view the full database implementation.
Link: https://www.dropbox.com/scl/fi/u3qdbte78b0xqcfow6jj8/Assign.accdb?rlkey=i0figtlu27kwpwjw1a19mdokp&st=488h80s2&dl=0 

How to View and Understand the Project

 

  • Read the description

    • It explains the database design, table structures, relationships, data entry, and query development in detail.

  • Examine the Database File (.accdb)

    • Download the Microsoft Access database file from the provided Dropbox link.

    • Open it in Microsoft Access to explore the actual implementation.

    • Navigate through the Tables (Courses, Students, Registration, RegistrationLn) to see how data is structured.

  • Review Relationships

    • Open the Database Tools → Relationships view in Access.

    • This will show how the tables are linked together with referential integrity (1-to-many relationships).

  • Check the Data Entries

    • Open each table in Datasheet View to review the sample data that was entered (students, courses, and registrations).

    • Notice how records in the Registration and RegistrationLn tables connect students to their enrolled courses.

  • Run the Query (Query1)

    • Open Query1 in Access to see how SQL was used to extract specific information (e.g., all students registered in  2KA3).

    • This demonstrates how the database can be queried for useful insights.

  • Study the ER Diagram

    • Refer to the E-R Diagram in the report to understand the conceptual design.

    • It shows entities (Students, Courses, Registration, RegistrationLn), attributes, and relationships, helping visualize how data is connected.

  • Connect Design to Implementation

    • Compare the ER diagram with the actual Access tables and relationships.

    • This helps verify that the conceptual model (design) matches the physical implementation in Access