CPT103-Introduction to Databases 课程笔记

---

1. Introduction to Database

What is Data

• Data is only meaningful under its designed scenario
• Must have ways to create/modify data.
• Must have ways to access data.

What is Database

• Database : Organised collection of data. Structured, arragned for ease and speed of search and retrieval.
• Database Management System (DBMS) : Software that is designed to enable users and programs to store, retrieve and update data from database.

DBMS Functions / Must Haves

• Allow users to store, retrieve and update data.
• Ensure either that all the updates corresponding to a given action are made or that none of them is made.
• Ensure that DB is updated correctly when multiple users are updating it concurrently.
• Recover the DB in the event it is damaged in in any way.
• Ensure that only authorised users can access the DB.
• Be capable of integrating with other software.

1.1 The Relational Model

• The relational model is one approach to managing data.
• The model uses a structure and language that is consistent with first-order predicate logic
• Relational database management systems (RDBMS) are based on the relational model.

Some terminologies

1. relation (table, file) : A relation is a mathematical concept. The physical form of a relation is a table with columns and rows.
2. attribute (column, field) : An attribute is a named column of a relation.
3. domain : A domain is the set of allowable values for attributes.
4. tuple (row, record) : A tuple is a row of a relation.
5. degree : The degree of a relation is the number of attributes it contains.
6. cardinality : The number of tuples in a relation.
7. relation schema : The definition of a relation, which contains the name and domain of each attribute.
8. relational database schema : A set of relation schemas, each with a distinct name.

Properties of Relations

• Relation’s name is unique in the relational database schema.
• Each cell contains exactly one atomic value.
• Each attribute of a relation must have a distinct name.
• The values of an attribute are from the same domain.
• The order of attributes has no significance.
• The order of tuples has no significance.
• No duplicate tuples.

1.2 Relational Keys

It is beneficial to let a program to automatically check for and reject duplicate values in one or more columns for you when tuples are added.

1.2.1 Primary Key

It can be done in database systems by applying a constraint called Primary Key on the columns of a table.

Important Properties

• Columns constrained by a primary key uniquely identifies tuples in a table.
• In relational databases, each table can only have one primary key.
• NULL values are not allowed if a primary key is present.

1.2.2 Super Key

Using more than enough columns to uniquely identify tuples in a table. Super key is taught so that you can avoid them when choosing the columns to be applied with primary keys.

1.2.3 Candidate Key

All these possible primary keys are called Candidate keys.The primary key is just a candidate key chosen by the table designer. There’s no definite way to decide which candidate key should be a primary key.

you can’t necessarily infer the candidate keys based solely on the data in your table.

1.2.4 Foreign Key

It is also very common that tuples in one relation references data from another relation. As a result, a database should provide such mechanism to ensure correct references, this is enforced by foreign key.

One or more attributes within one relation that must match the candidate key of some (possibly the same) relation.

The referenced column must be a candidate key of that table.

---

2. Table and Data

2.1 About SQL

• SQL stands for Structured Query Language.
• SQL consists of two parts:
  • Data Definition Language. (DDL)
  • Data Manipulation Language. (DML)

• SQL : 是一种语言。
• MySQL : 关系数据库，一个软件，使用 SQL 进行数据库管理。
• Phpmyadmin : 是基于 web 的 MySQL 数据库管理工具。

Database Containment Hierarchy

• Clusters : A computer may have one or more clusters. A cluster is a databas server, a computer can run multiple database servers.
• Catalogs : Each cluster contains one or more catalogs. Catalog is just another name for database.
• Schemas（模式） : Schema is a namespace of tables, and security boundary

在 SQL 环境下 Catalog 和 Schema 都属于抽象概念，主要用来解决命名冲突问题。一个 Cluster 包含多个 Catalog，每个 Catalog 包含多个 Schema，每个 Schema 包含多个数据库对象（表、视图、字段等）

关于数据库的 Schema 有很多疑惑，问题经常出现在 Schema 和 Catalog 之间是否有区别，如果有，区别在哪里。对 Schema 产生疑惑的一部分原因是 DBMS 倾向于以自己的方式处理 Schema，而这取决于数据库供应商

1. 在 MySQL 的文档中官方指出，在物理上，Schema 与 Catalog 是同义的。
2. Oracle 的文档却指出，某些对象可以存储在 Catalog 中，但不能存储在Schema 中，所以二者不是一回事
3. 而根据这篇文章 (https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd283095(v=sql.100)?redirectedfrom=MSDN)，Schema 是数据库 SQL Server 内部的一个独立的实体，所以也不是一回事。

参考文章 (https://blog.csdn.net/qq_18975791/article/details/102702942)

2.2 Creating a Database

这里我使用的是 XAMPP

官方文档 (https://dev.mysql.com/doc/refman/8.0/en/)

1. First, create a schema.

   CREATE SCHEMA name;
   --or
   CREATE DATABASE name;
   

   关于 CREATE SCHEMA 和 CREATE DATABASE

   一般习惯于使用 CREATE DATABASE 来创建数据库，使用 CREATE SCHEMA 来创建架构，但是根据官方的说法，从 MySQL 5.0.2 起，两者并无区别。

2. If you want to create tables in this schema, you need to tell MySQL to enter into this schema.

   USE name;
   

   then all following statements like SELECT or CREATE TABLE in the same script, will be executed in this schema.

• Avoid creating or modifying tables in these databases that are created automatically by the system, they are created for database administration purpose.

2.3 Creating Tables

CREATE TABLE [IF NOT EXISTS] name (
	col-name datatype [col-options],
	:
	col-name datatype [col-options],
	[constraint-1],
	:
	[constraint-n]
);
--Contents between [] are optional.

• In SQL, table or column names can have spaces, but it is $\text{strongly not recommended}$.

Data Type	描述
interger(size), int(size), smallint(size), tinyint(size)	整数，括号内规定数字最大位数（0 ~ 255）
decimal(size, d), numeric(size, d)	浮点数，size 规定最大位数，d 规定小数点右侧的最大位数（0 ~ 65）
char(size)	容纳可变长度的字符串，括号内指定最大长度（0 ~ 255）
date	容纳日期（3 字节）

2.4 Data Types

2.4.1 Numerical Data Types

Integers

数据类型	size 大小
TINYINT[(size)] [UNSIGNED] [ZEROFILL]	1 字节
SMALLINT[(size)] [UNSIGNED] [ZEROFILL]	2 字节
MEDIUMINT[(size)] [UNSIGNED] [ZEROFILL]	3 字节
INTERGER / INT[(size)] [UNSIGNED] [ZEROFILL]	4 字节
BIGINT[(size)] [UNSIGNED] [ZEROFILL]	8 字节

Fixed Point（定点数）

数据类型	size 大小
DECIMAL / DEC / NUMERIC / FIXED[(size[, d])] [UNSIGNED] [ZEROFILL]	1 ~ 65

Float

数据类型	描述
FLOAT[(size)] [UNSIGNED] [ZEROFILL]	MySQL 会根据 size 的大小选择单 / 双精度

定点数和浮点数

定点数在 MySQL 内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。MySQL 保存值时进行四舍五入，因此如果在 float(7,4)列内插入 999.00009，近似结果是 999.0001。在没有指定 d 时，默认的整数位为 10，默认的小数位为 0。

2.4.2 String Types

数据类型	描述
CHAR[(size)]	1 字节，固定长度，长度不够会在右侧补空格
VARCHAR[(size)]	2 字节，不固定长度

CHAR is faster than VARCHAR, but occupies more memory.

2.4.3 Date and Time

数据类型	描述
DATE	YYYY-MM-DD
DATETIME[(size)]	YYYY-MM-DD hh:mm:ss[.size]
TIMESTAMP	Similar to DATETIME, but stores the time as UTC time.

2.5 Column Options

• NOT NULL : Values of this column cannot be null.
• UNIQUE : Each value must be unique (candidate key on a single attribute)
• DEFAULT value : A default value for this column if not specified by the user. Doesn’t work in MS Access.
• AUTO_INCREMENT :
  • Must be applied to a key column.
  • A value (usually max(col) + 1) is automatically inserted when data is add.
  • You can also manually provide values to override this behaciour :

    ALTER TABLE name AUTO_INCREMENT = num;
    

2.6 Tuple Manipulation

• INSERT ： add rows into the database.

  INSERT INTO tablename (col1, col2, ...)
  	VALUES (val1, val2, ...),
  	  	 :
  	  	 (val1, val2, ...);
  --or if you are adding a value to every column:
  INSERT INTO tablename VALUES (val1, val2, ...);
  

• UPDATE : Changes values in specified rows based on WHERE conditions, if no condition is given all rows are changed.

  UPDATE tablename
  	SET col1 = val1[, col2 = val2, ...]
  	[WHERE condition]
  

• DELETE : Removes all rows, or those which satisfy a condition.

  DELETE FROM
  	tablename
  	[WHERE condition]
  

2.7 Table Constraints

CONSTRAINT name TYPE details;

• Constraint name is created so that later this constraint can be removed by referring to its name, if you don’t provide a name, one will be generated.
• MySQL provides following constraint types
  • PRIMARY KEY
  • UNIQUE
  • FOREIGN KEY
  • INDEX

2.7.1 Domain Constraints

You can limit the possible values of an attribute by adding a domain constraint. A domain constraint can be defined along with the column or separately.

--8.0.16+
CREATE TABLE People (
	id INTEGER PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	sex CHAR NOT NULL CHECK (sex IN ('M', 'F')), --define along with the column
	CONSTRAINT id_positive CHECK (id > 0)		 --define separately
);

2.7.2 UNIQUE

CONSTRAINT name UNIQUE (col1, col2, ...)

Same effect as the one specified with column options but can be applied to multiple columns and make them one single candidate key.

• One candidate key (a, b, c) : Tuples (1, 2, 3) and (1, 2, 2) are allowed.
• Separate candidate keys (a) (b) (c) : Tuples (1, 2, 3) and (1, 2, 2) are not allowed.

2.7.3 Primary Key

CONSTRAINT name PRIMARY KEY (col1, col2, ...)

Constraint name for a primary key is actually ignored by MySQL, but works in other databases. PRIMARY KEY also automatically adds UNIQUE and NOT NULL to the relevant column definition.

Primary key 与 Unique Key 的区别

1. Primary key 的1个或多个列必须为 NOT NULL，如果列为 NULL，在增加 PRIMARY KEY 时，列自动更改为NOT NULL。而 UNIQUE KEY 对列没有此要求。
2. 一个表只能有一个 PRIMARY KEY，但可以有多个 UNIQUE KEY。

2.7.4 Foreign Key

CONSTRAINT name
	FOREIGN KEY (col1, col2, ...) --Columns of the referencing table
	REFERENCES tablename (col1, col2, ...) --Referenced columns
	[ON UPDATE ref_opt --Reference options
	ON DELETE ref_opt]
--ref_opt : RESTRICT | CASCADE | SET NULL | SET DEFAULT

• In MySQL, string comparison is case-insensitive.

• BINARY : This keyword instructs MySQL to compare the characters in the string using their underlying ASCII values rather than just their letters.

  CREATE TABLE tablename (
  	col1 TYPE BINARY NOT NULL,
  	PRIMARY KEY (col1)
  );
  

• In other databases, string comparison can be treated differently.

Reference Options

• There are several options when this occurs:
  • RESTRICT : stop the user from doing it (the default option)
  • CASCADE : let the changes flow on
  • SET NULL : make referencing values NULL
  • SET DEFAULT : make referencing values the default for their column (not available in MySQL)
• These options can be applied to one or both kinds of the table updates:
  • ON DELETE
  • ON UPDATE

The reasonable definition:

CONSTRAINT name
	FOREIGN KEY (col1)
	REFERENCES tablename (col1)
		ON DELETE SET NULL
		ON UPDATE CASCADE

2.8 Altering Tables

• Add column:

  ALTER TABLE tablename
  	ADD col TYPE [opt];
  

• Drop column:

  ALTER TABLE tablename DROP COLUMN col;
  

• Modify column name and definition:

  ALTER TABLE tablename CHANGE COLUMN col
  	newcol TYPE [opt];
  

• Modify column definition only:

  ALTER TABLE tablename MODIFY COLUMN col
  	TYPE [opt];
  

• Add a constraint:

  ALTER TABLE tablename
  	ADD CONSTRAINT name TYPE (col);
  

• Removing Constraint:

  ALTER TABLE tablename
  DROP INDEX name | DROP FOREIGN KEY name | DROP PRIMARY KEY
  

  DROP INDEX name can be used to drop unique keys.
  
  

2.9 Deleting Tables

DROP TABLE [IF EXISTS] tablename1, tablename2, ...;

Table will be dropped in that exact order

• All tuples in the dropped tables will be deleted as well.
• Undoing is sometimes impossible.

Foreign Key will prevent DROP under the default RESTRICT option, to overcome this:

• Remove the foreign key constraints first then drop the tables.
• Drop the tables in the correct order (referencing table first)
• Turn off foreign key check temporarily.

---

3. SQL Select

The select statement is designed to allow database clients to look up data from tables

SELECT [DISTINCT | ALL]
	colList FROM tablenames
	[WHERE condition]
	[ORDER BY colList]
	[GROUP BY colList]
	[HAVING condition]

DISTINCT and ALL
By default, select keeps duplicate tuples

• Using DISTINCT after the SELECT keyword removes duplicates
• Using ALL retains duplicates. ALL is used as a default if neither is supplied
  
  

3.1 Expressions in SELECT

You can put simple expressions in SELECT statements

SELECT a, b, a+b AS sum FROM tablename;

All statements that return Boolean values can also be placed in the SELECT section
e.g. : SELECT postcode LIKE ‘gb%’ FROM places;

3.2 Word Search

Commonly used for searching product catalogues etc.

• Need to search by keywords
• Might need to use partial keywords

We can use the LIKE keyword to perform string comparisons in queries, it is not the same as = because it allows wildcard characters. And it is NOT normally case sensitive

SELECT * FROM tablename
	WHERE col LIKE 'word';

LIKE :

• The % can represent any number of characters, including none. The _ represents exactly one character.
• Search for a set of words

  SELECT * FROM tablename
  	WHERE col LIKE 'word1' AND col LIKE 'word2'; --OR can be used too.
  

Date and Time
The comparison of date and time can be done just like numbers.

SELECT * FROM tablename 
WHERE col < '2012-01-01';

But you can also search for dates like a string :

SELECT * FROM tablename 
WHERE col LIKE '2014-11-%';

More about the WHERE Clause
In the WHERE expression, you can use any of the functions and operators that MySQL supports, except for aggregate (group) functions.

3.3 Select and Cartesian Product

Cartesian product of two tables can be obtained by using SELECT * FROM Table1, Table2;

If the tables have columns with the same name, ambiguity will result. This can be resolved by referencing columns with the table name: TableName.ColumnName

SELECT colList FROM table1, table2(
	WHERE table1.col = table2.col
);

3.4 Aliases

Aliases rename columns or tables can make names more meaningful, can shorten names, making them easier to use, can resolve ambiguous names.

--Column alias
SELECT col [AS] newColName
--Table alias
SELECT * FROM tablename [AS] newTableName

You cannot use a column alias in a WHERE clause

Aliases and ‘Self-Joins’
Aliases can be used to copy a table, so that it can be combined with itself.

3.5 Subqueries

A SELECT statement can be nested inside another query to form a subquery. The results of the subquery are passed back to the containing query

SELECT col1 FROM tablename
	WHERE col2 = (
		SELECT col FROM tablename
			WHERE condition)

• The first SELECT part is evaluated first
• For each row of Employee, we check whether col2 equals to the result of the second SELECT
• Often a subquery will return a set of values rather than a single value. We cannot directly compare a single value to a set. There are some options for handling sets:
  • IN
  • EXISTS : checks to see if a set is empty
  • ALL / ANY
  • NOT

3.5.1 IN

Using IN we can see if a given value is in a set of values, NOT IN checks to see if a given value is not in the set

SELECT col FROM tablename
	WHERE col IN setname;

3.5.2 EXISTS

Using EXISTS we can see whether there is at least one element in a given set, NOT EXISTS is true if the set is empty

SELECT col FROM tablename
	WHERE EXISTS setname;

3.5.3 ANY & ALL

ANY and ALL compare a single value to a set of values. They are used with comparison operators like =, >, <, <>, >=, <=

val = ANY (set) is true if there is at least one member of the set equal to val
val = ALL (set) is true if all members of the set are equal to the val

e.g.

SELECT col FROM Employee
	WHERE Salary >= ALL	(
		SELECT Salary FROM Employee);

The result of col is the highest salary

3.6 Joins

Joins can be used to combine tables in a SELECT query.

3.6.1 CROSS JOIN

returns all pairs of rows from A and B, the same as Cartesian product

SELECT * FROM A CROSS JOIN B;

Same as :

SELECT * FROM A, B;

3.6.2 INNER JOIN

returns pairs of rows satisfying a condition

SELECT * FROM A INNER JOIN B ON condition

Can also use a USING clause that will output rows with equal values in the specified columns

SELECT * FROM A INNER JOIN B USING (col1, col2)
--col1 and col2 must appear in both A and B

3.6.3 NATURAL JOIN

returns pairs of rows with common values in identically named columns

SELECT * FROM A NATURAL JOIN B;

A NATURAL JOIN is a special case of an INNER JOIN where the USING clause has specified all identically named columns.

3.6.4 OUTER JOIN

returns pairs of rows satisfyting a condition, but also handles NULL

Sometimes an OUTER JOIN is the most practical approach. We may encounter NULL values, but may still wish to see the existing information

SELECT cols FROM A TYPE OUTER JOIN B ON condition;

TYPE is one of LEFT, RIGHT or FULL

LEFT OUTER JOIN :

RIGHT OUTER JOIN :

FULL OUTER JOIN :

Only LEFT and RIGHT OUTER JOIN are supported in MySQL, if you really want to use FULL OUTER JOIN, you can :

(SELECT * FROM A LEFT OUTER JOIN B ON condition) UNION
(SELECT * FROM A RIGHT OUTER JOIN B ON condition);

3.7 ORDER BY

SELECT col FROM tablename
	WHERE condition
	ORDER BY cols [ASC | DESC]

The ORDER BY clause sorts the results of a query, you should not choose to order by a column that is not in
the result.

3.8 Aggregate Functions

It is possible to put arithmetic expressions in SELECT (*). You can also use aggregate functions to compute summaries of data in a table.

Most aggregate functions (except COUNT) work on a single column of numerical data.

It’s best to use an alias to name the result.

• COUNT : The number of rows
• SUM : The sum of the entries in the column
• AVG : The average entry in a column
• MIN / MAX : The minimum / maximum entries in a column

SELECT COUNT | SUM | AVG | MIN | MAX (*) AS Count FROM tablename;

Combining Aggregate Functions

You can combine aggregate functions using arithmetic

e.g.
MAX(col) - MIN(col) AS Range

3.9 GROUP BY

Sometimes we want to apply aggregate functions to groups of rows

SELECT col1 FROM tables
	GROUP BY col2;

Every entry in col2 should be in col1, be a constant, or be an aggregate function

3.10 HAVING

HAVING is like a WHERE clause, except that it only applies to the results of a GROUP BY query. It can be used to select groups which satisfy a given condition

Cannot use columns or aggregate functions that does not exist after the step of column selection

3.11 Set Operations

Combine the results from two select statements. The results of the two selects should have the same columns and corresponding data types (Union compatible)

---

4. Entity-Relationship Diagrams

4.1 Entity-Relationship Modelling

ER Modelling is used for conceptual design, it consists of three types of components:

• Entities: objects or items of interest
• Attributes: properties of an entity
• Relationships: links between entities
  

4.1.1 Entities

In ER Diagrams, we will represent Entities as boxes with rounded corners. The box is labelled with the name of the class of objects represented by that entity.

4.1.2 Attributes

In an ER Diagram attributes are drawn as ovals. Each attribute is linked to its entity by a line. The name of the attribute is written in the oval

Entities and Attributes

Sometimes it is hard to tell if something should be an entity or an attribute

General guidelines:

• Entities can have attributes but attributes have no smaller parts
• Entities can have relationships between them, but an attribute belongs to a single entity

4.1.3 Relationships

Relationships have

• A name
• A set of entities that participate in them
• A degree: the number of entities that participate (usually 2)
• A cardinality ratio
  • One to one : Written as (1:1)
  • One to many : Written as (1:M) or (1:*)
  • Many to many : Written as (M:M) or (M:N) or (*:*)

Relationships are shown as links between two entities. The name is given in a diamond box. The ends of the link show cardinality.

Many to many Relationships

M:M relationships are difficult to represent in a database, so we can split a M:M relationship into two 1:M relationships


One to one Relationships

We can merge the two entities that take part in a redundant relationship together

4.2 From ER Diagram to SQL Tables

• Entities Become table names
• Attributes of an entity becomes the columns
• Relationships become foreign keys (M:1 are represented as a foreign key from the M-side to the 1)

---

5. Normalisation

Normalisation is a technique of re-organising data into multiple related tables, so that data redundancy is minimised

ER modeling VS. Normalisation

• ER diagram is useful when you have detailed database specifications but no existing table design
• When you already have a database, but the tables are not well designed, you can use normalisation techniques to improve them

e,g,

Data redundancy not only increases memory usage, but also leads to update anomalies

What information can help us to redesign tables

• We can observe the data in the table and find out the relationship between attributes.
• Common sense may also help, but be careful when applying common sense

5.1 Functional Dependency

We want to find relationship between attributes within a table so that we can regroup attributes based on their context and split the big table

If $A$ and $B$ are attribute sets of relation $R$, $B$ is functionally dependent on $A$ (denoted $A\to B$), if each value of $A$ in $R$ is associated with exactly one value of $B$ in $R$.
A is called determinant

The concept of FD is closely related to M:1 and 1:1 relationships. If these attributes are put together, they can form a relation, with the determinant being the unique key or primary key of the relation. For attributes that have a M:1 relationships, if they belong to the same context, they will be grouped into one relation, otherwise, they can be split into two tables and linked with a foreign key.

• Full functional dependency : If $A$ and $B$ are two sets of attributes of a relation, $B$ is fully functionally dependent on $A$, if $B$ is functionally dependent on $A$, but not on any proper subset of $A$. Determinants in full functional dependencies will become candidate keys if we split the table
• Partial FDs : not full FD. Determinants in partial functional dependencies will become super keys.

We only care about full FDs in normalisation

5.2 Transitive Dependency

Transitive dependency describes a condition where $A$, $B$, and $C$ are attributes of a relation such that if $A\to B$ and $B\to C$, then $C$ is transitively dependent on $A$ via $B$ (provided that $A$ is not functionally dependent on $B$ or $C$)

---

5.3 Normal Forms

5.3.1 Normalise to 1NF

A relation is said to be in first normal form (1NF) if all data values are atomic, this means that table entries should be single values, not sets or composite objects.

Method 1

• Remove the repeating group by entering appropriate data into the empty columns of rows containing the repeating data.(flattening the table)
• Assign a new primary / unique key to the new table.

Method 2

• Identify primary / unique key
• Place the repeating data along with a copy of the original (unique) key attribute(s) into a separate relation

5.3.2 1NF to 2NF

A relation is in second normal form (2NF) if it is in 1NF and no non-key attribute is partially dependent on the primary key. In other words, no $C\to B$ where $C$ is a strict subset of a primary key and $B$ is a non-key attribute

Method

• Identify the primary key(s) for the 1NF relation
• Identify the functional dependencies in the relation
• If partial dependencies exist on the primary key, remove them by placing attributes of the corresponding full FD in a new relation and leave the its determinant in the original table

5.3.3 2NF to 3NF

Based on the concept of transitive dependency. A relation that is in 1NF and 2NF and in which no non-key
attribute is transitively dependent on the primary key

Method

• Identify the primary key in the 2NF relation
• Identify functional dependencies in the relation
• If transitive dependencies exist on the primary key, remove them by placing them in a new relation along with a copy of their determinant

数据库范式 1NF, 2NF, 3NF 的问题与细解 (https://www.jianshu.com/p/94a274ef35a9)