COMP9315 Final Exam The University of New South Wales
COMP9315 DBMS Implementation
Final Exam
DBMS Implementation
[Instructions] [Notes] [PostgreSQL] [C]
[Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8]

Question 3 (6 marks)

Consider the following SQL statements to create three tables:

create table Students (
    id      integer primary key,
    name    text not null
);

create table Courses (
    id      integer primary key,
    code    char(8),
    title   text not null
);

create table Enrolments (
    stude   integer references Students(id),
    course  integer references Courses(id),
    mark    integer check (mark between 0 and 100),
    grade   char(2) check (grade in ('FL','PS','CR','DN','HD')),
    primary key (stude,course)
);

Consider also that you insert 1000 tuples into each of the Students and Courses tables, and insert exactly 4 Enrolments tuples for each student.

The pg_class catalog table has a field reltuples that indicates the total number of tuples stored in the pages of the relation. This includes tuples that have been deleted, but not yet vacuum'd.

For each parts (a), (b) and (c), what values will be produced from the queries:

select count(*) from Enrolments;
select reltuples from pg_class where relname='enrolments';
  1. after all the tuples are initially inserted (as above)

  2. after the SQL statement delete from Enrolments where stude = 1234567;
    (but before any vaccuuming)

  3. after the SQL statement delete from Enrolments; (but before any vaccuuming)

  4. if the SQL statement drop table Enrolments; is executed, which of the following tables in the PostgreSQL catalog will be modified:

    pg_aggregate,  pg_attrdef,  pg_attribute,  pg_class,  pg_collation,
    pg_constraint,  pg_conversion,  pg_database,  pg_depend,  pg_enum,
    pg_foreign_table,  pg_index,  pg_proc,  pg_range,  pg_rewrite,
    pg_sequence,  pg_transform,  pg_tablespace,  pg_trigger,  pg_type
    

    Note: incorrect table names are penalised; just writing them all scores zero.

The PostgreSQL catalog tables are described in detail in Chapter 48 of the PostgreSQL documentation.

Instructions:

End of Question