RDB PRIME!
Engineering
Home
Research Paper(s)
Resume
Technology Items
Site Map
Site Search
 
 It is 10:10 PST on Friday 01/31/2025

Wine Club MDM

Wine Club Warehouse

Created by: Wine Club Group
Date: Sat, Oct 12, 2002
Time: 1:32 PM PST


Table of Contents

  1. Data Models
    1. Main
  2. Data Dictionary

Data Models

Document: WineWarehouse.erd

Data Model

Image of Main

Main

Related Diagrams:

Related Dictionary Entries:

Area
Customer
Sales
Time
Wine

Return To Contents



Data Dictionary

Name: Area
Composition:
*Area'AreaID +
Area'AreaDescription
Definition:
Area DIMENSION Table
References:
ERD(DIMENSION) "WineWarehouse.erd" "Main"
SPEC(KEY) "AreaDimensionSpecs.spc" ""

Name: Area'AreaDescription
Composition:
Definition:
#GeneralDetails
Describe: This attribute is the decription given to each area identification. For example 001 is represented as New York City, 002 is represented as Albany, 003 is represented as Columbia, etc.
##
#UserDetails
##
#SQLDetails
Physical: AREADESCRIPTION
Qualify: notnull
DataType: VARCHAR2(4000)
##
References:
ATTRIBUTE

Name: Area'AreaID
Composition:
Definition:
#GeneralDetails
Describe: This attribute is made up area identifications. There are currently 200 area identifications that represent city locations throughout North America.
##
#UserDetails
##
#SQLDetails
Physical: AREAID
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Customer
Composition:
*Customer'CustomerID +
Customer'CustomerName +
Customer'CustomerAddress
Definition:
Customer DIMENSION Table
References:
ERD(DIMENSION) "WineWarehouse.erd" "Main"
SPEC(KEY) "CustomerDimensionSpecs.spc" ""

Name: Customer'CustomerAddress
Composition:
Definition:
#GeneralDetails
Describe: This attributes tracks the cutomers address. Address is defined as the street address.
##
#UserDetails
##
#SQLDetails
Physical: CUSTOMERADDRESS
Qualify: notnull
DataType: VARCHAR2(50)
##
References:
ATTRIBUTE

Name: Customer'CustomerID
Composition:
Definition:
#GeneralDetails
Describe: This is the attribute customerID. It is defined as 6 digit numeric. A customer is defined as an end user or a retail/wholesale store.
##
#UserDetails
##
#SQLDetails
Physical: CUSTOMERID
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Customer'CustomerName
Composition:
Definition:
#GeneralDetails
Describe: This attributes stores the name of the customer.
##
#UserDetails
##
#SQLDetails
Physical: CUSTOMERNAME
Qualify: notnull
DataType: VARCHAR2(50)
##
References:
ATTRIBUTE

Name: Sales
Composition:
@Sales'CustomerID +
@Sales'WineID +
@Sales'OrderTimeID +
@Sales'AreaID +
Sales'InvoiceNumber +
Sales'Quantity +
Sales'ItemCost +
Sales'ItemPrice
Definition:
Sales FACT Table
References:
ERD(FACT) "WineWarehouse.erd" "Main"

Name: Sales'AreaID
Composition:
Definition:
#GeneralDetails
Describe: This is the primary key Area ID for the Dimension Table Area and i s the foreign key for the Sales Fact Table.
##
#UserDetails
##
#SQLDetails
Physical: AREAID
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Sales'CustomerID
Composition:
Definition:
#GeneralDetails
Describe: This is the Customer ID number from the operational data ERD named CustomerID. This the FK in this Table, and is the PK in the Customer Dimension Table.
##
#UserDetails
##
#SQLDetails
Physical: CUSTOMERID
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Sales'InvoiceNumber
Composition:
Definition:
#GeneralDetails
Describe: This is the invoice number that is associated with the Operational ERD Customer Order Table attribute OrderID.
##
#UserDetails
##
#SQLDetails
Physical: INVOICENUMBER
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Sales'ItemCost
Composition:
Definition:
#GeneralDetails
Describe: This is the Item Cost from the operational data in the Order Item table, or the CostPrice from the Accessory Table.
##
#UserDetails
##
#SQLDetails
Physical: ITEMCOST
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Sales'ItemPrice
Composition:
Definition:
#GeneralDetails
Describe: This is the item price that is defined in the Operational Data Wine Table as PricePerBottle, or the price that is given from the Accessory Table as SellingPrice.
##
#UserDetails
##
#SQLDetails
Physical: ITEMPRICE
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Sales'OrderTimeID
Composition:
Definition:
#GeneralDetails
Describe: This is the primary key used in the Tiime Dimension table and is the foriegn key in the Sales Fact table. This value is a time stamp code, not a date. Time ID is made up of period number, week number, day number, and year.
##
#UserDetails
##
#SQLDetails
Physical: ORDERTIMEID
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Sales'Quantity
Composition:
Definition:
#GeneralDetails
Describe: This is the Quantity attribute in the Order Item table for a particular customer for a particular wine.
##
#UserDetails
##
#SQLDetails
Physical: QUANTITY
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Sales'WineID
Composition:
Definition:
#GeneralDetails
Describe: This is the Wine ID from the operational Data Wine Table. It it the FK in this table and the PK in the Wine Dimension Table.
##
#UserDetails
##
#SQLDetails
Physical: WINEID
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Time
Composition:
*Time'TimeID +
Time'Date +
Time'PeriodNumber +
Time'QuarterNumber +
Time'Year
Definition:
Time DIMENSION Table
References:
ERD(DIMENSION) "WineWarehouse.erd" "Main"
SPEC(KEY) "TimeDimensionSpecs.spc" ""

Name: Time'Date
Composition:
Definition:
#GeneralDetails
Describe: This is the date of a transaction -- in the form of : DD-MON-YYYY.
##
#UserDetails
##
#SQLDetails
Physical: DATE
Qualify: notnull
DataType: DATE
##
References:
ATTRIBUTE

Name: Time'PeriodNumber
Composition:
Definition:
#GeneralDetails
Describe: This is year divided up into 12 periods, with a structure of x01, x02, x03, where the x is equal to the last digit of a year. For example Period 1 for the year 2002 , would be stated as 201, second period 202, etc.
##
#UserDetails
##
#SQLDetails
Physical: PERIODNUMBER
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Time'QuarterNumber
Composition:

Definition:
#GeneralDetails
Describe: A quarter number is defined as 3 periods. There are usually 4 quarter in a year. However, periods are not the same as months. Periods are made up of 5 weeks, which can span traditional months.
##
#UserDetails
##
#SQLDetails
Physical: QUARTERNUMBER
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Time'TimeID
Composition:
Definition:
#GeneralDetails
Describe: This the primary key for the Time dimension table. The key is made up of sequence of numbers. For example, the time ID is made up of period number, week number, day number, and year.
##
#UserDetails
##
#SQLDetails
Physical: TIMEID
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Time'Year
Composition:
Definition:
#GeneralDetails
Describe: This attribute displays the year as four digit number, i.e., 2000, 2001, 2002, etc.
##
#UserDetails
##
#SQLDetails
Physical: YEAR
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Wine
Composition:
*Wine'WineID +
Wine'Name +
Wine'Vintage +
Wine'ABV +
Wine'PricePerBottle +
Wine'PricePerCase
Definition:
Wine DIMENSION Table
References:
ERD(DIMENSION) "WineWarehouse.erd" "Main"

Name: Wine'ABV
Composition:
Definition:
#GeneralDetails
Describe: ABV is the alcohol by volume of a wine. It is usally stated in %, i.e. 11% abv 500ml.
##
#UserDetails
##
#SQLDetails
Physical: ABV
Qualify: notnull
DataType: VARCHAR2(25)
##
References:
ATTRIBUTE

Name: Wine'Name
Composition:
Definition:
#GeneralDetails
Describe: This attribute is used to track the name of a wine. Such as, Falanghina, Merlot's, Paragon, Maglieri, Cabernet Sauvignon, etc.
##
#UserDetails
##
#SQLDetails
Physical: NAME
Qualify: notnull
DataType: VARCHAR2(50)
##
References:
ATTRIBUTE

Name: Wine'PricePerBottle
Composition:
Definition:
#GeneralDetails
Describe: This attribute tracks the bottle price for a wine.
##
#UserDetails
##
#SQLDetails
Physical: PRICEPERBOTTLE
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Wine'PricePerCase
Composition:
Definition:
#GeneralDetails
Describe: This attribute stores the price per case for a particular wine.
##
#UserDetails
##
#SQLDetails
Physical: PRICEPERCASE
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE

Name: Wine'Vintage
Composition:
Definition:
#GeneralDetails
Describe: This is the attrabute vintage. It describes a wine, that is, the yield of wine or grapes from a particular vineyard or district during one season. This attribute identifies a wine by year, veyard, district, and/or origin.
##
#UserDetails
##
#SQLDetails
Physical: VINTAGE
Qualify: notnull
DataType: VARCHAR2(50)
##
References:
ATTRIBUTE

Name: Wine'WineID
Composition:
Definition:
#GeneralDetails
Describe: This attribute is the defined as the identification number for each type of wine that sold. For example, wine ID 001 is a Casa Silva, 002 is a Aglianico, 003 is a Coda di Volpe, etc.
##
#UserDetails
##
#SQLDetails
Physical: WINEID
Qualify: notnull
DataType: INTEGER
##
References:
ATTRIBUTE


Back | Home | Top | Feedback | Site Search


E-Mail Me

This site is brought to you by
Bob Betterton; 2001 - 2011.

This page was last updated on 10/12/2002
Copyright, RDB Prime Engineering



This Page has been accessed " 1655" times.