20160118_jeffrey - silenceuncrio/diary GitHub Wiki

Index

  • 1000 - Six-Step Relational Database Design
  • 1040 - Step 1: Discover entities and assign attributes
  • 1040 - Step 1-1: Discover the entities
  • 1105 - Step 1-2: Assign attributes to each entity discovers
  • 1125 - Step 1-3: Select identifiers, keys and primary keys from attributes for each entity
  • 1125 - Step 2: Derive unary and binary relationships
  • 1130 - Step 2-1: Build the Matrix
  • 1140 - Step 2-2: Fill in the Matrix
  • 1310 - Step 3: Create simplified Entity-Relationship diagram
  • 1320 - Step 3-1: Create simplified E-R diagram
  • 1430 - Step 4: List assertions for all relationships
  • 1435 - Step 4-1: List assertions using the simplified E-R diagram

1000

åƒč€ƒäš‹å‰č˛ˇįš„書

Six-Step Relational Database Design

來設計 IoT Platform įš„čŗ‡æ–™åēĢ

1040

Step 1: Discover entities and assign attributes

1040

Step 1-1: Discover the entities

IoT Platform scenario

  • A Control Box may have more IoT detectors
  • There are different IoT detectors
    • KeypadInputs
    • OpenDetectors
    • LightDetectors
    • MotionDetectors
    • VibrateDetectors
    • SoundDetectos
    • TempDetectors
    • CoDetectors
    • BleDetectors
  • An action will be triggered by some rule
  • A rule according some IoT detector's attribute to trigger action

First:

Identify all the collective nouns and nouns in the statement of the problem that represent objects of interest from the problem domain:

  • A Control Box may have more IoT detectors
  • There are different IoT detectors
    • KeypadInputs
    • OpenDetectors
    • LightDetectors
    • MotionDetectors
    • VibrateDetectors
    • SoundDetectos
    • TempDetectors
    • CoDetectors
    • BleDetectors
  • An action will be triggered by some rule
  • A rule according some IoT detector's attribute to trigger action

Second:

List the discovered object of interest (entities):

  • Control Box
  • IoT Detectors
  • KeypadInputs
  • OpenDetectors
  • LightDetectors
  • MotionDetectors
  • VibrateDetectors
  • SoundDetectos
  • TempDetectors
  • CoDetectors
  • BleDetectors
  • action
  • rule

1105

Step 1-2: Assign attributes to each entity discovers

  • Control Box
    • BoxName
    • BoxLocation
    • BoxConfig
  • IoT Detectors
    • DetectorType
  • KeypadInputs
    • KeypadConfig
    • KeypadStatus
  • OpenDetectors
    • OpenConfig
    • OpenStatus
  • LightDetectors
    • LightConfig
    • LightStatus
  • MotionDetectors
    • MotionConfig
    • MotionStatus
  • VibrateDetectors
    • VibrateConfig
    • VibrateStatus
  • SoundDetectos
    • SoundConfig
    • SoundStatus
  • TempDetectors
    • TempConfig
    • TempStatus
  • CoDetectors
    • CoConfig
    • CoStatus
  • BleDetectors
    • BleConfig
    • BleStatus
  • action
    • ActionName
    • ActionConfig
  • rule
    • RuleName
    • RuleConfig

1125

Step 1-3: Select identifiers, keys and primary keys from attributes for each entity

  • Control Box
    • (PK)BoxId
    • BoxName
    • BoxLocation
    • BoxConfig
  • IoT Detectors
    • (PK)DetectorId
    • DetectorType
  • KeypadInputs
    • (PK)KeypadId
    • KeypadConfig
    • KeypadStatus
  • OpenDetectors
    • (PK)OpenId
    • OpenConfig
    • OpenStatus
  • LightDetectors
    • (PK)LightId
    • LightConfig
    • LightStatus
  • MotionDetectors
    • (PK)MotionId
    • MotionConfig
    • MotionStatus
  • VibrateDetectors
    • (PK)VibrateId
    • VibrateConfig
    • VibrateStatus
  • SoundDetectos
    • (PK)SoundId
    • SoundConfig
    • SoundStatus
  • TempDetectors
    • (PK)TempId
    • TempConfig
    • TempStatus
  • CoDetectors
    • (PK)CoId
    • CoConfig
    • CoStatus
  • BleDetectors
    • (PK)BleId
    • BleConfig
    • BleStatus
  • action
    • (PK)ActionId
    • ActionName
    • ActionConfig
  • rule
    • (PK)RuleId
    • RuleName
    • RuleConfig

1125

Step 2: Derive unary and binary relationships

1130

Step 2-1: Build the Matrix

Box Detectors Keypad Open Light Motion Vibrate Sound Temp Co Ble Action Rule
Box
Detectors
Keypad
Open
Light
Motion
Vibrate
Sound
Temp
Co
Ble
Action
Rule

1140

Step 2-2: Fill in the Matrix

Box Detectors Keypad Open Light Motion Vibrate Sound Temp Co Ble Action Rule
Box - - - - - - - - - - - -
Detectors contain - - - - - - - - - - -
Keypad assign - - - - - - - - - -
Open assign - - - - - - - - -
Light assign - - - - - - - -
Motion assign - - - - - - -
Vibrate assign - - - - - -
Sound assign - - - - -
Temp assign - - - -
Co assign - - -
Ble assign - -
Action -
Rule associate associate associate associate associate associate associate associate associate trigger

1310

Step 3: Create simplified Entity-Relationship diagram

1320

Step 3-1: Create simplified E-R diagram

image

1430

Step 4: List assertions for all relationships

1435

Step 4-1: List assertions using the simplified E-R diagram

First

  • A Box can contain many Detectors

  • A Detector can be assigned to only one KeypadInput

  • A Detector can be assigned to only one OpenDetector

  • A Detector can be assigned to only one ...

  • A Detector can be assigned to only one BleDetector

  • A KeypadInput's status can be associated to many Rules

  • A OpenDetector's status can be associated to many Rules

  • A ...'s status can be associated to many Rules

  • A BleDetector's status can be associated to many Rules

  • A Rule can trigger many Actions

Seconds

  • Each Detector can be contained in only one Box

  • Each KeypadInput must be assigned to only one Detectors

  • Each OpenDetector must be assigned to only one Detectors

  • Each ... must be assigned to only one Detectors

  • Each BleDetector must be assigned to only one Detectors

  • Each Rule can be associated to only one KeypadInput's status

  • Each Rule can be associated to only one OpenDetector's status

  • Each Rule can be associated to only one ...'s status

  • Each Rule can be associated to only one BleDetector's status

  • Each Action can be triggered by many Rules