The states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation Select one: O physical data independence NOT NULL constraint O Entity integrity constraints referential integrity constraint As a component of the DBMS, the restores the database to a valid state it was in before a failure has occurred. Select one: O physical data independence ODDL compiler o meta data O backup and recovery manager provides concepts to present a conceptual schema with concise description of the data requirements of the users, and includes detailed descriptions of the entity types, relationships, and relevant constraints. Select one: data redundancy ER model OODBC O key constraint In relational data model, a relation is in if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute in any tuple is a single value from its corresponding domain. Select one: referential integrity constraint NOT NULL constraint first normal form Entity integrity constraints Consider the following relations for a database schema for an electronics store that sells consumable electronics to its customers who each has a unique social security number (Ssn). The store keeps track of stocks of these products as indicated by 'Available_quantity attribute in PRODUCTS table, along with the individual selling price for individual items. A customer can place an order in which one or more products can be purchased from the store. The store then ships the order for delivery to the customer's address as specified in CUSTOMERS table. The ORDERS table lists all orders that have been placed by customers so far, along with the date of when orders were shipped. Also, the attribute Shipped_status' in ORDERS table indicates whether or not an order has been shipped. The ORDER DETAILS table lists all product items ordered in each order placed, along with the total price paid for each product type CUSTOMERS Customer number Ssn, Customer_name, Phone Address, Postal code, Country) ORDERS (Order number Order date, Shipped_date, Shipped_status, Customer_number) PRODUCTS (Product code Product name, Available_quantity, Selling price) ORDER DETAILS Order number. Product code Quantity_ordered, Price) Respond to the related questions that follows with the most appropriate answer: can be a foreign key in the ORDER DETAILS relation 1. The set of attributes 2 Executing the following SQL UPDATE statement would Respond to the related questions that follows with the most appropriate answer: 1. The set of attributes can be a foreign key in the ORDER DETAILS relation 2. Executing the following SQL UPDATE statement would UPDATE PRODUCTS SET Selling price = 200 WHERE Product_code 119980; 3. For each set of attributes given in the table below, determine the most appropriate type of key this set can be in the corresponding relation. For example, the set of attributes (Serial no, Model, Year) can be a superkey for CAR relation. Relation A set of attributes key type CUSTOMERS (Customer number/ Key(minimal superkey) ORDERS (Order number, Shipped_date) ORDER DETAILS (Product code) . ORDER DETAILS (Order number, Product code) CUSTOMERS (Ssh) PRODUCTS (Product code, Selling price Consider the COMPANY relational database given in Figure 1. The result of executing the following DELETE SQL statement would violate DELETE FROM department WHERE dnumber = 5; Select one: O referential integrity constraints with the relations: employee and project only O referential integrity constraints with the relations: employee, dept_locations, and project referential integrity constraint with the relation: employee only O referential integrity constraints with the relations, dept_locations and project only Consider the COMPANY relational database given in Figure 1. The result of executing the following UPDATE SQL statement is a UPDATE employee SET Super 33n = '101119991 WHERE Ssn - 888665555'; 1 Select one: O violation for a NOT NULL constraint successful execution without any constraint violation O violation for an entity integrity constraint violation for a referential integrity constraint Consider the COMPANY relational database given in Figure 1. The result of executing the following INSERT SQL statement is a INSERT INTO works on (essn, pno, hours) VALUES ('888665555', NULL, 40); 7 Select one: O violation for an entity integrity constraint O violation for a referential integrity constraint successful execution without any constraint violation violation for a key constraint Consider the COMPANY relational database given in Figure 1. The result of executing the following UPDATE SQL statement is a ot UPDATE department SET Mgr_3an = NULL WHERE Dnumber = 11 tion Select one: O successful execution without any constraint violation o violation for a key constraint violation for a referential integrity constraint O violation for an entity integrity constraint Consider the COMPANY relational database given in Figure 1. The result of executing the following INSERT SQL statement is a INSERT INTO works on (essn, pno, hours) VALUES ('888665555', NULL, 40); Select one: O violation for an entity integrity constraint violation for a referential integrity constraint successful execution without any constraint violation O violation for a key constraint Consider the COMPANY relational database given in Figure 1. The result of executing the following UPDATE SQL statement is a UPDATE department SET Mgr_ssn NULL WHERE Dnumber = 1; Select one: O successful execution without any constraint violation violation for a key constraint O violation for a referential integrity constraint violation for an entity integrity constraint Consider the COMPANY relational database given in Figure 1. The result of executing the following SQL statement would DROP TABLE works on: Select one: O violate a key constraint O delete the entire works on table successfully O violate an entity integrity constraint violate a referential integrity constraint Consider the COMPANY relational database given in Figure 1, and assume that the employee and works on relations are created with the following CREATE TABLE statements. CREATE TABLE employee fname VARCHAR(15) NOT NULL, minit CHAR, CREATE TABLE works on lname VARCHAR (15) NOT NULL, esan CHAR (9) NOT NULL, son CHAR(9) NOT NULL, pno INT NOT NULL, bdate DATE, address VARCHAR(30), hourg DECIMAL (3, 1) NOT NULL, sex CHAR, salary DECIMAL (10, 2), PRIMARY KEY (esan, pno), super_33n CHAR(9), dno INT NOT NULL, PRIMARY KEY (sun), FOREIGN KEY (ess) REFERENCES employee (san) ON DELETE CASCADE, FOREIGN KEY (super_33n) REFERENCES FOREIGN KEY (pno) REFERENCES project (pnumber) employee (sn) ) FOREIGN KEY (dno) REFERENCES department (dnumber) ): tuples in total in the entire database: Then, executing the following DELETE SQL statement would remove DELETE FROM works on WHERE essn-1999887777; Select one 0 3 2 1 Consider the COMPANY relational database given in Figure 1, and assume that the employee and works on relations are created with the following CREATE TABLE statements. CREATE TABLE employee ( fname VARCHAR(15) NOT NULL, minit CHAR CREATE TABLE works on Iname VARCHAR(15) NOT NULL, esan CHAR (9) NOT NULL, ssn CHAR (9) NOT NULL, pno INT NOT NULL, bdate DATE, address VARCHAR(30), hours DECIMAL (3, 1) NOT NULL, sex CHAR, salary DECIMAL (10, 2), PRIMARY KEY (esan, pno), super_99 CHAR(9), dno INT NOT NULL, FOREIGN KEY (essn) REFERENCES employee (990) ON PRIMARY KEY (son), DELETE CASCADE, FOREIGN KEY (super_ssn) REFERENCES FOREIGN KEY (pno) REFERENCES project (pnumber) employee (son), > FOREIGN KEY (dno) REFERENCES department (dnumber) tuples in total in the entire database: Then, executing the following DELETE SQL statement would remove DELETE FROM works on WHERE essn = '333445555'; Select one: 04 0 0 1 5 Consider the current state of some COMPANY relational database as given in Figure 1 below. The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. The database keep track of the start date when that employee began managing the department. A department may have several locations for its offices. A department may control a number of projects, each of which has a unique name, a unique number, and a single location. The database stores each employee's name, Social Security Number (SSN), address, salary, sex (gender), and the birth date. The social security number (SSN) is a unique number every person must have nation- wide. An employee is assigned to work for one department, but may work on several projects, which are not necessarily controlled by the same department. The database keep track of the current number of hours per week that an employee works on each project. The database also keep track of the direct supervisor of each employee, who is another employee. The database stores the details of the dependents of each employee for insurance purposes. These details include dependent's first name, sex, birth date, and relationship to the employee EMPLOYEE Frame Mat Usame Son Bdute Address Seu Salary Superman Do B Smith 1234567891065-01001 Fordren Houston, TX M30000 333445556 5 Franklin T Wong 339446960 1956-1200030 Voss, Houston, TX M40000 060666666 5 Alicia Zala00000227219600100321 Castle Spring TXF 25000 007654321 Jernier S Walc0076543211941-06-20 201 Berry, Botare, TX F 43000 000066666 Ramesh Narayan 606004444100200 1975 Fire Oak Humble, TX 30000 30445500 Joyce A English 450403453 1972 07 01 5601 R Houston, TX20000333445010 5 Amad V 917967967 1969-00-90080 Di Houston, TX M 2000007654321 4 E Borg 380665666 1937 11.10450 Stone Houston, TX M 50000 NULL 1 5 DEPARTMENT Chante Mesoch Admin Headquarters Onun 6 Mon 333445566 07654321 G66556 Mortart date 10580592 100 101 1991-06-19 6 DEPT LOCATIONS Onumber Dlocation 1 Houston 4 Stafford Bola Sugarlund D Houston . DEPARTMENT Dnumber 5 Dname Research Administration Headquarters Mgr_ssn 333445566 987654321 888666555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 4 DEPT LOCATIONS Dnumber Dlocation 1 Houston 4 Stafford 5 Bellaire 5 Sugarland 5 Houston 1 PROJECT WORKS ON Essn Pno Pnumber Plocation Hours 32.5 Dnum 5 1 1 2 2 5 7.5 40.0 3 Pname ProductX Product Productz Computerization Reorganization Newbenefits 3 6 Bellaire Sugarland Houston Stafford Houston Stafford 1 20.0 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 10 4 2 20 1 20.0 10.0 2 30 4 3 10.0 10 10.0 20 10.0 Sex F 30 10 30.0 10.0 35.0 Relationship Daughter Son M 10 DEPENDENT Essn 333445555 333445555 333445555 987654321 123456789 123456789 123456789 999887777 999887777 987987987 987987987 987654321 987654321 888665555 F Dependent name Alice Theodore Joy Abner Michael Alice Elizabeth Bdate 1986-04-05 1983-10-25 1958-05-03 1942-02-28 1988-01-04 1988-12-30 30 0.0 M 30 20.0 M F Spouse Spouso Son Daughter Spouse 20 15.0 20 NULL F 1967-05-05 Figure 1: COMPANY database state. Consider the entity-Relationship (ER) model for the COMPANY relational database as depicted in Figure 2 below. Fname Minit Lname Bdate Name Address Salary San Sex N Locations WORKS_FOR Name Number EMPLOYEE Start date Number_of_employees DEPARTMENT MANAGES CONTROLS Hours N M N Supervisor Superviseo WORKS ON PROJECT 1 SUPERVISION N Name Location DEPENDENTS OF Number N DEPENDENT Name Sex Birth date Relationship Figure 2: An ER diagram for the COMPANY database