Pizza delivery database

This page describes the contents of the example database. To see how the values are represented, look at the content listings of the tables.

Object

The identifiers are unique among product types, products, materials and categories. When a new material, product, product type or category is introduced its identifier is registered in table Object.

create table Object (
   ObjectId              varchar(8) not null,
   DateRegistered        date,
   constraint pk_object primary key (ObjectId)
);

View contents of table Object

ObjectName

Objectname provides names for objects (product types, products, materials and categories) in many languages. Attribute Language specifies the language (currently 'english', 'finnish', 'italian'). Attribute ObjectName contains the name of the object in that language.

create table ObjectName (
   ObjectId              varchar(8) not null,
   Language              varchar(20) not null,     
   ObjectName            varchar(40) not null,
   constraint pk_objectname primary key (ObjectId, Language),
   constraint fk_objectname_object foreign key
       (ObjectId) references Object
);

View contents of table ObjectName

Material

Table material contains the materials used in products or available as extras. Materials that have a non-null value as attribute AddOnPrice may be ordered as extras.

create table Material (
   MaterialId            varchar(8) not null,
   AddOnPrice            decimal(5,2),
   constraint pk_material primary key (MaterialId),
   constraint fk_material_object foreign key
      (MaterialId) references Object
);

View contents of table Material

Category

Table Category defines the order in which the item groups are to be represented in a standard price list. Item groups should be listed in ascending order by ListingSeqNo.

create table Category (
   CategoryId            varchar(8) not null,
   ListingSeqNo          smallint,
   constraint pk_category primary key (CategoryId),
   constraint fk_category_object foreign key
      (CategoryId) references Object
);

View contents of table Category

ProductType

Table ProductType contains the types of products available. Attribute Description describes the product. Attribute Picture contains an URL of a picture of the product. Each product belongs to exactly one category. ProductCategory contains the object identifier of the category.

create table ProductType (
   ProductId             varchar(8) not null,
   Description           varchar(1000),
   Picture               varchar(120),
   ProductCategory  varchar(8) not null,
   constraint pk_producttype primary key (ProductId),
   constraint fk_producttype_object foreign key
      (ProductId) references Object
);

View contents of table ProductType

Product

Products of one type may be sold in many forms (different size of portion usually). These forms are defined in table Product. Each form shares the productID but has a different model identifiers (ModelId). This has values like 'BIG', 'SMALL', etc. The price of a product depends on the form. Prices are in euros.

create table Product (
   ProductId             varchar(8) not null,
   ModelId               varchar(20),
   Price                   decimal(5,2),
   constraint pk_product primary key (ProductId,ModelId),
   constraint fk_product_type foreign key
      (ProductId) references ProductType
);

View contents of table Product

Contents

Table Contents describes what materials comprise the products.

create table Contents (
   ProductId        varchar(8) not null,
   MaterialId       varchar(8) not null,
   constraint pk_contents primary key (ProductId,MaterialId),
   constraint fk_contents_producttype foreign key
      (ProductId) references ProductType,
   constraint fk_contents_material foreign key
      (MaterialId) references Material
);

View contents of table Contents

Customer

Table Customer contains customer data. Customers have numeric identifiers. PaymentCategory defines whether the customer may pay with credit (value credit) or whether only cash is accepted (value cash). Areacode indicates the area within the city. There are codes like A01, A02, etc. Phone numbers and email-addresses are text fields. A customer must have at least one of them.

create table Customer (
   CustomerId             integer not null,
   Name                   varchar(60) not null,
   Address                varchar(80) not null, 
   PaymentCategory        varchar(20),
   AreaCode               varchar(20) not null,
   Phone                  varchar(20),
   Email                  varchar(60)
   constraint pk_customer primary key (CustomerId)
);

View contents of table Customer

Ordered

Table Ordered contains information about the Orders. The name of this table is not very good, but Order is a reserved word in SQL, and cannot be used. Orders have numeric identifiers. The time the order was made is registered in column Whenmade. This is a timestamp value, althoug in Oracle it must be defined as a date. Oracle date contains both date and time. Column Customer contains the identifier of the customer that made the order. Attribute WayIssused defines the media used in placing the order (phone, fax,email,...). PaymentBy defines how the order is to be paid or has been paid. TotalPrice determines the price of the order in euros. The contents of the table should be considered as a small sample of all the data. Currently the timestams refer to future times.

create table Ordered (
   OrderId                integer not null,
   WhenMade               date not null,
   Customer               integer not null, 
   WayIssued              varchar(20),
   PaymentBy              varchar(20) not null,
   TotalPrice             decimal(6,2) not null,
   constraint pk_order primary key (OrderId),
   constraint fk_ordercustomer foreign key (Customer) references Customer
);

View contents of table Ordered

ItemOrdered

Table ItemOrdered defines what has been ordered. Each item has a unique identifier (itemno) within its order. The attributes ProductId and ModelId identify the form of product ordered, and Amount specifies the amount as entire units.

create table ItemOrdered (
   OrderId                integer not null,
   ItemNo                 smallint not null,
   ProductId              varchar(8) not null, 
   ModelId                varchar(20) not null,
   Amount                 smallint not null,
   constraint pk_ItemOrdered primary key (OrderId,ItemNo),
   constraint fk_IO_Product foreign key
    (ProductId,ModelId) references Product,
   constraint fk_item_order foreign key
     (OrderId) references Ordered
);

View contents of table ItemOrdered

AddOn

Table AddOn defines the extras ordered. Extras are attached to the items. Thus, for example, carlic may be appended on a pizza not in Coca-Cola. MaterialId contains the identifier of the extra material. Amount contains its amount as portion units.

create table AddOn (
   OrderId                integer not null,
   ItemNo                 smallint not null,
   MaterialId             varchar(8) not null, 
   Amount                 smallint not null,
   constraint pk_AddOn primary key (OrderId,ItemNo,MaterialId),
   constraint fk_AddOn_Item foreign key
    (OrderId,ItemNo) references ItemOrdered,
   constraint fk_AddOn_Material foreign key
    (MaterialId) references Material
);

View contents of table AddOn

DeliveryBoy

DeliveryBoy list the boys and girls taking care of the deliveries. They have textual identifiers composed of their names. The DeliveryArea indicates the principal Delivery area of the boy. He/she may deliver to other areas too. All delivery boys do not have principal delivery areas in which case the field is empty.

create table DeliveryBoy (
   BoyId                   varchar(20) not null,
   Name                    varchar (60) not null,
   DeliveryArea            varchar (20),
   constraint pk_deliveryBoy primary key (BoyId)
);

View contents of table DeliveryBoy

Delivery

Delivery describes the deliveries. A delivery row is inserted into the table when the delivery is assigned to a delivery boy. The timestamp whenAssigned has always a non-null value, but the timestamp WhenDelivered may be null. It will be assigned a value after the delivery has been made. Column DeliveredBy contains the identifier of the delivery boy.

create table Delivery (
   OrderId                 integer not null,
   WhenAssigned            date not null,
   DeliveredBy             varchar(20) not null,
   WhenDelivered           date,
   constraint pk_delivery primary key (OrderId),
   constraint fk_Delivery_Boy foreign key
    (DeliveredBy) references DeliveryBoy,
   constraint fk_delivery_order foreign key
    (OrderId) references Ordered
);

View contents of table Delivery

The other tables of the database are currently empty and thus not explained here.