CamScanner...

1. Suppose we have to create a table for database of the student having five different column i.e Roll number, first name, last name, email id ,department.
a) Different partition schemes for this task in MySQL Are:-
Suppose we have created a table with MYSQL
CREATE TABLE Student(roll_n INT NOT NULL,
f_name VARCHAR(20),
l_name VARCHAR(20),
email VARCHAR(30).
department VARCHAR(30))
· Range Partitioning:-This type of partition allows us to partition of rows in the based of Column values. Less than operator is used for this purpose. This range of each partition should be in contiguous manner but should not be overlap each other.
eg:- Suppose we want to do 4 partition based on roll number
PARTITION BY RANGE (roll_n)(
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (30),
PARTITION p2 VALUES LESS THAN (70),
PARTITION p3 VALUES LESS THAN (100));
· List Partitioning:-This type partition allows to partition rows based on the list of discrete column values rather than in contiguous range . VALUES IN() statement is used for defining each partition.
eg. Suppose we want to partition on roll number
PARTITION BY LIST(roll_n) ( 
PARTITION pEast VALUES IN (5,10), 
PARTITION pWest VALUES IN (15, 18, 25), 
PARTITION pNorth VALUES IN (35, 45, 55), 
PARTITION pSouth VALUES IN (75, 85, 95));
· HASH Partitioning:-This type of partition used to divide data in predefined number of partitions.
eg:-
PARTITION BY HASH(roll_n)
PARTITIONS 5;
· COLUMNS Partitioning:-This type partitioning allows us to use multiple column in partition keys.This is of two type RANGE COLUMN PARTITIONING and list column partitioning.
eg.
PARTITION BY RANGE COLUMN(roll_n , department)
partition p0 VALUES LESS THAN(50,’COMPUTER SCIENCE’),
partition p01 VALUES LESS THAN(100,’ELECTRICAL SCIENCE’)
);
· KEY Partitioning:- In this type of partition ,Key is used to divide data in predefined number of partitions. If table contains primary key then primary key is used for partition otherwise unique key is used as partition key.
eg.
PARTITION BY KEY()  
PARTITIONS 2;
· Sub Partitioning:-It is used to partition further inside the partition.
eg.
PARTITION BY RANGE( roll_n) )  
    SUBPARTITION BY HASH(roll_n )  
    SUBPARTITIONS 2 (  
        PARTITION p0 VALUES LESS THAN (50),  
        PARTITION p1 VALUES LESS THAN (100) ); 
b. Different type of partition schemes in PostgreSQL
· RANGE partitioning:-This type of partition allows us to partition of rows in the based of Column values.
CREATE TABLE Student(roll_n integer not null,
f_name text not null,
l_name text not null),
email text not null
department text not null)
)PARTITION BY RANGE(roll_n);
CREATE TABLE roll_n1 PARTITION OF Student
FOR VALUES FROM 1 TO 50;
CREATE TABLE roll_n2 PARTITION OF Student
FOR VALUES FROM 51 TO 100;
· List partitioning:-Partition a table by a list of known values.
eg.
CREATE TABLE Student(roll_n integer not null,
f_name text not null,
l_name text not null),
email text not null
department text not null)
)PARTITION BY LIST(department);
CREATE TABLE department1 PARTITION OF Student
FOR VALUES FROM IN (“COMPUTER SCIENCE”);
CREATE TABLE department2 PARTITION OF Student
FOR VALUES IN (“ELECTRICAL”);
· Hash partioning:-Partition a table using a hash function on the partition key. This is especially useful when there is no obvious way of dividing data into logically similar groups and is often used on categorical partitioning keys that are accessed individually.
eg.
CREATE TABLE Student(roll_n integer not null,
f_name text not null,
l_name text not null),
email text not null
department text not null)
)PARTITION BY HASH(roll_n);
CREATE TABLE roll_n0 PARTITION of roll_n FOR VALUES WITH(MODULUS 3,REMAINDER 0);
CREATE TABLE roll_n1 PARTITION of roll_n FOR VALUES WITH(MODULUS 3,REMAINDER 1);
****************END of Question 1***************
2.
Key steps for Installing MySQL in local Computer:
· Download MySQL installation package from website https://www.mysql.com/downloads/ which is compatible on your operating system.
· After download click on downloaded file.
·...