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