Ch 4. Relations
4.7 Relational Model of Databases and Functions
We call a subset of the Cartesian product (see Section 4.3) of n sets an n-ary relation, and write it as
[latex]R \subseteq A_{1} \times A_{2} \times \dots \times A_{n}[/latex]
Each element of the relation is an n-tuple, which corresponds to a row in a relational database table. In database systems, these n-tuples represent structured records, and the entire relation is stored as a table.
Example 4.111
Consider the following table called Students:
| Student ID | Name | Major |
|---|---|---|
| 100074354 | Sophia Johnson | Marketing |
| 100049197 | Ethan Martin | International Business |
| 100014719 | Mia Brown | Marketing |
| 100044276 | Ethan Smith | Accounting |
| 100072036 | Emma Anderson | International Business |
| 100083733 | Olivia Anderson | Marketing |
| 100031497 | Ethan Martin | Marketing |
| 100037777 | Liam Smith | Finance |
| 100019278 | Emma Brown | Management |
| 100099452 | Noah White | Management |
Let [latex]A_{1}[/latex] = Student ID = {100074354, 100049197, 100014719, 100044276, 100072036, 100083733, 100031497, 100037777, 100019278, 100099452}, [latex]A_{2}[/latex] = Name = {Sophia Johnson, Ethan Martin, Mia Brown, Ethan Smith, Emma Anderson, Olivia Anderson, Liam Smith, Emma Brown, Noah White}, and [latex]A_{3}[/latex] = Major = {Marketing, International Business, Accounting, Finance, Management}. Then a ternary relation (or 3-tuple) might include:
(100074354, Sophia Johnson, Marketing)
(100049197, Ethan Martin, International Business)
A database is a structured collection of data. A Database Management System (DBMS) is software used to create, process, and maintain a database. The relational database model organizes data into tables (relations), where each row is an n-tuple, and each column is an attribute. Users interact with the database using queries to retrieve or manipulate data.
A relational algebra expression is a formal way of describing how to retrieve data from a relational database using a sequence of operations on relations (tables). Relational operators with databases include the selection operator, the projection operator, and the join operator.
The selection operator filters rows based on a condition.
Example 4.112
For the Students table, the query
select Major = ‘Marketing’(Students)
uses a selection operator to give the following table:
| Student ID | Name | Major |
|---|---|---|
| 100074354 | Sophia Johnson | Marketing |
| 100014719 | Mia Brown | Marketing |
| 100083733 | Olivia Anderson | Marketing |
| 100031497 | Ethan Martin | Marketing |
The projection operator selects specific columns from a relation.
Example 4.113
For the Students table, the query
project Name(Students)
uses a projection operator to give the following table:
| Name |
|---|
| Sophia Johnson |
| Ethan Martin |
| Mia Brown |
| Ethan Smith |
| Emma Anderson |
| Olivia Anderson |
| Ethan Martin |
| Liam Smith |
| Emma Brown |
| Noah White |
The join operator combines two relations based on a common attribute.
Example 4.114
Let the following table be Grades:
| Student ID | Course | Grade |
|---|---|---|
| 100074354 | Marketing | A |
| 100049197 | Finance | B |
| 100014719 | Marketing | A |
| 100044276 | Accounting | B |
| 100072036 | Business | A |
| 100083733 | Marketing | A |
| 100031497 | Marketing | B |
| 100037777 | Finance | A |
| 100019278 | Management | B |
| 100099452 | Management | B |
For the Students and Grades table, the query
Students join [Students.StudentID=Grades.StudentID] Grades
uses a join operator to give the following table:
| Student ID | Name | Major | Course | Grade |
|---|---|---|---|---|
| 100074354 | Sophia Johnson | Marketing | Marketing | A |
| 100049197 | Ethan Martin | International Business | Finance | B |
| 100014719 | Mia Brown | Marketing | Marketing | A |
| 100044276 | Ethan Smith | Accounting | Accounting | B |
| 100072036 | Emma Anderson | International Business | Business | A |
| 100083733 | Olivia Anderson | Marketing | Marketing | A |
| 100031497 | Ethan Martin | Marketing | Marketing | B |
| 100037777 | Liam Smith | Finance | Finance | A |
| 100019278 | Emma Brown | Management | Management | B |
| 100099452 | Noah White | Management | Management | B |
Selection, projection, and join operators can also be combined as in the following example.
Example 4.115
Find the names of students who are majoring in marketing and received an A grade in any course. For the Students and Grades table, the query
R1 = Students join [Students.StudentID=Grades.StudentID] Grades
R2 = select Major = ‘Marketing’ AND Grade = ‘A’(R1)
project Name(R2)
uses a join operator, then a select operator, and then a projection operator to give the following table:
| Name |
|---|
| Sophia Johnson |
| Mia Brown |
| Olivia Anderson |
Real-World Example 4.7: University Database
Suppose you are an administrator of a university’s relational database that is used to manage student records. The database is organized into three tables called Students, Courses, and Enrollments. The Students table contains records for each student’s personal information, the Courses table contains records for each available class, and the Enrollments table contains records for linking students to courses and grades.
As an administrator, your main task is to perform queries with the database. This may involve selecting students enrolled in a specific course, projecting student names and emails, and joining student and enrollment data to generate transcripts. This system relies on relations, functions, and relational operators to ensure accurate, efficient, and meaningful access to academic data.
