Database Design Conceptual Model and ER Diagramming University

Database Design: Conceptual Model and ER Diagramming University of California, Berkeley School of Information Management and Systems SIMS 202: Information Organization and Retrieval 9/7/1999 Information Organization and Retrieval

Review • Database Design Process • Basics of ER Diagrams 9/7/1999 Information Organization and Retrieval

Database Design Process Application 1 External Model Application 2 Application 3 Application 4 External Model Application 1 Conceptual requirements Application 2 Conceptual requirements Application 3 Conceptual requirements Conceptual Model Logical Model Application 4 Conceptual requirements 9/7/1999 Information Organization and Retrieval Internal Model

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) Employee 9/7/1999 Information Organization and Retrieval

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. ) Birthdate First Middle Age Name Employee Last 9/7/1999 SSN Projects Information Organization and Retrieval

Relationships • Relationships are the associations between entities. They can involve one or more entities and belong to particular relationship types 9/7/1999 Information Organization and Retrieval

Relationships Student Attends Class Project Supplier 9/7/1999 Supplies project parts Information Organization and Retrieval Part

Types of Relationships • Concerned only with cardinality of relationship 1 Assigned Employee 9/7/1999 1 Truck Employee n Assigned 1 Project Employee m Assigned n Project Information Organization and Retrieval

More Complex Relationships Manager 1/1/1 Employee 1/n/n Evaluation n/n/1 SSN Employee Date 4(2 -10) Assigned Project 1 Manages Employee 9/7/1999 Project Is Managed By Information Organization and Retrieval Project 1 Manages n

Weak Entities • Owe existence entirely to another entity Part# Invoice # Order Invoice# Contains Rep# 9/7/1999 Information Organization and Retrieval Quantity Order-line

Supertype and Subtype Entities Employee Sales-rep Sold Is one of Other Invoice 9/7/1999 Information Organization and Retrieval Manages Clerk

Many to Many Relationships SSN Proj# Hours Project Assignment Is Assigned Employee 9/7/1999 SSN Information Organization and Retrieval Project

Today • Building the Conceptual Model for the Diveshop database • Names for the Diveshop company? 9/7/1999 Information Organization and Retrieval

Developing a Conceptual Model • We will look at a small business -- a diveshop that offers diving adventure vacations • Assume that we have done interviews with the business and found out the following information about the forms used and types of information kept in files and used for business operations. . . 9/7/1999 Information Organization and Retrieval

Primary Business Operations • The shop takes orders from customers for dive vacations. • It ships information about the dive vacation to the customers. • It rents diving equipment for the divers going on the trips (these may include additional people other than the customer) • It bills the customer for the vacation and for equipment rental or sales. 9/7/1999 Information Organization and Retrieval

Business Operations (cont. ) • It arranges sub-trips to particular dive sites at the primary location. • It provides information about the features of various sites to help customers choose their destinations. – Features include sea life found at the location and shipwrecks 9/7/1999 Information Organization and Retrieval

Business Operations (cont. ) • Each dive order (or sale or trip) is on an invoice to one customer. – Invoices contain: • Line items for each type of equipment ordered, • Total amount due for the invoice, • Customer information: – Name, address, phone, credit card info. • Information must be kept on inventory of dive equipment. • There are multiple types of dive equipment: – The prices charged for sale or rental are maintained. 9/7/1999 Information Organization and Retrieval

Business Operations (cont. ) • Destination information includes: – Name of the destination – information about the location (accomodations, night life, travel cost, average temperatures for different times of the year • Destinations have associated dive sites. • Dive Sites have associated features – Sea life – Shipwrecks 9/7/1999 Information Organization and Retrieval

Business Operations (cont. ) • One record is kept for each order by a customer and will include the method of payment, total price, and location information. (I. e. Customers may have multiple orders) • The company needs to know how an order is to be shipped. • The shop has to keep track of what equipment is on-hand when replacements or additional equipment is needed 9/7/1999 Information Organization and Retrieval

Entities • • • Customer Dive Order Line item Shipping information Dive Equipment/ Stock/Inventory • Dive Locations 9/7/1999 • Dive Sites • Sea Life • Shipwrecks Information Organization and Retrieval

Diveshop ER Entities: Dive. Cust City State/Prov ZIP/Postal Code Country Street Name Dive. Cust First Contact Customer no 9/7/1999 Phone Information Organization and Retrieval

Diveshop ER Entities: Dive. Ords Ship Via Sale Date Total Invoice Customer No Payment Method Dive. Ords Order no Vacation Cost 9/7/1999 CCNumber Destination Return Date No of People Depart Date Information Organization and Retrieval CCExp. Date

Diveshop ER Entities: Dive. Item Qty Rental/ Sale Item no Line Note Dive. Item Order no 9/7/1999 Information Organization and Retrieval

Diveshop ER Entities: Ship. Via Ship Cost Ship. Via 9/7/1999 Information Organization and Retrieval

Diveshop ER Entities: Dive. Stok On Hand Reorder Point Equipment Class Description Sale Price Dive. Stok Item No 9/7/1999 Cost Information Organization and Retrieval Rental Price

Diveshop ER Entities: Dest Avg Temp (C) Spring Temp (F) Avg Temp (F) Summer Temp (F) Destination name Fall Temp (C) Dest Fall Temp (F) Destination no 9/7/1999 Summer Temp (C) Accommodations Travel Cost Body of Water Winter Temp (C) Temp (F) Night Life Information Organization and Retrieval

Diveshop ER Entities: Sites Site Highlight Site Notes Distance From Town (M) Distance From Town (Km) Site Name Destination no Depth (ft) Sites Site no Depth (m) Visibility(ft) Skill Level 9/7/1999 Visibility (m) Information Organization and Retrieval

Diveshop ER Entities: Biolife Species Name Length (cm) Common Name Category Notes Bio. Life Species no 9/7/1999 Length (in) Information Organization and Retrieval Graphic

Diveshop ER Entities: Shipwrck Type Interest Tonnage Length (ft) Category Site no Length (m) Shipwrck Beam (ft) Ship Name Graphic Condition Survivors 9/7/1999 Passengers/ Crew Comments Cause Date Sunk Information Organization and Retrieval Beam (m)

Functional areas • • • Ordering Inventory Supplies Shipping Billing Location/Site Selection – We will concentrate on Ordering and Location/Site Selection (these are joined tasks) 9/7/1999 Information Organization and Retrieval

Ordering Customer (DIVECUST) Orders (DIVORDS) Customers place Orders Each Order needs Customer information 9/7/1999 Information Organization and Retrieval

Ordering Ship Via Ship. Via Customer No Dive. Cust ship Orders Dive. Ords Order No 9/7/1999 Information Organization and Retrieval Customer No Dive. Item Repeating attribute

Ordering Normalization Ship Via Customer No Dive. Cust Ship. Via Item No Ship Orders Dive. Ords Order No Contains Qty Dive. Item Order No Rental/sale Customer No 9/7/1999 Information Organization and Retrieval

Details of Dive. Item No Has Company# Supplier m Supplies We’re ignoring this part. . . n Dive. Stok Item No On Hand Sale Price 9/7/1999 Order No Information Organization and Retrieval

Ordering: Full ER Customer No Destination Name Destination no Dest Dive. Cust 1 Customer No 1 n Ship. Via n Dive. Ords n 1 Ship. Via 1 Destination Order No n Dive. Item n Order No Item No 1 Dive. Stok 9/7/1999 Information Organization and Retrieval Item No Ship. Via

Location/Site Selection Destination Dive. Ords 9/7/1999 No Going to? Information Organization and Retrieval Destination Name Dest

Destination/ Sites Destination Name Customer No Destination no 1 n Dive. Ords Destination no Site No 1 n Destination Sites 9/7/1999 Information Organization and Retrieval Order No

Sites and Sea Life 1 Site No Destination no Sites Bio. Life 9/7/1999 Information Organization and Retrieval Multiple occurrences of sea life. . .

Diveshop ER diagram: Bio. Site Species No Site No Bio. Site 9/7/1999 Information Organization and Retrieval

Sites and Sea Life 2 Destination no Site No 1 Site No Species No Sites n Bio. Site n 1 Species No 9/7/1999 Bio. Life Information Organization and Retrieval

Sites and Shipwrecks Site No Destination no Sites 1 1/n Ship. Wrck Site No 9/7/1999 Information Organization and Retrieval

Dive. Shop ER Diagram Customer No Dive. Cust 1 Destination Name Destination no Customer No 1 n Destination no Site No 1 Site No Species No n Bio. Site Ship. Via n Dive. Ords Destination n Order No n 1 1/n 9/7/1999 Dive. Item Ship. Wrck n Ship. Via Site No 1 Order No Item No n 1 Species No 1 1 1 Sites n Dive. Stok Bio. Life Information Organization and Retrieval Item No Ship. Via

What must be calculated? • Total price for equipment rental? • Total price for equipment sale? • Total price of an order? – Vacation price – Equipment (rental or sale) – Shipping 9/7/1999 Information Organization and Retrieval

What is Missing? ? • Not really an “enterprise-wide” database – No personnel • • Sales people Dive masters Boat captains and crew payroll – Local arrangements • Dive Boats • Hotels – Suppliers/Wholesalers for dive equipment • Orders for new/replacement equipment 9/7/1999 Information Organization and Retrieval