MIS 431

1
(No Transcript)
2
MIS 431Dr. Steve RossSpring 2006
?
?
?
?
?
?
?

• Lecture 1.Relational Databasesand SQL

Material for this lecture is drawn from Hernandez
and Viescas, SQL Queries for Mere Mortals, and
the professors experience.
3
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

4
Relational Database Management Software

• System R
• INGRES
• Oracle
• DB2
• dBASE
• Paradox
• RBASE
• Access
• SQL Server

5
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

6
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

7
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
• 11
• 1M
• MN

How do we create a relationship?
8
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
9
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
10
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?

11
Fine-Tuning Fields IV

• Resolving multi-valued fields
• Examples of multi-valued fields
• dependents Larry, Moe, Curly
• phone numbers H360-734-4675 O360-650-3902
• skills Visual Basic, Access, SQL Server
• What is the true relationship?
• 1M
• MN

12
Fine-Tuning Tables I
Other issues Hungarian notation Singular or
plural?

• 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
13
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
14
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

15
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
16
Establishing Solid Relationships I

• 11
• How do you decide where to put the foreign key?
• What is the opti-max rule?
• 1M
• How do you decide where to put the foreign key?
• MN
• How do you decide where to put the foreign key?

17
Establishing Solid Relationships II

• Deletion (and update) rules
• Restrict
• Cascade
• Participation
• Type
• mandatory or optional
• Degree
• minimum (0,1,n)
• maximum (1,n,8)

18
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?

19
Next Lecture

• Relational DatabaseManagement Systemsand SQL
  Server