• Applications that control access to the database – who is allowed and what they are allowed.
• Interacts with users, the database and other applications.
• Frees the application programmer from low level detail of how data is stored.
• Examples: MS Access, SQLite, MySQL.

• Predicate – a testable property of a variable x that returns TRUE or FALSE. Can connect predicates with and, or and not. In maths ⋀, ⋁ ¬.

• Power set cardinality: #P(A) = 2^#A.

• Cartersian product: #(A x B) = #A * #B

• All data is represented in terms of tuples which are grouped into relations
• In practice we represent each relation as a two-dimensional table
• Sample schema: Student(id:integer, lastName:string, FirstName:string)

• Relational intension = Permanent, independent of time. Table schema.
• Relational extension = The set of tuples appearing in any relation at any given instance {t1, ... tm} or rows. Highly dynamic.

1. Each row represents an n-tuple
2. The ordering of rows is irrelevant
3. All rows are distinct
4. The ordering of attributes is significant
5. The significance of each column is conveyed by the name we give it 

One of all the candidate keys.

Not permitted to have NULL values in any attribute that defines a PK

A set of Foreign key attributes in a relation R that references the relation S must satisfy:

1. The attributes in FK must have the same domains (Data types) as the primary key of S
2. In the current state of R, the FK exists as a PK value in S or is NULL

• Selection σ
• Projection = ∏
• Theta join = |x|
• Cartesian product = x

Select operation  σ acts on a single relation as a filter keeping the tuples that satisfy the qualifying relation

• σ mark>70 (Transcript)  
• σ Course_code = COMP1008 ⋀ σ mark > 50 (Transcript)
• The select operator is unary and commutative – sequences can be applied in any order
• σ Course_code = COMP1008 (σ mark > 50 (Transcript)) = σ mark > 50 (σ Course_code = COMP1008 (Transcript))

Denoted as R x S; Pairs every tuple in R with every tuple in S. Not really used. More useful to filter the Cartesian product: 

σ id = student_id (Student X Transcript).

Cartesian product + filter

σ id = student_id (Student X Transcript).

This is called a thetha-join and is written as: Student ⋈ (id =StudentId) Transcript.

Could use set operations as well since relations are just sets.

For example:

σ Course_code = COMP1008 ⋀ mark > 50 (Transcript)  Is the same as:

σ Course_code = COMP1008 (Transcript) ∩ σ mark > 50 (Transcript)

For using set operations the following conditions must be met: The schemas have identical attributes. The order is identical.

• INNER JOIN - Fetch only the set of records that match in both tables (Intersection)
• OUTER JOIN = 3 types
• LEFT = Complete set of records from Table A with the matching from Table B where available, if no match the right side is null
• RIGHT - Complete set of records from table B with matching from Table A where available, if no match the right side is null
• FULL - Produces a set of records in table and Table with matching where available
• Cross Join = Cartesian product
• Limit - Limits number of entries to number specified
• Distinct - Removes duplicates
• Like - Similar to , e.g. Where Name like 'Ka%'
• In - Where countryCode in ('USA', 'FRA')
• Nested Selects - Aggregate operations not permitted, so this can be used instead
• Group by - Allows us to divide a table into logical groups and calculate the statistics for the group. Good when you have many similar thigns - Product type, user form country, city in country.
• Having - Used to filter the results of grouping. For example we might just want countries that have at least 100 cities

• Views are a single table that is derived by a query from another table
• The tables from which a view is built can be either other views or actual tables
• Once defined they can be used as tables in their own right
• Provide a level of isolate the user between the actual table data and the user's view of the data
• Used for covenience and security

CREATE VIEW UsersLondon AS

SELECT username FROM user WHERE city = ‘London’;

Depends on the DBMS.

In MySQL there has to be a one-to-one relationship between the rows in the view and the rows in the underlying table.

• If it contains:
• Aggregate functions
• They are distinct
• Grouped by
• Having
• Union or Union all
• Subquery in the selected list