Data Modelling and Design Flashcards
Data model pattern
A model for a particular type of problem which outline roles and relationships
Industry data models (consensus)
a model for a particular industry area
Deliverables for data modelling
- Diagrams
- Definitions
- Issues and questions
- Lineage (where the data comes from)
via conceptual, logical, physical data models
What is a data model?
describes the inherent logical structure of the data within a given domain and by the implication the underlying structure of that domain itself.
Levels of data model
enterprise
conceptual
logical
physical
Different DBs are different at the ____ and the same everywhere else
physical level
reverse engineerog
from the physical model to the logical model
forward engineering
from the logical model to the physical model
DDL
Data definitions language - used to create and modify the structure of objects in a database
External and Internal (SPARC)
External = Logical
Internal = Physical
What does a data model represent?
- entities
- attributes
- relationships
- organisation of data, irrespective of how it might be represented on the screen.
explicit relationship
x has y, carries data on the relationship itself
implicit relationship
derived through patterns or relationships within the dataset
Concrete Business Assertion
Relationship
Enterprise model
the big picture
Conceptual Model
Agree basic concepts and rules
Logical Model
detail, physical design
Physical Model
Optimised model for specific technical environment
Supertype Entity
Contains the primary key and common attributes
Subtype Entity
Contain Specific attributes for each type
Discriminator
Attribute to determine which subtype we are talking about
Generalisations
Supertypes
Specialisations
Subtypes
What are the two types of subtype
Exclusive and Inclusive
When are the boxes, crows feets, sub types and super types modeleld?
Logical Level
Non-identifying relationship types
A child entity (many end of relationship) does not depend upon its parent to get its uniqueness.
dotted line
How to tell which entity is a child and which is a parent
child is the many end of the relationship
How to tell in practise a non-identifying relationship type
the parents PK becomes just FK in the child, not the PK.
Identifying relationship type
A child entity inherits its parents identifier, the child is dependent on its parent.
solid line
Many to Many Relationship (nonspecific)
No way to determine parent and child, Each A have multiple Bs and each B has multiple As.
What does the resolution of a many to many relationship give
An installation/installation entity
Primary Key
Unique Identifier of an entity.
Recursive relationship
Relationship of an entity onto itself
e.g., employee manged by and manager of
A recursive many to many relationship is a…
Hierarchy
Features of a PK
- Mandatory
- Unique
- Unchanging
Surrogate key
artificial unique identifier not generated from the table data.
Candidate Key
minimal super key
Super Key
Any combination of attributes that could uniquely identify an entity (may end up with duplicated atrtibutes)