某储备粮的“学习笔记”

by 咳嗽di小鱼

Db2=>CONNECT TO db_name 
Db2=>EXPORT TO absolute_file_path OF DEL SELECT * FROM "table_name"
Db2=>IMPORT FROM absolute_file_path OF DEL INSERT_UPDATE INTO "table_name"

Definition实在不知道怎么概括= =...

  • Intention of a relation: relation definition and constrains
  • Extention of a relation: the actual data, tuples

Relation properties

  • Set Theory

    • Attributes has order (not necessary)
    • Value are used to identify tuples
    • Tuples dont have order
    • Tuples cant have duplication
  • Attribute value are atomic
  • Degree: How many attributes in the schema
  • Cardinality: How many tuples in an instance

Constraints

  • Let the DBMS to ensure the entry or modification on data are legal
  • Give applications' bug no chance to ruin the data

Three Constraint Level

  • Tuple-level

    • Domain restrictions (datatype)
    • Attribute comparisons (好像是指check)
  • Relation-level

    • Special type of keys

      • Superkey: 能uniquely identifies a tuple的一个or一组attribute
      • Candidate key: Superkey的最小集, (最简单的一个or一组attribute)
      • Primary key: 被选中, 用来identity tuples的Candidate key
  • Database-level

    • Referential integrity

      • Foreign key: 其他relation中的primary key
      • Referential integrity: 不允许出现和对应relation中的记录不符的情况
    • Inclusion dependencies: = =`不懂

Definitions

  • Database: is a large and persistent collection of pieces of information organized in a way that facilitate efficient retrieval and modification.
  • DBMS: is a program that manages details related to storage and access for a database.
  • Schema: is a description of the data interface to the database.
  • Instance: is a database that conforms to a given schema.
  • Transaction: is an application-specified atomic and durable unit of work.
  • View: is a relation in the external schema whose instance is determined by the instances of the relations in the conceptual schema.
  • Trigger: is a procedure executed by the database in response to a change to the database instance

Three Level Schema Architecture

  • External schema (view): visualized data
  • Conceptual schema: logical structure
  • Physical schema: file/storage devices

Data Independence

  • Application access the data through an abstract data model provided by the DBMS, rather than direct access
  • Physical: applications cant touch the storage stucture
  • Logical: applications cant change the data organization

Four Transaction Properties

NoSQL据说就是要break these properties= =`IDK why...

  • Atomic: once the transaction happens, it happened. No intermediate stage!
  • Consistency: transaction follows the database consitences
  • Isolated: concurrent transactions wont effect each other
  • Durable: transaction results permanently

What DBMS do

  • Remoce common code from applications
  • Provide uniform access to data
  • Guarantee data integrity
  • manage concurrent access
  • Protect against system failure
  • Set access policies for data

SELECT

SELECT attribute-expression-list FROM relation-list [ WHERE condition ];

attribute-expression-list:

  • [relation-name.]attribute
  • [relation-name.]attribute [arithmatic computation] AS another name

    • i.e., E.Salary - 40000 AS SalaryDiff
  • CASE WHEN ... THEN ...
    ELSE ... END

    • i.e., CASE WHEN E.Salary < 40000 THEN 0
      ELSE E.Salary - 40000 END

relation-list:

  • list of table names
  • seperate by comma

condition:

  • arithmetic operation +, -, *, /
  • comparisions =, <>, <, <=, >. >=
  • logical connectives AND, OR, NOT
  • attribute IN (Q)
  • attribute NOT IN (Q)
  • attribute op SOME (Q)
  • attribute op ALL (Q)
  • EXISTS (Q)
  • NOT EXISTS (Q)
  • IS [NOT] NULL

Read more...