[Main Page]      [Literature]      [Software]      [People]      [Conferences]      [Courses]      [Web-DB]

Homework Assignment #6:    ER-Model -> Tables

Reading Assignment

Please read the following sections from the book by Elmasri and Navathe (3rd Edition):

• Section 9.1, "Relational Database Design Using Er-to-Relational Mapping"
• Section 9.2, "Mapping EER Concepts to Relations"
• Chapter 14, "Functional Dependencies and Normalization for Relational Databases"

Exercise 6 (12 Points)

Please translate the schema shown below into the relational model. The schema models the multiple-choice online-tests/quizzes.

• For students, we need the name, social security number, and a password.
• For every test, a number and a general description (a few lines which mention the topics treated in this test)
• Every test belongs to a unique chapter of the course.
• Every test has a set of multiple-choice questions. Questions are identified by their number and the number of the test (e.g. "5.3"). Of course, every question has a text (e.g. some piece of HTML code or plain text).
• For every question, we store a set of possible answers. Answers are numbered within questions (e.g. question "5.3", answer "a"). Of course, answers also have some text. We also must store whether this answer is correct or incorrect.
• A test can have any number of questions and a question can have any number of answers. But a question can belong only to one test (and obviously, an answer can belong only to one question).
• We store which tests a student has taken and when. Every student can take each test only once.
• We also need the information which answers the student has checked. From this we can compute how well the student did the test.

Due Date:

Please submit your solution on Wednesday after the Spring break (March 15) before the lecture.

Stefan Brass (sbrass@sis.pitt.edu), March 1, 2000

Original URL: http://www2.sis.pitt.edu/~sbrass/db00_2/h6_trans.html   [HTML 3.2 Checked]