MIS Final

Describe two properties that each candidate key must satisfy.

1. A unique identifier. One of the candidate keys will become the primary key 2. Each non-key field is functionally dependent on every candidate key.

determinant vs candidate key

A candidate key is always a determinant, whereas a determinant may or may not be a candidate key. A candidate key is a determinant that uniquely identifies the remaining (nonkey) attributes in a relation.

Recursive foreign key

A foreign key in a relation that references the primary key values of the same relation.

Transitive dependency

A functional dependency between the primary key and one or more nonkey attributes that are dependent on the primary key via another nonkey attribute.

Partial functional dependency

A functional dependency in which one or more nonkey attributes are functionally dependent on part (but not all) of the primary key

Relation

A named, two-dimensional table of data. Table

Composite key

A primary key that consists of more than one attribute.

Enterprise key

A primary key whose value is unique across all relations.

Normal form

A state of a relation that requires that certain rules regarding relationships between attributes (or functional dependencies) are satisfied.

What is a well-structured relation? Why are well-structured relations important in logical database design?

A well-structured relation is a relation that contains minimal redundancy and allows users to insert, modify, and delete the rows in a table without errors or inconsistencies. Well-structured relations are important because they promote database integrity (mechanisms for implementing business rules that maintain integrity of manipulated data). They provide full credibility to the table.

• Explain how each of the following types of integrity constraints o entity integrity o referential integrity o domain integrity

Entity Integrity -No primary key attribute may be null. All primary key fields MUST contain data values. Referential Integrity -Rules that maintain consistency between the rows of two related tables. Domain Integrity -ensures that all data in a field contains valid values. -Allowable values for an attribute

A relation that has no partial functional dependencies is in _____ normal form.

Second

Determinant

The attribute on the left side of the arrow in a functional dependency.

Normalization

The process of decomposing relations with anomalies to produce smaller, well-structured relations.

Describe the primary way in which relationships in an E-R diagram are expressed in a corresponding relational data model.

Transforming E-R diagrams into relational data model is relatively an easy process which is defined using a set of rules. The following are the steps to be followed to convert E-R diagrams into its corresponding relational data model

Normal Form vs Normalization

With normal form the set of relations are divided to reduce the redundancies With normalization the database tables are divided to reduce the redundancies. ???

A relation that has no multivalued attributes is in _____ normal form.

first

composite key vs recursive foreign key

recursive foreign key - foreign key in a relation that references the primary key values of the same relation. composite key - A primary key that consists of more than one attribute.

Perform normalization from 0 to 3rd normal form

see assignment 4?

• Describe how the following components of an E-R diagram are transformed into relations: o regular entity type o relationship (1:M) o relationship (M:N) o relationship (super type/subtype) o multivalued attribute o weak entity o composite attribute

see notes / study guide

A relation that has no transitive dependencies is in _____ normal form.

third

List three conditions that you can apply to determine whether a relation that is in first normal form is also in second normal form.

1. In a relation, the only one primary key attribute is included. Thus, the relation does not contain partial dependency 2. The value of all non-primary key attributes should be dependent on the primary key attribute. Thus, the relation does not contain functional dependency 3. The non-primary key attributes are functionally dependent on primary key attributes in a relation

Describe three types of anomalies that can arise in a table and the negative consequences of each.

1. Insertion-if new product is ordered for order 1007 of existing customer, customer data must be re-entered, causing duplication 2. Deletion-if we delete the Dining Table from Order 1006, we lose information concerning this item's finish and price 3. Update-changing the price of product ID 4 requires update in multiple records

Summarize six important properties of relations

1. It must have a unique name. 2. Every attribute value must be atomic (not multivalued, not composite). 3. Every row must be unique (can't have two rows with exactly the same values for all their fields). 4. Attributes (columns) in tables must have unique names. 5. The order of the columns must be irrelevant. 6. The order of the rows must be irrelevant.

What is the primary purpose of normalization?

1. Minimize data redundancy, thereby avoiding anomalies and conserving storage space. 2. Simplify the enforcement of referential integrity constraints. 3. Make it easier to maintain data (insert, update, and delete). 4. Provide a better design that is an improved representation of the real world and a stronger basis for future growth.

Candidate Key vs Primary Key

A candidate key is an attribute, or combination of attributes, that uniquely identifies a row in a relation. A primary key is an attribute or a combination of attributes that uniquely identifies each row in a relation. One of the candidate keys will become the primary key Primary key is a minimal super key. So there is one and only one primary key in a relation. While in a relation there can be more than one candidate key.

Partial Dependency vs Transitive Dependency

Partial - In a table, if one or more non-primary key attributes is partially dependent on the primary key attribute, it is known as partial functional dependency Transitive - If any non-primary key attribute is functionally depending on any other non-primary key attribute, then it is termed as transitive dependency

foreign key vs primary key

Primary keys are unique identifiers of the relation. Examples include employee numbers, social security numbers, etc. This guarantees that all rows are unique. Foreign keys are identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship). foreign key is an attribute in a relation that serves as the primary key of another relation in the same database.

Describe the primary differences between the conceptual and logical data models.

The main difference between conceptual and logical data model is that conceptual data model represents entities and their relationships, while logical data model provides more details including attributes, primary and foreign keys in addition to entities and the relationships. Logical database design is the process of transforming the conceptual data model into a logical data model

Functional dependency

The value of one attribute (the determinant) determines the value of another attribute A constraint between two attributes in which the value of one attribute is determined by the value of another attribute

Be able to identify the what normal form the relation is in?

know differences