Databases and Database

Management Systems
What Is a Database?
• Database: A collection of related data stored in a manner so it can be
retrieved as needed
• Database management system (DBMS): Used to create, maintain, and
access databases
• A database typically consists of:
• Tables: Collection of related records
• Fields (columns): Single category of data to be stored in a database (name,
telephone number, etc.)
• Records (rows): Collection of related fields in a database (all the fields for one
customer, for example)
• Relational database: Data from several tables is tied together (related)
using a field that the tables have in common

2
A Simple Relational Database Example

3
What Is a Database?
• Primary key: Specific field that uniquely identifies the records in that
table
• Used in a relational
database to relate tables
together
• Must be unique and a field
that doesn’t change
• PC DBMSs include:
• Microsoft Access, Corel
Paradox, Lotus Approach
• For more comprehensive
enterprise databases
• Oracle Database, IBM DB2, Microsoft SQL Server

4
What Is a Database?

• Individuals involved with a DBMS:

• Database designers: Design the database
• Database developers: Create the database
• Database programmers: Write the programs needed to access the database or
tie the database to other programs
• Database administrators: Responsible for managing the databases within an
organization
• Users: Individuals who enter data, update data, and retrieve information out of
the database

5
5
Advantages and Disadvantages of the
DBMS Approach
• Advantages
• Faster response time
• Lower storage requirements
• Easier to secure
• Increased data accuracy
• Disadvantages
• Increased vulnerability (backup is essential)

6
Data Concepts and Characteristics
• Data hierarchy
• Fields/columns: Hold single pieces of data
• Records/rows: Groups of related fields
• Tables: Collection of related records
• Database: Contains a group of related tables
• Entity: Something of importance to the organization
• Entities that the organization wants to store data about typically becomes a
database table
• Attributes: Characteristics of an entity
• Typically become fields in the entity’s database table

7
Data Concepts and Characteristics
• Entity relationships: Describe an association between two or more
entities
• One-to-one (1:1) entity relationships
e.g. each store has a single manager
• One-to-many (O:M) entity relationships
• e.g. a supplier supplies more than one product to a company
• Many-to-many (M:M) entity relationships
• e.g. an order can contain multiple products and a product can appear on multiple orders

8
Data Concepts and Characteristics

• 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

9
Data Definition

10
Data Concepts and Characteristics

• Data dictionary: Contains all data definitions in a database

• Table structures
• Names, types and properties of each field
• Security information (passwords, etc.)
• Relationships between the tables in the database
• Current information about each table, such as the current number of records
• Does not contain any of the data in the tables
• Metadata: Data about the database tables
• Ensures that data being entered into the database does not violate any specified
criteria

11
Data Concepts and Characteristics
• Data integrity: The accuracy of data
• Quality of data input determines the quality of retrieved information
• 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
• Database locking prevents two individuals from changing the same data at the
same time

12
Data Validation

13
 Data Concepts and Characteristics
• Data security: Protecting data against destruction and misuse
• Protects against unauthorized access
• Database activity monitoring programs can be used to detect possible
intrusions
• Disaster recovery and backup
Data backup is the process of replicating files to be stored at a
designated location. Disaster recovery is a system that helps restore
those files following a catastrophe.
• Should include strict backup and disaster-recovery procedures (disaster-
recovery plan)
• Protects against data loss
• Data privacy is a Growing concern because of the vast amounts
of personal data stored in databases today

14
 Data Concepts and Characteristics

• Data organization: Arranging data for efficient retrieval

• Indexed
organization
uses an
index to
keep track
of where
data is
stored

15
Transaction processing

Transaction processing ensures that related data is added to or deleted

from the database simultaneously, thus preserving data integrity in your
application. In transaction processing, data is not written to the database
until a commit command is issued. When this happens, data is permanently
written to the database.
Data Concepts and Characteristics
• Direct organization
• Uses hashing algorithms to specify the exact storage location
(Hashing is simply passing some data through a formula that produces
a result, called a hash)
• Sometimes a combination of
indexing and direct organization
is used within a database
system

17
Database Classifications

18
The Relational Database Model
• Creating a relational database:
• Create the database file
• Create the structure of each individual table (in Access, can be performed in
either Design or Datasheet view)
• Enter data
• Existing data can be migrated to the new database
• New data can be added via form or the Design view
• Relate tables as needed

19
The Relational Database Model

20
The Relational Database Model

21
The Relational Database Model

22
The Relational Database Model
• Retrieving information from database

 Query: A request to see information from a database that

matches specific criteria

• Specifies which records should be retrieved by specifying criteria

• Can specify the fields to be displayed
• Many programs have wizards or other tools to make it easy to create a query
• Must be designed to extract information as efficiently as possible
• Queries are saved so they can be retrieved again when needed; proper
results are displayed each time the query is run

23
The Relational Database Model

24
 The Relational Database Model
• Report: Formatted means of looking at a database table or
the results of a query
• Reports can pull data from more than one table
• Many programs have wizards or other tools to make it easy to create a report
• Can be modified and customized using the Design view
• Reports are saved so they can be retrieved again when needed; proper results are
displayed each time the query is run

25
The Relational Database Model

26
Databases and the Web
• Middleware is used to connect two otherwise separate applications, such as a
Web server and a database management system
• Commonly written as scripts
• JavaScript
• VB Script
• CGI scripts
• Active Server Pages (ASPs)
• PHP scripts

27
Databases and the Web

28