0

i'm building a DB of a graduation projects management system. Students are divided into groups .There is groups table and faculty table. Each group has an advisor and two examiners. i'm confused here. Should i create 3 FKs from the the faculty table? 2 for examiners and 1 for advisor?

here is the SQL code:

 create table groups
(
    groupID             NUMBER                not null,
    nbStudents          NUMBER                not null,
    avgGPA              DOUBLE                NOT NULL,
    projectName         varchar(50)           not null,
    advisorID           NUMBER                
    examiner1ID         NUMBER  
    examiner2ID         NUMBER  
    primary key (groupID)
);
create table faculty
(
    name                varchar(30)           not null,
    facultyID           NUMBER(10)            not null,
    email               varchar(30)           not null,
    mobile              NUMBER(15)            not null,
    type                varchar               
    primary key (facultyID)
);

alter table groups
    add constraint FK_EX1 foreign key (examiner1ID)
       references faculty (facultyID) ;
alter table groups
    add constraint FK_EX1 foreign key (examiner2ID)
       references faculty (facultyID) ;
alter table groups
    add constraint FK_EX1 foreign key (advisorID)
       references faculty (facultyID) ;
onlyforthis
  • 444
  • 1
  • 5
  • 21
  • To Start You can't use the same name for different constraints. And i think you have to use the 2 constraints 1 of them is a composed foreign Key. – Blood-HaZaRd Jun 16 '14 at 12:14
  • Can an examiner/advisor be assigned to multiple groups? – natronite Jun 16 '14 at 12:15
  • Shouldn't you also have a third table for employees of the faculty which each have a faculty id? Then the advisors and examiners are just people linked to a group and also to a faculty - so to get from a group to a faculty you go through the third table? – Guy Lowe Jun 16 '14 at 12:16
  • I would go with many to many relationships. If you don't know what that means or why I mention it, I've heard good things about the book Database Design for Mere Mortals. – Dan Bracuk Jun 16 '14 at 12:16
  • 1
    This database design is fine (and preferable) if your relationship is fixed at a maximum of one advisor, and 2 examiners. Although the relationship is 1-n, introducing a junction table makes managing this much more difficult, and since `n` is known to be 3, 3 columns, although not fully normalised is much easier to work with. This essentially boils down to the `EAV vs Relational Model` debate, in which the relational Model is usually the winner. – GarethD Jun 16 '14 at 12:35
  • natronite .. yes he can. – onlyforthis Jun 16 '14 at 13:01
  • @GarethD 1. Multiple columns is not non-normalized. 2. This has nothing to do with EAV. 3. Obvious relational design is advisor(groupID,facultyID) & examiner(groupID,facultyID). 4. But yes the max count constraints are a pain in SQL even with null facultyID. – philipxy Jun 17 '14 at 02:48

1 Answers1

0

EDIT PENDING... see my first comment.

Just state the foreign keys as you find them.

A foreign key says that a value in a column in a table must appear as a value of a column in another (possibly the same) table where corresponding columns form a key. So in the given design just declare the FKs as you find them.

Although these aren't really FKs. First, in SQL a FK declaration actually declares a foreign superkey. Second, because those columns can be NULL. SQL says how it's going to check them, and it doesn't check when columns are NULL, and that's what you want. But that constraint just isn't a foreign (super)key. We just call it that in an SQL database.

Find statements that describe your application situtations then normalize.

It not non-normalized to have multiple columns per se. That is a common misconception.

However its generally contraindicated for at least an ideal design. Just find a parameterized statement parameterized by column names for every thing you need to say about a situation. Each statement gets a table.

// group [groupID] contains [nbStudents] students .... and has advisor [advisorID] and ...
groups(groupID,nbStudents,...,advisorID,examinerID)

The rows that make the statement true go in the table. Find all the statements you need to describe your application situations. Fill the tables with the rows that make their statements true.

Find simple statements and rearrange for NULL later.

Notice that the above statement is only true for rows with no NULLs. But you want to say sometimes that no faculty are in those roles.

Ideally you just want

// group [groupID] contains [nbStudents] students ... [projectName])
groups(groupID,nbStudents,...,projectName)
// [facultyID] advises [groupID]
advises(facultyID,groupID)
// [facultyID] examines [groupID]
examines(faculty,groupID)

With constraints about numbers of faculty per group. If you properly write a relational design without nulls then normalize you will get this sort of simple thing. Don't worry about the number of statements/tables. They just reflect the complexity of the application.

But SQL DBMSs generally don't suport constraints easily. So for certain reasons to do with SQL or performance we might to rearrange. But design null-free first. Ie pick straightforward statements & then normalize. Then rearrange soundly. (SOme rearranging might de-normalize, but not this particular case.)

Nulls complicate.

One problem with nulls is they complicate table meanings. Your design with nulls has table group holding the rows that make this statement true:

//*
    group [groupID] contains [nbStudents] students ....
AND (   [advisorID IS NULL  they have no advisor
    OR [advisorID] IS NOT NULL AND advisor [facultyID] advises them)
AND (   [examiner1ID IS NULL AND [examiner2ID] IS NULL and they have no examiner
    OR [examiner1ID] IS NOT NULL AND [examiner2ID] IS NULL AND [examiner1ID] examines them
    OR [examiner1ID] IS NULL AND [examiner2ID] IS NOT NULL AND [examiner2ID] examines them
    OR [examiner1ID] IS NOT NULL AND [examiner2ID] IS NOT NULL
       AND [examiner1ID] examines them AND [examiner2ID] examines them)
*//
groups(groupID,nbStudents,...,advisorID,examinerID)

Unless you cut out nulls back to the simple tables above when querying, your query meanings are complicated like this too. Ie queries give rows that make statements like that true.

On top of that when nulls are left in SQL gives you complex answers that do not mean "... and faculty unknown".

People have intuitive understanding of such nulls in base tables. But design first simply and soundly. Rearrange later. Be sure you properly cut out null-free parts and leave in null-free parts when you query.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83
  • Thanks for the detailed answer. Actually i kept the nulls because the examiners and advisors won't be determined until the groups choose their project and the admin finalize them. so when the group is initially created it will have no advisors or examiners. did i make it right?? – onlyforthis Jun 17 '14 at 09:53
  • If you'd like an answer re design issues (it depends on how you intend it to be used) then please add that to the question or ask a new question. (It only came up in comments.) If so answer: a. can there ever be other than 0 or 3 nulls and b. with 2 examiners whether it matters which examiner is 1 or 2 and c. with 1 examiner how do i pick the null – philipxy Jun 17 '14 at 21:48
  • a.actually it all shouldn't be null but the thing is they will be filled later, not immediately after creating a group. the value of these attributes depends on another action that happens later(groups choosing projects and group finalization by admin) b.doesn't matter c.they are always 2 for each group. i hope i made it clear. – onlyforthis Jun 17 '14 at 22:02
  • Thanks. a. The vague "actually it all shouldn't be null but" is not yes or no; and it is confusing and unhelpful and wrong; it's allowed by your design and you go on to say those columns are null sometimes. I guess from your answer to c the answer is "no; a row only ever has 0 nulls or 3 nulls" b."doesn't matter" is not yes or no; but I understand. c. Ok, 2. ...When there aren't 0. Again: please add to your question or ask a new one (with this info & your answer to natronite). – philipxy Jun 17 '14 at 22:31
  • i will.. but here is the direct answers for now: a.no, b.no, c.always 2 examiners. Thanks alot for all the effort – onlyforthis Jun 17 '14 at 22:45