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.Major1, 
    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 
    Student.ID, 
    Student.Major1, 
    Employee.Department 
  FROM Student
    LEFT JOIN Employee ON ( Student.ID = Employee.ID )

🔗

This selects all people who are students regardless of employement. As a result the Employee.Department may be null.

🔗

Set Notation: \(A\)

🔗

Example 1.4.3. SQL Inner Join in Set Notation.

SELECT 
    Student.ID, 
    Student.Major1, 
    Employee.Department 
  FROM Student
    INNER JOIN Employee ON ( Student.ID = Employee.ID )

🔗

This selects all people who are students and employees and excludes any who are only students or only employees.

🔗

Set Notation: \(A \cap B\)

🔗

Checkpoint 1.4.4.

SELECT 
    Employee.ID, 
    Student.Major1, 
    Employee.Department 
  FROM Student
    RIGHT JOIN Employee ON ( Student.ID = Employee.ID )

🔗

This selects all people who are employees regardless of student status. As a result the Student.Major1 may be null.

🔗

Set Notation:

🔗

Checkpoint 1.4.5.

SELECT 
    Student.ID, 
    Student.Major1, 
    Employee.Department 
  FROM Student
    LEFT JOIN Employee ON ( Student.ID = Employee.ID )
  WHERE Employee.ID IS NULL

🔗

This is which people:

🔗

Set Notation:

🔗

Checkpoint 1.4.6.

SELECT 
    Student.ID, 
    Student.Major1, 
    Employee.Department 
  FROM Student
    LEFT JOIN Employee ON ( Student.ID = Employee.ID )
  WHERE Student.ID IS NULL

🔗

This is which people:

🔗

Set Notation:

🔗

Checkpoint 1.4.7.

SELECT 
    Student.ID, 
    Student.Major1, 
    Employee.Department 
  FROM Student
    FULL OUTER JOIN Employee ON ( Student.ID = Employee.ID )

🔗

This is which people:

🔗

Set Notation:

🔗

Checkpoint 1.4.8.

SELECT 
    Student.ID, 
    Student.Major1, 
    Employee.Department 
  FROM Student
    FULL OUTER JOIN Employee ON ( Student.ID = Employee.ID )
  WHERE Student.ID IS NULL or Employee.ID IS NULL

🔗

This is which people:

🔗

Set Notation:

🔗

Prev Top Next