mail order company database l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Mail-Order Company Database PowerPoint Presentation
Download Presentation
Mail-Order Company Database

Loading in 2 Seconds...

play fullscreen
1 / 24

Mail-Order Company Database - PowerPoint PPT Presentation


  • 673 Views
  • Uploaded on

Mail-Order Company Database A small mail-order company must maintain the following information: The company must keep track of all its customers with their names, addresses, and the dates of their first orders. A unique customer number is assigned to each customer.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

Mail-Order Company Database


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
mail order company database
Mail-Order Company Database

A small mail-order company must maintain the following

information:

  • The company must keep track of all its customers with their names, addresses, and the dates of their first orders. A unique customer number is assigned to each customer.
  • Each order placed by a customer may contain multiple order-lines. Each order-line is used to order one kind of product for some quantity. The date of the order must be recorded. Each customer assigns order numbers to her orders. Orders from different customers may have identical order numbers.
  • Each product has a product number, a product name, and a unit retail price.
mail order company database cont d
Mail-Order Company Database (cont'd)
  • Each product may have multiple suppliers. A unique supplier number is assigned to each supplier. Different suppliers may offer the same product at different wholesale prices. A supplier may supply multiple products. The names and addresses of the suppliers must be recorded.
  • The company owns multiple warehouses. Each warehouse is identified by the name of the city where it is located. The telephone number of each warehouse must be recorded. The number of each product stocked at each warehouse must be recorded. A warehouse can stock different products. However, each product is stocked at most at one warehouse.
mail order company database e r diagram
Mail-Order Company Database: E-R Diagram

address

C#

Customers

name

since

1

name

place

S#

city

telephone #

address

M

Suppliers

O#

Warehouses

Orders

date

M

1

M

quantity

quantity

for

store

supply

M

M

M

wholeSalePrice

Products

name

P#

retailPrice

weak entities
Weak Entities
  • The keys of weak entities are uniquie only among their respective parents. Such keys are referred to as weak keys.
  • In referring to a weak entity, we must use the combination of the weak key of the weak entity and the primary key of the parent entity.
  • If the order numbers are assigned by customers, the order numbers are weak keys, and hence each order must be identified with (C#, O#).
  • Weak entities can exist only when their parent entities exist.
generating relational tables
Generating Relational Tables
  • Provide a table for each entity type. The attributes of the entity type become the table columns.
  • Provide a table for each M : M relationship type. The primary keys of the entity types associated by the relationship type become the foreign keys in the table. The combination of those foreign keys becomes the primary key of the table. Also, the attributes of the relationship type need be added to the table.
  • A M:1 or 1:M relationship type does not require a new table. The primary key of the entity type on the 1-side can be added as a foreign key to the table representing the entity type on the M-side.
mail order company database relational schema
Mail-Order Company Database: Relational Schema

(1) Customers(C#, name, address, since)

(2) Place(C#, O#)

(3) Orders(C#, O#, Date)

(4) For(C#, O#, P#, quantity)

(5) Products(P#, name, retailPrice)

(6) Suppliers(S#, name, address)

(7) Supply(S#, P#, wholeSalePrice)

(8) Warehouses(city, telephone#)

(9) StoredAt(P#, city, quantity)

Relation 2 is subsumed by relation 3.

Relations 5 and 9 can be merged:

Products(P#, name, retailPrice, city, quantity)

converting a m m relationship types into two 1 m relationship types
Converting a M:M Relationship Types intoTwo 1:M Relationship Types.

O#

Orders

O#

date

Orders

1

date

M

quantity

M

quantity

for

Orderline

M

M

Products

1

Products

name

P#

retailPrice

name

P#

retailPrice

m m relationships as entities
M:M Relationships as Entities
  • An edge representing a relationship is an entity.
  • Each relationship is connected to exactly one participating entity on each side.
  • Each participating entity is connected to multiple relationships.
  • Therefore, we have a 1:M relationship type on one side and an M:1 relationship type on the other side.
bug reports tracking system e r diagram
Bug-Reports Tracking System: E-R Diagram

M

Customers

reported-to

1

Packages

make

1

M

M

have

Workarounds

Reports

received by

M

M

1

M

M

Versions

Employees

have

M

for

for

1

1

1

M

M

M

perform

have

Problems

M

M

M

M

for

verified by

Changes

M

M

M

made by

Verifications

entity type hierarchy
(Entity) Type Hierarchy
  • Is a forest of (entity) types
  • Indicated a supertype-subtype relationship
  • Is also called an IS-A hierarchy (or relationship)

Example:

  • A car is a vehicle
  • A truck is a vehicle
  • A dump truck is a truck
  • A trailer-truck is a truck
entity type hierarchy extended er schema
(Entity) Type Hierarchy: Extended ER Schema

license#

owner

Vehicle

color

nPassengers

Car

Truck

weight

style

loadWeight

DumpTruck

TrailerTruck

nTrailers

entity type hierarchy vehicle database
(Entity) Type Hierarchy: Vehicle Database
  • For each vehicle, record its license number, owner, and color.
  • For each car, record the number of passengers and style (sedan, convertible, etc.).
  • For each truck, record the weight of the truck itself.
  • For each dump truck, record the maximum weight of the load.
  • For each trailer-truck, record the number of the trailers (1 - 3).
relational schema for type hierarchy i
Relational Schema for Type Hierarchy I
  • Store all the information on one entity in one table.
  • A similar method is used by object oriented programming languages such as C++ and Java.

Vehicle(license#, owner, color)

Car(license#, owner, color, nPassengers, style)

Truck(license#, owner, color, weight)

DumpTruck(license#, owner, color, weight, loadWeight)

TrailerTruck(license#, owner, color, weight, nTrailers)

relational schema for type hierarchy i15
Relational Schema for Type Hierarchy I

Vehicle(license#, owner, color)

Car(license#, owner, color, nPassengers, style)

Truck(license#, owner, color, weight)

DumpTruck(license#, owner, color, weight, loadWeight)

TrailerTruck(license#, owner, color, weight, nTrailers)

Get all information on all trailertrucks.

select * from TrailerTruck;

relational schema for type hierarchy i16
Relational Schema for Type Hierarchy I

Vehicle(license#, owner, color)

Car(license#, owner, color, nPassengers, style)

Truck(license#, owner, color, weight)

DumpTruck(license#, owner, color, weight, loadWeight)

TrailerTruck(license#, owner, color, weight, nTrailers)

Get license# and owner of all vehicles.

  • select licence#, owner, color from Vehicle
  • union
  • select licence#, owner, color from Car
  • union
  • select licence#, owner, color from Truck
  • union
  • select licence#, owner, color from DumpTruck
  • union
  • select licence#, owner, color from TrailerTruck;
relational schema for type hierarchy ii
Relational Schema for Type Hierarchy II

Vehicle(license#, owner, color)

Car(license#, nPassengers, style)

Truck(license#, weight)

DumpTruck(license#, loadWeight)

TrailerTruck(license#, nTrailers)

  • The information on one entity is stored in multiple tables
  • When a class hierarchy is relatively flat, query statements in SQL become simpler
sql queries for type ii class hierarchy
SQL Queries for Type II Class Hierarchy

Vehicle(license#, owner, color)

Car(license#, nPassengers, style)

Truck(license#, weight)

DumpTruck(license#, loadWeight)

TrailerTruck(license#, nTrailers)

Get all information on all trailertrucks.

  • select v.licence#, owner, color, weight, nTrailers
  • from Vehicle v, Truck t, TrailerTruck tt

where v.license# = t.licemse#

and t.license# = tt.license#

sql queries for type ii class hierarchy19
SQL Queries for Type II Class Hierarchy

Vehicle(license#, owner, color)

Car(license#, nPassengers, style)

Truck(license#, weight)

DumpTruck(license#, loadWeight)

TrailerTruck(license#, nTrailers)

Get license# and owner of all vehicles.

select licens#, owner from Vehicle;

inheritance support by postgresql
Inheritance support by PostgreSQL

create table vehicle (

license# char(8) primary key,

owner char(20),

color char(10),

);

create table truck (

weight float,

) inherits (vehicle);

create table dump_truck (

load_weight float,

) inherits (truck);

field service support system e r diagram
Field Service Support System: E-R Diagram

1

1

1

District

has

Depot

Supplier

1

1

M

has

makes

has

M

stores

M

M

Customer

Shipment

1

Employee

1

M

M

has

1

makes

for

M

gets

Technician

M

Computer

M

M

1

Report

Part

1

has

M

1

fixes

for

M

M

M

M

Problem

in

CPU

Memory

Disk

ternary relationship type
Ternary Relationship Type

PJ#

(key)

Name

Project

P#

(key)

S#

(key)

supply

Supplier

Part

Name

City

Price

Name

Weight

is this equivalent to the one ternary relationship type
Is This Equivalent to the One Ternary Relationship Type?

supply

Supplier

Part

participate

use

Project

We cannot know which supplier supplied which part with respect to which project.