Question

1 Approved Answer

Posted on Jul 17, 2024

Consider the relational tables created by the execution of the following CREATE TABLE statements. CREATE TABLE EMPLOYEE ENUM DECIMAL (12) NOT NULL, FNAME VARCHAR (50)

image text in transcribed

Consider the relational tables created by the execution of the following CREATE TABLE statements. CREATE TABLE EMPLOYEE ENUM DECIMAL (12) NOT NULL, FNAME VARCHAR (50) NOT NULL, INITIALS VARCHAR (5) NULL, LNAME VARCHAR (50) NOT NULL, DOB DATE NULL, BLDG DECIMAL (3) NOT NULL, STREET VARCHAR (50) NOT NULL, SUBURB VARCHAR(50) NOT NULL, STATE VARCHAR(5) NOT NULL, ZIPCODE DECIMAL (4) NOT NULL, CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY (ENUM)); CREATE TABLE DRIVER ( ENUM DECIMAL (12) NOT NULL, LNUM DECIMAL (8) NOT NULL, STATUS VARCHAR (10) NOT NULL, CONSTRAINT DRIVER_PKEY PRIMARY KEY (ENUM), CONSTRAINT DRIVER_UNIQUE UNIQUE (LNUM), CONSTRAINT DRIVER FKEY FOREIGN KEY (ENUM) REFERENCES EMPLOYEE (ENUM), CONSTRAINT DRIVER STATUS CHECK ( STATUS IN ('AVAILABLE', 'BUSY', 'ON LEAVE'))); CREATE TABLE ADMIN ENUM DECIMAL (12) NOT NULL, POSITION VARCHAR (50) NOT NULL, CONSTRAINT ADMIN_PKEY PRIMARY KEY (ENUM), CONSTRAINT ADMIN FKEY FOREIGN KEY (ENUM) REFERENCES EMPLOYEE (ENUM)); CREATE TABLE TRUCK REGNUM VARCHAR (10) NOT NULL, CAPACITY DECIMAL (7) NOT NULL, WEIGHT DECIMAL (5) NOT NULL, STATUS VARCHAR(10) NOT NULL, CONSTRAINT TRUCK_PKEY PRIMARY KEY (REGNUM), CONSTRAINT TRUCK STATUS CHECK (STATUS IN ('AVAILABLE', 'USED', MAINTAINED' ) ) ); CREATE TABLE TRIP TNUM DECIMAL (10) NOT NULL, LNUM DECIMAL (8) NOT NULL, REGNUM VARCHAR(10) NOT NULL, TRIP_DATE DATE NOT NULL, CONSTRAINT TRIP_PKEY PRIMARY KEY (TNUM), CONSTRAINT TRIP FKEY1 FOREIGN KEY (LNUM) REFERENCES DRIVER (LNUM), CONSTRAINT TRIP_FKEY2 FOREIGN KEY (REGNUM) REFERENCES TRUCK (REGNUM)); CREATE TABLE TRIPLEG TNUM DECIMAL (10) NOT NULL, LEGNUM DECIMAL (2) NOT NULL, DEPARTURE VARCHAR (30) NOT NULL, DESTINATION VARCHAR(30) NOT NULL, CONSTRAINT TRIPLEG_PKEY PRIMARY KEY (TNUM, LEGNUM), CONSTRAINT TRIPLEG_UNIQUE UNIQUE (TNUM, DEPARTURE, DESTINATION), CONSTRAINT TRIPLEG_FKEYI FOREIGN KEY (TNUM) REFERENCES TRIP (TNUM)); The database contains information about employees, drivers and administration staff, trucks, trips made by drivers, and legs of each trip. After loading data into the database the relational tables have the following sizes: EMPLOYEE 60 data blocks DRIVER 30 data blocks ADMIN 10 data blocks TRUCK 50 data blocks TRIP 100 data blocks TRIPLEG 300 data blocks We would like to use clustering to improve performance of the following types of queries: (i) Find full information about the drivers who live at a given address. (ii) Find full information about the administration people who live at a given address. (iii) Find full information about the trucks used by a driver with a given license number. (iv) Find full information about the drivers who made a trip on a given date. (v) Find full information about the legs of trips that used a truck with a given registration number. Assume, that queries (i) and (ii) are processed 10 times per day. Assume that queries (iii) and (iv) are processed 30 times per day. Assume that query (v) is processed 20 times per day. Assume that the relational tables r and s consist of b, and b, blocks each. Then - if r and s are clustered together then to read a cluster we need by + b, read block operations and if r and s are not clustered together then to join the tables we need 3* (b: +bs) read block operations (approximate estimation of hash-based join). Use a method of finding suboptimal clustering explained to you during the lecture classes in a presentation 18 Clustering to find suboptimal clustering of the sample database that improves the performance of the queries listed above. Deliverables A file solution 3.pdf with the following components: (1) Computations of costs and benefits that lead to construction of clustering graph. (2) A drawing of a clustering graph. (3) Optimal clustering that improves performance of some of some of queries (i), (i), (iii), (iv) and (v). Consider the relational tables created by the execution of the following CREATE TABLE statements. CREATE TABLE EMPLOYEE ENUM DECIMAL (12) NOT NULL, FNAME VARCHAR (50) NOT NULL, INITIALS VARCHAR (5) NULL, LNAME VARCHAR (50) NOT NULL, DOB DATE NULL, BLDG DECIMAL (3) NOT NULL, STREET VARCHAR (50) NOT NULL, SUBURB VARCHAR(50) NOT NULL, STATE VARCHAR(5) NOT NULL, ZIPCODE DECIMAL (4) NOT NULL, CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY (ENUM)); CREATE TABLE DRIVER ( ENUM DECIMAL (12) NOT NULL, LNUM DECIMAL (8) NOT NULL, STATUS VARCHAR (10) NOT NULL, CONSTRAINT DRIVER_PKEY PRIMARY KEY (ENUM), CONSTRAINT DRIVER_UNIQUE UNIQUE (LNUM), CONSTRAINT DRIVER FKEY FOREIGN KEY (ENUM) REFERENCES EMPLOYEE (ENUM), CONSTRAINT DRIVER STATUS CHECK ( STATUS IN ('AVAILABLE', 'BUSY', 'ON LEAVE'))); CREATE TABLE ADMIN ENUM DECIMAL (12) NOT NULL, POSITION VARCHAR (50) NOT NULL, CONSTRAINT ADMIN_PKEY PRIMARY KEY (ENUM), CONSTRAINT ADMIN FKEY FOREIGN KEY (ENUM) REFERENCES EMPLOYEE (ENUM)); CREATE TABLE TRUCK REGNUM VARCHAR (10) NOT NULL, CAPACITY DECIMAL (7) NOT NULL, WEIGHT DECIMAL (5) NOT NULL, STATUS VARCHAR(10) NOT NULL, CONSTRAINT TRUCK_PKEY PRIMARY KEY (REGNUM), CONSTRAINT TRUCK STATUS CHECK (STATUS IN ('AVAILABLE', 'USED', MAINTAINED' ) ) ); CREATE TABLE TRIP TNUM DECIMAL (10) NOT NULL, LNUM DECIMAL (8) NOT NULL, REGNUM VARCHAR(10) NOT NULL, TRIP_DATE DATE NOT NULL, CONSTRAINT TRIP_PKEY PRIMARY KEY (TNUM), CONSTRAINT TRIP FKEY1 FOREIGN KEY (LNUM) REFERENCES DRIVER (LNUM), CONSTRAINT TRIP_FKEY2 FOREIGN KEY (REGNUM) REFERENCES TRUCK (REGNUM)); CREATE TABLE TRIPLEG TNUM DECIMAL (10) NOT NULL, LEGNUM DECIMAL (2) NOT NULL, DEPARTURE VARCHAR (30) NOT NULL, DESTINATION VARCHAR(30) NOT NULL, CONSTRAINT TRIPLEG_PKEY PRIMARY KEY (TNUM, LEGNUM), CONSTRAINT TRIPLEG_UNIQUE UNIQUE (TNUM, DEPARTURE, DESTINATION), CONSTRAINT TRIPLEG_FKEYI FOREIGN KEY (TNUM) REFERENCES TRIP (TNUM)); The database contains information about employees, drivers and administration staff, trucks, trips made by drivers, and legs of each trip. After loading data into the database the relational tables have the following sizes: EMPLOYEE 60 data blocks DRIVER 30 data blocks ADMIN 10 data blocks TRUCK 50 data blocks TRIP 100 data blocks TRIPLEG 300 data blocks We would like to use clustering to improve performance of the following types of queries: (i) Find full information about the drivers who live at a given address. (ii) Find full information about the administration people who live at a given address. (iii) Find full information about the trucks used by a driver with a given license number. (iv) Find full information about the drivers who made a trip on a given date. (v) Find full information about the legs of trips that used a truck with a given registration number. Assume, that queries (i) and (ii) are processed 10 times per day. Assume that queries (iii) and (iv) are processed 30 times per day. Assume that query (v) is processed 20 times per day. Assume that the relational tables r and s consist of b, and b, blocks each. Then - if r and s are clustered together then to read a cluster we need by + b, read block operations and if r and s are not clustered together then to join the tables we need 3* (b: +bs) read block operations (approximate estimation of hash-based join). Use a method of finding suboptimal clustering explained to you during the lecture classes in a presentation 18 Clustering to find suboptimal clustering of the sample database that improves the performance of the queries listed above. Deliverables A file solution 3.pdf with the following components: (1) Computations of costs and benefits that lead to construction of clustering graph. (2) A drawing of a clustering graph. (3) Optimal clustering that improves performance of some of some of queries (i), (i), (iii), (iv) and (v)