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