EXAM Date: Thursday October 11, 2007Sample Problems2007 Fall CS157A Midterm 2 Study GuideProf. Sin-Min LeeEXAM Date: Thursday October 11, 2007(1) 20% First Midterm materials.. (2) 80% of the new topics.Readings: Relational DB, SQL, Functional Dependency Relational Algebra ----1. the language a. select b. project c. join d. the set operations union intersection difference e. renaming f. division 2. RA queries- Primary keys and foreign keys - Integrity rules and enforcement - Relational algebra Functional DependencyArmstrong Axioms,Functional Dependency Graph- The exam will be comprehensive. Definition type questions over the terminology (relational algebra terminology as well . Given a set of relations and some sample data for the relations be able to give the relation that results from a relation algebra expression.) - Given a set of relations and a relational algrebra expression, be able to describe in English what the result is. - Given a set of relations, be able to produce relational algebra expressions that produce some stated result (similar to your homework).Test material will be drawn from the text book, lecture, assignments and any supplementary material provided in class. Go through the reading and the examples in the textbook. Consider the problems at the end of each chapter and work some of them.You should review the following: - all the lecture notes - all the assigned readings Sample Problems 1. The relational model of database management was proposed in 19__ by Dr. E. F. Codd at IBM Research laboratory in San Jose, California. A. 60 B. 70 C. 80 D. 90 E. 95 F. 97 2. Multiple Choice. When we map a multivalued attribute for entity E from the ER model to the relational model, we will create (a) many relations, one for each of the distinct values of the attribute (b) one relation that contains a foreign key and a column for the attribute (c) a column in the relation that represents the entity E (d) none of the above 3. Codd's first rule says that all information in a relational datbase is represented by values in ________ . A. databases B. rows C. columns D. tables 4. Which of the following is not a database system? A. DB2 B. RDB C. INGRES D. SYBASE E. ORACLE F. SQL 5. Tuple is synonymous with ________ A. table B. row C. column 6. If two data objects, A and B, are relate in a one-to-many relationshipa. every A can be related to one more Bsb. every B can be related to one or more As.c. both a and b are true.d. either a or b is true, but not both. none of the above 7. Each row in a table describes one occurrence of an ________ . A. value B. file C. entity D. table E. relation8. The system tables, also known as the system catalog, is also known as the _________ . A. database tables B. user table C. user dictionary D. data dictionary 9. If you find a one-to-one relationship between two tables you should ___. A. not alter this relationship and keep both tables. B. collapse them into one table. C. discard both tables. D. None of the above10. One of the entities in your logical data model has the customer_number column, which does not allow user changes. Where should you enforce this restriction in an SQL Server database? (Choose two.) A. in the CREATE TABLE statement B. in the GRANT statement and the REVOKE statement C. in a rule bound to the customer_number column D. in an update trigger bound to this table11. The relational model requires that one-to-many relationships be represented by means of primary-key-__________ pairings. A. join B. association C. index D. foreign key E. table12. The relational model requires that one-to-many relationships be represented by means of primary-key-__________ pairings. A. join B. association C. index D. foreign key E. table 13. Which of the following is the correct way to enter data into a table. A. insert into publishers data ('0010', 'Pragmatics', '4 4th ln.', 'Chicago', 'IL') go B. insert into publishers entities ('0010', 'Pragmatics', '4 4th ln.', 'Chicago', 'IL') go C. insert into publishers values ('0010', 'Pragmatics', '4 4th ln.', 'Chicago', 'IL') go D. insert into publishers tuples ('0010', 'Pragmatics', '4 4th ln.', 'Chicago', 'IL') go E. None of the above14. "NULL" is a zero or blank. A. TRUE B. FALSE15. Entity integrity reqires that no primary key be allowed to have a ______ value. A. zero B. blank C. negative D. imaginary E. null 16. Designers generally start with lists and then move to sketches of the tables and the relationships among them, called data-structure or ______________ diagrams. A. E-R B. R-E C. Codd D. SE E. Primary-key17.. A complete database design should include planning for primary key/foreign key consistancy or _______ integrity. A. association B. referential C. entity D. transactional18.. Which of the following people invented the relational database model? A. Dr. E.F. Codd B. C.J. Date C. L.T. Data D. Dr. Peter Chan 19. Assume you have the databaseDepartment (dnumber,ssn,,dname, mgrssn, mgrstartdate. Headquarters, Administration) Employee (ssn, dno) Dept_Location (dnum, dlocation)Project (dnum, pnumber, pname, plocation, Product, Reorganization)Employee (ssn, fname, minit, lname, bdate, address, sex, salary, superssn, dno) Works_On (essn, pno, hours)Project (pnumber) and Works_On (pno)Dependent(essn, dependent_name, bdate, sex, relationship,Spouse)Present the following Queries in relational algebra and SQLQ1: select all employee SSNs.Q2: Retrieve the salary of every employee.Q3: Retrieve the names of all employees who do not have supervisors.Q4: Retrieve the birthdate and address of the employee whose name is ‘John B. Smith’.Queries Requiring Table JoinsQ5: Retrieve the SSNs and names of all employees who work on project number 1, 2, or 3.Q6: Retrieve the name and address of all employees who work for the ‘Research’ department.Q7: For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birthdate.Q8: Make a list of all project numbers for projects that involve an
View Full Document
Unlocking...