Data Partitioning and Relational Model Implementation

Posted on May 6, 2024 in Computers

Implementation of Data Partitioning through Range

Step 1: Create a Database

mysql>create database mca;

mysql>use mca

Step 2: Create a Partitioned Table

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE (YEAR(purchased)) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005),
PARTITION p4 VALUES LESS THAN (2010),
PARTITION p5 VALUES LESS THAN (2015)
);

Step 3: Insert Data

mysql> INSERT INTO tr VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'alarm clock', '1997-11-05'),
(3, 'chair', '2009-03-10'),
(4, 'bookcase', '1989-01-10'),
(5, 'exercise bike', '2014-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'espresso maker', '2011-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '2006-09-16'),
(10, 'lava lamp', '1998-12-25');

Step 4: Check Inserted Data

mysql> select * from tr;

Step 5: Query Specific Partitions

mysql> SELECT * FROM tr PARTITION (p2);
mysql> SELECT * FROM tr PARTITION (p5);
mysql> SELECT * FROM tr PARTITION (p4);

Step 6: View Partition Information

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM
INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='tr';

Step 7: Drop a Partition

MySQL> ALTER TABLE tr TRUNCATE PARTITION p0;

Mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM
INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='tr';

Implementation of Data Partitioning through List Partition

Step 1: Create a List Partitioned Table

Mysql> CREATE TABLE Stores (
cust_name VARCHAR(40),
bill_no VARCHAR(20) NOT NULL,
store_id INT PRIMARY KEY NOT NULL,
bill_date DATE NOT NULL,
amount DECIMAL(8,2) NOT NULL
)
PARTITION BY LIST(store_id) (
PARTITION pEast VALUES IN (101, 103, 105),
PARTITION pWest VALUES IN (102, 104, 106),
PARTITION pNorth VALUES IN (107, 109, 111),
PARTITION pSouth VALUES IN (108, 110, 112));

Implementation of Abstract Data Type & Reference (Relational Model)

Creating Tables

CREATE TABLE Customer_reltab (
CustNo NUMBER NOT NULL,
CustName VARCHAR2(200) NOT NULL,
Street VARCHAR2(200) NOT NULL,
City VARCHAR2(200) NOT NULL,
State CHAR (2) NOT NULL,
Zip VARCHAR2(20) NOT NULL,
Phone1 VARCHAR2(20),
Phone2 VARCHAR2(20),
Phone3 VARCHAR2(20),
PRIMARY KEY (CustNo));
SQL>desc Customer_reltab;

CREATE TABLE PurchaseOrder_reltab (
PONo NUMBER, /* purchase order no */
Custno NUMBER references Customer_reltab, /* Foreign
KEY referencing customer */
OrderDate DATE, /* date of order */
ShipDate DATE, /* date to be shipped */
ToStreet VARCHAR2(200), /* shipto address */
ToCity VARCHAR2(200),
ToState CHAR (2),
ToZip VARCHAR2(20),
PRIMARY KEY(PONo));

CREATE TABLE Stock_reltab (
StockNo NUMBER PRIMARY KEY,
Price NUMBER,
TaxRate NUMBER);

CREATE TABLE LineItems_reltab (
LineItemNo NUMBER,
PONo NUMBER REFERENCES PurchaseOrder_reltab,
StockNo NUMBER REFERENCES Stock_reltab,
Quantity NUMBER,
Discount NUMBER,
PRIMARY KEY (PONo, LineItemNo));

Inserting Values

1. Inserting into Stock_reltab

INSERT INTO Stock_reltab VALUES (1004, 6750.00, 2);
INSERT INTO Stock_reltab VALUES (1011, 4500.23, 2);
INSERT INTO Stock_reltab VALUES (1534, 2234.00, 2);
INSERT INTO Stock_reltab VALUES (1535, 3456.23, 2);

2. Inserting into Customer_reltab

INSERT INTO Customer_reltab VALUES (1, 'Jean Nance', '2 Avocet Drive','Redwood Shores', 'CA', '95054','415-555-1212', NULL, NULL);
INSERT INTO Customer_reltab VALUES (2, 'John Nike', '323 College Drive','Edison', 'NJ', '08820','609-555-1212', '201-555-1212', NULL);

3. Inserting into PurchaseOrder_reltab

INSERT INTO PurchaseOrder_reltab VALUES (1001, 1, SYSDATE, '10-MAY-1997', NULL, NULL, NULL, NULL);
INSERT INTO PurchaseOrder_reltab VALUES (2001, 2, SYSDATE, '20-MAY-1997','55 Madison Ave', 'Madison', 'WI', '53715');

4. Inserting into LineItems_reltab

INSERT INTO LineItems_reltab VALUES (01, 1001, 1534, 12, 0);
INSERT INTO LineItems_reltab VALUES (02, 1001, 1535, 10, 10);
INSERT INTO LineItems_reltab VALUES (01, 2001, 1004, 1, 0);
INSERT INTO LineItems_reltab VALUES (02, 2001, 1011, 2, 1);

Querying Data

Get Customer and Order Details for Purchase Order 1001

SELECT C.CustNo, C.CustName, C.Street, C.City, C.State, C.Zip, C.phone1, C.phone2, C.phone3, P.PONo, P.OrderDate, L.StockNo, L.LineItemNo, L.Quantity, L.Discount
FROM Customer_reltab C, PurchaseOrder_reltab P, LineItems_reltab L
WHERE C.CustNo = P.CustNo
AND P.PONo = L.PONo
AND P.PONo = 1001;

Get Total Value of Purchase Orders

SELECT P.PONo, SUM(S.Price * L.Quantity)
FROM PurchaseOrder_reltab P, LineItems_reltab L, Stock_reltab S
WHERE P.PONo = L.PONo
AND L.StockNo = S.StockNo
GROUP BY P.PONo;

Get Purchase Order and Line Item Data for Stock Item 1004

SELECT P.PONo, P.CustNo, L.StockNo, L.LineItemNo, L.Quantity, L.Discount
FROM PurchaseOrder_reltab P, LineItems_reltab L
WHERE P.PONo = L.PONo
AND L.StockNo = 1004;

Updating Data

Update Quantity for a Specific Line Item

UPDATE LineItems_reltab
SET Quantity = 20
WHERE PONo = 1001
AND StockNo = 1534;

Deleting Data

Delete Line Items and Purchase Order

DELETE FROM LineItems_reltab WHERE PONo = 1001;
DELETE FROM PurchaseOrder_reltab WHERE PONo = 1001;