CODEWITHSUNDEEP
sqlsqlite
student_R123
student_R123

Reputation: 1002

SQL tables with boolean variables

I am kind of new to SQL using and i am playing with different scenarios.

My Example is as follows :

Suppose that i need to have a student enrollment system . I created the student table as follows :

create table student (net_id char(10) , first_name char(20)  ,
 last_name char(20) , Major char(10) , ISGraduate BOOLEAN,
                        primary key (net_id)

where isgraduate boolean variable measures whether a student is a graduate student or not.

I have a another entity which is professor as follows :

 create table professor (net_id char(10) , first_name char(20) 
 , last_name char(20) , Rank char(10) , department char(20) ,
                        primary key (net_id))

Then if i want to model the relationship : a professor can advise many graduate students , then i might need to create a new table adviosry , But in there how can i filter only the graduate students ?

any help would be great .

Thank you.

Upvotes: 0

Views: 71

Answers (1)

forpas
forpas

Reputation: 164099

You can create the table advisory like this:

create table advisory (
  professor_id char(10),  
  student_id char(10),
  ISGraduate BOOLEAN default 1,
  foreign key (professor_id) references professor(net_id),
  foreign key (student_id, ISGraduate) references student(net_id, ISGraduate),
  primary key (professor_id, student_id)
);

and add a unique index in the table student for the columns net_id and ISGraduate:

create table student (
  net_id char(10), 
  first_name char(20),
  last_name char(20), 
  Major char(10), 
  ISGraduate BOOLEAN,
  unique(net_id, ISGraduate),
  primary key (net_id)
);

See the demo.
Also make sure that the flag foreign_keys is ON by executing:

PRAGMA foreign_keys = ON;

and get the graduate students from this table with a join to student:

select s.* 
from student s inner join advisory a
on s.net_id = a.student_id

Upvotes: 1

Related Questions