Physical Design part 3

• Involve the users! (Throughout the process)

• Translate logical data model for target DBMS
o Design base relations (tables)
o Determine how to represent derived data
o Design general constraints (methods to
enforce business rules)

• Design physical representation – file organization

and indexes
o Analyze transactions
o Choose file organizations
o Choose indexes
o Estimate disk space requirements

• Design user views

WE ARE HERE
• Design security mechanisms
• Consider the need to introduce controlled
redundancy (denormalization)
• Monitor and tune operational system

Design user views

In module 4, we’ll talk about the interface in detail. During physical design we want the system to store data
optimally, and we need to start thinking about the interface. There are physical design choices we can make that
can facilitate or hinder the interface design.

Consider this scenario. A radio button is an interface method that presents a set of choices, and the user is
allowed to select only ONE of those choices—“you can be male or female, but not both”, for example.

In most interfaces, radio buttons store integer data. So if we to use a radio button in the interface for gender, we
might decide to code that data using integers (e.g., male stored as 1, female stored as 2) instead of text (M, F).

If we stored gender as text it could still be used but would require much more
work in the interface. In this case, the interface designer would need to write
code to convert M & F to 1 & 2 and back; or you'd need to create a view that
codes the data before sending data to the interface, and a trigger that recodes
the data before storing in the database. This is a small example and may not
cause a huge performance hit by itself, but you can see that very many of
these would certainly make the interface slower and more complex to manage.

Database Security
Who has privileges to do what with the data – who can create, read, update,
delete what parts of the DB. We’ll discuss in detail in module 5.
 SQL (DDL)

You knew we couldn’t go too long without revisiting SQL, right?

SQL Data Manipulation Language (DML)

The SQL statements you’ve been practicing in previous modules were primarily to read (retrieve) data, using
statements that begin with SELECT. For example

SELECT d.pt_id, name, max(temper) as MaxTemp

FROM demog d, vitals v
WHERE d.pt_id = v.pt_id
AND temper > 37.7
GROUP BY d.pt_id, name
ORDER BY cint(d.pt_id)

We also practiced a little bit with inserting data

INSERT INTO demog (pt_id, name, race, gender, zip)
VALUES ('2100', 'x,x', 'C', 'M', '12345')

And we practiced updating data

UPDATE demog
SET name = 'Smart, Maxwell'
WHERE pt_id = '2100'

We didn’t practice deleting, but the syntax is similar. To delete Max’s record:
DELETE from demog
WHERE pt_id = ‘2100’

All those type of statements manipulate contents of the tables, that is, they manipulate DATA. Thus, this type of
SQL is called a data manipulation language (DML).

SQL Data Definition Language (DDL)

SQL statements are also used to build the data structures—to build, modify, or delete tables, indexes,
constraints, etc. These type of SQL statements are called data definition language (DDL).

The good news is that the way you write these SQL statements is almost the same as what you already know! In
Access, creating a table in “table design view” writes SQL “create table” statements behind the scenes. You
could write “create table” SQL statements in the Access SQL window, just like we’ve done with our “select”
statements (at least, in a limited way).
The basic syntax:
CREATE TABLE tablename (
columnName dataType (size if necessary),
…
Primary Key (list of column[s])
)

When the primary key is listed AFTER all the attributes, this is a “table constraint”. You must do it this way if
you have a multiple-attribute (composite) primary key. You may do it this way all the time. Some people list the
primary key with the attribute name if the table has a single attribute PK.

So…for demog we could do

CREATE TABLE demog ( Note: the line breaks, spacing, and capitalization don’t
PT_ID Text (4), matter. The order of items does matter – column name,
NAME Text (20), then data type, then size if applicable, then a comma.
ZIP Text (5), For some data types, such as a DATE, there’s no choice
GENDER Text (1), about size so you don’t have to list it. You do not have
RACE Text (1) to differentiate date-only from date-time columns.
Primary Key (pt_id)
) Mydate date,

OR Be sure to use data types that match your DBMS data

CREATE TABLE demog ( types.
PT_ID Text (4) primary key,
NAME Text (20),
ZIP Text (5),
GENDER Text (1),
RACE Text (1)
)

but for the adm_dx table we have only one choice – to do the PK as a table constraint:

CREATE TABLE adm_dx (

PT_ID text(4),
DIAGNOSIS text(20)
Primary key (pt_id, diagnosis)
)
NOTICE how similar the create table statement is to the data dictionary format!
Demog (first few columns of the Data Dictionary)…

Attribute Description Data size

name type
PT_ID Patient ID Text 4
NAME Patient name Text 20
ZIP Zip code Text 5
GENDER Patient gender Text 1
RACE Patient race Text 1

If we omit the “description” column…

Demog
Attribute Data size
name type
PT_ID Text 4
NAME Text 20
ZIP Text 5
GENDER Text 1
RACE Text 1

Convert table to text…and omit the headers

Demog
PT_ID Text 4
NAME Text 20
ZIP Text 5
GENDER Text 1
RACE Text 1

Now just add the “CREATE TABLE” words, and add punctuation and primary key…

CREATE TABLE demog (

PT_ID Text (4),
NAME Text (20),
ZIP Text (5),
GENDER Text (1),
RACE Text (1)
Primary Key (pt_id)
)

I am making a small change to the final project checklist. You do not have to show table design screen
shots or create table statements for ALL tables.

I would like you to show the create table SQL statement for ONE table – whether you use Access or Oracle.

We can use the desc command (Oracle) or MS Access documenter to show your final created DB. (see me for
help with this part)
SEE YOUR TEXT
For more information about DDL syntax such as for altering a table structure, or for dropping a table.

Note delete vs drop:

Delete from demog where pt_id = ‘100’ removes 1 row of data from the demog table
Delete from demog; removes all data from demog but leaves the table intact
Drop table demog; removes the actual table

Views
Views are essentially stored queries.

A couple of uses for views…A view can be used to help with security – for example, to only show the columns
from a table that a user is authorized to see. The view can help with usability, by creating a user-friendly alias
for column names.

Now, finish your data dictionary.

Data Dictionary (Meta data, Data about data). This is the documentation for physical design for this class. Data
dictionary can be active (used by the computer to create/maintain database), or passive (separate document used
by people). WE will create a passive data dictionary.

What’s in our data dictionary?

Please list all tables and their purpose.

Then for each table:

• Table name
• Attribute names
• Primary Key
• If appropriate, alt key(s) and foreign key(s)
• Derived attributes and how to compute

Code Tables
Table that contains a code and its meaning. You store the code in a main table and look up the meaning in
another table. It keeps you from mis-spelling terms and having to type long strings of text.

Coded vocabularies are one example. Picklists are another example. The SNDB example in Access contains a
picklist with gender codes (M = male, F = female).

I have asked you to create a code table (or picklist) for ONE attribute somewhere in your database.

Add this to your data dictionary – what is the picklist/code table; and show in the domain column of a table
where the picklist is used
Other table information might be useful in a “real life” DB design situation – you don’t have to create now.
• Who created table
• When table created
• Modification – what was done
• When last modified
• Who modified
• Who requested the modification
• Ownership/Access - who can access the data

Additional Documentation you might consider in a “real life” situation

• Description of applications and data used by each application
• Description of views
• Taxonomy, language information, for example, if diagnoses were coded with ICD9 or SNOMED codes
• Who are the users, administrators, programmers associated with this project
• Description/examples of reports
• Description of queries and purpose

While you’re waiting for me to evaluate the data dictionary, take the quiz.

Once the data dictionary is OK, build your tables.

Data Dictionary Example
Sample Database
Version 1.0
MS Access XP

Documented March 1, 2008

Kathy Sward

Not all tables are included in this example.

Table Overview
Main Tables
these are the ones from logical design

Table Name Who Created Who can Access Comments/Purpose

Customer K. Sward All Lists my customers
Employee K. Sward HR only Lists the employees
Order K. Sward All What orders were taken/filled
Product K. Sward Product group What are all my products
ProductCategory K. Sward Product group Categories for products
ProductOrder K. Sward All What products were used in each order
SalaryHistory K. Sward HR Salary history for each employee
WithholdingHistory K. Sward HR Withholding (tax) information for each employee
ZipCodes K. Sward All Pre-loaded with zip code data downloaded from
the web

Supplemental Tables (code/list tables)
added at physical design phase

Table Name Who Created Who can Access Comments
CodeShipper K. Sward All users Lists code and name for ShippedVia field
of the ORDER table
Table Details
Main Tables

Customer
Variable Name Description Data Type Data Domain Null?
Length
CustomerID Identifies Customers Auto number 4 Integer starting with 1 No
CompanyName Name of Company Text 40 Valid company names No
ContactName Contact Last Name Text 20 Free text, Last name Yes
of contact person
ContactTitle Contact Title Text 4 Mr., Ms., Mrs. Yes
Street Company Address Text 30 Street Address Yes
Zip Company Zip Code Text 5 US Postal Codes. Yes
From ZipCodes table
Phone Company Main Phone Text 12 999-999-0000 Yes
or 999-9999
Fax Company Fax Number Text 12 999-999-0000 Yes
or 999-9999
Primary Key: CustomerID
Index: Zip
Foreign Key: Zip (references ZipCodes.Zip)

Etc. for the rest of the tables