College of computing and Informatics
Department of Computer Science
Advanced database Individual Assignment Lab
Oracle
Section One
NAME ID_NO
1 AFENDI MOHAMMED MENGISTU 0597/16
SUBMITTED TO: MR BAHAR
SUBMISSION DATE : MAY 15 2025
-- Create the schema (user)
CREATE USER hararsenior_secondary_school IDENTIFIED BY password;
-- Grant necessary privileges to the new user
GRANT CONNECT, RESOURCE, DBA TO hararsenior_secondary_school;
-- Connect as the new user
-- ALTER SESSION SET CURRENT_SCHEMA = hararsenior_secondary_school;
-- 1. Create Employee object type
CREATE OR REPLACE TYPE Employee AS OBJECT (
employee_id NUMBER,
name VARCHAR2(100),
gender VARCHAR2(10),
salary NUMBER,
project_id NUMBER, -- Foreign key to Project
MEMBER PROCEDURE hire(new_name IN VARCHAR2, new_gender IN VARCHAR2, new_salary IN NUMBER,
new_project_id IN NUMBER),
MEMBER PROCEDURE fire,
MEMBER FUNCTION calculate_bonus(overtime_hours IN NUMBER) RETURN NUMBER,
MEMBER PROCEDURE display_employee_id,
MEMBER PROCEDURE display_employee_info
);
-- 2. Create Project object type with budget and funds management
CREATE OR REPLACE TYPE Project AS OBJECT (
project_id NUMBER,
project_name VARCHAR2(100),
location VARCHAR2(100),
contract_duration DATE,
budget NUMBER, -- Total project budget
funds_available NUMBER, -- Available funds for the project
MEMBER FUNCTION cancel_contract RETURN VARCHAR2,
MEMBER FUNCTION calculate_budget_status RETURN VARCHAR2, -- Calculate budget status
MEMBER PROCEDURE add_funds(amount IN NUMBER), -- Add funds to the project
MEMBER PROCEDURE display_project_info
);
-- 3. Create Contractor object type
CREATE OR REPLACE TYPE Contractor AS OBJECT (
contractor_id NUMBER,
name VARCHAR2(100),
license_level VARCHAR2(20),
MEMBER PROCEDURE take_project_contract(new_project_id IN NUMBER),
MEMBER PROCEDURE extend_contract(extension_date IN DATE),
MEMBER PROCEDURE level_up_license(new_license_level IN VARCHAR2),
MEMBER PROCEDURE display_contractor_info
);
-- 4. Create Material object type
CREATE OR REPLACE TYPE Material AS OBJECT (
equipment_id NUMBER,
equipment_name VARCHAR2(100),
manufacture_date DATE,
MEMBER PROCEDURE add_material(purchase_date IN DATE),
MEMBER PROCEDURE delete_material(sell_date IN DATE),
MEMBER PROCEDURE display_material_info
);
-- 5. Create Tables for Employee, Project, Contractor, and Material (using Object Types)
-- Employee Table
CREATE TABLE Employee_table OF Employee (
CONSTRAINT emp_pk PRIMARY KEY (employee_id)
);
-- Project Table
CREATE TABLE Project_table OF Project (
CONSTRAINT proj_pk PRIMARY KEY (project_id)
);
-- Contractor Table
CREATE TABLE Contractor_table OF Contractor (
CONSTRAINT cont_pk PRIMARY KEY (contractor_id)
);
-- Material Table
CREATE TABLE Material_table OF Material (
CONSTRAINT mat_pk PRIMARY KEY (equipment_id)
);
-- Implement the Procedures and Functions for the Object Types
-- 1. Employee Object Procedures and Functions
CREATE OR REPLACE TYPE BODY Employee AS
MEMBER PROCEDURE hire(new_name IN VARCHAR2, new_gender IN VARCHAR2, new_salary IN NUMBER,
new_project_id IN NUMBER) IS
BEGIN
name := new_name;
gender := new_gender;
salary := new_salary;
project_id := new_project_id;
END hire;
MEMBER PROCEDURE fire IS
BEGIN
name := NULL;
gender := NULL;
salary := NULL;
project_id := NULL;
END fire;
MEMBER FUNCTION calculate_bonus(overtime_hours IN NUMBER) RETURN NUMBER IS
BEGIN
-- Assuming bonus is 10% of salary for every 10 hours of overtime
RETURN (salary * 0.1) * (overtime_hours / 10);
END calculate_bonus;
MEMBER PROCEDURE display_employee_id IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_id);
END display_employee_id;
MEMBER PROCEDURE display_employee_info IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_id);
DBMS_OUTPUT.PUT_LINE('Name: ' || name);
DBMS_OUTPUT.PUT_LINE('Gender: ' || gender);
DBMS_OUTPUT.PUT_LINE('Salary: ' || salary);
DBMS_OUTPUT.PUT_LINE('Project ID: ' || project_id);
END display_employee_info;
END;
-- 2. Project Object Functions
CREATE OR REPLACE TYPE BODY Project AS
MEMBER FUNCTION cancel_contract RETURN VARCHAR2 IS
BEGIN
IF contract_duration < SYSDATE THEN
RETURN 'Project contract has ended and is canceled.';
ELSE
RETURN 'Project contract is still active.';
END IF;
END cancel_contract;
MEMBER FUNCTION calculate_budget_status RETURN VARCHAR2 IS
BEGIN
IF funds_available >= budget THEN
RETURN 'Sufficient funds available for the project.';
ELSIF funds_available < budget THEN
RETURN 'Insufficient funds. Project requires additional funding.';
ELSE
RETURN 'Project budget has not been set yet.';
END IF;
END calculate_budget_status;
MEMBER PROCEDURE add_funds(amount IN NUMBER) IS
BEGIN
funds_available := funds_available + amount;
DBMS_OUTPUT.PUT_LINE('Funds added: ' || amount || '. New available funds: ' || funds_available);
END add_funds;
MEMBER PROCEDURE display_project_info IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Project ID: ' || project_id);
DBMS_OUTPUT.PUT_LINE('Project Name: ' || project_name);
DBMS_OUTPUT.PUT_LINE('Location: ' || location);
DBMS_OUTPUT.PUT_LINE('Contract Duration: ' || TO_CHAR(contract_duration, 'DD-MON-YYYY'));
DBMS_OUTPUT.PUT_LINE('Budget: ' || budget);
DBMS_OUTPUT.PUT_LINE('Funds Available: ' || funds_available);
END display_project_info;
END;
-- 3. Contractor Object Procedures
CREATE OR REPLACE TYPE BODY Contractor AS
MEMBER PROCEDURE take_project_contract(new_project_id IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Contractor ' || name || ' takes the project contract with ID ' ||
new_project_id);
END take_project_contract;
MEMBER PROCEDURE extend_contract(extension_date IN DATE) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Contract for contractor ' || name || ' extended until ' ||
TO_CHAR(extension_date, 'DD-MON-YYYY'));
END extend_contract;
MEMBER PROCEDURE level_up_license(new_license_level IN VARCHAR2) IS
BEGIN
license_level := new_license_level;
DBMS_OUTPUT.PUT_LINE('Contractor ' || name || ' license level upgraded to ' || license_level);
END level_up_license;
MEMBER PROCEDURE display_contractor_info IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Contractor ID: ' || contractor_id);
DBMS_OUTPUT.PUT_LINE('Contractor Name: ' || name);
DBMS_OUTPUT.PUT_LINE('License Level: ' || license_level);
END display_contractor_info;
END;
-- 4. Material Object Procedures
CREATE OR REPLACE TYPE BODY Material AS
MEMBER PROCEDURE add_material(purchase_date IN DATE) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Material ' || equipment_name || ' added on ' || TO_CHAR(purchase_date,
'DD-MON-YYYY'));
END add_material;
MEMBER PROCEDURE delete_material(sell_date IN DATE) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Material ' || equipment_name || ' sold on ' || TO_CHAR(sell_date, 'DD-MON-
YYYY'));
END delete_material;
MEMBER PROCEDURE display_material_info IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Equipment ID: ' || equipment_id);
DBMS_OUTPUT.PUT_LINE('Equipment Name: ' || equipment_name);
DBMS_OUTPUT.PUT_LINE('Manufacture Date: ' || TO_CHAR(manufacture_date, 'DD-MON-YYYY'));
END display_material_info;
END;
-- Example Insert for Employee, Project, Contractor, and Material
-- Insert a Project with budget and funds available
INSERT INTO Project_table VALUES (
1,
'School Building',
'Location A',
TO_DATE('2025-12-31', 'YYYY-MM-DD'),
1000000, -- Budget set to 1 million
500000 -- Initial funds available is 500,000
);
-- Insert an Employee
INSERT INTO Employee_table VALUES (1, 'John Doe', 'Male', 5000, 1);
-- Insert a Contractor
INSERT INTO Contractor_table VALUES (1, 'Jane Smith', 'Intermediate');
-- Insert Material
INSERT INTO Material_table VALUES (1, 'Excavator', TO_DATE('2023-10-15', 'YYYY-MM-DD'));
-- Commit the transaction
COMMIT;
-- Example Usage: Display the budget status and add funds
DECLARE
emp Employee;
proj Project;
cont Contractor;
mat Material;
BEGIN
-- Create an Employee instance
emp := Employee(1, 'John Doe', 'Male', 5000, 1);
-- Display Employee Info
emp.display_employee_info;
-- Create a Project instance and display info
proj := Project(1, 'School Building', 'Location A', TO_DATE('2025-12-31', 'YYYY-MM-DD'), 1000000, 500000);
proj.display_project_info;
-- Create a Contractor instance and display info
cont := Contractor(1, 'Jane Smith', 'Intermediate');
cont.display_contractor_info;
-- Create a Material instance and display info
mat := Material(1, 'Excavator', TO_DATE('2023-10-15', 'YYYY-MM-DD'));
mat.display_material_info;
END;
/
