# data[].attributes.body
We are checking  every things such as Mysql, world, excel for submission on tomorrow.

# data[].attributes.body
This is our queries1

# data[].attributes.headline
Today (4/8/20) i insert Class  color store  to SQL  use for business.

# data[].attributes.subject
Today (4/8/20) i insert Class  color store  to SQL  use for business.

# data[].attributes.body
today we try to solve store323 but it still error in SQL.

# data[].attributes.body
1) departments can be sports clothes-> color can be red->style can be->slimfit->class can be kids
2) here is the link for sql command
https://www.w3schools.com/sql/func_mysql_dayofweek.asp

# data[].attributes.body
Excuse me,i have questions.
1.Can you give the example about class, style, colour and department. I wanna know more about it
Example. Colour is red, black, green
Department is import, export.
I don't know if I understand it correctly.

2. Which one should we use about day.
Theory in SQL  monday =0.
In excel monday =1

# data[].attributes.body
Salisa, your diagram:
1) If your table name is SKU Vendor the primary key must be combination of those two. But you indicate Vendor name as primary key. The problem with that is if you have another sku from that Vendor you will have to repeat Vendor name in another row. So my advice is either change primary key, or break this table into two: Vendor table and another table is SKU table that has Vendor name as Foreign key
2) If you have a store table it must have a store number and thats it. You cannot include one SKU. Because every store has many SKUs. SO, same advice break the table into Store table
3) Departments , same as other two I mentioned
4) DB table is correct!

Krittaphont, what errors are you encountering now?

# data[].attributes.body
Finally i try to slove the problems agian, those have no error.

# data[].attributes.headline
Today we try to in phpmyadmin program but it error so we will try to do it again .

# data[].attributes.subject
Today we try to in phpmyadmin program but it error so we will try to do it again .

# data[].attributes.body
Niceeee!

# data[].attributes.headline
To day we are use video conference for discussion about this topic. 

# data[].attributes.subject
To day we are use video conference for discussion about this topic. 

# data[].attributes.headline
Find data 3)to perform data import from excel into relevant tables without any SQL errors and Server errors.

# data[].attributes.subject
Find data 3)to perform data import from excel into relevant tables without any SQL errors and Server errors.

# data[].attributes.attachment_link.readable_text
As a programmer, one of the most common problems I face during data migration is the diverse forms that data comes in, rendering import of that data into SQL Server quite a cumbersome task! In fact, last week itself I was working on data import from as much as five different sources, all in their own disparate formats! Importing data through code was killing my project!!! That's when I came up with the idea of OPENROWSET method through stored procedure in SQL Server. Now I can import tons of data using OPENROWSET method within a fraction of seconds!

Let me show you how to easily import data from Excel files, .xls or .xlsx into SQL, using a stored procedure.

T-1: Prerequisites for Data Import

For this data import, we need to:

Create an Excel file to import sample data

Configure Windows Server, installing the necessary components

Configure the necessary permissions to the SQL instance from where we need to obtain data files

Stored procedure to read excel sheet and import data into the table with validations

Once we have all this, we need to prepare the environment for data import.
1. Creating an Excel File

The first step involves the creation of an Excel file sample with a few rows of data for demonstration purposes. We need to add a header row to explicitly define the data: ID, Item Name and Date Created. It is important that you note that the data sequence is only for facilitating the visualization of the content that is being manipulated.

2. Installing the necessary providers

As the next step, we now need to get the data through a query inside the SQL Server using an OLEDB Data Provider. I used Microsoft.ACE.OLEDB.12.0 Data Provider which is available free of cost, and provided all ODBC and OLEDB drivers.

Note that there are two versions of this package: AccessDatabaseEngine.exe for x86 platform and AccessDatabaseEngine_x64.exe for x64 platform, so choose a version suitable to your platform.

3. Enabling SQL Server Instance to Read File

The execution of stored procedure using OPENROWSET is only possible when the SQL Server instance has the AdHoc Distributed Queries configuration enabled. By default every SQL Server instance keeps this configuration disabled.

To enable Ad Hoc Distributed Queries configuration I used the following query.

Ad hoc distributed queries configuration will be effective only after the execution of the reconfigure command, as shown above.

In order to get permission required to use the OLEDB Drivers, link stored procedure with Microsoft.ACE.OLEDB.12.0 provider in SQL Server using AllowInProcess & Dynamic parameters. I used the following query.

4. Using OPENROWSET method in stored procedure

Now we will query to read excel sheet data using OPENROWSET method. We need to pass some essential parameter to this method:

Data Provider: Microsoft.ACE.OLEDB.12.0

Options: File Version; File path; Header (HDR); Import Mode (IMEX)

SQL statement with or without clauses to filter data

To perform other tasks for data manipulation, you should ideally always load the data into the database. You can insert data on existing table or else you can create new table.
I used the following query to use OPENROWSET method

It's also important to check if the SQL Server Service user has access to the Windows directory where the Excel files are stored.

Getting in the Groove: Importing excel template into the physical table through Stored Procedure

Now comes the real deal, importing excel template into the physical table with the help of a stored procedure. I have listed below the simplified, 8-step process, along with the query.

1. As the first step, you need to pass three parameters to the stored procedure:

Destination Table Name

File Path

Sheet Name

2. Once the above parameters are passed, you need to provide the following validation for table and excel Sheet:

Destination Table Name cannot be null or Empty, you have to provide the table name

File Path cannot be null or Empty, you have to provide the File Path

3. Once you have provided the validations, you then need to read the count of records from Excel sheet and store it into a variable. If the count is zero, you need to raise an error message in excel sheet that says Data Not Available; if the data is available, you need to read the data from the excel sheet and insert it into a temporary table, as shown below:

4. If the physical table exists, you need to drop the existing table and insert the records of the temporary table (excel sheet) into the physical table. Alternatively, if the physical table does not exist, you need to directly create and insert the data from the excel sheet into the physical table, as shown here:

5. Once this is done, you need to get the count of successfully inserted records into the physical table. In order to show the message, use the code given below.

6. After getting the count into the physical table, you need to drop the temporary table. You can use the following sample code for the same.

7. In this example, I have used Transaction within my stored procedure. The data can be rolled back in case of any error. Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.

8. In order to conclude this process, you need to handle all errors within the Catch block and store the errors into the error table, as shown below

Time is Money!!!

There are alternate methods to import data but if you are in a Catch-22 situation like me, importing data through SQL Server is your best bet. Remember, timely access to data is as important, if not more, as access to data itself.

Reference: Importing an Excel Spreadsheet into a SQL Server Database
Written by Piyush Ostwal, .Net Champion at Eternus Solutions

# data[].attributes.attachment_link.title
Data Import Simplified: Excel to SQL Using Stored Procedure - Eternus Solutions

# data[].attributes.attachment_link.description
As a programmer, one of the most common problems I face during data migration is the diverse forms that data comes in, rendering import of that data into SQL Server quite a cumbersome task! In fact, last week itself I was working on data import from as much as five different sources, all in their...

# data[].attributes.headline
I find the information about phpmyadmin.

# data[].attributes.subject
I find the information about phpmyadmin.

# data[].attributes.body
and I give file explanation and have example of SQL CREATE TABLE to check values with CHECK CONSTRAINT.

# data[].attributes.attachment_link.readable_text
CONSTRAINT

MySQL CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns. The purpose of inducing constraints is to enforce the integrity of a database.

MySQL CONSTRAINTS are used to limit the type of data that can be inserted into a table.

MySQL CONSTRAINTS can be classified into two types - column level and table level.

The column level constraints can apply only to one column where as table level constraints are applied to the entire table.

MySQL CONSTRAINT is declared at the time of creating a table.
MySQL CONSTRAINTs are :

NOT NULL

In MySQL NOT NULL constraint allows to specify that a column can not contain any NULL value. MySQL NOT NULL can be used to CREATE and ALTER a table.

UNIQUE

The UNIQUE constraint in MySQL does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. More than one UNIQUE column can be used in a table.

PRIMARY KEY

A PRIMARY KEY constraint for a table enforces the table to accept unique data for a specific column and this constraint creates a unique index for accessing the table faster.

FOREIGN KEY

A FOREIGN KEY in MySQL creates a link between two tables by one specific column of both tables. The specified column in one table must be a PRIMARY KEY and referred by the column of another table known as FOREIGN KEY.

CHECK

A CHECK constraint controls the values in the associated column. The CHECK constraint determines whether the value is valid or not from a logical expression.

DEFAULT

In a MySQL table, each column must contain a value ( including a NULL). While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.

Syntax :
CREATE  TABLE [table name]
([column name] [data type]([size]) [column constraint]....
[table constraint] ([[column name]......])......);

Explanation
The above MySQL code shows how to create a table with some constraints. Constraints are applied to columns as well as tables.

You can replace table name, column name, data type and size with your own.
MySQL CREATE TABLE with NULL CONSTRAINT
Using the default value as NOT NULL, while creating a MySQL table, it can be enforced that a column in a table is not allowed to store NULL values.

If you want to create a table 'newauthor' where no columns are allowed to store NULL VALUES the following statement can be used.
Example
If you want to create a table 'newauthor' where no columns are allowed to store NULL VALUES the following statement can be used.

CREATE TABLE IF NOT EXISTS newauthor (aut_id varchar(8) NOT NULL, aut_name varchar(50) NOT NULL, country varchar(25) NOT NULL, home_city varchar(25) NOT NULL );

Here in the above statement the constraint 'NOT NULL' have been used to exclude the NULL VALUE.
The following picture shows that the columns will not accept the NULL values.
MySQL CREATE TABLE to check values with CHECK CONSTRAINT
Adding a CHECK CONSTRAINT on a column of a table, you can limit the range of values allowed to be stored in that column.
Example
If you want to create a table 'newbook_mast' with a PRIMARY KEY on 'book _id' column, a unique constraint on 'isbn_no' column and a set the no_page in such, that it would hold values more than zero only, the following statement can be used.

CREATE TABLE IF NOT EXISTS newbook_mast (book_id varchar(15) NOT NULL UNIQUE, book_name varchar(50) , isbn_no varchar(15) NOT NULL UNIQUE , cate_id varchar(8) , aut_id varchar(8) , pub_id varchar(8) , dt_of_pub date , pub_lang varchar(15) , no_page decimal(5,0) CHECK(no_page>0) , book_price decimal(8,2) , PRIMARY KEY (book_id) );

Here in the above MySQL statement will create a table 'newbook_mast' with a PRIMARY KEY on 'book _id' column, unique constraint on 'isbn_no' column and adding CHECK(no_page>0) will set the no_page in such, that it would hold values more than zero only.
MySQL CREATE TABLE with CHECK CONSTRAINT using IN operator
MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with IN operator.
Example
If you want to create a table 'newauthor' with a PRIMARY KEY on a combination of two columns (aut_id,home_city) and checking a limit value for the column country are 'USA','UK' and 'India', the following statement can be used.

CREATE TABLE IF NOT EXISTS newauthor(aut_id varchar(8) NOT NULL , aut_name varchar(50) NOT NULL, country varchar(25) NOT NULL CHECK (country IN ('USA','UK','India')), home_city varchar(25) NOT NULL, PRIMARY KEY (aut_id,home_city));

Here in the above MySQL statement will create a table 'newauthor' with a PRIMARY KEY on a combination of two columns (aut_id,home_city) and the value for the column country has been limited by using IN operator.
MySQL CREATE TABLE with CHECK CONSTRAINT and LIKE operator
MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with LIKE operator.
Example
The MySQL statement stated below will create a table 'newbook_mast' with a PRIMARY KEY on 'book_id' column and a CHECK constraint to limit value to be stored for the column dt_of_pub along with LIKE operator and another CHECK constraint to column no_page (without using LIKE operator).

CHECK (dt_of_pub LIKE '--/--/----') checks whether the format of the date to be stored is the column dt_of_pub is like '--/--/----'.

Here is the statement.

CREATE TABLE IF NOT EXISTS newbook_mast ( book_id	varchar(15) NOT NULL UNIQUE, book_name varchar(50) , isbn_no varchar(15) NOT NULL UNIQUE , cate_id varchar(8) , aut_id varchar(8) , pub_id varchar(8) , dt_of_pub date CHECK (dt_of_pub LIKE '--/--/----'), pub_lang varchar(15) , no_page decimal(5,0) CHECK(no_page>0) , book_price decimal(8,2) , PRIMARY KEY (book_id) );
MySQL CREATE TABLE with AND and OR operator and CHECK CONSTRAINT
MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with AND and OR operator.
Example
The MySQL statement stated below will create a table 'newpublisher' with a PRIMARY KEY on 'pub_id' column and CHECK constraint along with AND and OR operator for country and pub_city columns.

CHECK ((country='India' AND pub_city='Mumbai') OR (country='India' AND pub_city='New Delhi')) checks whether (i)country is INDIA and pub_city is Mumbai OR (ii) country is INDIA and pub_city is New Delhi.

MySQL Manual says "The CHECK clause is parsed but ignored by all storage engines."

Here is the statement.

CREATE TABLE IF NOT EXISTS newpublisher (pub_id varchar(8) , pub_name varchar(50), pub_city varchar(25) , country varchar(25) , country_office varchar(25) , no_of_branch int(3), estd date CHECK ((country='India' AND pub_city='Mumbai') OR (country='India' AND pub_city='New Delhi')) , PRIMARY KEY (pub_id) );
MySQL UNIQUE CONSTRAINT
The UNIQUE constraint creates an index such that, all values in the index column must be unique. An error occurs when any body tries to add a new row with a key value that already exists in that row.
Example
The MySQL statement stated below will create a table 'newauthor' with a column 'aut_id' which will store unique values only since UNIQUE (aut_id) is used.

CREATE TABLE IF NOT EXISTS newauthor(aut_id varchar(8) NOT NULL , aut_name varchar(50) NOT NULL, country varchar(25) NOT NULL, home_city varchar(25) NOT NULL, UNIQUE (aut_id));
The picture below shows the structure of the table.
Example of MySQL UNIQUE CONSTRAINT check unique value
The MySQL statement stated below will create a table 'newauthor' with a column 'aut_id' which is meant to store unique values only. Notice that UNIQUE is used within the column definition

CREATE TABLE IF NOT EXISTS newauthor(aut_id varchar(8) NOT NULL UNIQUE , aut_name varchar(50) NOT NULL, country varchar(25) NOT NULL, home_city varchar(25) NOT NULL);
The following picture shows the Structure of the Table
MySQL CREATE TABLE with DEFAULT CONSTRAINT
While creating a table, MySQL allows you assign DEFAULT CONSTRAINTS to columns. DEFAULT is used to set a default value for a column and is applied using DEFAULT default_value; where default_value is the default value set to the column.
Example
The MySQL statement stated below will create a table 'newpublisher' with a PRIMARY KEY on 'pub_id' column, a CHECK constraint with logical operators for country and pub-city columns and a default value for pub_id, pub_name, pub_city and country columns.

The MySQL statement also sets the default value white space for pub_id, pub_name, pub_city columns and 'India' as a default value for a country column.

Here is the statement below.

CREATE TABLE IF NOT EXISTS newpublisher (pub_id varchar(8) NOT NULL UNIQUE DEFAULT '' , pub_name varchar(50) NOT NULL DEFAULT '' , pub_city varchar(25) NOT NULL DEFAULT '' , country varchar(25) NOT NULL DEFAULT 'India', country_office varchar(25) , no_of_branch int(3), estd date CHECK ((country='India' AND pub_city='Mumbai') OR (country='India' AND pub_city='New Delhi')) , PRIMARY KEY (pub_id));
MySQL CREATE TABLE with AUTO INCREMENT
MySQL allows you to set AUTO_INCREMENT to a column. Doing so will increase the value of that column by 1 automatically, each time a new record is added.
Example
The MySQL statement stated below will create a table 'newauthor' with a PRIMARY KEY on 'id' column and the 'id' column is an auto incremented field.

CREATE TABLE IF NOT EXISTS newauthor (id int NOT NULL AUTO_INCREMENT, aut_id varchar(8), aut_name varchar(50), country varchar(25), home_city varchar(25) NOT NULL, PRIMARY KEY (id));
MySQL PRIMARY KEY CONSTRAINT
Usually, a table has a column or combination of columns that contain values used to uniquely identify each row in the table.This column or combination of columns is called PRIMARY KEY and can be created by defining a PRIMARY KEY CONSTRAINT while creating a table. A table can have only one PRIMARY KEY. A PRIMARY KEY column cannot contain NULL values.
Example
The MySQL statement stated below will create a table 'newauthor' in which PRIMARY KEY set to the column aut_id.

CREATE TABLE IF NOT EXISTS newauthor(aut_id varchar(8) NOT NULL , aut_name varchar(50) NOT NULL, country varchar(25) NOT NULL, home_city varchar(25) NOT NULL, PRIMARY KEY (aut_id));
MySQL CREATE TABLE PRIMARY KEY CONSTRAINT on single column
In this topic, we have discussed how to set a PRIMARY KEY CONSTRAINT on a column of a table.
Example
The MySQL statement stated below will create a table 'newauthor' in which PRIMARY KEY set to the aut_id column. Notice that here PRIMARY KEY keyword is used within the column definition.

CREATE TABLE IF NOT EXISTS newauthor(aut_id varchar(8) NOT NULL PRIMARY KEY, aut_name varchar(50) NOT NULL, country varchar(25) NOT NULL, home_city varchar(25) NOT NULL);
MySQL CREATE TABLE PRIMARY KEY UNIQUE CONSTRAINT
In this topic, we have discussed how to set PRIMARY KEY as well as UNIQUE constraints on columns of a table while creating a table with CREATE TABLE command.
Example
The MySQL statement stated below will create a table 'newauthor' in which PRIMARY KEY is set to the aut_id column and UNIQUE is set to the home_city column.

CREATE TABLE IF NOT EXISTS newauthor(aut_id varchar(8) NOT NULL PRIMARY KEY, aut_name varchar(50) NOT NULL, country varchar(25) NOT NULL, home_city varchar(25) NOT NULL UNIQUE);
MySQL CREATE TABLE PRIMARY KEY on multiple columns
MySQL allows you to set PRIMARY KEY on multiple columns of a table. Doing this allows you to work on multiple columns as a single entity set as PRIMARY KEY for a table.
Example
The MySQL statement stated below will create a table 'newauthor' in which PRIMARY KEY is set with the combination of aut_id and home_city columns.

CREATE TABLE IF NOT EXISTS newauthor(aut_id varchar(8) NOT NULL , aut_name varchar(50) NOT NULL, country varchar(25) NOT NULL, home_city varchar(25) NOT NULL, PRIMARY KEY (aut_id, home_city));
MySQL creating table with FOREIGN KEY CONSTRAINT
While creating (or modifying) a MySQL table, you can set a FOREIGN KEY CONSTRAINT to a column of the table. A foreign key is a column or combination of columns which can be used to set a link between the data in two tables. PRIMARY KEY of a table is linked to the FOREIGN KEY of another table to enhance data integrity.
Syntax :
FOREIGN KEY [column list] REFERENCES [primary key table] ([column list]);
Arguments

column list

A list of the columns on which FOREIGN KEY is to be set.

primary key table

Table name which contains the PRIMARY KEY.

column list

A list of the columns on which PRIMARY KEY is set in the primary key table.

Example If you want to do the following tasks:
A new table 'newbook_mast' will be created.

The PRIMARY KEY for that table 'newbook_mast' is 'book_id'.

The FOREIGN KEY for the table 'newbook_mast' is 'aut_id'.

The 'aut_id' is the PRIMARY KEY for the table 'newauthor'.

The FOREIGN KEY 'aut_id' for the table 'newbook_mast' points to the PRIMARY KEY 'aut_id' of the table 'newauthor'.

That means the 'aut_id's which are present in the 'newauthor' table, only those authors will come to the 'newbook_mast' table.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newbook_mast (book_id varchar(15) NOT NULL PRIMARY KEY, book_name varchar(50) , isbn_no varchar(15) NOT NULL , cate_id varchar(8) , aut_id varchar(8) , pub_id varchar(8) , dt_of_pub date , pub_lang varchar(15) , no_page decimal(5,0) , book_price decimal(8,2) , FOREIGN KEY (aut_id) REFERENCES newauthor(aut_id));
MySQL CREATE TABLE with FOREIGN KEY CONSTRAINT on multiple columns
MySQL allows assigning FOREIGN KEY CONSTRAINTS on multiple columns of a table. Doing this, more than one columns of a table is set with a FOREIGN KEY CONSTRAINT referenced PRIMARY KEYs belonging to different tables.
Example If you want to do the following tasks:
A new table 'newpurchase' will be created.
The PRIMARY KEY for that table 'newpurchase' is 'invoice_no'.
The one FOREIGN KEY for the table 'newpurchase' is a combination of 'ord_no' and 'book_id'.
The another FOREIGN KEY for the table 'newpurchase' is 'cate_id'.
The 'ord_no' and 'book_id' combination is the PRIMARY KEY for the table 'neworder'.
The 'cate_id' is the PRIMARY KEY for the table 'category'.
The FOREIGN KEY 'ord_no' and 'book_id' combination for the table 'newpurchase', which points to the PRIMARY KEY 'ord_no' and 'book_id' combination of the table 'neworder'.
That means the distinct ('ord_no' and 'book_id') combination which are present in the in the 'neworder' table only those unique 'order number' and 'book id' combination will come in the 'newpurchase' table.

The another FOREIGN KEY 'cate_id' for the table 'newpurchase', which points to the PRIMARY KEY 'cate_id' of the table 'category'. That means the 'cate_id' which are present in the 'category' table only those 'category' will come in the 'newpurchase' table.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newpurchase (invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY, invoice_dt date , ord_no varchar(25) , ord_date date , receive_dt date , book_id varchar(8) , book_name varchar(50) , pub_lang varchar(8) , cate_id varchar(8) , receive_qty int(5) , purch_price decimal(12,2) , total_cost decimal(12,2) , INDEX (ord_no,book_id), FOREIGN KEY(ord_no,book_id) REFERENCES neworder(ord_no,book_id), INDEX (cate_id), FOREIGN KEY(cate_id) REFERENCES category(cate_id));
MySQL CREATE TABLE with FOREIGN KEY CONSTRAINT on multiple tables
MySQL allows assigning FOREIGN KEY CONSTRAINTS on multiple tables. Doing this, more than one columns of a table is set with a FOREIGN KEY CONSTRAINT referenced to PRIMARY KEYs belonging to different tables.
Example If you want to do the following tasks :
Aa new table 'newbook_mast' will be created.
The PRIMARY KEY for that table 'newbook_mast' is 'book_id'.
The one FOREIGN KEY for the table 'newbook_mast' is 'aut_id'.
The another FOREIGN KEY for the table 'newbook_mast' is 'pub_id'.
The 'aut_id' is the PRIMARY KEY for the table 'newauthor'.
The 'pub_id' is the PRIMARY KEY for the table 'newpublisher'.
The FOREIGN KEY 'aut_id' for the table 'newbook_mast', which points to the PRIMARY KEY 'aut_id' of the table 'newauthor'.
That means the 'aut_id' which are present in the in the 'nuwauthor' table only those authors will come to the 'newbook_mast' table.
The another FOREIGN KEY 'pub_id' for the table 'newbook_mast' , which points to the PRIMARY KEY 'pub_id' of the table 'newpublisher'.
That means the 'pub_id' which are present in the in the 'newpublisher' table only those publishers will come to the 'newbook_mast' table.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newbook_mast (book_id varchar(15) NOT NULL PRIMARY KEY, book_name varchar(50) , isbn_no varchar(15) NOT NULL , cate_id varchar(8), aut_id varchar(8) , pub_id varchar(8) , dt_of_pub date , pub_lang varchar(15) , no_page decimal(5,0) , book_price decimal(8,2) , INDEX (aut_id), FOREIGN KEY(aut_id) REFERENCES newauthor(aut_id), INDEX(pub_id), FOREIGN KEY(pub_id) REFERENCES newpublisher(pub_id) );
MySQL CREATE TABLE with CASCADE and RESTRICT
MySQL allows creating a table with CASCADE and RESTRICT options.

CASCADE option deletes or updates the row from the parent table (containing PRIMARY KEYs), and automatically delete or update the matching rows in the child table (containing FOREIGN KEYs).

RESTRICT option bars the removal (i.e. using delete) or modification (i..e using an update) of rows from the parent table.
Example If you want to do the following tasks:
A new table 'newpurchase' will be created.
The PRIMARY KEY for that table 'newpurchase' is 'invoice_no'.
The one FOREIGN KEY for the table 'newpurchase' is a combination of 'ord_no' and 'book_id'.
The another FOREIGN KEY for the table 'newpurchase' is 'cate_id'.
The 'ord_no' and 'book_id' combination is the PRIMARY KEY for the table 'neworder'.
The 'cate_id' is the PRIMARY KEY for the table 'category'.
The FOREIGN KEY 'ord_no' and 'book_id' combination for the table 'newpurchase', which points to the PRIMARY KEY 'ord_no' and 'book_id' combination of the table 'neworder'. That means the distinct ('ord_no' and 'book_id') combination which are present in the 'neworder' table only those unique 'order number' and 'book id' combination will come in the 'newpurchase' table.
The another FOREIGN KEY 'cate_id' for the table 'newpurchase' , which points to the PRIMARY KEY 'cate_id' of the table 'category'. That means the 'cate_id' which are present in the 'category' table only those 'category' will come in the 'newpurchase' table.
The ON UPDATE CASCADE ensures that the records inside the child table 'newpurchase' always points to the PRIMARY KEY inside the parent table 'neworder'.
If any record gets deleted/updated from the 'neworder' table MySQL handles the deletion/updating of the records from 'newpurchase' table.
ON DELETE RESTRICT prevents a record in a parent table 'neworder' being deleted or altered when it is still referenced from a child table 'newpurchase'.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newpurchase (invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY, invoice_dt date , ord_no varchar(25) , ord_date date , receive_dt date , book_id varchar(8) , book_name varchar(50) , pub_lang varchar(8) , cate_id varchar(8) , receive_qty int(5) , purch_price decimal(12,2) , total_cost decimal(12,2) , INDEX (ord_no,book_id), FOREIGN KEY(ord_no,book_id) REFERENCES neworder(ord_no,book_id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (cate_id), FOREIGN KEY(cate_id) REFERENCES category(cate_id))
MySQL CREATE TABLE with SET NULL
MySQL allows you to create a table with SET NULL option. Doing so will delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL.

You can use SET NULL for DELETE as well as UPDATE.

If SET NULL is used, you should not set NOT NULL options to the columns of the child table (containing FOREIGN KEYS).
Example If you want to do the following tasks:
Aa new table 'newpurchase' will be created.
The PRIMARY KEY for that table 'newpurchase' is 'invoice_no'.
The one FOREIGN KEY for the table 'newpurchase' is a combination of 'ord_no' and 'book_id'.
The another FOREIGN KEY for the table 'newpurchase' is 'cate_id'.
The 'ord_no' and 'book_id' combination is the PRIMARY KEY for the table 'neworder'.
The 'cate_id' is the PRIMARY KEY for the table 'category'.
The FOREIGN KEY 'ord_no' and 'book_id' combination for the table 'newpurchase', which points to the PRIMARY KEY 'ord_no' and 'book_id' combination of the table 'neworder'. That means the distinct ('ord_no' and 'book_id') combination which are present in the 'neworder' table only those unique 'order number' and 'book id' combination will come in the 'newpurchase' table.

The another FOREIGN KEY 'cate_id' for the table 'newpurchase' , which points to the PRIMARY KEY 'cate_id' of the table 'category'. That means the 'cate_id' which are present in the 'category' table only those 'category' will come in the 'newpurchase' table.
ON UPDATE SET NULL recurses to update the 'newpurchase' table it has already updated during the cascade, it acts like RESTRICT. It prevents infinite loops resulting from cascaded updates.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newpurchase (invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY, invoice_dt date , ord_no varchar(25) , ord_date date , receive_dt date , book_id varchar(8) , book_name varchar(50) , pub_lang varchar(8) , cate_id varchar(8) , receive_qty int(5) , purch_price decimal(12,2) , total_cost decimal(12,2) , INDEX (ord_no,book_id), FOREIGN KEY(ord_no,book_id) REFERENCES neworder (ord_no,book_id) ON UPDATE CASCADE ON DELETE SET NULL, INDEX (cate_id), FOREIGN KEY(cate_id) REFERENCES category(cate_id));
MySQL CREATE TABLE with NO ACTION
NO ACTION option in MySQL is equivalent to RESTRICT.
Example If you want to do the following tasks:
Aa new table 'newpurchase' will be created.
The PRIMARY KEY for that table 'newpurchase' is 'invoice_no'.
The one FOREIGN KEY for the table 'newpurchase' is a combination of 'ord_no' and 'book_id'.
The another FOREIGN KEY for the table 'newpurchase' is 'cate_id'.
The 'ord_no' and 'book_id' combination is the PRIMARY KEY for the table 'neworder'.
The 'cate_id' is the PRIMARY KEY for the table 'category'.
The FOREIGN KEY 'ord_no' and 'book_id' combination for the table 'newpurchase', which points to the PRIMARY KEY 'ord_no' and 'book_id' combination of the table 'neworder'. That means the distinct ('ord_no' and 'book_id') combination which are present in the 'neworder' table only those unique 'order number' and 'book id' combination will come in the 'newpurchase' table.
The another FOREIGN KEY 'cate_id' for the table 'newpurchase' , which points to the PRIMARY KEY 'cate_id' of the table 'category'. That means the 'cate_id' which are present in the 'category' table only those 'category' will come in the 'newpurchase' table.
The ON DELETE NO ACTION are preventing a record in a parent 'neworder' being deleted or altered when it is still referenced from a child table 'newpurchase' .

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newpurchase (invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY, invoice_dt date , ord_no varchar(25) , ord_date date , receive_dt date , book_id varchar(8) , book_name varchar(50) , pub_lang varchar(8) , cate_id varchar(8) , receive_qty int(5) , purch_price decimal(12,2) , total_cost decimal(12,2) , INDEX (ord_no,book_id), FOREIGN KEY(ord_no,book_id) REFERENCES neworder(ord_no,book_id) ON UPDATE CASCADE ON DELETE NO ACTION, INDEX (cate_id), FOREIGN KEY(cate_id) REFERENCES category(cate_id));

Previous: Loading data into a table and usage of line terminator
Next: MySQL CREATE INDEX

# data[].attributes.attachment_link.description
MySQL CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns. The purpose of inducing constraints is to enforce the integrity of a database. MySQL CONSTRAINTS are used to limit the type of data that can be inserted into a table.

# data[].attributes.headline
This is the database integrity and how to maintain it

# data[].attributes.subject
This is the database integrity and how to maintain it

# data[].attributes.attachment_link.readable_text
If your company's data is altered or deleted, and you have no way of knowing how, when and by whom, it can have a major impact on data-driven business decisions. This is why data integrity is essential. To understand the importance of data integrity to a company's bottom line, let us examine what it is, why it's significant, and how to preserve it.

What is Data Integrity?

Data integrity refers to the reliability and trustworthiness of data throughout its lifecycle. It can describe the state of your data-e.g., valid or invalid-or the process of ensuring and preserving the validity and accuracy of data. Error checking and validation, for example, are common methods for ensuring data integrity as part of a process.

What is the Difference Between Data Integrity and Data Security?

Data integrity is not to be confused with data security. Data security refers to the protection of data, while data integrity refers to the trustworthiness of data.

Data security focuses on how to minimize the risk of leaking intellectual property, business documents, healthcare data, emails, trade secrets, and more. Some data security tactics include permissions management, data classification, identity and access management, threat detection, and security analytics.

Why is it Important to Maintain Data Integrity?

Imagine making an extremely important business decision hinging on data that is entirely, or even partially, inaccurate. Organizations routinely make data-driven business decisions, and data without integrity, those decisions can have a dramatic effect on the company's bottom line goals.

A new report from KPMG International reveals that a large majority of senior executives don't have a high level of trust in the way their organization uses data, analytics, or AI.

Only 35% say they have a high level of trust in the way their organization uses data and analytics. 92% are concerned about the negative impact of data and analytics on an organization's reputation. What's more, 62% of senior executives said technology functions, not the C-level and functional areas, bear responsibility when a machine or an algorithm goes wrong.

Organizations need to go through the motions of preserving data integrity in order for C-level executives to make proper business decisions.

Data Integrity Threats

Data integrity can be compromised through human error or, worse yet, through malicious acts. Data that's accidentally altered during the transfer from one device to another, for example, can be compromised, or even destroyed by hackers.
Common threats that can alter the state of data integrity include:

Human error

Unintended transfer errors

Misconfigurations and security errors

Malware, insider threats, and cyberattacks

Compromised hardware

So how do you know when your data has integrity? You have to look at the following features:

Retrievability and accessibility - It's important to have accurate data in the proper locations at the right time when anyone is working on projections, a deal, or presentation. Without proper and easy access and retrieval, it can be detrimental to the business, yielding the way for your competition to win.

Traceability -Today, you can trace every touchpoint you make with a prospect or customer. How? With a data point. The data can inform decision makers, highlight red flags, deficiencies, or limitations. Make sure these touchpoints are accurate.

Reliability - Having reliable, consistent business metrics against company goals and the competition is what will take an organization to the top.

How to Preserve Data Integrity [Checklist]

The data integrity threats listed above also highlight an aspect of data security that can help preserve data integrity. Use the following checklist to preserve data integrity and minimize risk for your organization:

1. Validate Input: When your data set is supplied by a known or unknown source (an end-user, another application, a malicious user, or any number of other sources) you should require input validation. That data should be verified and validated to ensure that the input is accurate.

2. Validate Data: It's critical to certify that your data processes haven't been corrupted. Identify specifications and key attributes that are important to your organization before you validate the data.

3. Remove Duplicate Data: Sensitive data from a secure database can easily find a home on a document, spreadsheet, email, or in shared folders where employees without proper access can see it. It's prudent to clean up stray data and remove duplicates.

Smaller companies without a dedicated staff will find that these tools can assist them clean up duplicate files on a hard drive or cloud.

For Windows Servers: Use the Data Deduplication feature to clean up cloned files. Also try the File Server Resource Manager to remove stray files.

1. Back up Data: In addition to removing duplicates to ensure data security, data backups are a critical part of the process. Backing up is necessary and goes a long way to prevent permanent data loss. How often should you be backing up? As often as possible.Keep in mind that backups are critical when organizations get hit with ransomware attacks. Just make sure that your backups aren't also encrypted!

2. Access Controls: We've made the case above for input validation, data validation, removing duplications, and backups - all necessary to preserve data integrity. Let's not rule a few popular data security best practices that can also lend a hand or two: access controls and an audit trail! Individuals within an organization without proper access and with malicious intent can do grave harm to the data. What's worse, an outsider impersonating an insider can also be detrimental. Implementing a least privilege model - where only users who need access to data get access - is a very successful form of access control. What's often overlooked is physical access to the server. The most sensitive servers should be isolated and bolted to the floor or wall. Only individuals who access should have an access key - ensuring that the keys to the kingdom are kept secure.

3. Always Keep an Audit Trail: Whenever there is a breach, it's critical to data integrity to be able to track down the source. Often referred to as an audit trail, this provides an organization the breadcrumbs to accurately pin point the source of the problem.

Typically, an audit trail has the following:

Audit trails need to be automatically generated

Users should not have access to or the ability to tamper with the audit trail

Every event - create, delete, read, modified - is tracked and recorded

Every event is also aligned to the user, so you know who accessed the data

Every event is time stamped so that you know when the event took place

Data Integrity Empowers Decision Makers

Not too long ago, it was difficult to collect data. However, today it's no longer an issue. In fact, we're able to collect so much data, the responsible thing to do is to preserve data integrity. That way, management can confidently make data-driven decisions that steer their company in the right direction.

Interested in more information on data integrity? Take a listen to our podcast with Ann Cavoukian on GDPR and Access Control or browse through our article on The Difference Between IAM's User Provisioning and Data Access Management.

Cindy Ng

Cindy is the host of the Inside Out Security podcast.

# data[].attributes.attachment_link.title
What is Data Integrity and How Can You Maintain it?

# data[].attributes.attachment_link.description
If your company's data is altered or deleted, and you have no way of knowing how, when and by whom, it can have a major impact on data-driven business decisions. This is why data integrity is essential. To understand the importance of data integrity to a company's bottom line, let us examine what it is, why it's significant, and how to preserve it.

# data[].attributes.body
I find the information about Normalization rule by I will attaches file below and example data

# data[].attributes.attachment_link.readable_text
What is Normalization?

NORMALIZATION is a database design technique that organizes tables in a manner that reduces redundancy and dependency of data. Normalization divides larger tables into smaller tables and links them using relationships. The purpose of Normalization is to eliminate redundant (useless) data and ensure data is stored logically.

The inventor of the relational model Edgar Codd proposed the theory of normalization with the introduction of the First Normal Form, and he continued to extend theory with Second and Third Normal Form. Later he joined Raymond F. Boyce to develop the theory of Boyce-Codd Normal Form.

In this tutorial, you will learn-

Database Normal Forms

The Theory of Data Normalization in SQL is still being developed further. For example, there are discussions even on 6 th Normal Form. However, in most practical applications, normalization achieves its best in 3rd Normal Form. The evolution of Normalization theories is illustrated below-

Database Normalization Example:

We will study normalization with the help of a case study. Assume, a video library maintains a database of movies rented out. Without any normalization, all information is stored in one table as shown below.

Here you see Movies Rented column has multiple values. Now let's move into 1st Normal Forms:

1NF (First Normal Form) Rules

Each table cell should contain a single value.

Each record needs to be unique.

The above table in 1NF-

1NF Example

Before we proceed let's understand a few things --

What is a KEY?

A KEY is a value used to identify a record in a table uniquely. A KEY could be a single column or combination of multiple columns

Note: Columns in a table that are NOT used to identify a record uniquely are called non-key columns.

What is a Primary Key?

A primary is a single column value used to identify a database record uniquely.

It has following attributes

A primary key cannot be NULL

A primary key value must be unique

The primary key values should rarely be changed

The primary key must be given a value when a new record is inserted.

What is Composite Key?

A composite key is a primary key composed of multiple columns used to identify a record uniquely

In our database, we have two people with the same name Robert Phil, but they live in different places.

Hence, we require both Full Name and Address to identify a record uniquely. That is a composite key.

Let's move into second normal form 2NF

2NF (Second Normal Form) Rules

Rule 1- Be in 1NF

Rule 2- Single Column Primary Key

It is clear that we can't move forward to make our simple database in 2 nd Normalization form unless we partition the table above.

We have divided our 1NF table into two tables viz. Table 1 and Table2. Table 1 contains member information. Table 2 contains information on movies rented.

We have introduced a new column called Membership_id which is the primary key for table 1. Records can be uniquely identified in Table 1 using membership id

Database - Foreign Key

In Table 2, Membership_ID is the Foreign Key

Foreign Key references the primary key of another Table! It helps connect your Tables

A foreign key can have a different name from its primary key

It ensures rows in one table have corresponding rows in another

Unlike the Primary key, they do not have to be unique. Most often they aren't

Foreign keys can be null even though primary keys can not

Why do you need a foreign key?

Suppose, a novice inserts a record in Table B such as

You will only be able to insert values into your foreign key that exist in the unique key in the parent table. This helps in referential integrity.

The above problem can be overcome by declaring membership id from Table2 as foreign key of membership id from Table1

Now, if somebody tries to insert a value in the membership id field that does not exist in the parent table, an error will be shown!

What are transitive functional dependencies?

A transitive functional dependency is when changing a non-key column, might cause any of the other non-key columns to change

Consider the table 1. Changing the non-key column Full Name may change Salutation.

Let's move into 3NF

3NF (Third Normal Form) Rules

Rule 1- Be in 2NF

Rule 2- Has no transitive functional dependencies

To move our 2NF table into 3NF, we again need to again divide our table.

3NF Example

We have again divided our tables and created a new table which stores Salutations.

There are no transitive functional dependencies, and hence our table is in 3NF

In Table 3 Salutation ID is primary key, and in Table 1 Salutation ID is foreign to primary key in Table 3

Now our little example is at a level that cannot further be decomposed to attain higher forms of normalization. In fact, it is already in higher normalization forms. Separate efforts for moving into next levels of normalizing data are normally needed in complex databases. However, we will be discussing next levels of normalizations in brief in the following.

BCNF (Boyce-Codd Normal Form)

Even when a database is in 3 rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key.

Sometimes is BCNF is also referred as 3.5 Normal Form.

4NF (Fourth Normal Form) Rules

If no database table instance contains two or more, independent and multivalued data describing the relevant entity, then it is in 4 th Normal Form.

5NF (Fifth Normal Form) Rules

A table is in 5 th Normal Form only if it is in 4NF and it cannot be decomposed into any number of smaller tables without loss of data.

6NF (Sixth Normal Form) Proposed

6 th Normal Form is not standardized, yet however, it is being discussed by database experts for some time. Hopefully, we would have a clear & standardized definition for 6 th Normal Form in the near future...

That's all to Normalization!!!

Summary

Database designing is critical to the successful implementation of a database management system that meets the data requirements of an enterprise system.

Normalization helps produce database systems that are cost-effective and have better security models.

Functional dependencies are a very important component of the normalize data process

Most database systems are normalized database up to the third normal forms.

A primary key uniquely identifies are record in a Table and cannot be null

A foreign key helps connect table and references a primary key

# data[].attributes.attachment_link.title
What is Normalization? 1NF, 2NF, 3NF & BCNF with Examples

# data[].attributes.attachment_link.description
NORMALIZATION is a database design technique that organizes tables in a manner that reduces redundancy and dependency of data. Normalization divides larger tables into smaller tables and links them using relationships. The purpose of Normalization is to eliminate redundant (useless) data and ensure data is stored logically.

# data[].attributes.body
Today our group start do the objective 1. By  help searching  the way to do, how to do and  what is it .?
1. To re -design the current excel database according to normalization rules
2. To create database , all entities with necessary attributes and constraint in phymyadmin
3. To perform data import form excel into relevant tables without any  SQL errors and server errors
4.  To maintain database integrity and functionality