CS 101

1
CS 101 Nov. 9

• Text software issues (continued)
• Readability
• Fonts
• Database concepts
• Relationships
• Queries

2
Readability

• First, need to measure
• Words per sentence
• Syllables per word
• How to Write Plain English by Flesch
• 206.835 84.6spw 1.015wps
• 90s fifth grade
• 30s college level

3
Fonts

• Font style of printing
• Typeface
• Point size
• Ex. Times 10, Helvetica 12
• Fonts can be distinguished by
• Sarif or sans sarif
• Proportional or non-proportional

4
Font types

• Sarif has feet
• Sans sarif no feet
• Look at i, h, n, r, (but not t!)
• Proportional width of characters changes
• Non-proportional all have same width

5
Access review

• Purpose Manage data
• Parts of a database
• If your database has only 1 table, you are better
  off using Excel
• Relationship between tables?
• None
• One-to-one
• One-to-many (most common)
• Many-to-many

6
Table design

• What fields to we need?
• Age? ? store birth date
• GPA? ? store credits and quality points
• What year? ? store date of admission
• Store data in its smallest parts (e.g. address)
• Calculated fields dont belong in table!

7
Queries

• Usually we ask about info from 2 tables.
• By default, a query will perform an operation
  called a Cartesian Product, which gives all
  possible combinations.
• Ex. Name and City tables

Name
Bob
Mary
Ken
City
Miami
Pittsburgh
8
Cartesian Product

• Given 2 sets, find all possible ordered pairs.
• Analogously for more than 2 sets. ?
• Great example choosing a menu.
• Appetizer
• Entrée
• Dessert
• Unfortunately, most DB queries are not like this!
  We get too many results.

9
Relationships

• We want to tell Access that there is a
  relationship between the tables, so we can create
  meaningful query.
• One-to-many is most common
• Each city has one or more employees.
• Now, query will return 3 results instead of 6
• Miami Bob
• Pittsburgh Mary
• Ken

10
One-to-One
Empl Name Position
101 Smith Welder
102 Jones carpenter
Empl Salary
102 18,000
101 17,000

• Can be useful if some information is
  confidential.
• What if we didnt have any relationship?

11
1-1 Query

• When you combine tables that have a 1-1
  relationship Access will look for fields that
  are the same, and use this as a filter.
• In previous example, well have 2 results instead
  of 4. ?
• Employee 101s information
• Employee 102s information
• Lets look at another example.

12

• What happens when we join these 1-1 tables?

First name Last name Position City
Bob Fuller accountant Chicago
Bob Daniels cashier Greenville
Bob Daniels accountant Indianapolis
Alice Andrews gardener Greenville
First name Last name Salary Birthday
Bob Daniels 51,000 7/1/67
Elena Carlson 21,000 9/1/89
Alice Andrews 81,000 8/1/78
13
First name Last name Position City
Bob Fuller accountant Chicago
Bob Daniels cashier Greenville
Bob Daniels accountant Indianapolis
Alice Andrews gardener Greenville
First name Last name Salary Birthday
Bob Daniels 51,000 7/1/67
Elena Carlson 21,000 9/1/89
Alice Andrews 81,000 8/1/78
First Last Position City Salary Birthday
Bob Daniels cashier Greenville 51,000 7/1/67
Bob Daniels accountant Indianapolis 51,000 7/1/67
Alice Andrews gardener Greenville 81,000 8/1/78
14
Relationship summary

• When you have 2 tables, there is almost always a
  relationship
• They share one field in common.
• Can you tell what it is?
• Ex. Customers Orders
• Ex. Publishers books
• Ex. Students Class roster

15
Example
16
One-many Relationships

• Referential integrity
• Keep related records consistent
• Cascade delete allow deletion of one
• Cascade update allow update of one
• For example, changing someones CustomerID.

17
Many-to-many

• Ex. Customers to products
• Implement as 2 one-to-one
• Order details table
• Think of possible queries based on the 5 tables
  given in handout.