COURSEWORK #1 by yq3ifIr

VIEWS: 6 PAGES: 2

									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

                                    Specification                9/10/02
                                    Submission                   29/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.



Version 2                                                                                          1
Marian F. Ursu, Oct 2002
Coursework #1: The mechanics of the implementation of a simple database in Postgres



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.




Version 2                                                                                               2
Marian F. Ursu, Oct 2002

								
To top