#Lab Session 6 - Practical Assignment 2
###By Mitchell Welch

###University of New England

---

## Reading
* PostgreSQL Documentation: [http://www.postgresql.org/docs/9.4/static/index.html](http://www.postgresql.org/docs/9.4/static/index.html)
* [Practical Session 4](http://turing.une.edu.au/~cosc210/workshops/display_prac.php?prac=4) 
* [Practical Assignment 2](http://turing.une.edu.au/~cosc210/assignments/display_notes.php?assignment=4)

---

## 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](http://turing.une.edu.au/~cosc210/workshops/display_prac.php?prac=4)
* Start by designing you database schema. Review the assignment requirements and create the tables to store the relevant data items.
* Be careful when reading the assignment description that you define all the appropriate check constraints. 
* These constraints will need to be checked in your Java program as well.
* You can modify your program so that the ID number is generated by the database (ensuring it is unique for each record). 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: 

```sql

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:

```sql

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.