Database Concepts - Data Warehouse Systems: Design and Implementation - page 13

to a particular DBMS platform. Common DBMSs include SQL Server,
Oracle, DB2, MySQL, and PostgreSQL, among others.
A major objective of this four-level process is to provide data indepen-
dence , that is, to ensure as much as possible that schemas in upper levels
are unaffected by changes to schemas in lower levels. Two kinds of data
independence are typically defined. Logical data independence refers to
the immunity of the conceptual schema to changes in the logical schema.
For example, rearranging the structure of relational tables should not affect
the conceptual schema, provided that the requirements of the application
remain the same. Physical data independence refers to the immunity of
the logical schema to changes in the physical one. For example, physically
sorting the records of a file on a disk does not affect the conceptual or logical
schema, although this modification may be perceived by the user through a
change in response time.
In the following sections, we briefly describe the entity-relationship model
and the relational models to cover the most widely used conceptual and logical
models, respectively. We then address physical design considerations. Before
doing this, we introduce the use case we will use throughout the topic, which
is based on the popular Northwind relational database. 1 In this chapter, we
explain the database design concepts using this example. In the next chapter,
we will use a data warehouse derived from this database, over which we will
explain the data warehousing and OLAP concepts.
2.2 The Northwind Case Study
The Northwind company exports a number of goods. In order to manage and
store the company data, a relational database must be designed. The main
characteristics of the data to be stored are the following:
￿ Customer data, which must include an identifier, the customer's name,
contact person's name and title, full address, phone, and fax.
￿ Employee data, including the identifier, name, title, title of courtesy, birth
date, hire date, address, home phone, phone extension, and a photo.
Photos will be stored in the file system, and a path to the photo is
required. Further, employees report to other employees of higher level in
the company's organization.
￿ Geographic data, namely, the territories where the company operates.
These territories are organized into regions. For the moment, only the
1 This database can be downloaded from http://northwinddatabase.codeplex.com/ .
Notice that in this topic, we do not consider the tables CustomerDemographics and
CustomerCustomerDemo , which are empty, and in addition, we removed the space in
the name of the table Order Details .