PracticeProblemsandSolutions - Database Systems: An...

Info iconThis preview shows pages 1–6. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Database Systems: An Application-Oriented Approach (Introductory Version) Practice Problems and Solutions for Students Michael Kifer, Arthur Bernstein, Philip M. Lewis 1 Contents Problems for Chapter 3: The Relational Data Model 3 Problems for Chapter 4: Conceptual Modeling of Databases with E-R and UML 8 Problems for Chapter 5: Relational Algebra and SQL 14 Problems for Chapter 6: Database Design with the Relational Normalization Theory 26 Problems for Chapter 7: Triggers and Active Databases 36 Problems for Chapter 8: Using SQL in an Application 40 Problems for Chapter 9: Physical Data Organization and Indexing 42 Problems for Chapter 10: The Basics of Query Processing 50 Problems for Chapter 11: An Overview of Query Optimization 57 Problems for Chapter 12: Database Tuning 63 Problems for Chapter 13: An Overview of Transaction Processing 64 Problems for Chapter 16: Introduction to Object Databases 67 Problems for Chapter 17: Introduction to XML and Web Data 69 2 Problems for Chapter 3: The Relational Data Model 1. Define the database concepts: primary key, candidate key and superkey. Is a superkey always a key? Explain. Solution: Each relation must have a primary key, which is an attribute or set of attributes used to uniquely identify tuples in the relation. Thus any legal instance of a relation R cannot contain distinct tuples which agree on the value of the primary key but not on the remaining set of attributes. Every tuple in a relation has a unique value for the primary key. Superkey is any set of attributes which satisfies the uniqueness condition Primary key (or candidate key) a minimal superkey; that is, no proper subset of the key is a superkey. primary key one key must be designated as the primary key. 3 2. Answer the following questions: (a) Why are keys important? (b) Identify which ones of the following constraints are (i) structural, (ii) semantic, (iii) static, (iv) dynamic. i. A class start time must be before its end time. ii. A table of students should contain no more than 200 rows. iii. The mileage of a car can not decrease. iv. In teaching assignment table that assigns professors to courses, each individual as- signment should correspond to exactly one professor and one course. Solution: (a) A key uniquely identifies an entity and is minimal. The first is so that one can talk about an entity and others know which one exactly he is talking about. The second is for convenience and e ffi ciency of identifying an entity. (b) i. semantic, static. ii. semantic, static. iii. semantic, dynamic. iv. structural, static. (This is a foreign key constraint.) 4 3. For a simple BBS (Bulletin Board System) we use the following SQL statements to create two tables: one storing all posted messages and the other users who can post them....
View Full Document

This note was uploaded on 11/29/2010 for the course CS 6360 taught by Professor Sunanhan during the Spring '09 term at University of Texas at Dallas, Richardson.

Page1 / 72

PracticeProblemsandSolutions - Database Systems: An...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online