Database and database Learning

Database and database Learning
Database System Structure

From the perspective of the database management system, the database system usually adopts a three-level structure, which is the internal system structure of the database system.

From the perspective of database end users (external architecture of the database system), the structure of the database system is divided:
Single User Structure
Distributed Structure
Customer/Server
Browser/Application Server/Database Server multi-layer structure

Concept of Database System Mode

Concepts of "type" and "value"
Type)
Description of the structure and attributes of a type of data
Value)
Is a specific value of Type
For example
Student record type:
(Student ID, name, gender, department, age, nationality)
One record value:
(900201, Li Ming, male, computer, 22, Jiangsu)
Schema)
Description of the logical structure and features of the database
Type description
It reflects the data structure and connection.
The mode is relatively stable.
Instance)
A specific value of the Mode
Reflects the status of a database at a certain time point
There can be many instances in the same mode
The instance changes with the data updates in the database.

Mode

Mode (also called logical mode)
Logical Structure and feature description of all data in the database
The public data view of all users integrates the needs of all users
A database has only one mode.
Mode Status: the middle layer of the database system mode structure
It has nothing to do with the physical storage details of data and the hardware environment.
Independent of specific applications, development tools, and advanced programming languages
Mode definition
Logical Structure of data (data item name, type, value range, etc)
Data Connection
Data-related security and integrity requirements

Mode instance

Teaching database mode (the second example above)
Instructor (Instructor ID, name, title)
Primary Key: Instructor ID
Course (course number, course name, instructor number, teaching material)
Primary Key: course No. Foreign key: Instructor No.
Student (student ID, name, gender, instructor ID)
Primary Key: Student ID foreign key: Instructor ID
Course Selection (student ID, course number, score)
Primary Key: (student ID, course number)
Foreign key 1: Student ID, foreign key 2: course number

[Example] create table Student (Sno CHAR (9) primary key, Sname CHAR (20) not null, Ssex CHAR (2), Sage NUMBER, sdept CHAR (20) tablespace users;

[Example] CREATE Course table create table Course (Cno CHAR (4) primary key, Cname CHAR (40) not null, Cpno CHAR (4), Ccredit NUMBER, foreign key Cpno REFERENCES Course (Cno) tablespace users;

[Example] CREATE Elective Course SC table create table SC (Sno CHAR (9) NOT NULL, Cno CHAR (4) NOT NULL, Grade, PRIMARY KEY (Sno, Cno ), foreign key (Sno) REFERENCES Student (Sno), foreign key (Cno) REFERENCES Course (Cno) tablespace users;

External Mode

External mode (also called submode or user mode)
Logical Structure and feature description of local data used by database users (including application programmers and end users)
The data view of a database user is a logical representation of data related to an application.
External mode status: Between mode and Application
Relationship between mode and external mode: one-to-many
The external mode is usually a subset of the mode.
A database can have multiple external modes. Reflects the application requirements of different users, the way data is viewed, and the requirements for data confidentiality
The structure, type, length, and confidentiality level of the same data in the external mode can be different.
Relationship between external mode and application: one-to-many
The same external mode can also be used by multiple application systems of a user.
However, an application can only use one external mode.
Usage of external mode
A powerful measure to ensure database security
Each user can only view and access the data in the corresponding external mode.

CREATE a VIEW for computer students CS_Student create view CS_Student as select * FROM Student WHERE Sdept = 'cs ';

Internal Mode

Internal Mode (also called storage mode)
Is the description of the physical structure and Storage Method of Data
Is the internal representation of data in the database.
Record storage method (sequential storage, stored in B-tree structure,
Storage by hash)
Indexing Organization
Whether data is compressed or not
Data encryption?
Data storage record Structure
A database has only one internal mode.

Mode/internal mode image

The mode/internal mode image defines the correspondence between the Global Logical Structure of data and the storage structure.
The mode/internal mode image in the database is unique.
This image definition is usually included in the schema description.
Ensure the physical independence of data
When the storage structure of the database changes (for example, another storage structure is selected), the database administrator modifies the mode/internal mode image to keep the mode unchanged.
Applications are not affected. It ensures the physical independence of data and programs, or physical independence of data for short.
Database Mode
That is, the global logical structure is the center and key of the database.
Independent from other database levels
When designing the database schema structure, you must first determine the logical schema of the database.
Internal database mode
Depends on its Global Logical Structure
Database-independent user view, that is, external mode
Independent from specific storage devices
Organize the data structures and their relationships defined in the Global Logical Structure according to certain physical storage policies to achieve better time and space efficiency.

External Database Mode
Specific applications
Defined on the logic mode
Independent from storage mode and storage devices
When the application requirements change greatly and the corresponding external mode cannot meet the view requirements, the external mode must be modified accordingly.
Specific applications
The data structure described in the external mode
Depends on a specific external mode
Independent from the database mode and Storage Structure
Different applications sometimes share the same external mode.
Database secondary image
Ensures the stability of the external database mode.
The application stability is ensured from the underlying layer. Unless the application requirements change, the application generally does not need to be modified.

The independence between the data and the program allows the definition and description of the data to be separated from the application.

Data access is managed by DBMS.
Users do not have to consider details such as access paths.
Simplified application programming
Greatly reduced application maintenance and Modification

Database Administrator (DBA)

Specific responsibilities:
1. determine the information content and structure in the database
2. Determine the database storage structure and Access Policy
3. Define data security requirements and integrity constraints
4. Monitor the use and operation of databases
Periodic database dumping
Data Files
Log Files
System fault recovery
Media fault recovery
Monitor audit files
5. database improvement and restructuring
Performance monitoring and Tuning
Regularly reorganizes databases to improve system performance
When demand increases or changes, the database must be re-constructed