BUS2 116 Advanced Database Management Systems Professor Stephen Kwan

1
BUS2 116 Advanced Database Management
SystemsProfessor Stephen Kwan
2
Review of Materials

• What is a Database?
• What is a Database Management System (DBMS)?
• Why do we need Databases?
• 3-Level Schema Architecture
• External, Conceptual, Internal Views Schemas

3
What is a Database?
A Database is an Integrated Collection of Data
Meta-Data Organized to Meet the Needs of
Multiple Users in an Organization.
4
Database DBMS
5
3-Level Schema Architecture
Individual Users
Community View
Storage
6
Data Modeling

• Conceptual Data Modeling
• Entity-Relationship Model (ER Model)
• ER Diagrams
• Cardinality and Constraints
• 1 1 Relationships
• 1 M Relationships
• M N Relationships

7
ER Diagrams
1 1
1 M
M N
8
Relational Data Model 1

• Conceptual / Implement-able
• Tables represent Entities - must have Primary Key
• Columns represent Attributes
• Rows represent individual Entities
• Relationships are represented by Primary Key /
  Foreign Key correspondence

9
Relational Data Model 2

• Relational Diagram
• No M N relationships - decomposed into Two 1
  M relationships

RELATIONALDIAGRAM
ER DIAGRAM
RELATIONSHIP
CROSSREFERENCE TABLE
10
Relational Data Model 3

• Normalization
• SQL
• SELECT . From . Where . Ordered by.
• More statements in SQL
• SQL works with Tables and result in Table(s)

11
Data Integrity

• What is Data Integrity?
• Existence Requirement - the concept of NULL and
  Consistency
• Domain Integrity
• Entity Integrity
• Referential Integrity
• Business Rules

Data Integrity
12
Relational Database Implementation

• MS ACCESS
• Single vs. Multiple user systems
• Create Database
• Forms, Reports, Queries
• Macros
• Visual Basic for Applications (VBA) - inline or
  in modules

13
ACCESS 1

• Creating a Database and its Tables
• Defining the Primary Key
• Defining the Attributes and their Domains
• Defining the Relationships, Cross Reference
  Tables and Foreign Keys
• Maintaining Data Integrity
• Forms, Reports, Queries, Codes

14
ACCESS 2

• Functions
• ADD, CHANGE, SAVE, DELETE, SORT, FIND, QUERY,
  REPORT, QUIT, RESET, DISCARD, etc.
• ACCESS Development Environment Buttons, Text,
  Labels, Combo Check Boxes, Menus, Radio
  Buttons, etc.
• Error Trapping and Handling

15
New Topics 1

• More on ACCESS
• More on SQL
• Advanced Database Programming
• Multi-user Systems - Locking
• Transaction Processing
• ORACLE
• Client / Server Architecture
• Database Security

16
New Topics 2

• Distributed Databases
• ODBC - ACCESS, VB to Oracle
• Interfacing a Database to the Web
• Object-Oriented Database Systems
• Heterogeneous Databases - Data Warehouses
• Data Mining