CS 115 Database Table Design Lab

Objectives

Design relational database tables without update anomalies.
Identify appropriate data types for fields.
Identify fields that will make up an appropriate primary key.
Table design must support queries across table.

Relational Database: Stores data in tables related by primary keys.
Primary Key: A field or combination of fields in a relational database table that uniquely identifies a record.
Foreign Key: An attribute defined in one table that is defined to be a primary key in another table that is used for queries across tables.
Update Anomaly: Update anomalies occur when data is duplicated in multiple tables. If the duplicated data changes, multiple tables must be updated.

Reference

Refer to

While you do not actually have to create the tables in Open office, please refer to the lab where you did the table creation for details regarding what must be specified to design a table. Open Office lab.
Pages 497-503 of your textbook on Designing a Relational Database.Note: This is in the hard copy of the text that I have. The electronic version of the text may have slightly different page numbers. You should be able to find it.

To Do

You are going to design three tables that can be used to track/report students' progress through college. The design includes appropriate field definitions.

Student Personal Table

Student personal information (e.g. name, address, etc.).

Student Progress Table

Student/course completion information. This should contain all of the information about courses completed (successfully/unsuccessfully), when taken (e.g. semester year), etc.

Course Table

Course information (course number, title, and credit hours).

Table Design

Tables must be designed so that update anomalies do not exist. Update anomalies exist when data exists in multiple tables. If data is duplicated in multiple tables, multiple tables must be updated when changes to data occur.

The table design must support several different queries. Some queries will be to a single table only while others will be across the two tables.

You do not have to write these queries. Be sure your table design will support them.

Single-table queries supported:

Student(s) and personal information
Students course records with semester, year, and grade
List of all classes offered: course identification, title, and number of credit hours

Queries across tables

For a given student: name, address, all courses, semester, year, grade, course identification, title, and credit hours attempted.
For a given student: name, address, all courses, semester, year, grade, course identification, title, and credit hours successfully completed.
For a given student: name, address, all courses, semester, year, grade, course identification, title, and credit yours unsuccessfully completed.
For a given course, a list of students who completed it.

For each table described above

You are going to write a report that defines the tables above. For each table listed above:

List the fields that should be defined in each table that will support the queries listed.
For each field, define the data type.
Identify the field or combination of fields that should be the primary key. Remember, the primary key field uniquely identifies each record in the table.

Submit your completed report file to D2L

This page is maintained by Barbara Bracken Last Modified

5/15/2025