Solutions Manual for A Practical Guide to Database Design, 2e By Rex Hogan (All Chapters)

Solutions Manual for A Practical Guide to Database Design, 2e By Rex Hogan (All Chapters) Chapter 1 Questions 1. Do you consider MYSQL to be a Relational Database System? Why or why not? ANS: Frankly, either a Yes or No ...

Solutions Manual for A Practical Guide to Database Design, 2e By Rex Hogan (All Chapters) Chapter 1 Questions 1. Do you consider MYSQL to be a Relational Database System? Why or why not? ANS: Frankly, either a Yes or No is acceptable if justified appropriately. I personally don’t consider MQSQL to be a RDBMS. Each table is implemented in its own flat file. If a new column is added, the original file is copied into a new file having the additional column. This, in my opinion, does not meet the criteria of being able to dynamically make changes while the table is being used. Those saying Yes could base that judgement on its ability to respond to SQL queries. 2. In the context of a database transaction, what is a “unit of work”? Why is it important? ANS: A “unit of work” is a series of updates to the database that are guaranteed to either succeed as a group, or all be reversed should some error or problem occur. It is important because the database will always contain data that is in a consistent state from the user’s perspective. 3. What are the “ACID” properties of a RDBMS? Why are they important? ANS: The ACID properties refer to: Atomicity (database updates made are “all or nothing”) Consistency (data in the database will always be in a consistent state) Isolation (multiple concurrent users can access the database and updates from one will be shielded from others until a commit point is reached) Durability (once a transaction commits updates, the updates are guaranteed to survive any type of failure; e.g., a disk drive fails, or the computer crashes) 4. In a database recovery operation, what files are used to restore the database? What does each contain? ANS: Database backup – contains a complete copy of the database at some point in time. A database backup could be a “full” backup, making a complete copy of the data, or a “partial” backup, making a copy only of database components that were altered after the last full backup was made. Log files – contain “before” and “after” records of the database as updates are made. The recovery operation would begin by 1) restoring the database using the last full backup, 2) if it exists, applying a partial backup to apply updates it contains, then 3) using log files to apply all updates made after the last full/partial backup. 5. What’s the difference between a Table and a View? ANS: A table is a set of related data elements stored and managed vertically as columns (identified by name) and records are stored as horizontal rows. A view is a virtual definition for a subset of data contained in a table or for columns residing in two or more associated tables. Views are populated at run time based on their definition and the data residing in the view’s underlying tables at that moment of execution. 6. Given table structure the following table structure, write a SQL query to find the Employee ID and employee name from the Employee table for those assigned to Department 20. Employee EmployeeID EmployeeFirstName EmployeeMiddleName EmployeeLastName EmployeeWorkPhoneNumber EmployeeHomePhoneNumber EmployeeStreetAddress EmployeeCity EmployeeState EmployeeZipCode DepartmentID ANS: SELECT EmployeeID, EmployeeFirstName, EmployeeMiddleName, EmployeeLastName FROM Employee WHERE DepartmentID = 20; 7. Are SQL queries identical between products such as Microsoft Access, SQL Server and Oracle? ANS: The products have identical implementations for many SQL functions. However, each has its own extensions to differentiate it from others. SQL Server and Oracle also provide support for complex queries. 8. Write a SQL query to find the number of employees in Department 20. ANS: SELECT Count(*) as ‘Number of Employees’ FROM Employees WHERE DepartmentID = 20; 9. In writing a SQL query, what’s the difference between “=” and “like”? ANS: A “=” specifies an exact match, for example “WHERE DepartmentID = 20” will use only those rows having 20 in the DepartmentID column. A “like” qualifier works with a wildcard character and returns rows matching that pattern. For example, with Microsoft Access, a qualifier “…WHERE EmployeeFirstName LIKE ‘Ed*’ would return rows where employees first names are Edward, Edmond, and so on. 10. You are part of a team configuring a mission critical database for failover. What are the issues in locating the failover instance in same building? In adjacent building? In a nearby location? ANS: In the same building – transporting log records to backup shouldn’t be a problem, but wouldn’t provide protection if the building were lost due to structural failure (storm damage etc) or for local power failure. In adjacent building – transporting logs records to an adjacent building would take a bit longer. If the primary building were lost due to a structure failure or local power failure, the adjacent one may, or may not, be also affected. In a nearby location – transporting logs over a longer distance would be an issue, but the building separation would make it more likely the failover site would be operational should a structural failure or power outage occur.