Database Systems: Design, Implementation, & Management
11th Edition
ISBN: 9781285196145
Author: Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Question
Chapter 8, Problem 18RQ
Program Plan Intro
(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);
Program Plan Intro
(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);
Program Plan Intro
(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);
Program Plan Intro
(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);
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
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 key
Convert the following table to third normal form (3NF). In this table, StudentNum determines
StudentName, NumCredits, AdvisorNum, and AdvisorName. Advisor Num determines AdvisorName.
CourseNum determines Description. The combination of StudentNum and CourseNum determines
Grade.
STUDENT (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName, CourseNum,
Description, Grade).
Identify the primary key for the original table and the primary keys for the resulting tables. Indicate the
foreign keys with italic font. Give meaning names to the resulting tables.
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 columnsin the student_type and student_category tables respectively.- the coursecode and stud_id columns in the registration table are foreign keys in the courseand 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 alocation 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.nameFROM student, student_type, student_categoryWHERE stud_type_id =…
Chapter 8 Solutions
Database Systems: Design, Implementation, & Management
Ch. 8 - What is a cross join? Give an example of its...Ch. 8 - What three join types are included in the outer...Ch. 8 - Using tables named T1 and T2, write a query...Ch. 8 - Prob. 4RQCh. 8 - Prob. 5RQCh. 8 - Prob. 6RQCh. 8 - Prob. 7RQCh. 8 - What does it mean to say that SQL operators are...Ch. 8 - Prob. 9RQCh. 8 - Prob. 10RQ
Ch. 8 - Prob. 11RQCh. 8 - Prob. 12RQCh. 8 - Prob. 13RQCh. 8 - Given the employee information in Question 11,...Ch. 8 - Prob. 15RQCh. 8 - Prob. 16RQCh. 8 - Prob. 17RQCh. 8 - Prob. 18RQCh. 8 - What string function should you use to list the...Ch. 8 - Prob. 20RQCh. 8 - Prob. 21RQCh. 8 - Prob. 22RQCh. 8 - Prob. 23RQCh. 8 - Prob. 24RQCh. 8 - Prob. 1PCh. 8 - Insert the data into the tables you created in...Ch. 8 - Prob. 3PCh. 8 - Prob. 4PCh. 8 - Prob. 5PCh. 8 - Prob. 6PCh. 8 - Prob. 7PCh. 8 - Prob. 8PCh. 8 - Prob. 9PCh. 8 - Modify the CUSTOMER table to include two new...Ch. 8 - Prob. 11PCh. 8 - Prob. 12PCh. 8 - Prob. 13PCh. 8 - Prob. 14PCh. 8 - Prob. 15PCh. 8 - Prob. 16PCh. 8 - Write a trigger to update the customer balance...Ch. 8 - Write a procedure to delete an invoice, giving the...Ch. 8 - Prob. 19PCh. 8 - Prob. 20PCh. 8 - Prob. 21PCh. 8 - Prob. 22PCh. 8 - Prob. 23PCh. 8 - Prob. 24PCh. 8 - Prob. 25PCh. 8 - Prob. 26PCh. 8 - Prob. 27PCh. 8 - Create a trigger named trg_line_total to write the...Ch. 8 - Create a trigger named trg_line_prod that...Ch. 8 - Create a stored procedure named prc_inv_amounts to...Ch. 8 - Create a procedure named prc_cus_balance_update...Ch. 8 - Modify the MODEL table to add the attribute and...Ch. 8 - Prob. 33PCh. 8 - Modify the CHARTER table to add the attributes...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Prob. 40PCh. 8 - Create a trigger named trg_char_hours that...Ch. 8 - Create a trigger named trg_pic_hours that...Ch. 8 - Create a trigger named trg_cust_balance that...Ch. 8 - Alter the DETAILRENTAL table to include a derived...Ch. 8 - Alter the VIDEO table to include an attribute...Ch. 8 - Update the VID_STATUS attribute of the VIDEO table...Ch. 8 - Alter the PRICE table to include an attribute...Ch. 8 - Prob. 48CCh. 8 - Prob. 51C
Knowledge Booster
Similar questions
- Table 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_forwardQ9: You are asked to normalize the following Table into 3rd Normal Form. position salary Hours Worked Staff name No $1500 Tom Daneils Supervisor S0003 Sally Adams Manager S0010 Mary Martin Analyst $1500 Tom Daneils $2250 Robert Chin S0415 Sally Stern S3250 Art Peter S0010 Mary Martin S0415 Sally Stern a. b. C. d. e. Which of the following relations will NOT be part of the solution in the 3nd Normal Form? position Toy staffNo Alien Alien Alien Car Car branch No branch No staffNo Products Table 3500 20 Supervisor 4500 24 Manager 5000 11 Engineer 3000 14 4500 30 Supervisor Analyst 3500 23 Engineer 3000 21 Engine Engine 4500 12 5000 10 Part salary ন HoursWorked branchAddress name branchTelNo Branch Branch No Address B001 B001 B001 B004 8Th road, London, UK 8th road, London, UK 8th road, London, UK 5th road, Leeds, UK Central London, UK Central London, UK Avenue, Manch, UK 5th road, Leeds, UK B002 B002 B003 B004 B003 Avenue, Manch, UK Q10: Based on the following TWO Tables (Products and…arrow_forwardIn relational model terminology, a table is considered as Select one: a. Tuple b. Range c. Relation d. Domainarrow_forward
- Problem 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_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_forwardAssume 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_forward
- The 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_forwardAssume 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_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_forward1. The data type for Borrower_name in Borrowers Table is: A. Short text C. Currency B. Long text D. Yes/No Borrowers Field Name Borrower ID Borrower Name Phone num Membership_activation Data Type AutoNumber Short Text Short Text Yes/Noarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database 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
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning