(a)
UNION set operator:
The UNION set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains unique values.
Syntax:
QUERY UNION QUERY;
Consider two tables:
Table creation:
CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);
CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));
Inserting values:
INSERT INTO VENDOR VALUES(123);
INSERT INTO VENDOR VALUES(124);
INSERT INTO VENDOR VALUES(125);
INSERT INTO VENDOR VALUES(126);
INSERT INTO PRODUCT VALUES('ABC', 125);
INSERT INTO PRODUCT VALUES('DEF', 124);
INSERT INTO PRODUCT VALUES('GHI', 124);
INSERT INTO PRODUCT VALUES('JKL', 123);
(b)
UNION ALL set operator:
The UNION ALL set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains duplicate values.
Syntax:
QUERY UNION ALL QUERY;
Consider two tables:
Table creation:
CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);
CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));
Inserting values:
INSERT INTO VENDOR VALUES(123);
INSERT INTO VENDOR VALUES(124);
INSERT INTO VENDOR VALUES(125);
INSERT INTO VENDOR VALUES(126);
INSERT INTO PRODUCT VALUES('ABC', 125);
INSERT INTO PRODUCT VALUES('DEF', 124);
INSERT INTO PRODUCT VALUES('GHI', 124);
INSERT INTO PRODUCT VALUES('JKL', 123);
(c)
INTERSECT set operator:
The INTERSECT set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that are common in both the tables.
Syntax:
QUERY INTERSECT QUERY;
Consider two tables:
Table creation:
CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);
CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));
Inserting values:
INSERT INTO VENDOR VALUES(123);
INSERT INTO VENDOR VALUES(124);
INSERT INTO VENDOR VALUES(125);
INSERT INTO VENDOR VALUES(126);
INSERT INTO PRODUCT VALUES('ABC', 125);
INSERT INTO PRODUCT VALUES('DEF', 124);
INSERT INTO PRODUCT VALUES('GHI', 124);
INSERT INTO PRODUCT VALUES('JKL', 123);
(d)
EXCEPT/ MINUS set operator:
The MINUS set operator is used to combine the output of two or more than two queries and produce a result. The produced result contains the values (rows) that appear in the first table but not in the second table. The word “EXCEPT” can also be used in the place of “MINUS”.
Syntax:
QUERY EXCEPT QUERY;
Consider two tables:
Table creation:
CREATE TABLE VENDOR(VEND_CODE INT PRIMARY KEY);
CREATE TABLE PRODUCT(PROD_CODE VARCHAR(5), VEND_CODE INT,FOREIGN KEY (VEND_CODE) REFERENCES VENDOR (VEND_CODE));
Inserting values:
INSERT INTO VENDOR VALUES(123);
INSERT INTO VENDOR VALUES(124);
INSERT INTO VENDOR VALUES(125);
INSERT INTO VENDOR VALUES(126);
INSERT INTO PRODUCT VALUES('ABC', 125);
INSERT INTO PRODUCT VALUES('DEF', 124);
INSERT INTO PRODUCT VALUES('GHI', 124);
INSERT INTO PRODUCT VALUES('JKL', 123);
Trending nowThis is a popular solution!
Chapter 8 Solutions
Database Systems: Design Implementation & Management
- Answer the given question with a proper explanation and step-by-step solution. c. Create a relastionship: You need to add a new column called DeptID in the Employee table, it will be the foreign key in this table, it should be text data type and 5 characters in length. Make sure you enter one of the corresponding departmentID's values (i.e., ECON, MIS, and BUS) for this column in the Employee table for this newly created DeptId column that you have entered in the Department table previously. Then, use the Relationships tool under Database Tools to create a relationship between the Department Table and the Employee table, i.e., connecting the two tables together via the primary key (DepartmentID in Department Table) and foreign key pair (DeptID in the Employee Table and choose Enforce Referential Integrity to set the integrity contraint between the foreign key and primary keyarrow_forwardTable 1 is the Sales table, which is an unnormalized table. The description of the Sales table are SNUM = salesman number, CNUM = customer number, WNUM = warehouse number, SAMOUNT = sale amount, etc. a.)Write down four functional dependencies (FDs) that are valid in the Sales table b.)Write down the Sales table's normalization result that is in the 2NF, but not in the 3NF.Explain why the table is not in the 3NF.arrow_forwardIn relational model terminology, a table is considered as Select one: a. Tuple b. Range c. Relation d. Domainarrow_forward
- Assume the EMPLOYEE table contains four attributes, ID, DEPT, SALARY and NAME, and has the following rows ID DEPT SALARY NAME 100 SALES 40000 Smith 101 RD 38000 Terry 102 HR 60000 David 103 SALES 58000 Ellie 104 RD 70000 Judy The DEPARTMENT table contains two attributes, DEPTNAME and PHONE, and has the following rows: DEPTNAME PHONE RD 8596001234 SALES 8596005555 HR 8596009876 A.) The EMPLOYEE table needs to enforce the following integrity constraints: • The ID is the primary key. • The DEPT column is a foreign key that references the DEPTNAME in the DEPARTMENT table, so that any value that appears in the DEPT column should also exist in the DEPARTMENT table. • The value for the NAME column can never be null. What should be the statement used to create the EMPLOYEE table, if 1) the table was created as a partitioned table such…arrow_forwardProblem 22 Insert the following customer into the CUST_MYSQL table, allowing the AUTO_INCREMENT attribute set up in Problem 20.a and Problem 20.b to generate the customer number automatically: CUST_LNAME CUST_FNAME CUST_BALANCE Powers Ruth 500 This is what problem 20.a said, and got right: Using MySQL, create a table named CUST_MYSQL with the same fields as in Problem 16, except, use the AUTO_INCREMENT feature for the CUST_NUM field. This is what problem 20.b said, and got right: Using MySQL, alter the table to populate the CUST_NUM field, beginning the increment with 2000.arrow_forwardThe table STUDENT has the attributes STUDENT_ID, NAME, SCHOOL_ID, and ADDR. The table SCHOOL has the attributes SCHOOL_ID, NAME, and STATE_CODE. Assume that there is a 1:N relation between SCHOOL and STUDENT. Which of the following creates a table that shows the STUDENT attributes and the corresponding SCHOOL attributes? a. SELECT * SCHOOL_ID FROM STUDENT JOIN SCHOOL b. SELECT * SCHOOL_ID FROM STUDENT JOIN SCHOOL USING(SCHOOL_ID) c. SELECT * SCHOOL_ID FROM STUDENT NATURAL JOIN SCHOOL d. SELECT * SCHOOL_ID FROM STUDENT, SCHOOL WHERE SCHOOL_ID = SCHOOL_IDarrow_forward
- Assume you have a vehicle table with a surrogate key vehicle_id column and VIN (Vehicle Identification Number) column and some other columns. You want to designate a unique index on the vehicle_id and VIN columns because together they filter on the joining column with the highest cardinality (or degree of uniqueness). Write the syntax to add a unique index on the vehicle_id and VIN columns triggered by joins on the surrogate key column?arrow_forwardClosely examine the following tables and use it to answer the questions that follow: department course student_type student_category Student registration registration_type Suppose that the following are true: - all id column are primary keys in whichever table they are - the dept_id in course table is foreign key that is referencing the id column in the department table - the foreign keys stud_type_id, stud_cat_id in the student table are referencing the id columns in the student_type and student_category tables respectively. - the coursecode and stud_id columns in the registration table are foreign keys in the course and student tables respectively. 1. Design an Entity Relationship Diagram for the system above. 2. Write query that would return the names and contacts of all Local students. 3. Write a query that would return names, contact and locations of all departments that has a location text with Ave 3 as part of it. [hint: use the LIKE clause with “_” or “%”] 4. What would…arrow_forwardWrite SQL code for the following design schema of three tables. The tables should have the following properties: sales table has a primary key on SalesNumber and it is set to AUTO-INCREMENT.products table has a primary key on number and it is set to AUTO-INCREMENT, they is an unique index on prodidsalesdetails table has a primary key on number and it is set to AUTO-INCREMENT, they is an index on prodid. They is a one-to-many relationship between the salesdetails.SalesNumber andsales.SalesNumber fields. There is a one-to-many relationship between the products.prodid and salesdetails.prodid fields. There is to be a delete constraint on the product.prodid field a record in the product table should not be allowed to be deleted if they exist a matching prodid in the salesdetails table. Primary and foreign keys should be implemented in the appropriate tables. Referential Integrity should be enforce at the database level, where appropriate for example, if a sales record is deleted all…arrow_forward
- https://sqliteonline.com/ : Program link The first requirement: Using the SQL language, transform the following entity model into tables in the database. Entity Manger contains: M-name The name of the manager. M-ID and manager number.The department entity contains: D-name The name of the department. D-ID and department number.Create constraint master key name of the manager when creating the Manger table.Creating the foreign key constraint The name of the manager when creating the Department table. The second requirement: After creating the tables and specifying the primary and foreign key, do the following: * Add a column D_loc to the Department table, specifying the type of column.* Remove the column named M_name from the Manger table* Change the name of the department table to Dep Please:: ******* ATTACH A PICTURE OF THE CODES USED AND PICTURE OF THE PROGRAM SCREEN *******arrow_forwardSuppose that the following are true: all id column are primary keys in whichever table they are the dept_id in course table is foreign key that is referencing the id column in the department table the foreign keys stud_type_id, stud_cat_id in the student table are referencing the id columns in the student_type and student_category tables respectively. the coursecode and stud_id columns in the registration table are foreign keys in the course and student tables respectively. 1. Design an Entity Relationship Diagram for the system above. 2. Write query that would return the names and contacts of all Local students. 3. Write a query that would return names, contact and locations of all departments that has a location text with Ave 3 as part of it. [hint: use the LIKE clause with "_" or "%"] 4. What would be the result returned for the following query: SELECT first_name, last_name, student_type.name, student_category.name FROM student, student_type, student_category WHERE stud_type_id =…arrow_forwardWrite relation algebra for following:Student( Enrno, name, courseId, emailId, cellno)Course(courseId, course_nm, duration)i) Add a column city in student table.ii) Find out list of students who have enrolled in “computer” course.iii) List name of all courses with their duration.iv) List name of all students start with “a”.v) List email Id and cell no of all mechanical engineering students.arrow_forward
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning