CHAPTER 8: DATABASE CONCEPTS AND MANAGEMENT

8.1 DATABASE CONCEPTS

8.1.1 Limitations of File-Based Systems

Disadvantages:

No enforcing control on organization/structure
Data repeated in different files
Manual changes required in all files
Sorting must be done manually or by program
Data may be in different formats
Cannot be multi-user (chaotic)
Security not sophisticated

8.1.2 Database Approach

Database:

Collection of non-redundant interrelated data
Organized for efficient access

DBMS (Database Management System):

Software programs that allow databases to be defined, constructed, and manipulated

8.1.3 Relational Database Terminology

Term	Definition
Entity	Object/event that can be distinctly identified
Table	Contains related entities in rows and columns
Tuple	Row/record in relational database
Attribute	Field/column in relational database
Primary Key	Attribute that uniquely identifies each tuple
Candidate Key	Attribute that can potentially be primary key
Foreign Key	Attribute that relates two different tables
Secondary Key	Candidate key not chosen as primary
Referential Integrity	Prevents inconsistent data in relationships

8.1.4 Entity-Relationship Diagrams

Relationships:

One-to-Many: One record relates to many records
One-to-One: One record relates to one record
Many-to-Many: Many records relate to many records (requires intermediate table)

8.2 DATABASE MANAGEMENT SYSTEMS (DBMS)

8.2.1 Features of DBMS

Data Management:

Data stored in relational tables
Secondary storage

Data Dictionary:

List of all files
Number of records
Names and types of fields

Data Modelling:

Analysis of data objects
Identifying relationships

Logical Schema:

Overall view of database
Entities, attributes, relationships

Data Integrity:

Block copied when changed
Saved back when done

Data Security:

Password allocation
Automatic backups
Access rights control

Data Change Handling:

Exclusive mode (impractical for multiple users)
Lock all records in table
Lock current record only
Warn of simultaneous change

Deadlock:

Two locks at same time
One user must abort

8.2.2 DBMS Tools

Developer Interface:

Creates and manipulates database in SQL

Query Processor:

Handles high-level queries
Parses, validates, optimizes
Creates query plan

8.3 NORMALIZATION

8.3.1 First Normal Form (1NF)

Requirements:

No repeating attributes
Intersection of each tuple and attribute contains only one value

8.3.2 Second Normal Form (2NF)

Requirements:

In 1NF
Every non-primary key attribute fully dependent on primary key
Remove incomplete dependencies

8.3.3 Third Normal Form (3NF)

Requirements:

In 1NF and 2NF
All non-key elements fully dependent on primary key
No inter-dependencies between attributes

8.3.4 Many-to-Many Relationships

Cannot be directly normalized to 3NF
Must use intermediate table (two-step process)

8.4 SQL (STRUCTURED QUERY LANGUAGE)

8.4.1 Data Definition Language (DDL)

CREATE DATABASE:

<SQL>

CREATE DATABASE database-name

CREATE TABLE:

<SQL>

CREATE TABLE table-name (
 field1 data-type,
 field2 data-type,
 ...
)

ALTER TABLE:

<SQL>

ALTER TABLE table-name ADD field-name data-type

PRIMARY KEY:

<SQL>

PRIMARY KEY (field)

FOREIGN KEY:

<SQL>

FOREIGN KEY (field) REFERENCES table(field)

8.4.2 Data Manipulation Language (DML)

SELECT (Query):

<SQL>

SELECT field-name
FROM table-name
WHERE condition

Operators: =, >, <, >=, <=, <>, IS NULL

ORDER BY: Sort ascending

<SQL>

ORDER BY field-name

GROUP BY: Group identical data

<SQL>

GROUP BY field-name

INNER JOIN: Combine fields from different tables

<SQL>

INNER JOIN table ON condition

INSERT:

<SQL>

INSERT INTO table (field1, field2)
VALUES (value1, value2)

DELETE:

<SQL>

DELETE FROM table WHERE condition

UPDATE:

<SQL>

UPDATE table
SET field = value
WHERE condition

Data Types:

CHARACTER(n)
VARCHAR(n)
BOOLEAN
INTEGER
REAL
DATE
TIME