-- CREATE TABLE person (
-- p_id NUMBER(3),
-- p_name VARCHAR2(20),
-- p_sin CHAR(9),
-- p_hiredate DATE,
-- p_a_id NUMBER(3),
-- CONSTRAINT person_p_id_PK PRIMARY KEY (p_id),
-- CONSTRAINT person_p_a_id_FK FOREIGN KEY (p_a_id) REFERENCES area (a_id));
-- 2) to change the above table to increase the length specification of the p_name field:
-- ALTER TABLE person
-- MODIFY (p_name VARCHAR2(30);
-- 3) to drop a table including any dependent objects such as FOREIGN KEY constraints:
-- DROP TABLE person CASCADE CONSTRAINTS; (Should start all scripts with this preventative deletes, just in case the tables exist in the database.)

-- VARCHAR2 -- up to 2000 characters
-- VARCHAR -- up to ??

CREATE TABLE driver (
d_id INTEGER(3),
d_name VARCHAR(20),
d_contact CHAR(10),
CONSTRAINT driver_d_id_PK PRIMARY KEY(d_id)
);

CREATE TABLE limo (
l_id INTEGER(3),
l_callsign VARCHAR(15),
l_type VARCHAR(300),
CONSTRAINT limo_l_id_PK PRIMARY KEY(l_id)
);

CREATE TABLE client (
c_id INTEGER(3),
c_name VARCHAR(20),
c_contact CHAR(10),
c_methpmt VARCHAR(10),
CONSTRAINT client_c_id_PK PRIMARY KEY(c_id)
);

CREATE TABLE qualify (
g_id INTEGER(2),
g_l_id INTEGER(2),
g_d_id INTEGER(2),
CONSTRAINT qualify_g_id_PK PRIMARY KEY(g_id),
CONSTRAINT qualify_l_id_FK FOREIGN KEY(l_id) REFERENCES limo (l_id),
CONSTRAINT qualify_d_id_FK FOREIGN KEY(d_id) REFERENCES driver (d_id)
);

CREATE TABLE rental (
r_id INTEGER(3),
r_date DATE,
r_fee DECIMAL(15,2),
r_c_id int(3),
r_q_id int(3),
CONSTRAINT rental_r_id_PK PRIMARY KEY(r_id),
CONSTRAINT rental_r_c_id_FK FOREIGN KEY(r_c_id) REFERENCES client1 (c_id),
CONSTRAINT rental_r_q_id_FK FOREIGN KEY(r_q_id) REFERENCES qualify (g_id)
);


---- inserting into driver table
INSERT INTO driver VALUES (1, 'alf', 9022332332);
INSERT INTO driver VALUES (2, 'bob', 9022322323);
INSERT INTO driver VALUES (3, 'cal', 9024919999);
INSERT INTO driver VALUES (4, 'dan', 9024914545);


---- inserting into limo table
INSERT INTO limo VALUES (1, 'Car One', 'stretch limo');
INSERT INTO limo VALUES (2, 'Car Two', 'hummer limo');
INSERT INTO limo VALUES (3, 'Car Three', 'armored personnel carrier');

---- inserting into client table
INSERT INTO client VALUES (1, 'ann', 9028889999, 'cash');
INSERT INTO client VALUES (2,   'bub', 9027778888, 'CC');

---- inserting into qualify table
INSERT INTO qualify VALUES (1, 1, 1);
INSERT INTO qualify VALUES (2, 2, 1);
INSERT INTO qualify VALUES (3, 3, 1);
INSERT INTO qualify VALUES (4, 1, 2);
INSERT INTO qualify VALUES (5, 2, 2);

---- inserting into rental table
INSERT INTO rental VALUES (1, 21/3/2015, 550, 2, 1);
INSERT INTO rental VALUES (2, 21/3/2015, 1000, 1, 5);
INSERT INTO rental VALUES (3, 20/3/2015, 2050, 1, 3);
INSERT INTO rental VALUES (4, 19/3/2015, 550, 1, 1);
INSERT INTO rental VALUES (5, 20/3/2015, 500, 2, 4);



COMMIT;