|
|
|
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
- Data Models
1. Main
- Data Dictionary
Document: WineWarehouse.erd
Data Model
Main
Related Diagrams:
Related Dictionary Entries:
Area
Customer
Sales
Time
Wine
Return To Contents
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
|