教材：Database Systems: Design, implementation, and management (14th ed.) - Coronel, C., & Morris, Steven (2023).
记录Chapter 1 -10 基础部分，不包括chapter 11 - 12 高级数据库技术。
其他参考书：
1、oracle-sql-developer-data-modeler-users-guide
2、oracle-sql-developer-users-guide
3、Oracle_SQL_Functions
=====================
目录：
一、基本概念
二、数据库设计
三、关系数据库模型
四、SQL基础
五、NoSQL基础
六、工具
=====================
一、基本概念
1、database：a shared, integrated computer structure that stores a collection of the following:
a) End-user data—that is, raw facts of interest to the end user
b) Metadata, or data about data, through which the end-user data is integrated and managed.
2、Types of Databases
3、DBMS：A database management system (DBMS) is a collection of programs that manages the database structure and controls access to the data stored in the database.
4、Data modeling：the first step in designing a database, refers to the process of creating a specific data model for a determined problem domain.
5、Data model：A representation, usually graphic, of a complex “real-world” data structure. Data models are used in the database design phase of the Database Life Cycle.
6、Evolution of Major Data Models
7、Data Model Basic terminology
=====================
二、数据库设计
1、The Database Design Life Cycle：
a) Requirements definition
b) Conceptual Design
c) Logical Design
d) Physical Design
2、Requirements definition: Identify and analyse user views，output can be statements which describe the user views' particular requirements and constraints.
3、Comceptual model: The output of the conceptual design process. The conceptual model provides a global view of an entire database and describes the main data objects, avoiding details,i.e, independent of all physical implementation considerations
4、The most widely used conceptual model is the ER model.
5、Logical model：The output of the logical design process. This is a data model which targets a particular database type (e.g. relational, hierarchical, network, object-oriented, noSQL)， but independent of any implementation details which are specific to any particular vendors DBMS package.
6、Normalisation technique is used to test the correctness of a relational logical model
7、The physical design is to develop a strategy for the physical implementation of the logical data model.
8、Physical Model：The output of physical design process . A model in which physical characteristics such as location, path, and format are described for the data. The physical model is both hardware- and software dependent.
=====================
三、关系数据库模型The Relational Database Model
1、The relational model, introduced by E. F. Codd in 1970, is based on predicate logic and set theory. It's the fundamental basis for the relational DBMS.
2:key: One or more attributes that determine other attributes.
3、Relational algebra：defines the theoretical way of manipulating table contents using relational operators. 8 basic operations:
a) for single relation: selection, projection
b) between two relations: join, union,intersection, difference division
4、Normalization of Database Tables: normalization is process that assigns attributes to entities so that data redundancies are reduced or eliminated.
5、ER模型（实体关系模型）可以用作概念模型设计，也可以用作逻辑模型设计，在概念模型中不会出现PK，FK这些术语。
a) conceptual model: entity,attribute, instance, identifier(KEY)...
b)logical model:relation,attribute, tuple, PK/FK...
c)Physical model:table,column,row, PK/FK...
6、Data Modeling with Oracle SQL Developer：Oracle SQL Developer Data Modeler is a free graphical tool that enhances productivity and simplifies data modeling tasks. 主要功能包括：
a)概念模型：支持实体关系模型（ER模型）的创建和管理，用于捕捉高层次的业务需求。
b)逻辑模型：将概念模型转换为逻辑模型，包含具体的数据类型和约束，用于详细的数据库设计。
c)物理模型：将逻辑模型转换为物理模型，生成实际的数据库表结构和DDL脚本。
d)逆向工程：从现有的数据库中生成数据模型，方便数据库的重构和优化。
e)正向工程：从数据模型生成数据库脚本，用于创建或更新数据库架构。
f) 多种数据库支持：不仅支持Oracle数据库，还支持其他常见的数据库系统，如MySQL、SQL Server、PostgreSQL等
=====================
四、SQL基础
1、SQL Statements
a) Data Definition Language (DDL) – CREATE, ALTER, DROP
b) Data Manipulation Language (DML)– INSERT, UPDATE, DELETE,SELECT
c）Data Control Language (DCL)– GRANT
d）TCL (Transaction Control Language) - COMMIT，ROLLBACK
2、TCL (Transaction Control Language): COMMIT/ROLLBACK only applicable to INSERT/UPDATE and DELETE
3、ACID：Each individual transaction must display atomicity, consistency, isolation, and durability. These four properties are sometimes referred to as the ACID.
4、SEQUENCE：Oracle supports auto-increment of a numeric PRIMARY KEY
5、SQL basic
a) Comparison Operators: =, !=,< >, <, >, <=, >=
b)Range operator: BETWEEN
c)set Membership operator: IN
d)Pttern Match operator: LIKE
e)logical operators: AND，OR，NOT
f) Arithmetic operators
g)column alias
h)ordering/sorting result:ORDER BY ... ASC/DESC
i)removing duplicate rows:DISTINCT
j)inner joins (on, using, natual)
k) Aggregate Functions: COUNT, MAX, MIN, SUM, AVG
l)GROUP BY clause
j)HAVING clause: put a condition or conditions on the groups defined by GROUP BY clause.
6、SQL advanced
a）CASE statement：used in a select list enables a query to evaluate an attribute and output a particular value based on that evaluation.
b) Subquery: nested, inline, correlated
c) Views: virtual table derived from one or more base tables.
d)Joins：self join, outer join(full outer join,left outer join, right outer join)
e) Set Operators:UNION All,UNION,INTERSECT,MINUS
f) Oracle Function: EXTRACT, DECODE, LPAD，NVL(replace a NULL with a value)
7、Comparison Operators for query
a) Operator for single value comparison: =, <, >
b) Operator for multiple rows or a list comparison: equality(IN), inequality(ALL, ANY combined with <, >)
8、SQL Tuning
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/introduction-to-sql-tuning.html
=====================
五、NoSQL基础
1、NoSQL(Not only SQL)：A new generation of DBMS that is not based on the traditional relational database model.NoSQL databases are designed to handle the unprecedented volume of data, variety of data types and structures, and velocity of data operations that are characteristic of these new business requirement.
2、NoSQL databases
3、 Big Data refers to a set of data that displays the characteristics of volume, velocity, and variety (the 3 Vs) to an extent that makes the data unsuitable for management by a relational database management system. These characteristics can be defined as follows:
a) Volume—the quantity of data to be stored
b) Velocity—the speed at which data is entering the system
c) Variety—the variations in the structure of the data to be store
4、MongoDB Predicate Operators:
https://docs.mongodb.com/manual/reference/operator/query
example：db.documents.find( { qty: { $eq: 20 } }
5、Oracle NoSQL Database
https://www.oracle.com/au/database/nosql/technologies/nosql/
=====================
六、工具
1、免费的oracle数据库，”Try OCI for free“
https://www.oracle.com/au/cloud/
2、Oracle REST Data Services：提供界面支持数据库操作，如调用存储过程、执行复杂查询等。也为Oracle数据库提供RESTful接口，使开发者能够通过HTTP和REST协议与数据库进行交互。
https://www.oracle.com/database/sqldeveloper/technologies/db-actions/download/
3、可以本地安装的MongoDB
https://www.mongodb.com/try/download/community
https://www.mongodb.com/docs/manual/administration/install-community/
4、MongoDB安装手册和使用手册
https://www.mongodb.com/docs/manual/administration/install-community/
https://www.mongodb.com/docs/manual/tutorial/getting-started/
5、VSS code：
https://code.visualstudio.com/Download
6、VSS extentions for oracles and Mongodb client
a) Oracle eveloper Tools for VS Code (SQL and PLSQL)
b）Mongodb for vscode：
https://www.mongodb.com/docs/mongodb-vscode/
7、Visual Studio Code Shortcuts
https://code.visualstudio.com/shortcuts/keyboard-shortcuts-windows.pdf
8、Oracle SQL Developer Data Modeler
https://www.oracle.com/database/sqldeveloper/technologies/sql-data-modeler/download/
9、画图工具
a) Lucidchart - it can draw a wide range of different diagrams, including ER Diagrams
https://lucid.app/documents#/documents?folder_id=recent
b) DrawIO (https://www.drawio.com/) ,
c) Gliffy (https://www.gliffy.com/)
