Working with relational data models

Hierarchical and Recursive Queries in SQL Server

Jasmin Ludolf

Content Developer

Basics about relational data models

  • Database model: a type of data model that determines the structure of a database and in which manner data can be stored, organized, and manipulated.

The relational database model is the most widely used database model, which is the standard in database development.

A relational data model consists of:

  • Tables
  • Attributes
  • Relations
  • Relational algebra
Hierarchical and Recursive Queries in SQL Server

Tables and attributes

Properties:

  • Every table has a name (e.g., Personal_Data)
  • Each column describes an attribute (e.g., ID, Name, Birthday)
  • Each row consists of data
ID Name Birthday
1 Adam Smith 1.3.1978
2 Anna Jones 23.8.1991
3 Paul Williams 2.5.1954
4 Jessica Anderson 2.5.1954
Hierarchical and Recursive Queries in SQL Server

Create relations

A relation is created by:

  • primary key
  • foreign key

Properties of primary keys:

  • unique
  • each row has a primary key

Properties of foreign keys:

  • primary key of another table

Example: Order history

Data model for the order history

Hierarchical and Recursive Queries in SQL Server

Define primary and foreign keys

Primary key:

fieldName fieldType NOT NULL PRIMARY KEY,

e.g., for table Person_Data:ID INT NOT NULL PRIMARY KEY

Foreign key:

fieldName fieldType FOREIGN KEY REFERENCES tableName(primaryKey)

e.g., newID INT FOREIGN KEY REFERENCES Person_Data(ID)

Hierarchical and Recursive Queries in SQL Server

Relational algebra

Relational algebra is a formal language for relational databases and makes it possible to form a new relation from two or more relations.

Examples:

  • SELECT
  • UNION
  • DIFFERENCE
  • JOIN
Hierarchical and Recursive Queries in SQL Server

Let's practice!

Hierarchical and Recursive Queries in SQL Server

Preparing Video For Download...