2. Data creation

Create some appropriate data for the database (at least 6 tuples for each table) and

insert this data in your implementation. Choose you data properly so that the

following queries return at least a relation with one tuple.

3. SQL Queries

Design and implement MySQL statement for the following queries:

1.

List all the students

2.

List all the courses

3.

List all the lecturers

4.

What students take course CS04?

5.

Which lecturer teaches course CS04?

6.

What courses does Dr. Dodd teach?

7.

Are there any students not enrolled in any course and who are they?

8.

Are there any courses with no students enrolled and what are they?

9.

Delete a tuple from table student, check the results.

10.

Update the value of course number in table course, check the results.

4. What to submit?

Submit the list of SQL-queries, query-output, and the list of tuples of all relations in the database.

Objectives On completion of this experiment the student should be able to: Design a set of relations corresponding to an ER model 2. 1. Implement these relations using SQL and insert test data 3 Formulate and submit sQL queries 4. Log the results 5. Check to see whether the results are correct 1. Database definition Transform the following ER diagram into a suitable set of relations and implement these using MySQL. Specify any primary and foreign keys in your definition. Specify referential integrity constraints for foreign keys for both deletion and update student-name course-number title student-number year student enrols course lecturer teaches staff-number room-number lecturer-name Objectives On completion of this experiment the student should be able to: Design a set of relations corresponding to an ER model 2. 1. Implement these relations using SQL and insert test data 3 Formulate and submit sQL queries 4. Log the results 5. Check to see whether the results are correct 1. Database definition Transform the following ER diagram into a suitable set of relations and implement these using MySQL. Specify any primary and foreign keys in your definition. Specify referential integrity constraints for foreign keys for both deletion and update student-name course-number title student-number year student enrols course lecturer teaches staff-number room-number lecturer-name