What is a Database (DB)?

• Any collection of related information
• Phone Book
• Shopping list
• Todo list
• Your 5 best friends
• Facebook’s User Base

• Databases can be stored in different ways
• On paper
• In your mind
• On a computer
• This powerpoint
• Comments Section

• Computers are preferred tools to store data.
Computers are great at keeping track of large amounts of information

Database Management Systems (DBMS)

• A special software program that helps users create and maintain a database
• Makes it easy to manage large amounts of information
• Handles Security
• Backups
• Importing/exporting data
• Concurrency
• Interacts with software applications

C.R.U.D

• four main operations

• Create: Insert new data into tables.

• Read: Retrieve data with SELECT statements.

• Update: Change existing records.

• Delete: Remove records you no longer need.

Two Types of Databases

• Organize data into one or more tables
• Each table has columns and rows
• A unique key identifies each row

• Organize data is anything but a traditional table
• Key-value stores
• Documents (JSON, XML, etc)
• Graphs
• Flexible Tables

Relational Databases (SQL)

• Relational Database Management Systems (RDBMS)
• Help users create and maintain a relational database
• mySQL, Oracle, postgreSQL, maria DB, etc.

• Structured Query Language (SQL)
• Standardized 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 and structures
• SQL code used on one RDBMS is not always portable to another without modification.

Non-Relational Databases (noSQL / not just SQL)

• JSON：读作 J-son

• Non-Relational Database Management Systems (NRDBMS)
• Help users create and maintain a non-relational database
• mongoDB, dynamoDB, apache cassandra, firebase, etc

• Implementation Specific
• Any non-relational database falls under this category, so there’s no set language standard.
• Most NRDBMS will implement their own language performing C.R.U.D and administrative operations on the database.

Database Queries

[Wrap Up]

• Database is any collection of related information

• Computer are great for storing databases

• Database Management Systems (DBMS) make it easy to create, maintain and secure a database.

• DBMS allow you to perform the C.R.U.D operations and other administrative tasks

• Two types of Databases, Relational & Non-Relational

• Relational databases use SQL and store data in tables with rows and columns

• Non-Relational data store data using other data structures

Tables and Keys

• Column: vertical entry
E.g. major column

• Row: horizontal entry

• Primary key: special column
• Uniquely identify the row and database
• here: student id
• (uniquely) identify a specific row

• Primary key: email

• Primary key: emp id
a surrogate key is basically a key that has no mapping to anything in the real world
代理键 (Surrogate Key)是数据仓库中用于唯一标识表中每一行记录的人工键 (Artificial Key)，通常是一个自增的整数或全局唯一标识符 (GUID)。 与业务系统中的自然键 (如身份证号、订单号等)不同，代理键不包含任何业务含义，仅用于技术实现。 代理键不依赖业务规则，仅用于数据关联。 即使业务键发生变化，代理键保持不变，避免数据关系断裂。 通常使用整数类型，占用空间小，索引效率高。

• a natural key: a key that has a mapping or has a purpose in the real world, not just in the database.

• a foreign key: an attribute that we can store on a database table that will link us to another database table.
• a foreign key stores the primary key of a row in another database table.

• branch_id 和 supplier_name都有重复的值，所以它们单独都不能identify，但两者结合可以identify。

• Works_With Table
给客户卖出多少
• composite key: emp_id 和 client_id
• emp_id和client_id都是 foreign key

ChatGPT 总结 primary key/surrogate key/natural key/foreign key/composite key

我帮你用简单的例子解释这几个数据库中的常用键（keys），顺序按关联性排列，更容易理解：

---

1. Primary Key（主键）

• 定义：唯一标识表中每一条记录的字段（或字段组合），不能重复，也不能为空。

• 例子：在“学生”表中，用学号 student_id 作为主键，因为每个学生有唯一的学号。

---

2. Surrogate Key（代理键）

• 定义：人为生成的、没有实际业务含义的唯一标识符，通常是自增数字或UUID。

• 例子：虽然学生有学号，但为了方便数据库设计，给每个学生一个自动生成的ID，比如 student_auto_id = 1, 2, 3...，这个ID就是代理键。

---

3. Natural Key（自然键）

• 定义：在业务上天然唯一的字段，可以直接作为唯一标识的键。

• 例子：学生的身份证号 ID_number，它本身就是唯一的且有实际意义，所以可以用作自然键。

---

4. Foreign Key（外键）

• 定义：一个表中的字段，用来关联另一个表的主键，保证数据的参照完整性。

• 例子：在“选课”表中，有 student_id 字段，它指向“学生”表的主键 student_id，表示这条选课记录属于哪个学生。

---

5. Composite Key（复合键/组合键）

• 定义：由多个字段组合起来作为主键，联合唯一标识一条记录。

• 例子：在“选课”表中，单独 student_id 或 course_id 不能唯一确定一条记录，但两个字段组合 (student_id, course_id) 可以唯一确定学生选了哪门课，这就是复合主键。

---

简单总结表格

术语	含义	例子
Primary Key	唯一标识一条记录	学生表里的 student_id
Surrogate Key	系统生成的无业务含义的唯一ID	自动生成的学生ID（1，2，3，...）
Natural Key	业务上天然唯一的字段	学生身份证号 ID_number
Foreign Key	指向另一张表主键的字段	选课表中的 student_id
Composite Key	多字段组合起来作为主键	选课表中的 (student_id, course_id)

---

SQL Basics

Structured Query Language (SQL)

• SQL is a language used for interacting with Relational Database Management Systems (RDBMS)
• You can use SQL to get the RDBMS to do things for you
• Create, retrieve, update & delete data
• Create & manage databases
• Design & create database tables
• Perform administration tasks (security, user management, import/export, etc)

• SQL implementations vary between systems
• Not all RDBMS’ follow the SQL standard to a ‘T’
• The concepts are the same but the implementation may vary

• SQL 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.
• Get information that is already stored there
• Data Definition Language (DDL)
• Used for defining database schemas.
• Data Control Language (DCL)
• Used for controlling access to the data in the database.
• User & permissions management
• Data Manipulation Language (DML)
• Used for inserting, updating and deleting data from the database.

Queries

• A query is a set of instructions given to the RDBMS (written in SQL) that tell the RDBMS what information you want it to retrieve for you
• TONS of data in a DB
• Often hidden in a complex schema
• Goal is to only get the data you need

MySQL Windows Installation

1. search in Google for “mysql community server”



• MySQL :: Download MySQL Community Server

2. 下载，点击 “no thanks, just start my download”

Mysql8.1.0 安装问题-缺少visual studio 2019x64组件_mysql安装时提示缺少vs2019-CSDN博客

3. 不用Developer Default，因为会下载很多东西。点击Custom。

4. 点击MySQL Server - 下面的选项点击，右侧出现

5. MySQL Shell 下面的内容同样放到右侧
以上两样是讲师用到的。如有需要可以全部下载（Developer Default）

我安装的时候选的是Typical

6. 一路Next，设置 MySQL Root Password（自己要记住，后续用）
MySQL Root Password
Password

7. Create a database that I can use
在搜索栏点击MySQL… Command Line Client

8. 单击打开，Enter password

9. 输入 create database girrafe; hit Enter

• 更好Visualize

1. 搜索popsql
• PopSQL - Collaborative SQL Editor - Bring Order to SQL Chaos
• Start using PopSQL | PopSQL [Download]

2. Download and sign in

3. Connect to your database



1. Type: MySQL
2. Hostname: localhost
3. Port: 3306
4. Database: giraffe
刚才创建的名字
5. Username: root
6. Password
刚才的密码

我登录后没有这些设置。

MySQL Mac Installation

1. 搜索mysql community server

2. 下载mac OS第一个

3. temperory password：localhost后面的，记得copy

4. set up MySQL server
• search for system preferences，弹出：

5. click on MySQL
输入密码



a database server is running on our computer

6. Update our password:
1. open up terminal
type in mysql
• not found





2. type in echo ‘export PATH=/usr/local/mysql/bin:$PATH’>>~/.bash_profile



the command is now working
3. type in mysql -u root -p
输入刚才的temporary password



4. change password
type in ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘password’;



5. 重新type in mysql -u root -p，这次可以输入新密码

7. type out create database giraffe;

8. type in exit
clear up

9. log in again
mysql -u root -p



 

1. type out create database giraffe
create a database called giraffe
semicolon
click enter

2. 下面将使用 PopSQL [optional]
1. 下载
2. 设置
1. Type: MySQL
2. Hostname: localhost
3. Post: 3306
default
4. Database
database的名字
5. Username: roots
click connect

Creating Tables

• SQL demand: create table

Datatypes:

1. INT: integer

2. DECIMAL(10,4)

3. VARCHAR: variable char/ variable character
/ˈværiəbl/
1. VARCHAR(100) 储存100 characters

Create a table

• student_id: primary key - uniquely identify each row

• 或者 create table，大写更好identify

• student: SQL reserved words

• name VARCHAR(1000): out of bounds of reality

• define each column
1. student_id
2. name
3. major

• click run

• 鼠标点击到 DESCRIBE student;这一行后，点击Run，可以运行到这一行。

Delete & Modify a Table

• click run

• Add an extra column to the table

• DECIMAL(3,2): three total digits with two of the digits occurring after the decimal point

• 鼠标点到这一行，click run

• gpa一行就删去了

Insert Values

• INSERT INTO student VALUES(); full statement
• () 里面可以放想要store的information

• string需要在 single quotes ‘’里面

• click run

• click run

• click run

• not including major

• specify what information you wanna include in the table

• Click 2次run 会出现：Duplicate entry ‘3’ for key ‘PRIMARY’

• 换成4，则Work

Constraints

• DROP TABLE student; — there’s no such table

• Constrainst:
• NULL: doesn’t have a value
• NOT NULL: can’t be NULL
• UNIQUE
DEFAULT value
 
AUTO_INCREMENT
好的，我们来仔细解释 AUTO_INCREMENT。
---
1. 定义
AUTO_INCREMENT 是数据库（如 MySQL）中一个字段属性，用于 自动给新插入的记录生成一个递增的整数值。
通常用于 主键（primary key），这样你每次插入数据时不用手动指定唯一 ID。
---
2. 特点
1. 自动递增
• 第一个插入的记录默认从 1 开始（可以修改起始值），第二条自动 +1，第三条再 +1，以此类推。
2. 唯一性
• 通常和主键搭配使用，保证每条记录有唯一标识。
3. 只作用于整数类型
• 常用 INT 或 BIGINT。
---
3. 例子
查询结果：
student_id	name	major
1	Jack	Biology
2	Kate	Siology
如果你再插入一条记录，student_id 会自动变成 3。
---
💡 小提示
• 如果你手动给 student_id 指定了值，MySQL 会用你指定的值，并从这个值继续递增下一条自动生成的值。
• AUTO_INCREMENT 字段必须是索引（通常是主键）。
---
老师的演示：即使
• 结果依然会出现 student_id

• 如果运行，不可以。因为上面我们规定了name不可以是NULL

Update & Delete

• update and delete rows/entries inside a table

Run

• WHERE: conditions

• Update specific thing

Run

• student id是4的Jack：Bio变为Comp Sci

• OR logic

Run

• Jack和Claire的学科变为了Biochemistry

把鼠标点到第一行 SELCT * FROM student; ，然后run

第一行的学生名变为了Tom，major变为了undecided

把鼠标点到第一行 SELCT * FROM student; ，然后run

第五行Mike被删掉了

把鼠标点到第一行 SELCT * FROM student; ，然后run

Tom也被删去了

• Deleted everything

Basic Queries

• getting information from the database
• SELECT keyword

• 也可以写在一行

• *表示所有

RUN

RUN

只出现 name和major

RUN

RUN

• not returning student_id, but can still order by it

order them by major first, and then by student_id

RUN

• Filtering : WHERE condition

RUN

RUN

• less than

• greater than

• less than and equal to

• greater than and equal to

• equal to

• not equal to <>

• major not equal to Chemistry

RUN

• IN keyword

• if the name is Claire, Kate, Mike, gonna select that

RUN

RUN

Company Database Info

• Primary key: student id

• primary key: emp_id 【标红】

• foreign key: super_id, branch_id【标绿】

• foreign key: mrg_id

• foreign key: branch_id

Creating Company Database

• 依旧是在PopSQL里面演示

More Basic Queries

Functions

• blocks of codes to do things

Find out how many males and females there are 的结果

aggregation

Find the total sales of each salesman 的结果

Wildcards

Find any employee born on the 10th day of the month 的结果

Union

• SELECT employee.first_name AS Employee_Branch_Names
• AS是change最终显示的column名称为Employee_Branch_Names

以下这种写法会出错，因为一个有2个columns，一个只有一个column。所以Rule: column一致

Joins

• 在 SQL 里，JOIN 用来把两个或多个表的数据根据某些条件组合在一起，常见于查询有关系的表。
In SQL, JOIN is used to combine rows from two or more tables based on a related column between them.

INNER JOIN

• INSERT INTO branch VALUES(4, "Buffalo", NULL, NULL);
• 后面两个NULL的解释：not gonna have a manager ID, and a manager start date

INSERT INTO branch VALUES(4, "Buffalo", NULL, NULL); Result

employee table和branch table都有 employee’s IDs【JOINS可以把两个table通过 related column（这里就是employee’s IDs）关联】

LEFT JOIN

Result

RIGHT JOIN

• Include all of the rows from the Branch table no matter what.

Result

FULL OUTER JOIN

• a LEFT JOIN and a RIGHT JOIN combined.

• 返回左右两表的 所有记录。匹配的会合并显示，不匹配的补 NULL。
Returns all rows from both tables. Matches are combined, non-matches are filled with NULLs.

Nested Queries

• Nested query is basically a query where we’re going to be using multiple SELECT statements in order to get a specific piece of information.

• prefix with the table name just in case we have repeated column names
works_with.

Result

• WHERE employee.emp_id IN ( )
这里 use a nested query

• If the employee ID got returned from this query
SELECT works_with.emp_id FROM words_with WHERE works_with.total_sales > 30000;
which gave us the ID’s of all of the employees who have sold over 30,000,
then we’re going to return their first name and last name.

Result

• 首先execute ()内部的
SELECT branch.branch_id FROM branch WHERE branch.mgr_id = 102;

• 再执行外部的
SELECT client.client_name FROM client WHERE client.branch_id = ();

Result

ON DELETE

• Deleting entries in the database when they have foreign keys associated to them.

• Useful when we’re defining foreign key relationships between tables.

ON DELETE SET NULL

• If we delete one of these employees, that means that the manager ID that was associated to that employee is going to get set to NULL.

• If the employee ID in the Employee table gets deleted, I want to set the manager ID equal to NULL.

ON DELETE CASCADE

• If we delete the employee whose ID is stored in the manager ID column, then we’re just going to delete this entire row in the database.

• If the branch ID that’s stored as the foreign key in the Branch Supplier table gets deleted, then we’re just going to delete the entire row in the database.

• All of those branch supplier rows are going to get deleted that had 2 as their foreign key.

Result

Triggering

• Trigger: a block of SQL code which we can write which will define a certain action that should happen when a certain operation gets performed on the database.
• E.g. an entry was added into a particular table in the database.

• A great way to control what happens when ofther stuff happens, you can automate a lot of the stuff on the backend of your database.

• DROP TRIGGER my_trigger;
my_trigger is no longer going to be active.

• Change the SQL delimiter that we’re going to use.

• Do in terminal.
• Windows: open up the MySQL Command Line Client
• OSX
什么是OSX
OSX 指的是 Apple 的操作系统 macOS（以前叫 Mac OS X）。它是苹果电脑（Mac）上使用的操作系统。
---
1️⃣ 名称来源
• 原名：Mac OS X
• “X” 是罗马数字 10，表示它是 Mac OS 的第十代。
• 现在叫法：macOS
• Apple 从 2016 年开始统一命名风格，改成 macOS（比如 macOS Ventura、macOS Sonoma）。
---
2️⃣ 特点
• 基于 UNIX：安全性高，稳定性好。
• 图形界面友好：操作直观、美观。
• 与 Apple 生态兼容：和 iPhone、iPad 的应用、服务协同良好。
• 自带软件丰富：如 Safari、Mail、Finder、Time Machine 等。
---
3️⃣ 常见用途
• 日常办公（文档、邮件、网页浏览）
• 软件开发（支持 Xcode、Terminal）
• 图形设计、视频剪辑、音频制作
• 科学计算和数据分析（通过 Terminal 或 IDE）
---
简单记忆：
OSX = Mac 电脑操作系统 = macOS = Mac OS X（第十代 Mac 系统）
---
如果你想，我可以帮你列一个 Windows vs macOS vs Linux 的区别表，方便对比。
你希望我列吗？
type in mysql -u root - p ，hit Enter.
prompt you for your password and you should be logged in.

• We can create a trigger by saying CREATE and then I can say TRIGGER.

• TRIGGER my_trigger BEFORE INSERT
• define trigger, give it a name “my_trigger”
• before something gets inserted on the Employee table, for each of the new items that are getting inserted I want to insert into the trigger test table the values ‘added new employee’.
或者说： Before anything gets inserted on the Employee table now, I’m going to perform INSERT INTO trigger_test VALUES('added new employee');

• DELIMITER : a special keyword in MySQL
• It’ll change the MySQL delimiter. 分隔符
• Normally, the MySQL delimiter is a semicolon（分号）
• inside of these FOR EACH ROW BEGIN , I have to use this semicolon over here.
to end off the SQL command INSERT INTO trigger_test VALUES('added new employee');
So, I can’t actually use the same delimiter in order to end off the trigger creation. 不能使用相同的分隔符来结束触发器的创建。
But if I don’t change the delimiter, then this semicolon is basically going to tell SQL that we’re done creating the trigger, even though we’re clearly not. 但如果我不改变分隔符，我们已经完成了触发器的创建，尽管显然还没有。
• So I’m changing the delimiter to two dollar signs to delineate that the trigger is done being created. 所以，我这里将分隔符改为了两个美元符号，表明触发器已创建完成。
And then I can ust delinear back to a semicolon. 然后我就可以直接将其还原为分号。

• The reason that I have to do this over here in the terminal is because in PopSQL you can’t actually configure the delimiter（无法配置分隔符）. So the delimiter is actually something that’s defined not on the text editor level.

• Add in Oscar, Martinez
VALUES(109, 'Oscar', 'Martinez', '1968-02-19', 'M', 69000, 106, 3);

• Select all from trigger_test table.

Result

• INSERT INTO trigger_test VALUES(NEW.first_name);
• allow me to access a particular attribute about the thing that we just inserted. 允许我访问我们刚刚插入的东西的特定属性
• 因此，NEW.first_name 将为我提供插入的员工的名字。

• Insert Kevin Malone

Result

• 第三行的INSERT 可以换成 DELETE, UPDATE

• 第三行的BEFORE可以换成AFTER

ER Diagrams Intro

• Database schema: all the different tables and the different attributes /ˈætrɪbjuːt/ that are going to be on those tables.
Maybe you have some requirements for the different data that needs to get stored inside of your database and the different relationships that data is going to have.
You can use an ER diagram to act as a middleman between database or storage requirements and the actual database schema.

• ER = Entity Relationship
• Entity: an object we want to model & store information about

• ER diagram: a little diagram that consists of different shapes and symbols and text. It all kind of gets combined together to end up defining a relationship model.

• We can also define a primary key (underline).

• A way that we can sort of notate attributes（标记属性的一种方式）that could be derived from the attributes that we’re storing.

• Single line: this indicates partial participation
Not all students need to take a class.

• Double line: indicates total participation

• M: refers to any number
A student could take basically multiple classes.

Designing an ER Diagram

• Take this document and convert it into an ER diagram

1. The company is organized into branches. Each branch has a unique number, and a name
1. primary key: branch name

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

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

4. An employee can work for one branch at a time

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.
• single participation

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.

7. A branch may handle a number of clients, however a single client may only be handled by one branch at a time.
• new relationship: handle

8. Employees can work with clients controlled by their branch to sell them stuff. If necessary multiple employees can work with the same client.

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.

Converting ER Diagrams to Schemas

Step 1: Mapping of Regular Entity Types

• Employee
• primary key: emp_id

Step 2: Mapping of Weak Entity Types

Step 3: Mapping of Binary 1:1 Relationship Types

• Branch
• Foreign key: mgr_id

Step 4: Mapping of Binary 1:N Relationship Types

Step 5: Mapping of Binary M:N Relationship Types