Study Guide Mis 691 Chapter 2

Study Guide MIS 691 Midterm Chapter 2 2. 2 Entity: One theme or topic; something of importance to a user that needs to be represented in a database. In the entity-relationship model, entities are restricted to things that can be represented by a single table. An example of this is in the EMPLOYEE table; data about the entity (the employee) is in the table rows and data about the entity’s attributes are in the rows. 2. List the characteristics a table must have to be a relation: * Each row of the table holds data that pertain to some entity or a portion of some entity * Each column contains data of the attributes of the entity * The cells must hold a single value; no repeating elements * All of the entries in any column must be the same kind * Each column must have a unique name and the column order is unimportant * The order of the rows in unimportant * No two rows in the table may hold identical data values 2. 0 Define the term unique key and give an example: A key that defin es a unique row. An example of a unique key in the EMPLOYEE table is EmployeeNumber; the query of any EmployeeNumber (223 for example) will only produce a single row and data for one employee. 2. 11 Non-unique key: A key that potentially identifies more than one row. In the EMPLOYEE table from our book, Department is a non-unique key because it identifies several rows. 2. 12 Give an example of a relation with a unique composite key: A composite key contains two or more attributes.An example of a unique composite key in the EMPLOYEE table is if we combined LastName, FirstName, and Department as a key; these three attributes combined would insure that we would identify only one row. 2. 13 Explain the difference between a primary key and a candidate key: Candidate keys are keys that uniquely identify each row in a relation. They can be single or composite. The primary key is the candidate key that is chosen as the key that the DBMS will use to uniquely identify each row. 2. 5 What is a surrogate key and under what circumstances would you use one? A surrogate key is a column with a unique, DBMS-assigned identifier that has been added to a table to be the primary key. You would use a surrogate key when the primary key in the table is not ideal. You add a surrogate because it is short, numeric, and will never change – it is an ideal primary key. 2. 20 Define the term referential integrity restraint and give an example: A relationship constraint on foreign key values.A referential integrity constraint specifies that the values of a foreign key must be a proper subset of the values of the primary key to which it refers. In the EMPLOYEE table: the value of Department should match a value of DepartmentName in the DEPARTMENT table. 2. 21 Explain the three possible interpretations of a null value: 1. It could mean that no value is appropriate 2. It might mean that the value is known to be blank (intentionally left blank) 3. It may mean that the value is unknown 2. 4 Name the functional dependency and identify the determinants of: Area = Length x Width Area is functionally dependent upon Length and Width or that the composite of Length and Width are determinants of Area 2. 31 Describe the nature and purpose of the normalization process: Normalization is breaking up tables with more than one theme into sets of tables that have one theme each. You have to do this so that there isn’t repeat information in a table and so there are no modification or deletion issues.Normalization: Normalization is the process of breaking a table with one or more theme into a set of tables such that each only has one theme. You need to create a well-formed relation: 1. Every determinant must be a candidate key 2. Any relation that is not well-formed should be broken into two or more relations that are well-formed The Normalization Process: 1. Indentify all of the candidate keys 2. Indentify all of the functional dependencies 3. Examine the dereminants of the functional dependencies.If any determinant is not a candidate key, the relation is not well-formed. In this case: a. Place the columns of the functional dependency in a new relation of their own b. Make the determinant of the functional dependacy the primary key of the new relation c. Leave a copy of the determinat as a foreign key in the original relation d. Create a referential integrity constraint between the orginal and the new relation. 4. Repeat step 3 until every key is a candidate key