Entity-Relationship Diagrams, Database Design

Annotations:

• properties of entities

Annotations:

• to determine cardinality ask: A single instance of A can RELATIONSHIP at most HOW MANY instances of B ..then reverse it to find card of A

1. SERIALIZED

   Annotations:

   • ex. VIN # ...each individual instance is given a unique identitifier...PK
   1. VS
2. VIRTUAL INSTANCE

   Annotations:

   • Instances that do not need to be unique

Annotations:

• A logical concept in the mind of the analyst that represents a category of objects that have common characteristics or attributes

1. PK

   Annotations:

   • Uniquely identifies an instance
   1. PK REQUIREMENTS
      1. UNIQUE ID
      2. NON-REDUNDANCY
2. FK
3. SIMPLE KEY

   Annotations:

   • contains a single attribute
4. COMPOSITE KEY

   Annotations:

   • Contains more than one attribute
5. SEMANTIC (NATURAL) KEYS

   Annotations:

   • possible values have meaning like USA
6. ARTIFICIAL KEYS

   Annotations:

   • values have no meaning...like order ID...seq of #'s
7. MULTI-ATTRIBUTE KEYS

Annotations:

• how entities relate to one another; CUST buys PRODUCT

1. M:M
   1. Need to add intersection table
      1. LINKING TABLE
         1. A: PK LINK: A1.PKFK B1. PKFK B: PK
2. 1:1

   Annotations:

   • A can only relate to one and only one instance of B
   1. mandatory: one record of each on one side: PK with a FK one captain-one ship
   2. optional: 1:0...1 becomes FK in 0 table
3. 1:M

   Annotations:

   • An instance where A can relate to one or many instances of B PK:FK

1. UPDATE

   Annotations:

   • all records need to be updated for no inconsistencies
2. INSERT

   Annotations:

   • When certain facts cannot be recorded
3. DELETE