مشروع مشتريات

السلام عليكم ورحمة الله وبركاته

تحية طيبة اما بعد ..
ارجو التكرم بمساعدتي في مشروعي هذا وهو مشروع بسيط ولقد بدأت به ووجدت بعض الصعوبه في البرمجه حيث اختصاصي هو لغة الفجول بيسك وليس الأوراكل ولكني سوف اعمل المشروع بالأوراكل
المشروع ببساطه هو مشتريات ومبيعات
حيث هناك عدة جداول للزبائن والموضفين والأقسام والمناطق والمحلات وايضا هناك جدول للمواد وسوف ارفق جميع هذه الجداول على امل المساعده

الجداول هي كالآتي:

1- DEPARTMENT TABLE:
======================

SQL> DESC DEP
Name Null? Type
------------------------------- -------- ----
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(25)
REGION_ID NUMBER(7)

*****************************************************

2- REGION TABLE:
======================

SQL> DESC REGION
Name Null? Type
------------------------------- -------- ----
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(50)

*****************************************************

3- EMPLOYEE TABLE:
======================

CREATE TABLE EMPLOYEE(
ID NUMBER(7) PRIMARY KEY,
NAME VARCHAR2(25) NOT NULL,
AGE NUMBER(2),
GENDER CHAR(1),
PHONE VARCHAR2(12),
JOB VARCHAR2(25) REFERENCES TITLE(TITLE),
SAL NUMBER(11,2),
JOIN_DT DATE,
DEPT_ID NUMBER(7) REFERENCES DEP(ID));

Table created.

SQL> DESC EMPLOYEE
Name Null? Type
------------------------------- -------- ----
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(25)
AGE NUMBER(2)
GENDER CHAR(1)
PHONE VARCHAR2(12)
JOB VARCHAR2(25)
SAL NUMBER(11,2)
JOIN_DT DATE
DEPT_ID NUMBER(7)

*****************************************************

4- MEMBERS TABLE:
======================

SQL> CREATE TABLE MEMBERS(
2 ID NUMBER(7) PRIMARY KEY,
3 NAME VARCHAR2(25) NOT NULL,
4 GENDER CHAR(1),
5 PHONE VARCHAR2(12),
6 ADDRESS VARCHAR2(50),
7 CITY VARCHAR2(25),
8 DISCOUNT NUMBER(2,2),
9 NAT VARCHAR2(20),
10 REGION_ID NUMBER(7) REFERENCES REGION(ID));

Table created.

SQL> DESC MEMBERS
Name Null? Type
------------------------------- -------- ----
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(25)
GENDER CHAR(1)
PHONE VARCHAR2(12)
ADDRESS VARCHAR2(50)
CITY VARCHAR2(25)
DISCOUNT NUMBER(2,2)
NAT VARCHAR2(20)
REGION_ID NUMBER(7)

*****************************************************

5- ITEMS TABLE:
======================

CREATE TABLE ITEMS(
ID NUMBER(7) PRIMARY KEY,
NAME VARCHAR2(25) NOT NULL,
ITM_DESC VARCHAR2(50),
STOR_QTY NUMBER(7),
COST_PRICE NUMBER(5,2),
PRICE NUMBER(5,2),
ITM_PIC LONG RAW)
/

Table created.

SQL> DESC ITEMS
Name Null? Type
------------------------------- -------- ----
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(25)
ITM_DESC VARCHAR2(50)
STOR_QTY NUMBER(7)
COST_PRICE NUMBER(5,2)
PRICE NUMBER(5,2)
ITM_PIC LONG RAW

*****************************************************

6- SUPPLIERS TABLE:
======================

SQL> CREATE TABLE SUPPLIERS(
2 ID NUMBER(7) PRIMARY KEY,
3 NAME VARCHAR2(25) NOT NULL,
4 ADDRESS VARCHAR2(50),
5 PHONE VARCHAR2(15),
6 SHIP_DATE DATE);

Table created.

SQL> DESC SUPPLIERS
Name Null? Type
------------------------------- -------- ----
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(25)
ADDRESS VARCHAR2(50)
PHONE VARCHAR2(15)
SHIP_DATE DATE

*****************************************************

7- SALES TABLE:
======================

SQL> CREATE TABLE SALES(
2 INV_NO NUMBER(7) PRIMARY KEY,
3 ITM_ID NUMBER(7) REFERENCES ITEMS(ID),
4 MEM_ID NUMBER(7) REFERENCES MEMBERS(ID),
5 PRICE NUMBER(5,2),
6 QTY NUMBER(7),
7 SALE_DATE DATE,
8 TOTAL NUMBER(7,2));

Table created.

SQL> DESC SALES
Name Null? Type
------------------------------- -------- ----
INV_NO NOT NULL NUMBER(7)
ITM_ID NUMBER(7)
MEM_ID NUMBER(7)
PRICE NUMBER(5,2)
QTY NUMBER(7)
SALE_DATE DATE
TOTAL NUMBER(7,2)

*****************************************************

8- TITLE TABLE:
======================

SQL> DESC TITLE
Name Null? Type
------------------------------- -------- ----
TITLE NOT NULL VARCHAR2(25)

*****************************************************

9- WAREHOUSE TABLE:
======================

SQL> CREATE TABLE WAREHOUSE(
2 ID NUMBER(7) PRIMARY KEY,
3 NAME VARCHAR2(25) NOT NULL,
4 LOCATION VARCHAR2(25),
5 FLAG CHAR(2));

Table created.

SQL> DESC WAREHOUSE
Name Null? Type
------------------------------- -------- ----
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(25)
LOCATION VARCHAR2(25)
FLAG CHAR(2)

*****************************************************

10- TRANSACTION TABLE:
======================

SQL> CREATE TABLE TRANSACTION(
2 ID NUMBER(7) PRIMARY KEY,
3 ITM_ID NUMBER(7) REFERENCES ITEMS(ID),
4 SUP_ID NUMBER(7) REFERENCES SUPPLIERS(ID),
5 WH_ID NUMBER(7) REFERENCES WAREHOUSE(ID),
6 QTY NUMBER(7),
7 COST_PRICE NUMBER(5,2),
8 SHIPPED_DT DATE);

Table created.

SQL> DESC TRANSACTION
Name Null? Type
------------------------------- -------- ----
ID NOT NULL NUMBER(7)
ITM_ID NUMBER(7)
SUP_ID NUMBER(7)
WH_ID NUMBER(7)
QTY NUMBER(7)
COST_PRICE NUMBER(5,2)
SHIPPED_DT DATE

وفي الأخير اشكركم جزيل الشكر واتأسف اذا قد اخذت من وقتكم الثمين
وادعو من الله ان يوفقكم ويوفقنا
وشكرا