MIS 431
Dr. Steve Ross
Winter 2004
Lecture 1.
*
Relational Databases
and SQL
Material for this lecture is drawn from Hernandez and Viescas, SQL Queries for Mere Mortals, and the professor’s experience.
The Relational Model
Proposed in 1969 by E. F. Codd
Based on
• Set theory
• First order predicate logic
So-named because “relations” are a
component of set theory
• Relation = entity = table in practice
Relational Database
Management Software
System R
INGRES
Oracle
DB2
dBASE
Paradox
R:BASE
Access
SQL Server
Anatomy of a
Relational Database I
Table
• Records and fields
• Single, specific subject: an object or an event
• Physical order of records and fields immaterial
• Primary key field uniquely identifies records
Record
• Unique instance of the table subject
• Contains one of each of the fields
Anatomy of a
Relational Database II
Field
• Contains one and only one value
• Name identifies the type of data
Key
• One or more fields
• Primary
• foreign
Anatomy of a
Relational Database III
View
• “Virtual table”
• Composed of one or more base tables
• a.k.a. Query (e.g., in Access)
Relationship
• 1:1
• 1:M
• M:N
Fine-Tuning Fields I
Field name checklist*
• Is the name descriptive and meaningful to
your entire organization?
• Is the field name clear and unambiguous?
• Did you use an acronym or abbreviation as a
field name?
• Did you use a name that implicitly or explicitly
identifies more than one characteristic?
* Hernandez and Viescas, pp. 21-22
Fine-Tuning Fields II
Field design checklist*
• Make sure the field represents a specific
characteristic of the subject of the table.
• Make certain that the field contains only a
single value.
• Make sure the field does not store the result
of a calculation or concatenation.
• Make certain the field appears only once in
the entire database.
* Hernandez and Viescas, pp. 23-25
Fine-Tuning Fields III
Resolving multi-part fields
• Examples of multi-part fields
• Steven C. Ross
• 233 Bayside Road, Bellingham, WA 98225
• 360-734-4675
• Compaq MV720 17″ Monitor
• How far do you go?
Fine-Tuning Fields IV
Resolving multi-valued fields
• Examples of multi-valued fields
• [dependents] Larry, Moe, Curly
• [phone numbers] H:360-734-4675 O:360-650-3902
• [skills] Visual Basic, Access, SQL Server
• What is the true relationship?
• 1:M
• M:N
Fine-Tuning Tables I
Table name checklist*
• Is the name unique and descriptive enough to
be meaningful to your entire organization?
• Does the name accurately, clearly, and
unambiguously identify the subject of the table?
• Does the name convey physical characteristics?
• Did you use an acronym or abbreviation as a
table name?
• Did you use a name that implicitly or explicitly
identifies more than one subject?
* Hernandez and Viescas, pp. 29-31
Fine-Tuning Tables II
Table design checklist*
• Make sure the table represents a single
subject.
• Make certain each table has a primary key.
• Make sure the table does not contain any
multi-part or multi-valued fields.
• Make sure there are no calculated fields in the
table.
• Make certain the table is free of any
unnecessary duplicate fields.
* Hernandez and Viescas, pp. 31-32
Fine-Tuning Tables III
Resolving unnecessary duplicate fields
• Examples of unnecessary duplicate fields:
• “Reference data,” e.g., instructor name in class
schedule table
• “Multiple occurrences,” e.g., three fields for
committee assignments
Fine-Tuning Tables IV
Primary key checklist*
• Does the field uniquely identify each record in
the table?
• Does this field contain unique values?
• Will this field ever contain unknown values?
• Can the value of this field ever be optional?
• Is this a multi-part field?
• Can the value of this field ever be modified?
* Hernandez and Viescas, pp. 38-39
Establishing Solid
Relationships I
1:1
• How do you decide where to put the foreign key?
• What is the opti-max rule?
1:M
• How do you decide where to put the foreign key?
M:N
• How do you decide where to put the foreign key?
Establishing Solid
Relationships II
Deletion (and update) rules
• Restrict
• Cascade
Participation
• Type
• mandatory or optional
• Degree
• minimum (0,1,n)
• maximum (1,n,∞)
Structured Query Language
How is the acronym “SQL” pronounced?
Is there only one standard for SQL?
Is it static?
Are there different generations of SQL?
Who decides?
Who cares?
Next Lecture
Relational Database
Management Systems
and SQL Server