A) Introduction

need to refer to the CASE central repository

HIERARCHY OF DATA ORGANIZATION: BITS TO DATA BASES

• I. Bits, Bytes and Characters
• II. Data elements: fields, field length, data item
• III. Records: occurence of a record, coded data element
• IV. Files
• V. Data Bases: data redundancy; traditional file environment

B) Overview of Database Management Systems

1. Hierarchical DBMS

• 1. Background: Information Management System (IMS)
• 2. Hierarchical DBMS Example

occurence of data base structure; segment; data

base record; parent-child relationship; root

1. Network or CODASYL DBMS

• 1. A Network DBMS Example: 1-many; set; schema
• 2. Queries to the DBMS

Relational DBMS

• 1. Relational vs. Network DBMSs
• 2. A Relational DBMS Example: Table; tuple; attribute
• 3. Queries to the DBMS
• 4. DB2, ORACLE, FOCUS, IDMS/R, Ingres
• 5. Standards for DBMSs SQL(Structured Query Language)

Object-oriented DBMS

• 1. uses objects and messages to accommodate types of data and provide for data handling

DATABASE DESIGN CONSIDERATIONS (Fig. 11.2)

• logical database design - way user views data (external)
• subschema - each user's view of the database
• physical database design - actual structure of database
• based on the data dictionary
• schema - complete description of content and structure of database
• conceptual schema - RDB model (set of normalized tables)
• internal schema - physical organization of data

C) What is a Relational Database?

Tables - Relations

Properties (rows & columns == tuples & attributes) (Fig. 11.3-4)

• 1) Every table must have a primary key
• 2) Primary keys must be unique
• 3) Relationship (Foreign Key of Table 2 = PK of Table 1)

1. SQL Commands: CREATE - (Fig. 11.6)
2. SELECT - (Fig. 11.7)
3. INSERT, UPDATE, DELETE

D) Designing a Relational Database

• 1) Model the Entities and Map to Tables (Fig. 11.8)
• 2) Designate the Primary Keys (Fig. 11.9&10)
• 3) Model Relationships between Tables (Fig. 11.11)
• 4) Model Attributes of Tables (Fig. 11.12)
• 5) Normalize the Database
• 6) Prepare a Data Dictionary

E) What is Normalization?

A technique for optimizing relational database designs and freeing them from potential problems or anomalies

• 1) First Normal Form (eliminate repeated attributes) (Fig. 11.14)
• 2) Second Normal Form (eliminate partial functional dependencies - all attributes depend on entire primary key) (Fig. 11.15)
• 3) Third Normal Form (eliminate transitive dependencies)(Fig.11.16)

F) Systems for Storing and Manipulating Data

1) Traditional File Systems (Fig. 11.18 & 19)

• simple data design
• fast data access
• inexpensive supporting technology

2) DBMS - integrated data base; database vs. data base

• greater access to info
• better control - improved data integrity
• more efficient systems development - program-independent
• improved data security
• logical and physical data independence

3) Selecting the Appropriate System:

• application needs changing?
• frequent ad hoc inquiries vs. batch processing needs?
• data shared among departments?
• need to reduce programming lead time and costs?
• need to improve consistency of data?

G) Distributed DBMS - on-line vs. segmented vs. replicated

H) MANAGING DATABASES

DBA: implement database, provide security for database, monitor performance, design standards, provide backup & recovery

Exercises: look over 1-12; turn in 11.13, 11.17, 11.18, 11.21