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.
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.
- Student
- Teacher
- 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.
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.
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.
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.
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.
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.
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.
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.
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.
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
Teachers to Other tables
Admin to Student and Teacher JOINs
The join between Admin to Student table and Teacher table is identical. See the diagram below.
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.