Chapter 5

Question 1

Physical Database Design

Answer 1

Physical design involves enhancing and/or modifying a logical database design to improve the performance of the run-time environment

Question 2

Designing Table Columns–Default values

Answer 2

– assumed values if no explicit values

–e.g., value ‘FL’ for State column

Question 3

Designing Table Columns–Check rules

Answer 3

limitations on allowable values

e.g., value >=0 AND <=100 for TestScore column

Question 4

Designing Table Columns–Null values

Answer 4

allowing/prohibiting empty columns

e.g., prohibit leaving DateOfBirth column blank

Question 5

Designing Table Columns–Referential integrity

Answer 5

FK to PK match-ups

e.g., restrict valid values for PartNo column in the Order table to the contents of this column in the Part table

Question 6

Designing Table Columns-Coding

Answer 6

E.g., T(ampa), (St.)P(etersburg), S(arasota), L(akeland) for the four USF branch campuses
Implement by creating a look-up table

–Coding saves space, but costs an additional table lookup (join) to obtain the actual value, so performance can actually be slower

Question 7

Business Environment Requirements–Response time

Answer 7

The delay from the time a request is made to execute a query until the result appears on screen

Response time requirements depend on:

• -How application is used
• -Response expected by users

Question 8

Business Environment Requirements–Throughput:

Answer 8

-Measure of how many queries from simultaneous users can be satisfied in a given period of time

• Throughput requirements depend on:
• -Number of users needing access at the same time

Question 9

Data volatility:

Answer 9

This is how often stored data is updated

Impacts:

• -Throughput and response time (the greater the need for updates, the greater throughput required)
• -Extent of normalization (the greater the need for updates, the more important normalization becomes)

Question 10

Operational Requirements

Answer 10

Ongoing activities relating to protecting data and data availability

Question 11

Data security

Answer 11

Protecting sensitive data from unauthorized users
Protecting against theft and malicious destruction
Includes determining the location of, and the access to, the physical computer that is hosting the database

Question 12

Data backup and recovery

Answer 12

Recovering data from corrupted tables or databases due to hardware or software failures

Recovering from system loss after natural disaster

Question 13

GUI (Graphical User Interface) management utility

Answer 13

Graphical environment that provides visual cues for entering properties and options

Question 14

SQL command utility

Answer 14

Text-based utility run from command prompt

Question 15

Management Studio.

Answer 15

The GUI management utility supplied with Microsoft SQL Server

Question 16

Advantages in using SQL commands

Answer 16

• -More powerful/flexible than GUI equivalents
• -Require minimal resources
• -Can be used in batch files

Question 17

Disadvantages in using SQL commands

Answer 17

SQL language can appear complex and difficult to use for novices

Question 18

Domain integrity

Answer 18

ensures data values are valid

Question 19

Entity integrity

Answer 19

ensures table rows are unique
Enforced with:
Primary keys (values must be unique and non-null)
Can create unique sequential values with Identity property

Question 20

Referential integrity

Answer 20

Ensures that data references to other tables remain valid

Enforced typically by foreign key constraints

Question 21

Policy integrity

Answer 21

Ensures that data adhere to business rules

e.g., limiting checking account withdrawal to amount not greater than current balance

Enforced either through DBMS or by application

Question 22

What are triggers in data integrity?

Answer 22

executable procedures that run automatically in response to events such as data updates

Question 23

Normalization issues

Answer 23

need for joining tables
-Full breakdown of data into numerous small tables to avoid anomalies may not be efficient because of resources required for joining data in response to queries

Question 24

Adding External Features: Indexes
Which columns or combinations of columns should you consider indexing in order to have the greatest positive impact on the application environment’s performance?

Answer 24

Columns that are likely to be prominent in direct searches:

• -Primary keys
• -Search attributes (commonly searched for values)

—Columns that are likely to be major players in SQL operations such as joins (table links), ORDER BY clauses (sorts) and GROUP BY clauses (categorizations)

Question 25

Index Files

Answer 25

An index is a table file that is used to determine the location of rows in another table file that satisfy some condition

Question 26

Rules for Using Indexes

Answer 26

–Use especially on larger tables

–Index the primary key of each table

–Index fields that are frequently used to search for data or to sort data (e.g., LastName)

–Use indexes freely for non-volatile data, but limit the use of indexes for volatile data

Question 27

Adding External Features: Views

Answer 27

Doesn’t change the logical design

No data is physically duplicated

An important technique in protecting the security and privacy of data

Accomplished through the use of SQL code

Provide users with a subset of the columns in one or more database tables
E.g., a view could be constructed that joins data for customers and their salespersons

Question 28

Splitting a Table Into Multiple Tables: Vertical Partitioning

Answer 28

The separate groups, each made up of differ-ent columns of a table, are created because different users or applications require different columns of data

Each partition must have a copy of the primary key in order to properly identify the data rows

May be physically required by DBMS

—SQL Server maximum row size is approximately 8 KB

Question 29

Splitting a Table Into Multiple Tables: Splitting Off Large Text Attributes

Answer 29

This variation on vertical partitioning involves splitting off large text columns into separate partitions

This reduces the amount of space on disk that the remaining table columns require, allowing more rows to be stored in a smaller area (increasing data retrieval performance)

Question 30

Combining Tables: Combination of Tables in One-to-One Relationships

Answer 30

If two tables involved in a 1:1 relationship are combined into one table, then there will be querying situations in which the single table allows us to avoid joins that would have been necessary when there were two tables

Question 31

Combining Tables: Alternatives for Repeating Groups

Answer 31

If repeating groups are well-controlled, they can be combined into one table

In the example above, customer data has been combined with salesperson data because it is always the case that each salesperson has exactly two customers, a “large” & “small” one

Question 32

Combining Tables: Denormalization

Answer 32

It may be desirable, for performance reasons, to take pairs of related 3NF tables and to combine them, creating a lower normal form table and its associated data redundancy and potential anomalies

This may be advisable if unsatisfactory re-sponse times/throughput mandate eliminating run-time joins between multiple tables

Question 33

Duplicating Tables

Answer 33

When multiple applications require simultaneous access to the same tables, consider duplicating the tables and have the different applications access the duplicates

Question 34

Adding Subset Tables

Answer 34

duplicates only the portions of a table that are most heavily accessed
Assigns the table subsets to the different applications to ease the performance crunch
Similar redundancy issue as above to deal with

Question 35

Duplicating Tables

Answer 35

When multiple applications require simultaneous access to the same tables, consider duplicating the tables and have the different applications access the duplicates

Question 36

Adding Subset Tables

Answer 36

duplicates only the portions of a table that are most heavily accessed
Assigns the table subsets to the different applications to ease the performance crunch
Similar redundancy issue as above to deal with