Week 3

Question 1

What are the two approaches to database design?

Answer 1

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

Question 2

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

Answer 2

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

Question 3

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

Answer 3

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.

Question 4

What is Entity Relationship Modelling (ERM)

Answer 4

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.

Question 5

What does ERD consist of?

Answer 5

Entity, Attributes and Relationships

Question 6

What are entities in ERD?

Answer 6

Real word things we want to record data about

Question 7

What are attributes in ERD?

Answer 7

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

Question 8

What are relationships in ERD?

Answer 8

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

Question 9

What are cardinalities?

Answer 9

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

Question 10

What is an entity type?

Answer 10

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

Question 11

What is a key?

Answer 11

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

Question 12

What are the 4 meanings of carinalities?

Answer 12

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

Question 13

What is an 1:1 relationship?

Answer 13

Everyone uses one

Question 14

What is a C:C relationship?

Answer 14

Everyone can have 1 or 0

Question 15

What is an example of 1:N relationship?

Answer 15

Mother & child

Question 16

What is an example of 1:NC relationship?

Answer 16

Woman & child

Question 17

What is an example of 1:C relationship?

Answer 17

Woman & Husband

Question 18

What is an example of M:N relationship?

Answer 18

House & Landlord

Question 19

What is an example of M:NC relationship?

Answer 19

Lecturer & Lecture

Question 20

What is an example of MC:NC relationship?

Answer 20

Adult & Child

Question 21

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

Answer 21

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

Question 22

What is step 1 of ERM into relational models?

Answer 22

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

Question 23

What is step 2 of ERM into relational models?

Answer 23

1:1 relationships

Question 24

What is step 3 of ERM into relational models?

Answer 24

1:N relationships

Question 25

What is step 4 of ERM into relational models?

Answer 25

M:N relationships

Question 26

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

Answer 26

- 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.

Question 27

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

Answer 27

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

Question 28

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

Answer 28

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)

Question 29

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

Answer 29

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

Question 30

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

Answer 30

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

Question 31

4 Data requirements for Relational Databases

Answer 31

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.

Question 32

What is referential integrity?

Answer 32

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

Question 33

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

Answer 33

Entity integrity rule