Intergalactic Courier Service: Database and Application
Design
University of California, Berkeley
School of Information Management and Systems
SIMS 202 & 206
Database Design Process
Requirements analysis
Functional requirements
What services/activities does the database and system need to support?
Performance requirements
How fast must the system be able to fulfill its functions?
What volume of transactions must be handled?
Reliability requirements
What are the requirements for accuracy?
What is acceptable down-time?
What maintenance is required?
Functional Requirements
Support routing of packages (Basic function)
Support tracking of packages en route
Query location of package by tracking number (GPS?)
Query location of packages by shipper or addressee
Confirm delivery/receipt (signatures)
Support multiple priorities and shipping times
Support destination changes en route
Support arrival time estimates
Support rescheduling of missed deliveries
Functional Requirements
Support on-the-fly dispatching of closest trucks for
high-priority pickups
Reports (accessible online by management)
Per truck daily delivery numbers
Volume and destinations on a city by city basis
Delivery failure statistics
because no addressee available
because of shipping delays
Others?
Performance Requirements
Must support 2 major routing centers (Los Angeles and
Chicago).
Must support 20 local routing centers scattered across
the
country.
Must support 200 trucks handling local pickup and
deliveries.
Performance Requirements
System must be able to support 20,000 packages per day
currently and more as business grows.
Must be able to query current location of a package
and
get a response in under 5 seconds.
Must be able to query for arrival time and get estimate
within 20 seconds.
Performance Requirements
System must be available continuously during business
hours (East and West coast), and preferably
7x24.
Scheduled system maintenance should be able to be
performed only during non-business hours.
Performance Requirements
Backup systems in case of primary system failure.
Data backup at multiple locations.
Recovery from severe failures (e.g. disk crashes) within
2
hours. (emergency maintenance)
Recovery from catastrophic failures (e.g. flood, fire)
within
5 hours at secondary location
Database Design Process
Conceptual Model
Merge the collective needs of all applications
Determine what Entities are being used
Some object about which information is to maintained
What are the Attributes of those entities?
Properties or characteristics of the entity
What attributes uniquely identify the entity
What are the Relationships between entities
How the entities interact with each other?
Developing a Conceptual Model
Overall view of the database that integrates all the needed
information discovered during the requirements
analysis.
Elements of the Conceptual Model are represented by
diagrams, Entity-Relationship or ER Diagrams,
that show
the meanings and relationships of those
elements
independent of any particular database systems
or
implementation details.
Entity
An Entity is an object in the real world (or even imaginary
worlds) about which we want or need to maintain
information
Persons (e.g.: customers in a business, employees, authors)
Things (e.g.: purchase orders, meetings, parts, companies)
Attributes
Attributes are the significant properties or characteristics
of an entity that help identify it and provide
the
information needed to interact with it or
use it. (This is the
Metadata for the entities.)
Relationships
Relationships are the associations between entities. They
can involve one or more entities and belong
to particular
relationship types
Examples derived from David R. McClanahan
Conceptual Design DBMS Magazine January
1992, and
Hands-on Design, February 1992.
Relationships
Types of Relationships
Concerned only with cardinality of relationship
More Complex Relationships