1) Intro

2) Tables & Keys (23:12)

3) SQL Basics (43:32)

4) MySQL Installation - Windows (52:26)

5) MySQL Installation - Mac (1:02:00)

6) Creating Tables (1:15:50)

7) Inserting Data (1:31:05)

8) Updating & Deleting Data (1:48:11)

9) Basic Queries (1:56:11)

10) Company Database Intro (2:08:37)

11) Creating Company Database (2:17:06)

12) More Basic Queries (2:30:28)

13) Functions (2:36:25)

14) Wildcards (2:45:15)

15) Union (2:53:55)

16) Joins (3:01:37)

17) Nested Queries (3:11:51)

18) On Delete (3:21:53)

19) Triggers (3:30:05)

20) ER Diagrams Info (3:42:15)

21) Designing an ER Diagram (3:53:53)

22) Converting ER Diagrams to Schemas (4:08:34)

[ 1. Intro ]
1–1. What is database?
Database is a collection of related information that can be stored in different
ways.

Computers are great at keeping track of large amounts of information.

Database Management Systems (DBMS) is a special software program that

helps users create & maintain a database.

We can interact with a DBMS to Create, Read, Update, and Delete information.

There are two types of databases :

1) Relational Databases (SQL)

: Organize data into one or more tables

- Each table has columns & rows.

- A unique key identifies each row.2) Non-Relational Databases (No

SQL)

: Organize data in anything but a traditional table.

- Key-Value stores

- Documents (JSON, XML, etc)

 - Graphs

- Flexible tables

1–2. Relational Database

Relational Database Management Systems (RDBMS) helps users create & maintain a
relational database. (e.g., MySQL, PostgreSQL, etc)

Structured Query Language (SQL):

Standard language for interacting with RDBMS.

Used to perform C.R.U.D. operations as well as other administrative tasks (user
management, security, backup, etc).
Used to define tables(rows, columns) & structures.
SQL code used on one RDMBS is NOT always portable to another RDMBS without
modification.

1–3. Non-Relational Database

Any non-relational database falls under this category. So, there’s no set language
standard.

E.g., Document(e.g., JSON), Graph, Key-Value Hash(Keys are mapped to

values(e.g., JSON))

Non-Relational Database Management Systems (NRDBMS) helps users create &

maintain a relational database. (e.g., MongoDB, DynamoDB, etc)

Most NRDBMS will use their own language for performing C.R.U.D. and
administrative operations on database.

1–4. Database Queries

Queries are requests made to the database management system for specific
information.

As database’s structures become more and more complex, it becomes more difficult to get
specific pieces of information we want. A google search is a query.

[ 2. Tables & Keys (23:12) ]

2–1. Primary Key

When we make a table in a relational database, we need a special column called
“primary key”, which uniquely defines the row in the database.
 2018 ©️FreeCodeCamp

> Student ID 2 and Student ID 4 are different people.

There are two types of primary keys.

“Surrogate Key” is an artificially generated key with the sole purpose of

uniquely identifying a row (StackOverflow).

2018 ©️FreeCodeCamp

“Natural key” is a primary key made up of real world data.

2018 ©️FreeCodeCamp

“Composite Key” is “a combination of two or more columns in a table that can be used to
uniquely identify each row in the table. Uniqueness is only guaranteed when the columns
are combined; when taken individually the columns do not guarantee uniqueness.”
(Techopedia)

2–2. Foreign Key

“Foreign Key” is a key used to link two tables together.

2018 ©️FreeCodeCamp

2018 ©️FreeCodeCamp

A foreign key in a table is a primary key in another table.

There could be more than one foreign key.

[ 3. SQL Basics (43:32) ]

3–1. What is SQL?
SQL is a Structured Query Language used for interacting with a RDBMS.

It is actually a hybrid language. It’s basically 4 types of languages in one.

Data Query Language (DQL) : Used to query the database for information. Can get
information that’s already stored in the database.
Data Definition Language (DDL) : Used for defining database schemas.
Data Control Language (DCL) : Used for controlling access to data in the database.
Handles user & permissions management.
Data Manipulation Language (DML) : Used for inserting, updating, and deleting data
from the database.

In short, we can do the following things with SQL:

Create, Read, Update, and Delete data

Create & Manage databases
Design & Create database tables
 Perform administrative tasks (security, user management, import/export, etc)

3–2. Queries
A Query is a set of instructions given to the RDMBS (written in SQL) that tell
the RDMBS what information a developer wants it to retrieve for the
developer.

SELECT employee.name, employee.age

FROM employee

WHERE employee.salary > 30000;

[ 4. MySQL Installation — Windows (52:26) ]

[ 5. MySQL Installation — Mac (1:02:00) ]
5–1. Install & Log into MySQL
Download macOS DMG Archive at MySQL Community Server & install it.

Let’s make our terminal to recognize MySQL commands.

Let’s connect to the MySQL server.

mysql -u root -p

To change my password, type ALTER USER ‘root’@‘localhost’ IDENTIFIED BY

‘<enter a new password>’ in terminal.

To exit, type exit in terminal.

Create a database by running create database <database name>; in terminal.

5–2. Install & Log into PopSQL / MySQL WorkBench

I will use MySQL Workbench instead.

[ 6. Creating Tables (1:15:50) ]

6–1. Types
-INT : Whole numbers

- DECIMAL(M, N) : Decimal numbers (M: total decimals, N: decimals after

the decimal point)

- VARCHAR(1) : string of text of length 1

- BLOB : storage for large data

- DATE : 'YYYY-MM-DD'

- TIMESTAMP : 'YYYY-MM-DD HH:MM:SS'

6–2. Create/Delete/Update a table

Create a table called “student” :

CREATE TABLE IF NOT EXISTS student(

student_id INT PRIMARY KEY,

name VARCHAR(20),

major VARCHAR(40)

);
View the table : DESCRIBE student;

Delete a table : DROP TABLE student;

Add a table : ALTER TABLE student ADD gpa DECIMAL(3,2);

Delete a column : ALTER TABLE student DROP gpa;

Delete a row : DELETE FROM student WHERE student_id = 1

[ 7. Inserting Data (1:31:05) ]

7–1. Insert data
INSERT INTO student VALUES(1, ‘Jack’, ‘Biology’);

INSERT INTO student VALUES(2, 'Katie', 'Sociology');

INSERT INTO student VALUES(3, 'James', 'Computer Engineering');

7–2. View all data

SELECT * FROM student;

7–3. What if we don’t know a student’s major?

INSERT INTO student(student_id, name) VALUES(4, 'Kyle');

> The unfilled value is set as “null”.

7–4. Not null, Unique, Primary key

CREATE TABLE IF NOT EXISTS student(

student_id INT PRIMARY KEY,

 name VARCHAR(20) NOT NULL,

major VARCHAR(40) UNIQUE

);

NOT NULL : “Name” cannot be filled with NULL.

UNIQUE : A value of “Major” cannot be duplicated.
PRIMARY KEY : NOT NULL + UNIQUE

7–5. Default value

CREATE TABLE IF NOT EXISTS student(

student_id INT PRIMARY KEY,

name VARCHAR(20),

major VARCHAR(40) DEFAULT 'undecided',

);

7–6. Increment a primary key automatically.

CREATE TABLE IF NOT EXISTS student(

student_id INT AUTO_INCREMENT,

name VARCHAR(20),

major VARCHAR(40) DEFAULT 'undecided',

PRIMARY KEY(student_id)

);INSERT INTO student(name, major) VALUES(‘Jack’, ‘Biology’);

INSERT INTO student(name, major) VALUES('Katie', 'Sociology');

INSERT INTO student(name, major) VALUES('James', 'Computer

Engineering');

[ 8. Updating & Deleting Data (1:48:11) ]

8–1. Delete multiple rows
DELETE FROM student WHERE student_id IN (4,5,6,7,8);
8-2. Change values that match a condition
UPDATE student

SET major = 'Bio'

WHERE major = 'Biology';

> If you get this error, Run the following: SET SQL_SAFE_UPDATES = 0;

> You can reset the safe mode with SET SQL_SAFE_UPDATES = 1;

UPDATE student

SET major = 'Graphic Design'

WHERE student_id = 9;

UPDATE student

SET major = 'MIS'

WHERE major = 'Sociology' OR major = 'Graphic Design';

UPDATE student

SET name = 'Julia', major = 'undecided'

WHERE student_id = 1;
[ 9. Basic Queries (1:56:11) ]
SELECT name

FROM tutorial.student;

SELECT name

FROM tutorial.student

ORDER BY name;

SELECT name

FROM tutorial.student

ORDER BY name DESC;

SELECT *

FROM tutorial.student

ORDER BY major, student_id DESC;

SELECT *

FROM tutorial.student

LIMIT 2;
SELECT *

FROM tutorial.student

WHERE major = 'MIS' OR name = 'Julia';

SELECT *

FROM tutorial.student

WHERE student_id <=3 AND name <> 'Katie';

<> : not equal to

SELECT *

FROM tutorial.student

WHERE name IN ('Julia', 'Katie', 'Amy');

[ 10. Company Database Intro (2:08:37) ]

 2017 © M
️ ike Dane

2017 © M
️ ike Dane

[ 11. Creating Company Database (2:17:06) ]

To start, delete “student” table : DROP TABLE student;

11–1. Make tables.

[STEP 1] Create “employ” table.

2017 © M
️ ike Dane

CREATE TABLE employee (

emp_id INT PRIMARY KEY,

first_name VARCHAR(40),

last_name VARCHAR(40),

birth_day DATE,

sex VARCHAR(1),

salary INT,

super_id INT,

branch_id INT

);
[STEP 2] Create “branch” table & add a foreign key.

2017 © M
️ ike Dane

CREATE TABLE branch (

branch_id INT PRIMARY KEY,

branch_name VARCHAR(40),

mgr_id INT,

mgr_start_date DATE,

FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL

);

> ON DELETE SET NULL : “It specifies that the child data is set to NULL when the parent
data is deleted. The child data is NOT deleted.” (Tech on the Net)

[STEP 3] Add foreign keys to “employ” table.

2017 © M
️ ike Dane

ALTER TABLE employee

ADD FOREIGN KEY(super_id)

REFERENCES employee(emp_id)

ON DELETE SET NULL;ALTER TABLE employee

ADD FOREIGN KEY(branch_id)

REFERENCES branch(branch_id)

ON DELETE SET NULL;

[STEP 4] Create “client” table & add a foreign key.

2017 © M
️ ike Dane
CREATE TABLE client (

client_id INT PRIMARY KEY,

client_name VARCHAR(40),

branch_id INT,

FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL

);

[STEP 5] Create “works_with” table with composite keys

2017 © M
️ ike Dane

CREATE TABLE works_with (

emp_id INT,

client_id INT,

total_sales INT,

PRIMARY KEY(emp_id, client_id),

FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,

FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE

);

[STEP 6] Create “branch_supplier” table with composite keys

2017 © M
️ ike Dane

CREATE TABLE branch_supplier (

branch_id INT,

supplier_name VARCHAR(40),

supply_type VARCHAR(40),

PRIMARY KEY(branch_id, supplier_name),

FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE

);

To make composite keys, assign more than one key as primary keys.
ON DELETE CASCADE : “It specifies that the child data is deleted when the parent
data is deleted.” (Tech on the Net)
11–2. Insert data.
[STEP 1] Insert data of “Corporate” branch.

2017 © M
️ ike Dane

INSERT INTO employee VALUES(100, 'David', 'Wallace', '1967-11-17', 'M',

250000, NULL, NULL);INSERT INTO branch VALUES(1, 'Corporate', 100,
'2006-02-09');UPDATE employee

SET branch_id = 1

WHERE emp_id = 100;INSERT INTO employee VALUES(101, 'Jan', 'Levinson',

'1961-05-11', 'F', 110000, 100, 1);

[STEP 2] Insert data of “Scranton” branch.

2017 © M
️ ike Dane

INSERT INTO employee VALUES(102, 'Michael', 'Scott', '1964-03-15', 'M',

75000, 100, NULL);INSERT INTO branch VALUES(2, 'Scranton', 102, '1992-
04-06');UPDATE employee

SET branch_id = 2

WHERE emp_id = 102;INSERT INTO employee VALUES(103, 'Angela', 'Martin',

'1971-06-25', 'F', 63000, 102, 2);

INSERT INTO employee VALUES(104, 'Kelly', 'Kapoor', '1980-02-05', 'F',

55000, 102, 2);

INSERT INTO employee VALUES(105, 'Stanley', 'Hudson', '1958-02-19', 'M',

69000, 102, 2);

[STEP 3] Insert data of “Stamford” branch.

 2017 © M
️ ike Dane

INSERT INTO employee VALUES(106, 'Josh', 'Porter', '1969-09-05', 'M',

78000, 100, NULL);INSERT INTO branch VALUES(3, 'Stamford', 106, '1998-
02-13');UPDATE employee

SET branch_id = 3

WHERE emp_id = 106;INSERT INTO employee VALUES(107, 'Andy', 'Bernard',

'1973-07-22', 'M', 65000, 106, 3);

INSERT INTO employee VALUES(108, 'Jim', 'Halpert', '1978-10-01', 'M',

71000, 106, 3);

[STEP 4] Insert data to the “branch supplier” table.

2017 © M
️ ike Dane

INSERT INTO branch_supplier VALUES(2, 'Hammer Mill', 'Paper');

INSERT INTO branch_supplier VALUES(2, 'Uni-ball', 'Writing Utensils');

INSERT INTO branch_supplier VALUES(3, 'Patriot Paper', 'Paper');

INSERT INTO branch_supplier VALUES(2, 'J.T. Forms & Labels', 'Custom

Forms');

INSERT INTO branch_supplier VALUES(3, 'Uni-ball', 'Writing Utensils');

INSERT INTO branch_supplier VALUES(3, 'Hammer Mill', 'Paper');

INSERT INTO branch_supplier VALUES(3, 'Stamford Lables', 'Custom

Forms');

[STEP 5] Insert data to the “client” table.

2017 © M
️ ike Dane
INSERT INTO client VALUES(400, 'Dunmore Highschool', 2);

INSERT INTO client VALUES(401, 'Lackawana Country', 2);

INSERT INTO client VALUES(402, 'FedEx', 3);

INSERT INTO client VALUES(403, 'John Daly Law, LLC', 3);

INSERT INTO client VALUES(404, 'Scranton Whitepages', 2);

INSERT INTO client VALUES(405, 'Times Newspaper', 3);

INSERT INTO client VALUES(406, 'FedEx', 2);

[STEP 6] Insert data to the “works_with” table.

2017 © M
️ ike Dane

INSERT INTO works_with VALUES(105, 400, 55000);

INSERT INTO works_with VALUES(102, 401, 267000);

INSERT INTO works_with VALUES(108, 402, 22500);

INSERT INTO works_with VALUES(107, 403, 5000);

INSERT INTO works_with VALUES(108, 403, 12000);

INSERT INTO works_with VALUES(105, 404, 33000);

INSERT INTO works_with VALUES(107, 405, 26000);

INSERT INTO works_with VALUES(102, 406, 15000);

INSERT INTO works_with VALUES(105, 406, 130000);

[ 12. More Basic Queries (2:30:28) ]

12–1. Display different column titles.
SELECT first_name AS given_name, last_name AS surname

FROM employee;

12–2. Find out all branch IDs.

SELECT DISTINCT branch_id

FROM employee;

[ 13. Functions (2:36:25) ]

13–1. Find the number of employees with supervisors.

SELECT COUNT(super_id)

FROM employee;

13–2. Find the number of female employees born after

1970.

SELECT COUNT(emp_id)

FROM employee

WHERE sex = 'F' AND birth_day > '1970-01-01';

13–3. Find the average of all employees’ salaries.

SELECT AVG(salary)

FROM employee;

13–4. Find out how many males & females there are.
SELECT COUNT(sex), sex

FROM employee

GROUP BY sex;

13–5. Find the total sales of each sales person.

SELECT COUNT(total_sales), emp_id

FROM works_with

GROUP BY emp_id;

[ 14. Wildcards (2:45:15) ]

©️W3Schools

14–1. Find any client who are LLC.

SELECT *

FROM client

WHERE client_name LIKE '%LLC';

14–2. Find branch suppliers who are in the “label”

business.

SELECT *

FROM branch_supplier

WHERE supplier_name LIKE '% Label%';

14–3. Find any employee born in October.

The tutorial showed a way to find any employee born in October with the following query:

SELECT *

FROM employee

WHERE birth_day LIKE '____-10%';

However, I got the following response : Error Code : Incorrect DATE value:
‘____-10%’.

So, I tried the following query and worked!

SELECT *

FROM employee

WHERE MONTH(birth_day) IN(10);

[ 15. Union (2:53:55) ]

Ex) Find a list of all clients & branch suppliers’ names.

Clients : ~ FedEx | Suppliers : Hammer Mill ~

SELECT client_name AS clients_and_suppliers, client.branch_id

FROM client

UNION

SELECT supplier_name, branch_supplier.branch_id

FROM branch_supplier;

[ 16. Joins (3:01:37) ]

Let’s add a branch : INSERT INTO branch VALUES(4, ‘Buffalo’, NULL, NULL);

16–1. Types of Joins

©️SQL Joins Explained

©️SQL Joins Explained

16–2. Inner Join

Find all branches & names of their managers.
SELECT employee.emp_id, employee.first_name, branch.branch_name

FROM employee

JOIN branch

ON employee.emp_id = branch.mgr_id;

16–3. Left Join

SELECT employee.emp_id, employee.first_name, branch.branch_name

FROM employee

LEFT JOIN branch

ON employee.emp_id = branch.mgr_id;

16–4. Right Join

SELECT employee.emp_id, employee.first_name, branch.branch_name

FROM employee

RIGHT JOIN branch

ON employee.emp_id = branch.mgr_id;

16–5. Full Join

MySQL lacks support for Full Outer Join. We can emulate it by combining Left Join
and Right Join with Union.
(SELECT employee.emp_id, employee.first_name, branch.branch_name

FROM employee

LEFT JOIN branch

ON employee.emp_id = branch.mgr_id)UNION(SELECT employee.emp_id,

employee.first_name, branch.branch_name

FROM employee

RIGHT JOIN branch

ON employee.emp_id = branch.mgr_id);

[ 17. Nested Queries (3:11:51)]

17–1. Find names of employees who sold over $50,000

to a single client.
# Step 1 : Get employee IDs that match the criteria.

SELECT works_with.emp_id

FROM works_with

WHERE works_with.total_sales > 30000;

# Step 2 : Get names of employees that match the criteria.

SELECT employee.first_name, employee.last_name

FROM employee

WHERE employee.emp_id IN(

SELECT works_with.emp_id

FROM works_with

WHERE works_with.total_sales > 30000

);
17–2. Find all clients handled by a branch that Michael
Scott manages when you know his ID.
# Step 1 : Find out the branch ID.

SELECT branch.branch_id

FROM branch

WHERE branch.mgr_id = 102;

# Step 2 : Get names of branches that matches the criteria.

SELECT client.client_name

FROM client

WHERE client.branch_id = (

SELECT branch.branch_id

FROM branch

WHERE branch.mgr_id = 102

LIMIT 1

);

[ 18. On Delete (3:21:53) ]

18–1. On Delete Set Null

ON DELETE SET NULL : “It specifies that the child data is set to NULL when
the parent data is deleted. The child data is NOT deleted.” (Tech on the Net)

Let’s delete Michael Scott(ID: 102)’s information. When we created the branch table,
we set mgr_id that referenced emp_id in the employee table as ON DELETE SET NULL.

CREATE TABLE branch (

branch_id INT PRIMARY KEY,

branch_name VARCHAR(40),

mgr_id INT,

mgr_start_date DATE,

FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL

);
DELETE FROM employee

WHERE emp_id = 102;SELECT * from branch;

> Data associated with mgr_id =102 is expressed as “Null”.

18–2. On Delete Cascade

ON DELETE CASCADE : “It specifies that the child data is deleted when the
parent data is deleted.” (Tech on the Net)

Let’s delete the Scranton branch(ID : #2)’s information. When we created the
branch_supplier table, we set branch_id that referenced branch_id in the branch table
as ON DELETE CASCADE.

CREATE TABLE branch_supplier (

branch_id INT,

supplier_name VARCHAR(40),

supply_type VARCHAR(40),

PRIMARY KEY(branch_id, supplier_name),

FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE

);

DELETE FROM branch

WHERE branch_id = 2;SELECT * from branch_supplier;

> All data associated with branch_id = 2 are gone.

18–3. When to use “On Delete Set Null” and “On Delete
Cascade”

For the branch table, we used “ON DELETE SET NULL". It was okay to do that, because
the mgr_id in the branch table is just a foreign key. It’s not a primary key, which means
that mgr_id is not essential for the branch table.

However, in the branch_supplier table, the branch_id is a foreign key and one of primary
keys. That means that branch_id is crucial for the branch_supplier table. Because a
primary key can NOT have a null value, we should delete data when associated
branch_id is deleted.

[ 19. Triggers (3:30:05) ]

Let’s create a table called “trigger_test”.

CREATE TABLE trigger_test(

message VARCHAR(100)

);

19–1. Insert a sentence to a table when a new employee

is added.
I entered the following code at MySQLWorkbench.
DELIMITER $

CREATE

TRIGGER my_trigger BEFORE INSERT

ON employee

FOR EACH ROW BEGIN

INSERT INTO trigger_test VALUES('added new employee');

END$

DELIMITER ;

We used “$” to start and end trigger commands.

Before anything gets inserted into the “employee” table, we’ll insert “added new
employee” into the “trigger_test” table for each item gets inserted in.
At the end, we changed the delimiter back to semicolon(;).

This worked for me. But, if you’re using ‘PopSQL’, this might not work. In that
case, enter the following code in terminal.

Type mysql -u root -p in terminal and enter your password.

> “tutorial” is the name of the database I created to follow this tutorial.

After that, enter the code block above in terminal.

Let’s add a new employee!

INSERT INTO employee

VALUES(109, 'Oscar', 'Martinez', '1980-03-20', 'M', 69000, 106, 3);

19–2. Insert an added value to a table when a new

employee is added.
DELIMITER $

CREATE

TRIGGER my_trigger2 BEFORE INSERT

ON employee

FOR EACH ROW BEGIN

INSERT INTO trigger_test VALUES(NEW.first_name);

END$

DELIMITER ;
Let’s add a new employee!

19–3. Insert a conditional statement to a table when a

new employee is added.
DELIMITER $

CREATE

TRIGGER my_trigger10 BEFORE INSERT

ON employee

FOR EACH ROW BEGIN

IF NEW.sex = 'M' THEN

INSERT INTO trigger_test2 VALUES('added male employee');

ELSEIF NEW.sex = 'F' THEN

INSERT INTO trigger_test2 VALUES('added female employee');

ELSE

INSERT INTO trigger_test2 VALUES('added other employee');

END IF;

END$

DELIMITER ;

Let’s add a new employee!

19–4. Drop a trigger.

DROP TRIGGER my_trigger6;

[ 20. Entity Relationship(ER) Diagram Info (3:42:15) ]

20–1. Entity
Entity : An object we want to model & store information about
 2018 © M
️ ike Dane

20–2. Attributes
Attributes : Specific pieces of information about an entity

2018 © M
️ ike Dane

20–3. Primary Key

Primary Key : Attribute(s) that uniquely identify an entry in a database table

2018 © M
️ ike Dane

20–4. Composite Attribute

Composite Attribute : An attribute that can be broken up into sub-attributes
 2018 © M
️ ike Dane

20–5. Multi-valued Attribute

Multi-valued Attribute : An attribute that can have more than one value

2018 © M
️ ike Dane

20–6. Derived Attribute

Derived Attribute : An attribute that can be derived from other attributes.

2018 © M
️ ike Dane

20–7. Multiple Entities

Multiple Entities : You can define more than one entity in a diagram

2018 © M
️ ike Dane

2018 © M
️ ike Dane

20–8. Relationships
Relationship : defines a relationship between two entities

Total Relationship : All members must participate in the relationship

2018 © M
️ ike Dane

20–9. Relationship Attribute

Relationship Attribute : An attribute about the relationship
 2018 © M
️ ike Dane

20–10. Relationship Cardinality

Relationship Cardinality : The number of instances of an entity from a relationship
that can be associated with the relation

Types : 1 : 1, 1 : N, N : M

2018 © M
️ ike Dane

> N : M : Students can take multiple classes.

20–11. Weak Entity & Identifying Relationship

Weak Entity : An entity that can NOT be uniquely identified by its attributes alone

Identifying Relationship : A relationship that serves to uniquely identify the weak

entity. (* Must be N : M relationship cardinality)

2018 © M
️ ike Dane

20–12. Final Student ER Digram

 2018 © M
️ ike Dane

20–13. ER Diagram Template

2018 © M
️ ike Dane

[ 21. Designing an ER Diagram (3:53:53) ]

21–1. Company Data Requirements

# Step 1

2018 ©️Mike Dane

The company is organized into branches. Each branch has a unique number, a name,
and a particular employee who manages it.

# Step 2

2018 ©️Mike Dane

The company makes it’s money by selling to clients. Each client has a name and a
unique number to identify it.

# Step 3

2018 ©️Mike Dane

The foundation of the company is it’s employees. Each employee has a name, birthday,
sex, salary and a unique number.

# Step 4

2018 ©️Mike Dane

An employee can work for one branch at a time.

 1 : N : A branch can have any number of employees.
Total Participation : A branch must have employees working at the branch.

# Step 5

2018 ©️Mike Dane

Each branch will be managed by one of the employees that work there. We’ll also want to
keep track of when the current manager started as manager.

1 : 1 : A branch is managed by one employee.

Total Participation : All branches must be managed by an employee.
Partial Participation : Not all employees need to be a manager of a branch.

# Step 6

2018 ©️Mike Dane

An employee can act as a supervisor for other employees at the branch, an employee
may also act as the supervisor for employees at other branches. An employee can have at
most one supervisor.

# Step 7
 2018 ©️Mike Dane

A branch may handle a number of clients, with each client having a name and a unique
number to identify it. A single client may only be handled by one branch at a time.

1 : N : A branch can have any number of clients.

Partial Participation : Not all branches need to have a client.
Total Participation : A client must be handled by a branch.

# Step 8

2018 ©️Mike Dane

Employees can work with clients controlled by their branch to sell them stuff. If necessary,
multiple employees can work with the same client. We’ll want to keep track of how many
dollars worth of stuff each employee sells to each client they work with.

N : M : A client can work with any number of employees and vice versa.
Partial Participation : Not all employees need to have a client.
Total Participation : A client must be handled by a employee.

# Step 9
 2018 ©️Mike Dane

Many branches will need to work with suppliers to buy inventory. For each supplier we’ll
keep track of their name and the type of product they’re selling the branch. A single
supplier may supply products to multiple branches.

Weak Entity : An entity that can NOT be uniquely identified by its attributes alone
Identifying Relationship : A relationship that serves to uniquely identify the
weak entity. (* Must be N : M relationship cardinality)

21–2. Final Company ER Diagram

2017 © M
️ ike Dane

[ 22. Converting ER Diagrams to Schemas (4:08:34) ]

# Step 1 : Mapping of Regular Entity Types

 2017 © M
️ ike Dane

For each regular entity type, create a relation(table) that includes all simple attributes of
that entity.

2018 ©️Mike Dane

# Step 2 : Mapping of Weak Entity Types

2018 ©️Mike Dane

For each weak entity type, create a relation(table) that includes all simple attributes of
the weak entity.

> The primary key of the new relation should be the partial key of the weak entity plus
the primary key of its owner.

2018 ©️Mike Dane

# Step 3 : Mapping of Binary 1 : 1 Relationship Types

2018 ©️Mike Dane

Include one side of the relationship as a foreign key in the other. Also, let’s favor total
participation.

2018 ©️Mike Dane

# Step 4 : Mapping of Binary 1:N Relationship Types

 2018 ©️Mike Dane

Include one side’s primary key as a foreign key on the N side relation(table).

2018 ©️Mike Dane

# Step 5 : Mapping of Binary M:N Relationship Types

2018 ©️Mike Dane

Create a new relation(table), which a primary key is a combination of both entities’

primary keys. Also, let’s include any relationship attributes.
 2018 ©️Mike Dane

# Final : Company Database Schema

2018 ©️Mike Dane

# Result
 2017 © M
️ ike Dane

2017 © M
️ ike Dane

Thanks for reading! 🎵 If you like this blog post, please clap👏