Database Reference
In-Depth Information
Product
Supplier
City
ProductKey
ProductName
QuantityPerUnit
UnitPrice
Discontinued
CategoryKey
SupplierKey
CompanyName
Address
PostalCode
CityKey
CityKey
CityName
StateKey (0,1)
CountryKey (0,1)
Customer
CustomerKey
CustomerID
CompanyName
Address
PostalCode
CityKey
State
Sales
StateKey
StateName
EnglishStateName
StateType
StateCode
StateCapital
RegionName (0,1)
RegionCode (0,1)
CountryKey
AK: CustomerID
Category
CustomerKey
EmployeeKey
OrderDateKey
DueDateKey
ShippedDateKey
ShipperKey
ProductKey
SupplierKey
OrderNo
OrderLineNo
UnitPrice
Quantity
Discount
SalesAmount
Freight
CategoryKey
CategoryName
Description
Territories
EmployeeKey
CityKey
Time
Country
Employee
TimeKey
Date
DayNbWeek
DayNameWeek
DayNbMonth
DayNbYear
WeekNbYear
MonthNumber
MonthName
Quarter
Semester
Year
CountryKey
CountryName
CountryCode
CountryCapital
Population
Subdivision
ContinentKey
EmployeeKey
FirstName
LastName
Title
BirthDate
HireDate
Address
City
Region
PostalCode
Country
SupervisorKey
AK: (OrderNo,
OrderLineNo)
Shipper
Continent
ShipperKey
CompanyName
ContinentKey
ContinentName
AK: Date
Fig. 10.11
Relational schema of the Northwind data warehouse (repeated from
Fig.
5.4
)
levels, as explained in Chap.
5
. For the levels
State
and
Region
, we adopted
the first alternative, where the attributes
RegionName
and
RegionCode
have
been embedded in the
State
table as optional attributes. For the other levels,
we have chosen the snowflaked solution. For example, in the
City
table, we
have embedded
StateKey
and
CountryKey
as optional foreign keys. Then, if a
city does directly belong to a country, we can reference the country without
traversing the intermediate levels.
Territories
is a nonstrict hierarchy (Sect.
4.2.6
) since it contains a many-to-
many relationship between the
Employee
and
City
levels. In order to represent
this relationship in the relational model, we must use a bridge table. For that
purpose, we create the table
Territories
, which references both the
Employee
and the
City
tables.
Search WWH ::
Custom Search