Instructions
Requirements and Specifications
- Map ALL tables with respective columns (Refer Appendix, Table 1)
- For each column in respective Table, determine the datatype, size, decimal positions, domain, and nullable (or not) (Refer Appendix, Table 2)
- Incorporate at least 2 constraints (using DOMAIN) for those tables with more than 4 columns
- CREATE tables using information from step#2. (Refer Appendix, Table 3)
- Use CHECK CONSTRAINT for primary key(s) in your CREATE statement as applicable.
- INSERT 8 Records in each Table (Refer Appendix, Table 4)
Table Name | Columns (excluding PK and FK) | Primary Key | Foreign Key |
Customer | Cus_LName
Cus_FName Cus_Initial Cus_Areacode Cus_Phone Cus_Balance |
Table 2: Column Datatypes and other information |
---|
CUSTOMER Column Datatype Size Decimal Positions Domain Nullable? (Yes or No) CUS_NUM Numeric 8 0 > 0 NO CUS_LNAME Character 1 - 35 NO CUS_FNAME Character 1 - 30 NO CUS_INITIAL Character 1 YES CUS_AREACODE Numeric 3 0 ≥ 100 & ≤ 999 CUS_PHONE Numeric 7 0 ≥ 1010000 CUS_BALANCE Numeric 7 2 ≥ 0.00 |
Table 3: CREATE table and Screenshot
Table Name | Screenshot |
Table 4: INSERT INTO and Screenshot
Action | Result |
Source Code
create table CUSTOMER
(
CUS_NUM number(8, 0) not null primary key,
CUS_LNAME varchar2(35) not null,
CUS_FNAME varchar2(30) not null,
CUS_INITIAL varchar2(1),
CUS_AREACODE number(3, 0),
CUS_PHONE number(7,0),
CUS_BALANCE number(7, 2),
constraint cusnum_cnstr check (CUS_NUM > 0),
constraint areacode_cnstr check (CUS_AREACODE >= 100 and CUS_AREACODE <= 999),
constraint phone_cnstr check (CUS_PHONE >=1010000),
constraint balance_cnstr check (CUS_BALANCE >= 0.00)
);
create table INVOICE
(
INV_NUMBER number(8,0) primary key,
CUS_NUM number(8,0),
constraint inv_number_cnstr check (INV_NUMBER > 0),
constraint CUS_NUM_REF foreign key (CUS_NUM) references CUSTOMER(CUS_NUM)
);
create table VENDOR
(
V_CODE number(8,0) not null primary key,
V_NAME varchar(20) not null,
V_CONTACT varchar(35) not null,
V_AREACODE number(3,0),
V_PHONE number(7,0),
V_STATE varchar(35),
V_ORDER varchar(10),
constraint vcode_cnstr check (V_CODE > 0),
constraint v_areacode_cnstr check (V_AREACODE >= 100 and V_AREACODE <= 999),
constraint v_phone_cnstr check (V_PHONE >=1010000),
constraint vorder_cnstr check (V_ORDER = 'OPEN' or V_ORDER = 'PENDING' or V_ORDER = 'COMPLETE')
);
create table PRODUCT
(
P_CODE number(8,0) not null primary key,
P_DESCRIPT varchar2(40),
P_INDATE date not null,
P_ONHAND number(1,0),
P_MIN number(8,0),
P_PRICE number(8,2),
P_DISCOUNT number(3,2),
V_CODE number(8,0) not null,
constraint pcode_cnstr check (P_CODE > 0),
constraint pmin_cnstr check(P_MIN >= 0),
constraint ponhand_cnstr check(P_ONHAND = 0 OR P_ONHAND = 1),
constraint pprice_cnstr check (P_PRICE >= 0.00),
constraint pdiscount_cnstr check (P_DISCOUNT >= 0.00),
constraint vcode_ref_cnstr foreign key (V_CODE) references VENDOR(V_CODE)
)
create table LINE
(
INV_NUMBER number(8,0) not null,
LINE_NUMBER number(8,0) not null,
P_CODE number(8,0) not null,
LINE_UNITS number(8,0),
LINE_PRICE number(8,2),
constraint l_invnumber_ref foreign key (INV_NUMBER) references INVOICE(INV_NUMBER),
constraint l_linenumber_cnstr check (LINE_NUMBER > 0),
constraint l_pcode_ref foreign key (P_CODE) references PRODUCT(P_CODE),
constraint l_lineunits_cnstr check (LINE_UNITS > 0),
constraint l_lineprice_cnstr check (LINE_PRICE > 0.00),
constraint l_PKS primary key(INV_NUMBER, LINE_NUMBER)
)
INSERT ALL
INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(1, 'Wick', 'John', 'J', 111, 1234567, 0.73)
INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(2, 'King', 'Martin', 'M', 652, 1254785, 999.99)
INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(3, 'Hill', 'Mariah', 'H', 123, 4521452, 0.00)
INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(4, 'Smith', 'Carl', 'C', 563, 1253658, 1256.31)
INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(5, 'Shakespeare', 'William', 'S', 741, 1010001, 16556.25)
INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(6, 'Smith', 'William', 'S', 963, 5214474, 0.00)
INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(7, 'Carter', 'Monica', 'C', 852, 1020114, 63.52)
INTO CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES(8, 'Cox', 'Charlie', 'C', 888, 1020205, 74.10)
SELECT * FROM DUAL;
INSERT ALL
INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (1, 'Medical Stuff', 'msupplies@gmail.com', 152, 1234567, 'Florida', 'OPEN')
INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (2, 'Sound Stuff', 'awesomesound@gmail.com', 133, 6593157, 'New York', 'PENDING')
INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (3, 'Supermarket', 'Ms. Alex', 256, 6958214, 'Michigan', 'OPEN')
INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (4, 'Amazon', 'amazon@amazon.com', 111, 5865321, 'DC', 'COMPLETE')
INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (5, 'Ebay', 'ebay@gmail.com', 853, 9659658, 'Florida', 'OPEN')
INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (6, 'Walmar', 'walmart@gmail.com', 321, 9632541, 'Miami', 'PENDING')
INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (7, 'Dunder Mifflin', 'dmifflin@gmail.com', 777, 6353655, 'Kansas', 'OPEN')
INTO VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES (8, 'PC Stuff', 'mpcsuppliess@gmail.com', 152, 6549185, 'New York', 'OPEN')
SELECT * FROM DUAL;
INSERT ALL
INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (1, 'KN95 masks', TO_DATE('11/11/2021', 'dd/MM/yyyy'), 0, 150, 5.99, 6.00, 1)
INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (2, 'Microphone', TO_DATE('16/11/2021', 'dd/MM/yyyy'), 1, 1, 65.99, 6.00, 2)
INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (3, 'Vegetables', TO_DATE('12/11/2021', 'dd/MM/yyyy'), 0, 5, 12.49, 0.00, 3)
INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (4, 'Pillows', TO_DATE('05/09/2021', 'dd/MM/yyyy'), 1, 2, 37.49, 0.00, 4)
INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (5, 'Pokemon cards', TO_DATE('16/11/2021', 'dd/MM/yyyy'), 1, 10, 136.49, 5.00, 5)
INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (6, 'Sewing Machine', TO_DATE('05/10/2021', 'dd/MM/yyyy'), 0, 1, 217.00, 0.00, 6)
INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (7, 'Paper', TO_DATE('15/11/2021', 'dd/MM/yyyy'), 1, 20, 130.00, 2.00, 7)
INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES (8, 'Nvidia GPU', TO_DATE('10/11/2021', 'dd/MM/yyyy'), 1, 1, 569.99, 5.00, 8)
SELECT * FROM DUAL;