Week6 - Need of Normalization and First, Second, Third Normal Forms / Analyze

The questions can come in any order, so make sure you are selecting right option for all questions.

1. Consider the table as follows below.

STUD_NO	COURSE_NO	COURSE_FEE
1	C1	1000
2	C2	1500
1	C4	2000
4	C3	1000
4	C1	1000
2	C5	2000

Answer the following questions:

Question 1
Can COURSE_FEE attribute alone decide the value of COURSE_NO or STUD_NO. State True or False

(b) FALSE

Question 2
Can COURSE_FEE together with STUD_NO can decide the value of COURSE_NO. State True or False

(b) FALSE

Question 3
COURSE_FEE together with COURSE_NO cannot decide the value of STUD_NO. State True or False

(a) TRUE

Question 4
Candidate key of this relation will be:

(a) {STUD_NO, COURSE_NO}

Question 5
COURSE_FEE would be a non-prime attribute. State True or False

(a) TRUE

Question 6
Is COURSE_FEE is dependent on COURSE_NO. State True or False

(a) TRUE

Question 7
Is COURSE_NO is proper subset of the candidate key. State True or False

(a) TRUE

Question 8
This relation is not in 2NF. Why?

(a) COURSE_FEE is dependent on COURSE_NO, which is a proper subset of the candidate key.
(c) Non-prime attribute COURSE_FEE is dependent on a proper subset of the candidate key, which is a partial dependency and so this relation is not in 2NF.

Question 9
To convert the above relation to 2NF, we need to split the table into ____ tables.

(a) 2

Question 10
After normalizing the table to 2NF by splitting the table and avoiding redundancy, what will the resulting tables look like?

(a)
Table 1: (STUD_NO, COURSE_NO)
Table 2: (COURSE_NO, COURSE_FEE)

2. Suppose we have a table named "Employees" with the following structure:

Employee ID	Name	Skills	Department
101	John Doe	Java, SQL, Python	IT
103	Jane Smith	HTML, CSS, JavaScript, React	Marketing
102	John Doe	Python, R, Machine Learning Science	Data

Answer the following questions

Question 1
This table violates 1NF because:

(b) The "Skills" column contains multiple values (multiple skills) in the same cell.

Question 2
What is the first step to normalize the "Employees" table into 1NF?

(c) Separate each skill into individual rows

Question 3
After normalizing the "Employees" table to 1NF by separating each skill into individual rows, what will the resulting table look like?

(b)
Employee ID     Name        Skills              Department
101             John Doe    Java                IT
101             John Doe    SQL                 IT
101             John Doe    Python              IT
103             Jane Smith  HTML                Marketing
103             Jane Smith  CSS                 Marketing
103             Jane Smith  JavaScript          Marketing
103             Jane Smith  React               Marketing
102             John Doe    Python              Data Science
102             John Doe    R                   Data Science
102             John Doe    Machine Learning    Data Science

Question 4
After normalizing the "Employees" table to 1NF by splitting the table and avoiding redundancy, what will the resulting tables look like?

(a)
Table 1: Employee(Employee ID,Name,Department)
Table 2: Employee_Skill(Employee ID,Skill)

3. Suppose we have a table named "Books" with the following structure:

Book ID	Title	Author	Genre
1	The Great Gatsby	F. Scott Fitzgerald, Orwell	Fiction, Dystopian
2	Pride and Prejudice	Jane Austen	Romance, Thriller
3	The Catcher in the Rye	J.D. Salinger	Fiction, Suspense

Answer the following questions

Question 1
This table violates 1NF because:

(c) Both a and b

Question 2
What is the first step to normalize the "Books" table into 1NF?

(a) Separate authors into individual rows

Question 3
Following the first step, what is the final step to achieve 1NF for the "Books" table?

(c) Separate genres into individual rows

4. Suppose we have the following structure for the "Orders" table:

Order ID	Customer Name	Order Details
1001	John Doe	Laptop, Monitor, Mouse, Keyboard
1004	Jane Smith	Smartphone, Tablet, Headphones
1003	Alice Brown	Chair, Desk, Lamp, Bookshelf, Coffee Table
1002	Alice Brown	NULL

Answer the following questions

Question 1
This table violates 1NF because:

(b) The "Order Details" column contains multiple values (multiple order details) in the same cell.

Question 2
What is the first step to normalize the "Orders" table into 1NF?

(c) Separate each item in the Order Details into individual rows