/*
Created		2010/11/29
Modified		2011/01/08
Project		
Model		
Company		
Author		
Version		
Database		Oracle 10g 
*/


Drop table payments CASCADE CONSTRAINTS PURGE
/
Drop table deputation CASCADE CONSTRAINTS PURGE
/
Drop table clauses CASCADE CONSTRAINTS PURGE
/
Drop table contract_cluses CASCADE CONSTRAINTS PURGE
/
Drop table customers CASCADE CONSTRAINTS PURGE
/
Drop table contracts CASCADE CONSTRAINTS PURGE
/
Drop table employees CASCADE CONSTRAINTS PURGE
/
Drop table real_state_details CASCADE CONSTRAINTS PURGE
/
Drop table cities CASCADE CONSTRAINTS PURGE
/
Drop table nighbourhood CASCADE CONSTRAINTS PURGE
/
Drop table real_state CASCADE CONSTRAINTS PURGE
/


-- Create Types section


-- Create Tables section


Create table real_state (
	reael_esatat_num Varchar2 (10) NOT NULL ,
	customer_id Number NOT NULL ,
	nighb_num Number NOT NULL ,
	num_of_cities Number NOT NULL ,
	num_of_apar_ Number,
	north_ Varchar2 (100),
	west_ Varchar2 (100),
	east_ Varchar2 (100),
	south Varchar2 (100),
	block_num Number,
	real_estste_name_ Varchar2 (30),
	water_account_num_ Number,
	build_type Varchar2 (30),
	land_num_Number Varchar2 (30),
	evaluation_price Number,
	ADDING_DATE Date,
	dircteOrUndirct Varchar2 (30),
	endPrice Number,
	real_estate_type Varchar2 (30),
	for_rent_or_bay Varchar2 (30),
	Direction Varchar2 (10),
	area Number,
	num_of_shops Number,
primary key (reael_esatat_num) 
) 
/

Create table nighbourhood (
	nighb_num Number NOT NULL ,
	nighb_Name Varchar2 (30),
primary key (nighb_num) 
) 
/

Create table cities (
	num_of_cities Number NOT NULL ,
	cityName Varchar2 (30),
primary key (num_of_cities) 
) 
/

Create table real_state_details (
	elec_acc_num Number NOT NULL ,
	number_of_rooms Number,
	living_rooms Number,
	number_of_kitch Number,
	num_entries Number,
	reael_esatat_num Varchar2 (10) NOT NULL ,
	d_area Number,
	main_strate Varchar2 (100),
	sacandry_str Varchar2 (100),
	yearly_rent Varchar2 (100),
primary key (elec_acc_num) 
) 
/

Create table employees (
	emlployee_id Number NOT NULL ,
	first_name Varchar2 (30),
	second_name Varchar2 (30),
	last_name Varchar2 (30),
	user_name Varchar2 (30),
	password Varchar2 (30),
	job Varchar2 (30),
primary key (emlployee_id) 
) 
/

Create table contracts (
	contract_num Number NOT NULL ,
	contract_date_ Date,
	end_of_contract Date,
	way_of_payments Varchar2 (30),
	date_of_due Date,
	emlployee_id Number NOT NULL ,
	reael_esatat_num Varchar2 (10) NOT NULL ,
primary key (contract_num) 
) 
/

Create table customers (
	customer_id Number NOT NULL ,
	first_name Varchar2 (30),
	second_name Varchar2 (30),
	last_name Varchar2 (30),
	address Varchar2 (100),
	nationality Varchar2 (30),
	customer_type Varchar2 (30),
primary key (customer_id) 
) 
/

Create table contract_cluses (
	contract_num Number NOT NULL ,
	clause_id Number NOT NULL ,
primary key (contract_num,clause_id) 
) 
/

Create table clauses (
	clause_id Number NOT NULL ,
	clauses_text Varchar2 (100),
primary key (clause_id) 
) 
/

Create table deputation (
	depu_num Number NOT NULL ,
	depu_start_date Date,
	depu_end_date Date,
	emlployee_id Number NOT NULL ,
	reael_esatat_num Varchar2 (10) NOT NULL ,
primary key (depu_num) 
) 
/

Create table payments (
	rec_num Number NOT NULL ,
	Payments_no Number,
	rec_amount Number,
	rec_date Date,
	pay_type Varchar2 (30),
	rec_type Varchar2 (30),
	emlployee_id Number NOT NULL ,
	contract_num Number NOT NULL ,
primary key (rec_num) 
) 
/


-- Create Indexes section


-- Create Foreign keys section

Alter table real_state_details add  foreign key (reael_esatat_num) references real_state (reael_esatat_num) 
/

Alter table deputation add  foreign key (reael_esatat_num) references real_state (reael_esatat_num) 
/

Alter table contracts add  foreign key (reael_esatat_num) references real_state (reael_esatat_num) 
/

Alter table real_state add  foreign key (nighb_num) references nighbourhood (nighb_num) 
/

Alter table real_state add  foreign key (num_of_cities) references cities (num_of_cities) 
/

Alter table contracts add  foreign key (emlployee_id) references employees (emlployee_id) 
/

Alter table deputation add  foreign key (emlployee_id) references employees (emlployee_id) 
/

Alter table payments add  foreign key (emlployee_id) references employees (emlployee_id) 
/

Alter table contract_cluses add  foreign key (contract_num) references contracts (contract_num) 
/

Alter table payments add  foreign key (contract_num) references contracts (contract_num) 
/

Alter table real_state add  foreign key (customer_id) references customers (customer_id) 
/

Alter table contract_cluses add  foreign key (clause_id) references clauses (clause_id) 
/


-- Create Object Tables section


-- Create XMLType Tables section


-- Create Functions section


-- Create Views section


-- Create Sequences section


-- Create Packages section


-- Create Synonyms section


-- Create Table comments section


-- Create Attribute comments section









--===================================
CREATE SEQUENCE cities1
START WITH 1 
INCREMENT BY 1 
NOMAXVALUE

NOCYCLE;

--========================
CREATE SEQUENCE N1SEQ
START WITH 1 
INCREMENT BY 1 
NOMAXVALUE 
NOCYCLE;




--=====================
insert into employees(emlployee_id,first_name,second_name,last_name,user_name,password,job)
values
(1234567890,'fatimah','ali','faqihi','faf','fa','mang');
 
Insert into customers(customer_id,first_name,second_name,last_name,address,
nationality,customer_type)values(1234657901,'خالد','عبدالله','محمد','الرياض',
'سعودي','مالك');

Insert into customers(customer_id,first_name,second_name,last_name,address,
nationality,customer_type)values(1234657902,'سالم','علي','سالم','الرياض',
'سعودي','مالك');

Insert into customers(customer_id,first_name,second_name,last_name,address,
nationality,customer_type)values(125467901,'احمد','علي','محمد','الرياض',
'سعودي','مالك');

Insert into customers(customer_id,first_name,second_name,last_name,address,
nationality,customer_type)values(5674657903,'فهد','صالح','محمد','الرياض',
'سعودي','مالك');
--=============================================================

Insert Into cities(num_of_cities,cityName)values(cities1.NEXTVAL,'الرياض');
Insert Into cities(num_of_cities,cityName)values(cities1.NEXTVAL,'مكة');
Insert Into cities(num_of_cities,cityName)values(cities1.NEXTVAL,'جدة');
--====================================================================
Insert Into nighbourhood(nighb_num,nighb_Name)values(n1SEQ.NEXTVAL,'الشفا');
Insert Into nighbourhood(nighb_num,nighb_Name)values(n1SEQ.NEXTVAL,'الروضه');
Insert Into nighbourhood(nighb_num,nighb_Name)values(n1SEQ.NEXTVAL,'البديعه');
--===================================================================
Insert INTO REAL_STATE(
REAEL_ESATAT_NUM,
CUSTOMER_ID,
 NIGHB_NUM,
 NUM_OF_CITIES,
 NUM_OF_APAR_, 
NORTH_, 
WEST_,
EAST_, 
SOUTH,  
 BLOCK_NUM, 
REAL_ESTSTE_NAME_,
WATER_ACCOUNT_NUM_ ,
 BUILD_TYPE, 
LAND_NUM_NUMBER, 
EVALUATION_PRICE,  
ADDING_DATE, 
DIRCTEORUNDIRCT,
 ENDPRICE, 
REAL_ESTATE_TYPE,
FOR_RENT_OR_BAY, 
DIRECTION, 
AREA, 
NUM_OF_SHOPS)values
(1233,
1234657902 ,
1,
1,
4,
'شارع الملك عبد الله بطول 20 متر','شارع الملك فهد بطول 20 متر','شارع حليمة السعدية','شارع موسى بن نصير',
12,
null,
12345,
'مسلح',
null,
200000,
sysdate,
'مباشر',
350000,
'عمارة',
'للبيع',
'شرقية',
'400',
3
);
--================================================================
Insert INTO REAL_STATE(
REAEL_ESATAT_NUM,
CUSTOMER_ID,
 NIGHB_NUM,
 NUM_OF_CITIES,
 NUM_OF_APAR_, 
NORTH_, 
WEST_,
EAST_, 
SOUTH,  
 BLOCK_NUM, 
REAL_ESTSTE_NAME_,
WATER_ACCOUNT_NUM_ ,
 BUILD_TYPE, 
LAND_NUM_NUMBER, 
EVALUATION_PRICE,  
ADDING_DATE, 
DIRCTEORUNDIRCT,
 ENDPRICE, 
REAL_ESTATE_TYPE,
FOR_RENT_OR_BAY, 
DIRECTION, 
AREA, 
NUM_OF_SHOPS)values
(1239,
125467901 ,
1,
3,
4,
'شارع خديجة بنت خويلد بطول 30 متر','شارع الضباب بطول 30 متر','شارع حليمة السعدية بطول 30 متر','شارع موسى بن نصير بطول 30 متر',
12,
null,
12349,
'مسلح',
null,
200000,
sysdate,
'مباشر',
350000,
'فيلا',
'للبيع',
'شرقية',
'900',
1
);
--======================================================
Insert INTO REAL_STATE(
REAEL_ESATAT_NUM,
CUSTOMER_ID,
 NIGHB_NUM,
 NUM_OF_CITIES,
 NUM_OF_APAR_, 
NORTH_, 
WEST_,
EAST_, 
SOUTH,  
 BLOCK_NUM, 
REAL_ESTSTE_NAME_,
WATER_ACCOUNT_NUM_ ,
 BUILD_TYPE, 
LAND_NUM_NUMBER, 
EVALUATION_PRICE,  
ADDING_DATE, 
DIRCTEORUNDIRCT,
 ENDPRICE, 
REAL_ESTATE_TYPE,
FOR_RENT_OR_BAY, 
DIRECTION, 
AREA, 
NUM_OF_SHOPS)values
(1230,
125467901 ,
3,
2,
4,
'شارع عائشة بطول 30متر','شارع الإمام محمد بن سعود30 متر','شارع علي بن ابي طالب 30 متر','شارع طارق بن زياد بطول 30 متر',
12,
null,
12349,
'مسلح',
null,
200000,
sysdate,
'مباشر',
350000,
'ارض',
'تأجير',
'غربية',
'900',
1
);
select* from real_state;
--=================================================
Insert INTO REAL_STATE(
REAEL_ESATAT_NUM,
CUSTOMER_ID,
 NIGHB_NUM,
 NUM_OF_CITIES,
 NUM_OF_APAR_, 
NORTH_, 
WEST_,
EAST_, 
SOUTH,  
 BLOCK_NUM, 
REAL_ESTSTE_NAME_,
WATER_ACCOUNT_NUM_ ,
 BUILD_TYPE, 
LAND_NUM_NUMBER, 
EVALUATION_PRICE,  
ADDING_DATE, 
DIRCTEORUNDIRCT,
 ENDPRICE, 
REAL_ESTATE_TYPE,
FOR_RENT_OR_BAY, 
DIRECTION, 
AREA, 
NUM_OF_SHOPS)values
(1238,
125467901 ,
2,
1,
4,
'شارع عائشة بطول 30متر','شارع الإمام عبد الرحمن بن عوف 30 متر','شارع الهيثم30 متر','شارع ابن سينا بطول 30 متر',
12,
null,
12349,
'مسلح',
null,
200000,
sysdate,
'مباشر',
500000,
'عمارة',
'إدارة املاك',
'غربية',
'900',
1
);

--===========================================
insert into real_state_details(
elec_acc_num ,
	number_of_rooms ,
	living_rooms ,
	number_of_kitch ,
	num_entries ,
	reael_esatat_num  ,
	d_area ,
	main_strate ,
	sacandry_str ,
	yearly_rent,
)values
1234,
3,
2,
1,
null,
1238,
200,
'شارع عائشة',
'شارع الهيثم',
'20000'
);
--===============================================
insert into real_state_details(

	elec_acc_num ,
	number_of_rooms ,
	living_rooms ,
	number_of_kitch ,
	num_entries ,
	reael_esatat_num  ,
	d_area ,
	main_strate ,
	sacandry_str ,
	yearly_rent,
)values
1234,
3,
2,
1,
null,
1238,
200,
'شارع عائشة',
'شارع الإمام عبد الرحمن بن عوف ',
'25000',

);
--===================================================
insert into real_state_details(

	elec_acc_num ,
	number_of_rooms ,
	living_rooms ,
	number_of_kitch ,
	num_entries ,
	reael_esatat_num  ,
	d_area ,
	main_strate ,
	sacandry_str ,
	yearly_rent,
)values
1234,
3,
2,
1,
null,
1238,
200,
'شارع ابن سينا',
'شارع الإمام عبد الرحمن بن عوف ',
'15000',

);
--=========================================
insert into real_state_details(

	elec_acc_num ,
	number_of_rooms ,
	living_rooms ,
	number_of_kitch ,
	num_entries ,
	reael_esatat_num  ,
	d_area ,
	main_strate ,
	sacandry_str ,
	yearly_rent,
)values
1237,
3,
2,
1,
null,
1238,
200,
'شارع عائشة',
'شارع الإمام عبد الرحمن بن عوف ',
'25000',

);
--============================================

insert into real_state_details(

	elec_acc_num ,
	number_of_rooms ,
	living_rooms ,
	number_of_kitch ,
	num_entries ,
	reael_esatat_num  ,
	d_area ,
	main_strate ,
	sacandry_str ,
	yearly_rent,
)values
1236,
3,
2,
1,
null,
1238,
200,
'شارع عائشة',
'شارع الإمام عبد الرحمن بن عوف ',
'25000',

);
--=========================================
insert into real_state_details(

	elec_acc_num ,
	number_of_rooms ,
	living_rooms ,
	number_of_kitch ,
	num_entries ,
	reael_esatat_num  ,
	d_area ,
	main_strate ,
	sacandry_str ,
	yearly_rent,
)values(
1235,
null,
null,
null,

3,
1238,
100,
'شارع عائشة',
'شارع الإمام عبد الرحمن بن عوف ',
'25000');

=============================================================
SELECT * FROM PAYMENTS;
=============================================================
INSERT INTO PAYMENTS(REC_NUM,PAYMENTS_NO,REC_AMOUNT,REC_DATE,PAY_TYPE,REC_TYPE,EMLPLOYEE_ID,CONTRACT_NUM)
VALUES(2,2,5700,'1/2/2011','CHECK','RECIEPT','1234567','12345678');
========================================================================
INSERT INTO PAYMENTS(REC_NUM,PAYMENTS_NO,REC_AMOUNT,REC_DATE,PAY_TYPE,REC_TYPE,EMLPLOYEE_ID,CONTRACT_NUM)
VALUES(3,4,6700,'1/3/2011','CHECK','RECIEPT','17896','5678');
=========================================================================
INSERT INTO PAYMENTS(REC_NUM,PAYMENTS_NO,REC_AMOUNT,REC_DATE,PAY_TYPE,REC_TYPE,EMLPLOYEE_ID,CONTRACT_NUM)
VALUES(4,5,6700,'1/3/2011','CASH','RECIEPT','17896','5678');
===========================================================================
insert into employees(emlployee_id,first_name,second_name,last_name,user_name,
password,job)
values(123456789,'ahmed','mohammad','bugumy','ahmed22','22ahmed','manager');
===========================================================================
insert into employees(emlployee_id,first_name,second_name,last_name,user_name,
password,job)
values(08976589,'mahmood','mohammad','faris','faris2211','112faris','clerck');
==============================================================================
Insert into contracts(contract_num,contract_date_,end_of_contract,way_of_payments,date_of_due,emlployee_id,reael_esatat_num )
values(CONTSEQ.NEXTVAL,SYSDATE,SYSDATE+30,'كاش',SYSDATE,
'1234657901',3456789);
===========================================================================