Tutorial 1

COMP207 Tutorials 2021-10-07

This tutorial is focused on the tutorial 1 exercises which can be found here.

Part 1

1. Schemas:

    Students(first_name, last_name, birthday, s_id)
    Enrolments(s_id, course_code, year)
   

2. Students - 4 columns by 5 rows

   Enrolments - 3 columns by 9 rows

3. Creating tables:

    CREATE TABLE Students {
        first_name VARCHAR(20),
        last_name VARCHAR(20),
        birthday DATE,
        s_id INT,
        CONSTRAINT PK_Students PRIMARY KEY(s_id)
    };
   

    CREATE TABLE Enrollments {
        s_id INT,
        course_code CHAR(7),
        year INT
        CONSTRAINT FK_Enrollents FOREIGN KEY(s_id) REFERENCES Employees(s_id)
    };
   

4. Deleting enrolment:

    DELETE FROM Enrolments WHERE s_id=4 AND course_code='COMP201';
   

5. Deleting column:

    ALTER TABLE Students DROP COLUMN birthday;
   

6. Changing names:

    UPDATE Students SET first_name='Leah' WHERE s_id=2;
   

Part 2

1. Works
2. Fails - Longer than 20 characters.
3. Fails - Identical s_id.
4. Fails - No parent in Students.
5. Works
6. Fails - No primary key (s_id) set.
7. Fails - Missing VALUES command.
8. Fails - Incorrect attribute names.
9. Fails - Orphans left in Enrollments.
10. Fails - No parent in Students.
11. Fails - Orphans left in Enrollments.
12. Fails - Deletes all entries leaving orphans in Enrollments.
13. Works
14. Works - Deletes all entries