1 of 19

Chapter 4

Relational Databases

4-1

2 of 19

Learning Objectives

Explain the importance and advantages of databases.
Describe the difference between database systems and file-based legacy systems.
Explain the difference between logical and physical views of a database.
Explain fundamental concepts of database systems such as DBMS, schemas, the data dictionary, and DBMS languages.
Describe what a relational database is and how it organizes data.
Create a set of well-structured tables to store data in a relational database.
Perform simple queries using the Microsoft Access database.

4-2

3 of 19

Data Hierarchy

Field

Attributes about an entity

Record

Related group of fields

File

Related group of records

Database

Related group of files

4-3

4 of 19

Advantages of Database Systems

Data Integration

Files are logically combined and made accessible to various systems.

Data Sharing

With data in one place it is more easily accessed by authorized users.

Minimizing Data Redundancy and Data Inconsistency

Eliminates the same data being stored in multiple files, thus reducing inconsistency in multiple versions of the same data.

Data Independence

Data is separate from the programs that access it. Changes can be made to the data without necessitating a change in the programs and vice versa.

Cross-Functional Analysis

Relationships between data from various organizational departments can be more easily combined.

4-4

5 of 19

Database Terminology

Database Management System (DBMS)

Interface between software applications and the data in files.

Database Administrator (DBA)

Person responsible for maintaining the database

Data Dictionary

Information about the structure of the database

Field names, descriptions, uses

4-5

6 of 19

Logical vs. Physical

Physical View

Depends on explicitly knowing:

How is the data actually arranged in a file
Where is the data stored on the computer

Logical View

A Schema separates storage of data from use of the data
Unnecessary to explicitly know how and where data is stored.

4-6

7 of 19

Schemas

Describe the logical structure of a database

Conceptual Level

Organization wide view of the data

External Level

Individual users view of the data
Each view is a subschema

Internal Level

Describes how data are stored and accessed

Description of: records, definitions, addresses, and indexes

4-7

8 of 19

DBMS Languages

Data Definition Language (DDL)

Builds the data dictionary
Creates the database
Describes the subschema
Specifies record or field security constraints

Data Manipulation Language (DML)

Changes the content in the database

Updates, insertions, and deletions

Data Query Language (DQL)

Enables the retrieval, sorting, and display of data from the database

4-8

9 of 19

Relational Database

Relational data model represents the conceptual and external level schemas as if data are stored in tables.
Table

Each row, a tuple, contains data about one instance of an entity.

This is equivalent to a record

Each column contains data about one attribute of an entity.

This is equivalent to a field

4-9

10 of 19

A Relational Table

4-10

Row (Record)

Column (Field)

Each row contains multiple attributes describing an instance of the entity. In this case, inventory.

Same type of data

11 of 19

Attributes

Primary Key

An attribute or combination of attributes that can be used to uniquely identify a specific row (record) in a table.

Foreign Key

An attribute in one table that is a primary key in another table.

Used to link the two tables

4-11

12 of 19

Database Design Errors

If database is not designed properly data errors can occur.

Update Anomaly

Changes to existing data are not correctly recorded.

Due to multiple records with the same data attributes

Insert Anomaly

Unable to add a record to the database.

Delete Anomaly

Removing a record also removes unintended data from the database.

4-12

13 of 19

Design Requirements for Relational Database

Every column must be single valued.
Primary keys must contain data (not null).
Foreign keys must contain the same data as the primary key in another table.
All other attributes must identify a characteristic of the table identified by the primary key.

4-13

14 of 19

Normalizing Relational Databases

Initially, one table is used for all the data in a database.
Following rules, the table is decomposed into multiple tables related by:

Primary key–foreign key integration

Decomposed set of tables are in third normal form (3NF).

4-14

15 of 19

Microsoft Access Query #1

4-15

16 of 19

Microsoft Access Query #2

4-16

17 of 19

Microsoft Access Query #3

4-17

18 of 19

Microsoft Access Query #4

4-18

19 of 19

Microsoft Access Query #5

4-19