/* 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