SQL for Beginners

cmdlhz’s 2019 Self-learning Story #15

23 min readMay 26, 2019

[cmdlhz’s 2019 Self-leaching Story] Series

[JavaScript]
  🌱 Net Ninja - JavaScript ES6
  🌱 Net Ninja - Asynchronous JavaScript
  🌱 Net Ninja - Object Oriented JavaScript
  🌱 Coding Train - JavaScript ES6-ES8 (Part1, Part2)[Regular Expressions]
  🌱 Coding Train - Programming with Text[Vue]
  🌱 Net Ninja - Vue CLI3
  🌱 Net Ninja - Vue JS2
  🌱 Net Ninja - Vuex
  🌱 Net Ninja - Vuetify (Part1, Part2, Part3)[Python/Django]
  🌱 Net Ninja - Python3
  🌱 Net Ninja - Django[Figma]
  🌱 Design Course
  🌱 Figma (Part1, Part2, Part3, Part4, Part5)[Illustrator]
  🌱 TastyTuts (Part1, Part2, Part3)[SQL]
  🌱 freeCodeCamp

🌱 This is my summary of SQL for Beginners by freeCodeCamp on YouTube.

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.

> 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).

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

“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.

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

MySQL

Built on MySQL Enterprise Edition and powered by the Oracle Cloud, Oracle MySQL Cloud Service provides a simple…

www.mysql.com

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

PopSQL - Modern, collaborative SQL editor for your team

Write queries, visualize data, and share your results.

popsql.com

I will use MySQL Workbench instead.

MySQL :: MySQL Workbench

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data…

www.mysql.com

[ 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';

MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in…

stackoverflow.com

> 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) ]

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

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

11–1. Make tables.

[STEP 1] Create “employ” table.

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.

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.

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.

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

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

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.

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.

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.

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.

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.

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.

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) ]

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

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);

MySQL FULL JOIN?

Following are my query and I want the result as given below. How can I do this in mysql ? SELECT Persons.LastName…

stackoverflow.com

[ 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

20–2. Attributes

Attributes : Specific pieces of information about an entity

20–3. Primary Key

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

20–4. Composite Attribute

Composite Attribute : An attribute that can be broken up into sub-attributes

20–5. Multi-valued Attribute

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

20–6. Derived Attribute

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

20–7. Multiple Entities

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

20–8. Relationships

Relationship : defines a relationship between two entities

Total Relationship : All members must participate in the relationship

20–9. Relationship Attribute

Relationship Attribute : An attribute about the relationship

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

> 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)

20–12. Final Student ER Digram

20–13. ER Diagram Template

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

21–1. Company Data Requirements

# Step 1

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

# Step 2

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

# Step 3

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

# Step 4

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

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

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

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

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

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

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

# Step 1 : Mapping of Regular Entity Types

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

# Step 2 : Mapping of Weak Entity Types

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.

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

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

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

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

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

Create a new relation(table), which a primary key is a combination of both entities’ primary keys. Also, let’s include any relationship attributes.

# Final : Company Database Schema

# Result

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