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

territory and region description must be kept. An employee can be assigned
to several territories, but these territories are not exclusive to an employee:
Each employee can be linked to multiple territories, and each territory can
be linked to multiple employees.
￿ Shipper data, that is, information about the companies that Northwind
hires to provide delivery services. For each one of them, the company name
and phone number must be kept.
￿ Supplier data, including the company name, contact name and title, full
address, phone, fax, and home page.
￿ Data about the products that Northwind trades, such as identifier, name,
quantity per unit, unit price, and an indication if the product has been
discontinued. In addition, an inventory is maintained, which requires to
know the number of units in stock, the units ordered (i.e., in stock but not
yet delivered), and the reorder level (i.e., the number of units in stock
such that when it is reached, the company must produce or acquire).
Products are further classified into categories, each of which has a name,
a description, and a picture. Each product has a unique supplier.
￿ Data about the sale orders. The information required includes the
identifier, the date at which the order was submitted, the required delivery
date, the actual delivery date, the employee involved in the sale, the
customer, the shipper in charge of its delivery, the freight cost, and the full
destination address. An order can contain many products, and for each of
them, the unit price, the quantity, and the discount that may be given
must be kept.
2.3 Conceptual Database Design
The entity-relationship (ER) model is one of the most often used conceptual
models for designing database applications. Although there is general agree-
ment about the meaning of the various concepts of the ER model, a number of
different visual notations have been proposed for representing these concepts.
Appendix A shows the notations we use in this topic.
Figure 2.1 shows the ER model for the Northwind database. We next
introduce the main ER concepts using this figure.
Entity types are used to represent a set of real-world objects of interest
to an application. In Fig. 2.1 , Employees , Orders ,and Customers are examples
of entity types. An object belonging to an entity type is called an entity or
an instance . The set of instances of an entity type is called its population .
From the application point of view, all entities of an entity type have the
same characteristics.
In the real world, objects do not live in isolation; they are related to
other objects. Relationship types are used to represent these associations
between objects. In our example, Supplies , ReportsTo ,and HasCategory