Question $1$:

Based on the following information, draw an ER model in the $3$rd Normal form using Visio. Copy and paste your ER Model from Visio to your MS Word answer document.

A Painter might paint many Paintings. To be considered a Painter, the Painter must have painted at least one Painting. Each Painting is Painted by one $($and only one$)$ Painter. A Painting might $($or might not$)$ be exhibited in a Gallery; that is$,$ Gallery is an optional entity to the Painting entity. An $$Art Connoisseur$$ may buy multiple paintings or may not buy any. Further, the Gallery is managed by several Managers while a Manager is in$-$charge of multiple Galleries. Note: For each entity, include at least $3$ attributes.

Based on $3$rd Normal Form of your ER Model, implement the Database Model in the SQL Server $($SRV$-$BI$-$SQL$)$ using MSSQL DDL commands. In your response, provide the SQL code you have used to implement the model. Include at least $3$ rows of relevant data for each table using appropriate SQL commands. Provide screenshots of all tables in your Database Model $($with data$).(75$ points$)$

Question $2$:

Design an ER Model in the $3$rd Normal Form for a University Management System. The system should track information about students, courses, professors, departments, and enrollments. Copy and paste your ER Model from Visio to your MS Word answer document

Based on this ER model in the $3$rd Normal Form above, develop a database model using DDL commands in MSSQL and implement it in the SQL Server $($SRV$-$BI$-$SQL$).$ Then using the data provided for each table $($see below$),$ insert the corresponding data into each table of your database. Provide the code and the screenshots of all tables with the data in your MS Word answer document.

For the queries below provide screenshots along with the code in your MS word response document

a$.$ List the students who have not enrolled in any course. Include their names and student IDs. $(5$ pts$)$

b$.$ Find out the average grade for each course. Display the course name and its average grade. $(5$ pts$)$

c$.$ Write a query to find the students who have not enrolled in any course offered by Professor Dr$.$ Smith. Display the student name and ID$.(5$ pts$)$

d$.$ Write a query to find the students who have the highest grade in each department. Display the student name, department name, and the grade. If there are multiple students with the highest grade in a department, include all of them. $(5$ pts$)$

e$.$ Write a query to list the courses that have at least one student with a grade of $\text{'}$A$\text{'}$ and at least one student with a grade of $\text{'}$B$\text{'}.$ Display the course name and the department name. $(5$ pts$)$

f$.$ Find the students who have the highest grade in each course. Display the student name, course name, and the grade. $(5$pts$)$

g$.$ Write a query to display the courses with enrollment counts greater than the average enrollment count. Display the course name and the number of students enrolled in each course. $(5$ pts$)$

Following are the data corresponding to the tables in the Database.Following are the data corresponding to the tables in the Database.

STUDENT

COURSE:

PROFESSOR:DEPARTMENT:

ENROLLMENT:Following are the data corresponding to the tables in the Database.

STUDENT

COURSE:

PROFESSOR:

DEPARTMENT:

ENROLLMENT: