n this lab, you will use SQL to insert, update, and retrieve data from your UNIVERSITY database.

Error Handling Guidelines

1. On encountering an error, go to the line where the error occurred, read the error, understand what it means and try to fix it.
2. If the error message is not clear about what the error is, examine the error line and the two lines above it. Check for any syntax errors. You can always refer to the course slides or any online SQL resource to double check the correct syntax for the different statements.
3. If you are still unable to resolve the error, copy the error message in the search bar and try to learn more about this error, what it means, and how other developers were able to resolve it.

Part I: Inserting Data into your University Database

Using your downloaded DBMS (MS SQL Server or MySQL), write SQL queries that inserts at least three rows in each of the following tables

Department, Instructor, Course

( Use your own data for each of the following requirements)

1. Write SQL statements to update one row in each table.
2. Execute the statements and make sure they run correctly without violating any integrity constraints.

Submission

Submit a single Word document that contains each query, its results, and screen shots of your query & its results in the DBMS.

University data base

1. Listing the entities:

The given instructions says that system will have below entities:

a) College

b) Department

c) Office

d) Instructor

e) Courses

2. Constructing schema by adding attributes

The entities will have below attributes:

1. College College(collegeID, college_name, established_date)

b) Department(dept_name)

c) Office(office_name, size)

d) Instructor(instructor_id, name): added instructor_id as names can be same for 2 or more instructors

d) Courses(course_code, name): added appropriate attributes, as not given in description

3. Identify primary key:

The identified primary key in each entity is as below(bold and underlined):

a) College(collegeID, college_name, established_date)

b) Department(dept_name)

c) Office(office_name, size)

d) Instructor(instructor_id, name)

e) Courses(course_code, name)

4. Listing relationship:

a) Department must belong to exactly one college, a college has many departments. Thus department must have a foreign key to its college.

b) Department has an office, thus department will store the foreign key to its office also.

c) Department has many instructors, but an instructor has one department, thus instructor will have foreign key to its department.

d) Course is taught by many instructors and an instructor teaches many courses. It is Many to Many relationship to be stored in new table.

5. Adding foreign keys:

The entities will have below foreign keys marked as italic:

a) College(collegeID, college_name, established_date)

b) Office(office_name, size)

c) Department(dept_name, office_name, collegeID)

d) Instructor(instructor_id, name, dept_name)

e) Courses(course_code, name)

f) CurseInstructor(course_code, instructor_id)