Computer Concepts 8

1
??????
2
1
1
1
Chapter 4 File Management, Virus Protection, and
Backup
1
Chapter 1 Computer, Internet, Web, and E-Mail
Basics
3
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

4
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

5
Chapter 10 Outline

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

6
Chapter 10Databases

• Section A File and Database Concepts
• ?????????

Computer Concepts 8th Edition
7
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
8
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
9
Records What is a record?

• A record refers to a collection of data for
  someone or something
• A record is made of fields of individual pieces
  of data for each person or thing

Page 515
10
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
• In contrast, a database can contain a variety of
  different record types

Page 517
11
What are relationships?

• An association between data thats stored in
  different record types (a field in one table can
  refer to a record in another Table)
• 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
12
What are relationships?
???
Page 518
13
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

Not Relationship
Page 520
14
Whats a relational database?
Page 520
15
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
16
Whats an object-oriented database?
Page 521
17
Chapter 10Databases

• Section B Data Management Tools
• ??????

Computer Concepts 8th Edition
18
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
19
How about a simple, generic tool that allows me
to define a file structure?
Page 522
20
How about a simple, generic tool that allows me
to define a file structure?
Page 523
21
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
22
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
23
What kinds of tools are specifically designed for
creating and manipulating databases?
Page 525
24
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
25
Whats the simplest way to provide Web-based
access to a database?
Page 527
26
What if I want to provide access to current data?
Page 528
27
Is it possible to add and update database records
via the Web?
Page 529
28
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
29
How do I create server-side programs?
Page 530
30
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
31
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
32
How is XML typically used today?
Page 531
33
Chapter 10Databases

• Section C Database Design
• ?????

Computer Concepts 8th Edition
34
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
35
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
36
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
• Examples SSN, RUID, not lastname

Page 534
37
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, e.g.
  addition
• The two most common data types are numeric and
  character

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

• Real, e.g. 3.14, 5.99
• Integer, e.g. 0, 1, 2345
• Date, e.g. 11/10/2005
• Text, e.g. Gilbert, Grape (fixed length)
• Logical, e.g. yes/no, on/off, opened/closed
• Memo, Text with no fixed length
• BLOB, binary files, e.g. MSWord.exe, itec100.xls,
  me.gif
• Hyperlink, e.g. www.radford.edu/kstevens2

Page 534
39
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
• Example MSRP, DiscountPrice, (Savings)

Page 535
40
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, GIGO
• 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, e.g. require a decimal point, 3.00, or
  must be numeric
• In a case sensitive database, uppercase letters
  are not equivalent to their lowercase
  counterparts, e.g. Jewel, jewel, JEWEL

Page 536
41
Can a database designer prevent people from
entering inaccurate data?

• Field validation rule, range 0.00 -100.00
• Pick from a list
• Look up routine
• Field format

Page 536
42
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
  (nouns), or entities, that are tracked in the
  database

Page 537
43
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
44
How does a database designer group fields into
tables?
Page 543
45
How does a database designer group fields into
tables?
Page 538
46
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
47
How does a database designer group fields into
tables?
Page 538
48
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, which is slow

Page 539
49
How is indexing different from sorting?
Figure 10-36

• 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
50
What makes a good interface?

• Arrange fields in a logical order
• Provide visual clues, e.g. color
• Provide easy-to-use controls, e.g. buttons, lists
• Scroll only if absolutely necessary
• Provide instructions/help, e.g. Help System,
  ToolTips
• Provide appropriate Field Labels

Page 540
51
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
52
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
53
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
54
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

Page 544
55
Chapter 10Databases

• Section D SQL
• ???????-SQL

Computer Concepts 8th Edition
56
How does a query language work?

• Structured Query Language, SQL
• Work behind the scenes as an between the client
  software provided to users, and the database
  itself
• Intermediary database client software collects
  your input, then converts it into an SQL query
• An SQL query operates directly on the database to
  carry out your instructions

Page 546
57
How does a query language like SQL work?
SQL Query
Page 546
58
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
• Many 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
59
How does SQL specify the action that I want
carried out in the database?
Page 547
60
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
61
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
62
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
63
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
64
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
65
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 lt 10.00

Page 549
66
How does OR differ from AND?

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

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 ltgt or !, depending on
  the specifications of the query language
• SELECT CDName FROM CompactDisksWHERE ArtistName
  ltgt 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
• Can be used to
• Manipulate data
• 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