Overall Design Document
CPSC 451 Supplier Group #1

Department of Computer Science
University of Calgary
26 January 1997

Page maintainer: Terrence Asgar-Deen
terrence@cpsc.ucalgary.ca
  • Terrence Asgar-Deen
  • Patrick Chan
  • Thomas Hui
  • Carsten Jaeger
  • Matthew Johnson
  • Brian Low
  • Hoang Nguyen
  • Kevin Pattison
  • Csaba Suveges
  • Jeremy Tang
  • Leena Thakkar
  • Al-Amin Vira
  • Lin Zhang
    •

    Data Dictionary

    The data dictionary defines and specifies all data elements in the system. Each element is provided in a tabular format for easy reading.

    Name cellular #
    Kind Attribute
    Type Integer
    Description A non-key attribute of entity Salesperson.
    Maximum length of the attribute is 10 digits.
    The format of the attribute is: (XXX)XXX-XXXX.

     

    Name city
    Kind Attribute
    Type String
    Description A non-key attribute of entity Customer.
    The attribute describes the city in which the customer resides.
    The DEFAULT city is Calgary.
    The maximum length is 16 characters.

     

    Name comments on salesperson
    Kind Attribute
    Type Alphanumeric
    Description A non-key attribute of entity Salesperson.
    Maximum length of the comments is 8 lines.
    The comment would be a short description about the salesperson.

     

    Name comments on Customer
    Kind Attribute
    Type String
    Description A non-key attribute of entity Customer.
    Maximum length of the comments is 8 lines, 80 characters long.
    The comment would be a short description based on the past history of the particular customer.

     

    Name company name
    Kind Attribute
    Type Alphanumeric
    Description A non-key attribute of entity Customer.
    Maximum of length is 35 characters.

     

     

    Name company's email
    Kind Attribute
    Type Alphanumeric
    Description A non-key attribute of the entity Customer.
    The company's email is a global E-mail address for the company.
    The format is: (loginID)@(Address).
    The maximum length of the E-mail address is 35 alphanumeric characters.

     

    Name company's telephone #
    Kind Attribute
    Type Integer
    Description A non-key attribute of entity Customer.
    The length of the telephone number is 10 digits.
    The format is: (XXX)XXX-XXXX.

     

    Name contact's email
    Kind Attribute
    Type Alphanumeric
    Description A non-key attribute of the entity Customer.
    The contact's email is the E-mail address of the company's contact person.
    The format is: (loginID)@(Address).
    The length of the E-mail address should not contain more than 35 alphanumeric characters.

     

    Name contact's name
    Kind Attribute
    Type String
    Description A non-key attribute of entity Customer.
    The contact's name is the name of the company contact person.
    The length should be no more than 20 characters.

     

    Name contact's telephone #
    Kind Attribute
    Type Integer
    Description A non-key attribute of entity "Customer".
    The length of the telephone number should be 10 digits long.
    The format is: (XXX)XXX-XXXX.

     

    Name contains
    Kind Relationship - Mc:Mc
    Type @Order ID + @Product number + @Motif Number
    Description Relationship between entity Product and entity Order.
    Each order can have zero or more products.
    Each product can be on zero or more orders.

     

    Name cost
    Kind Attribute
    Type Float
    Description A non-key attribute of entity Product.
    It should have 5 digits and 2 decimal places.
    The maximum amount would be less than $100,000.

     

    Name Customer
    Kind Entity
    Type @Customer ID + Company name + Street address + Postal code + Contact's name + Contact's telephone + Contact's email + Company Description + salesperson ID + Date last contacted + City + Province
    Description This entity corresponds to the Customer who are currently served by the company.
    The entity would include all personal information of the customer and the general information about the salesperson who serves him/her.

     

    Name customer ID
    Kind Primary Key
    Type Integer
    Description A primary key attribute of entity Customer.
    Each customer has a unique customer ID number.
    The customer ID should be exactly 8 digits in length.

     

    Name date
    Kind Attribute
    Type Integer
    Description A non-key attribute of entity Order.
    The date is the purchase day the customer places the order.

     

    Name date last contacted
    Kind Attribute
    Type Integer
    Description A non-key attribute of entity Customer.
    The attribute is used to record the date that a salesperson last visited the customer.
    The format is: DD/MM/YYYY (Day/Month/Year) for a total of 8 digits.

     

    Name description of product
    Kind Attribute
    Type Alphanumeric
    Description A non-key attribute of entity Product.
    The description is used to describe the product in detail.
    The maximum length of the attribute is 5 lines.

     

    Name email
    Kind Attribute
    Type Alphanumeric
    Description A non-key attribute of entity Salesperson.
    The e-mail is the the E-mail address of the salesperson.
    The format is: (loginID)@(Address).
    The length of the E-mail address should not contain more than 35 alphanumeric characters.

     

    Name fax number
    Kind Attribute
    Type Integer
    Description A non-key attribute of entity Customer.
    Maximum length of the attribute is 10 digits.
    The format is: (XXX)XXX-XXXX.

     

    Name home phone #
    Kind Attribute
    Type Integer
    Description A non-key attribute of entity Salesperson.
    Maximum length of the attribute is 10 digits.
    The format is: (XXX)XXX-XXXX.

     

    Name Order
    Kind Entity
    Type @order ID + customer ID + salesperson ID + product ID + quantity +
    date + sub-total + GST + total cost
    Description The entity Order represents a completed transaction of an order. It includes all the information within this transaction.

     

    Name max visit/day
    Kind Attribute
    Type Integer
    Description A non-key attribute of entity Salesperson.
    Represents the maximum number of people a salesperson can visit on any given day.
    Maximum length of the attribute is 2 digits.

     

    Name motif #
    Kind Primary Key
    Type Alphanumeric
    Description A primary key attribute of entity Product.
    The format is a 3 digits motif number (mmm).
    This motif number is classified in the following format (color, design and font).

     

    Name name
    Kind Attribute
    Type String
    Description A non-key attribute of entity Salesperson.
    Maximum length of the string is 20 characters.

     

    Name Next expected contact
    Kind Attribute
    Type Integer
    Description A non-key attribute of entity Customer.
    This attribute is used by the salesperson to determine when to next visit this particular customer.
    The format is: DD/MM/YYYY (Day/Month/Year) for a total of 8 digits.

     

    Name order ID
    Kind Attribute
    Type Integer
    Description A key attribute of entity Order and must be exactly 8 digits long.

     

    Name pager
    Kind Attribute
    Type Integer
    Description A non-key attribute of entity Salesperson.
    Maximum length of the attribute is 10 digits.

     

    Name places order
    Kind Relationship - 1:Mc
    Type @Order ID + @Customer ID
    Description Relationship between entity Customer and entity Order.
    Each customer can have zero or more orders.
    Each order is for only one customer.

     

    Name postal code
    Kind Attribute
    Type Alphanumeric
    Description A non-key attribute of entity Customer.
    Represents the postal code of the customer.
    It should be exactly 6 alphanumeric characters in the form: (letter|number|letter number|letter|number).
    Example: T2N 4V3

     

    Name Product
    Kind Entity
    Type @product number + motif number + size + cost + description
    Description The entity Product contains all information about all the products that can be sold.

     

    Name product #
    Kind Attribute
    Type Integer
    Description A key attribute of entity Product.
    It is exactly 4 digits in length.

     

    Name province
    Kind Attribute
    Type ["BC","AB","SK","MB","ON","PQ","PI","NS","NB","YN","NT"]
    Description A non-key attribute of the entity Customer.
    The default value when a new customer is added is "AB".
    The other options are for future expansion of your company.

     

    Name quantity
    Kind Attribute
    Type Integer
    Description A non-key attribute of entity Order.
    It describes how many forms of one type the customer has ordered.
    The maximum value for this number 2**31.

     

    Name Salesperson
    Kind Entity
    Type @salesperson + name + territory + email + pager + cellular + office phone + fax number + max visit/day + comments
    Description This entity contains all information on the salespeople who are working or have worked for your company.

     

    Name salesperson ID
    Kind Attribute
    Type Integer
    Description A key attribute of entity Salesperson.
    It should be exactly 8 digits in length and unique.

     

    Name served by
    Kind Relation - 1:Mc
    Type @Salesperson ID + @Customer ID
    Description Relationship between entity Customer and entity Salesperson.
    Each customer can have only one salesperson.
    Each salesperson can have zero or more customers.

     

    Name size
    Kind Attribute
    Type [8.5 * 11 | 8.5 * 14 | 8.5 * 6]
    Description A non-key attribute of entity Product which describes the size of a particular form.

     

    Name street address
    Kind Attribute
    Type Alphanumeric
    Description A non-key attribute of entity Customer.
    This describes the address where the customer is located in the city.
    The maximum length is 80 characters.
    Example: 2132 12th Street NE

     

    Name Territory
    Kind Weak Entity
    Type @Customer ID + postal code + territory ID + salesperson
    Description This weak entity is used to keep track of which territory a customer resides in.

     

    Name territory ID
    Kind Attribute
    Type Integer
    Description A non-key attribute of entity Salesperson.
    The territory ID will be a number since there will be many subparts of the city which must be kept separate.