Supply Chain Application Suite-Computer Sciences Applications-Project Report, Study Guides, Projects, Research of Applications of Computer Sciences

Download Supply Chain Application Suite-Computer Sciences Applications-Project Report and more Study Guides, Projects, Research Applications of Computer Sciences in PDF only on Docsity! 3 | P a g e Contents 1 Introduction ............................................................................................................................. 8 1.1 Project Background .......................................................................................................... 8 1.2 Report Focus .................................................................................................................... 9 2 Entity Relationship Diagram ................................................................................................. 10 2.1 Modeling of Entities and the respective Relations ......................................................... 10 2.1.1 Customer Entity ...................................................................................................... 10 2.1.2 Order Entity ............................................................................................................ 12 2.1.3 Order_Line Entity ................................................................................................... 14 2.1.4 State Entity .............................................................................................................. 14 2.1.5 Customer_Account Entity ....................................................................................... 15 2.1.6 Database_Access Entity .......................................................................................... 17 2.1.7 Product Entity ......................................................................................................... 19 2.1.8 Product_Line_Item Entity ....................................................................................... 21 2.1.9 Catalog_Item Entity ................................................................................................ 22 2.1.10 Manufacturing _Company Entity ........................................................................... 22 2.1.11 Payment Entity ........................................................................................................ 23 2.1.12 Credit Card Entity ................................................................................................... 23 2.1.13 Invoice Entity .......................................................................................................... 24 2.1.14 Direct Billing Entity ................................................................................................ 25 2.2 Complete System E-R Diagram ..................................................................................... 26 3 Mapping Entity Relationship Diagram into Relations .......................................................... 27 3.1 Step 1: Map Regular Entities ......................................................................................... 27 3.1.1 Customer Relation .................................................................................................. 27 3.1.2 Order Relation ......................................................................................................... 27 3.1.3 State Relation .......................................................................................................... 27 3.1.4 Customer_Account Relation ................................................................................... 28 3.1.5 Database_Access Relation ...................................................................................... 28 3.1.6 Product Relation...................................................................................................... 28 3.1.7 Product_Line_Item Relation ................................................................................... 28 3.1.8 Manufacturing_Company Relation ......................................................................... 28 docsity.com 4 | P a g e 3.2 Step 2: Map Weak Entities ............................................................................................. 29 3.2.1 Catalog_Item Relation ............................................................................................ 29 3.3 Step 3: Map Binary Relationships .................................................................................. 29 3.3.1 Map Binary One-to-Many Relationships ................................................................ 29 3.3.2 Map Binary Many-to-Many Relationships ............................................................. 34 3.3.3 Map Binary One-to-One Relationships .................................................................. 35 3.4 Step 4: Map Associative Entities ................................................................................... 36 3.5 Step 5: Map Unary Relationships................................................................................... 37 3.6 Step 6: Map Ternary (and n-ary) Relationships ............................................................. 37 3.7 Step 7: Map Supertype/Subtype Relationships .............................................................. 38 4 Normalization ....................................................................................................................... 40 4.1 Step 1: Convert to First Normal Form ........................................................................... 40 4.1.1 Product .................................................................................................................... 40 4.1.2 Order_Line .............................................................................................................. 40 4.1.3 Order ....................................................................................................................... 41 4.1.4 Customer_Account ................................................................................................. 41 4.1.5 Customer ................................................................................................................. 41 4.1.6 Catalog_Item ........................................................................................................... 42 4.1.7 Quote ....................................................................................................................... 42 4.1.8 Database_Access..................................................................................................... 42 4.2 Step 2: Convert to Second Normal Form ....................................................................... 43 4.2.1 Product .................................................................................................................... 43 4.2.2 Order_Line .............................................................................................................. 43 4.2.3 Order ....................................................................................................................... 43 4.2.4 Customer_Account ................................................................................................. 44 4.2.5 Customer ................................................................................................................. 44 4.2.6 Catalog_Item ........................................................................................................... 44 4.2.7 Quote ....................................................................................................................... 45 4.2.8 Database_Access..................................................................................................... 45 4.3 Step 3: Convert to Third Normal Form .......................................................................... 45 5 References ............................................................................................................................. 46 docsity.com 7 | P a g e Table 41: Direct Billing relation ................................................................................................... 39 Table 42: Payment relations.......................................................................................................... 39 Table 43: Product (2NF) .............................................................................................................. 43 Table 44: Order_Line (2NF) ......................................................................................................... 43 Table 45: Order (2NF) .................................................................................................................. 43 Table 46: Customer_Account (2NF) ............................................................................................ 44 Table 47: Customer (2NF) ............................................................................................................ 44 Table 48: Catalog_Item (2NF) ...................................................................................................... 44 Table 49: Quote (2NF) .................................................................................................................. 45 Table 50: Database_Access (2NF)................................................................................................ 45 docsity.com 8 | P a g e 1 Introduction 1.1 Project Background Supply Chain Application Suite is a product designed to easily channelize the products from different vendors to specific customers using online resources. It comprises multiple modules namely: Order-to-Cash which is then subdivided into different main functions and Stock Management which procures the manufactured goods from the vendors. This is a customized product as per the requirements of the organization PCLAN Plus Inc. The entire flow which includes from consumer selecting the product using the web portal, fulfilling the order, making necessary changes to it, choosing the applicable payment option, providing their shipment information, and also then tracking their order later. On the organizational side, Supply Chain Application Suite endows them with core functionality of providing access to the purchase orders made, generating reports, updating stock and thereby expanding their business. The inputs thereby consist of: 1. Consumers selecting the product from the catalog available. 2. Consumers providing the shipment address. 3. Consumers providing the required information for the payment option selected. 4. Retailer system updating the stock values from the respective vendors. The outputs are: 1. Delivery of products 2. Invoice generation if the selected payment option is Open Account. 3. The reports generation for purchases made. The processes include: 1. Updating catalog as new products emerge. 2. Updating stock values as it diminishes. 3. Maintaining customers account for shipment and billing. 4. Tracking orders. 5. Maintaining records on purchases made for report generation. docsity.com 9 | P a g e 1.2 Report Focus As part of completing the first build of the system as following the incremental model, the prime objective of this report is to provide insight into two main models:  Conceptual Data model This comprises of the Entity Relationship Diagram (E-R Diagram). E-R diagram realizes the business rules of the organization, in this case PCLAN Plus Inc., which is derived from rules, constraints and core functionalities of the company. The report contains the Data descriptions of the Entities modeled and their relationships. Each Relationship is described in stages which are then combined into the overall E-R diagram of the system.  Logical Database Design and the Relational Model E-R diagram is then translated into relations. Relations are essentially two dimensional tables uniquely named and contain a set of named columns and an arbitrary number of unnamed rows. Here the graphical representation is applied as opposed to the short text statements. The graphical representation is depicted by a rectangle containing the attributes for the relation. Normalization, being the integral part of the logical database design, is carried out to verify that each of the relations that were created as from mapping the E-R diagram is devoid of anomalies and from well-structured relations. docsity.com 12 | P a g e 3. CUSTOMER Lives In State. This is a 1:M relationship. Each CUSTOMER can live in only one STATE but each STATE is lived-in by any number of CUSTOMERS. Customer Customer_ID Cust_Name Cust_Address Cust_Acount_ Status_Code Creation_ Date Modification_ Date State Lives In State_ID State_Name State_ Abbreviation Figure 4: Customer lives_in State 2.1.2 Order Entity 2.1.2.1 Order Data Description ORDER: The Transaction associated with the scale of one or more products to customer. The order is in an electronic Form. Figure 5: Order entity Order_Amt Order Order_TimeOrder_ID docsity.com 13 | P a g e Table 2: Order data description A ttr ib ut es Order Name Description Order_ID Identification Number Order_Time Time and date of order placement Order_Amt Total amount of Order Order_Status Delivery status 2.1.2.2 Order Entity Relationships with other Entities 1. ORDER Includes PRODUCT. This is a M:M relationship. Each instance of ORDER is requested for at least one PRODUCT. Each Product may be requested through any instances of ORDER. Since this is a M:M relationship, Requests relationship is substituted with ORDER_LINE associative entity. Order_Amt Order Order_TimeOrder_ID Product Product_ Description Product_ Acctual_Cost Product_ Status Product_ Count Product_ID EntityOrder_Line Ordered_ quantity Figure 6: Order includes Product docsity.com 14 | P a g e 2.1.3 Order_Line Entity 2.1.3.1 Order_Line Data Description ORDER_LINE: An associative entity. This shows the number of PRODUCT ordered in each instance of ORDER. EntityOrder_Line Ordered_ quantity Figure 7: Order_Line Entity Table 3: Order_Line data description A ttr ib ut es Order _Line Name Description Ordered_Quantity Number of Products ordered. 2.1.4 State Entity 2.1.4.1 State Data Description STATE: The state in which the customer resides or has made the purchase from. Each state is identified uniquely by a State_ID attribute. State State_ID State_Name State_ Abbreviation Figure 8: State entity Table 4: State data description A ttr ib ut e s State Name Description State_ID Identification Number State_Name Name of State State_Abbr Abbreviation for State docsity.com 17 | P a g e 2.1.6 Database_Access Entity 2.1.6.1 Database_Access Data Description DATABASE_ACCESS: This is essentially to store the customers account information. It includes the account Email and encrypted password with respect to each customer, who has an account. Cust_Account _Email Database_Access Customer_ reference _ID Figure 11: Database_Access entity Table 6: Database_Access data description A ttr ib ut es Database Name Description Customer_reference_ID This is to reference the Customer Account of the Customer. Email Email to login. Password Password to login. docsity.com 18 | P a g e 2.1.6.2 Database_Access Entity Relationships with other Entities 1. DATABASE_ACCESS Maintained_by DATABASE_MANGER. This is a M:1 relationship. Each instance of the DATABASE_ACCESS is maintained by a single DATABASE_MANGER at a time. Whereas each DATABASE_MANGER has the authority to update or edit the many instances of DATABASE_ACCESS at a time. PRODUCT: A type of computer products and other products related to computer and network technology made by various trusted brands. Cust_Account _Email Database_Access Customer_ reference _ID Database_Manager Managed_by DatebaseManager_ ID Name Address Figure 12: Database_Access managed by Database_Manger docsity.com 19 | P a g e 2.1.7 Product Entity 2.1.7.1 Product Data Description PRODUCT: A type of computer products and other products related to computer and network technology made by various trusted brands. Product Product_ Description Product_ Acctual_Cost Product_ Status Product_ Count Product_ID Figure 13: Product entity Table 7: Product data description A ttr ib ut es Product Name Description Product_ID Identification Number Prod_Actual_Cost Product actual production cost Cost_Currency_cd Date of creation Product_status Status of product indicating availability Product_Count Number of Products docsity.com 22 | P a g e 2.1.9 Catalog_Item Entity 2.1.9.1 Catalog_Item Data Description 1. CATALOG_ITEM: A weak entity which displays the products. Its key attribute is the combination by the Product_ID and the Name attribute of the catalog. DescriptionName Price Catalog_Item Figure 18: Catatlog_Item entity Table 9: Catalog_item data description A ttr ib ut e s Catalog Item Name Description Name Name of Catalog item Description Catalog item specification Price Customer price of Catalog Item 2.1.10 Manufacturing _Company Entity 2.1.10.1 Manufacturing_Company Data Descriptions MANUFACTURING_COMPANY: The various companies dealing with the supply of products. Manufacturing_ Comapany Manufacturing _Company_ Name Manufacturing_ Company_ID Address Telephone_No Figure 19: Manufacturing_Company entity docsity.com 23 | P a g e Table 10: Manufacturing_Company data description A ttr ib ut es Manufacturing Company Name Description Manufacturing Company_ID Identification Number Manufacturing Company_Name Name of Manufacturing Company Address Street Address of the Manufacturing Company Telephone_No Contact number of the Manufacturing Company 2.1.11 Payment Entity 2.1.11.1 Payment Data Description PAYMENT: This is a superclass. It includes the different kinds of payment that the customer is allowed to make. Payment Payment_IDPayment_Date Figure 20: Payment entity Table 11 : Payment data description A ttr ib ut es Payment Name Description Payment_ID Identification Number Payment_Date Date of payment 2.1.12 Credit Card Entity 2.1.12.1 Credit Card Data Description CREDITCARD: (a subclass of payment) A payment via which the customer can buy the products. Credit Card Credit Card Type Credit Card_No Expiry Date Figure 21: Credit Card entity docsity.com 24 | P a g e Table 12: Credit Card data description A ttr ib ut es Credit Card Name Description Credit Card type Credit card type Credit Card No. Credit card identification number Expiry Date Credit card expiry date Card Security Code Credit card security code Card holder first name Credit card holder’s first name Card holder last name Credit card holder’s last name Street address Credit card holder’s street address City Credit card holder’s City State Credit card holder’s State Zip Code Credit card holder’s Zip code Telephone Number Credit card holder’s telephone number E-mail Credit card holder’s e-mail address 2.1.13 Invoice Entity 2.1.13.1 Invoice Data Description INVOICE: (a subclass of payment) A payment via which the customer can by the products. Each invoice is identified by the Invoice_No. Invoice Bill_to_ Address Invoice_No Created_by Created_on Date_ delivered Description Figure 22: Invoice entity docsity.com 27 | P a g e 3 Mapping Entity Relationship Diagram into Relations This shows the transformation of the E-R diagram developed during the conceptual design into the relational database schemas. The entire procedure in broken down into various steps namely:  Step1: Map Regular Entities  Step 2: Map Weak Entities  Step3: Map Binary Entities  Step 4: Map Associative Entities  Step 5: Map Unary Relationships  Step 6: Map Ternary (and n-ary) Relationships  Step 7: Map Supertype/Subtype Relationships [1] 3.1 Step 1: Map Regular Entities 3.1.1 Customer Relation Customer_Address (composite attribute) is broken down into Street, City, Zip, Fax, and Contact_no. of the Customer. Table 15: Customer relation CUSTOMER Customer_ID Cust_Name Create_Date Cust_Acct_status_cd Street City Zip Fax Contact_no. 3.1.2 Order Relation Table 16: Order relation ORDER Order_ID Order_Time Order_Amt Order_Status 3.1.3 State Relation Table 17: State relation STATE State_ID State_Name State_Abbr docsity.com 28 | P a g e 3.1.4 Customer_Account Relation Table 18: Customer_Account relation CUSTOMER_ACCOUNT Acct_ID Create_Date Modi_Date Acct_Type_Cd 3.1.5 Database_Access Relation Table 19: Database_Access relation DATABASE_ACCESS Customer_reference_ID Email Password 3.1.6 Product Relation Table 20: Product relation PRODUCT Product_ID Prod_Actual_Cost Cost_Currency_cd Product_status Product_Count 3.1.7 Product_Line_Item Relation Table 21: Product_Line_Item relation PRODUCT_LINE_ITEM Product_line_ID Product_Line_Name 3.1.8 Manufacturing_Company Relation Address (Composite Attribute) is broken down into Street, City, State, Zip, and Fax. Table 22: Manufacturing_Company relation MANUFACTURING_COMPANY Manufacturing_Company_ID Manufacturing_Company_Name Street City State Zip Fax Telephone_No docsity.com 29 | P a g e 3.2 Step 2: Map Weak Entities 3.2.1 Catalog_Item Relation CATALOG_ITEM is represented as the weak entity dependent upon PRODUCT. A new relation is created with all the existing attributes of the CATALOG_ITEM plus the primary key of the PRODUCT which will be added as the foreign key and will form part of the primary key of the weak entity CATALOG_ITEM. Table 23: Catalog_Item relation PRODUCT Product_ID Prod_Actual_Cost Cost_Currency_cd Product_status Product_Count CATALOG_ITEM Name PRODUCT_ID Description Price 3.3 Step 3: Map Binary Relationships 3.3.1 Map Binary One-to-Many Relationships 3.3.1.1 CUSTOMER Submits ORDER Customer Customer_ID Cust_Name Cust_Address Cust_Acount_ Status_Code Order_Amt Submits Order Order_TimeOrder_ID Figure 25: Customer submits Order relation Two relations are created for CUSTOMER and ORDER respectively. Since CUSTOMER is on the one-side of the one-to-many relationship, its primary key is added to the ORDER relation as the foreign key. The foreign key relationship is indicated by an arrow. Table 24: Customer submits Order relations docsity.com 32 | P a g e key is added to the PAYMENT relation as the foreign key. The foreign key relationship is indicated by an arrow. Table 26: Customer_Account provides Payment relations CUSTOMER_ACCOUNT Acct_ID Create_Date Modi_Date Acct_Type_Cd PAYMENT Payment_ID Acct_ID Payment_Date 3.3.1.4 PRODUCT Included_In PRODUCT_LINE_ITEM Product Product_ Description Product_ Acctual_Cost Product_ Status Product_ Count Included _in Product_Line_Item Product_ID Product_Line_ ID Product_line_ Name Figure 28: Product included_in Product_Line_Item relation Two relations are created for PRODUCT and PRODUCT_LINE_ITEM respectively. Since PRODUCT_LINE_ITEM is on the one-side of the one-to-many relationship, its primary key is added to the PRODUCT relation as the foreign key. The foreign key relationship is indicated by an arrow. Table 27: Product included_in Product_Line_Item relations PRODUCT_LINE_ITEM Product_line_ID Product_Line_Name PRODUCT Product_ID Product_line_ID Prod_Actual_Cost Cost_Currency_cd Product_status Product_Count docsity.com 33 | P a g e 3.3.1.5 PRODUCT Displayed_by CATALOG_ITEM Product Product_ Description Product_ Acctual_Cost Product_ Status Product_ Count Displayed _by Product_ID DescriptionName Price Catalog_Item Figure 29: Product displayed_by Catalog_Item relation Two relations are created for PRODUCT and CATALOG_ITEM respectively. Since CATALOG_ITEM is on the one-side of the one-to-many relationship, its primary key is added to the PRODUCT relation as the foreign key. Since the CATALOG_ITEM already contains the PRODUCT_ID as the foreign key in the relation, PRODUCT relation has only the Catalog_Item_Name as the foreign key.The foreign key relationship is indicated by an arrow. Table 28: Product displayed_by Catalog_Item relations CATALOG_ITEM Catalog_item Name PRODUCT_ID Description Price PRODUCT Product_ID Catalog_item Name Prod_Actual_Cost Cost_Currency_Cd Product_status Product_Count docsity.com 34 | P a g e 3.3.2 Map Binary Many-to-Many Relationships 3.3.2.1 PRODUCT Supplied_by MANUFACTURING_COMPANY Product Product_ Description Product_ Acctual_Cost Product_ Status Product_ Count Supplied_by Product_ID Manufacturing_ Comapany Manufacturing _Company_ Name Manufacturing_ Company_ID Address Telephone_No Date_ Supplied Figure 30 : Product supplied_by Manufacturting_Company relation Two relations are created for PRODUCT and MANUFACTURING_COMPANY respectively. Another relation is created named as QUOTE to represent the M:N relationship. The primary keys of PRODUCT and MANUFACTURING_COMPANY are added to QUOTE relation as foreign keys. They also form the primary key of the new relation QUOTE. The foreign key relationship is indicated by an arrow. docsity.com 37 | P a g e Two relations are created for ORDER and PRODUCT respectively. Another relation is created named as ORDER_LINE to represent the associative entity. The primary keys of ORDER and PRODUCT are added to ORDER_LINE relation as foreign keys. They also form the primary key of the new relation QUOTE. The foreign key relationship is indicated by an arrow. Table 31: Order includes Product relations ORDER Order_ID Order_Time Order_Amt Order_Status ORDER_LINE Order_ID Product_ID Ordered_Quantity PRODUCT Product_ID Prod_Actual_Cost Cost_Currency_cd Product_status Product_Count 3.5 Step 5: Map Unary Relationships There are no unary relationships to map. 3.6 Step 6: Map Ternary (and n-ary) Relationships There are no ternary relationships to map. docsity.com 38 | P a g e 3.7 Step 7: Map Supertype/Subtype Relationships 3.7.1.1 Supertype PAYMENT with Subtypes CREDITCARD, INVOICE, and DIRECT BILLING Payment Credit Card Direct Billing is/a Invoice Credit Card Type Credit Card_No Expiry Date Maximum_ Credit_ Limit Limit_indicator Bill_to_ Address DirectBilling_Ac count_ID Invoice_No Created_by Created_on Date_ delivered Description Payment_IDPayment_Date Figure 33 : Supertype and Subtype relation For each entity type are relation is created. The PAYMENT being the supertype includes all the common attributes in the relation along with the primary key. A subtype discriminator is also added to PAYMENT relation. For all the other relations of the subtype, each will include the primary key of the supertype PAYMENT. Each of the primary key in the subtypes is prefixed to provide discrimination among the subtypes. The foreign key references are shown by the arrows. Table 32: Payment relation PAYMENT Payment_ID Payment_Date Payment_Type docsity.com 39 | P a g e Table 33: Credit Card relation CREDIT CARD C_Payment_ID Credit_Card_Type Credit_Card_No. Expiry_Date Card_Security_Cd First_Name Last_Name Street City State Zip Email Telephone_No Table 34: Invoice relation INVOICE I_Payment_ID Invoice_No. Bill_to_City Bill_to_State Bill_to_Fax Bill_to_Name Bill_to_Zip Code Created_by Created_on Date_Delivered Description Table 35: Direct Billing relation DIRECT BILLING D_Payment_ID Direct_Billing_Acct_ID Maximum_Credit_limit Limit_indicator Table 36: Payment relations PAYMENT Payment_ID Payment_Date Payment_Type CREDIT CARD C_Payment_ID Credit_Card_Type Credit_Card_No. Expiry_Date Card_Security_Cd First_Name Last_Name Street City State Zip Email Telephone_No INVOICE I_Payment_ID Invoice_No. Bill_to_City Bill_to_State Bill_to_Fax Bill_to_Name Bill_to_Zip Code Created_by Created_on Date_Delivered Description DIRECT BILLING D_Payment_ID Direct_Billing_Acct_ID Maximum_Credit_limit Limit_indicator docsity.com 42 | P a g e 4.1.6 Catalog_Item Catalog_item Name Product_ID Description Price There is one determinant in CATALOG_ITEM, and the functional dependencies are the following: Catalog_item_Name , Product_ID, Description, Price. Hence the candidate key is Catalog_item_Name and therefore forms the primary key in the relation. 4.1.7 Quote Product_ID Manufacturing_Company_ID Date_Supplied There is one determinant in QUOTE, and the functional dependencies are the following: Product_ID, Manufacturing_Company_ID Date_Supplied. Hence the candidate key is Product_ID and Company_ID, and therefore forms the composite key in the relation. 4.1.8 Database_Access Customer_reference_ID Email Password There is one determinant in DATABASE_ACCESS, and the functional dependencies are the following: Customer_reference_ID Email, Password. Hence the candidate key is Catalog_reference_ID and therefore forms the primary key in the relation. docsity.com 43 | P a g e 4.2 Step 2: Convert to Second Normal Form Second Normal Form is achieved by removing the partial functional dependencies between a nonkey attribute and the part of the primary key. 4.2.1 Product The PRODUCT relation shows full dependency among the non key attributes and the primary key as shown below, therefore it is also in the second normal form. Table 37: Product (2NF) PRODUCT (2NF) Product_ID Product_line_ID Prod_Actual_Cost Cost_Currency_cd Product_status Product_Count Catalog_item_Name 4.2.2 Order_Line The ORDER_LINE relation shows full dependency among the non key attributes and the primary key as shown below, therefore it is also inn the second normal form. Table 38: Order_Line (2NF) ORDER_LINE (2NF) Order_ID Product_ID Ordered_Quantity 4.2.3 Order The ORDER relation shows full dependency among the non key attributes and the primary key as shown below, therefore it is also in the second normal form. Table 39: Order (2NF) ORDER (2NF) Order_ID Customer_ID Order_Time Order_Amt Order_Status Full Dependency Full Dependency Full Dependency docsity.com 44 | P a g e 4.2.4 Customer_Account The CUSTOMER_ACCOUNT relation shows full dependency among the non key attributes and the primary key as shown below, therefore it is also in the second normal form. Table 40: Customer_Account (2NF) CUSTOMER_ACCOUNT (2NF) Acct_ID Customer_ID Create_Date Modi_Date Acct_Type_Cd 4.2.5 Customer The CUSTOMER relation shows full dependency among the non key attributes and the primary key as shown below, therefore it is also in the second normal form. Table 41: Customer (2NF) CUSTOMER (2NF) Customer_ID State_ID Cust_Name Create_Date Cust_Acct_status_cd Street City Zip Fax Contact_no. 4.2.6 Catalog_Item The CATALOG relation shows full dependency among the non key attributes and the primary key as shown below, therefore it is also in the second normal form. Table 42: Catalog_Item (2NF) CATALOG_ITEM (2NF) Catalog_item Name Product_ID Description Price Full Dependency Full Dependency Full Dependency docsity.com