DDL_43_60.txt

--------LOCATION------

CREATE TABLE location
(
location_id VARCHAR2(20) NOT NULL,
division VARCHAR2(30) NOT NULL,
district VARCHAR2(30) NOT NULL,
thana VARCHAR2(30),
zip_code INTEGER NOT NULL,

CONSTRAINT PK_location PRIMARY KEY(location_id)
);

----------PROFILE---------

CREATE TABLE profile
(
profile_no VARCHAR2(20) NOT NULL,
first_name VARCHAR2(30) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
gender VARCHAR2(20) NOT NULL,
date_of_birth DATE,
profile_picture VARCHAR2(200),
phone_no VARCHAR(11) NOT NULL,
location_id VARCHAR2(20) NOT NULL,
CONSTRAINT PK_profile PRIMARY KEY(profile_no),
CONSTRAINT FK_profile_location_id FOREIGN KEY(location_id) REFERENCES location(location_id),
CONSTRAINT VALID_profile_gender CHECK(gender IN('MALE','FEMALE','OTHERS'))
);


------ORGANIZATION----------

CREATE TABLE organization
(
organization_id VARCHAR2(20) NOT NULL, 
organization_name VARCHAR2(30) NOT NULL,
organization_type VARCHAR2(30),
location_id VARCHAR2(20) NOT NULL,
CONSTRAINT PK_organization PRIMARY KEY(organization_id),
CONSTRAINT FK_organization_location_id FOREIGN KEY(location_id) REFERENCES location(location_id));

---------INSTITUTION----------

CREATE TABLE institution
(
institution_id VARCHAR2(20) NOT NULL, 
institution_name VARCHAR2(30) NOT NULL,
institution_type VARCHAR2(30),
location_id VARCHAR2(20) NOT NULL,
CONSTRAINT PK_institution PRIMARY KEY(institution_id),
CONSTRAINT FK_institution_location_id FOREIGN KEY(location_id) REFERENCES location(location_id)
);

-------INSTITION_PROFILE_RELATION--------

CREATE TABLE education_history
(
profile_no VARCHAR2(20) NOT NULL,
institution_id VARCHAR2(20) NOT NULL,
faculty VARCHAR2(30) NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
result VARCHAR2(30),
CONSTRAINT PK_edu_history PRIMARY KEY(profile_no,institution_id),
CONSTRAINT FK_edu_history_profile_no FOREIGN KEY(profile_no) REFERENCES profile(profile_no),
CONSTRAINT FK_edu_history_inst_id FOREIGN KEY(institution_id) REFERENCES institution(institution_id)
);

----------ORGANIZATION_PROFILE_RELATION-------

CREATE TABLE work_history 
(
profile_no VARCHAR2(20) NOT NULL,
organization_id VARCHAR2(20) NOT NULL,
position VARCHAR2(30),
start_date DATE NOT NULL,
end_date DATE,
salary NUMBER NOT NULL,
CONSTRAINT PK_work_history PRIMARY KEY(profile_no,organization_id),
CONSTRAINT FK_work_history_profile_no FOREIGN KEY(profile_no) REFERENCES profile(profile_no),
CONSTRAINT FK_work_history_org_id FOREIGN KEY(organization_id) REFERENCES organization(organization_id)
);

----------ACCOUNT-----------

CREATE TABLE account
(
 username VARCHAR2(20) NOT NULL,
 email VARCHAR2(30) NOT NULL,
 password VARCHAR2(30) NOT NULL,
 profile_no VARCHAR2(20) NOT NULL,
 CONSTRAINT PK_account PRIMARY KEY(username),
 CONSTRAINT UNIQUE_account_email UNIQUE(email),
 CONSTRAINT FK_account_profile_no FOREIGN KEY(profile_no) REFERENCES profile(profile_no)  
);


-----------ADVERTISEMENT-----------------

CREATE TABLE advertisement
(
advertisement_id VARCHAR2(20) NOT NULL,
advertisement_type VARCHAR2(30),
payment_amount NUMBER,
payment_system VARCHAR2(30) NOT NULL,
ad_time DATE,
username VARCHAR2(20) NOT NULL,
transaction VARCHAR2(80) NOT NULL,

CONSTRAINT PK_advertisement PRIMARY KEY(advertisement_id),
CONSTRAINT FK_advertisement_username FOREIGN KEY(username) REFERENCES account(username)
);


-------PRODUCT---------

CREATE TABLE product
(
product_id VARCHAR2(20) NOT NULL, 
product_name VARCHAR2(30) NOT NULL, 
price NUMBER NOT NULL,
description VARCHAR2(300) NOT NULL, 
contact_no VARCHAR(11) NOT NULL,

advertisement_id VARCHAR2(20) NOT NULL,

CONSTRAINT PK_product PRIMARY KEY(product_id),
CONSTRAINT CHECK_product_price CHECK(price>0),
CONSTRAINT FK_product_advertisement_id FOREIGN KEY(advertisement_id) REFERENCES advertisement(advertisement_id)
);

------IMAGES--------

CREATE TABLE image
(
image_id VARCHAR2(20) NOT NULL, 
image_url VARCHAR2(200) NOT NULL, 
product_id VARCHAR2(20) NOT NULL, 

CONSTRAINT PK_image PRIMARY KEY(image_id),
CONSTRAINT FK_image_product_id FOREIGN KEY(product_id) REFERENCES product(product_id)
);

---------PROCDUCT_DEVICES--------

CREATE TABLE devices
(
product_id VARCHAR2(20) NOT NULL,
device_catagory VARCHAR2(30) NOT NULL,
brand VARCHAR2(30) NOT NULL,
model VARCHAR2(30) NOT NULL,
generation VARCHAR2(20),
features VARCHAR2(500),
condition VARCHAR2(30) NOT NULL,
authenticity VARCHAR2(30) NOT NULL,

CONSTRAINT PK_devices PRIMARY KEY(product_id),
CONSTRAINT FK_devices_product_id FOREIGN KEY(product_id) REFERENCES product(product_id)

);

---------PROCDUCT_PET--------

CREATE TABLE pet
(
product_id VARCHAR2(20) NOT NULL,
pet_type VARCHAR2(30) NOT NULL,
color VARCHAR2(30) ,
age NUMBER NOT NULL,
gender VARCHAR2(20) NOT NULL,
food_habit VARCHAR2(200) NOT NULL,

CONSTRAINT PK_pet PRIMARY KEY(product_id),
CONSTRAINT FK_pet_product_id FOREIGN KEY(product_id) REFERENCES product(product_id)

);

---------PROCDUCT_BOOK--------

CREATE TABLE book
(
product_id VARCHAR2(20) NOT NULL,
writer VARCHAR2(30) NOT NULL,
genre VARCHAR2(30) NOT NULL,
condition VARCHAR2(200) NOT NULL,

CONSTRAINT PK_book PRIMARY KEY(product_id),
CONSTRAINT FK_book_product_id FOREIGN KEY(product_id) REFERENCES product(product_id)

);

---------PROCDUCT_COURSE--------

CREATE TABLE course
(
product_id VARCHAR2(20) NOT NULL,
course_title VARCHAR2(50) NOT NULL,
organization VARCHAR2(30) NOT NULL,

CONSTRAINT PK_course PRIMARY KEY(product_id),
CONSTRAINT FK_course_product_id FOREIGN KEY(product_id) REFERENCES product(product_id)

);

---------PROCDUCT_TUTION--------

CREATE TABLE tution
(
product_id VARCHAR2(20) NOT NULL,
tution_subject VARCHAR2(50) NOT NULL,
time_duration  VARCHAR2(30) NOT NULL,
tutor_gender VARCHAR2(20) NOT NULL, 
education_level VARCHAR2(30) NOT NULL,

CONSTRAINT PK_tution PRIMARY KEY(product_id),
CONSTRAINT FK_tution_product_id FOREIGN KEY(product_id) REFERENCES product(product_id),
CONSTRAINT VALID_tution_gender CHECK(tutor_gender IN('MALE','FEMALE','OTHERS'))
);

----------JOB--------------

CREATE TABLE job
(
job_id VARCHAR2(20) NOT NULL,
job_type VARCHAR2(30) NOT NULL,
salary NUMBER NOT NULL,
designation VARCHAR2(30),
business_function VARCHAR2(30),
description VARCHAR2(300),
required_experience VARCHAR2(30),
gender_preference VARCHAR2(10),
minimum_qualification VARCHAR2(30),
skills_summary VARCHAR2(1000),

advertisement_id VARCHAR2(20) NOT NULL, 

CONSTRAINT PK_job PRIMARY KEY(job_id),
CONSTRAINT FK_job_advertisement_id FOREIGN KEY(advertisement_id) REFERENCES advertisement(advertisement_id),
CONSTRAINT CHECK_job_salary CHECK(salary>0)
);


----------------CHAT-----------------------

CREATE TABLE chat
(
chat_id VARCHAR2(20) NOT NULL,
message_content VARCHAR2(1000) NOT NULL,
time DATE NOT NULL,

sender_username VARCHAR2(20) NOT NULL,
receiver_username VARCHAR2(20) NOT NULL,



CONSTRAINT PK_chat PRIMARY KEY(chat_id),
CONSTRAINT FK_chat_sender_username FOREIGN KEY(sender_username) REFERENCES account(username),
CONSTRAINT FK_chat_receiver_username FOREIGN KEY(receiver_username) REFERENCES account(username)
);







--------LOCATION------
ALTER TABLE location MODIFY (district VARCHAR2(100));
ALTER TABLE location MODIFY (thana VARCHAR2(100));


----------PROFILE---------
ALTER TABLE profile MODIFY (first_name VARCHAR2(100));
ALTER TABLE profile MODIFY (first_name VARCHAR2(100));


------ORGANIZATION----------
ALTER TABLE organization MODIFY (organization_name VARCHAR2(500));
ALTER TABLE organization MODIFY (organization_type VARCHAR2(200));


---------INSTITUTION----------
ALTER TABLE institution MODIFY (institution_name VARCHAR2(500));
ALTER TABLE institution MODIFY (institution_type VARCHAR2(200));


-------INSTITION_PROFILE_RELATION--------
ALTER TABLE education_history MODIFY (result VARCHAR2(100));
ALTER TABLE education_history MODIFY (faculty VARCHAR2(100));


----------ORGANIZATION_PROFILE_RELATION-------
ALTER TABLE work_history MODIFY (position VARCHAR2(100));


----------ACCOUNT-----------
ALTER TABLE ACCOUNT MODIFY (EMAIL VARCHAR2(100));


-------PRODUCT---------
ALTER TABLE PRODUCT MODIFY (PRODUCT_NAME VARCHAR2(200));
ALTER TABLE PRODUCT MODIFY (DESCRIPTION VARCHAR2(1000));


---------PROCDUCT_DEVICES--------
ALTER TABLE DEVICES MODIFY (MODEL VARCHAR2(200));
ALTER TABLE DEVICES MODIFY (FEATURES VARCHAR2(1000));


---------PROCDUCT_PET--------
ALTER TABLE PET MODIFY (FOOD_HABIT VARCHAR2(500));


---------PROCDUCT_BOOK--------
ALTER TABLE BOOK MODIFY (WRITER VARCHAR2(500));
ALTER TABLE BOOK MODIFY (GENRE VARCHAR2(200));



---------PROCDUCT_COURSE--------
ALTER TABLE COURSE MODIFY (course_title VARCHAR2(500));
ALTER TABLE COURSE MODIFY (organization VARCHAR2(500));


---------PROCDUCT_TUTION--------
ALTER TABLE tution MODIFY (tution_subject VARCHAR2(500));
ALTER TABLE tution MODIFY (education_level VARCHAR2(500));
ALTER TABLE tution MODIFY (time_duration VARCHAR2(500));


----------JOB--------------
ALTER TABLE job MODIFY (designation VARCHAR2(200));
ALTER TABLE job MODIFY (DESCRIPTION VARCHAR2(1000));
ALTER TABLE job MODIFY (required_experience VARCHAR2(100));
ALTER TABLE job MODIFY (minimum_qualification VARCHAR2(200));



-----------CHAT--------------------------------------
ALTER TABLE chat MODIFY (message_content VARCHAR2(2000));