3. Relational Model

Question 1

How is the relational model related to SQL?

Answer 1

SQL is a practical implementation of the relational concept

SQL and the relational model are not 100% the same

Question 2

What is the relational model?

Answer 2

It is an abstract theory based on mathematics (Set theory and Predicate Logic)

Question 3

Relational Terms

Answer 3

1 ) Tuple

• Ordered list of elements
• Often referred to as a single record
• Implemented as a table row

2) Attributes

• Elements are called either ‘field’ or ‘attribute’
• Attributes are said to have a ‘domain’ and values
• Values are normally atomic/ scalar (not always)

3) Relation

• Set of tuples, e.g. S(A1:D1, A2:D2…Am:Dm)
• Can be thought of as a table (tables are a common implementation of a relation)

4) Schema

• Plan of how data is organized –> blueprint for database design
• Includes logical constraints (table names, attributes, entity relations)
• Often represented visually

Question 4

Relational Model aspects (1)

Answer 4

Every relation is a set

Not every set is a relation

Every relation can be perceived as a table

Not every table is a correct perception of a table (because tables can be a representation of combinations of relations?)

Question 5

Relational Model aspects (2)

Answer 5

Structural - the data is perceived by the user as tables, and nothing but tables

Manipulation - A set of operators available to the user to allow them to manipulate the tables

Integrity - tables satisfy integrity constraints

Question 6

What question does integrity answer?

Answer 6

What are the ways that I could mess up this database if I wanted to?

Question 7

Manipulation

Answer 7

Three main operators:

1) Restrict
2) Project
3) Join

Result of all of these operators is a table

Question 8

Restrict

Answer 8

Implements logical conditionals

E.g. Department WHERE Budget >= 10M

Question 9

Project

Answer 9

Way of extracting only certain elements of a table (useful if, for example, there is no need to see all of the columns)

E.g. Department {Dept#, Budget}

Question 10

Join

Answer 10

Useful for combining relations

E.g. Department JOIN Employee OVER Dept #
–> combines two relations into a table using Dept# as the reference that links the two tables

Question 11

Relational expressions

Answer 11

Output from each expression is a table

Output from one expression can form input to another

Relational expressions can be nested (i.e. operands can be relational expressions)

Question 12

Optimisation

Answer 12

Materialised Evaluation:

• Run part of the query and store the result on a disk as a table
• Next operation of the query runs on this part
• Lower memory usage but slower

Pipelined Evaluation:

• Run next part of the query as rows are generated by first
• Higher memory usage but faster

Relational models are DECLARATIVE languages

Question 13

Types of Languages

Answer 13

1) Procedural

• Series of steps
• Interpreter
• Need to ensure steps produce result

2) Declarative

• Describe what you want
• Interpreter figures out the steps

Question 14

Relational algebra

Answer 14

Purely procedural query language

3 Key operation types:

1) Set operations: set union, set intersection, set difference, cartesian product

2) Relational database specific operations: selection, projection, join, set division

3) Set functions: sum, avg, count, any, max, min

Order of operations can impact performance

Question 15

Procedural Languages Process

Answer 15

1 - Cook beef patty
2 - Toast buns
3 - Put cheese on patty
4 - Take patty off pan and place on bottom bun
5 - Put top bun on patty/cheese combo

–> Start with one piece –> keep adding to the ‘stack’ immediately once each piece is done

More concerned with the step by step process of the outcome

Question 16

Declarative Languages Process

Answer 16

1 - Cooked meat
2 - Melted cheese
3 - Toasted buns
4 - Patty on bottom buns
5 - Cheese on patty
6 - Top bun on top

–> build each piece separately and then join them together when needed

Focuses on specifying the desired outcome without defining the desired steps

Question 17

Optimisation

Answer 17

Raw query –> query parser –> query optimizer –> query evaluator –> result

Question 18

Integrity

Answer 18

What are some potential problems that could mess up our data / database?

–> helps prevent those problems

Problems integrity helps solve:

1) Ensure data is well formed
2) Ensure a record isn’t entered more than once
3) Connecting data from multiple relations

Question 19

How to ensure data is well-formed?

Answer 19

Data types enforce proper format (e.g. integer)

–> does not prevent wrong values, just prevents wrong format

Question 20

How to ensure a record isn’t entered more than once?

Answer 20

Use of primary keys –> enforce unique identifiers

Question 21

Primary key

Answer 21

Minimum combination of one of more columns that uniquely identifies a row

Table that contains the primary key is the PARENT table

Question 22

Connecting data from multiple relations

Answer 22

Use of foreign keys –> enforces values that exist in other tables

Question 23

Foreign key

Answer 23

Constraint that establishes a link between two tables by referencing the primary key of one table from another table

Table that contains the foreign key is the CHILD table –> references the parent table when connecting them

Question 24

The catalog

Answer 24

Catalog of a database consists of metadata in which definitions of database objects are stored

–> keeps details of tables, columns, rows…

Catalog is a relational variable

Question 25

Relational Variable (RV)

Answer 25

Symbol that can reference different values at different times

Two types:

1) Base RV –> relational variable which is already defined (not derived from any other relational variables)

2) Derived RV –> variables that are computed from one or more variables (e.g. View, can be derived from a combination of tables)

Note: views are not carried out. They are just stored in the catalog and evaluated in actual time.