chap 03-04

What restriction does the "referential integrity constraint" put on the values in a foreign‐key field?

Defining the parent key is called entity integrity. A referential constraint is the rule that the nonnull values of a foreign key are valid only if they also appear as values of a parent key.

Complete the following sentence using the words "table", "relation", or their plurals: "All ___ are ____, but not all __ are ___."

All RELATIONS are TABLES, but not all TABLEs are RELATIONS

Which normal form is used to eliminate multivalued dependences?

4nf

Which normal form(s) is(are) used to deal with data constraints and "oddities"?

5nf/ dk/ nf

What should be done to resolve multivalued dependencies in a relation?

Anomalies from multivalued dependencies are very problematic. • Always place the columns of a multivalued dependency into a separate table (4NF)

What other names are there for a database table column?

Attribute, field, characteristic

What is Datawarehouse?

Datawarehouse is a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining and online processing. Warehouse data have a subset of data called Data Marts.

What are the alternate terms for a row in a database relation?

tuple, record, instance

Can you have a NULL value in a foreign key field?

yes

Can you have a table that doesn't have a foreign key?

yes

Is a composite primary key necessarily a determinant?

yes, composite primary jeys can determine some values

Is a candidate key necessarily a determinant?

yes, it determines all other columns in a relation.

In functional dependencies, what does the union rule state?

• If A B and A C, then A (B, C).

What is a "composite determinant"?

•A determinant of a functional dependency that consists of more than one attribute

A partial dependency is when ___________________________.

•A relation is in 2NF if, and only if, it is in 1NF and all non-key attributes are determined by the entire primary key.

A transitive dependency is when ________________

•A relation is in 3NF if, and only if, it is in 2NF and there are no non-key attributes determined by another non-key attribute. This is also known as a Transitive Dependency.

A relation is in _______ if and only if it is in 3NF and every determinant is also a candidate key.

BCNF

In normalization theory, which "normal forms" address data table anomalies caused by functional dependencies?

BCNF

What are the advantages of normalizing data tables for a database?

1) A smaller database can be maintained as normalization eliminates the duplicate data. Overall size of the database is reduced as a result. 2) Better performance is ensured which can be linked to the above point. As databases become lesser in size, the passes through the data becomes faster and shorter thereby improving response time and speed. 3) Narrower tables are possible as normalized tables will be fine-tuned and will have lesser columns which allows for more data records per page. 4) Fewer indexes per table ensures faster maintenance tasks (index rebuilds). 5) Also realizes the option of joining only the tables that are needed.

What are the first four "normal forms" in order, from MOST restrictive to LEAST restrictive?

1nf, 2nf

What is a "determinant" in a functional dependancy?

A determinant in a database table is any attribute that you can use to determine the values assigned to other attribute (s) in the same row. The functional dependency is a relationship that exists between two attributes. It typically exists between the primary key and non-key attribute within a table. The left side of FD is known as a determinant, the right side of the production is known as a dependent.

When designing a database from existing data, what issues do general purporse or "remarks" columns present?

A general-purpose remarks column is a column with a name such as: - Remarks - Comments - Notes • It often contains important data stored in an inconsistent, verbal, and verbose way - A typical use is to store data on a customer's interests • Such a column may: - Be used inconsistently - Hold multiple data item

What is the difference between a foreign key and a primary key?

A primary key is used to ensure that data in the specific column is unique. A column cannot have NULL values. It is either an existing table column or a column that is specifically generated by the database according to a defined sequence. A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It is a column (or columns) that references a column (most often the primary key) of another table.

What does it mean for one or more attributes in a table to be "functionally dependent" on another attribute or set of attributes?

A relationship between two attributes

What is the difference between a surrogate key and a primary key?

A surrogate key is an artificially generated key. They're useful when your records essentially have no natural key (such as a Person table, since it's possible for two people born on the same date to have the same name, or records in a log, since it's possible for two events to happen such they

When or why might a business choose not to use BCNF normalization of a relation?

BCNF is used to control anomalies from functional dependencies. • There are times when BCNF is not desirable. • The classic example is ZIP codes: - ZIP codes almost never change. - Any anomalies are likely to be caught by normal business practices* - Not having to use SQL to join data in two tables will speed up application processing

What is true about a database table that is in 1NF and that has a single‐attribute primary key?

Because a partial dependency can exist only if a table's primary key is composed of several attributes, if a table in 1NF has a single-attribute primary key, then the table is automatically in 2NF.

Explain the multivalue, multicolumn problem in database design, the two choices to deal with it, and the rationale behind each choice.

Data duplication in foreign keys will not cause inconsistencies because referential integrity constraints prohibit them.

What difficulty does an "update anomaly" cause?

Data intgrity problems

What happens with a "deletion anomaly"?

If a tuple is deleted or updated from referenced relation and referenced attribute value is used by referencing attribute in referencing relation, it will not allow deleting the tuple from referenced relation.

What problem do missing values in data present when designing a database from existing data?

Null values are ambiguous: - May indicate that a value is inappropriate ▪ DateOfLastChildbirth is inappropriate for a male - May indicate that a value is appropriate but unknown ▪ DateOfLastChildbirth is appropriate for a female, but may be unknown - May indicate that a value is appropriate and known, but has never been entere

In the example on page 153, what does it mean to write "ObjectColor ‐‐> (Weight, Shape)"?

Object color determines weight and shape. Weight and shape are functionally dependent on object color.

When or why might you have data tables across which there was considerable data duplication?

One cause of accidental data redundancy is the quality of coding within an organization's data management system, as it can lead to pathway malfunction. This means that information may not update appropriately across the data management system, which can interfere with algorithms and cause disparities in the database.

What problem do inconsistent values in data present when designing a database from existing data?

Inconsistent values occur when different users or different data sources use slightly different forms of the same data value

What does JSON stand for?

JavaScript Object Notation

What is the difference between a candidate key and a primary key?

Keys in Relational Model Both Primary Key and Candidate Key are the attributes that are used to access tuples from a table. These(Primary key and Candidate key) are also can be used to create a relationship between two tables. Primary Key: Primary Key is a set of attributes (or attribute) which uniquely identify the tuples in relation or table. The primary key is a minimal super key, so there is one and only one primary key in any relationship. For example,

Is a composite determinant necessarily a composite key?

No

What synonyms are commonly used for a database "table"?

Relation, file, entity

What conditions must be met for a table to be a "relation"?

Rows contain data about an entity. Columns contain data about attributes of the entities. All entries in a column are of the same kind. Each column has a unique name. Cells of the table hold a single value. The order of the columns is unimportant. The order of the rows is unimportant. No two rows may be identical

Why do we say that normalization reduces duplication rather than eliminates it?

The main reason is to eliminate repetition of data, so for example if you had a user with multiple addresses and you stored this information in a single table the user information would be duplicated along with each address entry. Normalisation would seperate the addresses into their own table and then link the two using keys.

What is required by the "domain integrity constraint"?

The requirement that all of the values in a column are of the same kind is know as the domain integrity constraint.

According to the "entity integrity constraint" what must be true about the primary key in a table?

The requirement that, in order to function properly, the primary key must have unique data values inserted into every row of the table. This is known as the entity integrity constraint.

What does the "decomposition rule" of functional dependencies state?

This rule states that if X determines Y and Z, then X determines Y and X determines Z separately If A (B, C), then A B and A C

What is an "updatable" database?

Updatable databases are typically the operational databases of a company, such as the online transaction processing (OLTP) system discussed for Cape Codd Outdoor Sports at the beginning of Chapter 2

What is necessary for a database table to be in "first normal form" (1NF)?

a single cell must not hold more than one value (atomicity) there must be a primary key for identification no duplicated rows or columns each column must have only one value for each row in the table

What is a "read‐only" database?

allows readers to read but not modify data.

What disadvantages are there to normalizing database data tables?

) More tables to join as by spreading out data into more tables, the need to join table's increases and the task becomes more tedious. The database becomes harder to realize as well. 2) Tables will contain codes rather than real data as the repeated data will be stored as lines of codes rather than the true data. Therefore, there is always a need to go to the lookup table. 3) Data model becomes extremely difficult to query against as the data model is optimized for applications, not for ad hoc querying. (Ad hoc query is a query that cannot be determined before the issuance of the query. It consists of an SQL that is constructed dynamically and is usually constructed by desktop friendly query tools.). Hence it is hard to model the database without knowing what the customer desires. 4) As the normal form type progresses, the performance becomes slower and slower. 5) Proper knowledge is required on the various normal forms to execute the normalization process efficiently. Careless use may lead to terrible design filled with major anomalies and data inconsistency. So it was all about Normalization in DBMS: Anomalies, Advantages and disadvantages. If you want to ask any questions then please comment below.

What problem is caused by an "insertion anomaly"?

An insertion anomaly occurs when specific details cannot be inserted into the database without the other details.

What qualification does your instructor emphasize regarding the choice to use BCNF normalization, or not?

BCNF is used to control anomalies from functional dependencies. • There are times when BCNF is not desirable. • The classic example is ZIP codes: - ZIP codes almost never change. - Any anomalies are likely to be caught by normal business practices* - Not having to use SQL to join data in two tables will speed up application processing

When or why might you choose to "denormalize" data?

For read-only databases, normalization is seldom an advantage. • Application processing speed is more important. • Denormalization is the joining of the data in normalized tables prior to storing the data. • The data is then stored in non-normalized tables

Why doesn't data duplication in foreign keys lead to inconsistencies in entity‐relationship database data?

it depends on what role this foreign key plays in your relation. if this foreign key is also a key attribute in your relation, then it can't be NULL if this foreign key is a normal attribute in your relation, then it can be NULL.

Can you have a primary key with null values?

nah son

What is "denormalization" of data?

• Denormalization is the joining of the data in normalized tables prior to storing the data.

For a database table to be in "second normal form" (2NF), what is required?

•A relation is in 2NF if, and only if, it is in 1NF and all non-key attributes are determined by the entire primary key. This is also know as a Partial Dependency

In normalization theory, what is required for a table to be in "third normal form" (3NF)?

•A relation is in 3NF if, and only if, it is in 2NF and there are no non-key attributes determined by another non-key attribute.