COMP9315 Sample Exam
The University of New South Wales
COMP9315 DBMS Implementation
Sample Exam
DBMS Implementation
[Instructions] [Notes] [PostgreSQL] [C]
[Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8]
Question 2 (10 marks)
Consider the following SQL statements to create a new data type and table:
create type Gender as enum (‘male’, ‘female’, ‘none of your business’);
create table People (
id integer primary key,
name text not null,
sex Gender — may be null
);
Show the tuples that would be inserted into the system catalog tables
a. when the Gender type is created
b. when the People table is created
Since there are many catalog tables and many columns in most of the catalog tables, you only need to give values for the tables and columns shown below:
pg_attribute(oid, attrelid, attname, atttypid, attlen, attnum, attnotnull)
pg_class(oid, relname, relkind, relnatts, relhaspkey, relhasindex)
pg_enum(enumtypid, enumlabel)
pg_index(indexrelid, indrelid, indnatts, indisunique, indisprimary)
pg_type(oid, typname, typtype, typisdefined, typrelid)
Some tuples will contain foreign keys that are OIDs in other tables; indicate OID values by distinct upper-case letters (e.g. A, B, etc.) You may assume that the OID values for the builtin data types integer and text are defined as follows:
X = select oid from pg_type where typname=’int4′
Y = select oid from pg_type where typname=’text’
Note that some of the field values in some tuples may be NULL.
The PostgreSQL catalog tables are described in detail in Chapter 48 of the PostgreSQL documentation. Notes: variable-length types and composite types have typlen = -1; enumerated types have typlen = 4; boolean values may be written as true and false; primary key indexes are always called table_pkey; 0 is not the same value as NULL.
Instructions:
• Type your answer to this question into the file called q2.txt
• Submit via: give cs9315 sample_q2 q2.txt
or via: Webcms3 > exams > Sample Exam > Submit Q2 > Make Submission
End of Question