7. Databases and JDBC

Activity 1: Track Your Progress

Solution

1. The student table holds information on the student:

   CREATE TABLE IF NOT EXISTS student
   (
   STUDENT_ID long,
   FIRST_NAME varchar(255),
   LAST_NAME varchar(255),
   PRIMARY KEY (STUDENT_ID)
   );

2. The chapter table has a chapter number and a name:

   CREATE TABLE IF NOT EXISTS chapter
   (
   CHAPTER_ID long,
   CHAPTER_NAME varchar(255),
   PRIMARY KEY (CHAPTER_ID)
   );

   Note that the chapter ID is the chapter number.

3. The student_progress table maps a student ID to a chapter ID, indicating that a particular student completed a particular chapter:

   CREATE TABLE IF NOT EXISTS student_progress
   (
   STUDENT_ID long,
   CHAPTER_ID long,
   COMPLETED date,
   PRIMARY KEY (STUDENT_ID, CHAPTER_ID)
   );

   Note that by using both student ID and chapter ID as the composite primary key, each student can complete each chapter just once. There are no do-overs.

4. Here is a hypothetical student:

   INSERT INTO student
   (STUDENT_ID, FIRST_NAME, LAST_NAME)
   VALUES (1, &apos...