Data Modelling and Design Flashcards

1
Q

Data model pattern

A

A model for a particular type of problem which outline roles and relationships

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Industry data models (consensus)

A

a model for a particular industry area

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Deliverables for data modelling

A
  • Diagrams
  • Definitions
  • Issues and questions
  • Lineage (where the data comes from)

via conceptual, logical, physical data models

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Levels of data model

A

enterprise
conceptual
logical
physical

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Different DBs are different at the ____ and the same everywhere else

A

physical level

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

reverse engineerog

A

from the physical model to the logical model

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

forward engineering

A

from the logical model to the physical model

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

DDL

A

Data definitions language - used to create and modify the structure of objects in a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

External and Internal (SPARC)

A

External = Logical
Internal = Physical

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

explicit relationship

A

x has y, carries data on the relationship itself

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

implicit relationship

A

derived through patterns or relationships within the dataset

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Concrete Business Assertion

A

Relationship

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Enterprise model

A

the big picture

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Conceptual Model

A

Agree basic concepts and rules

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Logical Model

A

detail, physical design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Physical Model

A

Optimised model for specific technical environment

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Supertype Entity

A

Contains the primary key and common attributes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Subtype Entity

A

Contain Specific attributes for each type

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Discriminator

A

Attribute to determine which subtype we are talking about

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Generalisations

A

Supertypes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Specialisations

A

Subtypes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What are the two types of subtype

A

Exclusive and Inclusive

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

When are the boxes, crows feets, sub types and super types modeleld?

A

Logical Level

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Non-identifying relationship types

A

A child entity (many end of relationship) does not depend upon its parent to get its uniqueness.
dotted line

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

How to tell which entity is a child and which is a parent

A

child is the many end of the relationship

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

How to tell in practise a non-identifying relationship type

A

the parents PK becomes just FK in the child, not the PK.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Identifying relationship type

A

A child entity inherits its parents identifier, the child is dependent on its parent.
solid line

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Many to Many Relationship (nonspecific)

A

No way to determine parent and child, Each A have multiple Bs and each B has multiple As.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

What does the resolution of a many to many relationship give

A

An installation/installation entity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Primary Key

A

Unique Identifier of an entity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

Recursive relationship

A

Relationship of an entity onto itself
e.g., employee manged by and manager of

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

A recursive many to many relationship is a…

A

Hierarchy

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

Features of a PK

A
  • Mandatory
  • Unique
  • Unchanging
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

Surrogate key

A

artificial unique identifier not generated from the table data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Candidate Key

A

minimal super key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

Super Key

A

Any combination of attributes that could uniquely identify an entity (may end up with duplicated atrtibutes)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

Alternate key

A

is a secondary key within a table

40
Q

Nomalisation

A
  • removing redundant attributes, duplicate attributes, compounded attributes (e.g., address, name)
  • identifying a primary key and making sure everything depends on this
41
Q

Why normalise data

A
  • improved data quality (ensure integrity, remove redundancy)
  • reduction on timescales
  • easier to maintain
42
Q

1NF

A

Every non-key attribute in an entity must depend on its primary key

43
Q

2NF

A

Each entity must have the fewest possible correct primary key attributes

44
Q

3NF

A

Each non key element must be directly dependent upon the primary key and non upon any other non-key attributes

45
Q

Unnormalized data…

A

contains repeating groups

46
Q

Transitive dependency

A

an attribute that is dependent on another attribute not just the PK

47
Q

Normalised designs are best practise for

A

OLTP

48
Q

Denormalised designs are best practise for

A

OLAP

49
Q

Views, functions and stored procedures may be used for

A

restricting access to certain data

50
Q

Best practise for naming conventions

A

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
Q

Class Naming Convention

A

noun to describe general purpose/intent, maintained in repository (glossary)

52
Q

Prime Naming Convention

A

word/phrase to identify the applciation area/category

53
Q

Modifier Naming Convention

A

adjective or noun to add additional business information

54
Q

Qualifier Naming Convention

A

special modifier, often units of measure

55
Q

Physical Database Design Best Practises

A

PRISM
P - performance
R - reusability
I - Integrity
S - Security
M - Maintainability

56
Q

Denormalisation

A

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
Q

Views

A

logical virtual tables, a wrapper of code, used to simplify queries, control data access and rename columns.

58
Q

Horizontal Partitioning

A

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
Q

Vertical Partitioning

A

Dividing the table based on different columns. E.g., hide sensitive columns.

60
Q

ACID test for transaction processing

A

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
Q

BASE test for transaction processing acronym

A

Basic Availability soft state and eventual consistency

62
Q

When is BASE seen

A
  • microservices, message services
  • large companies processing lots of data
63
Q

What are relationship labels?

A

The verb phrases describing the business rules in each direction between two entities

64
Q

Relationship cardinality

A

whether a relationship is one to many, many to many or one to one

65
Q

ACID acronym

A

atomicity, consistency, isolation, durability

66
Q

Entity type, Entity instance, Dimension, Object, Hub and Node are all examples of what?

A

Entity Alias

67
Q

Simple, Compound, Composite & surrogate are examples of

A

Construction type keys

68
Q

Surrogate keys are an example of

A

a simple key

69
Q

What is the role of data analysts and designers in the Data Governance process?

A

They act as intermediaries between data producers and data consumers and balance the needs of both stakeholder groups

70
Q

Cardinality

A

defines the rules of relationships e.g. how many courses a membership can grant access to

71
Q

three types of data modelling (in order)

A

conceptual, logical, physical

72
Q

conceptual data model

A

captures high-level data requirements and relationships

73
Q

logical data model

A

adds more detail and normalization to the conceptual model

74
Q

Physical model

A

provides a detailed technical solution for storing and managing data e.g. primary keys and foreign keys

75
Q

Schemes to present data

A

relational
dimensional
object orientated
fact-based
time-based
noSQL

76
Q

WHat is a scheme

A

Schemes are different ways to organize and represent data based on specific requirements or use cases.

77
Q

Domain

A

possible values that you could be assigned

78
Q

What three characteristics does the DMBOK say are essential to create high-quality entity definitions (act as core metadata)

A

clarity accuracy completeness

79
Q

Unary, Binary and Ternary are examples of what?

A

Arity of relationship

80
Q

What is a fact table

A

a table that contains measurements (usually additive e.g., sales)

81
Q

What is a dimension table

A

a table combining business elements (field are description of those elements)

82
Q

Normalisation occurs as a conceptual model…

A

becomes a logical model

83
Q

grain of a dimensional model

A

number of rows in the fact table

84
Q

If we have generalised the common attributes and relationships of a group of entities, this generalised entity is known as a?

A

supertype

85
Q

What does relationship cardinality achieve?

A

Cardinality rules show how many of each entity that can participate with how many of another entity they are related to

86
Q

examples of Construction type keys?

A

Simple, Compound, Composit

87
Q

non-identifying relationship

A

The primary key of the parent entity becomes a foreign key in the child entity

88
Q

What are the major design objectives for the relational data modelling scheme?

A

Having an exact representation of business data and keeping one fact in one place

89
Q

description of the third normal form (3NF)?

A

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
Q

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?

A

Clarity, Accuracy and Completeness

91
Q

Unary, Binary and Ternary are examples of what?

A

Arity of relationships

92
Q

Surrogate keys are an example of a

A

simple key

93
Q

Deliverables of the data modelling process

A

Data lineage
Definitions of entities attributes and relationships
a data model diagram
a list of unresolved business questions

94
Q

What are fact tables used for

A

Storing numeric measurements, such as counts and quantities

95
Q

How does ISO 11179 connect Data Modelling to other data management disciplines?

A

How does ISO 11179 connect Data Modelling to other data management disciplines?