CODEWITHSUNDEEP
database-designnormalization
Omie
Omie

Reputation: 300

Database design help required

I need help in building simple database structure. Kind of stuck.

Here's what I'm trying.

Class means Department+Semester. Eg, Electronics Engineering SemI, Electrical Engg Sem II.. etc..

Student is in class; Class has a defined group of subjects; Group consists of multiple subjects.

Class and Subjects, 2 entities depend on same SubjectGroup entity. So isn't this a ternary relationship ?

I've made this image to make picture clear. [ P = Primary key; F = Foreign Key; Arrows do not mean anything of One-to-many/many to many etc.. they are just showing what referenced where]

alt text

I am stuck because I can not refer to GroupId two times.

How should I modify this structure ?

- Thanks

Upvotes: 0

Views: 138

Answers (2)

DwB
DwB

Reputation: 38300

Entities

  • Student.
  • Class.
  • Subject.

Relationships

  • Student to Class. One to Many. One class relates to many students.
  • Subject to Class. One to Many. One class relates to many subjects.

Tables

Student

Columns:

  • studentId - primary key of the student table.
  • stuff about sutdent (some number of columns).
  • classId - foreign key into the class table.

Subject

Columns:

  • subjectId - primary key of the subject table.
  • stuff about subject (some number of columns).
  • classId - foreign key into the class table.

Class

  • classId - primary key of the class table.
  • stuff about a class. nothing about student. nothing about subject. no foreign key to student. no foreign key to subject.

Queries

Students in a class

select
    s.studentName
from
    class c
    inner join student s on s.classId = c.classId
where
    c.classId = 'desired class id'

Subjects Student is Studying

select
    sub.subjectName
from
    student stu
    inner join subject sub on sub.classId = stu.classId
where
    stu.studentId = 'desired student id'

Upvotes: 1

Beth
Beth

Reputation: 9607

I think you need to introduce a classSubject table. Is this an accurate example of rows?

student
beth
john
mark

class
beths_and_johns_class bethssubject, johnssubject
marks_class markssubject

subject
bethssubject
johnssubject
markssubject

Upvotes: 0

Related Questions