-------------------------------------------------
-- Assignment 4, Question 2.1
-------------------------------------------------


create table PRODUCT (
	manufacturer	varchar (30) not null,
	model_number	varchar (20) not null,
	retail_price	decimal (8,2) not null,
	number_in_stock	integer not null,
	primary key (manifacturer, model_number)

create table CAMERA (
	manufacturer	varchar (30) not null,
	model_number	varchar (20) not null,
	release_date	date,
	sensor_size	integer not null,
	pixel_count	integer not null,
	has_efv		char (1) not null,
	has_ovf		char (1) not null,
	ovf_type	char (3),
	primary key (manifacturer, model_number),
	foreign key (manifacturer, model_number) references PRODUCT )

create view CAMERA_WITH_OVF as (
	select manufacturer, model_number, ovf_type from CAMERA
	where has_ovf = 'T' )

create view OVF_CAMERA as (
	select manufacturer, model_number from CAMERA_WITH_OVF
	where ovf_type = 'OVF' )

create view TTL_CAMERA as (
	select manufacturer, model_number from CAMERA_WITH_OVF
	where ovf_type = 'TTL' )

create view OVF_CAMERA as (
	select manufacturer, model_number from CAMERA_WITH_OVF
	where ovf_type = 'RF ' )

create view CAMERA_WITH_EVA as (
	select manufacturer, model_number from CAMERA
	where has_evf = 'T' )

create table CAMERA_W_LENS (
	manufacturer	varchar (30) not null,
	model_number	varchar (20) not null,
	focal_low	integer not null,
	focal_high	integer not null,
	aperture_low	integer not null,
	aperture_high	integer not null,
	is_prime	char (1) not null,
	primary key (manifacturer, model_number),
	foreign key (manifacturer, model_number) references CAMERA )

create table CAMERA_WO_LENS (
	manufacturer	varchar (30) not null,
	model_number	varchar (20) not null,
	primary key (manifacturer, model_number),
	foreign key (manifacturer, model_number) references CAMERA )
	
create table SEPARATE_LENS (
	manufacturer	varchar (30) not null,
	model_number	varchar (20) not null,
	focal_low	integer not null,
	focal_high	integer not null,
	aperture_low	integer not null,
	aperture_high	integer not null,
	is_prime	char (1) not null,
	primary key (manifacturer, model_number),
	foreign key (manifacturer, model_number) references PRODUCT )

create view BUILT_IN_LENS as (
	select * from CAMERA_W_LENS )

create view LENS as (
	( select * from SEPARATE_LENS )
	union
	( select * from BUILT_IN_LENS ) )

create view PRIME_LENS as (
	select * from LENS where is_prime = 'T' )

create table CUSTOMER (
	customer_number	integer,
	customer_name	varchar (30) not null,
	email_addr	varchar (40) not null,
	shipping_addr	varchar (60) not null,
	customer_type	varchar (8) not null,
	primary key (customer_number) )

create view DOMESTIC_CUSTOMER as (
	select customer_number from CUSTOMER
	where customer_type = 'DOMESTIC' )

create view FOREIGN_CUSTOMER as (
	select customer_number from CUSTOMER
	where customer_type = 'FOREIGN' )

create table PURCHASE_ORDER (
	order_number	integer,
	selling_price	decimal (8,2) not null,
	manufacturer	varchar (30) not null,
	model_number	varchar (20) not null,
	customer_number	integer,
	is_outstanding	char (1) not null,
	primary key (order_number),
	foreign key (manifacturer, model_number) references PRODUCT,
	foreign key (customer_number) references CUSTOMER )

create view OUTSTANDING_PURCHASE_ORDER as (
	select order_number from PURCHASE_ORDER
	where is_outstanding = 'T' )

create table EVALUATION (
	customer_number	integer,
	sequence_number	integer,
	manufacturer	varchar (30) not null,
	model_number	varchar (20) not null,
	score		integer not null,
	comment		varchar (200) not null,
	primary key (customer_number, sequence_number),
	foreign key (customer_number) references CUSTOMER,
	foreign key (manifacturer, model_number) references PRODUCT )


-------------------------------------------------
-- Assignment 4, Question 2.2 (examples)
-------------------------------------------------

-- There are lots of cases of tables with non-overlapping primary keys
-- that derive from generalizations in the ER diagram.  E.g.:

create assertion CAMERA-W-WO-LENS-DISJOINT as (
   not exists (
      (select manufacturer, model_number from CAMERA-W-LENS)
      intersect
      (select manufacturer, model-number from CAMERA-WO-LENS) ) )

-- The requirement that focal_low = focal_high for prime lenses
-- can be captured:

create assertion PRIME-LENS-SINGLE-FOCAL-LENGTH as (
   not exists (
      select * from PRIME_LENS where not focal_low = focal_high ) )