Database design & analysis

 ‫א‬‫א‬‫א‬ ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹] ‫א‬‫א‬‫א‬‫א‬

 ‫א‬‫א‬

١٦٢

 

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 

 W،،‫א‬‫א‬،‫א‬ 

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬   ‫א‬ ‫א‬  ،‫א‬    ‫א‬ ‫א‬ ‫א‬ ‫א‬ ‫א‬ 

‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬

،‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

 K ‫א‬‫א‬‫א‬ 

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

  ‫א‬        ،‫א‬ ‫א‬‫א‬   ‫א‬  ‫א‬ ‫א‬ ‫א‬ ‫א‬ ‫א‬   ‫א‬    ، 

‫א‬،‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

     ‫א‬  ‫א‬   ،‫א‬     K‫א‬‫א‬،‫א‬‫א‬ 

‫א‬???‫א‬‫א‬?‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ 

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ،‫א‬،‫א‬‫א‬‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬ 

،‫א‬‫א‬‫א‬‫א‬

 K‫א‬ 

‫א‬‫א‬‫א‬‫א‬

 ‫א‬‫א‬‫א‬ ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹] ‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬

١

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 W‫א‬‫א‬  ‫א‬‫א‬‫א‬  W‫א‬‫א‬  ‫א‬‫א‬ K١  ‫א‬‫א‬‫א‬ K٢



 W‫א‬‫א‬‫א‬  K٪١٠٠‫א‬‫א‬‫א‬   W‫א‬‫א‬     W‫א‬‫א‬  H   W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬ 

-١-

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬W‫א‬

  ‫א‬‫א‬‫א‬KK 

‫א‬K‫א‬‫א‬KKK‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬  ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬K‫א‬‫א‬‫א‬

‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

 K‫א‬‫א‬



  ‫א‬‫א‬  ‫؟‬EDatabase Management Information SystemFDBMS‫א‬‫א‬ ،  ‫א‬‫א‬‫א‬ ‫א‬‫א‬  

،‫א‬‫א‬‫א‬‫א‬

‫א‬ ،‫א‬ ‫א‬‫א‬

 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

-٢-

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 ‫א‬  W‫א‬ WEHardwareF‫א‬‫ א‬J ١ ‫א‬، ‫א‬ ‫א‬،  ‫א‬‫א‬‫א‬‫א‬  K‫א‬KKK‫א‬‫א‬‫א‬ WESoftwareF‫ א‬J ٢  W،‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬W‫א‬ J   KLinux, Unix, WindowsK…‫א‬

Oracle, ‫א‬‫א‬‫א‬‫א‬W‫א‬ J  KKKKSybase, DB2

‫א‬‫א‬‫א‬ ‫א‬‫א‬W‫א‬‫א‬‫א‬‫א‬‫א‬‫ א‬J  KKKK‫א‬‫א‬‫א‬  W‫א‬‫א‬‫א‬W‫ א‬J ٣

‫א‬ ‫א‬‫א‬‫א‬‫א‬  ‫א‬W‫א‬ J  W‫א‬

K‫א‬‫א‬‫א‬‫א‬‫א‬ K١ K‫א‬‫א‬‫א‬‫א‬ K٢ K‫א‬ K٣

W‫א‬‫א‬‫א‬‫א‬W‫א‬ J  K‫א‬‫א‬‫א‬ K١

K‫א‬‫א‬‫א‬ K٢ K‫א‬‫א‬ K٣

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K٤ K‫א‬

-٣-

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

‫א‬‫ א‬E  ‫א‬F  ‫א‬  ‫א‬  J  W‫א‬

‫א‬‫א‬‫א‬‫א‬ K١ K‫א‬‫א‬‫א‬ K٢ K‫א‬‫א‬ K٣

‫א‬‫א‬‫א‬‫א‬ K٤ K‫א‬‫א‬‫א‬‫א‬‫א‬

K‫א‬‫א‬ K٥ ‫א‬‫א‬‫א‬‫א‬W‫א‬‫ א‬J  W‫א‬

‫א‬‫א‬E  F ‫א‬‫א‬ K١ K‫א‬

K‫א‬‫א‬‫א‬ K٢

K‫א‬‫א‬‫א‬‫א‬ K٣

  ‫א‬‫א‬‫א‬  ‫א‬W‫א‬‫א‬J ‫هـ‬ K‫א‬KKK‫א‬،‫א‬،‫א‬،‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬  ‫א‬‫א‬W‫א‬‫א‬‫א‬‫ א‬J ٤ K‫א‬

 K‫א‬‫א‬‫א‬‫א‬W‫ א‬J ٥

‫א‬‫א‬‫א‬‫א‬  K‫א‬‫א‬‫א‬

-٤-

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 W‫א‬‫א‬  ،‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬ K E  ‫א‬F  ‫א‬‫א‬  ‫א‬

  ‫א‬‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬  K  W‫א‬‫א‬ W‫א‬‫א‬‫א‬‫ א‬J ١

 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J  ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬ J  K‫א‬‫א‬‫א‬‫א‬‫א‬

K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J  ‫א‬ ‫א‬‫א‬‫א‬W‫א‬ J ٢

‫א‬‫א‬‫א‬‫א‬   ‫א‬

W‫א‬ ‫א‬ F  ‫א‬‫א‬ ‫א‬ J  WE‫א‬‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K١

‫א‬‫א‬ K٢ K‫א‬

‫א‬‫א‬ K٣ K ‫א‬‫א‬‫א‬، ‫א‬‫א‬‫א‬‫א‬‫א‬ K٤ KKKK  ‫א‬‫א‬ ‫א‬‫א‬،‫א‬، ‫א‬‫א‬‫א‬

K

KEDBMSF‫א‬‫א‬‫ א‬J  -٥-

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

KEDBMSF‫א‬‫א‬‫א‬‫א‬ J  ‫א‬‫א‬‫א‬‫א‬‫א‬‫ א‬J  KEDBMSF‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬W ‫א‬ J ٣ KKKK‫א‬‫א‬‫א‬‫א‬‫א‬

W‫א‬‫א‬ J ٤ K‫א‬‫א‬ J   K‫א‬‫א‬‫א‬‫א‬ J  ،KK‫א‬‫א‬‫א‬‫א‬‫א‬W‫א‬‫א‬ J ٥  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

 ‫א‬‫א‬W ‫א‬J ٦ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

K‫א‬ 

-٦-

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 

‫؟‬‫א‬‫א‬‫א‬ J ١

‫א‬‫א‬‫א‬‫א‬ J  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬J  K‫א‬‫א‬ J 

 W J ٢

 ‫א‬

 ‫א‬‫א‬  ‫؟‬‫א‬ J ٣

 K‫א‬‫ א‬J ٤  K‫א‬‫א‬،‫א‬‫א‬‫ א‬J ٥



  

-٧-

 ‫א‬‫א‬‫א‬ ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹] ‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬

٢

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬‫א‬

 ١٦٢

 

 W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬   W‫א‬‫א‬ K‫א‬‫א‬‫א‬  KE‫א‬F‫א‬‫א‬‫א‬‫א‬

 KE‫א‬F‫א‬‫א‬‫א‬‫א‬‫א‬   W‫א‬‫א‬‫א‬  K٪١٠٠‫א‬‫א‬‫א‬‫א‬   W‫א‬‫א‬  K   W‫א‬‫א‬  KH   W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬

-٨-

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬‫א‬

 ١٦٢

 

 W‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬Codd‫א‬١٩٧٠‫א‬‫א‬‫א‬

K‫א‬‫א‬‫א‬ ‫א‬‫א‬ ‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬ERelationsFETablesF‫א‬‫א‬

‫א‬KE‫א‬F‫א‬ 

  ‫א‬،EF‫א‬ 

‫א‬‫א‬ERecords or TupleF ‫א‬  ‫א‬K‫א‬

 ‫א‬ ‫א‬ EDomainFEAttributesF‫א‬‫א‬ KK‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬

‫א‬ ‫א‬‫א‬‫א‬‫א‬EPrimary KeyF

‫א‬‫א‬ ‫א‬‫א‬K‫א‬‫א‬E‫א‬F  KERelational DatabaseF‫א‬ 

 ‫א‬EStudentF‫א‬‫א‬‫א‬ Student‫א‬‫ א‬J

K‫א‬  J K‫א‬St _No‫א‬‫ א‬J K‫א‬‫א‬Dept_Code‫ א‬J

J ٢٠٠١‫א‬‫א‬‫א‬‫א‬‫א‬‫ א‬EGpaF‫א‬ J K١٠J ٠١

Gpa‫א‬W‫א‬ J

‫א‬‫א‬‫א‬‫א‬ Dept_Code‫א‬K٥KK١

K‫א‬

-٩-

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬‫א‬

 ١٦٢

 

‫א‬‫א‬K‫א‬‫א‬ J W‫א‬‫א‬

Student St _No J ٠١J ٢٠٠١

St _Name Sami

Gpa ٣{٧٥

Birth_Date ١٩٨١J ٠١J ٠١

Dept_Code Comp

J ٠٢J ٢٠٠١

Khalid

٣{٥

١٩٨٢J ١٠J ١٠

Math

Ali

٤{٢

١٩٨٠J ٠٨J ١٢

Comp

١٠

٩٩ J ٠١J ٢٠٠٠

١٠١



- ١٠ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬‫א‬

 ١٦٢

 

 WE‫א‬F‫א‬‫א‬ ‫א‬ ‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  W‫א‬‫א‬

‫א‬‫א‬  ‫א‬WESuper KeyF‫א‬‫ א‬J  ‫א‬‫א‬K‫א‬‫א‬‫א‬

K

St_No St_No, St _Name St_No ,dept_code ‫א‬‫א‬E‫א‬F‫א‬WECandidate KeyF‫א‬‫ א‬J 

 ‫א‬‫א‬ KENullF‫א‬ St_No, St _Name

St_No 

KSt_No،،‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬WEPrimary KeyF‫א‬‫ א‬J  K St_NoK‫א‬‫א‬

،‫א‬W  ‫א‬‫ א‬J  Customer _id‫א‬‫א‬ E>‫؟‬ ‫א‬F‫א‬

‫א‬‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬

K Customer _id Customer name

tel

Address

WEForeign KeyF‫א‬‫א‬J 

EStudentF‫א‬ EDept_CodeF‫א‬EUniqueF  EDepartmentF‫א‬EForeign KeyF - ١١ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬‫א‬

 ١٦٢

 

 WE‫א‬F‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  W‫א‬‫א‬K‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬WE١W١F‫א‬‫א‬ K١ ‫א‬‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬  K‫א‬ Person

1

1

 - ١٢ -

Passport

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬‫א‬

 ١٦٢

 

‫א‬‫א‬ ‫א‬E١WNNW١F‫א‬ ‫א‬ K٢

K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

   ‫א‬‫א‬‫א‬ 

K‫א‬‫א‬

n

Student

1

Department

 ‫א‬‫א‬‫א‬‫א‬WEN:NF K٣

K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬





‫א‬   

 K 

Student

n

n



- ١٣ -

Class

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬‫א‬

 ١٦٢

 

   W J ١

 ‫א‬‫ א‬

 ‫א‬‫ א‬  ‫א‬‫ א‬

K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J ٢ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J ٣  K

 ‫א‬‫א‬ J ٤  EN :NF‫א‬‫א‬ E NW١F‫א‬

 EN :NF 

- ١٤ -

 ‫א‬‫א‬‫א‬ ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹] ‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬

٣

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 W‫א‬‫א‬  ‫א‬‫א‬‫א‬‫א‬  K‫א‬‫א‬

  K‫א‬‫א‬‫א‬

 W‫א‬‫א‬

K‫א‬‫א‬   W‫א‬‫א‬‫א‬  K٪١٠٠‫א‬   W‫א‬‫א‬  K٤   W‫א‬‫א‬  H    W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

- ١٥ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 W ‫א‬ ،‫א‬‫א‬ ‫א‬‫א‬

‫א‬K‫א‬‫א‬‫א‬

K ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  ‫א‬‫א‬‫א‬‫א‬ K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  ‫א‬‫א‬ Entity

‫א‬‫א‬‫א‬‫א‬  ‫א‬‫א‬‫א‬

KRelationship (ER)Diagram   W‫א‬  ‫؟‬‫א‬

 K‫א‬‫א‬EF‫א‬ ‫א‬K‫א‬

  ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬ ‫א‬‫א‬‫א‬K  ‫א‬‫א‬

‫א‬‫א‬K‫א‬‫א‬‫א‬

E ‫א‬F‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

،‫א‬‫א‬E ‫א‬F‫א‬E‫א‬F‫א‬

‫א‬‫א‬E‫א‬F‫א‬‫א‬‫א‬‫א‬‫א‬  K‫א‬‫א‬‫א‬



‫ﻣﺨﻄﻂ ﻣﻨﺰل‬



- ١٦ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 W‫א‬‫א‬  ‫א‬‫א‬‫א‬  W‫א‬‫א‬‫א‬KK‫א‬

EObjectsF  ‫א‬‫א‬  ‫א‬EEntity SetF  ‫א‬  EEntityF ‫א‬K‫א‬‫א‬‫א‬ ، ‫א‬،‫א‬K ‫א‬‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬K‫א‬‫א‬‫א‬

‫א‬  ‫א‬K  ‫א‬  K‫א‬

Patient ‫ﻣﺮﻳﺾ‬

Student ‫ﻃﺎﻟﺐ‬

 ‫א‬،‫א‬  ‫א‬ WEAttributesF‫א‬‫א‬ KK ‫א‬‫א‬‫א‬‫א‬‫א‬

،‫א‬،‫א‬‫א‬،‫א‬،‫א‬،‫א‬،‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬  K‫א‬،‫א‬

 K

Stu_address

Stu_name

Stu_major

Student

 ‫א‬ ‫א‬‫א‬ WEDomainF‫א‬ ٣٠‫א‬‫א‬،    ‫א‬ 

‫א‬KE٣{٥F  ٥KK٠‫א‬‫א‬ ‫א‬، ‫א‬K ٢٢‫א‬‫א‬    K‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

Eprimary keyF‫א‬‫א‬E‫א‬F‫א‬ - ١٧ -

K‫א‬

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

Car_model

Car_year

car Car_Code

 ‫א‬

 WSimple and Composite Attributes‫א‬‫א‬‫א‬J  K ‫א‬‫א‬،  ‫א‬ ،‫א‬،‫א‬،‫א‬F‫א‬‫א‬،E‫א‬‫א‬،‫א‬،‫א‬‫א‬F

   ‫א‬KKE‫א‬

 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ Fname

Init

Lname

Name

Student

 WSingle-Valued or Multiple-Valued Attributes‫א‬J  ‫א‬E‫א‬،‫א‬F‫א‬‫א‬‫א‬

  E‫א‬‫א‬،‫א‬،  F ‫א‬ ‫א‬K    

 K‫א‬‫א‬‫א‬‫א‬‫א‬ Car_Col or

car

 - ١٨ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 WEDerived AttributesF‫א‬‫א‬ ‫א‬  ‫א‬‫א‬  ‫א‬ ‫א‬ K‫א‬‫א‬‫א‬‫א‬

K‫א‬‫א‬‫א‬‫א‬  K‫א‬–‫א‬‫א‬Z‫א‬

ِAge

Student

 K ‫א‬ ‫א‬‫א‬

‫א‬‫א‬‫א‬ ‫א‬‫א‬   K‫א‬‫א‬‫א‬

 WERelationshipsF‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

KEKKKK،،،F‫א‬‫א‬

‫א‬‫א‬‫א‬K K‫א‬‫א‬‫א‬‫א‬

 KEN:NFEN :1F‫א‬E١W١F‫א‬‫א‬ K‫א‬‫א‬ N

Student

N Enroll



- ١٩ -

Class

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

   ‫א‬‫א‬  ‫א‬ W‫א‬‫א‬

‫א‬ 

‫א‬‫א‬K‫א‬‫א‬‫א‬،‫א‬‫א‬

E‫א‬F ‫א‬‫א‬ ‫א‬ ‫א‬‫א‬

‫א‬‫א‬K ‫א‬

‫א‬‫א‬F‫א‬ ‫א‬‫א‬‫א‬‫א‬  KE‫א‬‫א‬‫א‬ No

Employee

Name

Relationship D_Name

Dependent

B_date

 : ESupertype and SubtypeF‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ SupertypeE  F  ‫א‬ ‫א‬‫א‬ ‫א‬‫א‬‫א‬

  Account   ‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬،‫א‬  K‫א‬‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬W

EOverdraft AmountF‫א‬ECurrent AccountF‫א‬‫א‬W    ‫א‬

‫א‬‫א‬K   ‫א‬  KEInterest RateF‫א‬‫א‬

KEISAF‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ - ٢٠ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 





:ERecursiveF‫א‬ ‫א‬‫א‬‫א‬،‫א‬‫א‬‫א‬ E ‫א‬‫א‬F   E‫א‬F‫א‬

- ٢١ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

F ‫א‬‫א‬W‫א‬  ‫א‬‫א‬‫א‬KEER Diagram‫א‬‫א‬

 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

  F‫א‬‫א‬  ‫א‬ J ١ ‫א‬‫א‬‫א‬‫א‬،E‫א‬ ‫א‬

 K‫א‬،‫א‬‫א‬‫א‬‫א‬

K‫א‬‫ א‬J ٢ ‫א‬‫א‬   ‫א‬ J ٣ ‫א‬،  ‫א‬‫א‬F‫א‬‫א‬

K‫א‬‫א‬‫א‬‫א‬،‫א‬،E‫א‬‫א‬‫א‬‫א‬،‫א‬‫א‬F

‫א‬‫א‬ ‫א‬‫א‬F‫ א‬J ٤ K‫א‬‫א‬‫א‬E‫א‬

،‫א‬،‫א‬،،  ‫א‬‫א‬‫א‬ J ٥ K‫א‬‫א‬  K‫א‬‫א‬‫א‬ J ٦

- ٢٢ -



 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 



- ٢٣ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 

 WJ ١

KEntity‫ א‬  KAttribute‫ א‬

KRelationship‫ א‬

 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬J ٢  ‫؟‬‫א‬‫א‬‫؟‬‫א‬‫א‬‫א‬‫א‬‫א‬J ٣  K‫א‬‫א‬‫א‬ ‫א‬J ٤  W‫א‬‫א‬‫א‬J ٥

‫א‬‫א‬J   K‫א‬‫א‬،

‫א‬‫א‬J   K‫א‬

‫א‬‫א‬‫א‬J   K‫א‬

  ‫א‬‫א‬‫א‬J ٦

‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬

 K‫א‬‫א‬‫א‬‫א‬

 W‫א‬‫א‬‫א‬J ٧

- ٢٤ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 



- ٢٥ -

 ‫א‬‫א‬‫א‬ ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹] ‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬

٤

 ‫א‬‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 W‫א‬‫א‬  K3NF‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬   : EData AnomaliesF‫א‬‫א‬‫א‬

 W‫א‬‫א‬

 ‫א‬‫א‬‫א‬

 ‫א‬‫א‬‫א‬‫א‬  ‫א‬‫א‬‫א‬‫א‬  ‫א‬‫א‬‫א‬‫א‬   W‫א‬‫א‬‫א‬  K٪١٠٠3NF‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬   W‫א‬‫א‬   

 W‫א‬‫א‬  H   W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

- ٢٦ -

 ‫א‬‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 W ‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬K‫א‬‫א‬

‫א‬KK ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

 .EThird Normal Form 3NFF‫א‬

 : EData AnomaliesF‫א‬‫א‬ Employee_department Empno Ename Job Salary Deptno Dname Loc 101 Sami clerk 3000 10 Accounting Riyadh 205 Khalid manager 2500 10 Accounting Riyadh 303 Ali salesman 1200 20 Sales Jeddah 502 Saeed salesman 2100 20 Sales Jeddah 601 Salem clerk 1000 30 Operation Dmmam ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  W‫א‬‫א‬‫א‬‫א‬‫א‬

،‫א‬‫א‬‫א‬ W‫א‬ J ١ K‫א‬‫א‬‫א‬، ٢٠‫א‬ELocF‫א‬‫א‬‫א‬ ‫א‬W ‫א‬ J ٢

‫א‬‫א‬‫א‬‫א‬‫א‬Riyadh Jeddah K ‫א‬‫א‬‫א‬ ‫א‬ K‫א‬٣٠٣‫א‬‫א‬‫א‬

٣٠٣‫א‬Jeddah ‫א‬‫א‬‫א‬ K‫א‬ ،‫א‬٣٠‫א‬W‫א‬ J ٣ K‫א‬٣٠‫א‬٦٠٦‫א‬

- ٢٧ -

 ‫א‬‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

WEFunctional Dependency FD F‫א‬‫א‬



EF EF‫א‬‫א‬ A

B

KBAA‫א‬‫א‬ B   ‫א‬‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  KEAnomaliesF

W‫א‬‫א‬ ‫א‬‫א‬W FD1 : Empno Ename FD2 : Empno Deptno  ‫א‬‫א‬‫א‬  FD1 : Empno Ename,Deptno  FD :Functional Dependency ‫א‬‫א‬ Functional F‫א‬‫א‬‫א‬‫א‬  W‫א‬‫א‬‫א‬‫א‬‫א‬EDependency FD EX‫א‬YFFXY‫א‬WReflexive ‫ א‬J ١

YX  1- X ⊇ Y : X ÆY XZ  YX‫א‬WAugmentation‫א‬ ‫א‬ J ٢ YZ   2- { XÆ Y} ╞ XZÆYZ  ZY YX‫א‬WTransitive‫א‬ J ٣ KZX  3- {XÆ Y ,YÆZ} ╞ XÆZ  ZX Y X‫א‬WUnion‫א‬ J ٤ KYZX

- ٢٨ -



 ‫א‬‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 4- {XÆ Y ,XÆZ} ╞ XÆYZ ‫א‬ Decomposition‫א‬ J ٥  5- {XÆ YZ } ╞ XÆY , X Æ Z ‫א‬ Pseudotransitive ‫א‬‫א‬‫א‬ J ٦  6- {XÆ Y , WYÆZ } ╞ WXÆY  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬╞  :EFirst Normal Form 1NF F‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  K‫א‬EF 

 WEmployee‫א‬‫א‬١ No

Name Adresse Fname Mid Lname city Street House no 100 Ali Salem musa Riyadh Immam saud 210 120 Saeed Eisa Ali Riyadh King Fahad 202  ‫א‬‫א‬‫א‬‫א‬‫א‬

‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬K‫א‬

 ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬1NF, ‫א‬‫א‬   No Fname Mid Lname city 100 Ali Salem musa Riyadh

Street House no Immam 210 saud 120 Saeed Eisa Ali Riyadh King Fahad 202 ‫א‬‫א‬‫א‬‫א‬

 K1NF ‫א‬‫א‬‫א‬

‫א‬HOURS ‫א‬‫א‬‫א‬W ٢  ‫א‬‫א‬‫א‬PROJECTS - ٢٩ -

 ‫א‬‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

NO Name Project_Code Hours Deptno 210 Ali P1,p2,p3 12,20,40 10,20,20 Research, 201 Salem 305 Ali

P1,p3 P2,p3

30,15 40,20

10,20 20,20

Dname Operation, Operation Research Operation Operation, Operation 

  ‫א‬‫א‬   ‫א‬‫א‬

 ‫א‬Deptno‫א‬Hours‫א‬Project_Code‫א‬

،‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬  K‫א‬‫א‬‫א‬‫א‬ NO Name Project_Code Hours Deptno Dname 210 Ali P1 12 10 Research 210 Ali p2 20 20 Operation 210 Ali p3 40 20 Operation 201 salem P1 30 10 Research 201 salem p3 15 20 Operation 305 Ali P2 40 20 Operation 305 Ali p3 20 20 Operation  ‫א‬

   ‫א‬ ‫א‬EPrimary KeyF  ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K FD 1 :No Æ Name  K‫א‬‫א‬ FD 2 : Project_CodeÆ Deptno K‫א‬ FD 3 : Deptno ÆDname K‫א‬‫א‬

‫א‬    ‫א‬‫א‬  ‫א‬

 K‫א‬Ali - ٣٠ -

 ‫א‬‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

P1‫א‬ ‫א‬‫א‬  K

٢٠‫א‬‫א‬‫א‬

 K‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬

‫א‬E‫א‬‫א‬F    W‫א‬

 ‫א‬‫א‬ FD 4 :No, Project_CodeÆ name FD 5 :No, Project_CodeÆ Deptno FD 6 :No, Project_CodeÆ Hours FD 7 : Deptno ÆDname  FD 8 :No, Project_CodeÆ Name ,Hours, Deptno, Dname ‫א‬‫א‬‫א‬FD1,FD2FD4,FD5 ‫א‬‫א‬‫א‬‫א‬FD5 ،‫א‬

‫א‬‫א‬،  ‫א‬  KE1NFF‫א‬‫א‬‫א‬ :ESecond Normal Form 2NF F‫א‬‫א‬‫א‬  W‫א‬‫א‬‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬ J ١ K‫א‬‫א‬‫א‬ J ٢

 ‫א‬‫א‬‫א‬‫א‬E‫א‬F‫א‬W‫א‬‫א‬

A C B  C C  B A,B ‫א‬‫א‬CCA,B   K‫א‬‫א‬‫א‬‫א‬KB‫א‬‫א‬ - ٣١ -

 ‫א‬‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 NO 210 210 210 201 201 305 305

Name Ali Ali Ali Salem Salem Ali Ali

Project_Code P1 p2 p3 P1 p3 P2 p3

Hours 12 20 40 30 15 40 20

Deptno 10 20 20 10 20 20 20

Dname Research Operation Operation Research Operation Operation Operation 

 ‫؟‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

 W‫א‬‫א‬‫א‬  ‫؟‬‫א‬‫א‬‫א‬‫א‬J ١

 K‫א‬،،

 ‫؟‬‫א‬J ٢  ‫א‬‫א‬ FD 1 :No Æ Name FD 2 : Project_CodeÆ Deptno,Dname FD 3 :No, Project_CodeÆ name ,deptno, hours ‫א‬‫א‬NameNo No, Project_Code‫א‬‫א‬

 

K‫א‬ DnamedeptnoProject_Code  ‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬

 W‫א‬‫א‬‫א‬‫א‬‫א‬

 ‫א‬‫א‬‫א‬  ‫א‬‫א‬  J ١  KE‫א‬‫א‬F

‫א‬‫א‬ ‫א‬‫א‬ J ٢  KE‫א‬‫א‬F‫א‬

 KE‫א‬F‫א‬ J ٣

W‫א‬‫א‬‫א‬‫א‬‫א‬ J ٤ - ٣٢ -

 ‫א‬‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 NO 210 210 210 201 201 305 305

Project_Code P1 p2 p3 P1 p3 P2 p3

Project_Code P1 p2 p3

Hours 12 20 40 30 15 40 20

NO 210 210 210 201 201 305 305

Deptno 10 20 20

Name Ali Ali Ali salem salem Ali Ali

Dname Research Operation Operation 

WEThird Normal Form 3NF F‫א‬‫א‬‫א‬  W‫א‬‫א‬‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬ J ١ K‫א‬‫א‬‫א‬ J ٢

 K‫א‬‫א‬‫א‬‫א‬E‫א‬F‫א‬W‫א‬‫א‬

B A C

 C B A    ‫א‬‫א‬C,BC,B A 

‫א‬‫א‬‫א‬KB‫א‬‫א‬ C

 K

 ‫؟‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  W‫א‬‫א‬‫א‬ - ٣٣ -

 ‫א‬‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 ‫؟‬‫א‬‫א‬‫א‬‫א‬‫א‬J ١  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

 ‫؟‬‫א‬J ٢

 ‫א‬‫א‬

 ‫א‬‫א‬J   FD 1 :No Æ Name  K‫א‬

 ‫א‬‫א‬J  FD 1 :No, Project_CodeÆ hours  K‫א‬

 ‫א‬‫א‬J  FD 1 : Project_CodeÆ Deptno,Dname FD 2 : Deptno Æ Dname  Deptno  ‫א‬DeptnoDnameProject_Code ‫א‬‫א‬

 ‫א‬‫א‬K ‫א‬Dname  ‫א‬ ‫א‬  ‫ א‬     ‫א‬‫א‬‫א‬  EDeptnoF‫א‬‫א‬

 K‫א‬‫א‬‫א‬‫א‬‫א‬   J ١  W‫א‬‫א‬‫א‬‫א‬‫א‬ J ٢ 

NO 210 210 210 201 201 305 305

Project_Code P1 p2 p3 P1 p3 P2 p3

Project_Code

Hours 12 20 40 30 15 40 20

NO 210 210 210 201 201 305 305

Deptno

Deptno - ٣٤ -

Name Ali Ali Ali Salem Salem Ali Ali Dname

 ‫א‬‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

P1 p2 p3

10 20

10 20 20

Research Operation

‫א‬3NF ‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬  K‫א‬‫א‬

- ٣٥ -

 ‫א‬‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 

K‫א‬ EData AnomaliesF‫א‬‫א‬‫א‬ K١ ‫؟‬EFunctional Dependency FD F‫א‬‫א‬ K٢ K‫א‬‫א‬‫א‬‫ א‬K٣ ‫א‬ K٤ K‫א‬‫א‬‫ א‬J 

K‫א‬‫א‬‫ א‬J  K‫א‬‫א‬‫ א‬J 

W  K٥ K‫א‬‫א‬‫א‬ J 

K‫א‬‫א‬‫א‬ J  K‫א‬‫א‬‫א‬ J  

‫א‬‫א‬‫؟‬‫א‬‫א‬‫א‬‫א‬‫א‬ K٦ K‫א‬‫א‬‫א‬‫א‬

Course _No

Sec_ No

Dept

Credit _Hours

Course Level

Ins_id

 ‫ א‬‫ א‬‫ א‬   ‫א‬

‫א‬

Semeste Year r

Date

Room _No



No_of _ stu

‫ א‬ ‫ א‬   ‫ א‬ 

‫ א‬‫ א‬‫א‬‫ א‬‫א‬‫א‬

‫ א‬‫א‬‫ א‬   

- ٣٦ -

 ‫א‬‫א‬‫א‬ ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹] ‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬

٥

‫א‬‫א‬

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 W‫א‬‫א‬  K‫א‬‫א‬ ‫א‬ 

 W‫א‬‫א‬  K‫א‬‫א‬‫א‬   W‫א‬‫א‬‫א‬  K٪١٠٠‫א‬‫א‬‫א‬   W‫א‬‫א‬     W‫א‬‫א‬  H   W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

- ٣٧ -

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

  E‫א‬‫א‬ ‫א‬‫א‬ ‫א‬

‫א‬‫א‬KE‫א‬‫א‬F‫א‬‫א‬

    E‫א‬ ‫א‬ F‫א‬    ‫א‬  . ‫א‬‫א‬

  WE‫א‬‫א‬F‫א‬‫א‬‫א‬

- ٣٨ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

‫א‬‫א‬

W ‫א‬ Relation (R)‫א‬ Entity (E) J ١  ‫א‬‫א‬‫א‬‫א‬‫א‬

K  ‫א‬ ‫א‬       ، 

 Employee Eno

Fname

Mid

 W‫א‬‫א‬

Lname

sex Birthdate

Salary 

 KEF‫א‬

 Department Deptno Dname Location ‫א‬EF‫א‬  KEQueryF‫א‬‫א‬

- ٣٩ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

‫א‬‫א‬





Project Pnumber

Pname

Plocation

W Weak Entity‫א‬  Relation (R)‫א‬ EWeak EntityF     ‫א‬  ‫א‬ ‫א‬  ‫א‬ ‫א‬   ‫א‬ ‫א‬   ‫א‬  ‫א‬ ‫א‬  ‫א‬    ،    ‫א‬ ‫א‬       ، ‫א‬ ‫א‬  ‫א‬ KE‫א‬‫א‬‫א‬F‫א‬‫א‬

Dependent Eno

Name

Sex Birthdate Relationship  

- ٤٠ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

‫א‬‫א‬

E ١W١F ‫א‬  ‫א‬   ‫א‬  ‫א‬       W‫א‬ 

‫א‬E NWNFE NW١F‫א‬

 W‫א‬‫א‬  E١W١F‫א‬‫א‬J ١       ‫א‬    E ١W١F ‫א‬  ‫א‬   K‫א‬‫א‬

1

1

F‫א‬‫א‬‫א‬‫א‬ EMgrF ‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬E‫א‬ Department Deptno

Dname Location Mgr Startdate

EN: 1F‫א‬ J ٢      EF   EN:1F  ‫א‬   KE١F‫א‬‫א‬‫א‬‫א‬ENF‫א‬

1

Employee Eno

N

 Fname

Mid

Lname - ٤١ -

sex

Birthdate Salary

Mgr

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

‫א‬‫א‬

 ‫א‬‫א‬  ‫א‬ ‫א‬    EMgrF     ‫א‬    E‫א‬F‫א‬ N

1

Employee Eno

Fname

Mid

Lname

sex

Birthdate Salary

Mgr Deptno

F‫א‬‫א‬‫א‬EDeptnoF ‫א‬  E‫א‬

N

1

 Project Pnumber

Pname Plocation Deptno

‫א‬ ‫א‬  ‫א‬ ‫א‬    EDeptnoF     ‫א‬  

 KE‫א‬F

- ٤٢ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

‫א‬‫א‬

 EN:NFJ ٣     ‫א‬ ‫א‬      EN:NF     

 ‫א‬K‫א‬‫א‬‫א‬ N

N





Works_for Eno Pnumber Hours  (‫א‬،‫א‬،‫א‬ )‫א‬ ‫א‬‫א‬‫א‬ ‫א‬E‫א‬،‫א‬F  K‫א‬  ‫א‬،‫א‬    ISAESuper Type F‫א‬‫א‬‫א‬ESubtypeF‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  W‫א‬

 ‫א‬  ‫א‬‫א‬ J ١

‫א‬ J ٢  K‫א‬‫א‬‫א‬

- ٤٣ -

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 W‫א‬‫א‬‫א‬ 

S_Employee

H_Employee Eno Hour_Rate

Eno



- ٤٤ -

Salary

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 

 ‫؟‬‫א‬‫א‬‫א‬ K١  KN:N‫א‬‫א‬ K٢

Super F‫א‬‫א‬‫א‬ESubtypeF‫א‬‫א‬‫א‬‫ א‬‫א‬‫א‬ K٣  KISAEType

 ‫א‬‫א‬ K٤



  K‫א‬‫א‬٦‫א‬‫א‬‫א‬‫א‬ K٥

- ٤٥ -



 ‫א‬‫א‬‫א‬ ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹] ‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬

٦

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 W‫א‬‫א‬  KPLSQL‫א‬‫א‬‫א‬‫א‬   W‫א‬‫א‬ PLSQL ‫א‬‫א‬  PLSQL ‫א‬‫א‬‫א‬

 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  K‫א‬Datatypes‫א‬‫א‬‫א‬    W‫א‬‫א‬‫א‬  K٪١٠٠‫א‬‫א‬‫א‬‫א‬‫א‬   W‫א‬‫א‬     W‫א‬‫א‬    H   W‫א‬‫א‬  K‫א‬‫א‬ ‫א‬‫א‬‫א‬SQL‫א‬‫א‬‫א‬

- ٤٧ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 W Programming Language FSQL‫א‬‫؟‬PL/SQL

‫א‬‫א‬OracleEStructured Query Language

‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  K‫א‬‫א‬SQL

 KKKK‫א‬

،Modules‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬K‫א‬‫א‬KPL/SQL  ،Modules‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬ Database Server‫א‬ ‫א‬    ‫א‬‫א‬‫א‬‫א‬‫א‬

Source ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬

‫א‬KP-Code ‫א‬Compile‫א‬Code  W‫א‬‫א‬‫א‬‫א‬‫א‬

  W‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬ ‫א‬‫א‬‫א‬WAnonymous Block  K١

‫א‬‫א‬‫א‬K K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K‫א‬ - ٤٨ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

‫א‬‫א‬،SubprogramsWNamed Block K٢ Function, Procedure, Trigger, W‫א‬‫א‬‫א‬

KPackage

‫א‬‫א‬‫א‬   ‫א‬

 KNested Blocks‫א‬‫א‬

 W (Blocks)‫א‬‫א‬  W‫א‬(Block) ‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬Declarative E‫א‬F‫א‬ K١

‫א‬‫א‬‫א‬‫א‬ECursors F‫א‬‫א‬،(Block)‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

 K‫א‬(Block) 

‫א‬‫א‬‫א‬ SQLExecutable:‫א‬‫ א‬K٢   E‫א‬،‫א‬،‫א‬‫א‬F‫א‬ ،‫א‬‫א‬،‫א‬(Block) ‫א‬‫א‬‫א‬PLSQL K‫א‬‫א‬‫א‬‫א‬‫א‬KKKKK‫א‬‫א‬

‫א‬‫א‬ ‫א‬‫א‬WEExceptionF‫א‬‫א‬‫א‬‫ א‬K٣ K‫א‬‫א‬‫א‬‫א‬‫א‬ DECLARE 

Æ

BEGIN

Æ

SQL 

Æ

PLSQL 

Æ

EXCEPTION END;

 Æ

‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬،‫א‬‫א‬ ‫א‬‫א‬‫א‬  SQL 

PLSQL

‫א‬

E‫א‬F‫א‬‫א‬‫א‬ ‫א‬‫א‬ 

 - ٤٩ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 W‫א‬‫א‬‫א‬‫א‬ F‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  WPLSQL(Block) 

 W‫א‬‫א‬‫א‬‫א‬‫א‬

 W‫א‬‫א‬ K١

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬

 W‫א‬‫א‬ K٢

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ 

 K‫א‬‫א‬‫א‬  W‫א‬ K٣

E‫א‬‫א‬F%ROWTYPE %Type‫א‬‫א‬

‫א‬    

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬  K‫א‬‫א‬‫א‬

  W‫א‬‫א‬ ‫א‬  ‫א‬‫א‬‫א‬PLSQL‫א‬‫א‬‫א‬‫א‬

  ‫א‬،  ‫א‬‫א‬‫א‬‫א‬‫א‬ PLSQL (Block)  ‫א‬

 W‫א‬‫א‬‫א‬‫א‬‫א‬ KE;F identifier [CONSTANT] datatype [NOT NULL] [:= DEFAULT | expression ] ;

- ٥٠ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬

Identifier



CONSTANT

 K

K‫א‬‫א‬CONSTANT‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬

datatype

NOT NULL‫א‬‫א‬‫א‬،

NOT NULL

 ،  PLSQL

expression

K‫א‬‫א‬

EF‫א‬ 

Declare v_name VARCHAR2(10) ; v_date

 W ‫א‬

‫א‬‫א‬

DATE ;



v_id NUMBER(2) NOT NULL  :=10 ;  ١٠‫א‬‫א‬ NULL V_comm CONSTANT NUMBER  :=120; ‫א‬‫א‬‫א‬ ١٢٠‫א‬‫א‬

BEGIN ……

END;

- ٥١ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 W‫א‬‫א‬‫א‬‫א‬‫א‬ KSQL‫א‬‫א‬‫א‬‫ א‬K١

(Block) ‫א‬‫א‬ J  K

K(Block) ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J K٣٠‫א‬ J KSQL‫א‬‫א‬‫א‬‫א‬‫א‬ J  A-Z, a-z, 0-9, _, #,$ J  CONSTANTNOT NULL‫א‬‫א‬‫א‬‫א‬‫א‬ K٢ K‫א‬‫א‬‫א‬

KDEFUALT ‫א‬‫א‬‫א‬:=‫א‬‫א‬‫א‬ K٣

K‫א‬ K٤

 W‫א‬‫א‬  ‫א‬‫א‬  K‫א‬:=‫א‬‫א‬‫א‬‫א‬ J Identifier := expression ; W v_name :=’Ali’ ; v _name Ali ‫א‬‫א‬ net_sal := v_sal –v_sal*.08;  net_sal ‫א‬ SELECT ‫א‬‫א‬‫א‬ J  J

SELECT ename INTO v_name FROM emp WHERE empno = 7788; ‫א‬ ename  ‫א‬‫א‬‫א‬‫א‬  v_name ename ‫א‬‫א‬‫א‬  K

- ٥٢ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 W Datatypes ‫א‬‫א‬‫א‬ WScalar Datatype ‫א‬‫א‬‫א‬‫א‬J ١  W‫א‬‫א‬ ‫א‬‫א‬K‫א‬‫א‬‫א‬‫א‬ ‫א‬

‫א‬ size‫א‬‫א‬‫א‬

‫א‬ VARCHAR2(size)

‫א‬K‫א‬‫א‬ Byte ٣٢{٦٧٦K‫א‬

‫א‬ ‫א‬  size‫א‬‫א‬‫א‬

CHAR[(SIZE)]

‫א‬‫א‬‫א‬K‫א‬ Byte٣٢{٦٧٦١‫א‬‫א‬ ‫א‬‫א‬Precision‫א‬‫א‬‫א‬

 NUMBER (precision,scale)

EF‫א‬‫א‬

DATE

‫א‬‫א‬scale

٩٩٩٩‫א‬٤٧١٢‫א‬‫א‬ K‫א‬

‫א‬ size‫א‬‫א‬‫א‬

LONG

‫א‬K‫א‬‫א‬ Byte٣٢{٦٧٠K‫א‬ Byte٢{١٤٧{٤٨٣{٦٤٧LONG‫א‬ K‫א‬ EBinaryF‫א‬‫א‬ TRUE,FLASE‫א‬‫א‬

LONG RAW BOOLEAN

 ‫א‬ BINARY_INTEGER

٢{١٤٧{٤٨٣{٦٤٧J ٢{١٤٧{٤٨٣{٦٤٧  ٢{١٤٧{٤٨٣{٦٤٧J ٢{١٤٧{٤٨٣{٦٤٧‫א‬ BINARY_INTEGERNUMBER

- ٥٣ -

PLS_INTEGR

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 W

v_job  VARCHAR2(9); v_count BINARY_INTEGER := 0; v_total_sal NUMBER(9,2) := 0; v_orderdate DATE := SYSDATE + 7; c_tax_rate CONSTANT NUMBER(3,2) := 8.25; v_valid BOOLEAN NOT NULL := TRUE;  ‫א‬%TYPE‫א‬‫א‬‫א‬  K‫א‬



v_enameemp.ename%TYPE; 

emp‫א‬ ename‫א‬‫א‬ v_ename‫א‬

v_balanceNUMBER(7,2);   v_bqlqnce‫א‬ v_min_balancev_balance%TYPE := 10; K١٠‫א‬‫א‬ v_balance‫א‬‫א‬ v_min_balance‫א‬  WBOOLEAN ‫א‬‫א‬‫א‬  KTRUE , FLASE, NULL ‫א‬‫א‬‫א‬J

 AND,OR ,NOT‫א‬‫א‬‫א‬‫א‬‫א‬J ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

 KFALSE TRUE(A<B)

W

V_Sal1 NUMBER: =1000 ; V_Sal2 NUMBER: =2500 ; Valid BOOLEAN:= (V_Sal1 > V_Sal2); ‫א‬V_SAL1 > V_SAL2 Valid‫א‬ KTRUE Valid‫א‬‫א‬‫א‬TRUE ‫א‬ - ٥٤ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 WComposite Datatype ‫א‬‫א‬‫א‬J ٢ 

‫א‬  RECORDS ‫א‬J   K‫א‬‫א‬

 ‫א‬‫א‬‫א‬EMP_REC ‫א‬ W EMP_REC ID NAME SALARY  ALI 3000 ١٠١ 

‫א‬TABLES  ‫א‬‫א‬J  F  ‫א‬‫א‬‫א‬

 K‫א‬‫א‬KE‫א‬

WLOB Datatype ( Large Objectas) ‫א‬‫א‬‫א‬‫א‬J ٣ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

 K‫א‬‫א‬‫א‬‫א‬K‫א‬

‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬WBind Variables ‫א‬‫א‬J ٤ (Block) ‫א‬‫א‬‫א‬‫א‬‫א‬ESQL* PlusF‫א‬(Block)‫א‬

 K(Block) ‫א‬‫א‬‫א‬

SQL > VARIABLE NUMBER SQL > VARIABLE varchar2(20)

W‫א‬‫א‬

Emp_Sal  emp_name

Emp_Sal

 EHostF‫א‬NUMBER  Emp_ Name 

٢٠VARCHAR2(2) K EHostF‫א‬

  ‫א‬‫א‬‫א‬‫א‬(Block)  ‫א‬‫א‬‫א‬‫א‬ E:F‫א‬‫א‬‫א‬‫א‬(Block) ‫א‬ : Emp_Name:= ‘AHMED’; - ٥٥ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

PRINT ‫א‬‫א‬SQL* Plus‫א‬ SQL> PRINT Emp_ Name  :‫א‬‫א‬ PUT_LINE‫א‬‫א‬‫א‬‫א‬(Block)  ‫א‬ W‫א‬‫א‬DBMS_OUTPUT‫א‬‫א‬ DBMS_OUTPUT.PUT_LINE(‘Well Come to PLSQL Programming’); ‫א‬‫א‬‫א‬‫א‬‫א‬

S  QL >SET SERVEROUTPUT ON

 K(Block) ‫א‬‫א‬‫א‬‫א‬

SET SERVEROUTPUT ON ACCEPT p_annual_sal PROMPT ' Please enter the annual salary ' Declare V_sal NUMBER(9,2) :=&p_annual_sal ; BEGIN V_sal :=V_sal /12; DBMS_OUTPUT.PUT_LINE('Monthly Salary is '||TO_CHAR(V_sal)); END;

- ٥٦ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 

‫؟‬‫א‬PLSQL‫א‬‫א‬‫א‬‫א‬ K١ 1-

DECLARE VI_ID NUMBER (4);

2-

DECLARE v_x ,v_y ,v_z

VARACHAR2(10);

3-

DECLARE V_Date DATE NOT NULL;

4-

DECLARE V_valid BOOLEAN :=1;

   ‫א‬‫א‬‫א‬‫א‬ K٢

1-

V_days := v_date-SYSDATE;

2-

V_sender := USER||’ :’|| TO_CHAR(V_DEPTNO);

3-

v_sum := $100 + $3000;

4-

V_days:=v_date-SYSDATE;

5-

v_flage := TRUE;

6-

v_n1 := v_n2 > ( 2 * v_n3);

7-

v_value := NULL;

K‫א‬MY PLSQL WORKS PLSQL(Block) ‫ א‬K٣

G_MESSAGE -------------------------------My PL/SQL Block Works

- ٥٧ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

V-CHRPLSQL (Block)  K٤ ’42 is the answer’ ‫א‬V-CHRV_NUM   Kp6q4.sqlBlock‫א‬SQL * Plus ‫א‬‫א‬

SQL> PRINT g_char G_CHAR ---------------------42 is the answer

SQL> PRINT g_num G_NUM ---------42

- ٥٨ -

 ‫א‬‫א‬‫א‬ ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹] ‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬

٧

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬  KEBlockF‫א‬‫א‬‫א‬‫א‬‫א‬

 W‫א‬‫א‬

 K‫א‬‫א‬‫א‬

 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K‫א‬‫א‬‫א‬

K‫א‬‫א‬‫א‬‫א‬ 

 W‫א‬‫א‬‫א‬  K٪١٠٠‫א‬‫א‬‫א‬   W‫א‬‫א‬     W‫א‬‫א‬  K  H   W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ 

- ٥٩ -

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

  ،PLSQL EBlockF  ‫א‬ ‫א‬ ‫א‬    ‫א‬ ‫א‬  

 (Block),‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

 PLSQL  ESyntax F‫א‬ ‫א‬‫א‬     E  ،‫א‬F ‫א‬‫א‬    PLSQL ‫א‬ 

‫א‬KKKK‫א‬،‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬ ‫א‬K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

 K  W‫א‬J  WE‫א‬F‫א‬‫ א‬K١  K‫א‬٣٠‫א‬J

 ‫א‬‫א‬J  KJ

K(Block) ‫א‬ ‫א‬J  W

v_name varchr2(20); xyx number; birth_date date;

 W

1no number ; Dept%id number(2); Select varchar2(10);

 W Literal Values ‫א‬‫ א‬K٢  ‫א‬Date‫א‬Character‫א‬‫א‬‫א‬J

 KE‘ ‘F

v_name:=’ali’;  K‫א‬‫א‬‫א‬‫א‬Numbers‫א‬‫א‬J v_id :=201; - ٦٠ -

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 WOperations‫ א‬K٣  K ENOT,**F‫א‬‫א‬J  EJ ،HF‫א‬‫ א‬J

 EL،*F‫א‬‫א‬J

 KE =,>,<,<=, <=, IS NULL, LIKE, BETWEEN, IN F‫א‬J  EAND OR F‫א‬‫א‬J J  WComments ‫א‬‫א‬J ‫א‬‫א‬ ‫א‬‫א‬‫א‬

 K‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬E--F‫א‬‫א‬‫א‬J

 K

‫א‬E*/ F‫א‬‫א‬E/* F‫א‬‫א‬ J

K‫א‬

…..

V_name varchar2(20) ;-- this variable used to hold the employee name Begin /* this code is used to read The employee salary and calculate the annual salary And print the annual salary */ …….

End ;  K  Using Functions ‫א‬‫א‬‫א‬‫א‬  WEBlockF‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  W SQL‫א‬‫א‬‫א‬‫א‬ K١

(ROUND ,TRUNC, SQRT ….FNumber Functions‫א‬‫א‬‫ א‬J v_sal:= ROUND(v_sal,2) ;

ECONCAT, INITCAP, LOWER,…FCharacter Functions‫א‬‫א‬‫ א‬J SELECT INITCAP (enam) INTO v_name - ٦١ -

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

FROM emp WHERE empno =7788; (ADD_MONTHS, MONTHS_BETWEEN,… ) Date   ‫א‬ ‫א‬ J Functions Num_months := MONTHS_BETWEEN(SYSDATE,v_date); ‫א‬‫א‬ SQL ‫א‬ J Decode J  EMIN, MAX, AVG, ….. FGroup Functions J TO_CHAR

 W‫א‬‫א‬‫א‬‫א‬‫א‬ K٢ ◄ CHARACTER ‫א‬J 

 KTO_NUMBER◄  NUMBER ‫א‬J   K TO_DATE◄  DATE ‫א‬J 

ENested Blocks F‫א‬‫א‬‫א‬‫א‬ ‫א‬،‫א‬EBlockF ‫א‬‫א‬EBlockF،ENested BlockF‫א‬‫א‬

 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K ... x BINARY_INTEGER; BEGIN ...

DECLARE y NUMBER; BEGIN ...

END; ...

END;



‫ ﻣﺠﺎل‬x

‫ ﻣﺠﺎل‬y

 - ٦٢ -

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 x   ‫א‬EBlockF‫א‬   x   ‫א‬ ‫א‬  ‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬  (Block) ‫א‬

‫א‬(Blocks) ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ y K(Block) ‫א‬  K‫א‬(Block) ‫א‬‫א‬‫א‬

 ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J

 K‫א‬‫א‬(Block) ‫א‬  ... x BINARY_INTEGER; BEGIN ...

DECLARE X NUMBER; BEGIN ...

END; ...

END;

‫ داﺧﻞ هﺬﻩ‬X ‫أي اﺳﺘﺨﺪام‬ ‫( ﻳﻜﻮن ل‬Block) ‫اﻟﻮﺣﺪة‬

 DECLARE v_sal v_comm v_message BEGIN

 NUMBER(7,2) := 60000; NUMBER(7,2) := v_sal * .20; VARCHAR2(255) := ' eligible for commission'; DECLARE v_sal

NUMBER(7,2) := 50000; NUMBER(7,2) := 0;

v_comm

NUMBER(7,2) := v_sal + v_comm;

v_total_comp BEGIN

v_message := 'CLERK not' || v_message; END;

- ٦٣ -

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

v_message := 'SALESMAN'||v_message; END;  W‫א‬‫א‬‫א‬‫א‬‫א‬ ESub BlockF‫א‬‫א‬‫א‬ v_messageJ

 ‫א‬EBlockF‫א‬v_total_compJ  ESubBlockF‫א‬‫א‬‫א‬v_comm J

 ‫א‬EBlockF‫א‬ v_comm J

‫א‬EBlockF‫א‬ v_messageJ J 

  EProgramming GuidelinesF‫א‬    ‫א‬  ، ‫א‬ ‫א‬ ‫א‬ ‫א‬   ‫א‬    ‫א‬   ‫א‬ ‫א‬   ‫א‬ ‫א‬‫א‬   ‫א‬ ‫א‬  ‫א‬ ‫א‬   ‫א‬ ‫א‬‫א‬ ‫א‬   K     

 W‫א‬ ‫א‬‫א‬‫א‬‫א‬ K١  K‫א‬‫א‬‫א‬‫א‬‫א‬

E Case conventionF ‫א‬‫א‬‫א‬‫א‬‫ א‬K٢ W‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

 SELECT, INSERT DECLARE ,BEGIN, END v_sal, id, g_sal emp, dept, ename

‫א‬

‫א‬



SQL



‫א‬‫א‬



‫א‬‫א‬‫א‬



‫א‬‫א‬‫א‬ 

- ٦٤ -

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 W‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬  v_empno, v_sal

‫א‬ v_name

c_sal , c_tax

c_name

emp_cursor,

Name_cursor

e_too_many

e_name

p_empno

p_name

g_sal

g_name

‫א‬ variables‫א‬‫א‬ ‫א‬‫א‬ cursors ‫א‬‫א‬ exception‫א‬‫א‬ ‫א‬‫א‬‫א‬ substitute variables  global‫א‬‫א‬‫א‬ 

W‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K٣ BEGIN IF x=0 THEN y:=1; ELSE y:=2; END IF; END;

K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ BEGIN IF x=0 THEN y:=1; ELSE y:=2; END IF; END;



- ٦٥ -

 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 

 J ١

DECLARE v_weight number(3):=600; v_message VARCHAR2(255):='Product 10012'; BEGIN /* SUB BLOCK ‫א‬‫א‬‫* א‬/ DECLARE v_weight number(3):=1; v_message VARCHAR2(255):='Product 11001'; v_new_loc VARCHAR2(50):= 'Europe '; BEGIN v_weight := v_weight +1; v_new_loc:= 'Western '|| v_new_loc; END; v_weight := v_weight +1;  v_message := v_message || 'is in Stock ';  v_new_loc:= 'Western '|| v_new_loc; END ;

 W‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  KESub Block F‫א‬‫א‬‫א‬v_weight 

J 

 KESub Block F‫א‬‫א‬‫א‬v_new_loc J 

 KEmain Block F‫א‬‫א‬v_weight  J   KEmain Block F‫א‬‫א‬v_message J   KEmain Block F‫א‬‫א‬v_new_locJ 



‫א‬  EBlockF J ٢     ‫א‬   ،‫א‬ ‫א‬   ‫א‬  ‫א‬ ‫א‬ 

K‫א‬‫א‬   - ٦٦ -



 ‫א‬‫א‬ ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

Please enter the first number : 2 Please Enter The Second Number :4 G_RESULT ----------------------4.5  ‫א‬ ‫א‬‫א‬  ‫א‬   EBlockF     J ٣ ‫א‬ ‫א‬‫א‬    ‫א‬‫א‬     K ‫א‬  

K‫א‬K‫א‬‫א‬‫א‬  W

 KE 0.15١٥‫א‬‫א‬F‫א‬ J

KENVL‫א‬F‫א‬‫א‬‫א‬ J Please enter the salary amount: 50000 Please enter the bonus percentage: 10 PL/SQL procedure successfully completed.

G_TOTAL ---------55000 

- ٦٧ -

 ‫א‬‫א‬‫א‬ ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹] ‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬

Oracle‫א‬

Oracle ‫א‬

٨

 ‫א‬‫א‬ Oracle‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 W‫א‬‫א‬  KEBlockF‫א‬‫א‬Oracle ‫א‬‫א‬  EBlockF‫א‬‫א‬‫א‬‫א‬

 W‫א‬‫א‬

EBlockF‫א‬‫א‬‫א‬‫א‬

 Transactions‫א‬‫א‬  SQL CursorSQL‫א‬‫א‬‫א‬



 W‫א‬‫א‬‫א‬  K٪١٠٠Oracle‫א‬‫א‬   W‫א‬‫א‬     W‫א‬‫א‬    H   W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

- ٦٨ -

 ‫א‬‫א‬ Oracle‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

  PL/SQL SQL‫א‬‫א‬ ‫א‬‫א‬‫א‬

 ETransactionF      PL/SQL      ‫א‬

،Block ‫א‬ ‫א‬  COMMIT, SAVEPOINT, ROLLBACK  ‫א‬‫א‬  CREATE TABLE,   Block‫א‬‫א‬‫א‬‫א‬ DDL 

 KGRANT, REVOKE DCLALTER TABLE  

Select Statement ‫א‬  SELECT select_list {variable_name[, variable_name]... INTO | record_name} table FROM condition; WHERE

 ‫א‬‫א‬‫א‬‫א‬

select_list

record_nameVariablesSelect_ListK

INTO

‫א‬‫א‬‫א‬‫א‬

 ‫א‬‫א‬‫א‬‫א‬E‫א‬‫א‬F‫א‬ select_list‫א‬‫א‬  ‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬  ‫א‬‫א‬F‫א‬

variable_name record_name table condition

Exception ‫א‬ 

- ٦٩ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

Oracle‫א‬

 W

DECLARE v_deptno NUMBER(2); v_loc VARCHAR2(15); BEGIN SELECT INTO FROM WHERE

deptno, loc v_deptno, v_loc dept dname = 'SALES';

END; 

‫א‬'SALES'‫א‬‫ א‬‫א‬‫א‬‫א‬‫א‬  K‫א‬v_locv_deptno‫א‬‫א‬

 W‫א‬‫א‬‫א‬‫א‬   KE;F‫א‬ J

KINTO‫א‬ J

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J K‫א‬‫א‬‫א‬

K%TYPE ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J v_deptno dept.deptno%TPYPE; v_loc dept.loc%TPYPE; K‫א‬‫א‬WHERE J ‫א‬‫א‬ Group Function ‫א‬‫א‬‫א‬ J  KPLSQL‫א‬‫א‬Group Function‫א‬

 - ٧٠ -

 ‫א‬‫א‬ Oracle‫א‬

DECLARE v_sum_sal v_deptno BEGIN SELECT INTO FROM WHERE END;

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

  emp.sal%TYPE; NUMBER NOT NULL := 10; SUM(sal) -- group function v_sum_sal emp deptno = v_deptno;

 Control Transaction‫א‬DML‫א‬‫א‬ Data ManipulationDML‫א‬‫א‬‫א‬‫א‬  W

 K‫א‬WINSERT‫א‬ E١

BEGIN INSERT INTO emp(empno, ename, job, deptno) VALUES (empno_sequence.NEXTVAL, 'HARDING', 'CLERK', 10); END; K‫א‬‫א‬‫א‬WUPDATE‫א‬ E٢  ‫א‬‫א‬‫א‬WHERE K ‫א‬

:=‫א‬‫א‬Z‫א‬‫א‬‫א‬ J DECLARE v_sal_increase emp.sal%TYPE := 2000; BEGIN UPDATE emp SET sal = sal + v_sal_increase WHERE job = 'ANALYST'; END;

- ٧١ -

 ‫א‬‫א‬ Oracle‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

K‫א‬WDELETE‫א‬ E٣ K ‫א‬

DECLARE v_deptno emp.deptno%TYPE := 10; BEGIN DELETE FROM emp WHERE deptno = v_deptno; END; ‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬W ‫א‬ ‫א‬‫א‬ K ‫א‬     ‫א‬ WHERE ‫א‬    ‫א‬‫א‬ord‫א‬ordid‫א‬‫א‬‫א‬ ordid

 ‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬ WHERE ordid = ordid ; ‫א‬‫א‬ 601‫א‬ ordid‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬ ordid  K‫א‬‫א‬‫א‬

DECLARE orderdate ord.orderdate%TYPE; shipdate ord.shipdate%TYPE; ordid ord.ordid%TYPE := 601; BEGIN SELECT orderdate, shipdate INTO orderdate, shipdate FROM ord WHERE ordid = ordid; END; SQL> / DECLARE * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 6 - ٧٢ -

 ‫א‬‫א‬ Oracle‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

  WControl Transaction‫א‬ ‫א‬E،،F‫א‬WTransaction

‫א‬‫א‬K‫א‬ Transaction‫א‬

 W‫א‬

 K‫א‬‫א‬‫א‬‫א‬‫א‬COMMITJ

 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ROLLBACKJ  ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬SAVEPOINT J

 K‫א‬‫א‬

COMMIT [WORK]; SAVEPOINT savepoint_name; ROLLBACK [WORK]; ROLLBACK [WORK] TO [SAVEPOINT] savepoint_name;

 SQL Cursor SQL‫א‬‫א‬ ‫א‬ Cursor ‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬‫א‬

 K‫א‬‫א‬    ‫א‬   ‫א‬ ESQL CURSORS F‫א‬‫א‬ ‫א‬ ‫א‬  

 K‫א‬

 W‫א‬J

 ‫א‬‫א‬‫א‬‫א‬  W‫א‬

 KSQL‫א‬‫א‬

SQL%ROWCOUNTJ ١

 KSQL‫א‬ETRUEF

SQL%NOTFOUNDJ ٣

 KSQL‫א‬ETRUEF

- ٧٣ -

SQL%FOUNDJ ٢

 ‫א‬‫א‬ Oracle‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

SQL CURSORS K  ‫א‬  ‫א‬ ETRUEF  SQL%ISOPEN J ٤ ‫א‬‫א‬‫א‬‫א‬ FALSE‫א‬‫א‬‫א‬‫א‬  K‫א‬

1

SQL> VARIABLE rows_deleted VARCHAR2(30)

2 3 4 5 6 7 8 9 10

DECLARE v_ordid NUMBER := 605; BEGIN DELETE FROM item WHERE ordid = v_ordid; :rows_deleted := (SQL%ROWCOUNT ||' rows deleted.'); END; / SQL >PRINT rows_deleted

 



    SQL*Plus    rows_deleted ‫א‬   ‫א‬ ‫א‬  605orderitem‫א‬‫א‬EBlockF ‫א‬ ‫א‬‫א‬ SQL%ROWCOUNT‫א‬‫א‬ ٧‫א‬

Krows_deleted‫א‬ ' rows deleted.' ||‫א‬‫א‬،  KSQL*Plus‫א‬

- ٧٤ -

 ‫א‬‫א‬ Oracle‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 

 WEBlockF‫א‬J ١

 KDEPT ‫א‬J  KSQL*Plus‫א‬J

 K‫א‬‫א‬J

Kp8q1.sql ‫א‬J

G_MAX_DEPTNO -----------40 ‫א‬ DEPT ‫א‬‫א‬‫א‬ ‫א‬ J ٢  W‫א‬ . p8q2.sql 

 K‫א‬‫א‬‫א‬١٠‫א‬‫א‬J  K‫א‬‫א‬SQL*PlusSubstitution Variable ‫א‬J

 K location NULLJ

 KEBlockF‫א‬J

Please enter the department name: EDUCATION PL/SQL procedure successfully completed.

K‫א‬‫א‬‫א‬J DEPTNO DNAME LOC ---------------------------------50 EDUCATION  DEPTK location ‫א‬EBlockF‫א‬J ٣ W‫א‬K . p8q3.sql ‫א‬

 K‫א‬SQL*PlusSubstitution Variable ‫א‬J

Klocation ‫א‬SQL*PlusSubstitution Variable ‫א‬J Please enter the department number: 50 Please enter the department location: HOUSTON PL/SQL procedure successfully completed.

- ٧٥ -

 ‫א‬‫א‬ Oracle‫א‬

DEPTNO DNAME ----------------------50 EDUCATION

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

K‫א‬‫א‬J LOC ------------HOUSTON

‫א‬ K‫א‬‫א‬‫א‬‫א‬‫א‬ EBlockF‫א‬ J ٤ W‫א‬K . p8q4.sql 

 K‫א‬SQL*PlusSubstitution Variable ‫א‬J  K‫א‬‫א‬J KPlease enter the department number: 50 PL/SQL procedure successfully completed.

G_RESULT -------------------------------------------------------------------------------1 row(s) deleted.  ‫؟‬‫א‬J Please enter the department number: 99 PL/SQL procedure successfully completed.

G_RESULT -------------------------------------------------------------------------------0 row(s) deleted. 

. ‫א‬‫א‬J

- ٧٦ -

 ‫א‬‫א‬‫א‬ ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹] ‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬

‫א‬

‫א‬

٩

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬   W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬ K‫א‬‫א‬‫א‬

K‫א‬‫א‬‫א‬‫א‬ K‫א‬‫א‬‫א‬

K‫א‬‫א‬‫א‬‫א‬‫א‬ W‫א‬‫א‬‫א‬  K٪١٠٠‫א‬‫א‬‫א‬ 

 W‫א‬‫א‬  ٣ 

W‫א‬‫א‬    H -

 W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

- ٧٧ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

W ‫א‬    ‫א‬ ‫א‬  ‫א‬   EBlockF  ‫א‬   ‫א‬‫א‬‫א‬‫א‬  ٪٨‫א‬‫א‬ ٣٠٠٠ ‫א‬ ٪١٠‫א‬‫א‬ K‫א‬‫א‬‫א‬ K٣٠٠٠‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬  

KIF Statement ‫א‬‫א‬

‫א‬    ‫א‬‫א‬ ‫א‬  ‫א‬   ‫א‬  IF    K‫א‬

IF condition THEN statements;  END IF;

Ali  v_ename‫א‬‫א‬‫א‬ v_sal٣٥٠٠‫א‬‫א‬

. v_sal :=  ‫א‬   ‫א‬ E‫א‬F‫א‬     True  ‫א‬  

False‫א‬Aliv_ename‫א‬‫א‬3500 

W END IF‫א‬‫א‬‫א‬v_sal := 3500;‫א‬

….

IF v_ename = ‘Ali’ THEN v_sal := 3500; END IF; …….

: IF THEN ELSE ‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬ IF THEN ELSE K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

- ٧٨ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

 IF CONDITION1 THEN Statement1; ELSE Statement2.

END IF; IF v_deptno = 10 THEN UPDATE emp SET sal = sal * 1.10 WHERE deptno = v_deptno; ELSE UPDATE emp SET sal = sal * 1.08 WHERE deptno = v_deptno; END IF; ‫א‬‫א‬ ١٠‫א‬‫א‬‫א‬‫א‬‫א‬  ‫א‬

‫א‬‫א‬E‫א‬F‫א‬‫א‬،٪١٠‫א‬

K٪٨‫א‬

IF CONDITION1 THEN Statement1; ELSE - ٧٩ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

IF CONDITION2 THEN Statement2; END IF; END IF; IF v_deptno = 10 THEN UPDATE emp SET sal = sal * 1.10 WHERE deptno = v_deptno; ELSE IF v_job = ‘SALESMAN’ THEN UPDATE emp SET sal = sal * 1.11 WHERE job = v_job; END IF; ‫א‬‫א‬١٠‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬E‫א‬F‫א‬‫א‬،٪١٠‫א‬  K٪٨‫א‬‫א‬‫א‬‫א‬SALESMAN  

WIFTHEN ELSIF ‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬ IF THEN ELSIF    (ELSIF) ‫א‬ ‫א‬    IF  ‫א‬  ‫א‬   ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

 IF IF IF KESLE‫א‬

- ٨٠ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

IF CONDITION1 THEN Statement1; ELSIF CONDITION2 THEN Statement2; ELSIF CONDITION3 THEN Statement3; . . .

ELSE StatementN; END IF; IF v_grade >100 OR v_grade < 0 THEN DBMS_OUTPUT.PUT_LINE(‘Invalid Grade ’); ELSEF v_grade >= 90 THEN DBMS_OUTPUT.PUT_LINE(‘A’); ELSIF v_grade >= 80 THEN DBMS_OUTPUT.PUT_LINE(‘B’); ELSIF v_grade >= 70 THEN DBMS_OUTPUT.PUT_LINE(‘C’); ELSIF v_grade >= 60 THEN - ٨١ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

DBMS_OUTPUT.PUT_LINE(‘D’); ELSE DBMS_OUTPUT.PUT_LINE(‘F’); END IF; ٠‫א‬‫א‬v_grade‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬K١٠٠٠‫א‬‫א‬١٠٠  W‫א‬‫א‬‫א‬‫א‬‫א‬ ١٠٠٠‫א‬ ‫א‬ A B C D F

‫א‬ ١٠٠٩٠ ٨٩٨٠ ٧٩٧٠ ٦٩٦٠ ٥٩J ٠

W‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬ IF  ‫א‬‫א‬IF  W‫א‬

 IS NULL ‫א‬NULL ‫א‬‫א‬‫א‬‫א‬‫א‬J IF v_name IS NULL THEN … ….  END IF  KNULL ‫א‬NULL J  v_sal := 3000;  v_comm ;v_ annual_salary := 12 * v_sal + v_ ‫א‬NULL ‫א‬‫א‬ v_comm‫א‬

 KNULLannual_salary

 K‫א‬NULL NULL ‫א‬J

… v_job := NULL;

- ٨٢ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

v_name:=’AHMED’; v_info:= v_name || ‘ is ‘ || v_job ; …. v_jobAHMED is v_info‫א‬‫א‬‫א‬  K

AND ,OR , NOT ‫א‬‫א‬‫א‬J

IF v_sal > 3000 AND v_job = ‘SALESMAN’ OR v_deptno = 10 THEN …..

END IF ; W‫א‬‫א‬‫א‬ TRUE FLASE NULL AND TRUE TRUE FLASE NULL FLASE FLASE FLASE FLASE NULL NULL FLASE NULL OR TRUE FLASE NULL

TRUE TRUE TRUE TRUE

NOT TRUE FLASE NULL

FLASE TRUE TRUE

FLASE TRUE FLASE NULL

- ٨٣ -

NULL TRUE NULL NULL

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

;  v_reorder_flag ANDv_reorder_flag

v_flag :=

 

W‫א‬‫א‬v_flag 

v_reorder_flag TRUE TRUE NULL NULL

 v_reorder_flag TRUE FALSE TRUE FALSE

v_flag TRUE FALSE NULL FALSE

KLoops ‫א‬‫א‬     ‫א‬     ‫א‬‫א‬ ‫א‬‫א‬  PL /SQL  

 ‫א‬ ‫א‬ ‫א‬‫א‬‫א‬،‫א‬  W‫א‬PL /SQLK‫א‬‫א‬  KBasic Loop‫א‬‫א‬‫א‬ J KFOR‫א‬‫א‬ J

KWHILE ‫א‬‫א‬ J KNested LOOPS ‫א‬‫א‬‫א‬‫א‬ J :Basic Loop ‫א‬‫א‬‫א‬ ، End Loop‫א‬‫א‬ Loop ‫א‬‫א‬‫א‬‫א‬ E‫א‬‫א‬F‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬

  ‫א‬‫א‬      ‫א‬   E‫א‬ ‫א‬‫א‬   F ‫א‬‫א‬

‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬ Exit  K End Loop‫א‬‫א‬

- ٨٤ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

LOOP



Æ

statement1; ...

Æ

EXIT [WHEN condition];

Æ

‫א‬‫א‬‫א‬

 ‫א‬‫א‬ 

 F‫א‬  E‫א‬‫א‬ ‫א‬‫א‬

Æ END LOOP;

 

DECLARE item.ordid%TYPE := 601; v_ordid NUMBER(2) := 1; v_counter BEGIN LOOP INSERT INTO item(ordid, itemid) VALUES(v_ordid, v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP; END;

 - ٨٥ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

‫א‬‫א‬ item items ١٠‫א‬‫א‬  K١٠v_counter‫א‬‫א‬ 

 W‫א‬‫א‬‫א‬‫א‬W DECLARE v_counter NUMBER :=0; BEGIN LOOP DBMS_OUTPUT.PUT_LINE('v_counter = '||v_counter); EXIT WHEN v_counter > 5; v_counter:=v_counter+1; END LOOP; END ;

DECLARE v_counter NUMBER :=0; BEGIN LOOP DBMS_OUTPUT.PUT_LINE('v_counter = '||v_counter); EXIT WHEN v_counter > 5; v_counter:=v_counter-1; END LOOP; END ;

DECLARE v_counter NUMBER :=10; BEGIN LOOP DBMS_OUTPUT.PUT_LINE('v_counter = '||v_counter); EXIT WHEN v_counter > 5; v_counter:=v_counter+1; - ٨٦ -

 J 

 J 

 J 

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

For counter in [REVERSE] Lower_bound .. upper_pound LOOP statement1; statement2; ...

END LOOP; END LOOP; END ;

Æ

‫א‬‫א‬‫א‬ ‫א‬‫א‬

Æ ‫א‬‫א‬ Æ

 ‫א‬٧

J 

‫א‬‫א‬

J 

‫א‬١

J 

‫א‬‫א‬EF‫א‬‫א‬‫א‬‫א‬W‫א‬  ‫؟‬Loop   J   J  

: FOR ‫א‬‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬ FOR‫א‬‫א‬

 KKKK،١٥،‫א‬١٠‫א‬

- ٨٧ -

J 

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

‫א‬Lower_bound ‫א‬‫א‬J

Counter

‫א‬‫א‬‫א‬‫א‬‫א‬١‫א‬  KE‫א‬‫א‬reverse ‫א‬‫א‬Fupper_pound  K‫א‬‫א‬J

‫א‬‫א‬‫א‬‫א‬‫א‬J

 K‫א‬‫א‬

K‫א‬‫א‬J

‫א‬‫א‬‫א‬‫א‬‫א‬J ‫א‬  K ‫א‬‫א‬‫א‬‫א‬‫א‬‫ א‬J

REVERSE Lower_cound

F ‫א‬ J KE

  ‫א‬ ‫א‬‫א‬‫א‬‫א‬J F ‫א‬J

KE

BEGIN FOR i IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE('i= '||i); END LOOP; END; SQL> / i= 1 i= 2 i= 3 i= 4 i= 5

- ٨٨ -

upper_cound

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

 W‫א‬‫א‬‫א‬Block ‫א‬

DECLARE v_lower number:=1; v_upper number:=5; BEGIN FOR i IN v_lower.. v_upper LOOP DBMS_OUTPUT.PUT_LINE('i= '||i); END LOOP; END; SQL> / i= 1 i= 2 i= 3 i= 4 i= 5

: WHILE ‫א‬‫א‬ ‫א‬‫א‬ ‫א‬ ‫א‬     ‫א‬ ‫א‬  WHILE  ‫א‬‫א‬  

 K‫א‬‫א‬‫א‬‫א‬‫א‬، WHILE (condition) Æ ‫א‬‫א‬‫א‬  ‫א‬‫א‬

statement1; Æ ...

END LOOP; Æ

‫א‬‫א‬

DECLARE v_counter NUMBER :=0; BEGIN WHILE (v_counter <= 5) LOOP DBMS_OUTPUT.PUT_LINE('v_counter = '||v_counter); v_counter:=v_counter+1; END LOOP; END ;

- ٨٩ -

 J 

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬while ‫א‬‫א‬

 K‫א‬‫א‬K‫א‬‫א‬‫א‬ DECLARE v_counter NUMBER :=0; BEGIN LOOP DBMS_OUTPUT.PUT_LINE('v_counter = '||v_counter); EXIT WHEN v_counter > 5; v_counter:=v_counter+1; END LOOP; END ;

WHILE‫א‬‫א‬‫א‬‫א‬‫א‬W‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬W ACCEPT p_new_order PROMPT 'Enter the order number: ' ACCEPT p_items PROMPT 'Enter the number of items in this order: ' DECLARE NUMBER(2) := 1; v_count BEGIN WHILE v_count <= &p_items LOOP INSERT INTO item (ordid, itemid) VALUES (&p_new_order, v_count); v_count := v_count + 1; END LOOP; COMMIT; END;

 item



- ٩٠ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

: Nested Loops ‫א‬‫א‬‫א‬‫א‬        ‫א‬ ‫א‬‫א‬      ‫א‬KEWHILE FOR،Basic Loops‫א‬F‫א‬‫א‬ ‫א‬‫א‬‫א‬K‫א‬ELabelF‫א‬‫א‬

‫א‬‫א‬    ‫א‬  (Exit)‫א‬   ‫א‬ ‫א‬‫א‬ ELabelF‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

BEGIN ‫א‬‫א‬‫א‬‫א‬ <<Outer_loop>> LOOP v_counter := v_counter+1; EXIT WHEN v_counter>10; ‫א‬‫א‬‫א‬‫א‬‫א‬ <<Inner_loop>> LOOP ...

EXIT Outer_loop WHEN total_done = 'YES'; ‫א‬‫א‬‫א‬ -- Leave both loops EXIT WHEN inner_done = 'YES'; ‫א‬‫א‬‫א‬‫א‬‫א‬ -- Leave inner loop only ...

END LOOP Inner_loop; ...

END LOOP Outer_loop; END;

- ٩١ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

  CREATE TABLE messages VARCHAR2(60)) (results

 W‫א‬‫א‬‫א‬‫א‬J ١ 

 KEFOR‫א‬‫א‬‫א‬FK٨،٦‫א‬‫א‬١٠KKK١‫א‬J

 KEBlockF‫א‬‫א‬‫א‬‫א‬J

RESULTS ---------1 2 3 4 5 7 9 10

 Kmessages‫א‬J

 WNULLsalary ‫א‬‫א‬،emp‫א‬‫א‬J ٢ 

insert into emp values (8000, 'DOE', 'CLERK', 7698, SYSDATE, NULL, NULL, 10); ‫א‬‫א‬E PL/SQL BlockF J  W‫א‬‫א‬  Ksubstitution variable ‫א‬‫א‬‫א‬ J 

W‫א‬‫א‬‫א‬(commission)‫א‬ J   - ٩٢ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬

 ١٦٢

 

‫א‬

‫א‬‫א‬

٪١٠

١٠٠٠

٪١٥

١٥٠٠J ١٠٠٠

٪٢٠

١٥٠٠‫א‬ NULL



  K‫א‬emp‫א‬ J  ‫א‬message E‘The number is odd’Fp6q4.sql‫א‬J ٣ K‫א‬‫א‬‫א‬E‘The number is even’F ‫א‬  KempSTARS  K١

‫א‬‫א‬١٠٠STARS‫א‬*  E PL/SQL BlockF K٢ ‫א‬KE**********STARS١٠٠٠‫א‬‫א‬‫א‬F

Kp9q3.sql

 Ksubstitution variable ‫א‬‫א‬‫א‬J  K‫א‬ J 



- ٩٣ -

 ‫א‬‫א‬‫א‬ ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹] ‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬

‫א‬‫א‬

١٠

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  K‫א‬‫א‬

 W‫א‬‫א‬

K‫א‬‫א‬‫א‬‫א‬  K‫א‬‫א‬‫א‬

 W‫א‬‫א‬‫א‬  K٪١٠٠‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬   W‫א‬‫א‬  3  W‫א‬‫א‬  K  KH -

 W‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

- ٩٤ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

W ‫א‬  Block ‫א‬‫א‬‫א‬

‫א‬‫א‬‫א‬ ‫א‬  ‫א‬K‫א‬‫א‬ ‫א‬،‫א‬‫א‬‫א‬‫א‬

‫א‬‫א‬K ‫א‬ ‫א‬‫א‬KEErrorOracleF ‫א‬‫א‬ ‫א‬،‫א‬‫א‬

 K EBlockF‫א‬  W‫א‬‫א‬‫א‬  K‫א‬‫א‬‫א‬ W‫א‬‫א‬ : Predefined Oracle Server errors ‫א‬‫ א‬J ١ ‫א‬K‫א‬‫א‬‫א‬‫א‬ ٢٠  Oracle FOracle  ERAISEF  KEServer

: Non-Predefined Oracle server errors ‫א‬‫ א‬J ٢ K‫א‬oracle  Oracle ,EBlockF‫א‬‫א‬  KEOracle Server F WUser Defined Exceptions‫א‬‫א‬‫ א‬J ٣ EBlockF ‫א‬‫א‬

 K‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬

‫א‬‫א‬‫א‬  ‫א‬‫א‬‫א‬ KEBlockF‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬WHEN

K ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K  ‫א‬‫א‬‫א‬WHEN OTHERS THEN ‫א‬  ‫א‬‫א‬‫א‬‫א‬WHEN - ٩٥ -

OTHERS

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

   ‫א‬WHEN‫א‬K  K‫א‬‫א‬‫א‬‫א‬‫א‬ EXCEPTION WHEN exception1 [OR exception2 . . .] THEN statement1; statement2; ...

[WHEN exception3 [OR exception4 . . .] THEN statement1; statement2; . . .]

[WHEN OTHERS THEN statement1; statement2; . . .]  K‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬  ‫א‬‫א‬‫א‬‫א‬‫א‬K ‫א‬ 

 KEBlockF

oracle ‫א‬‫א‬‫א‬ ‫א‬

‫א‬K  ‫א‬‫א‬KEOracle Server F ‫א‬‫א‬‫א‬‫א‬EBlockF‫א‬‫א‬‫א‬

 K‫א‬‫א‬‫א‬‫א‬‫א‬K

- ٩٦ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

‫א‬  initialization nested collection method 

Kinitialization  varraytables

‫א‬   ‫א‬‫א‬‫א‬‫א‬ .Select ‫א‬ ‫א‬‫א‬ ‫א‬ ‫א‬‫א‬‫א‬ PL/SQL ‫א‬‫א‬‫א‬‫א‬

‫א‬  ACCESS_INTO_NULL COLLECTION_IS_NULL CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX INVALID_CURSOR LOGIN_DENIED NO_DATA_FOUND NOT_LOGGED_ON PROGRAM_ERROR ROWTYPE_MISMATCH STORAGE_ERROR

collection method ‫א‬ SUBSCRIPT_BEYOND_COUNT ‫א‬varraytables nested K‫א‬ ‫א‬‫א‬‫א‬‫א‬ Select ‫א‬  ‫א‬ ‫א‬

- ٩٧ -

TIMEOUR_RESOURCE TOO_MANY_ROWS VALUE_ERROR ZERO_DEVIDE

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

DECLARE V_ename emp.ename%Type; V_empno emp.empno%Type := &p_eno; Begin SELECT ename INTO v_ename From emp WHERE empno = v_empno; DBMS_OUTPUT.PUT_LINE ('Employee Name is ' || v_ename ); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (' Invalid Employee Number ' || v_empno); END; ‫א‬‫א‬٧٧٧٧،٧٧٨٨‫א‬‫א‬‫א‬ Enter value for p_eno: 7788

 K٧٧٧٧‫א‬

Employee Name is SCOTT PL/SQL procedure successfully completed.

SQL> / Enter value for p_eno: 7777 Invalid Employee Number 7777 PL/SQL procedure successfully completed.

- ٩٨ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

DECLARE V_ename emp.ename%Type; V_job emp.job%Type := upper( '&p_ejob'); Begin SELECT ename INTO v_ename From emp WHERE job= v_job; DBMS_OUTPUT.PUT_LINE ('Employee Name is ' || v_ename ); EXCEPTION

 

WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('There is no Job Employee has this '|| V_job ); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE ('The Job '|| V_job || ' has more than one Employee ' ); END; DRIVER ،MANAGER،PRESIDENT‫א‬‫א‬‫א‬

 MANAGER‫א‬‫א‬‫א‬‫א‬  KDRIVER Enter value for p_ejob: PRESIDENT old 3: V_job new 3: V_job

emp.job%Type := upper( '&p_ejob'); emp.job%Type := upper( 'PRESIDENT');

Employee Name is KING PL/SQL procedure successfully completed.

SQL> / - ٩٩ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

Enter value for p_ejob: MANAGER old 3: V_job new 3: V_job

emp.job%Type := upper( '&p_ejob'); emp.job%Type := upper( 'MANAGER');

The Job MANAGER has more than one Employee PL/SQL procedure successfully completed.

SQL> / Enter value for p_ejob: DRIVER There is no Job Employee has this DRIVER PL/SQL procedure successfully completed. :‫א‬‫א‬‫א‬ K‫א‬oracle oracle,EBlockF‫א‬‫א‬  KEOracle Server F 

 W‫א‬‫א‬‫א‬‫א‬ :EBlockF‫א‬‫א‬‫א‬ K١ DECLARE …..

Exception_name EXCEPTION; - ١٠٠ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

‫א‬PRAGMA_EXCEPTION_INIT‫א‬‫א‬‫א‬ K٢ ‫א‬‫א‬ ‫א‬‫א‬

K‫א‬‫א‬ PRAGMA_EXCEPTION_INIT(Exception_name ,error_number); WEBlockF‫א‬‫א‬‫א‬‫א‬ K٣

‫א‬WHEN‫א‬‫א‬‫א‬J

K‫א‬

 K‫א‬‫א‬‫א‬J  W DECLARE EXCEPTION;

Æ

e_emps_remaining

PRAGMA EXCEPTION_INIT ( e_emps_remaining , -2292);

Æ

dept.deptno%TYPE := &p_deptno; v_deptno BEGIN DELETE FROM dept deptno = v_deptno; WHERE COMMIT; EXCEPTION WHEN e_emps_remaining THEN DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' || TO_CHAR(v_deptno) || '. Employees exist. '); END;



‫א‬ ‫א‬‫א‬

-٢٢٩٢



Æ

‫א‬

‫א‬J ٢٢٩٢‫א‬e_emps_remaining‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬

K‫א‬، - ١٠١ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 W‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬EBlockF‫א‬‫א‬

 K‫א‬‫א‬‫א‬‫א‬K‫א‬

 W‫א‬‫א‬‫א‬‫א‬ :EBlockF‫א‬‫א‬‫א‬ J ١

DECLARE …..

Exception_name EXCEPTION; EBlockF‫א‬‫א‬‫א‬‫א‬ J ٢  K‫א‬RAISE ‫א‬‫א‬

Begin ….

RAISE Exception_name ….

; WKEBlockF‫א‬‫א‬‫א‬‫א‬ J ٣

WHEN‫א‬‫א‬‫א‬‫א‬

K‫א‬‫א‬

،‫א‬‫א‬‫א‬‫א‬‫א‬ W ‫א‬ e_invalid_product‫א‬E‫א‬F‫א‬

 K‫א‬‫א‬‫א‬‫א‬K

- ١٠٢ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

DECLARE EXCEPTION;e_invalid_product

‫א‬

Æ

BEGIN product UPDATE descrip = '&product_description' prodid = &product_number; WHERE

SET

IF SQL%NOTFOUND THEN RAISE e_invalid_product;

Æ

END IF; COMMIT; EXCEPTION WHEN e_invalid_product THEN DBMS_OUTPUT.PUT_LINE('Invalid product number.');

‫א‬  ‫א‬



Æ

‫א‬ END;

: SQLERRM ‫ و‬SQLCODE‫א‬‫ א‬‫א‬‫א‬‫א‬‫א‬‫א‬SQLERRMSQLCODE J ‫א‬‫א‬‫א‬‫א‬

K‫א‬‫א‬‫ א‬‫א‬ K‫א‬‫א‬‫א‬WSQLCODE J

K‫א‬‫א‬‫א‬SQLERRM J SQLCODE ‫א‬ ‫א‬

٠

‫א‬‫א‬ NO_DATA_FOUND

١ H١٠٠

ORACLE 



- ١٠٣ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 ‫؟‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬

K‫א‬KKKK‫א‬‫א‬

DECLARE ...

EXCEPTION; e_no_rows EXCEPTION; e_integrity PRAGMA EXCEPTION_INIT (e_integrity, -2292); BEGIN FOR c_record IN emp_cursor LOOP BEGIN SELECT ...

UPDATE ...

IF SQL%NOTFOUND THEN RAISE e_no_rows; END IF; EXCEPTION WHEN e_integrity THEN ...

WHEN e_no_rows THEN ...

END;

 ‫א‬‫א‬‫א‬‫א‬  ‫א‬Sub Block



‫א‬‫א‬‫א‬‫א‬  ‫א‬ ‫א‬ Main

Block‫א‬‫א‬

‫א‬  

‫א‬‫א‬  Main Block

END LOOP; ‫א‬‫א‬ EXCEPTION ‫א‬Main Block ‫א‬ WHEN NO_DATA_FOUND THEN . . .

WHEN TOO_MANY_ROWS THEN . . .  ‫א‬  ‫א‬ END; SQL ‫א‬ ‫א‬‫א‬ *Plus

- ١٠٤ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

 WRAISE_APPLICATION_ERROR ‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬ Procedures, F‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬

 KEFunctions

RAISE_APPLICATION_ERROR(error_number,message,[TRUE,FLASE]); ‫א‬K‫א‬‫א‬

error_number

J ٢٠٩٩٩J ٢٠٠٠٠‫א‬

‫א‬

 Stack.‫א‬‫א‬‫א‬‫א‬TRUEK‫א‬

message

[TRUE,FLASE]

K‫א‬‫א‬‫א‬FLASE  EDefualtF‫א‬‫א‬

‫א‬‫א‬RAISE_APPLICATION_ERROR‫א‬‫א‬  K‫א‬‫א‬

…

BEGIN ….

IF (v_grade > 100 OR v_grade < 0) THEN RAISE_APPLICATION_ERROR(-20210,’invlid grade ’); END IF; …..

END;

…

EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010,’invlid employee number’); …..

END; - ١٠٥ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

  KE‫א‬‫א‬F‫א‬‫א‬‫א‬‫א‬E PL/SQL BlockF J ١

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J

Kmessage More than one employee with a salary<salary>

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J Kmessage No employee with a salary<salary>   

‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬ ‫א‬‫א‬ J Kmessage

 K‫א‬‫א‬ J

RESULTS -----------------------------------------------------------SMITH – 800 More than one employee with a salary of 3000 No employee with a salary of 6000 ‫א‬‫א‬‫א‬‫א‬p8q3.sql‫א‬ J ٢ Kdept

K‫א‬ Please enter the department number: 50 Please enter the department location: HOUSTON PL/SQL procedure successfully completed.

G_MESSAGE -------------------------------------------------------------------------------Department 50 is an invalid department ‫א‬‫א‬‫א‬E PL/SQL BlockF J ٣ K١٠٠‫א‬‫א‬‫א‬‫א‬  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J  K‫א‬‫א‬‫א‬‫א‬‫א‬ J

‫א‬‫א‬‫א‬‫א‬‫א‬ J

 K(Some other error occurred ) - ١٠٦ -

 ‫א‬‫א‬

 ‫א‬‫א‬

 ‫א‬

 ‫א‬‫א‬

 ١٦٢

 

Please enter the salary: 800 PL/SQL procedure successfully completed.

G_MESSAGE -------------------------------------------------------------------------------There is/are 1 employee(s) with a salary between 700 and 900 Please enter the salary: 3000 PL/SQL procedure successfully completed.

G_MESSAGE -------------------------------------------------------------------------------There is/are 3 employee(s) with a salary between 2900 and 3100 Please enter the salary: 6000 PL/SQL procedure successfully completed.

G_MESSAGE -------------------------------------------------------------------------------There is no employee salary between 5900 and 6100

- ١٠٧ -



 

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

SQL> DESCRIBE emp Name Null?

Type ----------------------------------------------------- -------- -------------EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NOT NULL NUMBER(2)

SQL> SELECT * FROM emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ---------- -------- ---------- ---------- ---------7839 KING PRESIDENT 17/11/81 5000 10 7698 BLAKE MANAGER 7839 01/05/81 2850 30 7782 CLARK MANAGER 7839 09/06/81 2450 10 7566 JONES MANAGER 7839 02/04/81 2975 20 7654 MARTIN SALESMAN 7698 28/09/81 1250 1400 30 7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30 7844 TURNER SALESMAN 7698 08/09/81 1500 0 30 7900 JAMES CLERK 7698 03/12/81 950 30 7521 WARD SALESMAN 7698 22/02/81 1250 500 30 7902 FORD ANALYST 7566 03/12/81 3000 20 7369 SMITH CLERK 7902 17/12/80 800 20 7788 SCOTT ANALYST 7566 09/12/82 3000 20 7876 ADAMS CLERK 7788 12/01/83 1100 20 7934 MILLER CLERK 7782 23/01/82 1300 10

SQL>

DESCRIBE dept

Name Null?

Type ----------------------------------------------------- -------- ----------------DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)

SQL> SELECT * FROM DEPTNO ---------10 20 30 40

dept;

DNAME -------------ACCOUNTING RESEARCH SALES OPERATIONS

LOC ------------NEW YORK DALLAS CHICAGO BOSTON

- ١٠٨ -

 

SQL>

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

DESCRIBE SALGRADE

Name Null?

Type ----------------------------------------------------- -------- ------------------GRADE NUMBER LOSAL NUMBER HISAL NUMBER

SQL> SELECT *

FROM SALGRADE;

GRADE ---------- 1 2 3 4 5

LOSAL HISAL --------700 1201 1401 2001 3001

SQL>

---------1200 1400 2000 3000 9999

DESCRIBE ord

Name Null?

Type ----------------------------------------------------ORDID NOT NULL NUMBER(4) ORDERDATE COMMPLAN VARCHAR2(1) CUSTID NOT NULL SHIPDATE DATE TOTAL NUMBER(8,2)

SQL>

SELECT *

ORDID -------610 611 612 601 602 604 605 606 609 607 608 603 620 613

ORDERDAT -------07/01/87 11/01/87 15/01/87 01/05/86 05/06/86 15/06/86 14/07/86 14/07/86 01/08/86 18/07/86 25/07/86 05/06/86 12/03/87 01/02/87

FROM

ord;

C CUSTID SHIPDATE TOTAL - ---------- -------- ---------A 101 08/01/87 101.4 B 102 11/01/87 45 C 104 20/01/87 5860 A 106 30/05/86 2.4 B 102 20/06/86 56 A 106 30/06/86 698 A 106 30/07/86 8324 A 100 30/07/86 3.4 B 100 15/08/86 97.5 C 104 18/07/86 5.6 C 104 25/07/86 35.2 102 05/06/86 224 100 12/03/87 4450 108 01/02/87 6400

- ١٠٩ -

--------

DATE

NUMBER(6)

 

614 616 619 617 615 618 621

01/02/87 03/02/87 22/02/87 05/02/87 01/02/87 15/02/87 A 15/03/87 A

102 103 104 105 107 102 100

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

05/02/87 10/02/87 04/02/87 03/03/87 06/02/87 06/03/87 01/01/87

23940 764 1260 46370 710 3510.5 730

SQL> DESCRIBE PRODUCT Name Null?

Type ----------------------------------------------------- -------- ---------------------PRODID NOT NULL NUMBER(6) DESCRIP VARCHAR2(30)

SQL>

SELECT *

PRODID ---------100860 100861 100870 100871 100890 101860 101863 102130 200376 200380

FROM

PRODUCT ;

DESCRIP -----------------------------ACE TENNIS RACKET I ACE TENNIS RACKET II ACE TENNIS BALLS-3 PACK ACE TENNIS BALLS-6 PACK ACE TENNIS NET SP TENNIS RACKET SP JUNIOR RACKET RH: "GUIDE TO TENNIS" SB ENERGY BAR-6 PACK SB VITA SNACK-6 PACK

SQL> DESCRIBE ITEM Name -------------------------ORDID ITEMID PRODID ACTUALPRICE QTY ITEMTOT SQL>

SELECT *

FROM

Null?

-------NOT NULL NOT NULL

Type -----------NUMBER(4) NUMBER(4) NUMBER(6) NUMBER(8,2) NUMBER(8) NUMBER(8,2)

ITEM;

ORDID ITEMID PRODID ACTUALPRICE QTY ITEMTOT ------- ---------- ---------- ----------- ---------- ---------610 3 100890 58 1 58 611 1 100861 45 1 45 612 1 100860 30 100 3000 601 1 200376 2.4 1 2.4 602 1 100870 2.8 20 56

- ١١٠ -

 

604 604 604 603 610 610 613 614 614 612 612 620 620 620 613 613 613 619 617 617 614 616 616 616 616 616 619 619 615 607 615 617 617 617 617 617 617 617 617 609 609 618 618 618 621 621 615 608 608 609 606 605 605 605 605

1 2 3 2 1 2 4 1 2 2 3 1 2 3 1 2 3 3 1 2 3 1 2 3 4 5 1 2 1 1 2 3 4 5 6 7 8 9 10 2 3 1 2 3 1 2 3 1 2 1 1 1 2 3 4

100890 100861 100860 100860 100860 100870 200376 100860 100870 100861 101863 100860 200376 102130 100871 101860 200380 102130 100860 100861 100871 100861 100870 100890 102130 200376 200380 200376 100861 100871 100870 100870 100871 100890 101860 101863 102130 200376 200380 100870 100890 100860 100861 100870 100861 100870 100871 101860 100871 100861 102130 100861 100870 100890 101860

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

58 42 44 56 35 2.8 2.2 35 2.8 40.5 10 35 2.4 3.4 5.6 24 4 3.4 35 45 5.6 45 2.8 58 3.4 2.4 4 2.4 45 5.6 2.8 2.8 5.6 58 24 12.5 3.4 2.4 4 2.5 50 35 45.11 45 45 2.8 5 24 5.6 35 3.4 45 2.8 58 24

- ١١١ -

3 2 10 4 1 3 200 444 1000 20 150 10 1000 500 100 200 150 100 50 100 1000 10 50 2 10 10 100 100 4 1 100 500 500 500 100 200 100 200 300 5 1 23 50 10 10 100 50 1 2 1 1 100 500 5 50

174 84 440 224 35 8.4 440 15540 2800 810 1500 350 2400 1700 560 4800 600 340 1750 4500 5600 450 140 116 34 24 400 240 180 5.6 280 1400 2800 29000 2400 2500 340 480 1200 12.5 50 805 2255.5 450 450 280 250 24 11.2 35 3.4 4500 1400 290 1200

 

605 605 612 619

5 6 4 4

101863 102130 100871 100871

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

9 3.4 5.5 5.6

100 10 100 50

900 34 550 280

SQL> DESCRIBE PRICE Name Null?

Type ----------------------------------------------------- -------- -----------------PRODID NOT NULL NUMBER(6) STDPRICE NUMBER(8,2) MINPRICE NUMBER(8,2) STARTDATE DATE ENDDATE DATE

SQL> SELECT

* FROM PRICE;

PRODID STDPRICE MINPRICE STARTDAT ENDDATE ---------- ---------- ---------- -------- -------100871 4.8 3.2 01/01/85 01/12/85 100890 58 46.4 01/01/85 100860 35 28 01/06/86 100860 30 24 01/01/85 31/12/85 100861 45 36 01/06/86 100861 39 31.2 01/01/85 31/12/85 100870 2.8 2.4 01/01/86 100870 2.4 1.9 01/01/85 01/12/85 100871 5.6 4.8 01/01/86 101860 24 18 15/02/85 101863 12.5 9.4 15/02/85 102130 3.4 2.8 18/08/85 200376 2.4 1.75 15/11/86 200380 4 3.2 15/11/86

SQL> DESCRIBE CUSTOMER Name Null?

Type ----------------------------------------------------- -------- -------------CUSTID NOT NULL NUMBER(6) NAME VARCHAR2(45) ADDRESS VARCHAR2(40) CITY VARCHAR2(30) STATE VARCHAR2(2) ZIP VARCHAR2(9) AREA NUMBER(3) PHONE VARCHAR2(9) REPID NOT NULL NUMBER(4) CREDITLIMIT NUMBER(9,2) COMMENTS LONG

- ١١٢ -

 

CUSTID -------100 101 102 103 104 105 106 107 108

 ‫א‬

 ١٦٢

 

NAME ADDRESS --------------------------------------------- ---------------------JOCKSPORTS TKB SPORT SHOP VOLLYRITE JUST TENNIS EVERY MOUNTAIN K + T SPORTS SHAPE UP WOMENS SPORTS NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER

CITY CREDITLIMIT -----------------BELMONT 5000 REDWOOD CITY 10000 BURLINGAME 7000 BURLINGAME 3000 CUPERTINO 10000 SANTA CLARA 5000 PALO ALTO 6000 SUNNYVALE 10000 HIBBING 8000 CUSTID -------100 101 102 103 104 105 106 107

 ‫א‬‫א‬

ST ZIP

AREA PHONE

345 VIEWRIDGE 490 BOLI RD. 9722 HAMILTON HILLVIEW MALL 574 SUYYYYY RD. 3476 EL PASEO 908 SEQUOIA VALCO VILLAGE 98 LONE PINE WAY REPID

-- --------- ---------- --------- ---------- ---------CA 96711

415 598-6609

7844

CA 94061

415 368-1223

7521

CA 95133

415 644-3341

7654

CA 97544

415 677-9312

7521

CA 93301

408 996-2323

7499

CA 91003

408 376-9966

7844

CA 94301

415 364-9777

7521

CA 93301

408 967-4398

7499

MN 55649

612 566-9123

7844

COMMENTS ---------------------------------------------------------------------------Very friendly people to work with -- sales rep likes to be called Mike.

Rep called 5/8 about change in order - contact shipping.

Company doing heavy promotion beginning 10/89. Prepare for large orders during Contact rep about new line of tennis rackets.

Customer with high market share (23%) due to aggressive advertising.

Tends to order large amounts of merchandise at once. Accounting is considering Support intensive. Orders small amounts (< 800) of merchandise at a time.

First sporting goods store geared exclusively towards women. Unusual Promotional style and very willing to take chances towards new products!

108

- ١١٣ -

 ‫א‬

 ‫א‬‫א‬

 ‫א‬

 ١٦٢

 

 ‫א‬ ‫א‬   ١   ٨   ١٥

 ‫א‬  W‫א‬‫א‬  K‫א‬‫א‬   W‫א‬‫א‬  K‫א‬‫א‬‫א‬   W‫א‬‫א‬  K‫א‬‫א‬ 



 W‫א‬‫א‬‫א‬  K‫א‬‫ א‬

 ٢٦   ٣٧

 W‫א‬‫א‬  K‫א‬‫א‬ 



 W‫א‬‫א‬  K‫א‬‫א‬ 

 ٤٦   ٥٩   ٦٨   ٧٧ 



 W‫א‬‫א‬ K‫א‬‫א‬ 

 W‫א‬‫א‬  KOracle‫ א‬  W‫א‬‫א‬  K‫א‬ 

 ٩٤

 W‫א‬‫א‬  K‫א‬‫א‬ 

 ١٠٨

 ‫א‬

 

  

  ‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬  ‫א‬E‫א‬F‫א‬‫א‬ GOTEVOT appreciates the financial support provided by BAE SYSTEMS