CS8630 Test. Due October 3, 2005IntersectionIntersectionFor a1 = first row of alumni until a1 = last row of alumniCS8630 Test. Due October 3, 20051) List the Database Models in the order that they appeareda) Relational , Flat Files, Hierarchical and Network, Object and Object-Relationalb) Flat Files, Hierarchical and Network, Object and Object-Relational, Relationalc) Flat Files, Hierarchical and Network, Relational, Object and Object-Relationald) Flat Files, Relational, and Network, Object and Object-Relational2) Which one of the following statements about the Relational Model is NOT true.a) It’s creator was Ted Codd, an IBM Researcher at the timeb) DBA, Application programmers and end users visualize the database as a collection of Relations (Tables) were implementation details such as pointer and next record are hidden from the end-user.c) It was developed in a bottom-up fashion. First a bunch of relational products started appearing in the Market, then Codd came up with the model a generalization of existing products.d) It is the most used type of database today.3) Can we modify the value of a Primary Key ?a) Only when there are no FKs referencing the PK. If there are FKs referencing the PK, we can’t ever delete the PK.b) We can if there are no FKs referencing the PK OR if there are FKs referencingThe PKs, those FKs must be declared with the SET NULL or CASCADE values.c) We can NEVER modify a Primary Keyd) We can Always modify the value of a Primary Key4) Which of the following is NOT true about triggers a) A trigger is a piece of code that is activated whenever an event occursb) Triggers are normally stored inside the database similar to stored procedures and stored functionc) A Trigger differentiates from procedures and functions because a trigger is notexplicitly called.d) Triggers are used to implement complex integrity constraints that can’t be defined with the Create table or Alter table statement.e) A triggering event must always be some modification to a table through a DML command. For example: After Update on Customers, After Insert on Customers.5) What is the difference between a Function and a Procedurea) A function is stored in the database while a procedure is not.b) A procedure is stored in the database while a function is notc) A procedure returns a value and a function does not.d) A function returns a value while a procedure does not.6) Which is NOT true about Anonymous Blocks in PL/SQLa) Unlike a trigger, an Anonymous Blocks must be explicitly calledb) An anonymous block is not compiled and stored inside the Database like atrigger, stored procedure or stored function.c) An anonymous block has three parts (just like any other block), Declarations, Commands and Exceptions. Declarations and Exceptions areoptional blocks.d) To create anonymous block, a programmer must login as an anonymous user.7) Which SQL keyword must be used to remove duplicate rows from the result relation ?a) DELETE b) DISTINCTc) NOT EXISTS d) UNIQUEe) KEY8) SQL keyword used to state the condition that specifies which rows are to be selected ?a) EXISTS b) FROMc) SELECT d) SETe) WHERE f) EXISTS9) SQL keyword used to specify the table (s) to be used ?a) EXISTS b) FROMc) SELECT d) SETe) WHERE10) SQL keyword used to implement candidate keysa) NOT NULL b) CASCADEc) UNIQUE d) REFERENCEe) DISTINCT11) Which keyword is used to determine if a column value is equal to any one of a set of values ?a) AND b) EXISTSc) HAVING d) IN e) OR12) Rollback will undoa) DDL commands onlyb) DML commands onlyc) DDL and DML commandsd) DDL, DML and DCL commands13) Three DDL commands: _______________, _______________, and ____________14) Four DML commands: ____________, _____________, __________ and_________15) What is the command that allows us to add a foreign key after the table is created ?16) Describe what you know about the ON DELETE RESTRICT, ON DELETE CASCADE, ON DELETE SET NULL, ON UPDATE RESTRICT, ON UPDATE CASCADE, ON UPDATE SET NULL clauses. When do you use this clause ? What do they mean ? Give an example and write a command that has one of these clauses. What is the default if you leave out the clause. 17) Departments Employees did dname ssn name did d1 Marketing s1 Mary d1 d2 Accounting s2 John d1 s3 Sue d2(Assume no Alter Table command has been performed)If the Employees table was created with Create Table Employees (ssn char (2), name varchar (8), did char (2), foreign key did references Department);a) What will happen if we issue a Update Departments set did = ‘d7’ Where did = ‘d1’; command ?b) What will happen if we issue a Delete From Departments Where did = ‘d1’; command ?If the Employees table was created with Create Table Employees (ssn char (2), name varchar (8), did char (2), foreign key did references Department on update cascade on delete set null);c) What will happen if we issue a Update Departments set did = ‘d7’ Where did = ‘d1’; command ?d) What will happen if we issue a Delete From Departments Where did = ‘d1’; command ?18) When creating tables with referential integrity (FK on one table is equal to PK on the other one, it is necessary to:a) create the child table firstb) create the parent table firstc) the order is insignificantd) must create both at the same time19) When dropping tables tables with referential integrity (FK on one table is equal to PK on the other one, it is necessary to:a) drop the child table firstb) drop the parent table firstc) the order is insignificantd) must drop both at the same timeFor the CUSTOMER, AGENTS, PRODUCTS, ORDERS Database,IN SQL20) Raise the price of each product in the PRODUCTS table by 10% for all PRODUCTS located in Dallas.21) List the cname and the sum of dollars that each customer ordered IN SQL and (RELATIONAL ALGEBRA or RELATIONAL CALCULUS)22) List the names of all customers that placed an order for product ‘p03’ or product ‘p04’ but did not place any order for product ‘p05’.23) List name of customers that have ordered ‘p02’ and ‘p04’RELATINAL ALGEBRA24) Mapping SQL to Relational Algebra and Pseudo-code (based on coffee)A SQL (B ) Procedural Code (5)Intersection Row 4 Row 5RestrictionProjectionProductJoinIntersection = row 4 of the SQL code and row 5 of Procedural Code for Table below. Complete the table Restriction,