locked
Student, TA and teacher relationship, and login system in student management RRS feed

  • Question

  • I am working on creating student database management system and I get stuck into it.

    The system serves three group of user: Student, Professor/TA and administrator. to login to system, they just type in their LoginID. Password is not required.

    Student only logins to see their grades.

    Professor/TA logins to create and update assignment grades.

    Admin can do everything: creating class list including LoginID of students, creating LoginID and account info...

    I have come up with 2 database systems, but im not sure which one is right or neither.

    Database 1 Database 2

    I think TA is also a student, so I add "IsTA" attribute into student table.

    each course is assigned to one Professor and one TA, so I add 2 attributes: TAID and ProID into class table.

    For the login system:

    In the database 1, because password is not required, student or Professor can use their IDs to login. Therefore, it is not necessary to have a User table, but what if ProID is the same as studentID? (although it is unrealistic) So I changed something as in database 2. However, a student can be Professor role in this database.

    And the relationship between class table and user table  in database 2 is  kind of weird. I do not know how to solve it. I would appreciate any help.

    Tuesday, July 9, 2013 12:57 AM

Answers

All replies

  • I think you need to have four tables  Students,Professor/TA,Exams and ExamInfo (or something)

    >>>what if ProID is the same as studentID

    How that is possible?

    I think your question is more about application design and not a database... BTW ExamInfo table may have StudentID,ProfID,ExamID,Grade


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, July 9, 2013 5:08 AM
  • Thank you for answering me, but I think the Professor's task is just to create and update grades, not to upload the exams. In the Grade table, I have an attribute: GradeType which indicates type of exam like assignment, midterm or final. However, it is not what I am getting stuck into. I am confused of TA, Professor and student relationship. Should I merge TA with student as both of them are students or merge TA with Professor as both of them can teach or create separated TA table.

    Regarding to the login system, how can I design it when it just needs LoginID? Do I have to create User table with LoginID attribute in it or just use StudentID and ProID to login?

    ">>>what if ProID is the same as studentID"

    of course it is impossible, but if we create 2 table Student and Professor. this might happen if we don't check when admin inserts new students or Professors.

    Tuesday, July 9, 2013 7:20 AM
  • Can you post the DDL so I can get it into a model? I glanced at what you have and it seems you made a good first attempt. Nice job. The devil is in the details though.

    Hope this is Helpfull. J Alverson Microsoft BI Solutions Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, July 10, 2013 11:19 PM
  • This is the link to the sql file. you can download it.

    http://www.mediafire.com/download/pk9bl15ifs61joc/DDL.sql

    Thank you!

    Wednesday, July 10, 2013 11:39 PM
  • If students and professors can teach that info should be stored in ONE table called Teachers or something... You can have a flag within a table telling you is that person student or professor.

    IsStudent=0 for example.

     

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, July 11, 2013 5:51 AM
  • so I will have a Student table and a Faculty table which is for Teacher and TA. And the database would look like this:

    Database 3

    It still has a lot of relationships and kind of complicated. Also, how about the login system? Do I need the Login table or it is not necessary?

    Thank you.

    Thursday, July 11, 2013 8:01 AM
  • Why do you have Semester and Year column in Class and Grade tables?

    Regarding logins, I think you need to have a user/login table to contain ID, name, login date, logout date or something like that.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, July 11, 2013 8:34 AM
  • Semester and Year would indicate the the time of the course. For example, the course "CMPT-300"  is open in Summer-2013. And student taking this course will have different grades from one taking the same course but in Fall-2013.

    If I have a Login table, so it would have one-one relationship with Student and Faculty table as StudentID and ProID would be used as LoginID, right?

    Thursday, July 11, 2013 8:54 AM
  • Why do you need to relate Login table? What does it give you?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, July 11, 2013 9:23 AM
  • it says Admin can create and update class list which includes LoginID. I think it means Admin can use student's LoginID to enrol them to class.
    Thursday, July 11, 2013 8:30 PM
  • >>>it says Admin can create and update class list which includes LoginID

    It means it can INSERT/UPDATE rows, so I see no reason to relate Logins table.

    >>>>I think it means Admin can use student's LoginID to enrol 

    That is OK, but again why relate Logins table?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Friday, July 12, 2013 2:24 AM
  • If we do not link Login table to any table, how can Admin use LoginID to enrol? Unless we connect it directly to Class_list table. However, if it is, it is kind of redundant as a student has 2 IDs: StudentID and LoginID. why dont we use StudentID as LoginID?
    Friday, July 12, 2013 4:22 AM
  • When Admin enroll the exam you can add a column Enrolled by Whom or something. StudentID is a person who is taken an exam but LoginID is a person who is  enrolled the class/exam. 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Friday, July 12, 2013 5:30 AM
  • No, I mean when admin create a class_list, he will use Student's LoginID to enrol them to class. Students can not enrol themselves to class, so they need admin to do that.

    The rule just says: 

    "Student logs into system to see grade.

    Admin logs into system to create and update class_list."

    so I assume Students can not enrol themselves to class.

     
    Friday, July 12, 2013 6:32 AM