#Lab Session 6 - Practical Assignment
###By Mitchell Welch/Edmund Sadgrove

###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 Sessions 1](http://turing.une.edu.au/~cosc210/workshops/display_prac.php?prac=1) 
* [Practical Sessions 2](http://turing.une.edu.au/~cosc210/workshops/display_prac.php?prac=2)
* [Practical Sessions 3](http://turing.une.edu.au/~cosc210/workshops/display_prac.php?prac=3)
* [Practical Assignment](http://turing.une.edu.au/~cosc210/assignments/display_notes.php?assignment=4)

---

## Summary
* Practical Assignment

---

## Practical Assignment

In todays lab session you will be working on the practical assignment, to get started:

* Make sure you that you have completed the aforementioned practical workshops and have viewed the PostgreSQL lectures.
* Start by designing your database schema. Review the assignment requirements and create the tables to store the relevant data items.
* Carefully read the assignment description making sure you define the appropriate domain and check constraints. 

## Exercise 1
In exercise one you will be creating a database schema, you should have enough information from lecture 4 in week 2 to complete this exercise.
Recall the syntax for creating a table and inserting data:
```sql

CREATE TABLE tablename (
    colname INT,
    other_col VARCHAR
);

INSERT into tablename(other_col) VALUES (1,'test');
INSERT into tablename(other_col) VALUES (2,'test1');
INSERT into tablename(other_col) VALUES (3,'test2');

SELECT * FROM tablename;
```

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

prac_06=> 

```
The data for this assignment will be inserted from the script provided in the assingment description. 
Once you have created your database schema, you can import the MovieDirect data.
You will need to right click and save the data from the link below.

* [Link to Data](http://turing.une.edu.au/~cosc210/assignments/a4/MovieDirect_Data.sql) 
* Download the data to your working directory and import to your database:
* psql my\_assignment\_db < MovieDirect\_Data.sql

## Exercise 2
In exercise 2 you will be defining eight queries and creating views from your database schema. You are required to use the template when submitting your user views. Like the data script you can use the template to double check that your queries match assignment specification. Errors found when adapting to the template can once again be used to adhere to assignment specification.

To create a simple view, you can use the syntax. 
```sql

CREATE VEW AS 
     SELECT * FROM tablename;

```
Remember to download the template and place your queries into the template. You can then test your queries by importing them in the same way you imported the data. Recall; to display your Views you can use the psql command \dv.

* [Link to Template](http://turing.une.edu.au/~cosc210/assignments/a4/p_template.sql) 
* Download to your working directory, fill in the section marked with dots and test the template:
* psql my\_assignment\_db < p\_template.sql