Hey everyone!
I am developing a database for the Attendance system in my university, I've created a blue print somehow and sketched out the entities on the paper. But still I'm a little bit confused about the tables, that might be there should be more number of tables should be available in my database. I'm sharing my concept kindly help me out in filtering my design further.

This System is only for the Attendance for the students in the university, so I have made the following tables in my database.

1. Teachers.
        a)id
        b)name
        c)designation
        d)courses_assigned
2. Students.
        a)id
        b)name
        c)roll_no
        d)courses_registered
        e)Rfid_tag_id
        f)attendance %
        g)registeration_Status
        h)username
        i)password
        j)attendance marked(Boolean)
3. Admin/Users. (Will it be good to make them seperately?)
        a)id
        b)username
        c)password
4. Courses.
        a)id
        b)course_name
        c)credit_hours
        d)teachers_teaching
        e)semester

Is that enough? or some more entity or table should be present in my design?
Can someone help me out regarding to the constraints? or Relationships between the tables?

Recommended Answers

All 2 Replies

I have other vision:

Users [id, name, username, password, role]
Field role can take 0 for admin, 1 for teachers, 2 for students

Teachers [id, iduser, designation]
Students [id, iduser, ... other data...]
iduser = id from table Users    

Courses [id, course_name, credit_hours, idteacher, semester]

idteacher - is id from Teachers - the id of teacher who teaching the course; that if a course is teaching by a single teacher otherwise you need a link table like this:
    course_teacher [id, idteacher, idcourse]
and from table Courses eliminate field idteacher

course_student [id, idcourse, idstudent, percent_attendance]
This is a link table becouse a student participate in several courses and one course is followed by more students

attendace [id, idcs, date, attendance marked(Boolean)]
 idcs is the id from table course_student
 or
 [id, idcourse, idstudent, date, attendance marked(Boolean)]

I hope this is help.
Regards.

CREATE TABLE teacher (
    teacher_id INT NOT NULL AUTO_INCREMENT,
    course_id INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,
    FOREIGN KEY (course_id) REFERENCES course(course_id),
    PRIMARY KEY (teacher_id)
)


CREATE TABLE category (
    category_id INT NOT NULL AUTO_INCREMENT,
    category_id VARCHAR(100) NOT NULL,
    PRIMARY KEY (category_id)
)


CREATE TABLE student (
    student_id INT NOT NULL AUTO_INCREMENT,
    course_id INT NOT NULL,
    rfid_tag_id INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,
    roll_no VARCHAR(100) NULL,
    FOREIGN KEY (rfid_tag_id) REFERENCES        rfid_tag(rfid_tag_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id),
    PRIMARY KEY (student_id)
)

Admin

CREATE TABLE admin (
    admin_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,
    PRIMARY KEY (admin_id)
)

Courses

CREATE TABLE course (
    course_id INT NOT NULL AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    PRIMARY KEY (course_id)
)

Something like this?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.