The relational schema below defines an operational system of a university Bold and Capital = Name of Entity Underlined attribute(s) primary key(s) Asteriskede attribute(s) = foreign key(s) 1 m COURSE: course id, dept_id*, level_offered, pre-requisites, units, STUDENT: student id, name, standing, major, units_completed 1 ENROLLMENT: course id, student id, faculty id, quarter, m m 1 FACULTY: faculty id, name, rank, 1 DEPARTMENT: dept_id, name, Hod_id, majors offered Answer the following questions (1-6) based on the above schema: Answer the following questions (1-6) based on the above schema: 1. Is the schema in 3NF? If not, identify the normal form(s) and their violations. Normalize the schema to remove these violation(s). 2. Create a star schema for the information showing all dimension tables, fact tables, attributes, and the relationships between the tables. Identify key attributes and relationship cardinalities. 3. How many new records will get added to the enrollment table every year (4 quarters) if a student takes on average 3 courses per quarter? Assume there are 200 active students, 50 active faculty members, and 200 courses offered in the year. 4. Suppose faculty XYZ gets promoted to the rank of 'associate professor. What type of update is this? How would you update the faculty dimension table? Show the new dimension table and its attributes. 5. How many joins (based on the star schema) would be needed to answer the query: what is the average enrollment in courses offered by faculty ABC? Explain briefly. The relational schema below defines an operational system of a university Bold and Capital = Name of Entity Underlined attribute(s) primary key(s) Asteriskede attribute(s) = foreign key(s) 1 m COURSE: course id, dept_id*, level_offered, pre-requisites, units, STUDENT: student id, name, standing, major, units_completed 1 ENROLLMENT: course id, student id, faculty id, quarter, m m 1 FACULTY: faculty id, name, rank, 1 DEPARTMENT: dept_id, name, Hod_id, majors offered Answer the following questions (1-6) based on the above schema: Answer the following questions (1-6) based on the above schema: 1. Is the schema in 3NF? If not, identify the normal form(s) and their violations. Normalize the schema to remove these violation(s). 2. Create a star schema for the information showing all dimension tables, fact tables, attributes, and the relationships between the tables. Identify key attributes and relationship cardinalities. 3. How many new records will get added to the enrollment table every year (4 quarters) if a student takes on average 3 courses per quarter? Assume there are 200 active students, 50 active faculty members, and 200 courses offered in the year. 4. Suppose faculty XYZ gets promoted to the rank of 'associate professor. What type of update is this? How would you update the faculty dimension table? Show the new dimension table and its attributes. 5. How many joins (based on the star schema) would be needed to answer the query: what is the average enrollment in courses offered by faculty ABC? Explain briefly