Attendance Management Using MS Access and VB 6 Part – 2

In the previous article, you learned about the client requirements about the attendance management system.Now You will create a database for the attendance management.

Advertisements

Visit following links to learn about the other parts of the attendance management system.

Part 1: Attendance management system: requirement analysis

In this article, we will design the database for attendance management system which is required to store student information, teacher information, admin information, attendance details including passwords for login.

ER Diagram for Attendance Management

Every database design starts with identifying the entity set and the relationship between those entity sets. We already found following entity for the database.

  1. Student
  2. Teacher
  3. Admin

The entity sets have a relationship with each other such as:

  • a teacher teaches many students
  • a student has many teachers
  • teacher manage attendance for students
  • admin manages students
  • admin manages teachers

Based on the above information we can draw an er diagram for the system.

Figure1-ER Diagram for Attendance Management System
Figure1-ER Diagram for Attendance Management System

The ER diagram provides us with basic entity-relationship. It is not detailed because it does not show how to represent a relationship between entity sets while creating relations or tables.

Relational Model of Attendance Management System

Before you can create tables for the application, you must create another model that represents all possible relations within the database. It is basically, a practice of converting the ER model into a relational model.

All entities, relationship with attributes becomes tables and all attributes are fields of the table. The attributes with an underline are the primary key for that table.

Figure2-Relational Model for Attendance Management System
Figure2-Relational Model for Attendance Management System

Relations and Relationships

This project uses Microsoft Access as the back-end database. You must save your database files in access 2003-2007 (.mdb) format;therefore, create a new database file in the following location. See image below.

Figure3-MSAccess Database Location
Figure3-MSAccess Database Location

The visual basic 6 application can conveniently connect to the database and fetch all necessary records.

Let’s create all relations for the project.

Admin table

Open MS Access in Design view, create a new table with following fields and data types. Set the AID as the primary key for the table by clicking on the key icon on the toolbar.

Figure4-Admin Table in Design View

Note that the AID is not an autonumber if you are using autonumber then code to retrieve the records with change.

Student Table

Now you have to create a student table in Design view again. The student table has Sid as the primary key.

Advertisements
Figure5-Student table in Design view
Figure5-Student table in Design view

Sid is not an autonumber, selecting autonumber for data type will change the code to retrieve the data.

Teacher Table

Create a table for Teacher staff and set the Tid as the primary key. So far all tables have single field as primary key so it should not be difficult to create tables.

Figure6-Teacher table in Design view
Figure6-Teacher table in Design view

Attendance Table

Create the Attendance table which has 2 attributes working as the primary key. Therefore, select both SID and Adate and set as primary.

Figure7-Attendance Table in Design view
Figure7-Attendance Table in Design view

Teaches Table

Create a table for Teachers relationship based on the relational model and set SID and TID as the primary key for the table.

Figure8-Teaches table in Design View
Figure8-Teaches table in Design View

Creating Relationships between Tables

You can create relationships among table in ms access using the relationship tool under Database Tools tab. Add the tables for which you want to define relationships and create a relationship between specific fields.

You can also choose the type of relationship such as:

  • one-to-one
  • one-to-many
  • many-to-many

Create a relationship for attendance management system that matches the following figure. Before you join tables make sure that the TABLES ARE POPULATED WITH DATA, otherwise, you will get errors.

Figure9-Relationship Diagram in Access for Attendance Management
Figure9-Relationship Diagram in Access for Attendance Management

There are many types of relationships available in MS Access. But, for this project use following settings for relationships or JOINs between all tables.

Student to Other Tables

Figure10-Student to Attendance table is one-to-many
Figure10-Student to Attendance table is one-to-many
Figure11-Student to Teaches is one-to-many
Figure11-Student to Teaches is one-to-many

Teachers to Other tables

Figure12-Teachers to Attendance is one-to-many
Figure12-Teachers to Attendance is one-to-many
Figure13-Teachers to Teaches is one-to-many
Figure13-Teachers to Teaches is one-to-many

Admin to Student and Teacher JOINs

The join between Admin to Student table and Teacher table is identical. See the diagram below.

Figure14-Admin-to-Studentis one-to-one
Figure14-Admin-to-Studentis one-to-one

Create a similar connection or join between Admin and Teacher table in Relationship tool.

In the next part, we will build a visual basic interface to communicate with database and meet the user requirements by manipulating data.

Advertisements

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.