(Oracle) SQL Looking for primary and foreign keys in this problem. How do I set

Question

(Oracle) SQL

Looking for primary and foreign keys in this problem.

How do I set this u?

Q 4. College.com has hired you to design the database for them They currently have three tables

FACULTY1, CLASS1 and GRADE1

FACULTY1 (F_Num, F_Name, Rank, D_Name, Salary)

Where:

F_NUM is faculty number

F_Name is name of faculty

Rank is their rank (lecturer, assistant, associate or professor)

D_Name is the name of department they work for

salary is their annual salary

CLASS1   (C_Name, C_Time, F_Name, C_Room, Semester)

where C_Name is the name of class

C_time is time class is held

F_Name is the name of faculty teaching that class

C_Room is the room class is held

Semester is the semester class is offered

GRADE1 (S_Name, C_Name, Grade)

where grade is student’s grade for a given class

(A sample data is shown on the last page)

Explanation / Answer

As per the given tables, the following methods are there to create are incorporating the constraints within the structure of table creation or after the creation of table. Both are described below.

Below the creation of table:

FACULTY1 (F_Num, F_Name, Rank, D_Name, Salary)

CREATE TABLE FACULTY1
(F_Num integer PRIMARY KEY,
F_Name varchar(20) UNIQUE,
Rank integer,
D_Name varchar(20),
Salary integer);

.....
.....
.....

CLASS1 (C_Name, C_Time, F_Name, C_Room, Semester)

To make F_Name as a FK(Foreign Key), we have the following codes. :

CREATE TABLE CLASS1
(C_Name varchar(20) UNIQUE,
C_Time time,
F_Name varchar(20) REFERENCES FACULTY1(F_Name),
C_Room integer,
Semester integer);

if its mysql rather than oracle sql, the above code would work with certain modification like:

CREATE TABLE CLASS1
(C_Name varchar(20) UNIQUE,
C_Time time,
F_Name varchar(20),
C_Room integer,
Semester integer
FOREIGN KEY(F_NAME) REFERENCES FACULTY1(F_Name));

. . .
or in case you want to add constraint later on after creating the table, you can see the below code.:

Alter table CLASS1
ADD CONSTRAINT fk1 FOREIGN KEY (F_Name) REFERENCES FACULTY1(F_Name)

.....
.....
.....

GRADE1 (S_Name, C_Name, Grade)

Similarly the same thing can be done on the below table of Grade1

CREATE TABLE GRADE1
(S_Name varchar(20),
C_Name varchar(20) REFERENCES CLASS1(C_Name),
Grade integer);

or

Alter table GRADE1
ADD CONSTRAINT fk2 FOREIGN KEY (C_Name) REFERENCES CLASS1(C_Name);

:::

There is one thing I would like to point out. To have a foreign key in any table, the key has to be primary key or unique in the table from where it is refereanced like for example F_Name is mentioned unique in the first table, to have it as FK in the Class 1 table and same goes for the second table.

Also constraint of FK in the second table can be done only after the entry or key refereanced in the first table is unique or primary key. Like for example if we make no constraint of unique on F_Name and try to make F_Name in second table as a unique key, that would not be possible. You need to first add constraint which make F_name in table faculty as unique or PK and then make it FK in the class1 table

Adding to this, a better option would be to make the table structure like this

FACULTY1 (F_Num, F_Name, Rank, D_Name, Salary)
CLASS1 (C_Name, C_Time, F_Num, C_Room, Semester)

Here in the suggested structure of tables, Fnum is used as a fk in second key instead of first. Since two faculty can have same name but not same numbers, hence if F_Num is used, no unique constraint would be required for F_Name entry in the Faculty Table.

....

Good Luck!

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.