What should be the primary key in these two tables? (SQL)?

Question

For the SQL tables:
create table courses (
course_dept char(3),
course_no char(3),
course_name char(30),
course_credits integer,
course_room char(7),
course_days char(4),
course_times char(5),
course_fac_no integer,
course_desc text );


create table faculty (
fac_no integer,
fac_lastname char(20),
fac_firstname char(15),
fac_title char(6),
fac_room char(7),
fac_off_days char(4),
fac_off_times char(5),
fac_phone char(4)
fac_email char(30) );

What should be the primary key of each be? Why?

Answer 1

Primary keys should be (must be) unique. So it's course_no in the courses table, and fac_no in the faculty table. (The latter is given by its use in the course table.) think about all the otheer variables; they could all have more than one occurence -- for example course_room would be the same for every course held in that room.

Answer 2

Primary Key selection:

The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person).



So, select only keys that are UNIQUE.



courses table:

course_no char(3) is the primary key



Although there is a certain temptation to use course_name char(30) its should be avoided. Consider what if the course name is changed due to curriculum?



faculty table:

fac_no integer is the best option



fac_lastname or fact_firstname are NOT primary keys. People can have same names.

Answer 3

For the course table, you may want to make the key a combination of course_dept and course_no, if you DBMS allows it. I say this because, at my old university, the course number by itself did nothing to tell what the course was. The courses were always listed with the department to explain what the course was. CS160 would be a computer science class and PSY160 would be a psychology course. Without the CS or PSY department prefix, the 160 course number would be useless in identifying the course because several departments could have courses with the 160 number. But if things are different at your school, then perhaps course_no is all you will need.



For the faculty, the fac_no is your best bet.

Answer 4

For the first one I would make course_no the primary key and for the second one I would make fac_no the primary key. I imagine that they would be ID numbers (e.g. the course I'm doing has an ID of 3633) and would also be unique - all courses and faculties would need a different number.



Also in your first table, I would change course_fac_no to fac_no and make that a foreign key linking both tables together.

Answer 5

For 'faculty', almost certainly fac_no. For 'courses', depends on whether course_no is unique over all departments or just within one. If the former, just course_no; if the latter, a compound PK with (course_dept, course_no)

Answer 6

most likley the one that is integer or suto increment 99.10% of the time

Answer 7

Whichever one is unique.