Coursework #1: The mechanics of the implementation of a simple database in Postgres

COURSEWORK #1

The mechanics of the implementation of a simple database in Postgres

Value: 5% of final mark

Specification9/10/02

Submission29/10/02

Aim

Through this coursework you should be able to illustrate that:

(1)you have a set of basic skills in both the DDL and the DML components of SQL (through PostgreSQL), and

(2)you are accustomed with some of the elements of the Postgres DBMS.

Mode of work and marking

Work through the tasks specified below.

As evidence of the successful completion of a task you should provide in your report a listing of the commands you have used and the results you obtained. Be aware that we have access to the Postgres logs, meaning that we could trace all the operations you perform on your database.

The coursework is marked out of 100 marks. Each task is worth 4 marks. A task can only be either successfully completed or not completed. For a successfully completed task you get 4 marks. For a task that is not completed you get nothing. (this apparent coarse granularity is motivated by the relatively small weight/value the coursework has in the overall mark).

You get a maximum of 20 marks for presentation.

REMINDER: Plagiarism will be severely punished.

Tasks

  1. Create 3 databases, two from within the Linux shell and one from the Postgres environment. List the databases you own. Keep only one database and remove all the others you own.
  2. Create a table — referred to as Table1 — preferably with a meaning in an application domain that you are accustomed with, with at least one field/attribute/column of each of the following generic types: character/string, numeric, serial, monetary and date/time (I called them “generic types” because PostgreSQL may provide more than one built in type for each of them; e.g., both CHAR(10) and VARCHAR are character/string types).
  3. Create a table — Table2 — with the same specifications as above, but with each field, apart from the one of type serial, constrained in a way of your choice. All the constraints should differ from one another.
  4. List, using Postgres backend commands (backslash), the tables you have in your database and the definition of each table.
  5. Insert at least 6 tuples in Table2.
  6. Alter Table2 (after values were inserted into it) by renaming fields and adding new fields.
  7. A table cannot be altered by removing fields? Why? Can you find a way around to this problem?
  8. Retrieve values from Table2 via 4 queries with the following specifications:
    a restriction using a condition on a single field
    a restriction using a condition on two fields
    a restriction using a condition on three fields
    a projection
  9. Create a table — Table3 — with the following specifications:
    it has fewer fields than Table 2
    each field in Table3 has one and only one corresponding field in Table2; “corresponding”, here, means “of the same data type, but of a different name”.
    Insert values in Table3 from Table2 via a SELECT statement.
  10. Modify values/tuples in Table 2 and/or Table3 via at least 4 different UPDATE statements.
  11. Remove values/tuples from Table 2 and/or Table3 via at least 4 different DELETE statements.
  12. Copy the content of Table2 or Table 3 into a file. Copy the values stored in a file you previously created using a text editor (NB not word processor) into one of the tables of your database.
  13. Create 4 tables — Table4, Table5, Table6 and Table7 — according to the following specifications:
    Each table must have at least three fields
    Table4 — has a single field primary key (PK) and no foreign keys (FK)
    Table5 — has a single field PK and one FK referencing Table3
    Table6 — has a composite PK and no FKs
    Table7 — has its own PK and two FKs, one referencing Table3 and one referencing Table4
    It is preferred (but not necessary) that the tables you created have a meaning in an application domain.
  14. Insert values/tuples in all the new tables; each new table should contain at least 5 tuples.
  15. Write two queries:
    one that joins Table5 with Table4 (other conditions may also be used)
    one that joins Table5 with Table7 (other conditions may also be used)
  16. Write two queries
    one that joins tables 7, 5 and 4 (other conditions may also be used)
    one that joins tables 7, 5 and 6 (other conditions may also be used)
  17. Write a query that performs a join between three tables and restriction and a projection (if it is appropriate, you may re-use any of the solutions you provided above)
  18. Using any of the tables you have in the database, write three queries, each using a different aggregate function.
  19. Using any of the tables you have in the database, write a query that selects tuples using both one or more group conditions (via some aggregate functions) and conditions on individual tuples (at least one).
  20. Create two tables —Table 8 and Table 9 — exactly following the structure of Table2 and Table3. Move all the data from table 2 and 3 in the new tables. Then, remove the new tables.

1

Version 2

Marian F. Ursu, Oct 2002