COMP9315 21T1
Exercises 01
DBMSs, PostgreSQL, Catalogs
DBMS Implementation
[Show with no answers] [Show with all answers]
Some of these questions require you to look beyond the Week 01 lecture material for answers. Some of the questions preempt material that we’ll be looking at over the next few weeks. To answer some questions, you may need to look at the PostgreSQL documentation or at the texts for the course … or, of course, you could simply reveal the answers, but where’s the fun in that?
1.
2. List some of the major issues that a relational database management system needs to concern itself with.
[show answer]
3. Give an overview of the major stages in answering an SQL query in a relational database management system. For each step, describe its inputs and outputs and give a brief description of what it does.
[show answer]
4. PostgreSQL is an “object-relational database management system”. What are the differences between PostgreSQL and a “conventional” relational database management system (such as Oracle)?
[show answer]
5. A PostgreSQL installation includes a number of different “scopes”: databases (or catalogs), schemas (or namespaces), and tablespaces. The scopes correspond to notions from the SQL standard. Explain the difference between these and give examples of each.
[show answer]
6. For each of the following command-line arguments to the psql command, explain what it does, when it might be useful, and how you might achieve the same effect from within psql:
a. -l
b. -f
c. -a
d. -E
7. [show answer]
8. PostgreSQL has two main mechanisms for adding data into a database: the SQL standard INSERT statement and the PostgreSQL-specific COPY statement. Describe the differences in how these two statement operate. Use the following examples, which insert the same set of tuples, to motivate your explanation:`
insert into Enrolment(course,student,mark,grade)
9. values (‘COMP9315’, 3312345, 75, ‘DN’);
10. insert into Enrolment(course,student,mark,grade)
11. values (‘COMP9322’, 3312345, 80, ‘DN’);
12. insert into Enrolment(course,student,mark,grade)
13. values (‘COMP9315’, 3354321, 55, ‘PS’);
14.
15. copy Enrolment(course,student,mark,grade) from stdin;
16. COMP9315 3312345 75 DN
17. COMP9322 3312345 80 DN
18. COMP9315 3354321 55 PS
19. \.
20.
[show answer]
21. In psql, the \timing command turns on a timer that indicates how long each SQL command takes to execute. Consider the following trace of a session asking the several different queries multiple times:
\timing
22. Timing is on.
23. select max(id) from students;
24. max
25. ———
26. 9904944
27. Time: 112.173 ms
28. select max(id) from students;
29. max
30. ———
31. 9904944
32. Time: 0.533 ms
33. select max(id) from students;
34. max
35. ———
36. 9904944
37. Time: 0.484 ms
38. select count(*) from courses;
39. count
40. ——-
41. 80319
42. Time: 132.416 ms
43. select count(*) from courses;
44. count
45. ——-
46. 80319
47. Time: 30.438 ms
48. select count(*) from courses;
49. count
50. ——-
51. 80319
52. Time: 34.034 ms
53. select max(id) from students;
54. max
55. ———
56. 9904944
57. Time: 0.765 ms
58. select count(*) from enrolments;
59. count
60. ———
61. 2816649
62. Time: 2006.707 ms
63. select count(*) from enrolments;
64. count
65. ———
66. 2816649
67. Time: 1099.993 ms
68. select count(*) from enrolments;
69. count
70. ———
71. 2816649
72. Time: 1109.552 ms
73.
Based on the above, suggest answers to the following:
a. Why is there such variation in timing between different executions of the same command?
b. What timing value should we ascribe to each of the above commands?
c. How could we generate reliable timing values?
d. What is the accuracy of timing results that we can extract like this?
74. [show answer]
75. Both the pg_catalog schema and the information_schema schema contain meta-data describing the content of a database. Why do we need two schemas to do essentially the same task, and how are they related?
[show answer]
76.
77. Cross-table references (foreign keys) in the pg_catalog tables are defined in terms of oid attributes. However, examination of the the catalog table definitions (either via \d in psql or via the PostgreSQL documentation) doesn’t show an oid in any of the lists of table attributes. To see this, try the following commands:
psql mydb
78. …
79. \d pg_database
80. …
81. \d pg_authid
82.
Where does the oid attribute come from?
[show answer]
83.
84. Write an SQL view to give a list of table names and table oid’s from the public namespace in a PostgreSQL database.
[show answer]
85.
86. Using the tables in the pg_catalog schema, write a function to determine the location of a table in the filesystem. In other words, provide your own implementation of the built-in function: pg_relation_filepath(TableName). The function should be defined and behave as follows:
create function tablePath(tableName text) returns text
87. as $$ … $$ language plpgsql;
88.
89. select tablePath(‘myTable’);
90. tablepath
91. —————————–
92. PGDATA/base/2895497/2895518
93. select tablePath(‘ImaginaryTable’);
94. tablepath
95. ——————————-
96. No such table: imaginarytable
97.
Start the path string with PGDATA/base if the pg_class.reltablespace value is 0, otherwise use the value of pg_tablespace.spclocation in the corresponding pg_tablespace tuple.
[show answer]
98.
99. Write a PL/pgSQL function to give a list of table schemas for all of the tables in the public namespace of a PostgreSQL database. Each table schema is a text string giving the table name and the name of all attributes, in their definition order (given by pg_attribute.attnum). You can ignore system attributes (those with attnum < 0). Tables should appear in alphabetical order.
The function should have following header:
create or replace function tableSchemas() returns setof text ...
100.
and is used as follows:
select * from tableschemas();
101. tableschemas
102. ---------------------------------------------------------------------------------
103. assessments(item, student, mark)
104. courses(id, code, title, uoc, convenor)
105. enrolments(course, student, mark, grade)
106. items(id, course, name, maxmark)
107. people(id, ptype, title, family, given, street, suburb, pcode, gender, birthday, country)
108. (5 rows)
109.
[show answer]
110.
111. Extend the function from the previous question so that attaches a type name to each attribute name. Use the following function to produce the string for each attribute's type:
create or replace function typeString(typid oid, typmod integer) returns text
112. as $$
113. declare
114. typ text;
115. begin
116. typ := pg_catalog.format_type(typid,typmod);
117. if (substr(typ,1,17) = 'character varying')
118. then
119. typ := replace(typ, 'character varying', 'varchar');
120. elsif (substr(typ,1,9) = 'character')
121. then
122. typ := replace(typ, 'character', 'char');
123. end if;
124. return typ;
125. end;
126. $$ language plpgsql;
127.
The first argument to this function is a pg_attribute.atttypid value; the second argument is a pg_attribute.atttypmod value. (Look up what these actually represent in the PostgreSQL documentation).
Use the same function header as above, but this time the output should look like (for the first three tables at least):
assessments(item:integer, student:integer, mark:integer)
128. courses(id:integer, code:char(8), title:varchar(50), uoc:integer, convenor:integer)
129. enrolments(course:integer, student:integer, mark:integer, grade:char(2))
130.
[show answer]
131. The following SQL syntax can be used to modify the length of a varchar attribute.
alter table TableName alter column ColumnName set data type varchar(N);
132.
where N is the new length.
If PostgreSQL did not support the above syntax, suggest how you might be able to achieve the same effect by manipulating the catalog data.
[show answer]