The Complete Database Design & Modeling Beginners Tutorial - Rares Ilea https://www.youtube.com/watch?v=xN008Sqd3XU

Good one - lynda.com https://www.youtube.com/watch?v=V5DyvUfsboA

case study in banking https://www.youtube.com/watch?v=qlLpDBSOe3o

DB Design Steps

1. Determine the purpose of the DB.
2. Find and Organize the information required.
3. Divide the information into tables.
4. turn information itmes into columns
5. specify primary keys
6. setup the table relationships
7. Apply the normalization rules
8. refine the design

3 Levels of Database Design

Conceptual DB Design - Logical - Physical -

Conceptual and Logical design are called Data Modelling

Conceptual and Logical design is nothing but high level and abstract design phase. In this phase we describe what data is contained in the database. So the entities such as customer and products describe the relationships between the entities such as

1. customer can buy many products
2. describe the constraints on the data such as a customer first name can have maximum 45 characters.

Physical Data Model

Is specific database management system which includes internal database storage structure file organization or indexing techniques. So it is specific to the database management system.

Below Table describes which feature of db design activity is performed in conceptual, logical and physical database design levels

** Feature **

Entity Names Conceptual & Logical

Entity Relationships Conceptual & Logical

Attributes Logical

Primary Key Logical & Physical

Foreign Key Logical & Physical

Table Names Logical & Physical

column Data types Logical & Physical

In conceptual model

We find tables to store specific data and define its relationship

Products -------Orders -------Customer

In logical model

for the tables identified in conceptual model i.e

Products, Orders and Customers we will find all the columns and define Primary and Foreign Key relationships

In Physical Design

We will create tables with column data types defined and create primary and foreign key relationship, indexes

DB Modelling tools

Free - MySQL Workbench

DB Relationship

Degree of relationship (cardinality) is the number of occurrences in one entity which are associated to the number of occurances in another.

There are 3 types of cardinality

1 to 1 1 to many many to many