Functional Dependency, Normalization and Relational Algebra

1.Complete the following:

Review the table below.

Show functional dependencies in the table.

Decompose the table until it is in 3^rd normal form.

Underline attributes denotes table key.

Review Student Course Database if needed.

Table

Student_Grade

Stno	Stud_name	Major	Section_ID	Instructor	Semester_Year	Grade

The Student Course Database

Student

stno NOT NULL NUMBER (3)

PRIMARY KEY NOT NULL

sname VARCHAR2(20)

major CHAR(4)

class NUMBER(1)

bdate DATE

Grade_Report

student_number NOT NULL NUMBER(3)

section_id NOT NULL NUMBER(6)

grade CHAR(1)

PRIMARY KEY (student_number, section_id)

Section

section_id NOT NULL NUMBER(6)

PRIMARY KEY NOT NULL

course_num CHAR(8)

semester VARCHAR2(6)

year CHAR(2)

instructor CHAR(10)

bldg. NUMBER(3)

room NUMBER(3)

Department_to_Major

dcode NOT NULL CHAR(4)

PRIMARY KEY NOT NULL

dname CHAR(20)

Course

course_name CHAR(20)

course_number NOT NULL CHAR(8)

PRIMARY KEY NOT NULL

credit_hours NUMBER(2)

offering_dept CHAR(4)

Room

bldg NOT NULL NUMBER(3)

room NOT NULL NUMBER(3)

capacity NUMBER(4)

ohead CHAR(1)

PRIMARY KEY (bldg., room)

Prereq

course_number CHAR(8)

prereq CHAR(8)

PRIMARY KEY (course_number,prereq)