Database Backup and Restoration Operations

Database and Table Creation

Create a database named libraryDB and switch to it:

CREATE DATABASE libraryDB;
USE libraryDB;

Define tables for books, authors, and their relationships:

CREATE TABLE book_catalog (
    book_id INT PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    pub_year YEAR NOT NULL
);

CREATE TABLE author_list (
    author_id INT PRIMARY KEY,
    name VARCHAR(20),
    gender CHAR(1)
);

CREATE TABLE book_author_link (
    author_id INT NOT NULL,
    book_id INT NOT NULL
);

Data Insertion

Populate the tables with sample data:

INSERT INTO book_catalog VALUES
(11078, 'Learning MySQL', 2010),
(11033, 'Study Html', 2011),
(11035, 'How to use php', 2003),
(11072, 'Teach yourself javascript', 2005),
(11028, 'Learning C++', 2005),
(11069, 'MySQL professional', 2009),
(11026, 'Guide to MySQL 5.5', 2008),
(11041, 'Inside VC++', 2011);

INSERT INTO author_list VALUES
(1001, 'WriterX', 'f'),
(1002, 'WriterA', 'f'),
(1003, 'WriterB', 'm'),
(1004, 'WriterC', 'f'),
(1011, 'WriterD', 'f'),
(1012, 'WriterE', 'm'),
(1013, 'WriterF', 'm'),
(1014, 'WriterG', 'f'),
(1015, 'WriterH', 'f');

INSERT INTO book_author_link VALUES
(1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),
(1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);

Backup Procedures

1. Backup all tables in the libraryDB database:

   mysqldump -u admin -p libraryDB > libraryDB_full_backup.sql
   

2. Backup only the book_catalog table from libraryDB:

   mysqldump -u admin -p libraryDB book_catalog > libraryDB_book_backup.sql
   

3. Backup multipel databases (libraryDB and sampleDB):

   mysqldump -u admin -p --databases libraryDB sampleDB > multi_db_backup.sql
   

Restoration Methods

4. Restore the book_catalog table using the mysql command:

   mysql -u admin -p libraryDB < libraryDB_book_backup.sql
   

5. Restore the book_catalog table within the MySQL shell:

   USE libraryDB;
   SOURCE libraryDB_book_backup.sql;
   

Index Management Tasks

Table Structure Setup

Create a table for products:

CREATE TABLE product_inventory (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(20) NOT NULL,
    category_id INT DEFAULT 0,
    brand_id INT DEFAULT 0,
    product_code CHAR(12) NOT NULL,
    price DECIMAL(6,2) DEFAULT 0.00,
    description TEXT
);

Create a tible for categories:

CREATE TABLE product_category (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(20),
    parent_id INT DEFAULT 0
);

Index Operations

1. Modify the product_inventory table by removing the description and product_code columns, and adding a view_count column:

   ALTER TABLE product_inventory
   DROP COLUMN description,
   DROP COLUMN product_code,
   ADD COLUMN view_count INT DEFAULT 0;
   

2. Add a unique index on the product_name column using ALTER TABLE:

   ALTER TABLE product_inventory ADD UNIQUE INDEX idx_product_name (product_name);
   

3. Create a standard index on the price column using CREATE INDEX:

   CREATE INDEX idx_price ON product_inventory (price);
   

4. Add and remove an index on the view_count column:

   • Add the index:

     CREATE INDEX idx_view_count ON product_inventory (view_count);
     

   • Remove the index using DROP INDEX:

     DROP INDEX idx_view_count ON product_inventory;
     

   • Remove the index using ALTER TABLE:

     ALTER TABLE product_inventory DROP INDEX idx_view_count;