Database Design

We now have a provisional design for the database done, as outlined below;

Database design has now been mdified and finalised - 12/3/07

Table 1 - books

book_id - varchar(10) - primary key
title - varchar - not null
author - varchar - not null
genre - varchar - not null
price - real - not null
picture_loc - varchar
stock - integer - not null
review - varchar

Table 2 - customers

cust_id - serial - primary key
password - varchar - not null
first_name - varchar - not null
last_name - varchar - not null
street - varchar - not null
city - varchar - not null
postcode - varchar(8) - not null
country - varchar - not null
phone_number - varchar - not null
email - varchar - not null

Cust_id sequence = customers_cust_id_seq
cust_id starting value is 100001

Table 3 - orders

order_id - serial - primary key
cust_id - integer - foreign key
book_id - integer - foreign key
data - date - not null

Order_id sequence value = orders_order_id_seq

Rob and I will be spending the weekend working on SQL queries, focusing on adding/modifying and queries respectively. We hope to have the database design finalised by Tuesday at the latest, allowing us to decide on the methods we require, with a view to coding the majority of the JDBC by the end of the week.

We have now, after considerable issues, been able to connect to a local version of our database using netbeans and the postgresql drivers, and we are now able to start writing our JDBC.

Now that the database is finalised, an API for the JDBC can be written and work can begin on writing the methods and the sql queries.

Next Meeting - 13/3/7 12.00pm - Agenda = task review & allocation.