Suppose we have a table and assumptions as follows: (every attribute is atomic)

Question

Suppose we have a table and assumptions as follows: (every attribute is atomic)

SEMINARS (Seminar#, Title, Room, Seminar_Date, Seminar_fee, Instructor#, Instructor_Name, Honorarium, Student#, Student_name, Regsitration_date, Registration fee)

- A seminar has a fixed title and fee.

- Each seminar is always held in the same room, regardless of days offered

- A seminar is one-day long, but held many times over the period.

- Each seminar is always taught by the same instructor.

- One instructor may teach multiple seminars and receive a different honorarium for different seminars.

- A student can register a particular seminar that is held in a particular date.

- If you register 30 days early for a particular seminar, you pay an early-bird fee which is 20% cheaper than a regular registration fee.

- A student can register multiple seminars at different dates.

List all meaningful, direct, and non-trivial functional dependencies, 2NF, 3NF

Explanation / Answer

The given table is,

SEMINARS (Seminar#, Title, Room, Seminar_Date, Seminar_fee, Instructor#, Instructor_Name, Honorarium, Student#, Student_name, Regsitration_date, Registration fee)

Candidate key of the tabe : Seminar#

Functional dependencies are as follows,

Seminar# -> Title, Room, Seminar_Date, Seminar_fee, Instructor#, Student#

Instructor# -> Instructor_Name, Honorarium

Student# -> Student_name, Regsitration_date, Registration fee

Prime attribute : The attributes which are the members of candidate key.

Non-prime attribute : The attributes which are not the members of candidate key.

                      Prime attribute :   Seminar#

1st Normal form

Rule : All attribute should have atomic values.

2nd Normal form

   Rule : the table must be in 1st normal form and there should not be any partial dependency between attributes. That is every non-prime attribute must be fully dependent on candidate key.

Here there is no partial dependency between attributes. Because candidate key is Seminar#. There is only one attribute as candidate key. Only first dependency is dependent on this candidate key which is not a partial dependency. Other dependencies are not determined by prime attributes so there is no relevance for partial dependency.

Seminar# -> Title, Room, Seminar_Date, Seminar_fee, Instructor#, Student#

3rd Normal form

Rule : the table must be 2nd normal form and for a non-trivial functional dependency X -> Y , it should satisfy either of the following two conditions,

                                   Now we are decomposing the main table into sub tables based on the dependency to make it in 3rd normal form. Decomposed tables are,

Seminar#

Title

Room

Seminar_Date

Seminar_Fee

Instructor#

Student#

Dependency of this table is =>    

Seminar# -> ( Title, Room, Seminar_Date, Seminar_fee, Instructor#, Student#   

Instructor#

Instructor_Name

Honorarium

Dependency of this table is =>     Instructor# -> ( Instructor_Name, Honorarium )           

Instructor# is the candidate key.so it is also super key. Since this table satisfies the rule of 3rd NF.    

Student#

Student_name

Registration_Date

Registration fee

Dependency of this table is =>    

          Student# -> (Student_name, Regsitration_date, Registration fee ).

Here Student#   is the candidate key.so it is also super key. Since this table satisfies the rule of 3rd NF.    

Now we got 3 tables instead of the main table which are in 3rd normal form.

Seminar#

Title

Room

Seminar_Date

Seminar_Fee

Instructor#

Student#

Instructor#

Instructor_Name

Honorarium

Student#

Student_name

Registration_date

Registration fee

***********END***************PLS GIVE ME GOOD RATING*********************************

Seminar#

Title

Room

Seminar_Date

Seminar_Fee

Instructor#

Student#

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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