Thomas Walsh 336 Project 03/01/01John FranciscoPage 1Stage 1: ER Design1. Informal Description:The well-established company of Gall-Mart is running into a problem. Their agedcomputer system is not Year 2002 compliant. On the date 2/20/2002 the systems willfail. This is because being a European based Company they store dates in the formatdd/mm/yyyy. When 2/20/2002 rolls around, the date will be stored in the DB as20022002, which is also their end of file marker.Adapt the current database of stores, suppliers, shippers, products, and store stock tobecome year 2002 compliant, and add online ordering to enhance their system.2. List of users:Who WhyEveryone So people can check prices.Clients So people can order products over the web.Cashiers So employees can check orders out at the store.Stock Managers So an employee can enter stock into the system when it arrives.Product Managers So an employee can insert, delete, or update products in the list ofavailable products to sell.Returns Managers So an employee can arrange to accept returned Products.Orders Manger So an employee can orders stock for a store.Computer Operator So an employee can run any batch jobs that are need to optimizethe DB and basic system maintenance.Administrators So an employee can maintain any part of the system in case ofspecial situations.Thomas Walsh 336 Project 03/01/01John FranciscoPage 23. List of significant functionality:Application Function WhoPrice look up Find the price of an item. Everyone.New account Create a unique account name, andassociate background informationwith it.Everyone.Log On Uniquely identify a user of the system. Everyone.Place Web Order Create an order for products availablefrom suppliers.ClientsPlace Store Order Create an order for stocked items of astoreCashiersProcess Returns Produce an order to return “defective”product to Supplier, back to storeshelves. Credit Client, Debit StoreProfit.Returns ManagersUpdate Products Add, remove and update products aswell as their suppliers.Product MangersUpdate Stock Update stock quantity to the DB whenit arrives at the store.Stock Managers (add only)Cashiers (subtract only)Place Supplier Order Summarize the day’s orders andprovide the suppliers with a completelist of where to send the products thatwere ordered that day.Order ManagersSystem Maintenance Run any jobs needed to optimize theDB. An example job would be toremove inactive accounts after a yearworth of inactivity.Computer OperatorManage Accounts Promote accounts to have moreauthority than just placing orders.Reset passwords. Disable accounts.Computer OperatorEdit Stores Maintain the table of Stores.Computer OperatorEdit Shippers Maintain the table of Shippers.Example of items addresses andshipping rates.Computer OperatorThomas Walsh 336 Project 03/01/01John FranciscoPage 34. E-R Diagram of the Database:Break down of requirements:• Entity-Attributes are not contained in this diagram for ease of reading.• Keys are not represented, as the entity-attributes are not depicted.• There are eight (8) Entities.• There are seven (7) Relations.• There are zero (0) 1 to 1 relations.• There are four (4) 1 to many relations.• There are one (1) many too many relations.Please refer to the entities below to view the attributes and keys.Process Relation: Description of the entire order process in the DBMSOrderProduct ListShipperContains ProductAddressClientPlacesSupplys SupplierHasQtyOrder InfoOrderStoreAddress ofCost ofShippingThomas Walsh 336 Project 03/01/01John FranciscoPage 4Secondary DB: User Interface Only.Break down of requirements:• There are two (2) Entities.• There is one (1) Relation.• There are zero (0) 1 to 1 relations.• There are zero (0) 1 to many relations.• There is one (1) many too many relation.Permissions Relation: Descriptions of the way permissions are implemented for theUser Interface in DBMS.LinksLinkUser LevelsUser Level NamePermissionsLink NameLink ID User Level IDThomas Walsh 336 Project 03/01/01John FranciscoPage 55. Detailed description of each entity:• Please note: the drawing package did not support dashed-underline, double underline wassubstituted to depict weak keys, but they show up as bold-underlines in word. Please refer tothe key type column of the descriptions for a clear definition of this information.• Phone Domain type = Area/int <1000, Exchange/int < 1000, Extension/int <10000• Name Domain Type = First/VarChar(20), Middle/VarChar(20), Last/VarChar(20)Client EntityEntity or Attribute KeyTypeDomain ReasonClient EntityRecord client specific information.Account Name Primary VarChar(20) Unique name or number to identify this ClientPassword VarChar(20) Combined with the account name will provide alevel of security for an account.Enabled Int When disabled, contains a value of zero, theaccount is not allowed to log onto the system.Name: First Middle Last Name Identify who this client is for shipping orbilling.Email Account VarChar(50) For promotional salesPhone Number: Homeand WorkPhone Contact information if there is a problem withthe orderBilling Address ID Foreign Int This address probably won’t change much, butwe need to record it when we charge theperson’s CC.Mailing Address ID Foreign Int We need to know where to send the productonce it is ordered.Total Purchases Numeric(10,2) Calculated from orders placed, is used tocalculate frequent client discount.Date Last Logged On Date Used to determine when to purge the accountfrom the DB.Permissions Foreign VarChar(20) Used to determine the amount of access to theDB the client can have.Represents Foreign Int Helps to track which store gets updated withsales data.ClientMailing Address IDClient IDEmail Account Billing Address IDTotal PurchasesPermissionsName: First,Middle, LastDate lastlogged onPhone Number:Home and workRepresentsPassword EnabledThomas Walsh 336 Project 03/01/01John FranciscoPage 6Store EntityEntity orAttributeKeyTypeDomain ReasonStore EntityRecord all stores.Store number Primary Int Uniquely identify a store in the DB.Email account VarChar(50) One type of contact method.Phone Number Phone Second contact method.Manger Name Name Identify the store manager.Store AddressIDForeign Int Identifies the stores location.Cost Numeric(10,2) Calculated from the sum of the base cost of allitems in the store.Total Sales Numeric(10,2) Calculated from individual sales. Used to calculateProfit, which in turn provides a performancemeasure for the