Home » SQL & PL/SQL » SQL & PL/SQL » Methods
Methods [message #219049] Mon, 12 February 2007 12:46
Zimmyantz
Messages: 1
Registered: February 2007
Junior Member
Hi, ok sorry...wish someone had asked me earlier to be a little clearer....

Its very much object orientated.....

I've started off with an EER....done my tables/keys and now i have to write methods for the tables.

my tables look like this:

--create equipment table
CREATE TABLE equipment
(
equipment_id NUMERIC (4) primary key, --set equipment_id to be primary key
manufacturer_id NUMERIC (4), --fk
sub_equipment_id NUMERIC (4), --fk
fault_status_id NUMERIC (4), --fk
usage_id NUMERIC (4), --fk
connection_id NUMERIC (4), --fk
maintenance_id NUMERIC (4), --fk
log_id NUMERIC (4), --fk
equipment_name VARCHAR2 (15),
comments VARCHAR2 (30),

--Adding foreign key constraints for equipment table
constraint fk_manufacturer_id foreign key (manufacturer_id) references manufacturer(manufacturer_id),
constraint fk_sub_equipment_id foreign key (sub_equipment_id) references sub_equipment(sub_equipment_id),
constraint fk_fault_status_id foreign key (fault_status_id) references fault_status(fault_status_id),
constraint fk_usage_id foreign key (usage_id) references usages(usage_id),
constraint fk_connection_id foreign key (connection_id) references connection_status(connection_id),
constraint fk_maintenance_id foreign key (maintenance_id) references maintenance(maintenance_id),
constraint fk_log_id foreign key (log_id) references logger(log_id)
);

--create sub_equipment table
CREATE TABLE sub_equipment
(
sub_equipment_id NUMERIC (4) primary key, --set sub_equipment_id to be primary key
maufacturer_id NUMERIC (4), --fk
equipment_id NUMERIC (4), --fk
maintenance_id NUMERIC (4), --fk
fault_status_id NUMERIC (4), --fk
usage_id NUMERIC (4), --fk
connection_id NUMERIC (4), --fk
log_id NUMERIC (4), --fk
sub_name VARCHAR2 (15),
sub_type VARCHAR2 (15),
comments VARCHAR2 (30)

--Adding foreign key constraints for sub_equipment table
constraint fk_manufacturer_id foreign key (manufacturer_id) references manufacturer(manufacturer_id),
constraint fk_equipment_id foreign key (equipment_id) references equipment(equipment_id),
constraint fk_maintenance_id foreign key (maintenance_id) references maintenance(maintenance_id),
constraint fk_fault_status_id foreign key (fault_status_id) references fault_status(fault_status_id),
constraint fk_usage_id foreign key (usage_id) references usages(usage_id),
constraint fk_connection_id foreign key (connection_id) references connection_status(connection_id),
constraint fk_log_id foreign key (log_id) references logger(log_id)
);

--create manufacturer table
CREATE TABLE manufacturer
(
maufacturer_id NUMERIC (4) primary key, --set manufacturer_id to be primary key
maintenance_id NUMERIC (4), --fk
equipment_id NUMERIC (4), --fk
sub_equipment_id NUMERIC (4), --fk
details VARCHAR2 (30)

--Adding foreign key constraints for manufacturer table
constraint fk_maintenance_id foreign key (maintenance_id) references maintenance(maintenance_id),
constraint fk_equipment_id foreign key (equipment_id) references equipment(equipment_id),
constraint fk_sub_equipment_id foreign key (sub_equipment_id) references sub_equipment(sub_equipment_id)
);

--create connection status table
CREATE TABLE connection_status
(
connection_id NUMERIC (4) primary key, --set connection_id to be primary key
equipment_id NUMERIC (4), --fk
sub_equipment_id NUMERIC (4), --fk
maintenance_id NUMERIC (4), --fk
fault_status_id NUMERIC (4), --fk
usage_id NUMERIC (4), --fk
connected VARCHAR2 (15)

--Adding foreign key constraints for connection_status table
constraint fk_equipment_id foreign key (equipment_id) references equipment(equipment_id),
constraint fk_sub_equipment_id foreign key (sub_equipment_id) references sub_equipment(sub_equipment_id),
constraint fk_maintenance_id foreign key (maintenance_id) references maintenance(maintenance_id),
constraint fk_fault_status_id foreign key (fault_status_id) references fault_status(fault_status_id),
constraint fk_usage_id foreign key (usage_id) references usages(usage_id)
);

--create fault status table
CREATE TABLE fault_status
(
fault_status_id NUMERIC (4) primary key, --set fault_status_id to be primary key
equipment_id NUMERIC (4), --fk
sub_equipment_id NUMERIC (4), --fk
maintenance_id NUMERIC (4), --fk
log_id NUMERIC (4), --fk
fault_name VARCHAR2 (15),
fault_type VARCHAR2 (15),
fault_fix VARCHAR2 (15)

--Adding foreign key constraints for fault_status table
constraint fk_equipment_id foreign key (equipment_id) references equipment(equipment_id),
constraint fk_sub_equipment_id foreign key (sub_equipment_id) references sub_equipment(sub_equipment_id),
constraint fk_maintenance_id foreign key (maintenance_id) references maintenance(maintenance_id),
constraint fk_log_id foreign key (log_id) references logger(log_id)
);

--create usages table
CREATE TABLE usages
(
usage_id NUMERIC (4) primary key, --set usage_id to be primary key
equipment_id NUMERIC (4), --fk
sub_equipment_id NUMERIC (4), --fk
maintenance_id NUMERIC (4), --fk
log_id NUMERIC (4), --fk
current_usage VARCHAR2 (15),
recc_usage VARCHAR2 (15),
pause_usage VARCHAR2 (15)

--Adding foreign key constraints for usages table
constraint fk_equipment_id foreign key (equipment_id) references equipment(equipment_id),
constraint fk_sub_equipment_id foreign key (sub_equipment_id) references sub_equipment(sub_equipment_id),
constraint fk_maintenance_id foreign key (maintenance_id) references maintenance(maintenance_id),
constraint fk_log_id foreign key (log_id) references logger(log_id)
);

--create maintenance table
CREATE TABLE maintenance
(
maintenance_id NUMERIC (4) primary key, --set maintenance_id to be primary key
fault_status_id NUMERIC (4), --fk
log_id NUMERIC (4), --fk
equipment_id NUMERIC (4), --fk
sub_equipment_id NUMERIC(4), --fk
manufacturer_id NUMERIC (4), --fk
main_details VARCHAR2 (15),
main_date VARCHAR2 (15),
comments VARCHAR2 (15)

--Adding foreign key constraints for maintenance table
constraint fk_fault_status_id foreign key (fault_status_id) references fault_status(fault_status_id),
constraint fk_log_id foreign key (log_id) references logger(log_id),
constraint fk_equipment_id foreign key (equipment_id) references equipment(equipment_id),
constraint fk_sub_equipment_id foreign key (sub_equipment_id) references sub_equipment(sub_equipment_id),
constraint fk_manufacturer_id foreign key (manufacturer_id) references manufacturer(manufacturer_id)
);

--create log table for all equipment / maintenance / usage logs
CREATE TABLE logger
(
log_id NUMERIC (4) primary key, --set log_id to be primary key
equipment_id NUMERIC (4), --fk
sub_equipment_id NUMERIC (4), --fk
usage_id NUMERIC (4), --fk
maintenance_id NUMERIC (4), --fk
fault_stauts_id NUMERIC (4), --fk
name VARCHAR2 (15),
details VARCHAR2 (15),
comments VARCHAR2 (30)

--Adding foreign key constraints for logger table
constraint fk_equipment_id foreign key (equipment_id) references equipment(equipment_id),
constraint fk_sub_equipment_id foreign key (sub_equipment_id) references sub_equipment(sub_equipment_id),
constraint fk_usage_id foreign key (usage_id) references usages(usage_id),
constraint fk_maintenance_id foreign key (maintenance_id) references maintenance(maintenance_id),
constraint fk_fault_status_id foreign key (fault_status_id) references fault_status(fault_status_id)
);



This is the code i have already done, i now have to invoke a method of doing the following:

--initialise a log into the logger table and record entry for initial counter(usage) reading, which should be 0

--update the accumulated hours of usage for a machine and all of its connected sub-components (update the usage counter)

--connect / disconnect a sub-component (set usage counter to pause)

[Updated on: Mon, 12 February 2007 14:20]

Report message to a moderator

Previous Topic: instr function
Next Topic: Handle ORA 28002 Password expires in X days
Goto Forum:
  


Current Time: Thu Feb 13 11:06:55 CST 2025