ALL Relational Operator in Oracle

composite fk 

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   FOREIGN KEY (column1, column2, ... column_n)
   REFERENCES parent_table (column1, column2, ... column_n);

it inventory software


create table it_item
(
itemcode               varchar2(30),  ---pk 
itemname               varchar2(120),
uom_code               varchar2(3),   ---fk
cat_code               varchar2(10),  ---fk
brand_code             varchar2(10),  ---fk
item_status            varchar2(1),
user_id                varchar2(6),
wk_dt                  varchar2(20)   default to_char(sysdate,'DD-MON-RR HH24:MI:SS')
)
/
alter table it_item add constraint it_item_pk  primary key(itemcode,itemname)
/
alter table it_item add constraint it_item_fk foreign key(uom_code) references it_item_uom(uom_code)
/
alter table it_item add constraint it_item_category_fk foreign key(cat_code) references it_item_category(cat_code)
/
alter table it_item add constraint it_item_brand_fk foreign key(brand_code) references it_item_brand(brand_code)
/


create table it_item_uom
(
uom_code               varchar2(3),  ---pk  
uom_nm                 varchar2(3)
)
/
alter table it_item_uom add constraint it_item_uom_pk  primary key(uom_code)
/



create table it_item_category
(
cat_code              varchar2(10),   ---pk 
cat_nm                varchar2(50)
)
/
 alter table it_item_category add constraint it_item_category_pk  primary key(cat_code)
/


create table it_item_brand 
(
brand_code              varchar2(10),   ---pk
brand_nm                varchar2(50)   
)
/
alter table it_item_brand add constraint it_item_brand_pk  primary key(brand_code)
/


create table it_receipr_master
(
recpt_no              varchar2(30),   ---pk
recpt_dt              date,
slcode                varchar2(6),   ----
slname                varchar2(70),
prs_no                varchar2(30),
prs_dt                varchar2(30),
remk                  varchar2(200),
recpt_by              varchar2(6),
user_id               varchar2(6),
wk_dt                 varchar2(20) default  to_char(sysdate,'DD-MON-RR HH24:MI:SS')
)
/
alter table it_receipr_master add constraint it_receipr_master_pk  primary key(recpt_no)
/


create table it_receipt_detail
(
recpt_no             varchar2(30),  ---com.pk 
slno                 number(30),    ---com.pk
itemcode             varchar2(30),  ---fk   
qty                  number(20,2),
unit_price           number(20,2),
tot_price            number(20,2)
)
alter table it_receipt_detail add constraint it_receipt_detail_pk primary key(recpt_no,sl_no,itemcode)
/
alter table it_receipt_detail add constraint it_item_fk foreign key(itemcode) references it_item(itemcode)
/
alter table it_receipt_detail add constraint it_receipr_master_fk foreign key(recpt_no) references it_receipr_master(recpt_no)
/




create table it_emp_delivery_master
(
del_no               varchar2(30),   --pk  
del_dt               date,
emp_id               varchar2(6),    ---- fk view
remk                 varchar2(150),
del_by               varchar2(6),
user_id              varchar2(6),
wk_dt                varchar2(20)     default  to_char(sysdate,'DD-MON-RR HH24:MI:SS')
)
/

alter table it_emp_delivery_master add constraint it_emp_delivery_master_pk  primary key(del_no)
/


create table it_emp_delivery_detail
(
del_no               varchar2(30),   ---com.pk
slno                 number(30),     ---com.pk
itemcode             varchar2(30),   ---fk 
qty                  number(10,2),
item_desc            varchar2(150)
)

alter table it_emp_delivery_detail add constraint br_receipt_detail_pk primary key(del_no,slno,itemcode)
/
alter table it_emp_delivery_detail add constraint it_emp_delivery_detail_fk foreign key(itemcode) references it_item(itemcode)
/
alter table it_emp_delivery_detail add constraint it_emp_delivery_master_fk foreign key(del_no) references it_emp_delivery_master(del_no)
/



create table it_return_master
(
rtn_no               varchar2(30),     ---pk
rtn_dt               date,
emp_id               varchar2(30),     ---fk view 
rtn_desc             varchar2(200),
del_by               varchar2(6),
user_id              varchar2(6),
wk_dt                varchar2(20)     default  to_char(sysdate,'DD-MON-RR HH24:MI:SS')
)
/
alter table it_return_master add constraint it_return_master_pk  primary key(rtn_no)





create table it_return_detail
(
rtn_no               varchar2(30),   ---com.pk
slno                 number(30),     ---com.pk
itemcode             varchar2(30),   ---fk   
qty                  number(10,2),
item_desc            varchar2(150)
)  

alter table it_return_detail add constraint it_return_detail_pk primary key(recpt_no,slno,itemcode)
/
alter table it_return_detail add constraint it_return_detail_fk foreign key(itemcode) references it_item(itemcode)
/
alter table it_return_detail add constraint it_return_master_fk foreign key(rtn_no) references it_return_master(rtn_no)
/


create table it_wastage_master
(
wast_no             varchar2(30),   ---pk
wast_dt             date,
emp_id              varchar2(6),    ---fk view
remk                varchar2(150),
wast_by             varchar2(6),
user_id             varchar2(6),
wk_dt               varchar2(20)     default  to_char(sysdate,'DD-MON-RR HH24:MI:SS')

)
/
alter table it_wastage_master add constraint it_wastage_master_pk  primary key(wast_no)

/



create table it_wastage_detail
(
wast_no             varchar2(30),  ---com.pk  ,fk
slno                varchar2(30),  ---com.pk
itecode             varchar2(30),
qty                 number(10,2),
item_desc           varchar2(150)
)
/
alter table it_wastage_detail add constraint it_wastage_detail_pk primary key(wast_no,slno,itemcode)
/
alter table it_wastage_detail add constraint it_wastage_detail_fk foreign key(itemcode) references it_item(itemcode)
/
alter table it_wastage_detail add constraint it_wastage_master_fk foreign key(wast_no) references it_wastage_master(wast_no)
/





create table it_emp_issue_info
(
tag_sl               varchar2(50),    ---pk 
del_no               varchar2(30),    ---fk 
emp_id               varchar2(6),     ---fk
computer_code        varchar2(30),    ---fk
cpu_serialno         varchar2(50),
cpu_brand            varchar2(50),
cpu_qty              number(4,2),
cpu_desc             varchar2(100),
ups_serial           varchar2(50),
ups_brand            varchar2(50),
ups_qty              number(4,2),
ups_desc             varchar2(100),
monitor_serialno     varchar2(50),
monitor_brand        varchar2(50),
monitor_qty          varchar2(4,2),
keyboard_brand       varchar2(50),
keyboard_qty         number(4,2),
mouse_brand          varchar2(50),
mouse_qty            number(4,2),
laptop_bag_brand     varchar2(50),
laptop_bag_qty       number(4,2),
mouse_pad_brand      varchar2(50),
mouse_pad_qty        number(4,2),
printer_serialno     varchar2(50),
printer_brand        varchar2(50),
scanner_serialno     varchar2(50),
scanner_brand        varchar2(50),
photocopy_serialno   varchar2(50),
photocopy_brand      varchar2(50),
pen_drive_serialno   varchar2(50),
pen_drive_brand      varchar2(50),
modem_brand          varchar2(50),
modem_qty            number(4,2),
email                varchar2(100),
fax_no               varchar2(50),
telephone            varchar2(50),
ext_no               varchar2(50),
ip_address           varchar2(50),
anti_virus           varchar2(50),
warranty_period      number(4,2),
anti_virus_desc      varchar2(100),
software_desc        varchar2(100),
net_access           varchar2(1),
wifi_access          varchar2(1),
software_access      varchar2(1),
status               varchar2(1),
flag                 varchar2(1),
remk                 varchar2(150),
user_id              varchar2(6),
wk_dt                varchar2(20)    default  to_char(sysdate,'DD-MON-RR HH24:MI:SS') 


)


/


create table computer_type
(
computer_code       varchar2(30),  ---pk
computer_nm         varchar2(50),
computer_typ        varchar2(50)
)
/
alter table computer_type add constraint computer_type_pk primary key(computer_code)
/




create view emp_mast_vw
(
com_cd                 varchar2(2),
emp_id                 varchar2(6),
emp_nm                 varchar2(50),
desg_cd                varchar2(3),
loc_cd                 varchar2(2),
cost_sl                varchar2(2),
dept_cd                varchar2(2),
sec_cd                 varchar2(2),
join_dt                date,
resig_dt               date,
sal                    varchar2(1),
flag                   varchar2(1),
conf_fg                varchar2(1),
wk_dt                  date,
user_id                varchar2(6),
div_cd                 varchar2(2),
emp_type               varchar2(1),
mgr_id                 varchar2(6)
)