Transcript csis115Lecture2

CSIS 115
Database Design and
Applications for Business
Dr. Meg Fryling
“Dr. Meg”
Fall 2012
@SienaDrMeg
#csis115
Agenda
• Questions?
– Syllabus, Course Schedule, etc.
•
•
•
•
Homework
Spreadsheet vs Database revisited (cont.)
Chapter 1(Briefly)
Chapter 2
Homework
• Homework P1
– Was due at the beginning of class!
– Let’s finish intros.
• Homework 1
– Due Wednesday (9/12) by beginning of class
• Project – Part I
– Due Monday (9/17) by beginning of class
• Finish Reading Chapter 1, 2 (Skip 48-56 and 63-82),
& Appendix A (Online)
– http://wps.prenhall.com/bp_kroenke_database_12/
• Start Reading Chapters 5 and 3 (100-105 only)
1-3
Vet Clinic Spreadsheet
Why is this not a good solution?
1-4
Database Benefits
• Databases allow us to organize data in a
structured way
• Eliminate (or at least significantly reduce)
data duplication
• Database user interfaces help Prevent
Data Errors
1-5
Components of a Database System
with SQL
Homework 1
KROENKE AND AUER - DATABASE PROCESSING, 12th Edition
© 2012 Pearson Prentice Hall
1-6
Applications, the DBMS, and SQL
• Applications are the computer programs
that users work with.
• The Database Management System
(DBMS) creates, processes, and
administers databases.
• Structured Query Language (SQL) is an
internationally recognized standard
database language that is used by all
commercial DBMSs.
KROENKE AND AUER - DATABASE PROCESSING, 12th Edition
© 2012 Pearson Prentice Hall
1-7
Structure of the Vet Database
Assignments > In-Class Activities
> DB vs SS > Vet Clinic DB
1-8
The Tables Where the data is stored
Students – Refer to the database relationships as I advance through the next 3 slides.
1-9
ClientID is a…
A. Table
B. Field
C. Huh?
1-10
ClientID in the CLIENTS table is…
A. A primary key
B. A surrogate key
C. A foreign key
D. An alternate key
E. What?
1-11
AnimalID in the VISITS table is…
A. A primary key
B. A surrogate key
C. A foreign key
D. An alternate key
E. No clue
1-12
What type of relationship exists
between the CLIENTS and
ANIMALS tables?
A. One-to-one (1:1)
B. One-to-Many (1:N)
C. Many-to-Many (M:N)
D. None of the above
E. Does she really expect us to know this
stuff??
1-13
Data in Tables
What is the relationship
between STUDENT and
CLASS?
KROENKE AND AUER - DATABASE PROCESSING, 12th Edition
© 2012 Pearson Prentice Hall
1-14
The Key Characteristic of
Databases: Related Tables
KROENKE AND AUER - DATABASE PROCESSING, 12th Edition
© 2012 Pearson Prentice Hall
1-15
Naming Conventions in this
Textbook
• Table names are written with all capital
letters:
– STUDENT, CLASS, GRADE
– Typically singular not plural
• Column names are written with an initial
capital letter, and compound names are
written with a capital letter on each word:
– Term, Section, ClassNumber, StudentName
KROENKE AND AUER - DATABASE PROCESSING, 12th Edition
© 2012 Pearson Prentice Hall
1-16
Databases Create Information
• Data = recorded facts and figures
• Information = knowledge derived from
data
• Databases record data, but they do so in
such a way that we can produce
information from the data.
– The data on STUDENTs, CLASSes, and
GRADEs could produce information about
each student’s GPA.
KROENKE AND AUER - DATABASE PROCESSING, 12th Edition
© 2012 Pearson Prentice Hall
1-17
The Startup Screen: Switchboard Main
Menu
1-22
The Registration Screen
1-23
DB – Data Validation
• Add yourself as new client with the
following info:
– Zip Code: 02569
– Phone Number: 518 459 87765
– Give each animal (pet) at least one visit date.
– For the appointment dates try: 12/35/2004
and 6/10/1905.
– For the cost of the visit make one entry of
over $500.
1-24
Which of these did you observe while
entering data?
A. Impossible date was detected
B. Too many digits in a phone number was
detected
C. Unlikely bill amount was detected
D. Leading zero kept in zip code
E. All of the above
1-25
Which is true about entering a new client,
pets, and vet visits?
A. Client data had to be duplicated for each
pet.
B. Pet data had to be duplicated for each
visit.
C. Data did not need to be duplicated.
D. Data was automatically entered for each
pet.
E. None of the above
1-26
What difference did you observed between
entering data into a Spreadsheet vs. a
Database?
A. Database automatically corrected the
data entered.
B. Spreadsheet applied no required format
checks, but the Database did.
C. Database did not prevent possible errors,
while the Spreadsheet did.
D. There were no real differences between
them.
1-27
E. None of the above
In order to prevent errors in data entry, what
must be true for the database?
A. Each data field has to have a proper data.
B. The database must hold additional data about
each field telling what constitutes correct format
for that field.
C. The database has to have the ability to undo
the entered data
D. The database has to have a way of storing
correct data.
E. I have no idea!
1-28
Database Applications: Queries
and Reports
• “Cats only” Query
– Design View
– SQL View
– How can we make this query better?
• “Clients with Cats” Report
– Where is this data coming from?
– Advantages over a spreadsheet?
1-29
What Is Microsoft Access?
• Microsoft Access is a DBMS
• Creates, processes, and administers
Microsoft Access databases.
• PLUS an application generator
– The application generator includes query
(QBE), form, and report components.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
1-36
The Relational Database Model
• The dominant database model is the
relational database model—all current
major DBMS products are based on it.
• It was created by IBM engineer E. F.
Codd in 1970.
• It was based on mathematics called
relational algebra.
• This text examines and explains the
relational database model.
KROENKE AND AUER - DATABASE PROCESSING, 12th Edition
© 2012 Pearson Prentice Hall
1-43
Chapter 2
• Let’s take a look at the “language” we use
to “talk” with relational databases
• Chapter 2 (skip 48-56 and 63–82)
1-49
Components of a Database System
with SQL
KROENKE AND AUER - DATABASE PROCESSING, 12th Edition
© 2012 Pearson Prentice Hall
1-50
Structured Query Language
• Acronym: SQL
• Pronounced as “S-Q-L” [“Ess-Que-El”]
– Also pronounced as “Sequel”
• Originally developed by IBM as the
SEQUEL language in the 1970s
• SQL-92 is an ANSI national standard
adopted in 1992
• Newer versions exist, and incorporate
XML and some object-oriented concepts.3-51
SQL as a Data Sublanguage
• Not a full featured programming
language
– C, C#, Java
• Data sublanguage for creating and
processing database data and metadata
• Ubiquitous in enterprise-class DBMS
products
• A critical skill!
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-52
SQL DDL and DML
• SQL statements can be divided into two
categories:
– Data definition language (DDL) statements
• Used for creating database structures
– tables, relationships (constraints), and other structures.
• Covered in Chapter 7.
– Data manipulation language (DML)
statements
• Used for data retrieval and modification queries.
• These are the type of queries you used in CSIS114!
• Covered in this chapter (Chapter 2).
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-53
The SQL SELECT Statement
• The fundamental framework for SQL query
states is the SQL SELECT statement.
– SELECT
– FROM
– WHERE
{ColumnName(s)}
{TableName(s)}
{Conditions}
• All SQL statements end with a semi-colon
(;).
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-54
Let’s Explore the Cape Codd Retail
Sales Data Mart Database
• Cape Codd Outdoor Sports is a fictitious
company based on an actual outdoor retail
equipment vendor.
• The Cape Codd marketing department needs an
analysis of in-store sales.
• The entire database is not needed for this, only
an extraction of retail sales data.
• The data is extracted by the IS department from
the operational database into a separate, offline database for use by the marketing
department.
– Data Mart
– Data Warehouse
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-55
In-Class Activity: SQL Basics
• Assignments > In-Class Activity > SQL
Basics
• Download the “SQL Basics.docx” and
“Cape-Codd.accdb” files to your CSIS115
folder on the CS Server (Z Drive).
• Open SQL Basics.docx
– We will use shortly
• Open the Cape-Codd.accdb database
1-56
Retail Sales Extract Tables
Note: your DB
has more than
3 tables
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-57
SQL Basics
In-Class Activity
Part I Only
Open “SQL Basics.docx”
1-58