Lesson 10

This module examined table joins, user views, and explained how table joins are formed from existing tables. This module also discussed how joins can be used to retrieve information, and described the different types of joins. This module also discussed user permissions, defined user views, described how views are stored, and listed the benefits of views.

Having completed this module, you should be able to:

1. Explain how to form table joins
2. Describe the types of joins
3. Define user views
4. Explain how views are stored in an RDBMS
5. List the benefits of creating user views
6. Describe permissions
7. Determine which permissions individual users require

This module introduced you to the following terms:

1. Cartesian product: A type of table join: the Cartesian product of two tables consists of the combination of every record in one table with every record in another table.
2. equi-join: Also referred to as a natural join, an equi-join is a type of table join. The common field contains equivalent values.
3. Inner Join: A link between tables in a database that displays only the rows with a match (true value of the join condition) for both join tables. An inner join does not guarantee the return of every row of data that you expect will be returned.
4. join: An operation that links table records based on data in common fields.
5. nulls: Unknown values in a column or field
6. outer join: A link between tables in a database. When two tables are combined by an outer join, any records from the base table with no matching records from the other table are included in the results and any columns where no values are available are filled with nulls.
7. same-table join: A type of inner join, so named because it creates two copies of a table and then joins records from the tables where the tables have equivalent values in designated fields.
8. View: A database object that enables you to define the columns and rows that a specific user can see. A view can also serve as a tool for enforcing security within your database.
9. virtual table: A table stored in the computer’s memory. Virtual tables themselves are not stored in the database; rather, the definition of the view is stored and given a name. Users call up that name, and the view is created (from base tables) on the fly. When a user closes the view, it disappears from memory, only to be recreated the next time its name is invoked.
10. natural join: Also referred to as a equi-join, an natural join is a type of table join. The common field contains equivalent values.
11. permissions: Permissions are database tasks that a user or group of users is allowed to carry out. There are several types of permissions the administrator can grant: ·
    1. Select: The user can retrieve data from a table or view..·
    2. Insert: The user can create new records in a table or view. The user may also be limited to inserting values into particular fields..·
    3. Update: The user can modify existing values in a table or view, again for an entire table or specific columns..·
    4. Delete: The user can remove existing records from a table or view..·
    5. References: The user can use a field in an existing table or view a field as a foreign key in a table that the user creates. This permission may be limited to particular fields.
    6. All: The user has all of the permissions listed above.
12. requirements analysis: The stage in the database design cycle when designers find out everything they can about the data the client needs to store in the database and the conditions under which that data needs to be accessed.
13. Structured Query Language (SQL): The standard language used by all relational databases, including Microsoft SQL Server

Before you move on to the next module, click the Quiz link below to check your understanding of table joins and user views.
Table Joins - Quiz
The next module discusses considerations relating to the physical design of the database.