Lecture 3: Relational Databases

Question 1

What is a relational DB

Answer 1

• Popular database type
• Each relation is a table. Relational databases are organized as tables
• Tables: Rows, Columns
• Poweful tool for storing and accessing data
• Simple data representation
• Ease to customise
• Using SQL
• Implemented in most popular DBMS

Question 2

What do the columns contain?

Answer 2

Fields / attributes (field names)

Question 3

What do the rows contain?

Answer 3

Records / tuples

Question 4

What is an attribute domain?

Answer 4

Data type = allowed value, bijv. ’integer’, ‘string’, ‘real number’

Question 5

Can tuples be identical?

Answer 5

No

Question 6

Which two concepts exist on the model level of a DB?

Answer 6

Schema vs. instance

Question 7

Explain schema

Answer 7

meta-data = column head for the data, i.e. how data has to be structured at the logic level.
Specifies name of table, attributes and the attribute domains.
Rarely changes (high costs to update!)

Question 8

Explain instance

Answer 8

Tuple, content of the data
Changes regularly and easy, but conforms to schema

Question 9

Define keys

Answer 9

A set of one or more attributes (columns) in the
table that have certain properties

More formally = Set or combination of attributes in table that have certain properties.

Question 10

Define compound key

Answer 10

Key that includes two or more attributes (and can be used to look up tuples)

Question 11

Define superkey

Answer 11

“Unique key.” Any set of attributes of columns which help to identify rows in a table uniquely. No two tuples have the same values. Can uniquely identify a specific tuple in a table (for example in enrollment database the SID and CID)

Question 12

Define cadidate key

Answer 12

Minimum super key

Question 13

Define primary key

Answer 13

Used to uniquely identify or find the records in a table. A table can have only one primary key.
– Every tuple in a relational database has its own
primary key
- Primary key cannot be null!

Question 14

Define secondary key

Answer 14

Key used to lookup tuple, but may not guarantee uniqueness

Question 15

Define foreign key

Answer 15

Key in another table. Used to locate records in another table.
Key is used a as constraint rather that to find a tuple

Question 16

What is an index?

Answer 16

Database structure making it easier to find tuple based on the values of one or more tuples (is not a key!, but closely related).

For example: use name to find SID. “Attributes most likely needed to search” (because nobody knows their SID, but do know their name). Then you place an index on the attribute “Name”. It takes time to create, so only put indexes on those attributes most likely needed.

Question 17

What is a constraint?

Answer 17

Restriction on data allowed in database. It is not part of the database.

Question 18

Define basic constraint

Answer 18

Constraint related to particular attribute. Bijv. make attribute required (not NULL), or attribute domain (cannot put string in integer field)

Question 19

Check constraint

Answer 19

Evaluates Boolean expression to see if data is allowed in the database (true or false).
- Attribute-level check constraint validates single column (bijv. age > 0).
- Table-leve check constraint can be placed on more of tuple’s attributes to see if the data is valid

Question 20

Primary key constraint

Answer 20

No two records can have the same value for primary key attribute of a table (and not NULL)
Is called “entity integrity”

Question 21

Foreign key constraint

Answer 21

Key in another table. Used to locate records in another table. “Referential integrity constraint”

Constraint requires that a tuple’s value in one or more attributes in one table must match value in another table (i.e. reference table, often the primary key in the foreign table)

Question 22

What does the DB operation ‘projection’ mean?

Answer 22

Drops attributes from a table or selection (bijv. list only student’s names and not their ages)

Question 23

What does the DB operation ‘union’ mean?

Answer 23

Combines tables with similar columns and remove duplicates (bijv. all students enrolled in this course ever)

Question 24

What does the DB operation ‘intersection’ mean?

Answer 24

Find records that are the same in two tables

Question 25

What does the DB operation ‘difference’ mean?

Answer 25

Selects tuples in one table that that are not present in second table

Question 26

What does the DB operation ‘Cartesian product’ mean?

Answer 26

Creates new table containing all tuples in first tables combined with every tuple in second table (see slide 1,2,3 x A,B,C)

The join operation a Cartesian product only if tuples meet some conditions

Question 27

What does the DB operation ‘divide’ mean?

Answer 27

Opposite of Cartesian product. Use one table to partition tuples in another table (so go back to 1,2,3 x A,B,C)