Database Design

Data surrounds us, we generate it in our daily lives, businesses generate in their transactions, we consume it online, in stores, using our "swipe cards". How it is stored is essential for the safe, consistent, accurate and timely use of the data. Database design is an important task in any data project.

Database Design

Knowing how to organise and classify data makes it possible to draw useful conclusions about seemingly random facts.

ER Modelling

The Entity Relationship Diagram (ERD) is a tool for discussion when designing a system.

Identify Relationships

Relationship optionality, cardinality and connecting entities

Diagram conventions

There is a format used by Oracle for creating ER diagrams.

Speaking ERDish

ERDish is a way of naming the entities involved in a relationship and the attributes associated to that relationship.

Matrix Diagrams

A method for discovering relationships.

Super and Subtypes modelled

Super and Subtypes modelled

Modelling Business Rules

Modelling Business Rules

Relationship Transferability

Are relationships transferable on your ERD?

Entity Relationship Types

1:1 1:Many and Many:Many relationships

Resolving Many to Many Relationship

We must resolve all many to many relationships on the ERD

CRUD Analysis

Create Retrieve Update and Delete

Unique Identifiers

Artificial, composite and secondary unique identifiers

1NF

First normal form: no multivalued attributes

2NF

Second Normal Form: Any non UID attritbute must be dependent on the entire UID.

3NF

Third Normal Form: No non-UID attribute can be dependent on another non-UID attribute.

Relational Databases

Basic relational database concepts

Basic Mapping

Basic mapping concepts

Relationship mapping

Mapping each type of relationship to an implementation

Subtype and supertype mapping

Mapping of sub and supertypes in the form of an implementation

DATA TYPES

Commonly used data types in Oracle relational databases

CREATE TABLE

SQL statement to create tables

INSERT INTO

SQL statement to insert values into tables

Introduction to Constraints

Introduction to Constraints

Constraints: FOREIGN KEY, and CHECK constraints

Constraints: FOREIGN KEY, and CHECK constraints

Managing constraints using the ALTER TABLE statement

Managing constraints using the ALTER TABLE statement

SELECT statement

SELECT statement

ORDER BY Clause

ORDER BY Clause