? database system Concept 1-data abstraction, model, and SQL

? The DBMS (Database-management system) consists of a database and a program for accessing data, the basic goal of the DBMS is to provide a convenient and efficient way to access data, and for most enterprises, data is very important, so the DBMS must also ensure the security of the data, For example, when a system crashes or an unauthorized access request is received.

first, data abstraction
A) The database system hides the details of the data in terms of storage and maintenance, providing the user with data in the form of a view. The database system must be able to meet the requirements of efficiently querying the data, so the designer abstracts the database system into three layers: physical layer, logical layer, view layer
b) Physical layer, which describes the actual storage of the data in the physical storage media;
The logical layer, which describes how the data is stored in the database and the logical relationship between the data, although complex physical relationships are required to achieve these logical relationships, the logic layer hides these complexities for the user.
The view layer is the highest level of abstraction of the database, although the logic layer hides the complexity of the physical layer, but for the information stored in a complete database, the logic layer is still very complex, except that the DBA may sometimes directly manipulate the logic layer, and the average user only needs to access some of the data. The view layer is meant to simplify the interaction between the ordinary user and the database. The same database can have multiple views.
c) as the data is deleted and changed, the database will change continuously. At a moment the collection of all information in the database is called instance, and the overall structure of the database is called Schema,schema rarely changed. In analogy with a program, the schema is equivalent to a pre-declared variable, and instance is equivalent to the value of the variable.
Databases have different hierarchies, corresponding to different schemas, and the view layer has even multiple subschema.

second, the data model
A data model is a collection of tools that describe data, data relationships, data semantics, and consistency constraints. The data model can describe the design of a database from a physical, logical, and view level, with several types:
A) relational Model
Relational models use tables to describe data and relationships, each table has multiple columns, each column has a unique name, and there is a relationship between the table and the table. Relational model is a record-based model, the data recorded by this model has a fixed number of fields or attributes (columns), the model is most widely used.
b) Entity-relationship Model
The E-R model uses concepts such as the relationship between entities and entities that correspond to real-world objects. This model is also widely used.
c) object-based Data Model
Because the object-oriented programming language becomes the mainstream, the object-based data model comes into being, which combines the characteristics of the model with the relational model and the E-R model, and adds the object-oriented ideas such as encapsulation and method.
d) semistructured Data Model
In a semi-structured data model such as XML, the data is allowed to have different fields, which is not the same as the previous model.

third, the database language
SQL contains DDL (data-definition language) and DML (Data-manpulate language).
A) DML
DML is used to manipulate data such as additions and deletions, divided into process DML and non-procedural DML, the former in use to specify the need to manipulate the data and the mode of operation, the latter only need to specify the required data, and the rest to the database system (the query statement translated into the operation of the physical layer), So the latter is easier to learn and use.
b) DDL
DDL is used to define the schema, access mode, constraints, etc. of the data.
Domain constraints: A constraint on a data type that is checked every time the data is inserted
Referential integrity (referential ingegrity): To ensure that the associated object is present, such as dept_name in a course table associated with a column in a department table, the value of Dept_name must be department actually exist. Modifications to the database can break referential integrity, and you should block operations to avoid breaking referential integrity.
Assertion (assertions): A condition that a database must satisfy at all times, domain constraints and referential integrity are special assertions. It is common to create assertions about a restriction such as "every department must provide at least 5 programs", and then check whether an assertion is satisfied before each change to the data.
Permissions (Authorization): Common types of permissions have read, INSERT, UPDATE, delete, etc., you can control the user has a few permissions.
The result of the DDL statement execution is a data dictionary, which can be thought of as a special table that can only be maintained by the database system. The database will consult the data dictionary before querying and manipulating the data.

Learning materials: Database System concepts, by Abraham Silberschatz, Henry F.korth, S.sudarshan

? database system Concept 1-data abstraction, model, and SQL