20160119_jeffrey - silenceuncrio/diary GitHub Wiki

Index

  • 1020 - Step 4-1: List assertions using the simplified E-R diagram - exclude different things
  • 1040 - Step 4-1: List assertions using the simplified E-R diagram - different things
  • 1045 - Step 3-1: Create simplified E-R diagram
  • 1315 - Step 5: Create detail E-R diagram using assertion
  • 1330 - Step 5-1: Use assertions and the simplified E-R diagram to create the detailed E-R diagram - Exclude Different Things
  • 1345 - Step 5-1: Use assertions and the simplified E-R diagram to create the detailed E-R diagram - Different Things
  • 1400 - Step 6: Transform the detailed E-R diagram into an implementable R-M diagram
  • 1410 - Step 6-1: Many-to-many relationships - Exclude Different Things
  • 1445 - Step 6-1: Many-to-many relationships - Different Things
  • 1450 - Step 6-2: One-to-many relationships - Exclude Different Things
  • 1500 - Step 6-2: One-to-many relationships - Different Things
  • 1505 - Step 6-3 One-to-one relationships - Exclude Different Things
  • 1515 - Step 6-3: One-to-many relationships - Different Things
  • 1620 - Implementation of Design - Entities and Attributes
  • 1630 - Implementation of Design - R-M Diagram
  • 1650 - Implementation of Design - Data Dictionary

1020

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

不同種類的 Thing 先不看
再 review 一次並做修改 Step 4-1

First

  • A Box must contain at least one Thing
  • A Thing's status can be associated to many Rules
  • A Rule must trigger at least one Actions

Seconds

  • Each Thing must be contained in only one Box
  • Each Rule must be associated to at least one Thing's status
  • Each Action can be triggered by many Rules

1040

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

First

  • A KeypadInput must be assigned to only one Thing
  • A OpenDetector must be assigned to only one Thing
  • A LightDetector must be assigned to only one Thing
  • A MotionDetector must be assigned to only one Thing
  • A VibrateDetector must be assigned to only one Thing
  • A SoundDetector must be assigned to only one Thing
  • A TempDetector must be assigned to only one Thing
  • A CoDetector must be assigned to only one Thing
  • A BleDetector must be assigned to only one Thing

Seconds

  • Each Thing can be assigned to only one KeypadInput
  • Each Thing can be assigned to only one OpenDetector
  • Each Thing can be assigned to only one LightDetector
  • Each Thing can be assigned to only one MotionDetector
  • Each Thing can be assigned to only one VibrateDetector
  • Each Thing can be assigned to only one SoundDetector
  • Each Thing can be assigned to only one TempDetector
  • Each Thing can be assigned to only one CoDetector
  • Each Thing can be assigned to only one BleDetector

1045

Step 3-1: Create simplified E-R diagram

根據今天的推導再畫一次

Control Center Database Step 3-1 Exclude Different Things

image

Control Center Database Step 3-1 Different Things

image

1315

Step 5: Create detail E-R diagram using assertion

  • Optionality 0 - can(optional)
  • Optionality 1 - Must(obligatory)
  • Capdinality 1 - Only one
  • Capdinality N - Many or At least one

All the possible combinations of optionality:cardinality that can appear on the detail E-R diagram

  • 0:1 - [Entity] can [relationship] only one [Entity]
  • 0:N - [Entity] can [relationship] many [Entity]; or [Entity] can [relationship] at least one [Entity]
  • 1:1 - [Entity] must [relationship] only one [Entity]
  • 1:N - [Entity] must [relationship] many [Entity]; or [Entity] must [relationship] at least one [Entity]

1330

Step 5-1: Use assertions and the simplified E-R diagram to create the detailed E-R diagram - Exclude Different Things

  • Boxes - Things
    • A Box must contain at least one Thing - 1:N
    • Each Thing must be contained in only one Box - 1:1
  • Things - Rules
    • A Thing's status can be associated to many Rules - 0:N
    • Each Rule must be associated to at least one Thing's status - 1:N
  • Rules - Actions
    • A Rule must trigger at least one Actions - 1:N
    • Each Action can be triggered by many Rules - 0:N

image

1345

Step 5-1: Use assertions and the simplified E-R diagram to create the detailed E-R diagram - Different Things

  • KeypadInputs - Things
    • A KeypadInput must be assigned to only one Thing - 1:1
    • Each Thing can be assigned to only one KeypadInput - 0:1
  • OpenDetectors - Things
    • A OpenDetector must be assigned to only one Thing - 1:1
    • Each Thing can be assigned to only one OpenDetector - 0:1
  • LightDetectors - Things
    • A LightDetector must be assigned to only one Thing - 1:1
    • Each Thing can be assigned to only one LightDetector - 0:1
  • MotionDetectors - Things
    • A MotionDetector must be assigned to only one Thing - 1:1
    • Each Thing can be assigned to only one MotionDetector - 0:1
  • VibrateDetectors - Things
    • A VibrateDetector must be assigned to only one Thing - 1:1
    • Each Thing can be assigned to only one VibrateDetector - 0:1
  • SoundDetectors - Things
    • A SoundDetector must be assigned to only one Thing - 1:1
    • Each Thing can be assigned to only one SoundDetector - 0:1
  • TempDetectors - Things
    • A TempDetector must be assigned to only one Thing - 1:1
    • Each Thing can be assigned to only one TempDetector - 0:1
  • CoDetectors - Things
    • A CoDetector must be assigned to only one Thing - 1:1
    • Each Thing can be assigned to only one CoDetector - 0:1
  • BleDetectors - Things
    • A BleDetector must be assigned to only one Thing - 1:1
    • Each Thing can be assigned to only one BleDetector - 0:1

image

1400

Step 6: Transform the detailed E-R diagram into an implementable R-M diagram

1410

Step 6-1: Many-to-many relationships - Exclude Different Things

image

1445

Step 6-1: Many-to-many relationships - Different Things

image

1450

Step 6-2: One-to-many relationships - Exclude Different Things

image

1500

Step 6-2: One-to-many relationships - Different Things

image

1505

Step 6-3 One-to-one relationships - Exclude Different Things

image

1515

Step 6-3: One-to-many relationships - Different Things

image

1620

Implementation of Design - Entities and Attributes

Boxes Things Rules Actions
(PK)BoxId (PK)ThingId (PK)RuleId (PK)ActionId
BoxName ThingType RuleName ActionName
BoxLocation ThingStatus_ RuleConfig_ ActionConfig_
BoxConfig_
KeypadInputs OpenDetectors LightDetectors MotionDetectors VibrateDetectors
(PK)KeypadId (PK)OpenId (PK)LightId (PK)MotionId (PK)VibrateId
KeypadConfig_ OpenConfig_ LightConfig_ MotionConfig_ VibrateConfig_
KeypadStatus_ OpenStatus_ LightStatus_ MotionStatus_ VibrateStatus_
SoundDetectos TempDetectors CoDetectors BleDetectors
(PK)SoundId (PK)TempId (PK)CoId (PK)BleId
SoundConfig_ TempConfig_ CoConfig_ BleConfig_
SoundStatus_ TempStatus_ CoStatus_ BleStatus_

1630

Implementation of Design - R-M Diagram

image

image

1650

Implementation of Design - Data Dictionary

Table - Boxes

Column Data Type Length Indexed Required(Default)
(PK)BoxId Number NA Yes, Primary Key Yes
BoxName Character 16 No Yes
BoxLocation Character 32 No Yes
BoxConfig_ Character 32 No Yes

Table - Things

Column Data Type Length Indexed Required(Default)
(PK)ThingId Number NA Yes, Primary Key Yes
ThingType Number NA No Yes
ThingStatus_ Character 32 No No
(FK)BoxId Number NA Yes, Foreign Key Yes

Table - ThingRules

Column Data Type Length Indexed Required(Default)
(PK)ThingRuleId Number NA Yes, Primary Key Yes
(FK)ThingId Number NA Yes, Foreign Key Yes
(FK)RuleId Number NA Yes, Foreign Key Yes

Table - Rules

Column Data Type Length Indexed Required(Default)
(PK)RuleId Number NA Yes, Primary Key Yes
RuleName Character 16 No Yes
RuleConfig_ Character 32 No Yes

Table - RuleActions

Column Data Type Length Indexed Required(Default)
(PK)RuleActionId Number NA Yes, Primary Key Yes
(FK)RuleId Number NA Yes, Foreign Key Yes
(FK)ActionId Number NA Yes, Foreign Key Yes

Table - Actions

Column Data Type Length Indexed Required(Default)
(PK)ActionId Number NA Yes, Primary Key Yes
ActionName Character 16 No Yes
ActionConfig_ Character 32 No Yes

Table - KeypadInputs

Column Data Type Length Indexed Required(Default)
(PK)KeypadId Number NA Yes, Primary Key Yes
KeypadConfig_ Character 32 No Yes
KeypadStatus_ Character 32 No Yes
(FK)ThingId Number NA Yes, Foreign Key Yes

Table - LightDetectors

Column Data Type Length Indexed Required(Default)
(PK)LightId Number NA Yes, Primary Key Yes
LightConfig_ Character 32 No Yes
LightStatus_ Character 32 No Yes
(FK)ThingId Number NA Yes, Foreign Key Yes

Table - OpenDetectors

Column Data Type Length Indexed Required(Default)
(PK)OpenId Number NA Yes, Primary Key Yes
OpenConfig_ Character 32 No Yes
OpenStatus_ Character 32 No Yes
(FK)ThingId Number NA Yes, Foreign Key Yes

Table - MotionDetectors

Column Data Type Length Indexed Required(Default)
(PK)MotionId Number NA Yes, Primary Key Yes
MotionConfig_ Character 32 No Yes
MotionStatus_ Character 32 No Yes
(FK)ThingId Number NA Yes, Foreign Key Yes

Table - VibrateDetectors

Column Data Type Length Indexed Required(Default)
(PK)VibrateId Number NA Yes, Primary Key Yes
VibrateConfig_ Character 32 No Yes
VibrateStatus_ Character 32 No Yes
(FK)ThingId Number NA Yes, Foreign Key Yes

Table - SoundDetectors

Column Data Type Length Indexed Required(Default)
(PK)SoundId Number NA Yes, Primary Key Yes
SoundConfig_ Character 32 No Yes
SoundStatus_ Character 32 No Yes
(FK)ThingId Number NA Yes, Foreign Key Yes

Table - TempDetectors

Column Data Type Length Indexed Required(Default)
(PK)TempId Number NA Yes, Primary Key Yes
TempConfig_ Character 32 No Yes
TempStatus_ Character 32 No Yes
(FK)ThingId Number NA Yes, Foreign Key Yes

Table - CoDetectors

Column Data Type Length Indexed Required(Default)
(PK)CoId Number NA Yes, Primary Key Yes
CoConfig_ Character 32 No Yes
CoStatus_ Character 32 No Yes
(FK)ThingId Number NA Yes, Foreign Key Yes

Table - BleDetectors

Column Data Type Length Indexed Required(Default)
(PK)BleId Number NA Yes, Primary Key Yes
BleConfig_ Character 32 No Yes
BleStatus_ Character 32 No Yes
(FK)ThingId Number NA Yes, Foreign Key Yes