Data Model: We need to store information about university management system; The university keeps track of each National ID number students name, student number, current address and phone, address and phone, birthdate, gender, level , major department, minor department, The name of the student should have first, middle, and last names. The address should have apartment number, street number, city, state, postal code. Both National ID number and student number must be unique for each student.

Each college has college number and college name

Each department must be described by a name, department code and phones. Code must be unique for each department.

Each course has a course name, description, course number, number of credit hours and level number,. The course number should be unique for each course.

Each section has an instructor name, semester, year, section number. The section number must be unique and each course can have multiple sections taught during same semester/year.

A teacher has teacher id ,name (first_name,last_name ) ,email and salary

Other requirements are as follows.

1- Every colleges has many departments.

2- Each student belongs to a major department.

3- Each department offers many course and each course offers by many department

4- Each section may be part of multiple courses. Sections are only identified through the course offering them

5- Each student can be enrolled in an arbitrary number of courses, and an arbitrary number of students can be enrolled in a course.

6- Each teacher teaches many course.

7- Each teacher work for one department.

8- Each teacher manages one department.

Implementation requirement: Considering the following queries, first finalize the schema (by adding required relations and/or changing the above relations) and then implement your relations. Next, populate your relations with appropriate data. Make sure each table in the database has enough number of tuples so that each query gets a meaningful and reasonable size output. For this, you should store at least 3 colleges 5 department for each colleges, 5 section for each department, 5 subject for each department5 students for each department and 5 teachers.

Finally, implement the following queries in SQL.

The queries are as follows.

1- Display all colleges college name in which the third letter of the name is m"

2- For each colleges find college number and number of department.

3- For each department find first_name and maximum salary for all teachers when the maximum s salary greater than 5000

4- Find the course name of courses in the art department that have credits 3

5- For each course, find course name and number of student that have number of student more than 5.

6- Write a query to display the last name, department number, and department name for all teachers work in deprment_name NEWT WORK

7- Create query to displays the department number, last name of teacher , and email for every teacher in the IS department

What you should hand in: You should print and submit a report that includes

(1) The E/R model for your database design together with reasonable assumption(s) made,

(2) List of all schemas and their attributes with appropriate data type. Also, identify the primary key for each schema together with the relationship between different schemas.

(3) The print out of tuples in each table, the SQL queries and the output of your queries against your database.