[Database study notes] Basic knowledge

1. Overview of the database system

1.1 Concept

data: symbols that describe the transaction
Data Processing: The process of converting data into information. Including the collection, arrangement, storage, processing, maintenance, sorting, retrieval and transmission of data. The purpose is to extract effective information to provide a basis for further analysis, management and decision-making.
Database: DataBase permanent storage, organized, shareable, long-term storage of data collection on the computer
Database management system: The DataBase Managerment System is responsible for the management and maintenance of the database and is located between the user and the operating system. Including data definition function, data manipulation function, database operation management, database establishment and maintenance function.
Database system: DataBase System consists of database, database management system, application system, database administrator, and user.
Database Administrator: DataBase Administrator
Data management: including manual management stage, file system stage, database system stage

1.2 Features

data structuring
When describing data, the database not only describes the data itself, but also describes the connection between the data
High data sharing, low redundancy, and easy expansion
Data can be shared and used by multiple users for each application, which greatly reduces data redundancy, saves storage space, and avoids data inconsistency. Easy to add new applications
High data independence
How data is stored in the database on disk is managed by the DBMS, and the user program does not need to know.
The logical structure of the data is changed, and the user program does not need to be changed.
Data independence is guaranteed by the secondary mapping of the DBMS.
Data is uniformly managed and controlled by DBMS
Users and applications access and use the database through the DBMS. Multiple users can access simultaneously
data in the database.

1.3 Composition

Hardware Platform and Database Software Personnel

2. Data Model

In order to abstract and organize concrete things in the real world into DBMS The data model that can be supported usually first abstracts the real world into a conceptual model, and then converts the conceptual model into a certain model in the computer. DBMS Supported data model.

2.1 Conceptual Model

1. Basic concepts in the information world

entity entity: Things that exist objectively and can be distinguished from each other are called entities. An entity can be a concrete person, thing, or thing, or it can be an abstract concept or connection.
Attributes attribute: a characteristic of an entity
 code key: The smallest set of attributes that uniquely identifies an entity is called a key, also known as a key or key
 area domain: The value range of an attribute is called the domain of the attribute
 entity type entity type: Entities with the same properties must have common characteristics and properties. The entity name and its attribute name set are used to abstract and describe the same entity, which is called entity type.
entity set entity set: A collection of entities of the same type is called an entity set
 connect relationship: One-to-one One-to-many Many-to-pair

2. ER diagram

ER model

entity-contact method Entity-Relationship approach´╝îProvides methods for entity types, properties, and associations

3. E-R model design principles

(1)Attributes should exist in and only exist in a certain place (entity or association). This principle ensures that a certain data in the database is only stored in a certain database table, avoiding data redundancy.
(2)An entity is a single entity and cannot exist as an attribute of another entity. This principle ensures that one database table cannot contain another database table.
(3)the same entity in the same E-R Appears only once

4. E-R model design steps

(1)Divide and identify entities
(2)Divide and identify connections
(3)determine properties, for simplicity E-R Model, things that can be used as attributes should be treated as attributes as much as possible
(4)draw E-R Model
(5)optimization E-R Models that remove redundant connections and attributes between data

2.2 Data Model Elements

data structure
data manipulation
data constraints

2.3 Basic data model

Hierarchical Data Model

A tree structure is used to represent various entities and the connections between entities.

mesh data model

The mesh model removes two of the limitations of the hierarchical model, allowing nodes to have multiple parents and multiple nodes without parents.

relational data model

Organize data into tabular form. The most widely used

object-oriented data model

The relational model is simple and flexible, but cannot express complex data structures in the real world.
The object-oriented model can completely describe the data structure of the real world, but it is more complex.

3. Database system structure

According to the different relationship between various types of people and the database, the views can be divided into three types: user and application programmer - external view, system analyst and DBA-logical view, DBA-internal view

3.1 The three-level schema structure of the database system

outside mode

The logical structure and feature description that can be seen and used by database users is the logical representation of the data associated with an application.
Different users have differences in application requirements, ways of viewing data, and requirements for data confidentiality, so their external schema descriptions are different.
That is, the same data in the schema can also have different structures, types, lengths, and security levels in the outer schema.
The same mode can also be used by multiple application systems of a user, but an application can only use one external mode

logical mode

A global logical structure constructed from a unified viewpoint by the database designer synthesizing the data of all users.
It is a description of the logical structure and characteristics of the entire data in the database.	is a public view of data for all users
 A database has only one schema, which is described in a schema description language.

Intra mode

Also known as storage mode
 A database has only one internal schema, which is a description of the physical structure and storage of data.

3.2 The secondary mapping function and data independence of the database

In order to realize the connection and transformation of the three levels of abstraction internally, the database management system provides two levels of mapping between these three levels of schemas.

Outer schema/schema mapping (guarantee logical independence)

Corresponding to the same pattern, there can be any number of outer patterns.
For each foreign schema, the database system has a foreign schema/Schema mapping, which defines the mapping relationship between the external schema and the schema. These mappings are usually kept in their respective outer schema descriptions.
When the schema changes, each external schema is checked by the database administrator/The schema mapping can be changed accordingly to keep the outer schema unchanged. The application is written according to the external schema of the data, and the application does not need to change.

Intra-mode/mode mapping (guaranteed physical independence)

The corresponding relationship between the global logical structure of the database and the storage structure is defined.
When the storage structure of the database changes, by DBA Inbound mode/Changing the schema mapping accordingly keeps the schema and therefore the application unchanged.

3.3C/S and B/S structure

C/S structure

Client/Server Client Server Architecture
 The basic principle is to decompose computer application tasks into multiple sub-tasks, which are completed by multiple computers.
The client performs data processing, data presentation, and user interface functions.
server-side completion DBMS Core functions

B/S structure

Browser/Server browser/server structure
Web The browser is the main application software of the client
 The biggest advantage is that it can be operated anywhere, and system maintenance is easy

Tags: Database SQL

Posted by rammac13 on Sat, 22 Oct 2022 11:11:31 +1030