Oracle Database Objects: A Practical Guide to Object Types - Prof. Bahar, Cheat Sheet of Database Management Systems (DBMS)

Download Oracle Database Objects: A Practical Guide to Object Types - Prof. Bahar and more Cheat Sheet Database Management Systems (DBMS) in PDF only on Docsity!

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/

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,

-- 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);