INFS205

1

• INFS205
• Spring 2004

Computer Concepts 6th EditionParsons/Oja Course
Technologies
2
Databases
File And Database Concepts
Section A
CHAPTER 10
PARSONS/OJA
Page 492
3
Databases
Chapter 10
PREVIEW

• When you complete this powerpoint 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

Page 493
4
Databases
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

Page 493
5
File and Database Concepts
Section A
Chapter 10
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 is similar to a card file or
  Rolodex because it uses a uniform format to store
  data for each person or thing in the file
• A field contains the smallest unit of meaningful
  information, so you might call it the basic
  building block for a structured database
• Each field has a unique field name that describes
  its contents
• A field can be either variable length or fixed
  length.

Page 494
6
File and Database Concepts
Section A
Chapter 10
What is the basic element of a structured file?

• A field can be either variable length or fixed
  length.
• A variable-length field is like an accordionit
  expands to fit the data you enter, up to some
  maximum number of characters
• A fixed-length field contains a predetermined
  number of characters (bytes)
• The data that you enter in a fixed-length field
  cannot exceed the allocated field length

Page 495
7
File and Database Concepts
Section A
Chapter 10
What is the basic element of a structured file?
Page 495
8
File and Database Concepts
Section A
Chapter 10
Records What is a record?

• A record refers to a collection of data fields

Page 495
9
File and Database Concepts
Section A
Chapter 10
Who defines the structure for the records in a
file?

• The person who creates a data file defines the
  fields it contains
• Each kind of record is referred to as a record
  type
• A record type is similar to a blank form, and it
  is usually shown without any data in the fields
• A record that contains data is referred to as a
  record occurrence, or simply a record.

Page 496
10
File and Database Concepts
Section A
Chapter 10
Who defines the structure for the records in a
file?
Page 496
11
File and Database Concepts
Section A
Chapter 10
Who defines the structure for the records in a
file?
Page 496
12
File and Database Concepts
Section A
Chapter 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 497
13
File and Database Concepts
Section A
Chapter 10
What are relationships?

• In database jargon, a relationship is 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
• When one record is related to many records, the
  relationship is referred to as a one-to-many
  relationship

Page 497
14
File and Database Concepts
Section A
Chapter 10
What are relationships?
Page 497
15
File and Database Concepts
Section A
Chapter 10
What are relationships?

• many-to-many relationship
• one-to-one relationship
• Relationships between record types can be
  graphically depicted using diagramming
  techniques, such as an entity-relationship
  diagram, sometimes called an ER diagram or
  ERD.

Page 497-498
16
File and Database Concepts
Section A
Chapter 10
What are relationships?
Page 498
17
File and Database Concepts
Section A
Chapter 10
Whats a relational database?

• 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 500
18
File and Database Concepts
Section A
Chapter 10
Whats a relational database?
Page 500
19
File and Database Concepts
Section A
Chapter 10
Whats a relational database?

• A row of the table is called a tuple, and is
  equivalent to a record
• The columns in the table are called attributes,
  and are equivalent to fields
• In a relational database, relationships are
  specified through the use of common data stored
  in the fields of records in different tables

Page 500
20
File and Database Concepts
Section A
Chapter 10
Whats a relational database?
Page 500
21
File and Database Concepts
Section A
Chapter 10
Whats an object-oriented database?

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

Page 501
22
Databases
Database Design
Section C
CHAPTER 10
PARSONS/OJA
Page 513
23
Database Design
Section C
Chapter 10
Define 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 513
24
Database Design
Section C
Chapter 10
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, making it difficult to alphabetize
  customers by last name, or produce a report in
  which names appear in a format like Grape,
  Gilbert B

Page 513
25
Database Design
Section C
Chapter 10
Why are last names stored in a different field
than first names?
Page 513
26
Database Design
Section C
Chapter 10
How does a data base designer know what data
types to use?

• The data that can be entered into a field depends
  on the fields data type
• Data Type
• technical perspective
• user perspective
• The two most common data types are ? And ?
• numeric data type

Page 514
27
Database Design
Section C
Chapter 10
How does a data base designer know what data
types to use?
Page 514
28
Database Design
Section C
Chapter 10
How does a data base designer know what data
types to use?

• There are two main numeric types ? and ?
• character data type, a.k.a. string data type.
• Additional types
• date data type
• logical data type
• memo data type
• BLOB

Page 514
29
Database Design
Section C
Chapter 10
How does a database handle computations?

• A computed field is a calculation that a DBMS
  performs during processing, and then temporarily
  stores in a memory location.
• An efficiently designed database uses computed
  fields whenever possible because they do not
  require disk storage space

Page 515
30
Database Design
Section C
Chapter 10
How does a database handle computations?
Page 515
31
Database Design
Section C
Chapter 10
How can a database designer prevent people from
entering inaccurate data?

• Help prevent errors
• case sensitive database
• field format
• field validation rule
• lists

Page 515-516
32
Database Design
Section C
Chapter 10
How can a database designer prevent people from
entering inaccurate data?
Page 516
33
Database Design
Section C
Chapter 10
Normalization How does a database designer
group fields into tables?

• When designing a database, a process called
  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
• It is necessary to use two tables to store data

Page 517
34
Database Design
Section C
Chapter 10
How does a database designer group fields into
tables?
Page 517
35
Database Design
Section C
Chapter 10
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 518
36
Database Design
Section C
Chapter 10
How does a database designer group fields into
tables?
Page 518
37
Database Design
Section C
Chapter 10
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 518
38
Database Design
Section C
Chapter 10
How does a database designer group fields into
tables?
Page 518
39
Database Design
Section C
Chapter 10
Organizing Records How are database records
organized?

• Database tables can be organized in different
  ways depending on how people want to use them
• No single way of organizing the data accommodates
  everyones needs, but tables can be sorted or
  indexed in multiple ways

Page 519
40
Database Design
Section C
Chapter 10
What happens when the data table is sorted?

• 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
• Why sort?
• Sorted tables typically produce faster queries
  and updates
• Queries and updates within an unsorted database
  are slow
• 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

Page 519
41
Database Design
Section C
Chapter 10
How is indexing different from sorting?
Page 519
42
Database Design
Section C
Chapter 10
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 522-523
43
Database Design
Section C
Chapter 10
How can I display or print data as a formatted
report?
Page 522
44
Database Design
Section C
Chapter 10
How can I display or print data as a formatted
report?
Page 523
45
Database Design
Section C
Chapter 10
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
• Present only the information required
• Present information in a usable format
• Information should be timely

Page 523
46
Database Design
Section C
Chapter 10
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, dates, labels, and column
  headings
• Present information in the format most
  appropriate for the audience

Page 523
47
Database Design
Section C
Chapter 10
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 524
48
Database Design
Section C
Chapter 10
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 524
49
Databases
SQL
Section D
CHAPTER 10
PARSONS/OJA
Page 526
50
SQL
Section D
Chapter 10
SQL Basics How does a query language like SQL
work?

• Query languages like SQL typically 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 526
51
SQL
Section D
Chapter 10
How does a query language like SQL work?
Page 526
52
SQL
Section D
Chapter 10
What does a simple SQL query look like?

• 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
• Imperative Statement
• An SQL query is a sequence of words, much like a
  sentence
• SELECT TrackTitle FROM Tracks WHERE TrackTitle
  Fly Away
• SQL keywords, such as SELECT, FROM, INSERT, and
  WHERE issue instructions to the database

Page 527
53
SQL
Section D
Chapter 10
How does SQL specify the action that I want
carried out in the database?
Page 527
54
SQL
Section D
Chapter 10
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 527
55
SQL
Section D
Chapter 10
How does SQL specify parameters?

• The term parameter is technical jargon that
  refers to the detailed specifications for a
  command
• Keywords such as WHERE usually begin an SQL
  clause that contains the parameters for a command

Page 528
56
SQL
Section D
Chapter 10
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 528
57
SQL
Section D
Chapter 10
How are records added to a database?
Page 528
58
SQL
Section D
Chapter 10
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 529
59
SQL
Section D
Chapter 10
How do SQL queries carry out searches?
Page 529
60
SQL
Section D
Chapter 10
Can SQL perform complex searches?

• Yes, 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 529
61
SQL
Section D
Chapter 10
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 CompactDisks
• WHERE ArtistName Natalie Merchant AND
  DiscountPrice lt 10.00

Page 529
62
SQL
Section D
Chapter 10
How does OR differ from AND?

• OR means to pick each record that meets one
  criteria or the other, but not both
• SELECT CDName
• FROM CompactDisks
• WHERE (ArtistName Natalie Merchant OR
  ArtistName 10,000 Maniacs) AND DiscoutPrice
  lt10.00
• Note the use of parentheses around the OR clause

Page 530
63
SQL
Section D
Chapter 10
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 CompactDisks
• WHERE ArtistName Natalie Merchant OR
  (ArtistName 10,000 Maniacs AND DiscountPrice
  lt 10.00)

Page 530
64
SQL
Section D
Chapter 10
How does NOT work in an SQL query?

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

Page 530
65
SQL
Section D
Chapter 10
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 CompactDisks
• SET QtyInStock QtyInStock 1
• WHERE CDName Tigerlily

Page 531
66
SQL
Section D
Chapter 10
Is it possible to update a group of records?

• In addition to changing the data in a single
  record, 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 CompactDisks
• SET DiscountPrice 12.00
• WHERE ArtistName The Rolling Stones
• The UPDATE command means that you want to change
  the data in some or all of the records

Page 531
67
SQL
Section D
Chapter 10
Joining Tables How is data retrieved from more
than one table at a time?

• In SQL terminology, 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 532
68
SQL
Section D
Chapter 10
How is data retrieved from more than one table
at a time?
Page 532
69
SQL
Section D
Chapter 10
How does the JOIN command work?

• A single SQL query can retrieve data from the two
  tables

Page 533
70
SQL
Section D
Chapter 10
How does the JOIN command work?
Page 533
71
SQL
Section D
Chapter 10
How extensive is SQL?

• SQL is a very extensive and powerful language
  that can be used not only to manipulate data, but
  to create databases, tables, and reports
• CASE 1

Page 533