Week 2 -

Question 1

What is the challenge of the conceptual data model?

Answer 1

With the conceptual data model, we need to transform a textual description of a real problem into a set of concepts conveying exactly the same information.

Question 2

What are the two fundamental approaches of conceptual data modelling?

Answer 2

Entity-Relationship Modelling
Relational modelling (mathematical)

Question 3

What is a limitation of ER model?

Answer 3

It does not guarantee optimality in operations and query executions, whereas the Relational Modelling does

Question 4

What is a conceptional data model?

Answer 4

It is a mathematical model for interpreting our data. It uses theorems from set theory, functional dependency and normalisation theory and relational algebra

Question 5

What do we need to identify before using the conceptual data model?

Answer 5

The entities, attributes and relationships

Question 6

Any entity and any relationship are modelled as a relation, which maps to a 2-D table. What are the rows and columns in this 2-D table?

Answer 6

Columns are an ordered set of attributes.
A set of tuples (rows) represents instances.
There exists a specific attribute that uniquely identifies a tuple in the relation

Question 7

How do you define a schema of a relation?

Hint: include relation R, attributes A1,…,An, and domain Di.

Answer 7

The schema of a relation: R(A1,A2,…,An)

Relation with name R and an ordered set of attributes A1,A2,…,An.
Each attribute Ai assumes values in a domain Di

Question 8

How do you define a tuple t of relation R?

Answer 8

A tuple t of R is an ordered set of values corresponding to attributes of R, satisfying the domain constraints:
t=(v1,v2,…,vn), vi in Di

Question 9

What is an instance of a relation r(R)?

Answer 9

An instance of a relation r(R) is a set of tuples:

r(R) = {t1,t2,t3,…,tm}: ti is a tuple of R

Question 10

What are the possible reasons for a null value?

Answer 10

Represents an unknown, inapplicable, uncertain or missing value.

Question 11

How do you define a relational database schema?

Answer 11

it is a set of relations:

s = {R1,R2,…,Rn} or {Null}

Question 12

What is the definition of relational constraints?

Answer 12

Relational constraints are conditions that must hold on all instances, for each relation

Question 13

What are the three fundamental constraints?

Answer 13

Key constraints (unique tuple identification)
Entity integrity constraint (keys are never null)
Referential integrity constraint (interpretation of relationships)

Question 14

What is the superkey of a relation?

Answer 14

A superkey of a relation R is a set of attributes containing at least one attribute that uniquely identifies any tuple.

Question 15

Where SK is the super key and t1 and t2 are two distinct tuples, if t1 doesn’t equal t2 in the instance r(R), then what can we say about t1[SK] and t2[SK]

Answer 15

t1[SK] doesn’t equal t2[SK]

Question 16

What is a candidate key?

Answer 16

A candidate key is the minimal super key; ie the key with the smallest number of attributes that uniquely identifies tuples. i.e. these are the only attributes that i need to provide a unique tuple.

Question 17

How do we prove a candidate key?

Answer 17

Let K = {A1,A2,…,An} i.e. a list of attributes, then:

K is a candidate key if and only if K’ = K{Aj} is not a SK for any Aj in K.

i.e. the removal of any attribute from K results in K’ that is no longer a superkey.

\ means “without”

Question 18

What is the primary key?

Answer 18

One of the available candidate keys.

Question 19

What is the entity integrity constraint principle?

Answer 19

The primary key (PK) cannot be null in any tuple of instance r(R).

i.e. t[PK] is not equal to NULL for any tuple t in r(R)

If PK has several attributes, NULL is not allowed in any of these attributes.

Question 20

What is a composite key?

Answer 20

When the primary key has more than one attribute

Question 21

What is the referential integrity constraint?

Answer 21

Two relations are associated if and only if they both share common attributes.

Question 22

If we have R1 and R2 as relations, what is a foreign key in R1?

Answer 22

There exists an attribute Foreign Key in R1 that either has exactly the same value with the primary key in R2 or is null.

Question 23

If K is a super key, then any superset of K is a….

Answer 23

Superkey

Question 24

The Candidate Key is the minimal superkey: i.e. K is a candidate key if there is no subset of K that is also a superkey. Is this statement true or false?

Answer 24

True

Question 25

Is the following statement true or false:

“A relation can have only one candidate key”?

Answer 25

This is false. A relation can have several different candidate keys; one of them is chosen to be the Primary Key (PK).

Convention: key attributes are underlined in the relation schema.

Question 26

What values should the foreign key have in a referencing relation?

Answer 26

A foreign key should either be NULL (if it is not part of the primary key) or should have a value of the primary key of the referenced relation.

Question 27

What is the secondary key?

Answer 27

Out of the candidate keys, you randomly name one of them the primary key and the rest which weren’t so lucky are secondary keys. Secondary keys therefore should never be null

Question 28

What is a composite primary and composite foreign key?

Answer 28

A primary key that is made by the combination of more than one attribute is known as a composite key.