Applications of Computers Lectures 1 and 2

1
Applications of Computers Lectures 1 and 2

• We will look at databases and their applications
• Databases have wide-spread applications in all
  walks of life
• We will introduce network model and relational
  model of database

2
Databases

• Databases are implemented in many forms on all
  possible platforms
• Think of flight reservation, credit card
  accounts, registration for a semester, payrolls,
  IRS records, SS records,
• Everyday, we interact with at least one database
  in our day to day activities
• Database contains records that contain information

3
Database Concepts

• A database is usually a large collection of
  information
• A DBMS (Database Management System) is
  implemented to retrieve information from a
  database in an effective and efficient way
• Database itself is implemented in files that
  contain records. Each record contains data fields
  having item-specific information

4
Database Concepts An example
Adapted for academic use from Exploring The
Digital Domain by Abernethy Allen, ITP 1999
5
Use of Indices in file management

• If you store information in the way shown, you
  may want to select a specific persons record
• In order to select the appropriate record, you
  have to spell out the last name of a person
• This last name serves as a search index as all
  records matching this last name can be retrieved
  (http//www.switchboard.com)

6
Use of Indices in File Management

• The database files searched this way are called
  Indexed files
• Indexed files are implemented usually with
  binary search tree (BST)
• The BST nodes contain the data values of the
  indexed fields e.g. last names
• Additionally, a link is provided to the actual
  file

7
Trees

• A binary tree is a data structure that is very
  useful in search applications
• Trees are everywhere
• A natural tree has a root from which everything
  starts
• Think about your family tree
• Look at an organization chart for a company

8
Trees

• Think about the way directories are organized in
  your computer
• There is a root directory C\
• Then there are sub-directories
• Sub-directories can also have further
  sub-directories
• This is the directory tree

9
Binary Tree

• A binary tree is a tree in which each node can
  have just two children
• Binary tree is easy to sketch
• Start with a root node (root is at the top as
  opposed to natural trees)
• One child is shown on left and the other one is
  shown on right
• The children can also have maximum two children
  each

10
(No Transcript)
11
Binary Search Tree

• A binary search tree is a specialized type of
  tree
• In BST, a node can have only two children (right
  and left)
• The value of the left child is LESS than the
  value of ROOT
• The value of RIGHT child is MORE than the value
  of ROOT

12
A Plain Binary Tree
13
A Binary Search Tree
14
How to Have a Balanced BST

• The selection of value for root node is very
  important
• For example, look at the list of numbers
  presented here and make a BST from it
• 1,2,3,4,5,6,7 What is the depth?
• Now look at the list of numbers presented here
  and make a BST from it
• 4,2,1,3,6,5,7 What is the depth?

15
Use of Indices in File Management

• Indexed files are implemented usually with our
  friendly binary search tree (BST)
• The BST nodes contain the data values of the
  indexed fields e.g. last names
• Additionally, a link is provided to the actual
  file
• A BST should be balanced in order to cut down the
  search overhead

16
BST and Actual File
Adapted for academic use from Exploring The
Digital Domain by Abernethy Allen, ITP 1999
17
Network Database Model

• The need for a convenient query language and
  interaction developed for applications beyond
  payrolls and inventory databases
• The programmers worked to link records in
  separate files together
• This model is called network database model
  because it uses a network of links between files
• Programmers must be aware of physical
  organization of files and links

18
Network Database Model
Adapted for academic use from Exploring The
Digital Domain by Abernethy Allen, ITP 1999
19
Relational Database Model

• Relational database model was introduced in late
  70s
• This model gives a more conceptual view of the
  database
• It establishes a logical relationship between
  records using one field as a logical link
• The information needed to employ relational
  database is intuitive and does not include
  physical disk addresses

20
Relational Database Model
Adapted for academic use from Exploring The
Digital Domain by Abernethy Allen, ITP 1999
21
Relational Database Model
Adapted for academic use from Exploring The
Digital Domain by Abernethy Allen, ITP 1999
22
Comparing Both Models

• Network model is much faster than the relational
  model because the links are physical disk
  addresses
• Relational model is more flexible so it can
  handle different types of queries
• RDBMS performs search after search to retrieve
  information from different files
• The tradeoff is flexibility vs speed

23
Airline Reservation System

• When we reserve a seat in a flight, the agent
  interacts with the database
• The types of queries are limited to a few options
  (class, fare, availability, restrictions)
• There are thousands of queries in progress at any
  given time
• We want the database to be fast and efficient

24
Player Performance Statistics

• During televised games, comments are made on
  certain players
• For example, Mark has a batting average of 0.407
  against the Braves, etc.
• This information is obtained on the fly from a
  database of player performance
• We want this database to be flexible as many
  different types of queries are allowed

25
Example of a Central Database

• AFIS is FBIs Automated Fingerprint
  Identification System
• It provides a national database of digitized
  fingerprints
• California requires thumb prints on driver
  licenses
• International travelers may be provided with
  smart cards and checked with hand identification

26
Creating Your Own Database

• Large databases cannot be implemented on personal
  computers
• For PCs, the relational database model is more
  appropriate as the number of data items is small
  and speed is not a primary concern
• MS-Access will be used as an example to
  illustrate the database management on PCs

27
Defining the Database Structure

• Initial work involves deciding about the contents
  of individual fields and overall organization
• Each field can contain only one type of data
• Related data files are called tables
• A table is an object that stores data in records
  (rows) and fields (columns). The data is, for
  example, about a customer or an employee

28
A Table in MS-Access

• In table Design view, you can create an entire
  table from scratch, or add, delete, or customize
  an existing table's fields.
• In table Datasheet view, you can add, edit, or
  view the data in a table. You can also check the
  spelling and print your table's data, filter or
  sort records, change the datasheet's appearance,
  or change the table's structure by adding or
  deleting columns.

29
Databases in MS-Access

• In MS-Access, you can create a new database with
  simple graphical interface
• As soon as you name your database, you are
  presented with a window having six tabs
• We define four basic tabs here
• Data will be kept in tables. You have to create a
  new table and define its fields (columns). You
  should use the design view to create the table

30
Databases in MS-Access

• In order to facilitate entry of data, MS-Access
  provides forms. Forms can be designed through the
  Forms tab
• Queries and Reports can be created to interact
  with the database and extract data that meets
  certain search requirements or it has been
  sorted. Reports generate nice formatted display
  of the output data

31
Forming Queries

• Queries are requests for specific information
  that meet a certain criteria
• Queries are written in a query language
• Normally, queries act as the only user interface
  in a database
• Store cashier, bank teller clerk, payroll data
  entry operators.all use query language to
  interact with the databases

32
Forming Queries

• SQL (sequel) stands for structured query
  language and it is a de-facto standard
• SQL queries are simple and you do not need to
  know programming to form these queries
• For example, to show all items whose value
  exceeds 200 in the inventory database
• SELECT Item-Name FROM Inventory WHERE Valuegt200

33
Natural Language Queries

• Work is in progress to have a natural language
  query system
• For example, the above question could be
  re-phrased as Find the item names from inventory
  whose value exceeds 200
• One of the search engines on the web, AltaVista,
  supports natural language queries
• QBE(Query-by-example) is also popular and
  MS-Access uses it by having criteria field in
  query design

34
The Web Interface

• Web and database technologies are merging
  providing exciting opportunities
• (Think about the e-commerce, it has become
  possible for you to book a flight, browse through
  items and buy things online)
• Web database front-ends provide forms using
  which users can make selections as per their
  criteria and interact with the database

35
Web Interface

• Web interface supports http (hyper text transfer
  protocol) that runs over TCP/IP
• This protocol supports transfer of text, graphics
  and applets, thus opening a lot of possibilities
• Industry is interested in small gadgets running
  http protocol that can exchange information in a
  secure way