SQL Server Database foundation--e-r diagram, three paradigms, database design steps

Objective of this book:

Understand the basic concepts and paradigms of a database

Familiar with SMSS, T-SQL implementation of the database, build tables, add constraints

Mastering T-SQL programming for powerful queries

Master Creating indexes, views, quick access to databases

Be familiar with creating stored procedures and implementing complex business rules

Understanding the principle of triggers , mastering advanced constraints

Understanding Sqlservler Security Management

Understanding Backup and recovery of databases

　　

Objectives of this chapter:

Understand the basic concepts of a database

steps to understand database design

Master Drawing Database e-r diagram

Understanding the three main paradigms of database

　　　　

What is a database system?

The database system (DBS) is a complete operational entity with a database as its core,

The composition of the database:

System hardware platform, system software platform, data, database management system, database administrator

The concept of database management system:

Database management System (DBMS) is the organization of database, it is a kind of system software, which is responsible for data organization, data manipulation, data maintenance, control and protection and data Service in database. Database management system is the core of database system.

The specific functions of the database management system are:

Data definition, data manipulation, database operation management, data organization, storage and management, data establishment and maintenance

What is a database administrator?

Database Administrator (DBA)

Database administrator tasks include: Install, configure, upgrade, and migrate databases, backup and recovery databases, database security management, storage and capacity planning, performance monitoring and tuning, troubleshooting

Data Model:

The data model should meet three requirements:

1, can simulate the real world more realistically

2, easy to understand

3, easy to implement on the computer

The data model is divided into two categories--conceptual model, logical model and physical model.

　　　Conceptual model:

Also known as the information model, it is based on the user's point of view of data and information modeling, for database design.

　　　　Logical models and Physical models:

The main types of logical models include mesh models, hierarchical models, relational models, object-oriented models, and so on. According to the viewpoint of computer system, it is used for DBMS implementation, which is a model of how to define database input and output, its main function is to provide data definition and format for information system, and is the core and foundation of database system.

A physical model is an abstraction of the lowest level of data, describing how and how data is represented inside the system, how it is stored on disk or tape, and how it is accessed.

Data Model Conversions:

　　Abstract process of Objective object

1. Abstract the objective object in the real world as a conceptual model

2. Transform the conceptual model into a data model supported by a DBMS.

　　　　

The composition of the relational database:

Table: two-dimensional tables store data

Records: Each row of data in a table

Fields: Each column of data in a table

Index: A sorted list of single or multiple columns of data in a table, with each index pointing to a row of its related data table.

Relationship between tables: a database often contains multiple tables, and different categories of data are stored in different tables, and the relationships between the tables are linked together.

The consequences of database design

A good database design:

Save data storage space, ensure data integrity, and facilitate the development of database application system

　Poor database design:

Data redundancy, wasted storage space, wasted memory space, data update and insert exceptions

Steps to design a database:

Collect information--identify objects (entities)--identify the properties of each entity---Identify the relationship between objects--draw e-r--and convert the E-r diagram to a table

Gather information: Communicate and talk with the relevant personnel of the system to fully understand the tasks that the database needs to accomplish

Identity object: Identifies the key object or entity to be managed by the database;

E-r (entity-relationship) diagram: Mainly used to describe the data relationship of the system. Software developers typically use entity correlation diagrams to build conceptual data models

　　　　

　　　　

　　　　　　

E-r Chart conversion to table:

Convert each entity to a corresponding table, converting each property to a column corresponding to each table

Identify primary key columns for each table

Establish a primary foreign key between tables, reflecting the mapping between entities

　

Data normalization:

Just having a good RDBMS is not enough to avoid data redundancy, you must create a good table structure in your database design.

　　Three main paradigms:

First paradigm: The goal is to ensure the atomicity of each column

Second normal: Columns other than the primary foreign key are dependent on the primary key; Each table is required to describe only one thing

Third normal: Columns other than the primary foreign key do not pass dependent on the primary key column

　　　　

　　　　

　　　　

SQL Server Database foundation--e-r diagram, three paradigms, database design steps