Transcript Understanding Computers, Chapter 12

Databases
Creating databases to store information
Learning Objectives
1.
2.
3.
Explain what a database is, including common database
terminology, and list some of the advantages and
disadvantages of using databases.
Discuss some basic concepts and characteristics of data,
such as data hierarchy, entity relationships, and data
definition.
Describe the importance of data integrity, security, and
privacy, and how they affect database design.
Understanding
Computers: Today and
22
Learning Objectives
4.
5.
6.
7.
Identify some basic database classifications and discuss
their differences.
List the most common database models and discuss
how they are used today.
Understand how a relational database is designed,
created, used, and maintained.
Describe some ways databases are used on the Web.
3
Understanding
Computers: Today and
Breaking into database development
From SDLC, break into DDLC
1. Analysis
•
Who will use it and
how?
2. Design
•
•
What data should it
have?
Should it have
forms?
3. Develop
•
Creation
4. Implement
•
Get rid of the “bugs”
5. Maintain
•
Keeping it running
2 What Is a Database?


A collection of related data stored in a manner so it can
be retrieved as needed
Database management system (DBMS):


A system used to create, maintain, and access databases
DBMSs include:

Personal




Microsoft Access,
Corel Paradox
Lotus Approach
Enterprise databases



Oracle Database
IBM DB2
Microsoft SQL Server
5
Who uses databases?
Department of Motor Vehicles
Amazon – largest database on the Web
El Camino College
Search sites – Internet keyword search – Google,
Bing, Yahoo! Search, Microsoft Live Search, Ask.com
60% of web page connections linked from search
sites
Database Careers

Individuals involved with a DBMS:

Database designers:


Database administrators:




Designs and creates the database and its objects
Responsible for managing the databases within an organization
Makes sure they have disk space
Makes sure they are secure
Users:

Individuals who enter data, update data, and retrieve information out
of the database
How does a database work?
Databases and the Web
Databases are commonly used on the Web
Information retrieval, e-commerce, dynamic Web pages
(change based on user input), etc.
9
Database Hierarchy
Database: Contains a group of related tables
Tables: Contains collection of related records
Records(rows): Contains collection of related
fields in a database (all the fields for one
customer, for example)
Fields(columns): Single category of
data to be stored in a database (name,
telephone number, etc.)
Data(characters): letters,
numbers, symbols
10
Data Concepts and Characteristics

Relationships: Describe an association between two or
more tables

One-to-one (1:1) entity relationships (not common)


One-to-many (1:M) entity relationships (most common)


e.g. each school has one president and a president is only at one
school
e.g. a employee earns one or more paychecks and each paycheck is
for only one employee
Many-to-many (M:N) entity relationships (requires a third table
to tie the tables together)

e.g. a teacher teaches one or more students and a student has one or
more teachers
11
Tables

Relational databases arrange fields into structures
called tables


Each table contains a collection of fields (columns) – each field is
a particular piece of information
A set of related fields (collection of related information) is called a
record (row)
Relationships through Common fields

Each table may relate to one or more other tables in the
database through common fields with common values.
Primary Keys

Must have unique data (not duplicated)
Must have data that doesn’t change
Special meaning in a relational database
– it is used to connect 2 tables
Each record in a table should have a field/fields which make
that record unique and identifiable from the other records
Foreign Keys – how tables talk to each other

The common field in the related table is called a foreign key it links to the primary key column(s) in another table, thereby
creating a relationship.
Constraints


Manage the REFERENTIAL INTEGRITY of the data
Cascade Updates and Cascade Deletes

Eliminates orphan records
Fine-tuning the database design

Data definition: The process of describing the properties
of data to be included in a database table

During data definition, each field is assigned:





Name (must be unique within the table)
Data type (such as Text, Number, Currency, Date/Time)
Description (optional description of the field)
Properties (field size, format of the field, allowable range, if field is
required, etc.)
Finished specifications for a table become the table
structure
Keeping it Meaningful

Data integrity: The accuracy of data


Quality of data input determines the quality of retrieved
information (GIGO)
Data validation: Ensuring that data entered into the database is
valid


Record validation rules: Checks all fields before changes to a record
are saved
Can be enforced on a per transaction basis so the entire transaction
will fail if one part is invalid
Installing the Information System


After all programs and database are created, they are
tested to make sure they work well together
“Information System” is then ready to be used
Wrapping it up
•
Large companies(like Amazon, Home Depot, Kaiser, etc.) have an
Integrated Enterprise type of Information System
– Activities are designed to work together throughout the
enterprise
– Enterprise Resource Planning (ERP) system
• Large integrated system that ties together all of a business’s
activities
•
Most common ERP systems are Inventory related – track and
control inventory
•
•
Supply Chain Management (SCM) – reduce operating costs while
meeting delivery objectives
• deliver the right product, to the right place, at the right time, at the right
price
Product Lifecycle Management (PLM) – manages a product as it
moves through its life cycle, from design, to development, to
retirement
Wrapping it up
•
Information systems have a life cycle (FADDIM)
•
Systems Analyst studies business problem
•
•
uses tools such as ERD’s (entity-relationship diagrams), DFD’s
(data flow diagrams) and DT’s (decision tree’s) to collect and
analyze data
Programmer develops software
•
Uses tools such as SDK’s (software development kits) , API’s
(application programming interfaces), flowcharts to help create
the programs
Wrapping it up
•
It takes programs and data to make the information
system work
Objective: Business Intelligence through data mining
•
,
•