COMP9315 Final Exam |
The University of New South Wales COMP9315 DBMS Implementation Final Exam |
DBMS Implementation |
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';
after all the tuples are initially inserted (as above)
after the SQL statement
delete from Enrolments where stude = 1234567;
(but before any vaccuuming)
after the SQL statement delete from Enrolments; (but before any vaccuuming)
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: