LECTURE NOTES DBMS PART 2 (2 MARKS)

DATABASE MANAGEMENT SYSTEMS part 2

11. Discuss the alternative terminology that is used in the relational model.

Relations are also called tables, and sometimes by the older data processing term files. A row is known as a tuple in the relational model, but may also be referred to as a record. Finally, relational model attributes are known as table columns and sometimes as fields.

12. Why are functional dependencies not equations?

Equations deal with numerical relationships. A functional dependency deals with the existence

of a determinant relationship between attributes, regardless of whether or not there is a

numerical relationship between them. Thus, if we know that there is no hot water every

Wednesday, No-Hot-Water is functionally dependent on Wednesday. So, if we know it is

Wednesday, then we know we will have No-Hot-Water. This is a functional dependency, but

not an equation.

13. What is a foreign key, and what is it used for?

A foreign key is used to establish relationships among relations in the relational model.

Technically, a foreign key is a column (or columns) appearing in one relation that is (are) the

primary key of another table. Although there may be exceptions, the values in the foreign key

columns usually must correspond to values existing in the set of primary key values. This

correspondence requirement is created in a database using a referential integrity constraint on

the foreign key.

14. What are insertion and deletion anomalies?

A deletion anomaly occurs when, by deleting the facts about one entity, we inadvertently

delete facts about another entity; with one deletion, we lose facts about two entities. For

example, if we delete the tuple for Student 001289 from a table, we may lose not only the fact

that Student 001289 is in Pierce Hall, but also the fact that he has $200 left in his security

deposit. An insertion anomaly happens when we encounter the restriction that we cannot

insert a fact about one entity until we have an additional fact about another entity. For

example, we want to store the fact that the security deposit for Pierce Hall is $300, but we

cannot enter this data into the Student relation until a student registers for Pierce Hall.

15. What does it mean when we say that a relation is in Boyce-Codd Normal Form

(BCNF)?

A relation is in BCNF when every determinant in the relation is a candidate key. This means

that any possible primary key can determine all other attributes in the relation. Attributes may

not be determined by non-candidate key attributes or part of a composite candidate key. Thus

it is said "I swear to construct my tables so that all nonkey columns are dependent on the key,

the whole key and nothing but the key, so help me Codd!"

16. You have been given a set of tables with data and asked to create a new

database to store them. When you examine the data values in the tables, what are

you looking for?

(1) Multivalued dependencies, (2) Functional dependencies, (3) Candidate keys, (4) Primary

keys and (5) Foreign keys.

17. Why do normalized tables require more complex SQL when SQL statements are

used in application programs?

Tables that are normalized contain data that has been distributed among the tables, but which

may need to be recombined to answer queries from an application. To recombine the data, the

programmer will have to use subqueries and/or joins. These SQL structures are more complex

to write than a simple SELECT statement.

18. What is the multivalue, multicolumn problem? Include an example not used in

the text.

The multivalue, multicolumn problem occurs when a table is designed to include multiple

columns that hold variations of one type of attribute data. One example is where boat owners

have the names of their boats stored as BOAT_01, BOAT_02 and BOAT_03.

19. Why is the multivalue, multicolumn problem another form of the multivalued

dependency problem?

Both problems try to store multiple values on an attribute in a table. In the multivalue,

multiplecolumn problem, the multiple values are stored in different columns. In the

mutlivalued dependency problem the multiple values are stored in different rows. In both

cases, the solution is the same: store the multiple values in a separate table.

20. What is the inconsistent values problem? Include an example not used in the

text.

The inconsistent values problem occurs when different users or data sources use slightly

different forms of the same data value. One example is where automobiles are specified as

"Ford, 2-door, Red" in one cell and "Red Ford 2-door" in another.