Some sample exam 1 questions: 1. Briefly define the following terms: Relation Tuple Integrity Constraint Candidate Key Primary Key Foreign Key Entity, Entity set (in an ER model) Relationship, Relationship set (in an ER model) Key constraint (in an ER model) Participation constraint (ER) Weak entity (ER) ISA hierarchy (ER) Equi-Join Natural Join Trigger Relationally Complete Cursor Embedded SQL Dynamic SQL JDBC SQLJ HTML XML DTD 2. A music database contains information about Songs (identified by a unique song id# and the title of the song), Bands (identified by a unique band name and the number of members in the band), and Albums (identified by a unique album id#, the title of the album, the number of songs on the album). Bands sing Songs and produce Albums. Songs are parts of an Album. Draw an ER diagram for each of the situations below: - A Song may be part of more than one Album. - A Song must be part of at least one Album. - A Song appears on one and only one Album. - Each Album must have at least one Song. - A Band may sing more than one version of a Song, but each version must appear on a different Album. 3. For the following relations (keys are shown with _s over the field name): Student Class_Grade Class ___ ___ ___ ___ sname sid age gpa sid cid grade cid cdept cnum -------------------- ------------- -------------- Ann 1 21 3.5 1 1 A- 1 CS 1621 Bob 2 19 3.4 1 2 A 2 BIOL 1011 Cal 3 20 2.6 2 1 B 3 ECE 1315 Dee 4 22 4.0 2 3 C 3 1 A 3 2 C 3 3 F 4 2 A How would you design the following queries in Relational Algebra: - The id#s of students who are 19 or 20. - The student names (sname) of those students who have taken CS 1621. - Students (names) who have taken a biology course or ECE course. - Students (names) who have taken every course in Class. - Students (names) who have received an A. 4. Answer question 3 using Domain Relational Calculus. 5. Answer question 3 using SQL. 5a. How would design these queries in SQL: - What is the average GPA of students by age (e.g., what is the average GPA of students of age 18, 19, etc.) - What is the maximum GPA of each student by age where a result is reported only if there are at least 2 students of that age - For each course indicate the number of As in that course (only for courses where a student got an A) 6. Define the following operations in relational algebra: Selection, Projection, Cross Product, Intersection, Union, Natural Join, Division How would you perform operations similar to these in Domain Relational Calculus? 7. A SQL query may have five different clauses, a SELECT, FROM, WHERE, GROUP BY and HAVING clause. Explain how each of these clauses works and how they are evaluated in an SQL query. Give an example using all five clauses. 8. How is a view defined in SQL? How are queries on views resolved? What are some of the difficulties introduced with views? 9. Define the terms primary key, candidate key, foreign key, and domain constraint. How are these concepts introduced using SQL? Give an example of a relation definition or definitions in SQL that includes all of these concepts. 10. What is a general integrity constraint in SQL? How is such a constraint defined? 11. Discuss two mechanisms that can be used to obtain information in a high-level programming language, such as Java or C++. Make sure to discuss the advantages and disadvantages of each of the mechanisms. 12. How is XML related to HTML? What are the advantages of XML? What is a DTD and how does it relate to XML? Give an example of each of these ideas.