1

I'm struggling to set up a database to use in my School Academic system that is supposed to record all student's scores and then be able to prepare a report that shows students ranks.

The hardest part is, if I have many students and many courses/subjects, and students take 2 tests every semester, how do I set up db tables to record these in such a way that I can later use sql SELECT statements to figure out student's total scores as well as ranks?

I currently have tables for demographic,courses,classes & scores as seen in the attached photodatabase tables created so far

Thanks.

1

Your current scores table (with 4 test scores in it) isn't going to work. What if you have five scores? Or none?

Database tables always have one record per entity. In the case of a Scores table, that means you have one score per record. Do that, and I think you'll find that you can write a SQL query for whatever you want, including the grade, average and totals, all of which can be calculated on the fly in a query.

1

It's really hard to give you an answer that sufficiently solves your question. The reality is that the point of using a relational database is to model real world relationships between entities.

A school database can be quite complex. It has to take into account a lot more than just a few courses, students, etc. And normalizing that data (removing redundancy) is complex!

For example, heres what some of those relationships might look like:

# (middle initial is last for optimization)
-person
    - id
        +- int
        +- primary key, unique, not null
    - type
        +- [foreign key] => position_id
    - gender
        +- enum (Male/Female)
        +- not null
    - dob
        +- can be a date, a string, etc
        +- not null
    - address
        +- [foreign key] => address_id
    - first_name
        +- varchar
        +- not null
    - last_name
        +- varchar
        +- not null
    - middle_initial
        +- char
        +- can be null

# the position table tells you the type of person
# everytime a new position is created, it's added here
# this can be simple w/ 2 records:
#   1. student
#   2. faculty
# but it can also have administrative staff, etc and grow
# rather large
-position
    - position_id
        +- primary key, not null, unique
    - psition
        +- varchar


# holds all addresses.. these can be shared by faculty and students...
-addresses
    - address_id
        +- can be a hashsum, an int, etc... but it can also be
           a composite key, made up of every other field in this table
    - street_number
        +- int
    - street_name
        +- varchar
    - municipality
        +- [foreign key] => municipality_id
    - county
        +- [foreign key] => county_id
    - country
        +- [foreign key] => country_id

-municipality
    ...contains attributes of all municipalites that
    the school serves. new ones can be added, old ones
    can be removed...

-county
    ...same as municipality

# list of departments
-department
    -department_id
    ...etc, more attributes of a department

# list of available courses
-course
    - course_id
    - department
        +- [foreign key] => department_id

# current semester and all previous semesters
-semester
    ...this is where the semesters are stored

# grade
-grade
    - grade_id
    - foreign key for course_id
    - foreign key for semester_id
    - foreign key for student person record
    - foreign key for teacher person record

Now, this isn't perfect by any means. It's rather incomplete! Providing you with a fully normalized, air-tight database schema is beyond the scope of this post (unless you're willing to hire a contractor, wink).

The point I was trying to make with the schema outline I provided is that you want to store entities in unique tables and link those tables together to show relationships.

The person table is a good example because that table can have quite a few relationships. For example, more than one person may live at the same address and so we've added an address table. We have to describe the type/function/role of that person -- are they a student? faculty? administrative employee? volunteer? school board? emergency contact? A grandparent who is authorized to pick a child up? etc, etc...

Also, one could argue that gender is shared across multiple records and should be a foreign key instead -- that would be a poor decision. Since there is such a small number of possible answers, it's more efficient to use a databases built-in enum type. The database engine will optimize enums for you.

If you don't separate entities your database performance will suffer. Imagine if you had the same counties and municipalities duplicated thousands of times in the address table! Municipalities and counties are finite -- usually a school can limit them down to a certain geographical area, say within 100 miles of the school.

This way, in the future it is easy to add new counties, if a new person has an address that resides in a municipality or county that doesn't have a corresponding record.

You may also want to create a country table if you have even the most trivial reason to believe that students who permanently reside in another country will be in the database. Think foreign exchange student, or a student studying abroad.

As you can see, these relationships are complex and you need to think out your database accordingly.

For instance, there are quite a large number of entities that will be represented in the database. I'll list some below and let you take it from here:

  • Departments
  • Semesters
  • Courses
  • Course_materials
  • Grades
    • Keep in mind students may take the same class twice if they fail

There are more things you must keep in mind as well, which can be in the databse:

  • Keeping track of vendors utilized by the school
  • Receiving tuition, billing tuition, etc
  • Keeping track of who has scholarships
  • Keeping track of which students are on which sports teams
  • Keeping track of GPAs and putting failing students on academic probation
  • Keeping track of infractions, detentions, suspensions
  • Keeping track of transfer students, etc
  • Thanks you so much @Charles for your input. As you said, this is going to be a complex database as it will feature most of what you have listed in your post. I'm taking your recommendation and continue thinking how to better design this database. – Frank Lema Jul 23 '16 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.