Relational Algebra Practice Questions Consider the following

Relational Algebra Practice Questions. Consider the following conditions in your instantiation. A) Design a table with 10 tuples (rows) For each of four tables provided in airline data base schema. B) Fill in tables with random values. (Values should be meaningful) C) Tables that have common columns (e.g. \'aid\'), should have at least 4 common values for that column. D) Based on your original tables, create result table for each operation.

Solution

CREATE TABLE IF NOT EXISTS `aircraft` (
`aid` varchar(20) NOT NULL,
`aname` varchar(20) DEFAULT NULL,
`range_aircraft` int(10) DEFAULT NULL,
PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Values for aircraft

INSERT INTO `aircraft` (`aid`, `aname`, `range_aircraft`) VALUES
(\'A001\', \'Airbus100\', 3500),
(\'A002\', \'Airbus13\', 4000),
(\'A003\', \'Tupoloev\', 3500),
(\'A004\', \'Airbus155\', 3500),
(\'A005\', \'Boeing787\', 3500),
(\'A006\', \'Boeing123\', 4500),
(\'A007\', \'Embraer12\', 5000),
(\'A008\', \'Embraer456\', 3000),
(\'A009\', \'Bombardier100\', 3000),
(\'A010\', \'Bombardier200\', 3500);

CREATE TABLE IF NOT EXISTS `certified` (
`eid` varchar(20) DEFAULT NULL,
`aid` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Values for table certified

INSERT INTO `certified` (`eid`, `aid`) VALUES
(\'E001\', \'A005\'),
(\'E001\', \'A001\'),
(\'E003\', \'A002\'),
(\'E003\', \'A003\'),
(\'E003\', \'A004\'),
(\'E004\', \'A006\'),
(\'E005\', \'A007\'),
(\'E006\', \'A007\'),
(\'E007\', \'A009\'),
(\'E008\', \'A010\');

CREATE TABLE IF NOT EXISTS `employee` (
`eid` varchar(20) NOT NULL,
`ename` varchar(20) DEFAULT NULL,
`salary` int(10) DEFAULT NULL,
PRIMARY KEY (`eid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Values for table `employee`

INSERT INTO `employee` (`eid`, `ename`, `salary`) VALUES
(\'E001\', \'Williamson\', 120000),
(\'E002\', \'Taylor\', 150000),
(\'E003\', \'Boult\', 100000),
(\'E004\', \'McCullum\', 220000),
(\'E005\', \'Guptill\', 110000),
(\'E006\', \'Ponting\', 100000),
(\'E007\', \'Smith\', 150000),
(\'E008\', \'Watson\', 120000),
(\'E009\', \'Bracewell\', 120000),
(\'E010\', \'Lee\', 120000);

CREATE TABLE IF NOT EXISTS `flights` (
`flno` varchar(10) DEFAULT NULL,
`from_origin` varchar(20) DEFAULT NULL,
`to_destination` varchar(20) DEFAULT NULL,
`departs` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Values for table `flights`

INSERT INTO `flights` (`flno`, `from_origin`, `to_destination`, `departs`) VALUES
(\'AI-651\', \'Mumbai\', \'Visakhapatnam\', \'7:30AM\'),
(\'SG-756\', \'Chennai\', \'Mumbai\', \'8:00AM\'),
(\'AI-652\', \'Mumbai\', \'Delhi\', \'8:30AM\'),
(\'JE-123\', \'Mumbai\', \'Bangalore\', \'7:30AM\'),
(\'IG-456\', \'Bangalore\', \'Visakhapatnam\', \'7:30AM\'),
(\'GA-333\', \'Goa\', \'Visakhapatnam\', \'7:30AM\'),
(\'SG-122\', \'Delhi\', \'Kolkata\', \'9:00PM\'),
(\'AI-653\', \'Mumbai\', \'Raipur\', \'7:30AM\'),
(\'AI-654\', \'Mumbai\', \'Cochin\', \'7:30AM\'),
(\'AI-655\', \'Mumbai\', \'Bhubhaneshwar\', \'7:30AM\');

PS: Practice questions were not given in the post.