A Practical Guide to Database Design, 2e By Rex Hogan (Solutions Manual)
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?
Pa
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
ss
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.
vi
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.
be
Passvibes Passvibes
s
Downloaded by: tutorsection | Want to earn $1.236
Distribution of this document is illegal extra per year?
, 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;
Pa
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.
ss
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
vi
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.
be
Passvibes Passvibes
s
Downloaded by: tutorsection | Want to earn $1.236
Distribution of this document is illegal extra per year?
, 11. What are the differences between RAID 0 and 1?
ANS:
RAID 0 “stripes” the data across all drives and SIGNIFICANTLY improves input/output performance. It
provides no protection for device failure.
RAID 1 “mirrors” data across multiple drives. It doesn’t improve performance, but provides against device
failure.
12. You are asked to install a RDBMS on a desktop computer with two internal drives. How would you configure
the system across those drives to provide the best possible backup/recovery protection?
ANS:
Drive 1 – System software and data files; drive 2 – index files, log files and database backups
13. Why are Referential Integrity constraints important?
ANS:
Tables are logically associated together when the key of one table appears within another table; this
establishes a one-to-many relationship between those tables.
Because each table can be independently updated by application logic, Referential Integrity constraints
enforce the validity for these relationships to ensure the data in each table is always consistent. For
example, in the Department-Employee relationship, the DepartmentID values in the Employee table must
always be valid.
14. A new database has a new table for Department and another for Employee as shown in Section 1.5, and
Referential Integrity constraints created. Files are available to load each table. Does it matter in which order the
tables are loaded? Why or why not?
ANS:
Yes. The Department file must be loaded before Employee so that when new Employee rows are created,
the associated Department row will exist.
15. In setting up backup/recovery operations for a database for use by your business, what considerations are there
in making/storing external backups for the database?
ANS:
Scheduled nightly backups should be made at the end of each business day. In addition, a copy of that
backup should be made to another computer in that office.
Every Friday evening, an external copy of the backup should be made after the latest backup is taken and
Pa
be moved to external storage that evening to guarantee availability should the business office were lost due
to structural damage, fire, or theft.
Chapter 2
Questions
ss
1. Define the terms “entity” and “attribute”, and give examples of each.
ANS:
An entity is something about which information is known. For example, we store information about
Employees.
Hint – when thinking about an item and deciding if it’s an entity or attribute, we can ask “does it have a
single value?”. If not, and multiple items are needed to describe it, it’s an entity.
vi
An attribute represents a single piece of information that provides something about an entity. For example,
each Employee will have an Employee-ID (or something equivalent), which has a single value.
be
Passvibes Passvibes
s
Downloaded by: tutorsection | Want to earn $1.236
Distribution of this document is illegal extra per year?
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?
Pa
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
ss
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.
vi
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.
be
Passvibes Passvibes
s
Downloaded by: tutorsection | Want to earn $1.236
Distribution of this document is illegal extra per year?
, 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;
Pa
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.
ss
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
vi
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.
be
Passvibes Passvibes
s
Downloaded by: tutorsection | Want to earn $1.236
Distribution of this document is illegal extra per year?
, 11. What are the differences between RAID 0 and 1?
ANS:
RAID 0 “stripes” the data across all drives and SIGNIFICANTLY improves input/output performance. It
provides no protection for device failure.
RAID 1 “mirrors” data across multiple drives. It doesn’t improve performance, but provides against device
failure.
12. You are asked to install a RDBMS on a desktop computer with two internal drives. How would you configure
the system across those drives to provide the best possible backup/recovery protection?
ANS:
Drive 1 – System software and data files; drive 2 – index files, log files and database backups
13. Why are Referential Integrity constraints important?
ANS:
Tables are logically associated together when the key of one table appears within another table; this
establishes a one-to-many relationship between those tables.
Because each table can be independently updated by application logic, Referential Integrity constraints
enforce the validity for these relationships to ensure the data in each table is always consistent. For
example, in the Department-Employee relationship, the DepartmentID values in the Employee table must
always be valid.
14. A new database has a new table for Department and another for Employee as shown in Section 1.5, and
Referential Integrity constraints created. Files are available to load each table. Does it matter in which order the
tables are loaded? Why or why not?
ANS:
Yes. The Department file must be loaded before Employee so that when new Employee rows are created,
the associated Department row will exist.
15. In setting up backup/recovery operations for a database for use by your business, what considerations are there
in making/storing external backups for the database?
ANS:
Scheduled nightly backups should be made at the end of each business day. In addition, a copy of that
backup should be made to another computer in that office.
Every Friday evening, an external copy of the backup should be made after the latest backup is taken and
Pa
be moved to external storage that evening to guarantee availability should the business office were lost due
to structural damage, fire, or theft.
Chapter 2
Questions
ss
1. Define the terms “entity” and “attribute”, and give examples of each.
ANS:
An entity is something about which information is known. For example, we store information about
Employees.
Hint – when thinking about an item and deciding if it’s an entity or attribute, we can ask “does it have a
single value?”. If not, and multiple items are needed to describe it, it’s an entity.
vi
An attribute represents a single piece of information that provides something about an entity. For example,
each Employee will have an Employee-ID (or something equivalent), which has a single value.
be
Passvibes Passvibes
s
Downloaded by: tutorsection | Want to earn $1.236
Distribution of this document is illegal extra per year?
