The file attached below and the class is info systems development. I need this assignment on this Monday.

The file attached below and the class is info systems development. I need this assignment on this Monday.
Entities and Relationships   An Entity Relationship Diagram is the most common data model for information systems (as described in the textbook).  Watch the following videos to get a better sense for the components of an ERD and how to create an ERD: start at the 1:30 mark https://www.youtube.com/watch?v=mQ4D0drMrYI (Links to an external site.) https://www.youtube.com/watch?v=u2QqjofJvGo (Links to an external site.)https://www.youtube.com/watch?v=-fQ-bRllhXc (Links to an external site.)   And watch this video on some additional issues such as normalization and referential integrity: https://www.youtube.com/watch?v=dZNuafOaM1s (Links to an external site.) NOMAS 03 In my last memo, I briefly described NOMAS’ direct order procedures (refer to that memo as necessary). It is time that we formally started analyzing and documenting all the direct-order data being collected, and getting a better idea of how it is being used. I took some notes on each of the folders and file descriptions, but they were quite disorganized, so it is quite possible that I might have over-looked some things. Here are some of my initial findings:   DIRECT-ORDERS-FILE As you all remember, customer order forms (whether they are received in the mail or taken over the phone) are kept in various folders before they are actually entered in the database (consult my previous memo for more information). Each order form has an order number, customer information (name, address, telephone number), item information (itemcode, number ordered, color, size, unit price, total cost), shipping information (per item weight, total item weight, UPS cost), sales tax information (if any), and method of payment (charge (if so, type and charge number), personal check, cashier’s check, or money order). The folders I am referring to include:   PROBLEM-ORDERS. The difference is that these forms have a piece of paper attached to them. The piece of paper contains a list of what information is missing or in error. Once these items are cleared-up by the processing clerks, they are placed in the accepted-orders folder. ORDERS-RECEIVED. This contains the order forms without the paper attached. If there are no problems, the order forms are placed in this file. In the afternoon, they are removed from this file and sorted (based on method of payment) and placed into the various files listed below. CREDIT-CARD-ORDERS-PENDING. The orders placed in this file usually remain here only for a short time. At the end of each day, one of the clerks orders them by credit card issuer (98% are either Visa or MasterCard), and places a call to the issuer for approval (a large majority are approved). If they are not approved, they are placed back in the Problem-orders file (with a note). If approved, they are placed in the accepted orders file. PENDING-ORDERS. This file (which probably should be called the pending-check-orders) contains those order forms for which a personal check was submitted, and has not yet cleared the bank. Each day, the checks are taken to the bank and deposited. At the same time, the bank provides a list of checks which have been cleared, and a list of checks which were returned. Those orders which have been cleared are removed and placed in the accepted-orders file; orders with returned checks are removed, noted, and placed back in the problem-orders file. ACCEPTED-ORDERS. Those orders that have been paid by money order or cashier’s check are put directly into this file (assuming there are no entry errors). Although sometimes the checks do not clear, this is apparently quite rare. When/if personal checks and credit card orders clear, the orders are also placed in this file.   Each afternoon the accepted-order data is entered into the Direct-Orders database. This database contains the following items:   Cust-Ord-Num (Char[12]), Cust-Number (numeric[7.0]), Item-Code(char[12]), Num-ordered (numeric[4.0]), Color (char[10]), Size (Char[8]), weight (Numeric[6.0]), unit-price (numeric[8.2]), total-cost (numeric[8.2]), shipping-cost (numeric[8.2]), taxrate (numeric[5.3]), item-tax (numeric[5.2]), paytype (numeric[2.0]), chargecode (character[16]), date-received (date)   The database is indexed on Cust-Ord-Num. No, I don’t know why. Quite a mouthful, isn’t it? I couldn’t believe it either. The record is repeated for each item the customer orders. That is, if the customer orders 10 items, 10 records will appear in the database. I wonder what idiot designed that database? I was pleasantly surprised when I saw they had created a separate customer-data-file database (I almost expected them to repeat customer data in each record).   CUSTOMER-DATA This database contains one record per customer. The record structure is as follows:   Cust-Number (numeric[7.0]),              same as in the Direct-Orders-File Cust-Name (Char[20]),                        last name, first name Cust-street (char[30]),                         Street address Cust-city (char[20]),                             City/Town Cust-State (char[2]),                            State Cust-zip (char[8]),                                Zipcode Cust-Area (numeric[3.0]),                    Areacode Cust-Phone (char[7]),                          Telephone number Date-First (date)                                   Date of first order   The database is indexed on Cust-Number. Not too bad. I actually didn’t know that NOMAS had such a database. I had to interrupt Lynne to find out who created this database. She told me that she had designed it and had Marcus install it (bright woman; I wonder why she hasn’t done anything with the direct-orders-file?). She also mentioned that she had Blake develop a few other files, such as the Products-File, the Manufacturer-File, the Supplier-Filled-File, the Items-on-Order-file, the Backorders-File, the Refund-File, and the Completed-Transactions-File.   PRODUCTS-FILE I dug out the Products-file description and found the following: Item-code (char[12]), Item-descript (char[25]), unit-cost (numeric[8.2]), Carried (logical) The database is indexed on Item-code. No, I don’t know why again. I was obviously curious. If an item was not carried in stock, how did they know what manufacturer carried it, and how to get it to the customer? Once again, I went and interrupted Lynne who was on the phone with another manufacturer (what does Pedro do all day?). When she saw me, she stated that the manufacturer code is part of the item code (I should have known).   MANUFACTURER-FILE The fields contained in this database include: Man-code (char[4]), Man-Name (char[20]), Man-street (char[25]), Man-city (char[20]), Man-State (char[2]), Man-Zip (char[9]), Man-Areacode (numeric[3.0]), Man-phone (numeric[7.0]), Man-Pay (numeric[2.0]), Man-ship (logical) The database is indexed on Man-code. I could figure out that the Man-Pay field was a code which applied to the manufacturer due dates (e.g., 2% within 10 days, 30 days net, etc.). I did ask what Man-ship was used for. Lynne explained that if a manufacturer is equipped to send off the customer ordered items directly (Man-ship is set to ‘true’), then they will have them do so, otherwise they will have to order the merchandise and send it off to the customer when they receive it (I was so embarrassed; I should have known that too).   SUPPLIER-FILLED-FILE The supplier-filled database is really simple: SF-Ord-Num (char[12]),          this is the Cust-Ord-Num Item-Code (char[4]),                the same as in the products file Num-Ordered (numeric[3.0]),  the number the customer ordered Number-sent (numeric[3.0]),   the number to be sent to the customer Date-Ordered (date),               when the order to the Manufacturer was placed Date-Confirmed (date)             the date the order was sent out The database is indexed on SF-Ord-Num. Obviously, the supplier-filled database is used if NOMAS does not carry the item or does not have it in stock AND the manufacturer will send it directly to the customer. I do see some potential problems with this setup.   ITEMS-ON-ORDER-FILE If NOMAS has to order the items from the manufacturer, the items-on-order database is updated. It contains the following fields: IOO-Ord-Num (char[12]),                   this is the Cust-Ord-Num Item-Code (char[4]),                           the same as in the products file Num-ordered (Numeric[3.0]),             the number ordered Date-Ordered (date),                          when the order to the Manufacturer was placed Date-received (date)                           the date the order was received by NOMAS Num-Received (numeric[3.0])            the number NOMAS received The database is indexed on IOO-Ord-Num. When NOMAS receives the orders, they are checked off. If the number received is less than the number ordered, the backorders file is updated.   BACKORDERS-FILE This file contains: BO-Ord-Num (char[12]),                     this is the Cust-Ord-Num Item-Code (char[4]),                            the same as in the products file Num-Due (Numeric[3.0]),                    the number on Backorder Date-Ordered (date),                           when the order to the Manufacturer was placed Date-received (date),                           the date the order was received by NOMAS Num-Received (numeric[3.0])             the number NOMAS received The database is indexed on BO-Ord-Num.   REFUND-FILE If NOMAS is unable to get the items, or the customer returns the items, this information is entered into the database. The database contains the following information: Cust-Name (char[25]), cust-street (char[25]), cust-city(char[20]), Cust-State (char[2]), cust-zip (char[7]), refund-amt (numeric[7.2]), refund-date (date)   COMPLETED-TRANSACTION-FILE If all is well with the world and the customer receives their order, the customer order data is removed from the direct-orders-file and placed in this file. The information taken from the Direct-Orders-File includes: Cust-Ord-Num (Char[12]), Cust-Number (numeric[7.0]), Item-Code (char[12]), Num-ordered (numeric[4.0]), Color (char[10]), Size (Char[8]), weight (Numeric[6.0]), unit-price (numeric[8.2]), tot-ship-cost (numeric[8.2]), taxpaid (numeric[5.3]), paytype (numeric[2.0]), date-received (date), Comp-date (date) The database is indexed on Comp-date (date of completion). Well, I must admit, even though there are a lot of obvious problems, I think it could be worse. Lynne has done a tremendous job in the relatively short time she has been with the company. I was exhausted by the time I went through all the files.   RETAIL SALES For the retail side of the house, the idea is to follow Margi’s procedures in the Southern Region. I suppose we can play their game and pretend that all the retail stores are following the same procedures. Let’s just focus on these and make our suggestions based on them. Each day, Margi has the daily summaries entered into her computer. She apparently has the following files set up:   INVENTORY-FILES Since the Southern Region is the only one with a centralized warehouse, this database does not extend well to the other regions’ operations. However, the way that Margi has set it up is worth noting. First, she keeps a running count of all items in the regional warehouse: Regional-Inventory  (ITEM-CODE [same as NOMAS’ code],                                     Man-code [Same as NOMAS’ code],                                     Item-description [character[30]],                                     Number-in [numeric[4]],                                     Unit-Cost [numeric[8.2]],                                     Sale-Price [Numeric[8.2]])   I decided to call Margi for more details. I asked her a number of questions, including why she had a Sale-Price field in the database. “Initially, I toyed with the idea of giving each of the stores the items at our cost. However, while that would improve the profits for each of the stores, I would have to account for the costs of running the warehouse and transporting the merchandise to the stores. I decided to run the warehouse as a profit center also. Blake, Mr. Srinivasen, and I have worked closely together in calculating the sale-price. It had to be no more than the cost to the stores had they ordered the merchandise themselves, and is generally much lower. I was bound and determined to have the regional warehouse show a profit. So far, it has, although just barely.” Margi also keeps track of in-store inventories, although she admitted that the accuracy level is not nearly what it should be. Then again, it does give her minimal control over each store’s inventory and could serve as a basis for how the items are moving in the stores. For each store, Margi keeps updated inventory records based on the daily reports she gets. When I talked to her, I asked her where she found the time to enter all of this data. She stated that it wasn’t really too bad since there was one program which updated all the databases. Anyway, each store has their own file laid out as follows (NN refers to the store number): Inventory-NN  (ITEM-CODE [same as the Regional Item Code],                           Man-Code [same as the regional Manufacturer Code],                           Sale-Price)   Obviously, Sale-Price is the price that the store sells the item for. Not too bad. Some redundancies, granted. It’s time for a pause. I think we are all aware the dilemma. Should we start working towards the centralized warehouse approach? How centralized? One main warehouse to service all of NOMAS, including direct sales? One central warehouse for each region? A variable number of warehouses, dependent upon the region? No centralization? What? A few things to keep in mind. First, IT’S NOT OUR DECISION. That belongs to Pedro and Enid, and we all know that at least one of them is a wee bit stubborn. On the other hand, we would be negligent if we were not concerned. We were hired to analyze their information system and (possibly) design a new one. Sometimes that means that we should make suggestions for operating changes, provided they do not hinder overall functioning and are acceptable to management. So what do we do? Assume that we are going to operate under the present procedures. Relatively easy. According to Pedro, we should follow Margi’s policies. That means for the other regions, just use the individual store database layout. What if we lean toward centralized regional warehouses? Well, we’ve already got the basic framework (which we can readily improve on). However, we might face some resistance (Ms. Elam, for example). Could we phrase our argument in such as fashion as to make it appealing to those who might oppose the changes? I think so. We know that these people have certain preferences, desires, and needs. We could put forth our suggestions in a manner that would appeal to them and make them understand that they would directly benefit from them. The same goes for the extreme (one central warehouse). OK, so maybe we are getting ahead of ourselves. But start thinking about it. NOW.   DISTRIBUTOR-FILE This file is intended to correspond to NOMAS’ Manufacturer file. There are some discrepancies, however (what a surprise). DISTRIBUTOR (char[4]), Dist-Name (Char[30]), Street (char[30]), City (Char[25]), State (char[2]), Zipcode (Char[7]), Telephone (char[10])   ITEMS-ORDERED-FILE Since Margi gets copies of all the purchase orders placed by each of the stores, she keeps the following information: PONumber (Char[10]), Store-Code (Char[3]), DATEORDERED (Date), ItemCode (same as above), Num-Ordered (numeric[4]), DateReceived (date), NumReceived (Numeric[4]), Num-BackOrdered (Numeric[4]) The first character in store code is ‘S’ (for the Southern Region). Apparently it would be ‘C’ or ‘N’ for the Central and Northern Regions, if we were to follow through with this format for the other stores. But wait. I asked Benn if the PO Numbers did not also contain unique identifiers for the stores? “Well, ah, yes, I believe so.” There is one record for each item ordered. I admit I was curious about this. I asked Benn how the Num-BackOrdered field was used. He said that when Margi originally set up the database, she didn’t take into account the fact that orders received were not always complete. In order to account for that, she added the Num-BackOrdered Field. Now, if an order is incomplete, the pertinent data is entered, and a new record is generated. In the new record, the PONumber, the DateOrdered, and the ItemCode remain the same, but the Num-Ordered is entered as the Num-BackOrdered. When the items are received, the fields are then filled in accordingly.   DAILY-SALES-FILE This store lists the daily sales of each of the stores. Store-Code (as Above), DATE (date), Sale-Type (numeric[2]), Item-Code (as Above), Num-Sold (Numeric[5.0]), Unit-price (numeric[8.2]), Total-sale (Num[8.2]), Total-Sales-Tax (Numeric[7.2]) I asked about the sale type. Benn mentioned that it was Margi’s idea. Rather than setting up a refund database, she decided to put this field in each record. Zero (0) meant that it was a cash sale, One (1) was a personal check, Two (2) was a charge, 10 was a cash refund, 11 was a charge refund. She stated that she has assigned the warehouse the store code ’00’ and Sale-Type ’99’ for inter- store sale (apparently, there are sometimes sales between the retail stores also). How does she keep track of inter-store sales? She has developed another database called:   INTER-STORE-SALES PONUMBER, Selling-Store-Code Neat. Notice that PONUMBER contains the number of the store placing the order. Selling-Store code is the number of the store that sent the goods to the other store. To get information on the items sold, she need only look in the Items-Ordered-File. I noticed that Margi picked-up on the relationship between PONumber and the store ordering the merchandise. Why didn’t she also use this relationship in the Daily-Sales-File? I glanced back at the structure of the Items-Ordered-File. Wasn’t it difficult to locate the PONumber (remember, the store is indexed on Date-Ordered). “Yes, it is tedious. But it is not too bad since the records are stored in chronological order, as are the reports we generate from them.” True, but still … I asked if perhaps I could see Milt about what he does with the information he gets. Each day Warren Remus receives the daily summaries of sales from each of the districts (as well as the mail order daily sales summary and inter-company sales information). They enter the following data into the database:   DAILY-SALES-SUMMARY DATE (date), Transaction-code (char[5]), Item-Code (as Above), Num-Sold (Numeric[4.0]), CGS (Numeric[8.2]), Tax (Numeric[7.2]), Sale-price (numeric[8.2]) I asked about Transaction-codes. They apparently are calculated based as follows: Character 1:            A if a direct-order sale                                 B if a retail store sale                                 C if an intercompany sale                                 D if a direct-order refund                                 E if a retail store refund Character 2:            The District code (if a retail sale) Characters 3-5:       The retail store code (if a retail sale)   I could readily figure out the Item-Code, Num-Sold, and Sale-Price. But why CGS (obviously Cost-Of-Goods-Sold)? For quick calculation of Profits? Probably, but maybe a little redundant. But, what about inter-company transfers? “Well,” Milt droned, “we have another file which keeps track of payments.”   INTER-COMPANY-TRANSFERS INTER-COMPANY-ORDER-NUMBER (Char[7]), From-Company-Code (Same as store code), To-Company-code (ditto), Item-Code (as before), Number (Numeric [4.0]), CGS (numeric[8.2]), Shipping-cost (Numeric[6.2]), Tax, (Numeric[7.2]), Sales-Price (Numeric[8.2]), Amount-owed (Numeric[8.2]) What a mess. Duplications, unnecessary information. Questions : My head is splitting. Basically, we need to get a better feel for the data relationships. For both Direct and Retail, you are to: Identify the basic entities. For the present system, determine which entities can be described by the data. Identify or define the keys for each entity. Determine primary keys, candidate keys, and combination keys. Lay out the E-R diagram. Identify and map the data elements So, you need to create two ERDs – one for Direct and one for Retail.  Be sure to keep them separate and to keep the business situations separate between them.