CS 200
Lecture 06 Database Introduction
CS 200 Spring 2018 1 Database Introduction
Abbreviations
Miscellaneous Notes
• aka also known as
• DBMS DataBase Management System
• mutatis mutantis with the necessary changes having been made
CS 200 Spring 2018 2 Database Introduction
Administrativia
Please read and highlight BEFORE lab
• Assignment6
• This week’s lecture slides
Reading
• Database Design (on Learn > Database Resources > Database Design) Adapted from Access Database Design and Programming by Steven Roman
Today
• Database basics and Design • Creation of a database
• Reports and Queries
• Relationships
Please ask questions!
CS 200 Spring 2018 3
Database Introduction
Assumptions
You have used a database before
You understand basic data management
CS 200 Spring 2018 4 Database Introduction
Things to think about
• What are the data objects in a database?
• What are different databases you use?
• How are the databases you are part of used? • Is the way data is displayed important?
CS 200 Spring 2018 5 Database Introduction
Databases
This is your first pass at databases. By the end of this section you will be able to:
• design a simple database
• think about queries and reports
• understand database terminology and • create relationships with a database
Our objectives are
• to introduce you to database fundamentals
CS 200 Spring 2018 6 Database Introduction
What is a Database?
A collection of related data
Data consists of information on a specific topic
CS 200 Spring 2018 7 Database Introduction
Databases – Structured Data
Why use a database at all?
Structuring data allows us to do things we can’t do efficiently,
or can’t feasibly do, with unstructured data
• The added power & flexibility aren’t free
it takes time and effort to create (and maintain) the structure
• You have to decide if that effort is worthwhile
Obvious questions:
• What do I mean by “structure” in a database? • What’s the payoff?
CS 200 Spring 2018 8 SQL
Key DBMS Functionality
Data entry “validation”
Sophisticated searching (aka “queries”) Sophisticated summarizing and reporting Safe simultaneous updates by multiple users
The REAL power of a relational database
• appears when you have multiple related tables • what does “related” mean?
• why have multiple tables?
CS 200 Spring 2018 9 SQL
Use of Databases
What databases are you a part of?
What are some of the benefits of belonging to a database? What are some of the drawbacks?
Why/when do I use a database?
How are the databases I am part of used?
CS 200 Spring 2018 10 Database Introduction
Table (Entity) Field (Attribute) Record
Database Terminology (1)
CS 200 Spring 2018 11 Database Introduction
Report Query
Database Terminology (2)
CS 200 Spring 2018 12 Database Introduction
Creation of a Table
Consider the following example:
You would like to create a database with all of the Albums you have downloaded onto your Phone.
What info would be relevant to have in a table?
What types of fields will each of these be?
What are limitations, validations we want the
fields to have?
CS 200 Spring 2018 13 Database Introduction
Text
Numbers (real or integer) Date
Time
Timestamp
Media
Calculation
Summary
CS 200 Spring 2018 14
Database Introduction
Types of Fields
Auto Entry Number/Dates Ranges
Value Lists Unique Values Required Fields and more….
Data Entry Validation
CS 200 Spring 2018 15
Database Introduction
It’s easier to catch & correct errors
when data is entered
than it is to find and correct later
Creating a Database
CS 200 Spring 2018 16 Database Introduction
Album Table Fields
Field Name
Album Title
AlbumType
Medium
Recording Year
Label
Purchase Price
Album Artist
Purchase Date
CS 200 Spring 2018 17 Database Introduction
Album Table Field Types
Field Name
FieldType
Album Title
Text
AlbumType
Text
Medium
Text
Recording Year
Number
Label
Text
Purchase Price
Number
Album Artist
Text
Purchase Date
Date
CS 200 Spring 2018 18 Database Introduction
Album Table Field Definitions
Field Name
FieldType
Options
Album Title
Text
AlbumType
Text
Value List (TP, CD, LP, MP3)
Medium
Text
Value List (Jazz, Blues, Rock, )
Recording Year
Number
Range (1921-2010), Number
Label
Text
Purchase Price
Number
Number
Album Artist
Text
Purchase Date
Date
Date
CS 200 Spring 2018 19 Database Introduction
More than one Table in a Database
Adding Songs (another entity) How many songs on an album?
CS 200 Spring 2018 20 Database Introduction
The General Problem Just Illustrated
We want to have multiple copies of some field(s)
and we can’t know in advance how many copies — especially difficult if there’s no limit!
Replicating fields is bad because
•it’s a lot of work to set up
• it makes searching difficult
• you waste a lot of space
• you must modify the database structure
CS 200 Spring 2018
21 SQL
to add more copies
if you run out
Songs Table
More than one Table in a Database
SongTable
Album Table
Song Title
Album Title
Side
AlbumType
Track
Medium
Mins
Recording Year
Secs
Label
Purchase Price
Album Artist
Purchase Date
CS 200 Spring 2018 22 Database Introduction
Primary Key
Foreign Key
Uniquely Identifying Records
CS 200 Spring 2018 23 Database Introduction
Songs Table
More than one Table in a Database
SongTable
Album Table
Song ID
Primary Keys
Album ID
Song Title
Album Title
Side
AlbumType
Track
Medium
Mins
Recording Year
Secs
Label
Song Album ID
Foreign Key
Purchase Price
Album Artist
Purchase Date
CS 200 Spring 2018 24 Database Introduction
One to One One to Many Many to One Many to Many
CS 200 Spring 2018
25 Database Introduction
Types of Relationships
Relational databases
• keep data in multiple tables
• each of which has a primary key
• and link those tables
• by matching field values (though not necessarily via a foreign key)
This works in both directions
• given a song, get the album info
• given an album, list the songs on that album — just by matching field values (a “relationship”)
Summary
CS 200 Spring 2018 26 SQL
Criteria for when you need another table…
(1) You have more than one “entity” (eg cars and drivers); fields for one are empty for the other (& vice-versa)
(2) You can have multiple values of some field, ESPECIALLY when you can’t predict how many
(such as having multiple songs on each album)
(3) Given the value of one field A …
you know the value of another field B without looking because B’s value is always the same for a given value of A just as, given Album ID, we knew Album Title, Group, etc
Cours
Sec
Teacher
Office
Phone
Userid
Machine
e
cs100
3
KAAnderson DC 3141 6656 kaanders math KAAnderson DC 3141 6656 kaanders math
cs100
4
cs100
6
BMDaly DC 3133 6692 bmzister math
cs100
13
DMSwitzer DC 3111 6200 dmswitze math
cs100
14
BMDaly DC 3133 6692 bmzister math
cs100
15
KAAnderson DC 3141 6656 kaanders math
cs100
16
BMDaly DC 3133 6692 bmzister math
cs100
21
DMSwitzer DC 3141 6656 kaanders math
cs100
22
KAAnderson DC 3141 6656 kaanders math
cs100
23
DMSwitzer DC 3111 6200 dmswitze math
cs100
24
BMDaly DC 3133 6692 bmzister math
cs100
25
DMSwitzer DC 3111 6200 dmswitze math
cs100
26
BMDaly DC 3133 6692 bmzister math
cs100
31
DMSwitzer DC 3111 6200 dmswitze math
cs100
32
BMDaly DC 3133 6692 bmzister math
cs200
22
DMSwitzer DC 3111 6200 dmswitze math DMSwitzer DC 3111 6200 dmswitze math
cs200
25
cs200
26
JCBeatty
DC 2109
4525
jcbeatty
math
cs200
34
JCBeatty
DC 2109
4525
jcbeatty
math
cs230
1
JCBeatty
DC 2109
4525
jcbeatty
math
•••
•••
•••
•••
•••
•••
•••
eg in a Course Offerings table:
CS 200 Spring 2018
SQL
Questions we may want to ask of the data
Queries
CS 200 Spring 2018 28 SQL
Different ways I may want to display the data
Reports
CS 200 Spring 2018 29 SQL
Album ID
• is a “primary key” for the Album Table because it uniquely identifies an album
• is a “foreign key” of the Songs Table
because it contains a primary key of the Album Table
and thus links a Song record to a unique Album record
“One-to-many” and “many-to-one”
• wrt Album ID
Albums is the “one table”
• Songs is the “many table” because for a given Album ID there is only ONE Album record
— but are (usually) MANY Song records
• “many-to-many” can happen, too
— though not by matching a primary key in each of two tables! — it is often useful
• we’ll see an example next week
Terminology
CS 200 Spring 2018 30 SQL
Things That Might Confuse You in FileMaker
You can define as many layouts as you want
• these are also called “views” of the data (SQL-speak) • give them meaningful names!!!
You don’t have to include all fields on all layouts
• defining a new field doesn’t cause it to appear on all layouts
• by default, a new field is placed on the “current layout,”
although there’s a preference to prevent that
In FileMaker
• data entry options are associated with a field’s definition
• the appearance of a field is specified on the layout, and can vary • other databases may choose different conventions
CS 200 Spring 2018 31 Database Introduction