/* COMP 1278 Fall 2010
   Assignment 2
   Mike Keneqa and Fahad Zaidi
   Oct 27, 2010
*/
 
SPOOL asg2.log
SET ECHO ON
 
DROP TABLE person;
DROP TABLE person_address;
DROP TABLE person_phone;
DROP TABLE person_email;
DROP TABLE employee;
DROP TABLE customer;
DROP TABLE reward;
DROP TABLE bike;
DROP TABLE purchases;
DROP TABLE tailor_made;
DROP TABLE repair;
DROP TABLE repair_shop_time;
DROP TABLE repair_part;
DROP TABLE repair_service;
DROP TABLE repair_note;
 
-- still questioning
DROP TABLE uses_components;
DROP TABLE build_a_bike;
 
--not sure abou this one
CREATE TABLE uses_component(
	build_a_bike_id NUMBER PRIMARY KEY,
	component_id NUMBER NOT NULL
);
 
CREATE TABLE repair_note(
	dateTime CURRENT_TIMESTAMP,
	repair_id NUMBER NOT NULL,
	person_id NUMBER NOT NULL,
	note VARCHAR2(100);
);
 
CREATE TABLE repair_service(
	repair_id NUMBER PRIMARY KEY,
	description VARCHAR2(100),
	service_time CURRENT_TIMESTAMP
);
 
CREATE TABLE repair_part(
	repair_id NUMBER PRIMARY KEY,
	description VARCHAR2(100),
	quantity NUMBER,
	price NUMBER
);
 
CREATE TABLE repair_shop_time(
	repair_id NUMBER PRIMARY KEY,
	description VARCHAR2(50),
	shop_time CURRENT_TIMESTAMP
);
 
CREATE TABLE repair(
	repair_id NUMBER PRIMARY KEY,
	emp_id NUMBER NOT NULL,
	cust_id NUMBER NOT NULL,
	date_in DATE,
	date_out DATE
);
 
--not sure about this one 
CREATE TABLE build_a_bike(
	bike_id NUMBER PRIMARY KEY,
	person_id NUMBER NOT NULL
);
 
CREATE TABLE tailor_made(
	bike_id NUMBER PRIMARY KEY,
	price NUMBER NOT NULL,
	trim_level VARCHAR2(5)
);
 
CREATE TABLE purchase(
	person_id NUMBER PRIMARY KEY,
	bike_id NUMBER,	
	purchase_date DATE
);
 
CREATE TABLE bike(
	bike_id NUMBER PRIMARY KEY,
	bike_style VARCHAR2(15) NOT NULL,
	serial_number NUMBER NOT NULL,
	make VARCHAR2(15) NOT NULL,
	model VARCHAR2(15) NOT NULL,
	colour VARCHAR2(15) NOT NULL,
	bike_size CHAR NOT NULL
);
 
CREATE TABLE reward(
	reward_id NUMBER PRIMARY KEY,
	person_id NUMBER,
	reward_date DATE,
	type_of_sale CHAR,
	points NUMBER	
);
 
CREATE TABLE customer(
	person_id NUMBER PRIMARY KEY,
	rewards_status CHAR,
);
 
CREATE TABLE employee(
	person_id NUMBER PRIMARY KEY,
	sin_number NUMBER,
	hire_date DATE,
	salary NUMBER
);
 
CREATE TABLE address(
		person_id NUMBER PRIMARY KEY,
		add_type CHAR,
		street VARCHAR2(15),
		city VARCHAR2(15),
		pCode VARCHAR2(6)
);
 
CREATE TABLE person(
	person_id NUMBER PRIMARY KEY,
	first_name VARCHAR2(20),
	last_name VARCHAR2(30)
);
 
CREATE TABLE email(
	email_id NUMBER PRIMARY KEY,
	first_name VARCHAR2(20),
	last_name VARCHAR2(30)
);
 
 
CREATE TABLE phone(
	person_id NUMBER,
	ph_number NUMBER,
	ph_type CHAR;
);
 
CREATE TABLE address(
	person_id NUMBER,
	ph_number NUMBER,
	ph_type CHAR;
 
	/* first_name VARCHAR2(50),
	last_name VARCHAR2(50) NOT NULL,
	birth_date DATE CHECK (birth_date > '01-JAN-1890'),
	position VARCHAR2(50),
	department_id NUMBER REFERENCES department(dept_id) */
);
 
 
SET ECHO off
SPOOL off