GorillaSoft

Data Descriptions and Relations

  • Summary
  • Entity Relationship Diagrams
  • Legend
  • Figure 1 - Deliveries
  • Figure 2 - Retail Sales
  • Figure 3 - Household Sales
  • Figure 4 - News Box Sales
  • Figure 5 - Complaints
  • Figure 6 - Invoices

    Summary

    This section of the Overall Design Document includes the Entity Relationship Diagrams and a Data Dictionary. These items represent the problem domain described to us by our customer, Northam Publishing Inc., in manner that will form the basis for implementing a systems solution to meet their needs.

    While these components are more specific to the detailed design and implementation of the system, they are included in the Overall Design Document so that Northam can review GorillaSoft's interpretation of the product sales and delivery business practices.

    Entity Relationship Diagrams (ERD)

    The following six diagrams (figures 1-6) together provide an overall representation of the entities and relationships defined for the Northam Publishing problem domain. These entities and relationships are our model of what Northam has described for us as its business and operations practices. This model will be of major importance to the way we define the system data and structure the database for the system.

    The ERD has been divided up into several diagrams to simplify the representation of the complexity that exits in the problem domain. For this reason some entities appear on more than one diagram in order to capture all of their relationships.

    Each of the boxes represent an entity (or object). Each entity shows the primary key data attribute(s) and any other data attributes that are involved in establishing relationships in the relational database tables. The data attributes comprising the primary key are highlighted in bold typeface. The complete data attributes for each entity are defined in the data dictionary section.

    These diagrams are not a traditional Entity Relationship Diagram. They are closer to an object model and also fairly closely represent the data tables and data structures that will be built for the relational database solution. The lines connecting entities represent a relationship that has been defined to exist between them. Relationships can be defined such that:
    • each instance of entity A is related to one instance of entity B,
    • each instance of entity A is related one or many instances of entity B, or
    • each instance of entity A is related to zero, one or many instances of entity B.

    Each diagram is accompanied by a brief description of its entities and relationships.

    Back to Top


    Legend

    Back to Top


    Figure 1 - Deliveries

    A Delivery Route is an actual route that a delivery truck driver services by making product deliveries to Drop Locations, News Boxes and Retail Customers. A Drop Location is a delivery point where a Carrier will pick up their bundles of products each day for delivery. A Drop Location can service one or more Household Delivery Zones. A Household Delivery Zone is a geographical area of the city that contains a number of Household Customers. Household Customers within a Household Delivery Zone have their products delivered by one Carrier. However, the same Carrier may deliver products to several Household Delivery Zones.

    A District is a large geographic area of the city that contains zero, one or many News Boxes, Retail Customers and Household Customers. Since our model does not directly relate a District to a Household Delivery Zone, the boundary of a Household Delivery Zone can cross the boundary of a District. In the same way, a Delivery Route is not confined to the boundary of a District.

    Back to Top


    Figure 2 - Retail Sales

    This ERD models Retail Customers, the agreements they enter with Northam for taking products for sale on consignment and the daily Retail Consignment deliveries that result. Retail Customers enter into Retail Magazine Consignment Agreements and Retail Newspaper Consignment Agreements for the stocking of Magazine Products and Newspaper Products.

    The Retail Magazine and Newspaper Consignment Agreement entities will store the data necessary to describe the amounts of each product that the Retail Customer has agreed to take on consignment. The consignment quantities are defined beforehand, for example for each day of the week for a newspaper product, or by issue for a magazine product. Agreement data is central to generating each day's delivery lists. The Retail Consignment entity captures the actual delivered product amount and the returned amount for each day that a consignment is delivered pursuant to the Agreement. The Retail Consignment data is used to generate Retail Customer invoices (see Figure 6 - invoices). This data is also periodically used to update a Product Sales History data table in the database. Product Sales History data will provide Northam with a view of sales over time by District or by product.

    Back to Top


    Figure 3 - Household Sales

    This ERD is structurally very similar to Figure 2 - Retail sales. This ERD models Household Customers, their Household Magazine Subscriptions, their Household Newspaper Subscriptions and the actual Household Delivery that results. The description for Figure 2 applies to this ERD with the exception that, since Household Magazine Subscriptions are prepaid, the delivery data for magazines is only used for Product Sales History updates.

    Back to Top


    Figure 4 - Newsbox Sales

    This ERD models Northam's operations for a News Box. Each News Box entity will store data related to a specific News Box, such as its location. The entity News Box Consignment Order stores data describing the daily amounts of a Newspaper Product to be stocked in the News Box over a 7 day stocking pattern. The entity News Box Consignment captures the actual quantity of newspapers delivered to the News Box and also the amount that was unsold and collected for return on the following day. The data in the News Box Consignment entity will be used to update the database for Product Sales History. No invoices are generated from this data as Northam owns the News Boxes.

    Back to Top


    Figure 5 - Complaints

    This ERD models the handling of data related to Customer Complaints made by a Household Customer or a Retail Customer regarding the delivery service they receive. In addition, Complaints About News Boxes can be expected from other customers who purchase newspaper products from a News Box. A greater amount of data can be captured in the case of news box complaints, since information about the person making the complaint (e.g., name, telephone number, etc.) is not know, as it would be in the case of a Household or Retail Customer.

    Back to Top


    Figure 6 - Invoices

    This ERD models the billing functionality that Northam has requested. A Customer Account entity is maintained by each Household Customer and Retail Customer. The Customer Account table in the database stores a summary of the accounting information (e.g., Current Balance, New Charges, Overdue Amount) for the account. The Customer Account is kept current by periodically issuing an Invoice to each customer for products delivered to Household Customers or consigned to and sold by Retail Customers. The delivered or sold amounts of each product are available in the Household Delivery and Retail Consignment entities. The billing system can determine the prices to be charged for each customer, by product, from a combination of the Date field in the Household Delivery and Retail Consignment entities, the related subscription or consignment agreement (see ERD's 2 & 3) and the pricing schedules stored in the specific Newspaper Product or Magazine Product entities.

    Back to Top
    Back to Overall Design Document
    Back to Detailed Design Document