Code-Bin #907 - Asg2

Asg2 - Bike Shop
Posted by: Mike & Fahad | October 22, 2010 @ 9:49pm

Revision 1 - Original post on October 22, 2010 @ 5:07pm

SQL Code

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

Syntax Highlighting

[Open in new window]

Author Comments

none

Rating

4.45 / 8
60 Votes