Lecture 3B: Relational Model

Question 1

What is another name for relational Model?

Answer 1

Conceptual model of data.

Question 2

describe facts for the relational model.

Answer 2

1. all data is structured within relations (tables).

2. dominant database solution

Question 3

Quickly list the DB design from top to bottom

Answer 3

1. conceptual db design
2. logical db design
3. physical db design

Question 4

define relation

Answer 4

table with columns and rows.

it is a set, thus no duplicate tuples in a relationship

Question 5

define attributes

Answer 5

a column of a relation

Question 6

define tuples

Answer 6

a row in a relation

Question 7

define domain

Answer 7

a set of allowable values for one or more attributes

Question 8

define degree (of a relation)

Answer 8

number of attributes it contains

Question 9

define cardinality (of a relation)

Answer 9

number of tuples it contains

Question 10

Maths behind Relational Model contains Cartesian products of two sets (D1, D2),

what other names are used to describe these sets?

Answer 10

• a set of ordered pairs
• sets D1 and D2 are called domains.
  can contain n domains. therefore the relation represented with that product has degree n
  and elements are n-tuples

Question 11

in a relational model we have constraints that impose restrictions on values in a column or a combination of columns within a table.

define constraint.

Answer 11

a column or group of columns must be unique across all rows in the table

e.g. student_no must be unique in student table.

Question 12

define superkey

Answer 12

attribute or set of attributes that uniquely identify a tuple in a relation

Question 13

define candidate key

Answer 13

a superkey such that none of its subset is a superkey for that relation.
e.g. its unique and irreducible

Question 14

what is a primary key

Answer 14

the candidate key, that is used to uniquely identify tuples in a relation.
thus, there may be many candidate keys, but only one is chosen as PK

Question 15

What is a foreign key

Answer 15

an attribute or set of attributes within one relation (home relation) that matches the candidate key in another relation.

Question 16

Describe “NULL’

Answer 16

1. represents a value that is unknown or the absence of a value
2. null is different from zero.
3. null can be used when we don’t know the value for an attribute to prevent inserting a false value

Question 17

list two integrity constraints

Answer 17

1. entity integrity

2. referential integrity

Question 18

define entity integrity

Answer 18

no attribute of a primary key can be null

Question 19

define referential integrity

Answer 19

if a FK exists, either:
1.the value of the FK matches the value of the Candidate key in the home relation

2. value of the FK is null

Question 20

List three perspectives

Answer 20

1. user
2. conceptual
3. storage

Question 21

describe how users view the data.

Answer 21

table, row, column

Question 22

describe the conceptual perspective

Answer 22

relation, tuple, attribute

Question 23

describe and explain the storage perspective

Answer 23

file, record, field

describe the details of how the data is stored in the computer.

Question 24

list the two constraints

Answer 24

1. domain

2. primary key

Question 25

explain domain constraint

Answer 25

the value of each attribute in each row must be from the domain assigned to this attribute

Question 26

explain primary key constraint

Answer 26

two distinct tuples in any state of the relation, cannot have identical values for all attributes in the key.