The term relationship refers to the connection between two pieces of data that have some association, such as a book and its author, or a book and the customer who bought the book. A relational database such as MySQL specializes in storing and retrieving such relations.
The process of removing duplicate data and optimizing tables is called normalization.
The three rules of First Normal Form are: (1) there should be no repeating columns containing the same kind of data, (2) all columns should contain a single value, and (3) there should be a primary key to uniquely identify each row.
To satisfy Second Normal Form, columns whose data repeats across multiple rows should be removed to their own tables.
In a one-to-many relationship, the primary key from the table on the “one” side must be added as a separate column (a foreign key) to the table on the “many” side.
To create a database with a many-to-many relationship, you create an intermediary table containing keys from two other tables. The other tables can then reference each other via the third.
To initiate a MySQL transaction, use either the BEGIN or the START
TRANSACTION command. To terminate a transaction and cancel
all actions, issue a ROLLBACK
command. To terminate a transaction and commit all actions, issue a
COMMIT command.
To examine how a query will work in detail, you can use the
EXPLAIN command.
To back up the database publications to a file called publications.sql, you would use a command
such as:
mysqldump -uuser-ppasswordpublications > publications.sql