Database management test 2

A requirement to begin designing physical files and databases is:

*A. normalized relations.*

Discuss why it is important to remove multivalued attributes from a relation.

A multivalued attribute is one that can take on more than one value. According to the definition of a relation, there can be no multivalued attributes. The reason for this will be seen later when the schema is normalized. A multivalued attribute can be resolved by creating a separate instance for each value and repeating the data from the non-multivalued attributes.

Explain what primary, secondary and unique key indexes are.

A primary key index is automatically generated when a table is created (provided that a primary key is specified). The primary key index identifies uniquely each row of the table. Thus, there is one entry in the primary key index for each row. A unique key index is similar, except that we have to define this with a separate create index command. A secondary key is nonunique. So, for example, we might create a secondary index on state. Since many records would have the same state, this is non-unique.

Discuss the various relational keys

A primary key uniquely identifies each row of a relation (or table). It can be either a single column or a composite of two or more columns, which is called a composite key. A foreign key allows us to represent the relationship between two tables. A foreign key in one table is generally a reference to a primary key in another table.

problems are encountered when removing data with transitive dependencies.

Deletion

Discuss the two major occasions when you benefit from using normalization.

During logical database design, normalization is used as a check and balance to make sure that your E-R diagram is correct as well as your relational schema before doing physical design. Another occasion when you benefit is reverse-engineering an older system, since many of the tables and user views are redundant.

Discuss how you would map a regular entity to a relation.

Each regular entity is transformed into a relation. There are a couple of things that need to be done with some special types of attributes. Composite attributes are broken down into their individual components. Multivalued attributes are broken down into separate relations. For example, if there was a multivalued skill attribute, this would become a skill relation. Also, we ignore derived attributes.

A method for handling missing data is to:

track missing data with special reports.

Database access frequencies are estimated from:

transaction volumes.

A relation that contains minimal redundancy and allows easy use is considered to be:

well structured

Horizontal partitioning makes sense:

when different categories of a table's rows are processed separately.

Which type of file is easiest to update?

Hashed

A relation that contains no multivalued attributes and has nonkey attributes solely dependent on the primary key but contains transitive dependencies is in which normal form?

second

One field or combination of fields for which more than one record may have the same combination of values is called a(n):

secondary key

A key decision in the physical design process is:

selecting structures.

Which type of file is most efficient with storage space?

sequential

Data is represented in the form of:

tables

Which of the following is an objective of selecting a data type?

Improve data integrity

A domain definition consists of all of the following components EXCEPT:

Integrity constraints

Explain how one goes about choosing data types.

Selecting a datatype involves four objectives that have different levels of importance for different applications. One must represent all possible values, improve data integrity, support all data manipulations and minimize storage space.

Data Normalization

Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data The process of decomposing relations with anomalies to produce smaller, well-structured relations

Understanding the steps involved in transforming EER diagrams into relations is important because:

you must be able to check the output of a CASE tool.

partitioning distributes the columns of a table into several separate physical records.

Vertical

An appropriate datatype for adding a sound clip would be:

blob

In the figure below, Customer_ID in the CUSTOMER Table is which type of key?

candidate

Modification Anomaly

changing data in a row forces changes to other rows because of duplication

An appropriate datatype for one wanting a fixed-length type for last name would include:

char

primary key that consists of more than one attribute is called a:

composite key

An advantage of partitioning is:

efficiency

A primary key whose value is unique across all relations is called a(n):

enterprise key

The ________ states that no primary key attribute may be null.

entity integrity rule

disadvantage of partitioning is:

extra space and update time.

The smallest unit of application data recognized by system software is a:

field

A(n) ________ is a technique for physically arranging the records of a file on secondary storage devices.

file organization

When all multivalued attributes have been removed from a relation, it is said to be in:

first normal form

Distributing the rows of data into separate files is called:

horizontal partitioning

An index on columns from two or more tables that come from the same domain of values is called a:

join index.

A rule that states that each foreign key value must match a primary key value in the other relation is called the:

referential integrity constraint

two-dimensional table of data sometimes is called a:

relation

Explain what a candidate key is and how it might be used.

A candidate key is an attribute or a combination of attributes that uniquely identifies a row in a relation. A candidate key must be nonredundant and must also uniquely identify each row. When we look at functional dependencies and candidate keys, we can always say that a determinant is always a candidate key.

Referential Integrity

A set of rules that Access enforces to maintain consistency between related tables when you update data in a database.

Discuss when it is best to create a surrogate key for an associative entity.

A surrogate key should be created when any of the following conditions hold: there is a composite primary key, the natural primary key is inefficient (such as a long name) or the natural primary key is recycled. When you have an associative entity, it is often best to use a surrogate key when you have more than two relations that the associative entity is related to, since the primary key would be a composite of the primary keys of all entities related to the associative entity.

What is an anomaly, and what are the three types of anomalies?

An anomaly is an error or inconsistency that may result when a user attempts to update a table that contains redundant data. There are three types of anomalies. An insert anomaly occurs when a user attempts to enter new information for part of a table. For example, if we stored customer address with an order. A deletion anomaly occurs when the user wants to delete part of a record but also has to delete related data. A modification anomaly occurs when we have to change the same data in multiple places.

The storage format for each attribute from the logical data model is chosen to maximize ________ and minimize storage space.

Data integrity

a component of the relational data model included to specify business rules to maintain the integrity of data when they are manipulated.

Data integrity

Discuss the critical decisions that must be made during physical database design.

First, one must choose the storage format (or data types) of each attribute. These should be chosen to maximize data integrity and minimize storage space. Next, one must decide how to group the attributes from the logical model into tables. One must also decide upon the file organization to be used to store records, and also think about protecting data and how to recover if errors are found. Finally, one has to think about indexing and query optimization. What types of indexes will be required for the most efficient retrieval? How can queries be optimized?

Discuss how data integrity can be controlled.

In many database management systems, data integrity controls can be built into the field. The data type is one integrity control. Another integrity control is default values, where one specifies the value that the field will take if no data has been entered. Range control is another data integrity constraint which indicates the upper and lower bounds for the field. Null value control allows one to specify whether null values are allowed or not in the field. Finally, referential integrity ensures that any value entered into a foreign key must have a corresponding value in the primary key of the related table. This ensures that there are no misplaced values in the foreign key.

All of the following are common denormalization opportunities EXCEPT:

One-to-many relationship

Discuss the advantages and disadvantages of partitioning.

Partitioning data has several advantages. First, queries are more efficient since data queried together are stored close to one another. Also, each partition of data can be stored in such a way as to optimize its own use. Partitioned data is more secure, since it is much easier to isolate data and make it available only to a group of users. Since smaller files take less time to backup and recover, it is easier to keep a partitioned database up and running. Also, the effects of damage are isolated, since other files (partitions) may not be affected by one damaged file. It is also easier to load balance, since the smaller files can be spread across multiple disks. Some disadvantages include inconsistent access speed. Different partitions may have different access times, depending upon physical storage of the file. Also, partitioning adds complexity for the programmer. Although to the user, partitioning appears transparent, this is often not the case for a programmer. Finally, data may be replicated across multiple partitions, which will require more storage space and possibly more time for updates.

An integrity control supported by a DBMS is:

Range Control

Discuss the types of integrity constraints.

The three major types of integrity constraints are domain constraints, entity integrity and referential integrity. A domain is a set of attributes assigned to an attribute and consists of the domain name, data type, size and allowable values. The entity integrity rule is designed to ensure that every relation has a primary key and that the data values for the primary key are valid (in particular, not null). The referential integrity constraint is a rule that maintains consistency among the rows of two relations. If there is a foreign key in one relation, there must be a matching primary key in the other relation or the foreign key must be null.

Discuss three possible opportunities for denormalization.

There are several possible situations when denormalization makes sense. When we have two entities with a one-to-one relationship and high access frequency between them, then it makes sense to combine them into one entity. If one entity is optional, then there will be fields with null values in the combined entity. Another scenario is when you have an associative entity with nonkey attributes. If there is a high frequency of join operations between the entities, it might be more efficient to combine the associative entity with one of the other entities. The third case is reference data. If we have a 1:M relationship and the entity on the one side does not participate in any other relationships, then it might be wise to merge the entities.

Explain how subtype/supertype relationships are converted to relations.

When we have a supertype/subtype relationship, we need to assign one entity for the supertype and one for each subtype. The supertype has all attributes common to all subtypes as well as a primary key. Each subtype relation has the primary key of the supertype as well as any attributes that are specific to that subtype. Finally, we assign one or more attributes to the supertype to function as subtype discriminators.

Insertion Anomaly

adding new rows forces user to create duplicate data

When a regular entity type contains a multivalued attribute, one must:

create two new relations, one containing the multivalued attribute.

A detailed coding scheme recognized by system software for representing organizational data is called a(n):

data type

The value a field will assume unless the user enters an explicit value for an instance of that field is called a:

default value

Deletion Anomaly

deleting rows may cause a loss of data that would be needed for other future rows

Designing physical files requires ________ of where and when data are used in various ways.

descriptions

All of the following are the main goals of normalization EXCEPT

maximize storage space.

All of the following are horizontal partitioning methods in Oracle EXCEPT:

multivalued partitioning.

The entity integrity rule states that:

no primary key attribute can be null

Which of the following are properties of relations?

no two rows in a relation are identical