Chapter 10

In SQL which of the following is an example of an instance?

-a snapshot of the actual content of a table at any given time. -a snapshot of the actual content of a database at any given time. -a snapshot of the actual content of a cell at any given time.

What are the stages to creating a database

1. Data analysis involves using Entity Relationship Modeling and Normalization. 2. Physical implementation of that design in a database system.

Multiple choice questions are good examples of what kind of question?

A closed question

What is atomicity?

A data value that cannot be further divided into smaller pieces. It means all the statements inside a transaction should either succeed or fail as a unit.

n SQL what is entity-relationship (ER) modeling?

A diagram that shows the entities, entity attributes and the relationship between those entities. Entities (tables) attributes (column definitions), and relationships between the entities (one to one, one to many, many to many)

What is declarative referential integrity?

A feature of RDBMS that creates a foreign key constraint on referenced values to maintain data integrity.

What is a foreign key constraint?

A foreign key constraint requires that a column contain only values from the primary key column.

Which of the following best describes the function of a form?

A form is used to gather input from users

What is the difference between a form like a web form and a normal form

A form is used to receive input from users. A normal form addresses data normalization

3rd Normal Form (3NF)

A relation that is in 1NF and 2NF, and in which no non-primary key attribute is transitively dependent on the primary key. That is, all non-key elements are fully dependent on the primary key

2nd Normal Form (2NF)

A relation that is in 1NF and every non-primary key attribute is fully dependent on the primary key. That is, all the incomplete dependencies have been removed. No repeating values in rows. Every non-key column depends on primary key.

Which of the following best describes the function of a report?

A report is used to summarize and display information

What is the function of a report?

A report is used to summarize and display information. SQL queries are often used to generate report data.

Which of the following is the best definition for normal forms?

A set of rules for removing anomalies and redundancies from database design

What does EER model represent

An EER model is a representation of the entities, or objects, of the database including the tables, views, and stored programs. An EER diagram is a visual representation of an EER model. As a result, when you modify the tables in the diagram, you also modify the model

Statement of Work (SoW)

An agreement between those providing the work and the client. It defines what's included in the projects, and keeps the project on schedule, and within budget.

Essay questions are good examples of what kind of question?

An open ended question

10 tips for writing a good survey

Begin with the end in mind. Write questions to your audience's level of understanding and avoid slang, jargon and trendy language. Avoid leading questions. Beware of double-barreled and double negative questions. It's OK to relax your grammar. Offer an 'out' for questions that do not apply. In multiple choice questions, cover all options without overlapping. Response choices should be balanced. Avoid too many response choices. Ask for demographics last.

Cardinality data type for a column

Cardinality is the combination of the minimum and maximum cardinality, respectively the least number of things in a column or relationship and the most number of things in the same column or relationship. Cardinality is represented by numbers for the minimum and maximum cardinality separated by two dots.

What are three types of (SoW)

Design/Detail Level of Effort/Time and Materials/Unit Rate Performance-Based

Five types of data integrity

Domain constraints Entity integrity Column constraints User-defined integrity constraints Referential integrity

Inserting a row in the foreign key table is allowed even if there is no matching primary key in the related table.

False

Updating the value of a foreign key is permissable even if the new foreign key value doesn't have a matching primary key value in the related table

False. You cannot change a foreign key to a value that does not exist in the related table.

EER Model

MySQL Workbench allows you to create an enhanced entity-relationship model (EER model). This type of model extends the original entity-relationship model (ER model).

Commonly used SQL constraints

NOT NULL - Ensures that a column cannot have a NULL value UNIQUE - Ensures that all values in a column are different PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table FOREIGN KEY - Uniquely identifies a row/record in another table CHECK - Ensures that all values in a column satisfies a specific condition DEFAULT - Sets a default value for a column when no value is specified INDEX - Used to create and retrieve data from the database very quickly

What is data normalization?

Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like insertion, update and deletion anomalies. Normalization is used for mainly two purpose, - eliminating redundant (useless) data - ensuring data dependencies make sense

Interviews are good for asking what kind of questions?

Open ended questions

Referential integrity refers to the relationship between primary and foreign keys. Which statement below concerning referential integrity is true?

Records with a foreign key can be deleted even if they are associated with a primary key in another table

SQL constraints

SQL constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted by the constraint. Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement). CREATE TABLE table_name ( column_name1 data_type(size) constraint_name, column_name2 data_type(size) constraint_name, column_name3 data_type(size) constraint_name, .... );

The steps for Normalization are:

Step 1: Select the data source and convert into an unnormalised table (UNF) Step 2: Transform the unnormalised data into first normal form (1NF) Step 3: Transform data in first normal form (1NF) into second normal form (2NF) Step 4: Transform data in second normal form (2NF) into third normal form (3NF) Occasionally, the data may still be subject to anomalies in third normal form. In this case, we may have to perform further transformations. Transform third normal form to Boyce-Codd normal form (BCNF) Transform Boyce-Codd normal form to fourth normal form (4NF) Transform fourth normal form to fifth normal form (5NF)

1st Normal Form (1NF)

Tabular format in which all of the key attributes are defined; some tables contain partial dependencies; there are no repeating groups in the table; in other words, each row/column intersection (cell) contains one and only one value, not a set of values; all attributes are dependent on the primary key. No repeating values in a column. All values in a column are scalar (single value)

You cannot update the primary key if the foreign key table contains one or more rows related to the row that's changed

True

Anomalies (SQL)

Update Anomalies Delete Anomalies Insert Anomalies

Which if the following is NOT one of the three criteria for creating indexes.

When the column contains a small number of distinct values.

To be in the first normal form, each cell in a table must contain

a single, scalar value

Which of the following is an example of an entity?

a table, it represents a recognizable concept like a person, place, thing or event

When a row in a table with a foreign key references a primary key that no longer exists, it is called _________.

an orphan row

What type of question is multiple choice?

closed.

Which of the following is a characteristic of unnormalized data structures?

data redundancy. Unnormalized data can contain redundant data.

To be in the third normal form,

each non-key column must depend only on the primary key

What kind of question is an interview?

open ended

To apply the second normal form, you move columns that don't depend on the entire primary key to another table and establish a relationship between the two tables. This

reduces redundancy and makes maintenance easier