SQL and Database Systems 基本知识

Relational Database Basic Concepts

- Databases

Relational Databases Management System consists of one or more databases.

- Entity

Entities are converted to tables as the physical design stage.

- Data Type

The common data type are boolean ,integer,float,currency,string,date and time.

- DDL

DDL stands for Data Definition language. DDL commands can be used to create,modify database structures,Sample commands are CREATE,ALTER and DROP.

- DML 

DML stands for Data Manipulation Language. DML commands can be used to insert data into database tables,retrieve or modify data,deleting data in database.Sample DML commands are INSERT,DELETE and UPDATE.

- DCL 

DCL stands for Data Control Language. DCL commands can be used to create rights and permissions. Sample DCL commands are GRANT and REVOKE.

- Query

SQL developers can use a query to get data or information from one or more database tables.

- Attributes

Types of Attributes:

    - Simple attribute 

    An attribute that cannot be divided into subparts.

   - Composite attribute

    An attribute that can be divided into simple attributes.

   - Derived attribute

    An attribute whose value can be derived(calculated) from other attribute.

   - Single-value attribute

   An attribute contains a single value.

   - Multi-value attribute

   An attribute have more than one values.

- Tables

Each database contains collection of tables.

- Fields(Columns)

Each table consists of smaller entities called fields or columns.

- Records(Rows)

Each table consists of one or more records(rows)

- Primary Key

Each table can have only one primary key.

- Foreign Key

Database tables might be related by (foreign key) common column(s)

- NULL

Null value is a field with no value.

- Constraints

Constraints define rules to restrict what values can be stored in columns.

Common Constraints

 - NOT NULL 

  A column does not accept NULL values.

- DEFAULT

 Set a default value to a column when no value is specified to a column.

- UNIQUE

No duplicated values in a column

- Primary Key

A column or a combination of columns that uniquely defines a row.The primary key column can not contain a NULL value.

- Foreign Key

A foreign key in one table point to a candidate key in another table.

- CHECK

Check whether the value is valid or not

- Data Integrity

 - Entity Integrity

   No duplicate records in a table

 - Referential Integrity

   Referential integrity is violated when deleting a row that is referenced by a foreign key in another table.

- Entity Relational Diagram(ERD)

- Types of Relationships

  - One-to-Many Relationships

  - Many-to-Many Relationships

  - One-to-One Relationships

  - Self-Referencing Relationships