Chapter 10: Databases

1
1
1
1
Chapter 4 File Management, Virus Protection, and
Backup
1
Chapter 1 Computer, Internet, Web, and E-Mail
Basics
2
Chapter 10 Preview

• After this chapter, you should be able to
• Define basic database terminology, such as
  fields, records, record types, and cardinality
• Describe the characteristics of hierarchical,
  network, relational, and object-oriented
  databases
• Explain the capabilities of various data
  management tools, such as commercial
  applications, word processing software,
  spreadsheet software, file management software,
  and database management

3
Chapter 10 Preview

• Describe various ways to provide access to
  databases via the Web
• Explain how to design an effective relational
  database
• Use your knowledge of SQL queries to describe how
  to add records, delete records, search for
  information, update fields, and simultaneously
  access data from multiple tables

4
Chapter 10 Outline

• Section A
• File and Database Concepts
• Section B
• Data Management Tools
• Section C
• Database Design
• Section D
• SQL

5
Chapter 10Databases

• Section A File and Database Concepts

Computer Concepts 8th EditionParsons/Oja
6
Databases and Structured Files What is a
database?

• A database is a collection of information
• Databases are typically stored as computer files
• A structured file uses a uniform format to store
  data for each person or thing in the file

Page 514
7
Fields What is the basic element of a structured
file?

• A field contains the smallest unit of meaningful
  information
• Each field has a unique field name that describes
  its contents
• A field can be either variable length or fixed
  length

Page 514
8
Records What is a record?

• A record refers to a collection of data fields

Page 515
9
Relationships and Cardinality How do record
types pertain to files and databases?

• A data file that contains only one record type is
  often referred to as a flat file
• A database can contain a variety of different
  record types

Page 517
10
What are relationships?

• An association between data thats stored in
  different record types
• One important aspect of the relationship between
  record types is cardinality
• Cardinality refers to the number of associations
  that can exist between two record types
• A particular order cannot be placed jointly by
  two customers
• When one record is related to many records, the
  relationship is referred to as a one-to-many
  relationship

Page 517
11
What are relationships?
Page 518
12
Whats a relational database?

• Stores data in a collection of related tables
• Each table (also called a relation) is a
  sequence, or list, of records.
• All of the records in a table are of the same
  record type

Page 520
13
Whats a relational database?
Page 520
14
Whats an object-oriented database?

• Stores data as objects, which can be grouped into
  object classes, and defined by attributes and
  method
• A class specifies the attributes and methods that
  are shared by all objects in a class
• A class attribute is equivalent to a field, and
  contains the smallest unit of data
• A method is any behavior that the object is
  capable of performing

Page 521
15
Whats an object-oriented database?
Page 521
16
Chapter 10Databases

• Section B Data Management Tools

Computer Concepts 8th EditionParsons/Oja
17
Data Management Software Are simple data
management tools available?

• The simplest tools for managing data are software
  packages designed for a specific data management
  task
• To use one of these tools, you just enter your
  data
• The software provides menus that allow you to
  manipulate your data after it is entered

Page 522
18
How about a simple, generic tool that allows me
to define a file structure?
Page 522
19
How about a simple, generic tool that allows me
to define a file structure?
Page 523
20
Database Management Systems What kinds of tools
are specifically designed for creating and
manipulating databases?

• DBMS (database management system) refers to
  software that is designed to manage data stored
  in a database
• An XML DBMS, for example, is optimized for
  handling data that exists in XML format
• An OODBMS (object-oriented database management
  system) is optimized for the object-oriented
  database model

Page 525
21
What kinds of tools are specifically designed for
creating and manipulating databases?

• An RDBMS (relational database management system)
  allows you to create, update, and administer a
  relational database
• Todays most popular RDBMS software also provides
  capability to handle object classes and XML data

Page 525
22
What kinds of tools are specifically designed for
creating and manipulating databases?
Page 525
23
Databases and the Web Is it possible to access a
database over the Web?

• The Web provides both opportunities and
  challenges for accessing the information in a
  database
• The Web provides an opportunity for many people
  to gain access to data from many locations
• Web access is constrained by the stateless nature
  of HTTP
• Web access to hierarchical, network, relational,
  and object-oriented databases is

Page 527
24
Whats the simplest way to provide Web-based
access to a database?
Page 527
25
What if I want to provide access to current data?
Page 528
26
Is it possible to add and update database records
via the Web?
Page 529
27
How do I create server-side programs?

• Several tools, including ASP, CGI, and PHP help
  you create server-side programs
• ASP (Active Server Pages) technology can be used
  to generate an HTML document that contains
  scripts
• Scripts are small embedded programs that can be
  designed to get user input, run queries, and
  display query results
• CGI (Common Gateway Interface) provides a
  non-proprietary way to create HTML pages based on
  data in a database

Page 529
28
How do I create server-side programs?
Page 530
29
XML Documents How does XML relate to the Web and
databases?

• XML is a markup language that allows field tags,
  data, and tables to be incorporated into a Web
  document
• HTML documents contain lots of information
• XML provides tags that can be embedded in an XML
  document to put data in context

Page 530
30
How is XML typically used today?

• XMLs most positive contributions to data
  management is the ability to add context to the
  information contained in a widely diverse pool of
  documents on the Web
• An XML document can also contain structured data
  organized into records and fields
• Storing data in an XML document provides several
  advantages
• It is portable

Page 531
31
How is XML typically used today?
Page 531
32
Chapter 10Databases

• Section C Database Design

Computer Concepts 8th EditionParsons/Oja
33
Defining Fields How does a database designer
know what data to store?

• The first step in designing a relational database
  is to determine what data must be collected and
  stored
• A database designer might begin by listing
  available data, as well as any additional data
  that is necessary to produce on-screen output or
  printed reports
• The next step is to organize that data into fields

Page 533
34
Why are last names stored in a different field
than first names?

• The treatment of first and last names illustrates
  the concept of breaking data into fields
• With the entire name in one field, the database
  would not be able to access individual parts of
  the name

Page 533
35
What makes each record unique?

• A computer must have some way to differentiate
  between records
• A primary key is a field that contains data
  unique to a record

Page 534
36
How does a database designer know what data types
to use?

• The data that can be entered into a field depends
  on the fields data type
• From a technical perspective, a data type
  specifies the way data is represented on the disk
  and in RAM
• From a user perspective, the data type determines
  the way that data can be manipulated
• The two most common data types are numeric and
  character

Page 534
37
How does a database handle computations?

• A computed field is a calculation that a DBMS
  performs during processing
• An efficiently designed database uses computed
  fields whenever possible

Page 535
38
Can a database designer prevent people from
entering inaccurate data?

• The information supplied by reports and
  processing routines is only as accurate as the
  information in the database
• Data entry errors can compromise the accuracy and
  validity of a database
• Most DBMS tools that the database designer can
  use to prevent some, but not all, data entry
  errors
• In a case sensitive database, uppercase letters
  are not equivalent to their lowercase counterparts

Page 536
39
Can a database designer prevent people from
entering inaccurate data?
Page 536
40
Normalization How does a database designer group
fields into tables?

• Normalization helps the database designer create
  a database structure that can save storage space
  and increase processing efficiency
• The goal of normalization is to minimize data
  redundancy
• The first step to grouping fields is to get an
  idea of the big picture of the data
• Groupings correspond to the physical items, or
  entities, that are tracked in the database

Page 537
41
How does a database designer group fields into
tables?

• This data redundancy not only requires extra
  storage space, but also may lead to storing
  inconsistent or inaccurate data
• The solution is to create separate tables
• If the designer provides fields for ordering ten
  items, the database cannot handle large orders
  for more than ten

Page 538
42
How does a database designer group fields into
tables?
Page 538
43
How does a database designer group fields into
tables?

• If a customer orders fewer than ten, space is
  wasted by having empty fields in each record
• A one-to-many relationship exists between an
  order and the ordered items
• The database designer should separate the data
  into two tables such as Orders and OrderDetails

Page 538
44
How does a database designer group fields into
tables?
Page 538
45
What happens when the data in a table is sorted?

• A tables sort order is the order in which
  records are stored on disk
• Sorted tables typically produce faster queries
  and updates
• Queries and updates within an unsorted database
  are slow
• A tables sort key is one or more fields that are
  used to specify where new records are inserted in
  a table
• A table can have only one sort key at a time, but
  the sort key can be changed

Page 539
46
How is indexing different from sorting?

• A database index contains a list of keys, and
  each key provides a pointer to the record that
  contains the rest of the fields related to that
  key
• An index has no bearing on the physical sequence
  of records on disk
• A table can have multiple indexes, but only one
  sort order
• Database tables should be indexed by any field or
  fields that are commonly used as search fields
• The database designer typically creates indexes
  at the time the database structure is designed

Page 539
47
Designing the Interface Does a database designer
have control over the user interface?

• The way that database records, queries, and
  reports appear on the screen depends on its user
  interface
• A professional user interface designer typically
  creates and maintains the user interface
• Large databases may even require a group of user
  interface designers to maintain the user
  interface
• The user interface for smaller databases is most
  likely created by the database designer

Page 540
48
Designing Report Templates How can I display or
print data as a formatted report?

• A report generator is a software tool that
  provides the ability to create report templates
  for a database
• A report template contains the outline or general
  specifications for a report
• The template does not, however, contain data from
  the database
• Data is merged into the template when you
  actually run a report
• When you actually produce a report, it is based
  on the data currently contained in the database
  table

Page 542
49
How can I display or print data as a formatted
report?
Page 543
50
How does the database designer create effective
report templates?

• The database designer can create templates for
  reports that effectively present information by
  observing the following guidelines
• Supply only the information required
• Present information in a usable format
• Information should be timely

Page 543
51
How does the database designer create effective
report templates?

• Information should be presented in a clear,
  unambiguous format, and include necessary titles,
  page numbers, dates, labels, and column headings
• Present information in the format most
  appropriate for the audience

Page 543
52
Loading Data How is data loaded into the
database tables?

• Data can be loaded into a database manually by
  using generic data entry tools supplied with the
  DBMS, or by using a customized data entry module
  created by the database designer
• If the data exists electronically in another type
  of database file or in flat files, it is usually
  possible to transfer the data using a
  custom-written conversion routine, or import and
  export routines

Page 544
53
How is data loaded into the database tables?

• A conversion routine converts the data from its
  current format into a format that can be
  automatically incorporated into the new database
• An import routine brings data into a database
• An export routine copies data out of a software
  package, and into the database
• Typically, you would use either an import routine
  or an export routine, but not both

Page 544
54
Chapter 10Databases

• Section D SQL

Computer Concepts 8th EditionParsons/Oja
55
SQL Basics How does a query language like SQL
work?

• Work behind the scenes as an intermediary between
  the database client software provided to users,
  and the database itself
• The client software collects your input, then
  converts it into an SQL query, which can operate
  directly on the database to carry out your
  instructions

Page 546
56
How does a query language like SQL work?
Page 546
57
What does a simple SQL query look like?

• An SQL query is a sequence of words, much like a
  sentence
• SELECT TrackTitle FROM Tracks WHERE TrackTitle
  Fly Away
• The SQL query language provides a collection of
  special command words called SQL keywords
• Most SQL queries can be divided into three simple
  elements that specify an action, the name of a
  database table, and a set of parameters

Page 547
58
How does SQL specify the action that I want
carried out in the database?
Page 547
59
How does SQL specify which table to use?

• SQL keywords such as USE, FROM, or INTO can be
  used to construct a clause specifying the table
  that you want to access.
• The clause consists of the keyword followed by
  the name of the table

Page 547
60
How does SQL specify parameters?

• The term parameter refers to the detailed
  specifications for a command
• Keywords such as WHERE usually begin an SQL
  clause containing the parameters for a command

Page 548
61
Adding Records How are records added to a
database?

• The client software that you use collects the
  data that you enter in the form and generates an
  SQL statement using the INSERT command, which
  adds your data to the database

Page 548
62
Searching for Information How do SQL queries
carry out searches?

• One of the most common database operations is to
  query for a particular record or group of records
  using the SELECT command

Page 549
63
Can SQL perform complex searches?

• SQL uses search operators to form complex queries
• Because search operators were originally the idea
  of mathematician George Boole, they are also
  referred to as Boolean operators

Page 549
64
How does AND work in an SQL query?

• AND (sometimes indicated by a sign) is used
  when you want to retrieve records that meet more
  than one criteria
• The AND operator specifies that both of the
  search criteria must be true for the record to be
  selected
• SELECT CDName FROM CompactDisksWHERE ArtistName
  Beyonce AND DiscountPrice

Page 549
65
How does OR differ from AND?

• Inclusive ORExclusive OR
• SELECT CDName FROM CompactDisksWHERE (ArtistName
  BeyonceOR ArtistName Destinys
  Child)AND DiscountPrice
• Note the use of parentheses around the OR clause

Page 550
66
How does OR differ from AND?

• Parentheses tell the DBMS to process this part of
  the search criteria first
• The placement of parentheses can change the
  results of a query, sometimes drastically
• SELECT CDName FROM CompactDisksWHERE ArtistName
  BeyonceOR (ArtistName Destinys Child
  AND DiscountPrice

Page 550
67
How does NOT work in an SQL query?

• The NOT operator can be used to specify a
  not-equal relationship
• SELECT CDName FROM CompactDisksWHERE
  NOT(ArtistName Beyonce)
• Sometimes NOT relationships are specified using a
  not-equal operator, like or !, depending on
  the specifications of the query language
• SELECT CDName FROM CompactDisksWHERE ArtistName
  Beyonce

Page 550
68
Updating Fields Can I change the contents of a
record?

• You can change records in a database only if you
  have the rights to do so
• UPDATE CompactDisksSET QtyInStock QtyInStock
  1WHERE CDName Dangerously in Love

Page 551
69
Is it possible to update a group of records?

• SQL can perform a global update that changes the
  data in more than one record at a time
• It would be easier to change all of the records
  with a single command
• UPDATE CompactDisksSET DiscountPrice
  9.95WHERE ArtistName The Rolling Stones
• The UPDATE command means that you want to change
  the data in some or all of the records

Page 551
70
Joining Tables How is data retrieved from more
than one table at a time?

• Creating a relationship between tables is
  referred to as joining tables
• To take advantage of the relationship between two
  tables, you first have to join the tables
• The SQL JOIN command allows you to temporarily
  join and simultaneously access the data in more
  than one table

Page 552
71
How is data retrieved from more than one table at
a time?
Page 552
72
How extensive is SQL?

• Very extensive and powerful language that can be
  used not only to manipulate data, but to create
  databases, tables, and reports

Page 553
73
Conclusion

• You should now be able to
• Define basic database terminology, such as
  fields, records, record types, and cardinality
• Describe the characteristics of hierarchical,
  network, relational, and object-oriented
  databases
• Explain the capabilities of various data
  management tools, such as commercial
  applications, word processing software,
  spreadsheet software, file management software,
  and database management

74
Conclusion

• Describe various ways to provide access to
  databases via the Web
• Explain how to design an effective relational
  database
• Use your knowledge of SQL queries to describe how
  to add records, delete records, search for
  information, update fields, and simultaneously
  access data from multiple tables