Presenter:

Jorge Barba

Describe the Relational Data Model componentsModel Entities, Relationships and Attributes

Translate a Data Model Diagram into Schema Data types Learn the mapping of

Attributes to Data TypesEntities to Tables

Relationships to Foreign Keys

Learn SQL SELECT statementUnderstand the Projection, Filtering, Join, and Order

Why Data Modeling?

Target: The System.

Application: To automate data management, to generate reports, to record activity.

Examples: Facebook, Amazon, Oracle E-Business Suite, Nowait.

Databases hold the data, the application is used to enter, modify, remove the data, and generate reports. Together the Database and the Application are “The System”.

Scientific Model

Conceptual Model, Entity Relationship Model, Data Modeling

Conceptual Model vs Physical Model

Business Rules/Business Functionality vs Programs/Applications

Tables and Programs are the target but we start with The Model

It describes the information needs of the business (Requirements)

It facilitates discussion

It helps to prevent mistakes

Common language between Developers, Designers, DBAs

A model forms a basis for the design of the physical database

Regardless of the final Database destination the Model works for any physical implementation

Out of everything out there in the World

What do I care about to have in … “The System”?

Example: Camera Store

We want to sell cameras and lenses

Mainly Nikon, Canon, Sony, Fujifilm and some other brands

No bundles, lens sold separately

We will have different technologies from DSLR to Mirrorless to Film

We are interested in comparing features like ISO Sensitivity Range, Top Continuous Speed (FPS), Movie Resolution, Sensor Size, Effective Pixels (Megapixels)

The orders from Customers are archived

We don’t care about accounting, stock control, payroll

Find out the entities for the Camera Store

Out there in the world what interests me for The System

The business wants to keep information about it

An ENTITY is a class or type of things

Entities have instances

An ENTITY is usually a noun

The attributes DESCRIBE the entity

An attribute is a property of the entity

We are interested only in attributes relevant to “The System”

Attributes have values

An attribute value can be a number, a character string, a date, an image.

Distinction between an ENTITY, an attribute, and an instance of an entity.

Find out the attributes for Camera

How the entities are mutually related

Always exist between two entities

Have two perspectives

Are named at both ends

RELATIONSHIP EXAMPLESEMPLOYEES have JOBS

JOBS are held by EMPLOYEES

PRODUCTS are classified by a PRODUCT TYPE

PRODUCT TYPE is a classification for a PRODUCT

PEOPLE make TICKET RESERVATIONS

TICKET RESERVATIONS are made by PEOPLE

All EMPLOYEES have a JOBNo EMPLOYEE has more than one JOB

Not all JOBS are held by an EMPLOYEE

Some JOBS are held by more than one EMPLOYEE

ShintaroJill

AdamAhmed

Maria

ManagerCook

WaitressDish washer

Financial controllerPorter

WaiterPiano player

Before we see details on Relationships we open a parenthesis to see how to represent the model

* Mandatory attribute

o Optional attribute

EMPLOYEE* Family Name* Addresso Birth Dateo Shoe Sizeo Email

JOB* Title* Description

Attributes in Diagrams

An employee has exactly one job

EMPLOYEEJOBhas

held by

Mandatory: Optional:

FK

All EMPLOYEES have a JOBNo EMPLOYEE has more than one JOB

Not all JOBS are held by an EMPLOYEE

Some JOBS are held by more than one EMPLOYEE

ShintaroJill

AdamAhmed

Maria

ManagerCook

WaitressDish washer

Financial controllerPorter

WaiterPiano player

How do you relate to others?

Do you have good relations?

You Are The Average Of The Five People You Spend The Most Time With

1:m

(a)

(b)

(c)

(d)

Optional: Mandatory:

m:m

(e)

(f)

(g)

Optional: Mandatory:

1:1

(h)

(i)

(j)

Optional: Mandatory:

Group the various types of information into entities and attributes.

Name the relationships and draw a diagram.

Read a relationship.

Use your knowledge of people and towns.

Use your knowledge of shopping.

m:m

Quantity is an attribute of . . .

Quantity is an attribute of . . .

New Entity ORDER

FK1 FK2

Multiple PRODUCTS for an ORDER

Another New Entity : ORDER ITEM

Another New Entity : ORDER ITEM becomes the ORDER_ITEMS table

FK1 FK2

FK

The key to a good model and a good life is GOOD RELATIONS

Read the Relationship

Relationships express how entities are connected.

Initially relationships often seem to be of type m:m.

Finally relationships are most often of type m:1.

Relationships can be resolved into: – Two new relationships. – One intersection entity.

Third Normal form is generally accepted standard.

DDL

DML

SELECT

INSERT

UPDATE

DELETE

MERGE

DDL

CREATE

ALTER

DROP

RENAME

TRUNCATE

DCL

GRANT

REVOKE

Transaction Control

COMMIT

ROLLBACK

SAVEPOINT

Using the Order Entry (OE) sample schema

Tables: CUSTOMERS

Using the Order Entry (OE) sample schema

Tables: ORDERS

Using the WHERE clause in the SELECT statement to filter the rows with a condition

Using the WHERE clause in the SELECT statement to filter the rows with two conditions

Using the WHERE clause in the SELECT statement to JOIN two tables and to filterthe rows with two conditions

Using the ORDER BY clause in the SELECT statement to order by order date in descending order (most recent orders first)

Not really

It depends

It is iterative prior to physical phase

Once the structures are built the process does not iterate

Modifications are needed but more like change management not as repetitive iterations

Everyone has its own methodology

Data Modeling and Relational Database Design Volume 1 Student Guide – Oracle Corp.

Oracle Database: SQL Fundamentals I Student Guide – Oracle Corp.

Data Modeling and Database Design - Minder Chen, Ph.D.

Database Analysis and ER Modeling: db.grussell.org

DB Design Tutorial: California State University Long Beach, Department of Computer Engineering and Computer Science: web.csulb.edu/colleges/coe/cecs/dbdesign

Web Database Applications with PHP and MySQL: Entity Relationship Modeling docstore.mik.ua/orelly/webprog/webdb/appc_02.htm

Guru 99: https://www.guru99.com/database-normalization.html