Chapter 7 - Database Design with Semantic Object Models

Transformation of Semantic Objects into Relational Database Designs

Simple Objects - contains only single-valued, nonobject attributes (ex. EQUIPMENT)

EQUIPMENT

_ID  EquipmentNumber
      Description
      AcquisitionDate
      PurchaseCost

EQUIPMENT

EquipmentNumber Description, AcquisitionDate, PurchaseCost

Composite Objects - contains one or more multivalued nonobject attributes (ex. HOTEL-BILL)

HOTEL-BILL

_ID  InvoiceNumber
      ArrivalDate
      CustomerName
      TotalDue
    DailyCharge

_ID  ChargeDate
      RoomCharge
      FoodCharge
      PhoneCharge
    MiscCharge
    TaxCharge

HOTEL-BILL

InvoiceNumber ArrivalDate, CustomerName, TotalDue

DAILY-CHARGE

InvoiceNumber, ChargeDate RoomCharge, FoodCharge, PhoneCharge, MiscCharge, TaxCharge

Compound Objects - contains at least one object attribute (ex. COLLEGE)

Four Types of Compound Objects
Object2
Can
Contain Object1 Can Contain

One Many

One 1:1 1:N

Many M:1 M:N

Four Types of Compound Objects
Object2 Can Contain	Object1 Can Contain
	One	Many
One	1:1	1:N
Many	M:1	M:N

Representing One-to-One Compound Objects

MEMBER

_ID  MemberNumber
      Name
      Address
      City
    State
    Zip
    LOCKER_1.1

LOCKER

_ID  LockerNumber
      Type
      Combination
      Location
    MEMBER_0.1

MEMBER

MemberNumber Name, Address, City, State, Zip, LockerNumber

LOCKER

LockerNumber Type, Combination, Location

Representing One-to-Many Compound Objects

EQUIPMENT

_ID  SerialNumber
      Type
      Model
      AcquisitionDate
    AcquistionCost
    Location
    REPAIR_0.N

REPAIR

_ID  InvoiceNumber
      Date
      Description
      Cost
    EQUIPMENT_1.1

EQUIPMENT

SerialNumber Type, Model, AcquisitionDate, AcquistionCost, Location

REPAIR

InvoiceNumber Date, Description, Cost, SerialNumber

Representing Many-to-Many Compound Objects

BOOK

_ID  ISBN
      Title
      CallNumber
    AUTHOR_1.N

AUTHOR

_ID  SocialSecurityNumber
      Name
      Phone
    BOOK_0.N

BOOK

ISBN Title, CallNumber

AUTHOR

SocialSecurityNumber Name, Phone

BOOK-AUTHOR-INT

ISBN, SocialSecurityNumber

Hybrid Objects - combinations of objects of two types (semantic object with at least one multivalued group attribute that includes a semantic object attribute)

SALES-ORDER

_ID  SalesOrderNumber
      Date
    CUSTOMER_1.1
    SALESPERSON_1.1
    LineItem_1.N

      Quantity
_ID  ITEM_1.1
      ExtendedPrice_1.1

    Subtotal_1.1
    Tax_1.1
    Total_1.1

ITEM

_ID  ItemNumber
      ItemDescription
      UnitPrice_1.1
    SALES-ORDER_0.N

CUSTOMER

_ID  CustomerName
      Address
      City
      State
      Zip
_ID    Phone
    SALES-ORDER_0.N

SALESPERSON

_ID  SalesPersonName
      SalesPersonCode_1.1
    SALES-ORDER_0.N

SALES-ORDER

SalesOrderNumber Date, Subtotal, Tax, Total, Phone, SalespersonName

CUSTOMER

CustomerName, Address, City, State, Zip Phone

SALESPERSON

SalesPersonName SalesPersonCode

LINE-ITEM

SalesOrderNumber, ItemNumber Quantity, ExtendedPrice

ITEM

ItemNumber ItemDescription, UnitPrice

Association Objects - object that relates two (or more) objects and stores data that are peculiar to that relationship [ex. FLIGHT is an association object that associates AIRPLANE and PILOT objects

AIRPLANE

_ID  TailNumber
      Type
      Hours
    FLIGHT_0.N

PILOT

_ID  PilotNumber
      Name
      Phone
    FLIGHT_0.N

FLIGHT

_ID  FlightID
         FlightName
         Date
      OriginationCity
      DestinationCity
    AIRPLANE_1.1
    PILOT_1.1

AIRPLANE

TailNumber TypeHours

PILOT

PilotNumber Name, Phone

FLIGHT

FlightNumber, Date OriginationCity, DestinationCity, TailNumber, PilotNumber

Parent/Subtype Objects - the subtype inherits all of the attributes of its parent [ex. MANAGER / EMPLOYEE where a manager is also an employee]

PERSON

_ID  SocialSecurityNumber
      PersonName
      Phone
    STUDENT_0.ST
    PROFESSOR_0.ST
_0.1.1

STUDENT

_ID  PERSON_P
      DateofBirth
      GPA

PROFESSOR

_ID  PERSON_P
      Title
      Department

PERSON

SocialSecurityNumber PersonName, Phone

STUDENT

SocialSecurityNumber DateofBirth, GPA

PROFESSOR

SocialSecurityNumber Title, Department

PERSON1

SocialSecurityNumber PersonName, Phone, PersonType

PERSON2

SocialSecurityNumber PersonName, Phone, StudentType, ProfessorType

ArchType/Version Objects - produces other semantic objects that represent versions, releases, or editions of the archetype [ex. TEXTBOOK produces EDITIONs]

PRODUCT

_ID  Name
      Description
      TotalSales
    RELEASE_0.N

RELEASE

_ID  Release-ID_1.1
      PRODUCT_1.1
      ReleaseNumber_1.1
    ReleaseDate
    ReleaseSales

PRODUCT

Name Description, TotalSales

RELEASE

Name, ReleaseNumber ReleaseDate, ReleaseSales

Sample Objects

Subscription Form

SUBSCRIPTION

_ID  SubNumber
      StartDate
      EndDate
      AmtDue
      Name
      Address
      City
      State
      Zip
      PayCode

SUBSCRIPTION

SubNumber StartDate, EndDate, AmtDue, Name, Address, City, State, Zip, PayCode

CUSTOMER

_ID  CustomerNumber
      Name
      Address
      City
      State
      Zip
    SUBSCRIPTION_0.N

SUBSCRIPTION

_ID  SubNumber
      StartDate
      EndDate
      AmtDue
      PayCode     CUSTOMER_1.1

CUSTOMER

CustomerNumber Name, Address, City, State, Zip

SUBSCRIPTION

SubNumber StartDate, EndDate, AmtDue, PayCode

Product Description

CEREAL-PRODUCT

_ID  Name
      ServingSize
      NumberServings
      Nutrient
    _ID  NutrientName
        CerealOnlyAmount
        WithMilkAmt
      USDARecDailyAllow
    _ID  VitaminName
        CerealOnlyPercent
        WithMilkPercent
      Ingredient
      VitaminIron

Traffic-Warning Citation - see pg. 181