Relation Database

Functions of DBMS

Data Processing

A User Accessible Catalog

Transaction Support

Concurrency Control Services

Recovery Services

Authorization Services

Support for Data Communication

Integrity Services

Components of DBMS

User Interface

Data Definition Language (DDL) and Data Manipulation Language (DML) Compilers

System Catalog/Data Dictionary

Runtime Database Processor

Concurrency Control, Backup, & Recovery Manager

Database Design Methodology

Conceptual Design

Logical Design

Physical Design

Logical Design

Relational Data Model

Basic Constructs

Referential Integrity Constraints

Querying

Relational Algebra

Selection

Projection

Picking both rows and columns by composing operators

Cartesian Product

Union

Intersection

Difference

Relational Data Model

Querying (Cont)

basic operations

Database Design Methodology

Conceptual Design

Logical Design

Physical Design

Entity Relationship Model

Entity

Attribute

Relationship

Types

Chen’s ERD

Entity Representation

Attribute Representation

Relationship Representation

Degree of a Relationship

Replacing Ternary relationships

Cardinality of relationships

One to One

One to Many

Many to Many

Chen’s ERD

Entity Representation (Cont)

Attribute Representation (Cont)

Relationship Representation (Cont)

Constructing an ERD

9 steps to Constructing an ERD

What is Mapping?

Preparing to Map the ERD

Mapping Entity Type into a Relation

Mapping 1:1 Relationships

Combine Entities

If not Combined

Mapping 1:M Relationships

Mapping M:N Relationships

What is Normalisation?

Data integrity

Entity integrity

Referential integrity

Data redundancy

Problem of Redundancy

Understanding Data

Normal Forms

First Normal Form

Flattened Table Problems

Effect of Redundancy

Insertion Anomaly

Update Anomaly

Deletion Anomaly

Second Normal Form

Third Normal Form

Boyce-Codd Normal Form

Importance of Data

Resource:

Data as a resource:

Levels of Data

Real World Data

Levels of Data

Levels of Data

Database Users

Application Programmers

End Users

Naïve Users

Sophisticated Users

Database Administrators (DBA)

Database Architecture

External Level

Conceptual or Logical Level

Internal or Physical Level

Three-Levels

Objectives

Introduction to relational database

Entity-Relationship Modelling

Normalisation

Queries

Definition

What is a database?

Why do we need a database?

Problems with traditional file systems.

Efficiency

Accuracy

Ease of Updating

Security

Controlled Redundancy

Overview

Examples

Hotel Booking System – Typical example

Video-Sharing Portal

Flight Booking System

Job Portal

Property Listings

In Griffith

Important terms

Data

information

Relational database terminology

Flat database vs. Relational database

Database

Database Management Systems (DBMS)

Structured Query Language (SQL)

Relational Databases

Object-Oriented Databases

Advantages

Disdvantages

Deductive Databases

Distributed Databases

Advantages

Disdvantages

Characteristics of DDBMS

DDB types

Homogeneous

Heterogeneous

Transparency

Aggregate Functions

MAX

MIN

SUM

AVG

COUNT

GROUP BY

HAVING

SUBQUERIES

More Complex SELECT

CROSS JOIN

INNER JOIN

LEFT JOIN

RIGHT JOIN

Table Aliases

Renaming Columns

JOIN – Example

SELECT

UPDATE

DELETE

Types of SQL statements

Data Definition Language (DDL)

Data Manipulation Language (DML)

Data Control Language (DCL)

MySQL Data Types

Numeric

Integer

Floating point

Fixed point

String

Date and time

Large storage

SQL Tables

Naming convention

Create Table

Constraints

Drop Table

Alter Table

Rename a table

Adding a column

Removing a column

Rename a column

Modifying a constraint

Modifying a column data type

Truncate Table

Codd’s 12 Rules

Rule 1: The Information Rule

Rule 2: Guaranteed Access Rule

Rule 3: Systematic Treatment of NULL Values

Rule 4: Dynamic Online Catalog Based on the Relational Model

Rule 5: Comprehensive Data Sublanguage Rule

Rule 6: View Updating Rule

Rule 7: High-Level Insert, Update, and Delete

Rule 8: Physical Data Independence

Rule 9: Logical Data Independence

Rule 10: Integrity Independence

Rule 11: Distribution Independence

Rule 12: Non-Subversion Rule

MySQL Basics

MySQL Basics Example