# 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
TermDefinition
**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