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