Basic RDBMS Concepts

RDBMS: relational database management system based on SQL (Structured query statement)

The main relational database management systems are: Oracle, SQL Server, DB2, MySQL, etc.

SQL statements are divided into 4 categories:

DDL: Data Definition language

DML: Data Manipulation language

DQL: Data Query Language

DCL: Data Control Language

SQL Server is divided into 2 login methods (can be configured during installation or specified after installation): Accounts (more common in development), Windows integration

With a distributed connection to a remote SQL Server server, you need to open port 1433 on the server side, such as:

You can also specify an IP address individually

You can create a login user name in database security

Then specify the access rights for the login (this will create a user with the same name for the database)

Remove the logged-in user from the specified database before deleting the user, and then deleting the access and login names

At this point the user only has access to the Bookstoredb database and other databases are inaccessible.

Detaching a database

Attaching a database

Backing Up the database

Restoring a Database

Default System Database

Master: Save all database information on the SQL Server engine

Model: template for creating a new user database

TEMPDB: Storing temporary objects, such as global and local temporary tables

MSDB: Saving database backup, agent, and task information

Database design

Good design can: save storage space, ensure data integrity, easy to develop

Poor design leads to: Data redundancy, wasted space, update anomalies

Database design is divided into:

Requirements analysis, Profile design (er diagram), detailed design, code authoring, testing, installation Deployment

Database Normalization Design:

First paradigm 1NF: atomicity per column

Second paradigm 2NF: primary key correlation

Third paradigm 3NF: direct correlation

Basic RDBMS Concepts