Please do all parts completely

(60 POINTS) Following is a database state for a University database.

A.) (15 POINTS) Create this database. You may use any relational database system MySQL. Please provide the create table AND all data population (insert) statements (either attached as an .sql file or as an inline text) in your answer document file.

B.) (5 POINTS) A screenshot of the content of each file in your database environment, i.e. STUDENT, FACULTY, OFFERING, COURSE, and ENROLLMENT

C.) (5 POINTS) A screenshot of the database diagram that shows all tables, their PKs and FKs, and all relationships among tables. Please draw this by either using the draw database diagram feature of your database system

D.) (5 POINTS) Test that the referential integrity constraint 3 (between the ENROLLMENT table and the OFFERING table) is satisfied properly by your design. Please provide

supporting screenshots of your test. IMPORTANT: Please remember to restore your database state to its original after this test by

reinserting any tuples that you have removed.

E.) (30 POINTS) Please provide the following for each of the 5 queries below:

1. Relational Algebra expression

2. SQL statement

3. The screenshot of the resulting table when you execute the SQL statement

Query1. Increment the salaries of Faculty by 20% who started working at the University after

2009. Display faculty with his Fname, Lname, start date and the NEW (incremented) salary

fields.

Query2: Display the OfferNo and CourseNo of Summer2012 courses which has no faculty

assigned.

Query3: Display each student major and average GPA for each student major.

Query4: Display in ascending order of GPA the firstname, lastname, and GPA of each senior

student.

Query5: Display the Faculty firstname and lastname, OfferNo, and CourseNo of the IT

courses that were taught by Asst. instructors in Fall 2012.

STUDENT StdSSN StdFName Std LName StdCity StdState Stdzip StdMajor Stdyear StdGPA 123456791 Hanes Wilson Savon OK 68123-1113 IT Freshman 3.10 Nuke Burwick OK 68013-2123 FIL Junior 2.80 124567892 Blake Keller Thomasville OK 234567892 Claire 69044-3323 ART Junior 345678903 Wayne Keller Savon OK 68125-1143 IT enior 2.90 OK 68123-2335 FIL Senior 3.30 456789014 Josh art Savon 567890125 Maggie Diamond Savon OK 68116-0023 IT Junior 370 678901236 Tammy Diamond Richmond OK 68118-2346 ART Sophomore 3.40 OK 68123-2214 FIL. Junior 2.60 789012347 Richard Moore Savon OK 68116-1334 IT Senior 4.00 876543212 Chris Coleman Savon 2.30 890123458 Lance Brave Savon OK 98118-0023 IT Senior 98115-1887 IT Burwick OK 901234569 Winston Pale Sophomore