際際滷

際際滷Share a Scribd company logo

Conversion To 3nF

HAMMAD AHMAD
HAMMAD AHMAD

The document describes converting a point of sale information system for a supermarket chain into third normal form (3NF) and designing an entity relationship diagram (ERD) for it. Initially, the data is in a non-normalized form with repeating groups and various anomalies. It is then normalized through a multi-step process into 1st NF, 2nd NF, and finally 3rd NF by separating the data into different tables for customers, items, invoices, manufacturers, and a customer-item link table. An ERD is then designed to model the normalized database structure.

1 of 8
1 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi CONVERSION TO 3RD nF AND ERD DESIGN OF POINT OF SALE INFORMATION SYSTEM OF PAKISTAN GROUP OF SUPER STORES (PGSS) Presented by: - Presented to: - HAMMAD AHMAD Sir Farhad M. Riaz PGD-IT (35) Lecturer hammadahmad29@yahoo.com Govt. Gordon College +92-312-500 22 47 Computer Section Rwp st Jan 31 2012
2 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi POINT OF SALE INFORMATION SYSTEM Conversion to 3rd nF Relation Repeating Group I-Price/unit C-Payment M-Address C-Address C-Invoice M-Name C-Name I-Name C-DOP Mode M-ID C-QP C-ID I-ID No. 101 Hammad F-block 31Jan2012 02 Cash 00111 3333 Shampoo 300 98711 Pantene Karachi Rwp Hammad F-block 01Feb2012 01 Cash 00542 3651 Perfume 1750 92410 Chestity Karachi Rwp 102 Badar Defense 03Feb2012 06 Credit 00875 3210 Cooking 750 95001 Dalda Karachi Lhr Oil 5kg Badar Defense 04Feb2012 02 Home 00921 3001 Cold 70 98540 Cocacola USA Lhr Delivery drink 1Ltr The above relation is not in 1st nF because it has repeating group as highlighted above. It is a repeating group because a customer can purchase different items. It has following anomalies:- Insertion anomaly In primary key column, there cannot be a null value. But if a customer purchases items on separate dates then the data cannot be entered. Deletion anomaly If the customer information is deleted then the item & manufacturer information is also deleted. So it can cause a lot of damage to the organization. Updating anomaly The item price and detail vary with the item no. so the whole rows and columns are to be updated with the change. Since there are also customer and manufacturer in the table so it is very difficult to update any of the categories. st Jan 31 2012
3 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi 1st nF The relation is converted in 1st nF by forming a composite key of C-ID and I-ID as shown below:- Composite Key I-Price/unit C-Payment M-Address C-Address C-Invoice M-Name C-Name I-Name C-DOP Mode M-ID C-QP C-ID I-ID No. 101 3333 Hammad F-block 02 31Jan2012 Cash 00111 Shampoo 300 98711 Pantene Karachi Rwp 101 3651 Hammad F-block 01 01Feb2012 Cash 00542 Perfume 1750 92410 Chastity Karachi Rwp 102 3210 Badar Defense 06 03Feb2012 Credit 00875 Cooking 750 95001 Dalda Karachi Lhr Oil 5kg 102 3001 Badar Defense 02 04Feb2012 Home 00921 Cold 70 98540 CocaCola USA Lhr Delivery drink 1Ltr But the above relation is not in 2nd nF because of the partial dependency of the non-key attributes as shown with highlighted arrows above. These partial dependent attributes are as follows:- I-ID I-Name, I-Price /unit, M-ID, M-Name, M-Address. C-ID C-Name, C-Address, C-QP, C-DOP, C-Payment Mode. It has following anomalies:- Insertion anomaly If a new item is introduced or a customer wants to register himself then the data cant be entered without C-ID & I-ID respectively. Deletion anomaly If a customer or item information is deleted then the whole information customer, item and manufacturer will be deleted. Updating anomaly It is very difficult to update the information. If there is any change in the data then the whole rows and columns are to be updated. st Jan 31 2012
4 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi 2nd nF The 1st nF relation is converted in 2nd nF by normalizing it into C-ID & I-ID entities. It is normalized as follows:- Transitive Dependency Customer C-Payment C-Address C-Invoice C-Name C-DOP Mode C-QP C-ID No. 101 Hammad F-block Rwp 02 31Jan2012 Cash 00111 101 Hammad F-block Rwp 01 01Feb2012 Cash 00542 102 Badar Defense Lhr 06 03Feb2012 Credit 00875 102 Badar Defense Lhr 02 04Feb2012 Home Delivery 00921 Item M-Address Price/unit M-Name I-Name M-ID I-ID I- 3333 Shampoo 300 98711 Pantene Karachi 3651 Perfume 1750 92410 Chastity Karachi 3210 Cooking Oil 750 95001 Dalda Karachi 5kg 3001 Cold drink 1Ltr 70 98540 CocaCola USA Transitive Dependency st Jan 31 2012
5 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi The above entities are not in 3rd nF because of the transitive dependency of non-key attributes. The transitive dependent non-key attributes are as follows:- C-QP M-ID C-Payment C-DOP Mode AND M- M-Name C-Invoice Address No. It has following anomalies:- Insertion anomaly If an unregistered customer purchases some items then his information cannot be entered. On the other hand a new manufacturers information cannot be entered without item id. Deletion anomaly By deleting customer information or item information the invoice information and manufacturers information is also deleted. Updating anomaly Still it is very difficult to update the information. If there is any change in the data then the whole rows and columns are to be updated. st Jan 31 2012
6 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi 3rd nF The 2nd nF relation is converted into 3rd nF by further normalizing the customer and item entities. They are normalized as follows:- Customer Foreign Key Address C-Name Invoice C-ID No. C- C- 101 Hammad F-block Rwp 00111 101 Hammad F-block Rwp 00542 102 Badar Defense Lhr 00875 102 Badar Defense Lhr 00921 Invoice No. C-Invoice Payment C-DOP Mode C-QP No. C- 00111 02 31Jan2012 Cash 00542 01 01Feb2012 Cash 00875 06 03Feb2012 Credit 00921 02 04Feb2012 Home Delivery Item Foreign Key Price/u I-Name M-ID I-ID nit I- 3333 Shampoo 300 98711 3651 Perfume 1750 92410 3210 Cooking Oil 5kg 750 95001 3001 Cold drink 1Ltr 70 98540 st Jan 31 2012
7 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi Manufacturer M-Name Address M-ID M- 98711 Pantene Karachi 92410 Chastity Karachi 95001 Dalda Karachi 98540 CocaCola USA Customer-Item (C-I Gerund) C-ID I-ID 101 3333 101 3651 102 3210 102 3001 Now the relation is in 3rd nF. The anomalies are reduced to maximum extent. Now each entity has its own record. Since The Prestigious Group of Super Stores has four other branches in Pakistan, so the above entities have a homogenous distributed system spread over Pakistan. st Jan 31 2012
8 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi ERD DESIGN ITEM MANUFACTURER C-I CUSTOMER INVOICE NO. st Jan 31 2012

More Related Content

Conversion To 3nF

  • 1. 1 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi CONVERSION TO 3RD nF AND ERD DESIGN OF POINT OF SALE INFORMATION SYSTEM OF PAKISTAN GROUP OF SUPER STORES (PGSS) Presented by: - Presented to: - HAMMAD AHMAD Sir Farhad M. Riaz PGD-IT (35) Lecturer hammadahmad29@yahoo.com Govt. Gordon College +92-312-500 22 47 Computer Section Rwp st Jan 31 2012
  • 2. 2 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi POINT OF SALE INFORMATION SYSTEM Conversion to 3rd nF Relation Repeating Group I-Price/unit C-Payment M-Address C-Address C-Invoice M-Name C-Name I-Name C-DOP Mode M-ID C-QP C-ID I-ID No. 101 Hammad F-block 31Jan2012 02 Cash 00111 3333 Shampoo 300 98711 Pantene Karachi Rwp Hammad F-block 01Feb2012 01 Cash 00542 3651 Perfume 1750 92410 Chestity Karachi Rwp 102 Badar Defense 03Feb2012 06 Credit 00875 3210 Cooking 750 95001 Dalda Karachi Lhr Oil 5kg Badar Defense 04Feb2012 02 Home 00921 3001 Cold 70 98540 Cocacola USA Lhr Delivery drink 1Ltr The above relation is not in 1st nF because it has repeating group as highlighted above. It is a repeating group because a customer can purchase different items. It has following anomalies:- Insertion anomaly In primary key column, there cannot be a null value. But if a customer purchases items on separate dates then the data cannot be entered. Deletion anomaly If the customer information is deleted then the item & manufacturer information is also deleted. So it can cause a lot of damage to the organization. Updating anomaly The item price and detail vary with the item no. so the whole rows and columns are to be updated with the change. Since there are also customer and manufacturer in the table so it is very difficult to update any of the categories. st Jan 31 2012
  • 3. 3 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi 1st nF The relation is converted in 1st nF by forming a composite key of C-ID and I-ID as shown below:- Composite Key I-Price/unit C-Payment M-Address C-Address C-Invoice M-Name C-Name I-Name C-DOP Mode M-ID C-QP C-ID I-ID No. 101 3333 Hammad F-block 02 31Jan2012 Cash 00111 Shampoo 300 98711 Pantene Karachi Rwp 101 3651 Hammad F-block 01 01Feb2012 Cash 00542 Perfume 1750 92410 Chastity Karachi Rwp 102 3210 Badar Defense 06 03Feb2012 Credit 00875 Cooking 750 95001 Dalda Karachi Lhr Oil 5kg 102 3001 Badar Defense 02 04Feb2012 Home 00921 Cold 70 98540 CocaCola USA Lhr Delivery drink 1Ltr But the above relation is not in 2nd nF because of the partial dependency of the non-key attributes as shown with highlighted arrows above. These partial dependent attributes are as follows:- I-ID I-Name, I-Price /unit, M-ID, M-Name, M-Address. C-ID C-Name, C-Address, C-QP, C-DOP, C-Payment Mode. It has following anomalies:- Insertion anomaly If a new item is introduced or a customer wants to register himself then the data cant be entered without C-ID & I-ID respectively. Deletion anomaly If a customer or item information is deleted then the whole information customer, item and manufacturer will be deleted. Updating anomaly It is very difficult to update the information. If there is any change in the data then the whole rows and columns are to be updated. st Jan 31 2012
  • 4. 4 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi 2nd nF The 1st nF relation is converted in 2nd nF by normalizing it into C-ID & I-ID entities. It is normalized as follows:- Transitive Dependency Customer C-Payment C-Address C-Invoice C-Name C-DOP Mode C-QP C-ID No. 101 Hammad F-block Rwp 02 31Jan2012 Cash 00111 101 Hammad F-block Rwp 01 01Feb2012 Cash 00542 102 Badar Defense Lhr 06 03Feb2012 Credit 00875 102 Badar Defense Lhr 02 04Feb2012 Home Delivery 00921 Item M-Address Price/unit M-Name I-Name M-ID I-ID I- 3333 Shampoo 300 98711 Pantene Karachi 3651 Perfume 1750 92410 Chastity Karachi 3210 Cooking Oil 750 95001 Dalda Karachi 5kg 3001 Cold drink 1Ltr 70 98540 CocaCola USA Transitive Dependency st Jan 31 2012
  • 5. 5 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi The above entities are not in 3rd nF because of the transitive dependency of non-key attributes. The transitive dependent non-key attributes are as follows:- C-QP M-ID C-Payment C-DOP Mode AND M- M-Name C-Invoice Address No. It has following anomalies:- Insertion anomaly If an unregistered customer purchases some items then his information cannot be entered. On the other hand a new manufacturers information cannot be entered without item id. Deletion anomaly By deleting customer information or item information the invoice information and manufacturers information is also deleted. Updating anomaly Still it is very difficult to update the information. If there is any change in the data then the whole rows and columns are to be updated. st Jan 31 2012
  • 6. 6 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi 3rd nF The 2nd nF relation is converted into 3rd nF by further normalizing the customer and item entities. They are normalized as follows:- Customer Foreign Key Address C-Name Invoice C-ID No. C- C- 101 Hammad F-block Rwp 00111 101 Hammad F-block Rwp 00542 102 Badar Defense Lhr 00875 102 Badar Defense Lhr 00921 Invoice No. C-Invoice Payment C-DOP Mode C-QP No. C- 00111 02 31Jan2012 Cash 00542 01 01Feb2012 Cash 00875 06 03Feb2012 Credit 00921 02 04Feb2012 Home Delivery Item Foreign Key Price/u I-Name M-ID I-ID nit I- 3333 Shampoo 300 98711 3651 Perfume 1750 92410 3210 Cooking Oil 5kg 750 95001 3001 Cold drink 1Ltr 70 98540 st Jan 31 2012
  • 7. 7 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi Manufacturer M-Name Address M-ID M- 98711 Pantene Karachi 92410 Chastity Karachi 95001 Dalda Karachi 98540 CocaCola USA Customer-Item (C-I Gerund) C-ID I-ID 101 3333 101 3651 102 3210 102 3001 Now the relation is in 3rd nF. The anomalies are reduced to maximum extent. Now each entity has its own record. Since The Prestigious Group of Super Stores has four other branches in Pakistan, so the above entities have a homogenous distributed system spread over Pakistan. st Jan 31 2012
  • 8. 8 Hammad Ahmad Govt. Gordon College Computer Department Rawalpindi ERD DESIGN ITEM MANUFACTURER C-I CUSTOMER INVOICE NO. st Jan 31 2012