


create table member (
       member_id	SERIAL,
       member_no	CHAR(5) NOT NULL,
       title		CHAR(3),
       fname		CHAR(25),
       lname		CHAR(25) NOT NULL,
       house_flat_ref	CHAR(25) NOT NULL,
       address1		CHAR(50) NOT NULL,
       address2		CHAR(50),
       town		CHAR(50) NOT NULL,
       state		CHAR(2),
       phone		CHAR(30),
       zipcode		CHAR(10) NOT NULL,
       CONSTRAINT	member_no_uniq UNIQUE(member_no)
);


create table title (
       title_id		SERIAL,
       title_text	CHAR(60) NOT NULL,
       asin		CHAR(10),
       director		CHAR(25),
       genre_id		INT,
       classification	CHAR(10),
       actor1		CHAR(25),
       actor2		CHAR(25),
       release_date	CHAR(8),
       rental_cost	CHAR(6)
);

create table disk (
       disk_id		SERIAL,
       title_id		INT NOT NULL,
       member_id	INT,	 /* set if rented out otherwise NULL */
       rented_date	CHAR(8)
);


create table member_booking (
       member_id	   INT NOT NULL,
       title_id		   INT NOT NULL,
       date_required	   CHAR(8) NOT NULL
);

create table filmclass (
	class_name	  CHAR(10)
);

create table genre (
       genre_id		  INT NOT NULL,
       genre_name	  CHAR(20),
       CONSTRAINT	  genre_id_uniq UNIQUE(genre_id)
);

create table errtext (
       err_code		INT,
       err_text		CHAR(50)
);


