Question

Now you are ready for the implementation phase. For this part we will use the DDL scripts that

you created in your previous lab. Execute those scripts again to create the set of normalized

relations and populate with data.

Part 03



Now that you have a set of relations:



Create at least 1 index of your choice.



Drop one of the tables and then recreate the table but this time create a partitioned

table (any type of partition that makes sense for your case i.e partition by year, partition

by state area (east, northeast, ... ), partition by id range,.... )

DDL script：

Now you are ready for the implementation phase. For this part we will use the DDL scripts that

you created in your previous lab. Execute those scripts again to create the set of normalized

relations and populate with data.

Part 03



Now that you have a set of relations:



Create at least 1 index of your choice.



Drop one of the tables and then recreate the table but this time create a partitioned

table (any type of partition that makes sense for your case i.e partition by year, partition

by state area (east, northeast, ... ), partition by id range,.... )v

Create table store(

Store_name varchar(50),

Store_mgr varchar(50),

Opening_date date,

PRIMARY KEY (Store_name)

);

CREATE TABLE Employee

(

Emp_Id integer

Emp_Salary number,

Emp_Super varchar,

Emp_Dept integer,

PRIMARY KEY (Emp_Id),

)

Create table Department

(

Dept_Id integer,

Dept_Name varchar(50),

PRIMARY KEY (Dept_Id)

)

CREATE TABLE Emp_Store

(

Emp_Id integer,

Store_Name VARCHAR(50),

Hours_per_week integer,

Rating number,

PRIMARY KEY (Emp_Id, Store_Name)

FOREIGN KEY (Emp_Id) REFERENCES Employee (Emp_Id),

Answer 1

For making partition we will drop the table Employee

 DROP TABLE employee;

Now making a new table employee and without any dependencies

Making PARTITION according to the Emp_Id of Employee

 CREATE TABLE `employee` ( `Emp_Id` int(11) NOT NULL, `Emp_Salary` int(11) DEFAULT NULL, `Emp_Super` varchar(520) DEFAULT NULL, `Emp_Dept` int(11) DEFAULT NULL) PARTITION BY RANGE (Emp_Id)(                 PARTITION p0 VALUES LESS THAN (100),            PARTITION p1 VALUES LESS THAN (500),            PARTITION p3 VALUES LESS THAN MAXVALUE );

selection from the PARTITION table

 select * from employee PARTITION (`p1`); select * from employee PARTITION (`p3`);

selecting data from partition and the base table;

 SELECT Emp_Salary, COUNT(Emp_Dept) AS numbers FROM employee PARTITION (p1,p3) GROUP BY Emp_Super HAVING numbers < 4;

Delete PARTITION table from the employee table

 ALTER TABLE employee TRUNCATE PARTITION p0;