Week 3 Flashcards

1
Q

What are the two approaches to database design?

A

Normalisation (bottom-up) and Semantic Data Modelling (top-down)

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

What is normalisation (Bottom-Up) in database design?

A

this has one table describing all relevant data and serves as the starting point. Consists of multiple tables which are decomposed until 3NF.

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

What is Semantic Data Modelling (Top-down) in database design?

A

Knowledge about business processes and information needs is used to create a diagram (entity relationship diagram). This describes the data and its relationships that are to be recorded in a database.

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

What is Entity Relationship Modelling (ERM)

A

ERM is a modelling notation used to model the characteristics and relationships of data. Useful to formalize and visualize the structure of data for implementation of databases.

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

What does ERD consist of?

A

Entity, Attributes and Relationships

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

What are entities in ERD?

A

Real word things we want to record data about

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

What are attributes in ERD?

A

Characteristics of entities, all entities should have the same in common. Predefined value range.

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

What are relationships in ERD?

A

Associations between entities. Defines a set of relationships among entities from different entity types. A relationship type can also have an attribute.

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

What are cardinalities?

A

Expresses the number of entity occurrences associated with one occurrence of a related entity.

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

What is an entity type?

A

collection of entities with similar characteristics. The name should be a generic term describing all entities of the collection.

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

What is a key?

A

The identifying attribute - it is an attribute that unambiguously identifies an entity. A key can be a combination of multiple attributes.

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

What are the 4 meanings of carinalities?

A

1 = {1}
C = {0, 1}
N = {1, 2, …, n}
NC = {0, 1, 2, …, n}

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

What is an 1:1 relationship?

A

Everyone uses one

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

What is a C:C relationship?

A

Everyone can have 1 or 0

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

What is an example of 1:N relationship?

A

Mother & child

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

What is an example of 1:NC relationship?

A

Woman & child

17
Q

What is an example of 1:C relationship?

A

Woman & Husband

18
Q

What is an example of M:N relationship?

A

House & Landlord

19
Q

What is an example of M:NC relationship?

A

Lecturer & Lecture

20
Q

What is an example of MC:NC relationship?

A

Adult & Child

21
Q

What should be done with attributes that cannot be assigned to an entity?

A

Attributes that cannot be assigned to an entity have to be assigned to relationships

22
Q

What is step 1 of ERM into relational models?

A

Every entity type is translated into a table, the attributes of the entity type become the columns of the table

23
Q

What is step 2 of ERM into relational models?

A

1:1 relationships

24
Q

What is step 3 of ERM into relational models?

A

1:N relationships

25
Q

What is step 4 of ERM into relational models?

A

M:N relationships

26
Q

What is the process in step 1; transformation of entities?

A
  • An entity type becomes a table
  • Attributes become column
  • A single entity is represented by a single row
  • The primary key is used to unambiguously identify one row.
27
Q

What is the process in step 2; transformation of 1:1-relationships?

A

The data from two tables is combined into one single table.

28
Q

What is the process in step 3: transformation of 1:N-relationships?

A

You have 2 tables. The primary key of the higher table (Customer) is added as an additional column (foreign key) in the lower order table (Order)

29
Q

What is the process in step 4: transformation of M:N-relationships?

A

3 tables are required. The new table includes the primary key of both entity tables and attributes of the relationships.

30
Q

What does the transformation of C:C carinals look like?

A

Usually C:C is treated is NC:NC, to avoid NULL values.

31
Q

4 Data requirements for Relational Databases

A
  1. Every column must be single valued.
  2. Primary key must contain data (nut null)
  3. Referential integrity
  4. All non-key attributes must describe a characteristic of the entity identified by the primary key.
32
Q

What is referential integrity?

A

Foreign keys must contain the same data as the primary key in another table

33
Q

The constraint that all primary keys must have non-null values is referred to as

A

Entity integrity rule