Database Views and Access SQL

Views

• Provide access to a subset of fields and records in one or more tables
• Provide a light blanket of security by limiting access to specific data
• Automatically show up-to-date information when executed
• Do not duplicate copy of the selected data

Index

• Primary purpose is to improve the speed by which data is searched and presented
• Not for documenting relationships, identifying integrity issues, or adding event trigger functionality

Null Value

• Represents an intentional nothing, not a zero-length string, space, or tab character
• Not the string "null"

Referential Integrity

• Enforcement prevents deleting a record in the parent table that has a matching record in a child table
• Enforcement prevents changing a primary key field value that has matching records in a related table
• Does not prevent incorrect entry into a primary key field or value entry into a foreign key field that doesn't have a match in the table that contains the primary key field

One-to-Many Relationship

• Table on the "one" side is also called the parent or left table
• Table on the "one" side may or may not have related records in the "many" table
• Table on the "many" side may have orphan records, especially if referential integrity is not enforced
• Table on the "one" side may not have orphan records

SQL JOINs

• RIGHT JOINS are helpful to find orphan records
• Default join type when two tables are joined in an Access query is INNER

Access Features

• Database Documenter is used to create a report on the metadata about tables and queries
• Data macros are not used to create a report on metadata
• Stored procedures differ from triggers in that they are usually run intentionally by users, whereas triggers are generally run automatically by processes

Field Properties

• Validation Rule is an Access field property that might be used to set data integrity rules on a single field

Programming Languages

• The "P" in modern software solution stacks such as LAMP, LAPP, and WAMP stacks stands for PHP, Python, or Perl, but not Pascal

Database Catalog

• Metadata found in a database catalog includes table and view names, relationship descriptions, and index definitions
• Values in a primary key field are not metadata

Data Consistency

• Means giving each user a consistent view of the data, including changes
• Can be achieved using properties such as the Validation Rule property to improve data entry accuracy

Data Recovery

• Compact and repair feature potentially helps recover data in an Access database
• Journaling and incremental backups are not features of Access that help with data recovery

SQL GRANT Statement

• The correct syntax to give user cyclone1 the ability to select, insert, update, or delete records from the Students table is GRANT ALL on STUDENTS to cyclone1

This quiz tests your knowledge of database views, including their functionality and limitations, as well as the reasons why Access SQL does not support the CREATE VIEW statement.