[10 pts] What is data normalization?

[10 pts] What is the main purpose of data de-normalization?

[10 pts] What are the categories of data models? Explain them briefly.

[15 pts] What is data independence?

[30 pts] Using the Figure 1, write the following queries in SQL:

classroom(room_number, building, capacity) department(dept_name, building, budget) course(course_id, title, dept_name, credits)

instructor(inst_id, fname, mname, lname, dept_name, salary)

section(sec_id, course_id, room_number, semester, year, building, time_slot_id) teaches(inst_id,course_id, sec_id, semester, year) student(stud_id, name, dept_name, tot_cred) takes(stud_id, course_id, sec_id, semester, year, grade)

Figure 1 Schema of the university database. The underlined attributes are primary and/or foreign keys.

Display a list of all instructors, showing their id, first name, last name and their salary. [4 pts]

Display a list of all instructors, showing their id, first name, last name and their salary, whose salary is greater than $100,000. [4 pts]

Display a list of all instructors and the course(s) they teach (showing their id, first name, last name, course id). [4 pts]

Display a list of all instructors and the course(s) they teach (showing their id, first name, last name, course id, course name). [4 pts]

For the instructor whose name is John Doe and id is 123456789, display a list of all his course(s) he teaches and the budget of the department he works at (showing instructor id, first name, last name, course id, course name, department name and department budget). [4 pts]

Display the first names of all instructors who have taught any Computer Science course(s) ever (there should be no duplicates first names). [5 pts]

The salary of the instructor John Doe (id: 123456789) is increased 25%. Update the student database accordingly. [5 pts]

[15 pts] Construct an ER diagram of a hospital system consisting of doctors, patients, insurance, visits, and tests. A patient can have a single insurance and multiple visits. A doctor can have multiple patient visits and examine various tests. Each patient visit has a log of the various tests conducted by a doctor.

A patient should have a unique id, ssn, first name, last name, and insurance. An insurance consists of a unique insurance number, and insurance name. A doctor has an employee number, first name, last name, and date of the job started. A visit includes visit number of the patient, date of the visit and date of the admission. A test consists of a unique test number and test name.

Note: You can draw the diagram on a piece of paper, take a screenshot and put it here.

[10 pts] Remember that an ER diagram shows mostly the conceptual high-level data model of the database. How would the ER diagram of the hospital system (Question 6) look like in the physical database? List the tables for each entity and/or relationship in your ER diagram. For example, for a patient you can write as following:

Table patient

patient(patient_id, ssn, first_name, )

Note: Make sure that the tables contain primary/foreign/unique keys as well. You can show

primary keys bold, foreign keys underlined, and unique keys italic underlined.