I. Specification for assignment:

The following is sample of relations for a course enrollment database in a college:

Student (Student_ID, Student_first name, Student_last name, Major, Registered year)

Course (Course_ID, Course_title, Course_unit, Lecturer_ID)

Lecturer (Lecturer_ID, Lecturer_first name, Lecturer_last name, Dept_ID)

Enrollment (Enrollment_ID, Course_ID, Student_ID, maximum_no)

A high-level description of the project:

Each student has a unique student ID and a profile (first and last names, major and the year the student is registered in the college).

Each lecturer has a unique staff ID and a profile. The profile indicates first and last name and department where the lecturer works.

Each course has a course ID, course title, credit unit and staff ID.

Each course is limited to only 30 students per enrollment

Answer the following questions:

1. (i) Based on the above relations, create one transaction when a student wants to enroll in a course (e.g., CMT321 (Management and Engineering of Database), 3 units)

(ii) What are the database operations and non-database operations that should be included in this transaction? State the operations.

2. Explain which property/properties that should be fulfilled by this transaction in order to maintain a consistent database.

3. Assume another student also wants to enroll in the same course and you are required to create another transaction for this purpose.

(i) Produce a serial schedule by combining transaction that you have created in Q1(i) and Q3.

(ii) Produce a non-serial concurrent schedule with interleaved operations based on the schedule that you have created in Q3.

(iii) Test the schedule in Q3(ii) whether it is serializable or not. If it is serializable, state the equivalent serial schedule