bcis lesson 10
What are the two goals of the normalization process?
(1) eliminating redundant data (e.g., storing the same data in more than one table) and (2) ensuring data dependencies make sense (e.g., only storing related data in a table).
_________ are simply restrictions on the information to be retrieved.
Criteria
_________ provide a tool for data input into the database.
Forms
Describe the process of a cascading update.
If the primary key for a record in the parent table changes, all corresponding records in the child tables will be modified.
What are the two main goals of a primary key?
It must contain a unique value for each row of data, and (2) it cannot contain a null value.
What is the advantage to creating a dropdown box with predefined state abbreviations for the user to enter a state when filling out a form?
It reduces user error as the user is forced to choose one of the predefined values in the list
__________ involves dividing the database into two or more tables and defining a relationship between them.
Normalization
__________ is a relationship where each record in the parent table has one or more related records in the child table. The one-to-many relationship is the most common type of relationship. A frequently used example is that of the Customer and Orders tables. The Customer table is the parent table and usually contains a CustomerID field, which is set as a primary key.
One-to-many
A __________ensures that only numbers are entered.
numeric check
Describe the process of a cascading delete.
orphaned record
A __________examines the data typed into two different fields to determine identical entrie
range check
A __________verifies that the entered data falls within a certain range.
range check
Explain a limitation of a flat file database model.
A flat file database model can only store information relating to one entity, such as a customer or an order. If you want to view a customer's order, you are forced to open two distinct files—customer and order.
Although data can be entered into a database table via datasheet view, what is the primary method of entering data into a database?
A form
Distinguish between a null value and a zero (0).
A null value is an empty value. A zero represents a value; however, a blank or nothing is a null value.
Explain the purpose of a primary key in a table.
A primary key (PK) is used to uniquely identify each record. The PK should be a field such as StudentID, CustomerID, or OrderID.
What will happen if you assign a number data type to store a person's last name?
An error will occur
Provide an example of a many-to-many relationship.
An example of a many-to-many relationship is the Students and Classes relationship—a student can take more than one class and more than one student usually takes a class.
What is the function of a table index?
An index is a data structure setup in a table that provides quick access to rows in a table based on the values in one or more columns (the index key). This allows for faster searching for finding values rather than scanning the entire table row by row.
Describe the concept of data integrity.
Data integrity refers to the validity of the data contained in a database. Data integrity can be compromised in many ways, including typing errors during data entry, hardware malfunctions, and data transmission errors.
__________ are associated with fields and describe the type of data a field will store.
Data types
What is data validation?
Data validation is a technique used in databases to ensure valid data are entered into the database
When is the primary key for a field typically assigned?
During the creation of a table
A table can be 2NF without achieving 1NF.
False (A table must first be 1NF)
When creating a field, you can set the field size to store a predefined number of characters, where would the field size be set?
Field properties
_________ are used to describe and store a piece of data, and __________ are composed of a group of related fields.
Fields; records
What will be the result of this query? Select FirstName, LastName From Students Where LastName = "Smith";
First and last name of all students who have the last name "Smith"
List the four rules a table must adhere to in order to be in 1NF.
For a database to be in first normal form (1NF), the following rules have to be met for each table in the database. There are no columns with repeated or similar data. Each data item cannot be broken down any further. For instance, the address field should not contain 623 East 68th St. Apt 3D. This data is not atomic, broken down to its lowest level. The address should be divided as follows: Address (623 East 68th St.), Apt Number (3D). Apt should have its own separate field. Each row is unique—that is, it has a primary key. Each field has a unique name.
_________ are questions that you ask about your data.
Queries
_________ is a database concept that states the table relationships must always be consistent, and any foreign key field must agree with the primary key that is referenced by the foreign key. Therefore, any primary key field changes must be applied to all foreign keys, or not at all.
Referential integrity
What is relationship cardinality?
Relationship cardinality refers to the number of entity instances involved in the relationship. For example: one CUSTOMER may place many CUSTOMER ORDERS just as many STUDENTS may sign up for many CLASSES, and one EMPLOYEE receives one PAYCHECK.
__________ are used to display data and allow you to show summary information such as subtotals, totals, and percentages for groups of records and organize your data in a polished, professional presentation.
Reports
_______ is a standard language for accessing and manipulating data in databases.
SQL
In an Access database, what is table design view used for?
Table design view is used to create the actual structure of all tables in the database.
How do you know what data type to assign?
This is most likely discovered during the requirements gathering process by the business analyst. Then it is up to the BA or data modeler to use the correct data type.
An __________ is a record that has no parent relation.
When rows in the parent table are deleted, the matching foreign key columns in the child table are also deleted.
How do you solve the problem of a many-to-many relationship?
You solve the many-to-many relationship problem by creating an intermediary table (sometimes referred to as a junction, joining, or linking table) that contains the primary keys from each of the two tables, thus creating a one-to-many relationship between each table and the intermediary table.
A(n) __________ ensures that only alphabetic data (the letters of the alphabet) are entered in a field.
alphabetic check
Fields are sometimes referred to as _____________.
attributes
A __________ determines whether a required field has been left empty. For example, when filling out a form online, the last name cannot be blank.
completeness check
A __________ is a set of software programs that allows users to create, edit, and update data in database files; and store and retrieve data from those database files.
database management system (DBMS)
A __________ relationship exists between a pair of tables if a single record in the first table can be related to one or more records in the second table, and a single record in the second table can be related to one or more records in the first table.
many-to-many
_________ is a relationship where each record in the first table has only one related record in the second table.
many-to-many
A __________ is a query that retrieves information from more than one related table.
multi-table query
A __________ stores data in tables that are related to other tables within the database.
relational database
Tables are sometimes referred to as _____________. Explain a limitation of a flat file database model.
relations
A __________ is a way of formally defining how two tables are related to each other by telling the database on which fields they are joined.
relationship
Rows are sometimes referred to as __________.
tuples