Data Mgmt-MIS 372 Ch3 Qs

Q3.1 Which conditions must hold in order for a table to be a relation?

1. Each column must have a name. Within one table, each column name must be unique. 2. Within one table, each row must be unique. 3. All values in each column must be from the same (predefined) domain. 4. Within each row, each value in each column must be single valued (one value from a predefined domain, within each row in each column). Multiple values of the content represented by the column are not allowed in any rows of the table.

Q3.18 How is a 1:1 unary relationship mapped into a relational schema?

1:1 unary relationships are mapped in the same way as 1:M unary relationships

Q3.2 What is a primary key?

A column (or a set of columns) in a relation whose value is unique for each row; in case there are multiple candidates, a designer chooses one of the candidates to be the primary key.

Q3.8 What is a foreign key?

A foreign key is a column in a relation that refers to a primary key column in another (referred) relation.

Q3.12 Give a definition of the referential integrity constraint.

A relational database rule stating that in each row of a relation containing a foreign key, the value of the foreign key either matches one of the values in the primary key column of the referred relation or the value of the foreign key is null

Q3.22 What are user-defined constraints?

Database constraints that are added by the database designer

Q3.15 How is a derived attribute mapped into a relational schema?

Derived attributes are not mapped as a part of the relational schema

Q3.3 How is a regular entity with regular attributes mapped into a relation?

Each regular entity becomes a relation, and each regular attribute of a regular entity becomes a column of the newly created relation.

Q3.4 How is a composite attribute mapped into a relation?

If an entity contains composite attributes, each component of a composite attribute is mapped as a column of a relation, while the composite attribute itself does not appear in the mapped relation.

Q3.5 How is a unique composite attribute mapped into a relation?

If an entity contains unique composite attributes, each component of a composite attribute is mapped as a column of a relation, underlined and bolded as primary keys, while the composite attribute itself does not appear in the mapped relation.

Q3.17 How is an M:N unary relationship mapped into a relational schema?

In addition to the relation representing the entity involved in a unary M:N relationship, another relation is created to represent the M:N relationship itself. This new relation has two foreign keys, both of them corresponding to the primary key of the relation representing the entity involved in the unary M:N relationship. Each of the foreign keys is used as a part of the composite primary key of the new relation.

Q3.10 How is an M:N relationship between two entities mapped into a relational schema?

In addition to the two relations representing the two entities involved in the M:N relationship, another relation is created to represent the M:N relationship itself. This new relation has two foreign keys, corresponding to the primary keys of the two relations representing the two entities involved in the M:N relationship. The two foreign keys form the composite primary key of the new relation.

Q3.20 How is a ternary relationship mapped into a relational schema?

Mapping a ternary relationship is similar to mapping an M:N relationship. A new relation is created with foreign keys from the participating relations forming a composite primary key of the new relation.

Q3.24 What is a designer-created primary key?

Primary key column, not called for by the original requirements, added to a table by the database designer

Q3.14 How is a multivalued attribute mapped into a relational schema?

The multivalued attribute is mapped as a separate relation that has a column representing the multivalued attribute and a foreign key column referring to the primary key of the relation resulting from the entity itself.

Q3.9 How is a 1:M relationship between two entities mapped into a relational schema?

The relation mapped from the entity on the M side of the 1:M relationship has a foreign key that corresponds to the primary key of the relation mapped from the 1 side of the 1:M relationship.

Q3.19 How is a weak entity mapped into a relational schema?

The resulting relation has a composite primary key that is composed of the partial identifier and the foreign key corresponding to the primary key of the owner entity

Q3.13 How are candidate keys mapped into a relation?

The term "candidate key" comes from the fact that one of the candidate keys has to be chosen by the database designer as the primary key during the entity-to-relation mapping process, while the other candidate keys are mapped as non-primary-key columns.

Q3.16 How is a 1:M unary relationship mapped into a relational schema?

Unary relationships in ER diagrams are mapped into a relational schema in the same way as binary relationships, using a foreign key in cases of 1:M and 1:1 relationships, and using a new relation with two foreign keys in the case of an M:N relationship

Q3.6 How is an optional attribute mapped into a relation?

When optional attributes are mapped into relations, the resulting optional columns are marked as (O)

Q3.7 Give a definition of the entity integrity constraint.

a relational database rule that states that all primary key columns must have values.

Q3.11 How is a 1:1 relationship between two entities mapped into a relational schema?

choose one of the mapped relations to have a foreign key referring to the primary key of the other mapped relation.

Q3.23 What are business rules?

rules that specify restrictions on resulting databases that are not a part of the standard notation for creating ER diagrams

Q3.25 What are the main reasons against creating a relational database model without first creating an ER model?

• ER modeling is better suited for visualization of the requirements. • Certain concepts can be visualized graphically only in ER diagrams. • Every attribute is mentioned only once in the ER diagram. • An ER model is a better communication and documentation device.

Q3.21 List implicit constraints in the relational database model.

• Each relation in a relational schema must have a different name. • Each relation must satisfy the following conditions:• Each column must have a different name.• Each row must be unique.• All values in each column must be from the same predefined domain (this restriction is also known as domain constraint).• In each row, each value in each column must be single valued.• The order of columns is irrelevant.• The order of rows is irrelevant. • Each relation must have a primary key, which is a column (or a set of columns) whose value is unique for each row (this restriction is also known as the primary key constraint). • No primary key column can have null values (entity integrity constraint). • In each row of a relation containing a foreign key, the value of the foreign key either matches one of the values in the primary key column of the referred relation or the value of the foreign key is null (referential integrity constraint).