Transcript Database - WVU CS101

INTRODUCTION TO
DATABASE SYSTEMS
TERMINOLOGY
• The term "database" refers to the
collection of related records. It is just data!
• A database management system
(DBMS) is a software package with
computer programs that controls the
creation, maintenance, and use of a
database.
• The database data collection with DBMS
is called a database system.
BUT REALLY WHAT IS A
DATABASE?
• Databases are used in real life, most of you use
them without realizing it
o WVU Directory – uses databases
o Facebook – uses databases
o Google – uses databases
• Every time you are asked to input data,
you’re accessing a database
AN EXAMPLE
• Lets look at the WVU Directory page.
What you see is sometimes called a Form – it is
nothing more than a friendly front end interface.
WVU DIRECTORY
WVU DIRECTORY
• Say we want to search for all students
named John Smith
• When you hit search it will run a query
that looks like this:
SELECT * FROM Students
WHERE (((Students.FirstName)="John")
AND ((Students.LastName)="Smith"))
AN EXAMPLE – ISOLATING
RESULTS
• WVU has a database somewhere that lists all
students, when we do a search we search that
database for all students who have a first name
John AND a last name Smith.
• We could either get exactly one result, no results
or multiple results.
QUESTION
• This brings up the question, how do we
differentiate between the John Smith that
is a Math major and the John Smith that is
a Education major in our database?
• One way could be to use email addresses,
since we hope everyone has a “unique”
email address. But, is there another
“unique” identifier that separates one
person from another?
Student ID
FirstName
LastName
Major
Email
A123
John
Smith
Math
[email protected]
B122
Jane
Smith
Finance
[email protected]
• In this example we can see why using LastName
as a “unique” way to identify students maybe a
bad idea.
• StudentID, FirstName, LastName, Major, Email
are called Fields. Each entry is called a Record,
a collection of records make up a Table. A
collection of tables make up a database.
• The Field StudentID has a special name called
Primary Key since it uniquely identifies each
student, no two students can have the same
StudentID.
WHAT IS ACCESS?
• Is a relational database management system
from Microsoft.
• Allows the user to create relationships between
tables
• It comes together with Graphical User
Interface (GUI).
• Access is NOT a database – it is a database
management system. There are numerous
other database management systems.
ACCESS VS EXCEL
When to use Access?
• When you need a relational database to store your data or
you anticipate adding more tables.
• When you have a large amount of data
• Rely on external databases to derive and analyze the data
you need
• Need to maintain constant connectivity to an external
database
• Need to regroup data from different tables in a single place
with complex queries
• Have many people working in the database and need strong
options to update the data
ACCESS VS EXCEL
When to use Excel?
• Require a flat or ‘non relational’ view of your data
• Want to run calculations or statistical comparisons on
your data
• Know your data set is manageable (no more than 15,000
rows)
12
ACCESS OR EXCEL
• Depending on the type of data that you are
managing and what you want to do with
that data.
• If your goal is to maintain data integrity in
a format that can be accessed by multiple
users, Access is your best choice,
whereas Excel is better suited for complex
numerical data that you want to analyze in
depth.
THE HOME TAB
HOME Tab – the basic Access tab. Contains basic
editing functions such as cut and paste along
with most formatting actions.
Views
Clipboard
Text Formatting
Records
Sort & Filter
Find
14
THE CREATE TAB
CREATE Tab – Brings together all create
operations in one area. Includes ability to create
queries through the wizard or in Design view.
Tables
Queries
Forms
Reports
15
THE EXTERNAL DATA TAB
EXTERNAL DATA Tab – Contains all operations to
facilitate collaborations and data exchange.
Import & Link
Export
Collect Data
16
THE DATABASE TOOLS TAB
DATABASE TOOLS Tab – The area that contains
the operational backbone of Access. Here you
create and maintain relationships of the
database. You also analyze the file performance
and perform routine maintenance.
– Tools
– Relationships
17
BASIC ELEMENTS OF A
DATABASE
Field – is a basic entry or a data element, such as the
name or a book or the telephone number. A field
that uniquely identifies a record is called a Primary
Key.
Record – is a complete set of all of the data (fields)
pertaining to one person, place, etc
Table – is a collection of records, every table contains
the same fields in the same order
Database – consists of one or more tables and the
supporting objects used to get data from those
tables.
18
OBJECTS
• Objects are the components that make a
database function.
• Four most commonly used object types
– Tables
– Queries
– Forms
– Reports
• All these objects can be created and
managed from Create tab.
COMPARING ACCESS TO EXCEL
A FIELD in Access is equivalent to a COLUMN NAME
in Excel
A RECORD in Access is equivalent to a ROW in Excel
A TABLE in Access is equivalent to a WORKSHEET in
Excel
A DATABASE in Access is equivalent to a
WORKBOOK in Excel