Data Modelling and Design Flashcards
1
Q
Data model pattern
A
A model for a particular type of problem which outline roles and relationships
2
Q
Industry data models (consensus)
A
a model for a particular industry area
3
Q
Deliverables for data modelling
A
- Diagrams
- Definitions
- Issues and questions
- Lineage (where the data comes from)
via conceptual, logical, physical data models
4
Q
What is a data model?
A
describes the inherent logical structure of the data within a given domain and by the implication the underlying structure of that domain itself.
5
Q
Levels of data model
A
enterprise
conceptual
logical
physical
6
Q
Different DBs are different at the ____ and the same everywhere else
A
physical level
7
Q
reverse engineerog
A
from the physical model to the logical model
8
Q
forward engineering
A
from the logical model to the physical model
9
Q
DDL
A
Data definitions language - used to create and modify the structure of objects in a database
10
Q
External and Internal (SPARC)
A
External = Logical
Internal = Physical
11
Q
What does a data model represent?
A
- entities
- attributes
- relationships
- organisation of data, irrespective of how it might be represented on the screen.
12
Q
explicit relationship
A
x has y, carries data on the relationship itself
13
Q
implicit relationship
A
derived through patterns or relationships within the dataset
14
Q
Concrete Business Assertion
A
Relationship
15
Q
Enterprise model
A
the big picture
16
Q
Conceptual Model
A
Agree basic concepts and rules
17
Q
Logical Model
A
detail, physical design
18
Q
Physical Model
A
Optimised model for specific technical environment
19
Q
Supertype Entity
A
Contains the primary key and common attributes
20
Q
Subtype Entity
A
Contain Specific attributes for each type
21
Q
Discriminator
A
Attribute to determine which subtype we are talking about
22
Q
Generalisations
A
Supertypes
23
Q
Specialisations
A
Subtypes
24
Q
What are the two types of subtype
A
Exclusive and Inclusive
25
When are the boxes, crows feets, sub types and super types modeleld?
Logical Level
26
Non-identifying relationship types
A child entity (many end of relationship) does not depend upon its parent to get its uniqueness.
dotted line
27
How to tell which entity is a child and which is a parent
child is the many end of the relationship
28
How to tell in practise a non-identifying relationship type
the parents PK becomes just FK in the child, not the PK.
29
Identifying relationship type
A child entity inherits its parents identifier, the child is dependent on its parent.
solid line
30
Many to Many Relationship (nonspecific)
No way to determine parent and child, Each A have multiple Bs and each B has multiple As.
31
What does the resolution of a many to many relationship give
An installation/installation entity
32
Primary Key
Unique Identifier of an entity.
33
Recursive relationship
Relationship of an entity onto itself
e.g., employee manged by and manager of
34
A recursive many to many relationship is a...
Hierarchy
35
Features of a PK
- Mandatory
- Unique
- Unchanging
36
Surrogate key
artificial unique identifier not generated from the table data.
37
Candidate Key
minimal super key
38
Super Key
Any combination of attributes that could uniquely identify an entity (may end up with duplicated atrtibutes)
39
Alternate key
is a secondary key within a table
40
Nomalisation
- removing redundant attributes, duplicate attributes, compounded attributes (e.g., address, name)
- identifying a primary key and making sure everything depends on this
41
Why normalise data
- improved data quality (ensure integrity, remove redundancy)
- reduction on timescales
- easier to maintain
42
1NF
Every non-key attribute in an entity must depend on its primary key
43
2NF
Each entity must have the fewest possible correct primary key attributes
44
3NF
Each non key element must be directly dependent upon the primary key and non upon any other non-key attributes
45
Unnormalized data...
contains repeating groups
46
Transitive dependency
an attribute that is dependent on another attribute not just the PK
47
Normalised designs are best practise for
OLTP
48
Denormalised designs are best practise for
OLAP
49
Views, functions and stored procedures may be used for
restricting access to certain data
50
Best practise for naming conventions
Class - noun to describe general purpose/intent, maintained in repository (glossary)
Prime - word/phrase to identify the applciation area/category
Modifier - adjective or noun to add additional business information
Qualifier - special modifier, often units of measure
51
Class Naming Convention
noun to describe general purpose/intent, maintained in repository (glossary)
52
Prime Naming Convention
word/phrase to identify the applciation area/category
53
Modifier Naming Convention
adjective or noun to add additional business information
54
Qualifier Naming Convention
special modifier, often units of measure
55
Physical Database Design Best Practises
PRISM
P - performance
R - reusability
I - Integrity
S - Security
M - Maintainability
56
Denormalisation
Selectively and justifiably violating normalisation rules to reduce retrieval time, potentially at the expense of additional space, insert/update time and reduced data quality.
57
Views
logical virtual tables, a wrapper of code, used to simplify queries, control data access and rename columns.
58
Horizontal Partitioning
partitioning of the table into smaller tables on the basis of rows. still logically one table, but store separately making performance and security better.
59
Vertical Partitioning
Dividing the table based on different columns. E.g., hide sensitive columns.
60
ACID test for transaction processing
A - atomicity, every transaction is either completed completely or not at all
C - consistency, every transaction takes the database from one consistent state to another, data is always integral
I - isolation, two transactions happen separately
D - Durability, once its been committed then it is permanently saved
61
BASE test for transaction processing acronym
Basic Availability soft state and eventual consistency
62
When is BASE seen
- microservices, message services
- large companies processing lots of data
63
What are relationship labels?
The verb phrases describing the business rules in each direction between two entities
64
Relationship cardinality
whether a relationship is one to many, many to many or one to one
65
ACID acronym
atomicity, consistency, isolation, durability
66
Entity type, Entity instance, Dimension, Object, Hub and Node are all examples of what?
Entity Alias
67
Simple, Compound, Composite & surrogate are examples of
Construction type keys
68
Surrogate keys are an example of
a simple key
69
What is the role of data analysts and designers in the Data Governance process?
They act as intermediaries between data producers and data consumers and balance the needs of both stakeholder groups
70
Cardinality
defines the rules of relationships e.g. how many courses a membership can grant access to
71
three types of data modelling (in order)
conceptual, logical, physical
72
conceptual data model
captures high-level data requirements and relationships
73
logical data model
adds more detail and normalization to the conceptual model
74
Physical model
provides a detailed technical solution for storing and managing data e.g. primary keys and foreign keys
75
Schemes to present data
relational
dimensional
object orientated
fact-based
time-based
noSQL
76
WHat is a scheme
Schemes are different ways to organize and represent data based on specific requirements or use cases.
77
Domain
possible values that you could be assigned
78
What three characteristics does the DMBOK say are essential to create high-quality entity definitions (act as core metadata)
clarity accuracy completeness
79
Unary, Binary and Ternary are examples of what?
Arity of relationship
80
What is a fact table
a table that contains measurements (usually additive e.g., sales)
81
What is a dimension table
a table combining business elements (field are description of those elements)
82
Normalisation occurs as a conceptual model...
becomes a logical model
83
grain of a dimensional model
number of rows in the fact table
84
If we have generalised the common attributes and relationships of a group of entities, this generalised entity is known as a?
supertype
85
What does relationship cardinality achieve?
Cardinality rules show how many of each entity that can participate with how many of another entity they are related to
86
examples of Construction type keys?
Simple, Compound, Composit
87
non-identifying relationship
The primary key of the parent entity becomes a foreign key in the child entity
88
What are the major design objectives for the relational data modelling scheme?
Having an exact representation of business data and keeping one fact in one place
89
description of the third normal form (3NF)?
Ensures each entity has no hidden primary keys and that each attribute depends on no attributes outside the key (“the key, the whole key and nothing but the key”)
90
Entity definitions are essential to the value of the data model and act as core Metadata for the business. Which three characteristics does the DMBOK say are essential to create high-quality definitions?
Clarity, Accuracy and Completeness
91
Unary, Binary and Ternary are examples of what?
Arity of relationships
92
Surrogate keys are an example of a
simple key
93
Deliverables of the data modelling process
Data lineage
Definitions of entities attributes and relationships
a data model diagram
a list of unresolved business questions
94
What are fact tables used for
Storing numeric measurements, such as counts and quantities
95
How does ISO 11179 connect Data Modelling to other data management disciplines?
How does ISO 11179 connect Data Modelling to other data management disciplines?
