Chapter 5 Data Modeling and Database Design 2003

ﺑﺴﻢ ﺍﻟﻠﻪ ﺍﻟﺮﺣﻤﻦ ﺍﻟﺮﺣﻴﻢ Chapter 5 ﺍﻟﻔﺼﻞ ﺍﻟﺨﺎﻣﺲ Data Modeling and Database Design ﺗﺸﻜﻴﻞ )ﺻﻴﺎﻏﺔ( ﺍﻟﺒﻴﺎﻧﺎﺕ ﻭﺗﺼﻤﻴﻢ ﻗﺎﻋﺪﺓ ﺑﻴﺎﻧﺎﺕ © 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5 -1

From Chapter: (4) ﺍﻟﻤﻔﺘﺎﺡ ﺍﻟﺮﺋﻴﺴﻲ ﺍﻟﺤﻘﻞ Field Non-Key Primary Key ﺭﺻﻴﺪ ﺍﻟﻌﻤﻴﻞ ﺗﺎﺭﻳﺦ ﺍﻟﺘﻌﺎﻣﻞ ﺍﺳﻢ ﺍﻟﻌﻤﻴﻞ ﺭﻗﻢ ﺍﻟﻌﻤﻴﻞ 225 25/12/02 ﻣﻬﺎ 1101 590 2/12/04 ﻋﻤﺎﺩ 511 21/5/03 ﻣﺤﻤﺪ 1102 1103 112 16/6/04 ﺧﺎﻟﺪ 1104 380 25/06/04 ﺭﺟﺐ 1105 98 25/12/02 ﺳﻬﻴﺮ 1106 200 12/01/03 ﻣﺤﻤﺪ 1107 © 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart ﺍﻟﺴﺠﻞ Record Tuple 2

From Chapter: (4) Database Systems: Schemas ﺍﻟﺮﺳﻮﻡ ﺍﻟﺘﺨﻄﻴﻄﻴﺔ : ﻧﻈﻢ ﻗﻮﺍﻋﺪ ﺍﻟﺒﻴﺎﻧﺎﺕ Subschema A Jackson 210 Houston 100 Subschema B r Subschema C r xxxxxxx External Level Mapping external level views to conceptual level schema Inventory Sales Customer Conceptual Level Cash receipt Mapping conceptual level facts to internal level descriptions Inventory Record: Item number: integer (5), non-null, index = itemx Internal Description: character (15) Level Cost: currency © 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 5

The Six basic steps in designing and implementing a database system ﺍﻟﺨﻄﻮﺍﺕ ﺍﻟﺴﺘﺔ ﺍﻟﺮﺋﻴﺴﺔ ﻟﺘﺼﻤﻴﻢ ﻭﺗﻄﺒﻴﻖ ﻧﻈﺎﻡ ﻗﺎﻋﺪﺓ ﺑﻴﺎﻧﺎﺕ Planning ﺍﻟﺘﺨﻄﻴﻂ Data modeling occurs here Requirements analysis ﺗﺤﻠﻴﻞ ﺍﻻﺣﺘﻴﺎﺟﺎﺕ ﻫﻨﺎ ﻳﺘﻢ ﺻﻴﺎﻏﺔ ﺍﻟﺒﻴﺎﻧﺎﺕ Design ﺍﻟﺘﺼﻤﻴﻢ Coding ﺍﻟﺘﺮﻣﻴﺰ Implementation ﺍﻟﺘﻨﻔﻴﺬ Operation and maintenance ﺍﻟﺘﺸﻐﻴﻞ ﻭﺍﻟﺼﻴﺎﻧﺔ © 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 8

Sample E-R Diagrams ﻋﻴﻨﺔ ﻟﻠﺮﺳﻮﻣﺎﺕ ﺍﻟﺒﻴﺎﻧﻴﺔ ﻟﻠﻜﺎﺋﻨﺎﺕ ﺍﻟﻌﻼﺋﻘﻴﺔ Employers Managed By Part of Supervisors Manages Departments Customer Orders Players Lead To Sales Lead To Cash Receipts Lead To Teams Part of League © 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 13

Sample REA diagram ، ﺍﻷﺤﺪﺍﺙ ، ﻋﻴﻨﺔ ﻟﻠﺮﺳﻮﻣﺎﺕ ﺍﻟﺒﻴﺎﻧﻴﺔ )ﺍﻟﻤﻮﺍﺭﺩ ( ﺍﻟﻮﻛﻼﺀ © 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 17

Relationships between Entities ﺍﻟﻌﻼﻗﺎﺕ ﺑﻴﻦ ﺍﻟﻜﻴﻨﻮﻧﺎﺕ ¢ 1. Three basic types of relationships between entities are possible. They are: : ﻳﻮﺟﺪ ﻫﻨﺎﻙ ﺛﻼﺛﺔ ﺃﻨﻮﺍﻉ ﻣﻦ ﺍﻟﻌﻼﺋﻖ ﺑﻴﻦ ﺍﻟﻜﻴﻨﻮﻧﺎﺕ ﻭﻫﻲ A one-to-one relationship (1: 1) ﻋﻼﻗﺔ ﻭﺍﺣﺪ ﺇﻟﻰ ﻭﺍﺣﺪ Sales 2. Cash Receipts (1, 1) A one-to-many relationship (1: N) ﻋﻼﻗﺔ ﻭﺍﺣﺪ ﺇﻟﻰ ﻣﺘﻌﺪﺩ Sales 3. (0, 1) (0, N) (1, 1) Cash Receipts A many-to-many relationship (M: N) ﻋﻼﻗﺔ ﻣﺘﻌﺪﺩ ﺇﻟﻰ ﻣﺘﻌﺪﺩ Sales (0, N) (1, N) © 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart Cash Receipts 18 ¢

Implementing an REA model ﺗﻨﻔﻴﺬ ﻧﻤﻮﺫﺝ ﺍﻟﺮﺳﻢ ﺍﻟﺒﻴﺎﻧﻲ © 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 20

Create Tables ﺇﻧﺸﺎﺀ ﺍﻟﺠﺪﺍﻭﻝ : ﺍﻹﺟﺮﺍﺀ ﺍﻷﻮﻝ From the previously discussed REA diagram, nine tables would be created: one for each of the seven entities and one for each of the many-to-many relationships. ، ﺟﺪﻭﻝ ﻟﻚ ﻭﺍﺣﺪﺓ ﻣﻦ ﺍﻟﺴﺒﻌﺔ ﻛﻴﻨﻮﻧﺎﺕ : ﻣﻦ ﺍﻟﻤﻔﺘﺮﺽ ﺇﻧﺸﺎﺀ ﺗﺴﻌﺔ ﺟﺪﺍﻭﻝ ، ﻣﻦ ﺍﻟﺮﺳﻢ ﺍﻟﺒﻴﺎﻧﻲ ﺍﻟﺴﺎﺑﻖ ¢. ﺍﻟﻌﻼﻗﺎﺕ ﺍﻟﻤﺘﻌﺪﺩﺓ ﻭﻭﺍﺣﺪ ﻟﻜﻞ ﻣﻦ ¢ 1. 2. 3. 4. 5. Inventory ﺍﻟﺒﻀﺎﺋﻊ Purchases ﺍﻟﻤﺸﺘﺮﻳﺎﺕ Employees ﺍﻟﻤﻮﻇﻔﻴﻦ Vendors ﺍﻟﻤﻮﺭﺩﻳﻦ Cashier ﺃﻤﻴﻦ ﺍﻟﺼﻨﺪﻭﻕ 6. 7. 8. 9. Cash disbursements ﺗﻮﺯﻳﻊ ﺍﻟﻨﻘﺪ Cash ﺍﻟﻨﻘﺪ Purchases-inventory ﺷﺮﺍﺀ ﺍﻟﺒﻀﺎﺋﻊ Purchases-cash disbursements ﺗﺴﺪﻳﺪ ﺍﻟﻤﺸﺘﺮﻳﺎﺕ © 2003 Prentice Hall Business Publishing, Accounting Information Systems, 9/e, Romney/Steinbart 21