Database Design Logical Model Design Access DB Creation

Database Design: Logical Model Design & Access DB Creation University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management IS 257 – Fall 2004. 09. 20 - SLIDE 1

Lecture Outline • Review – Database Design, Conceptual Model – Object-Oriented Modeling • Logical Design for the Diveshop database • Access Database Creation IS 257 – Fall 2004. 09. 20 - SLIDE 2

Lecture Outline • Review – Database Design, Conceptual Model – Object-Oriented Modeling • Logical Design for the Diveshop database • Access Database Creation IS 257 – Fall 2004. 09. 20 - SLIDE 3

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 Internal Model Application 4 Conceptual requirements IS 257 – Fall 2004. 09. 20 - SLIDE 4

Object-Oriented Modeling • Becoming increasingly important as – Object-Oriented and Object-Relational DBMS continue to proliferate – Databases become more complex and have more complex relationships than are easily captured in ER or EER diagrams • (Most UML examples based on Mc. Fadden, “Modern Database Management”, 5 th edition) IS 257 – Fall 2004. 09. 20 - SLIDE 5

Dive. Shop ER Diagram Customer No Dive. Cust 1 Destination Name Destination no Customer No Ship. Via n Dest n 1 Dive. Ords n 1 Ship. Via 1 Destination no Site No 1 n Site No Species No Bio. Site 1 Destination n Sites Order No n 1 1/n Ship. Wrck Dive. Item n Order No Item No n Site No 1 Species No Bio. Life IS 257 – Fall 2004 1 Dive. Stok Item No 2004. 09. 20 - SLIDE 6

Entities • • • Customer Dive Order Line item Shipping information Dive Equipment Stock/Inventory • Dive Locations IS 257 – Fall 2004 • Dive Sites • Sea Life • Shipwrecks 2004. 09. 20 - SLIDE 7

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 IS 257 – Fall 2004. 09. 20 - SLIDE 8

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 – No history (only current or last order) IS 257 – Fall 2004. 09. 20 - SLIDE 9

Object-Oriented Modeling • Becoming increasingly important as – Object-Oriented and Object-Relational DBMS continue to proliferate – Databases become more complex and have more complex relationships than are easily captured in ER or EER diagrams • (Most UML examples based on Mc. Fadden, “Modern Database Management”, 5 th edition) IS 257 – Fall 2004. 09. 20 - SLIDE 10

Object Benefits • Encapsulate both data and behavior • Object-oriented modeling methods can be used for both database design and process design – Real-World applications have more than just the data in the database they also involve the processes, calculations, etc performed on that data to get real tasks done – OOM can be used for more challenging and complex problems IS 257 – Fall 2004. 09. 20 - SLIDE 11

Unified Modeling Language (UML) • Combined three competing methods • Can be used for graphically depicting – Software designs and interaction – Database – Processes IS 257 – Fall 2004. 09. 20 - SLIDE 12

CLASS • A class is a named description of a set of objects that share the same attributes, operations, relationships, and semantics. – An object is an instance of a class that encapsulates state and behavior. • These objects can represent real-world things or conceptual things. – An attribute is a named property of a class that describes a range of values that instances of that class might hold. – An operation is a named specification of a service that can be requested from any of a class's objects to affect behavior in some way or to return a value without affecting behavior IS 257 – Fall 2004. 09. 20 - SLIDE 13

UML Relationships • An relationship is a connection between or among model elements. • The UML defines four basic kinds of relationships: – Association – Dependency – Generalization – Realization IS 257 – Fall 2004. 09. 20 - SLIDE 14

UML Diagrams • The UML defines nine types of diagrams: – activity diagram – class diagram • Describes the data and some behavioral (operations) of a system – collaboration diagram – component diagram – deployment diagram – object diagram – sequence diagram – statechart diagram – use case diagram IS 257 – Fall 2004. 09. 20 - SLIDE 15

Class Diagrams • A class diagram is a diagram that shows a set of classes, interfaces, and/or collaborations and the relationships among these elements. IS 257 – Fall 2004. 09. 20 - SLIDE 16

UML Class Diagram DIVEORDS Order No Customer No Sale Date Shipvia Payment. Method CCNumber No of People Depart Date Return Date Destination Vacation Cost Calc. Total. Invoice() Calc. Equipment() IS 257 – Fall 2004 Class Name List of Attributes List of operations 2004. 09. 20 - SLIDE 17

Object Diagrams 307: DIVORDS Order No = 307 Customer No = 1480 Sale Date = 9/1/99 Ship Via = UPS Payment. Method = Visa CCNumber = 12345 678 90 CCExp. Date = 1/1/01 No of People = 2 Depart Date = 11/8/00 Return Date = 11/15/00 Destination = Fiji Vacation Cost = 10000 IS 257 – Fall 2004. 09. 20 - SLIDE 18

Differences from Entities in ER • Entities can be represented by Class diagrams • But Classes of objects also have additional operations associated with them IS 257 – Fall 2004. 09. 20 - SLIDE 19

Operations • Three basic types for database – Constructor – Query – Update IS 257 – Fall 2004. 09. 20 - SLIDE 20

Associations • An association is a relationship that describes a set of links between or among objects. • An association can have a name that describes the nature of this relationship. You can put a triangle next to this name to indicate the direction in which the name should be read. IS 257 – Fall 2004. 09. 20 - SLIDE 21

Associations • An association contains an ordered list of association ends. – An association with exactly two association ends is called a binary association – An association with more than two ends is called an n-ary association. IS 257 – Fall 2004. 09. 20 - SLIDE 22

Associations: Unary relationships * 0. . 1 Person 0. . 1 IS 257 – Fall 2004 Is-married-to manages Employee 0. . 1 manager 2004. 09. 20 - SLIDE 23

Associations: Binary Relationship Employee 0. . 1 Is-assigned Parking Place 0. . 1 One-to-one Product Line 1 contains * Product One-to-many Student * Registers-for * Course Many-to-many IS 257 – Fall 2004. 09. 20 - SLIDE 24

Associations: Ternary Relationships Part * Vendor IS 257 – Fall 2004 * Supplies * Warehouse 2004. 09. 20 - SLIDE 25

Association Classes Registers-for Student * Course * Computer Account Registration _________ acct. ID Term issues Password * 0. . 1 Grade Server. Space ________ Check. Eligibility() IS 257 – Fall 2004. 09. 20 - SLIDE 26

Derived Attributes, Associations, and Roles Course Student Course Offering ____________ Scheduled-for name Registers-for crse. Code term ssn * crse. Title * * 1 section date. Of. Birth credit. Hrs time Derived /age location attribute * * /participant Derived role {age = current. Date – date. Of. Birth} /Takes Derived association IS 257 – Fall 2004. 09. 20 - SLIDE 27

Generalization Employee ______ emp. Name emp. Number address date. Hired ______ print. Label() Hourly Employee ________ Hourly. Rate ________ compute. Wages() IS 257 – Fall 2004 Salaried Employee ________ Annual Sal stockoption ________ Contributepension() Consultant ________ contract. Number billing. Rate ________ compute. Fees() 2004. 09. 20 - SLIDE 28

Other Diagramming methods • SOM (Semantic Object Model) • Object Definition Language (ODL) – Not really diagramming • Access relationships display • Hybrids IS 257 – Fall 2004. 09. 20 - SLIDE 29

Application of SOM to Diveshop DIVECUST Name Address Street City State. Province ZIPPostal. Code Country Phone First. Contact 1. 1 1. 1 DIVEORDS IS 257 – Fall 2004 1. N 2004. 09. 20 - SLIDE 30

DIVEORDS Order. No Sale. Date DIVECUST id SHIPVIA DESTINATION DIVEITEM Payment. Method CCNumber CCExp. Date No. Of. People Depart. Date Return. Date Vacation. Cost IS 257 – Fall 2004. 09. 20 - SLIDE 31

Lecture Outline • Review – Database Design, Conceptual Model – Object-Oriented Modeling • Logical Design for the Diveshop database • Access Database Creation IS 257 – Fall 2004. 09. 20 - SLIDE 32

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 Internal Model Application 4 Conceptual requirements IS 257 – Fall 2004. 09. 20 - SLIDE 33

Dive. Shop ER Diagram Customer No Dive. Cust 1 Destination Name Destination no Customer No Ship. Via n Dest n 1 Dive. Ords n 1 Ship. Via 1 Destination no Site No 1 n Site No Species No Bio. Site 1 Destination n Sites Order No n 1 1/n Ship. Wrck Dive. Item n Order No Item No n Site No 1 Species No Bio. Life IS 257 – Fall 2004 1 Dive. Stok Item No 2004. 09. 20 - SLIDE 34

Logical Design: Mapping to a Relational Model • Each entity in the ER Diagram becomes a relation. • A properly normalized ER diagram will indicate where intersection relations for many-to-many mappings are needed. • Relationships are indicated by common columns (or domains) in tables that are related. • We will examine the tables for the Diveshop derived from the ER diagram IS 257 – Fall 2004. 09. 20 - SLIDE 35

Customer = DIVECUST IS 257 – Fall 2004. 09. 20 - SLIDE 36

Dive Order = DIVEORDS IS 257 – Fall 2004. 09. 20 - SLIDE 37

Line item = DIVEITEM IS 257 – Fall 2004. 09. 20 - SLIDE 38

Shipping information = SHIPVIA IS 257 – Fall 2004. 09. 20 - SLIDE 39

Dive Equipment Stock= DIVESTOK IS 257 – Fall 2004. 09. 20 - SLIDE 40

Dive Locations = DEST IS 257 – Fall 2004. 09. 20 - SLIDE 41

Dive Sites = SITE IS 257 – Fall 2004. 09. 20 - SLIDE 42

Sea Life = BIOLIFE IS 257 – Fall 2004. 09. 20 - SLIDE 43

BIOSITE -- linking relation IS 257 – Fall 2004. 09. 20 - SLIDE 44

Shipwrecks = SHIPWRK IS 257 – Fall 2004. 09. 20 - SLIDE 45

Mapping to Other Models • Hierarchical – Need to make decisions about access paths • Network – Need to pre-specify all of the links and sets • Object-Oriented – What are the objects, datatypes, their methods and the access points for them • Object-Relational – Same as relational, but what new datatypes might be needed or useful (more on OR later) IS 257 – Fall 2004. 09. 20 - SLIDE 46

Advantages of RDBMS • Possible to design complex data storage and retrieval systems with ease (and without conventional programming). • Support for ACID transactions – Atomic – Consistent – Independent – Durable IS 257 – Fall 2004. 09. 20 - SLIDE 47

Advantages of RDBMS • Support for very large databases • Automatic optimization of searching (when possible) • RDBMS have a simple view of the database that conforms to much of the data used in businesses. • Standard query language (SQL) IS 257 – Fall 2004. 09. 20 - SLIDE 48

Disadvantages of RDBMS • Until recently, no real support for complex objects such as documents, video, images, spatial or time-series data. (ORDBMS add support for these). • Often poor support for storage of complex objects from OOP languages (Disassembling the car to park it in the garage) • Still no efficient and effective integrated support for things like text searching within fields. IS 257 – Fall 2004. 09. 20 - SLIDE 49

Lecture Outline • Review – Database Design -- Object-Oriented Modeling • Logical Design for the Diveshop database • Normalization • Access Database Creation IS 257 – Fall 2004. 09. 20 - SLIDE 50

Database Creation in Access • Simplest to use a design view – wizards are available, but less flexible • Need to watch the default values • Helps to know what the primary key is, or if one is to be created automatically – Automatic creation is more complex in other RDBMS and ORDBMS • Need to make decision about the physical storage of the data IS 257 – Fall 2004. 09. 20 - SLIDE 51