7830textanswersch4 - Chapter Four Database Design Using...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
Chapter Four Database Design Using Normalization Page 4-1 ANSWERS TO REVIEW QUESTIONS 4.1 Summarize the premise of this chapter. The premise of this chapter is that we have received – from some source – one or more tables of data that need to be stored in a new database. 4.2 When you receive a set of tables, what steps should you take to assess their structure and content? (1) Examine the table structure and contents, (2) Examine data values and interview users to determine: (a) multivalued dependencies, (b) functional dependencies, (c) candidate keys, (d) primary keys, and (e) foreign keys, and (3) Assess the validity of any assumed referential integrity constraints. 4.3 Show SQL statements to count the number of rows and to list the top 15 rows of the RETAIL_ORDER table. The SQL statement is: SELECT TOP 15 * FROM RETAIL_ORDER; 4.4 Suppose you receive the following two tables: DEPARTMENT (DepartmentName, BudgetCode) EMPLOYEE (EmpNumber, Name, Email, DepartmentName) and you conclude that EMPLOYEE.DepartmentName is a foreign key to DEPARTMENT.DepartmentName. Show SQL for determining whether the following referential integrity constraint has been enforced: EMPLOYEE.DepartmentName must exist in DEPARTMENT.DepartmentName The solution to review question 4.4 is contained in the Microsoft Access database dbp10_im_ch04_EOC.mdb The following SQL statement will return any values of the foreign key that violate the constraint: SELECT DepartmentName FROM EMPLOYEE WHERE DepartmentName NOT IN (SELECT DepartmentName FROM DEPARTMENT WHERE EMPLOYEE.DepartmentName = DEPARTMENT.DepartmentName);
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Chapter Four Database Design Using Normalization Page 4-2 Given these two tables, an implied referential integrity relationship may not have been created. Alternatively, in MS ACCESS, it is possible to define the relationship between the EMPLOYEE and DEPARTMENT without enforcing referential integrity.
Background image of page 2
Chapter Four Database Design Using Normalization Page 4-3 Running the query, we find that the existing data does NOT comply with the referential integrity constraint: We need to either (1) add the Sales department and its budget code to DEPARTMENT, or (2) change the value of EMPLOYEE.DepartmentName to ‘Marketing’ for any employee currently having a value of ‘Sales’. 4.5 Summarize how database design principles differ with regards to the design of updateable databases and the design of read-only databases. When designing updateable databases we need to be concerned about modification anomalies and inconsistent data. This means that normalization principles must be carefully considered, and that the tables generally be fully normalized although some denormalization may be justified. When designing read-only databases, we are working with tables that will never be updated through normal operations
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 11/15/2011 for the course ECON 101 taught by Professor David during the Spring '11 term at Kwansei Gakuin University.

Page1 / 11

7830textanswersch4 - Chapter Four Database Design Using...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online