Sets in Relational Database

Section 1.4 Sets in Relational Database

Databases consist of sets of data. Tables are sets of structured data. Each “row” contains entries for one or more fields; each field is a specific type of data such as a string, integer, or date. One of the uses of databases is to connect tables based on shared fields. For an example see Appendix A.

🔗

The most common method for accessing databases, especially large, enterprise databases, is Structured Query Language (SQL pronounced sequel). Below is a very simple illustration of the format of SQL query statements.

🔗

Table 1.4.1. SQL query example

🔗

`SELECT [ list of fields ]` 🔗	each field from each table desired 🔗
`FROM [ 1st table ]` 🔗
`(INNER/LEFT/RIGHT/OUTER)` `JOIN [ 2nd table ]` 🔗	how to match rows of tables 🔗
`WHERE <condition>` 🔗	limits data used 🔗

An example using the sample database is below that shows the major and department in which they work for every person that is both a student and an employee.

🔗

SELECT Student.[ID], Student.[Major 1], Employee.Department, 
FROM Student 
INNER JOIN Employee ON Student.ID = Employee.ID;

🔗

Note that the JOIN statements determine what set operation to perform on the tables. For example a left join takes all rows from the 1st table regardless of whether there is a matching row in the second table. For a left join from student to employee this would list all students whether or not they were also an employee. The employee fields would be null. An inner join requires that there be a matching row in both tables. For the example above this is all people who are both students and employees. An outer join uses all rows from both tables regardless of whether there is a match. This would include all people; student fields would be null for employees who never were a student and vice versa.

🔗

Example 1.4.2. SQL Left Join in Set Notation.

SELECT [fields]
  FROM Table A LEFT JOIN Table B
  ON A.Key=B.Key

🔗

Set Notation: \(A\)

🔗

Example 1.4.3. SQL Inner Join in Set Notation.

SELECT [fields]
  FROM Table A INNER JOIN Table B
  ON A.Key=B.Key

🔗

Set Notation: \(A \cap B\)

🔗

Checkpoint 1.4.4.

SELECT [fields]
  FROM Table A RIGHT JOIN Table B
  ON A.Key=B.Key

🔗

Set Notation:

🔗

Checkpoint 1.4.5.

SELECT [fields]
  FROM Table A LEFT JOIN Table B
  ON A.Key=B.Key
  WHERE B.Key IS NULL

🔗

Set Notation:

🔗

Checkpoint 1.4.6.

SELECT [fields]
  FROM Table A RIGHT JOIN Table B
  ON A.Key=B.Key
  WHERE A.Key IS NULL

🔗

Set Notation:

🔗

Checkpoint 1.4.7.

SELECT [fields]
  FROM Table A FULL OUTER JOIN Table B
  ON A.Key=B.Key

🔗

Set Notation:

🔗

Checkpoint 1.4.8.

SELECT [fields]
  FROM Table A FULL OUTER JOIN Table B
  ON A.Key=B.Key
  WHERE A.Key IS NULL or B.Key IS NULL

🔗

Set Notation:

🔗

Prev Top Next