Lab Session 9 - Practical Assignment 2

By Mitchell Welch

University of New England

Reading

• PostgreSQL Documentation: http://www.postgresql.org/docs/9.4/static/index.html
• Practical Session 4
• Practical Assignment 2

Summary

• Practical Assignment 2

Practical Assignment 2

In todays lab session you will be working on practical assignment 2. To get started:

• Make sure you can connect to you ‘apps’ database. To do this you should review the house keeping section in Practical session 4
• Start by designing you database schema. Review the assignment requirements and create the tables to store the relevant data items.
• Your schema should be in 3NF (Will deduct marks if it is not), so make sure you approach the design carefully.
• Recall that each contact should have a unique serial number. This can be implemented using the SERIAL datatype in postgres. The serial datatype is an auto-incrementing number that does not need to be specified on insert:

CREATE TABLE tablename (
    colname SERIAL,
    other_col VARCHAR
);

INSERT into tablename(other_col) VALUES ('test');
INSERT into tablename(other_col) VALUES ('test1');
INSERT into tablename(other_col) VALUES ('test2');

SELECT * FROM tablename;

prac_08=> SELECT * FROM tablename;
 colname | other_col 
---------+-----------
       1 | test
       2 | test1
       3 | test2
(3 rows)

prac_08=> 

• The RETURNING clause can be used to return the value of the auto-incrementing serial when an insert is executed:

INSERT into tablename(other_col) VALUES ('test3') RETURNING colname;
INSERT into tablename(other_col) VALUES ('test4') RETURNING colname;
INSERT into tablename(other_col) VALUES ('test5') RETURNING colname;

prac_08=> INSERT into tablename(other_col) VALUES ('test3') RETURNING colname;
 colname 
---------
       4
(1 row)

INSERT 0 1
prac_08=> INSERT into tablename(other_col) VALUES ('test4') RETURNING colname;
 colname 
---------
       5
(1 row)

INSERT 0 1
prac_08=> INSERT into tablename(other_col) VALUES ('test5') RETURNING colname;
 colname 
---------
       6
(1 row)

INSERT 0 1
prac_08=> 

• This will allow you Java program to access the serial number when a new contact is created.

• Once your schema is up and running, start on your Java application.

  • Start with the basics: get you application connecting and reading the data from the contacts table.
  • Then move on and get you application creating new records.
  • Then get your menus all straightened out.
• Remember that you program should prompt the user for the connection information at startup - so there is no need to hard-code any connection details.
• Make sure you include lots of internal documentation in you Java code and your SQL database schema.
• Make sure everything is tabbed and presented consistently.