1 / 50

Database Models and Introduction to Access

Database Models and Introduction to Access. University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management. Last Time. Database concepts and terminology Database Life cycle. Terms and Concepts. Database: Enterprise Entity Attributes

eavan
Download Presentation

Database Models and Introduction to Access

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Models and Introduction to Access University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management Database Management -- Fall 2000 -- R. Larson

  2. Last Time • Database concepts and terminology • Database Life cycle Database Management -- Fall 2000 -- R. Larson

  3. Terms and Concepts • Database: • Enterprise • Entity • Attributes • Data values • Records • File • Key • Primary Key Database Management -- Fall 2000 -- R. Larson

  4. Terms and Concepts • Data Independence • Models • (1) Levels or views of the Database • Conceptual, logical, physical • (2) DBMS types • Relational, Hierarchic, Network, Object-Oriented, Object-Relational • Metadata Database Management -- Fall 2000 -- R. Larson

  5. Application 1 Application 2 Application 3 Application 4 External Model External Model External Model External Model Application 1 Conceptual requirements Application 2 Conceptual requirements Conceptual Model Logical Model Internal Model Application 3 Conceptual requirements Application 4 Conceptual requirements Models (1) Database Management -- Fall 2000 -- R. Larson

  6. Terms and Concepts • Data Dictionary • Data Administration • Database Administration • Data Steward • DA • DBA Database Management -- Fall 2000 -- R. Larson

  7. Database System Life Cycle Physical Creation 2 Conversion 3 Design 1 Growth, Change, & Maintenance 6 Integration 4 Operations 5 Database Management -- Fall 2000 -- R. Larson

  8. Today • Models(2): DBMS types • Hierarchical • Network • Relational • Object-Oriented • Object-Relational Database Management -- Fall 2000 -- R. Larson

  9. Books (id, title) Authors (first, last) Publisher Subjects Database Data Models • Hierarchical Model • Similar to data structures in programming languages. Database Management -- Fall 2000 -- R. Larson

  10. Hierarchical Model • Data items are structured in a Parent-Child hierarchical relationship • Data items are grouped into “logical record types”. Each of these approximately corresponds to a table in the relational model. • FOCUS permits “virtual segments” that are stored as files. Database Management -- Fall 2000 -- R. Larson

  11. BIBINFO PUBINFO INDXINFO CALLINFO SUBINFO LIBINFO Hierarchical Model for Cookie Database Management -- Fall 2000 -- R. Larson

  12. FOCUS BIBFILE Definition FILENAME = BIBFILE, SUFFIX = FOC, $ SEGNAME = BIBINFO, SEGTYPE = S, $ FIELD = ACCESSION NO, ALIAS = ACCNO, USAGE = A4, $ FIELD = AUTHOR, ALIAS = AU, USAGE = A30, $ FIELD = TITLE, ALIAS = TI, USAGE = A40, $ FIELD = LOCATION, ALIAS = LOC, USAGE = A20, $ FIELD = PUBLISHERID, ALIAS = PUBID, USAGE = A2, $ FIELD = DATE, ALIAS = D, USAGE = A4, $ FIELD = PRICE, ALIAS = PR, USAGE = D6.2, $ FIELD = PAGINATION, ALIAS = PAGIN, USAGE = A13, $ FIELD = ILLUSTRATION, ALIAS = ILL, USAGE = A9, $ FIELD = HEIGHT, ALIAS = HT, USAGE = I2, $ SEGNAME = PUBINFO, PARENT = BIBINFO, SEGTYPE = KU, CRFILE = PUBFILE, CRKEY = PUBLISHERID, $ SEGNAME = INDXINFO, PARENT = BIBINFO, SEGTYPE = S, $ FIELD = SUBID, ALIAS = SID, USAGE = A2, $ SEGNAME = CALLINFO, PARENT = BIBINFO, SEGTYPE = S, $ FIELD = LIBRARYID, ALIAS = LIBID, USAGE = A2, $ FIELD = CALL NUMBER, ALIAS = CALLNO, USAGE = A15, $ FIELD = COPIES, ALIAS = C, USAGE = I4, $ SEGNAME = LIBINFO, PARENT = CALLINFO, SEGTYPE = KU, CRFILE = LIBFILE, CRKEY = LIBRARYID, $ SEGNAME = SUBINFO, PARENT = INDXINFO, SEGTYPE = KU, CRFILE = SUBFILE, CRKEY = SUBID, $ Database Management -- Fall 2000 -- R. Larson

  13. PUBFILE Segment FILENAME = PUBFILE, SUFFIX = FOC, $ SEGNAME = PUBINFO, SEGTYPE = S, $ FIELD = PUBLISHERID, ALIAS = PUBID, USAGE = A2, FIELDTYPE = I, $ FIELD = PUBLISHER, ALIAS = PNAME, USAGE = A30, $ FIELD = PUB ADDRESS, ALIAS = PADDRESS, USAGE = A20, $ FIELD = PUB CITY, ALIAS = PCITY, USAGE = A15, $ FIELD = PUB STATE, ALIAS = PSTATE, USAGE = A2, $ FIELD = PUB ZIP, ALIAS = PZIP, USAGE = A5, $ FIELD = PUB PHONE, ALIAS = PPHONE, USAGE = A10, $ FIELD = PUB SHIP, ALIAS = SHIP, USAGE = I3,$ Database Management -- Fall 2000 -- R. Larson

  14. SUBFILE Segment FILENAME = SUBFILE, SUFFIX = FOC, $ SEGNAME = SUBINFO, SEGTYPE = S, $ FIELD = SUBID, ALIAS = SID, USAGE = A2, FIELDTYPE = I, $ FIELD = SUBJECT, ALIAS = SUB, USAGE = A32, $ Database Management -- Fall 2000 -- R. Larson

  15. LIBFILE Segment FILENAME = LIBFILE, SUFFIX = FOC, $ SEGNAME = LIBINFO, SEGTYPE = S, $ FIELD = LIBRARYID, ALIAS = LIBID, USAGE = A2, FIELDTYPE = I, $ FIELD = LIBRARY, ALIAS = LIB, USAGE = A42, $ FIELD = LIB ADDRESS, ALIAS = LADDRESS, USAGE = A25, $ FIELD = LIB CITY, ALIAS =LCITY, USAGE = A15, $ FIELD = LIB STATE, ALIAS = LSTATE, USAGE = A2, $ FIELD = LIB ZIP, ALIAS = LZIP, USAGE = A5, $ FIELD = LIB PHONE, ALIAS = LPHONE, USAGE = A10, $ FIELD = MONOPEN, ALIAS = MOP, USAGE = I4, $ FIELD = MONCLOSE, ALIAS = MCL, USAGE = I4, $ FIELD = TUEOPEN, ALIAS = TUOP, USAGE = I4, $ FIELD = TUECLOSE, ALIAS = TUCL, USAGE = I4, $ FIELD = WEDOPEN, ALIAS = WOP, USAGE = I4, $ FIELD = WEDCLOSE, ALIAS = WCL, USAGE = I4, $ FIELD = THURSOPEN, ALIAS = THOP, USAGE = I4, $ FIELD = THURSCLOSE, ALIAS = THCL, USAGE = I4, $ FIELD = FRIOPEN, ALIAS = FOP, USAGE = I4, $ FIELD = FRICLOSE, ALIAS = FCL, USAGE = I4, $ FIELD = SATOPEN, ALIAS = SATOP, USAGE = I4, $ FIELD = SATCLOSE, ALIAS = SATCL, USAGE = I4, $ FIELD = SUNOPEN, ALIAS = SUNOP, USAGE = I4, $ FIELD = SUNCLOSE, ALIAS = SUNCL, USAGE = I4, $ Database Management -- Fall 2000 -- R. Larson

  16. Hierarchic Querying • All searches must proceed from the “root” of the hierarchy, and traverse each segment containing required information Database Management -- Fall 2000 -- R. Larson

  17. BIBINFO PUBINFO INDXINFO CALLINFO SUBINFO LIBINFO Hierarchical Query Processing Database Management -- Fall 2000 -- R. Larson

  18. Authors Subjects Books Publishers Database Data Models • Network Model • Provides for single entries of data and navigational “links” through chains of data. Database Management -- Fall 2000 -- R. Larson

  19. Network Database Systems • Network DBMS are an evolutionary step from Hierarchical systems. • Hierarchical systems can be considered a subset of Network systems. Database Management -- Fall 2000 -- R. Larson

  20. History • Specifications for network systems came from CODASYL (Conference on Data and Systems Languages) -- The same fine folks who brought you COBOL. • The DTBG (Data Base Task Group) was founded in 1965 to specify a “standard language for manipulating records” • The result was a report (published in 1971) Database Management -- Fall 2000 -- R. Larson

  21. History • The CODASYL DBTG report contained specifications for: • A DDL - Data Definition Language • A DML - Data Manipulation Language • Inherent in the report was the underlying Network database structure. Database Management -- Fall 2000 -- R. Larson

  22. Components of DDL and DML • DDL is used to describe or define: • database records • individual data items • the associations that exist between record types • security • record positioning • The database definition created by the DDL is called the database schema • User views can also be defined in the DDL and are called subschemas Database Management -- Fall 2000 -- R. Larson

  23. DDL Continued • DDL is also used by the database designer to define all associations between record types • These associations are called Sets and are sometimes referred to as DTBG Sets. • Sets describe a one to many relationship between two distinct record types. • The record on the “one” side of the set is called the “owner” • The record on the “many” side of the set is called the “member” Database Management -- Fall 2000 -- R. Larson

  24. DDL Definitions • Example DDL for a DB (partial) RECORD NAME IS INVOICE; LOCATION MODE IS VIA CUSTOMER-INVOICE SET WITHIN ORDERENTRY; 02 INVOICE-ID PICTURE IS X(5). 02 INVOICE-DATE PICTURE IS 9(6). 02 INVOICE-AMOUNT TYPE IS BINARY. Etc... SCHEMA NAME IS SAMPLEDB. AREA NAME IS ORDERS. RECORD NAME IS CUSTOMER; LOCATION MODE IS CALC USING CUSTOMERID DUPLICATES ARE NOT ALLOWED; WITHIN ORDERENTRY; 02 CUSTOMERID PICTURE IS X(5). 02 CUSTOMER-NAME PICTURE IS X(30). 02 CUSTOMER-ADDRESS. 05 STREET PICTURE IS X(25). 05 CITY PICTURE IS X(15). 05 STATE PICTURE IS XX. 05 ZIPCODE PICTURE IS X(10). 02 CUSTOMER-TELEPHONE PICTURE IS X(13). Etc…. SET NAME IS CUSTOMER-INVOICE; OWNER IS CUSTOMER INSERTION IS FIRST MEMBER IS INVOICE MANDATORY AUTOMATIC LINKED TO OWNER SET SELECTION IS THRU CUSTOMER-INVOICE CURRENT OF SET. Database Management -- Fall 2000 -- R. Larson

  25. Set Definition and Pointers CUSTOMER INVOICE INVOICE INVOICE INVOICE Database Management -- Fall 2000 -- R. Larson

  26. Set Definitions and Pointers INVOICE Lst Fst LINE-ITEM O N P LINE-ITEM O N P LINE-ITEM O N P LINE-ITEM O N P LINE-ITEM O N P Database Management -- Fall 2000 -- R. Larson

  27. Database Data Models • Relational Model • Provides a conceptually simple model for data as relations (typically considered “tables”) with all data visible. Database Management -- Fall 2000 -- R. Larson

  28. Books (id, title) Authors (first, last) Publisher Subjects Database Data Models • Object Oriented Data Model • Encapsulates data and operations as “Objects” Database Management -- Fall 2000 -- R. Larson

  29. Object-Oriented DBMSBasic Concepts • Each real-world entity is modeled by an object. Each object is associated with a unique identifier (sometimes call the object ID or OID) Database Management -- Fall 2000 -- R. Larson

  30. Object-Oriented DBMSBasic Concepts • Each object has a set of instance attributes (or instance variables) and methods. • The value of an attribute can be an object or set of objects. Thus complex object can be constructed from aggregations of other objects. • The set of attributes of the object and the set of methods represent the object structure and behavior, respectively Database Management -- Fall 2000 -- R. Larson

  31. Object-Oriented DBMSBasic Concepts • The attribute values of an object represent the object’s status. • Status is accessed or modified by sending messages to the object to invoke the corresponding methods Database Management -- Fall 2000 -- R. Larson

  32. Object-Oriented DBMSBasic Concepts • Objects sharing the same structure and behavior are grouped into classes. • A class represents a template for a set of similar objects. • Each object is an instance of some class. Database Management -- Fall 2000 -- R. Larson

  33. Object-Oriented DBMSBasic Concepts • A class can be defined as a specialization of of one or more classes. • A class defined as a specialization is called a subclass and inherits attributes and methods from its superclass(es). Database Management -- Fall 2000 -- R. Larson

  34. Object-Oriented DBMSBasic Concepts • An OODBMS is a DBMS that directly supports a model based on the object-oriented paradigm. • Like any DBMS it must provide persistent storage for objects and their descriptions (schema). • The system must also provide a language for schema definition and and for manipulation of objects and their schema • It will usually include a query language, indexing capabilities, etc. Database Management -- Fall 2000 -- R. Larson

  35. employee Employee No Name Address Date hired Date of Birth calculateAge Hourly Salaried consultant Hourly Rate Annual Salary Stock Option Contract No. Date Hired calculateWage calculateStockBenefit AllocateToContract Generalization Hierarchy Database Management -- Fall 2000 -- R. Larson

  36. Database Data Models • Object-Relational Model (1990’s) • Combines the well-known properties of the Relational Model with such OO features as: • User-defined datatypes • User-defined functions • Inheritance and sub-classing Database Management -- Fall 2000 -- R. Larson

  37. Test Database • The DiveShop database contains information for the business operations of a skin & scuba diving shop that: • Organizes trips to particular locations (destinations) with various dive sites • Dive sites have various features including • types of marine life found there • other features (like shipwrecks) • Rents/Sells equipment to dive customers for particular trips (or to other dive shops) Database Management -- Fall 2000 -- R. Larson

  38. ER Diagrams • We will examine ER diagrams in greater detail later • ER Diagrams show Entities (rectangles) and their attributes (ovals) and the relationships between entities (diamonds) Database Management -- Fall 2000 -- R. Larson

  39. Site Highlight Site Notes Distance From Town (M) Site Name Distance From Town (Km) Destination no Depth (ft) Sites Site no Depth (m) Visibility(ft) Skill Level Visibility (m) Diveshop ER Entities: SITES Database Management -- Fall 2000 -- R. Larson

  40. City State/Prov ZIP/Postal Code Street Country Name Phone DiveCust Customer no First Contact Diveshop ER Entities: DIVECUST Database Management -- Fall 2000 -- R. Larson

  41. Avg Temp (C) Spring Temp (F) Summer Temp (C) Avg Temp (F) Summer Temp (F) Destination name Fall Temp (C) Dest Destination no Fall Temp (F) Accommodations Winter Temp (C) Travel Cost Winter Temp (F) Body of Water Night Life Diveshop ER Entities: DEST Database Management -- Fall 2000 -- R. Larson

  42. Diveshop ER Entities: BIOLIFE Species Name Length (cm) Length (in) Common Name Notes external Category Graphic external BioLife Species no Database Management -- Fall 2000 -- R. Larson

  43. Type Interest Tonnage Category Length (ft) Site no Length (m) Shipwrck Ship Name Beam (ft) Graphic external Condition Beam (m) Passengers/ Crew Cause Survivors Comments external Date Sunk Diveshop ER Entities: SHIPWRCK Database Management -- Fall 2000 -- R. Larson

  44. Diveshop ER Entities: DIVESTOK On Hand Reorder Point Cost Equipment Class Sale Price Description DiveStok Rental Price Item No Database Management -- Fall 2000 -- R. Larson

  45. Diveshop ER Entities: DIVEORDS Ship Via Sale Date Total Invoice Customer No Payment Method DiveOrds Order no CCNumber Vacation Cost CCExpDate Destination No of People Return Date Depart Date Database Management -- Fall 2000 -- R. Larson

  46. Diveshop ER diagram: DIVEITEM Qty Rental/ Sale Item no DiveItem Line Note Order no Database Management -- Fall 2000 -- R. Larson

  47. Species No Site No BioSite Diveshop ER diagram: BIOSITE Database Management -- Fall 2000 -- R. Larson

  48. Ship Via Ship Cost ShipVia Diveshop ER diagram: SHIPVIA Database Management -- Fall 2000 -- R. Larson

  49. Customer No DiveCust Destination Name ShipVia Customer No Destination no ShipVia ShipVia DiveOrds Dest Destination no Destination Order No Site No Order No Sites DiveItem Site No BioSite ShipWrck Item No Species No Site No DiveStok BioLife Item No Species No DiveShop ER Diagram: All 1 n 1 n n 1 1 1 n n 1 1 n 1/n n n 1 1 Database Management -- Fall 2000 -- R. Larson

  50. Assignment 1:Diveshop Questions • How many tons was the sunken ship Delaware? • What is customer Karen Ng’s address? • At what site might you find a Spotted Eagle Ray? • Where is the site Palancar Reef? • What sites might Lorraine Vega dive on her trip? • Keith Lucas wants to see a shipwreck on his trip. Is he going to the right place? • What equipment is Richard Denning getting? • What is the cost of the equipment rental for Louis Jazdzewski Database Management -- Fall 2000 -- R. Larson

More Related