Database and MySQL Database Management System? (Part 1)

Today I would like to introduce to you newcomers who have knowledge about databases and MySQL database management systems that we must be sure to apply when building websites.

Database

Database: A set of related data stored on the computer to meet the exploitation needs of a group of users for certain purposes.

Model ER

1. Entity

• Be an object of the real world;
• An entity is described by a set of attributes;
• It is a specific or abstract physical object.For example, an E-learning system with User entity, Subject, Course registration, …

2. Properties

• Characteristics that describe an entity, an entity can have many properties.
• Each particular entity will have values ​​for each of its properties.
• Property’s value domain (domain): the set of all valid values ​​that can be assigned to the property

3. Key Properties

• Entity type key: is an attribute whose value is different on any 2 entities of that entity type.
• Key to distinguish the entities in the entity type.

4. Relationship: An association between two or more entities

• Relationship type between entity types: set of all the same relationships on the entities of the entity type.
• Link level : The number of entity types that participate in the association
• Constraint on the association type
  Ratio constraint: Consider the binary relationship R (level 2) between 2 sets of entities A and B
  Constraint (min, max) : specifies each entity that is the least and most involved in the instance of R

Relational model

relationship

Information stored in the database is organized into two-dimensional tables called relationships
Relations include

1. Name
2. Set of columns: Fixed, Named, Has data type (Properties)
3. Set of lines: Change over time (Sets)

• A line ~ An entity, or related event
• One column (field) ~ An attribute
• Relationship ~ Practice, event practice
  

Properties

• The names of the columns of the relationship
• Describes the meaning for the values ​​in that column
• All data in the same column have the same data type, the values ​​are prime

Domain value (domain)

The symbol Dom (A) is the value domain of A; ie A values ​​can take

Tuple

The rows of a relationship (except the header line – the names of attributes) represent data specified by the attributes of a related entity or event in the relationship.

Constraint

Constraint: Rules and conditions that need to be satisfied in a relational database instance
1. Domain Constraints
The value of the set t in attribute A (t [A]) must belong to Value domain A.
2. Key Constraints
Suppose for R (A1, .. An). t1, t2 <R, exist attribute set SK such that t1 [SK] # t2 [SK] => SK is called super key. The key is the minimal super key

• 2.1. Entity Integrity Constraint (Primary Key)
  Select one of the candidate courses as the basis for identification of sets (the selected key has the least attributes)
  The selected key is called the primary key
  
• 2.2. Reference integrity constraint (Foreign key)
  A set in relation R, at property A, if we take a value from property B of relation S, we call R to refer to S. The referenced set must exist first.
  Consider two schemes R1 and R2. Let FK be the property set (non-null) of R1; PK is the primary key of R2. FK is the Foreign Key of R1 when:
  Attributes in FK must have the same range of values ​​as PK (R2).
  Value at FK of a set t1 <R1 (t1 [FK]) or equal to the value at the primary key of a set t2 <R2 (t2 [PK]) or t1 [FK] = Null
• An property can both participate in the primary key and in the foreign key
• The foreign key can refer to the primary key on the same relational schema
• There can be more than one foreign key that references the same primary key.

How to build a database for a web project

* B1: Determine the entity (relationship)

* B2: Determine the relationship (link)

* B3: Define attributes and attach attributes to entities and relationships

* B4: Determine the domain value for the property

* B5: Decide key properties

* B6: Defines the constraint (ratio; min-max; join constraint) for the relationship

• Each relationship nn identifies a table in the relational model
• Each 1-n relationship identifies 1 foreign key

Do it on Cacoo tool https://cacoo.com/

MySQL database management system

MySQL is a Relational Database Management System that uses Structured Query Language (SQL).
After building the database through the ER model, we switch to the relational schema and then build the tables and constraints on the database management systems for realization.

Install and configure MySQL

You can refer to the following two articles to know how to install
https://openplanning.net/10221/cai-dat-co-so-du-lieu-mysql-tren-windows
https://blog.hostvn.net/chia-se/huong-dan-cai-dat-mysql-tren-ubuntu-18-04.html

Create and modify table structure

To create the table, MySQL uses the CREATE TABLE statement. The statement has the following structure:

IF NOT EXISTS to avoid creating a table already exists in the database, table_name is the name of the table you want to create
Table_type : determines the type of the data table to be stored (note that this property is specific to MySQL). If not specified, MySQL will use the default table type.
MyISAM : MyISAM tables work very quickly, but do not support transactions. Commonly used in Web applications, was the default table type in versions of MySQL prior to 5.5
InnoDB : InnoDB tables support secure transactions, support foreign keys. InnoDB is the default storage type since MySQL 5.5.
Defines a set of columns : Columns are listed with properties such as data type, default value, if any, constraints on the column.
Constraint
PRIMARY KEY : This constraint defines a column or combination of columns that uniquely identifies each row in the table.
NOT NULL : This constraint requires that the value of the forbidden column be NULL
UNIQUE: Constraints require that the values ​​of the column be distinct. Note that with this constraint the value of the column can be NULL if the NOT NULL constraint is not applied on the column.
FOREIGN KEY
The FOREIGN KEY keyword is used to define a foreign key

ON DELETE RESTRICT : It means that it is not allowed to delete the row of data in the referenced table while there is still data referenced.
ON UPDATE CASCADE: means that when updating data in the referenced table, the data on the referenced table will be automatically updated.
When these options are not used, RESTRICT will be used by default for the DELETE and UPDATE events
The syntax of the ALTER TABLE command is as follows:

Above, I have presented basic knowledge of databases, how to build databases for products, how to create databases with MySQL database management system. In the next article I will present the query section in MySQL, hope you will read.
Thank you everyone for reading. Hopefully through the article, everyone can build a database effectively.!

Reference source

• Lecture on database subject – Faculty of Information Technology – University of Technology – Hanoi National University
• https://www.w3schools.com/sql/default.asp