ER Model: what is it (short)?
Detailed, logical representation of the data for an organization/ business
Main elements/ concepts in an ER model: mention the main elements and the sub elements.
- Entities:
- entity type: collection of entities (often a table)
- entity instance: a person, a place, object, event, etc ( often a ROW in a table) - Relationships
- relationship type: category of relationship ( corresponds to links between entity types)
- relationship instance: link between entities ( PK, FK in related tables ) - Attributes
- properties/ characteristics of an entity TYPE or relationship TYPE (a field in a table)
Business Rules: what are they and what do they do?
- Statements that define or constrain some aspect fo the business
- derived from policies, procedures, events
- assert business structure
- control or influence business behavior
- automated through DBMS software
Characteristics of GOOD Business Rules: How should a business rule be?
- Declarative: what, not how
- Precise: clear
- Atomic: one statement
- Consistent: internally and externally
- Expressible: structured, natural language
- Distinct: non-redundant
- Business-Oriented: understood by business people
Data naming: how should entities, relationships and attributes be named?
It is important to ER models.
The names of entities, relationships and their attributes need to be:
- related to business and non-technical
- meaningful
- unique
- readable
- approved list of words
- repeatable
- written in standard syntax
Naming Entity Types: how?
- singular noun
- specific to organization
- ## concise, or abbreviation
Types of entities: just the name
- Strong
- Weak
- Associative
Types of relationship degrees: just names
- Unary
- Binary
- Ternary
Strong vs Weak entities:
- Strong entities exist independently of other types and have unique identifier underlined with single line
- weak entities depend on a strong entity and cannot exist on its own (find the owner). No unique identifier (only partial). Entity box and partial identifier have double lines.
- Link strong entities to weak entities
Attributes: what are they and what types are there?
Attribute = property or characteristic of entity TYPE or relationship TYPE
types of attributes:
- required vs optional
- simple vs composite
- single- valued vs multivalued
- stored vs derived
- identifier
Composite attribute: what is it?
- an attribute that has meaningful component parts (sub-attributes)
ex: Composite attribute = Emp Address
Street address, city, state, postal code (all of these are component attributes/ sub attributes)
- uses ()
Multivalued attributes: explain
- uses {}
- an attribute that can have more than one value
ex: an employee can have more than one {Skill}
Derived attributes: explain
- values are calculated from related attribute values (not physically stored in the db)
- [Years Employed] -> is calculated based on when you got hired and current date
Identifiers (keys)
- single or combination of attributes that uniquely identify individual instances of an entity type
- can be simple or composite
Candidate identifier: what is?
- an attribute that could be an identifier (satisfies requirements)
Criteria for identifiers: how to chose identifiers?
- chose identifiers that will NOT change in value, and will NOT be NULL.
- select constant and unique attributes
Criteria for defining attributes:
- state what the attribute is
- state what is and what isnt included in the attribute value
- include alias in documentation
- specify if required or optional
- state if attribute value can change once set
- state min and max number of occurrences allowed
- indicate relationships with other attributes
