Consider the activity models and answer the questions provided. First reflect on these questions on your own briefly, before discussing and comparing your thoughts with your group. Appoint one member of your group to discuss your findings with the class, and the rest of the group should help that member prepare their response. Answer each question individually from the activity on the Class Activity Questions discussion board. After class, think about the questions in the reflective prompt and respond to those individually in your notebook. Report out on areas of disagreement or items for which you and your group identified alternative approaches. Write down and report out questions you encountered along the way for group discussion.
Model 1: Tables
FirstName
LastName
Age
Alex
Smith
20
Lee
Jones
21
Brian
McMullen
18
Samantha
Johnson
22
Lee
Jones
24
CourseNum
CourseName
CS377
Database Design
CS173
Intro to Computer Science
CS174
Object Oriented Programming
CS275
Software Engineering
Questions
How might you compare database tables and records, with software classes and objects?
How can you differentiate between the two students named Lee?
Not all cultures use "first" and "last" names; what might be a better choice for these column names?
Model 2: Primary and Foreign Keys
FirstName
LastName
Age
Course
Alex
Smith
20
CS377 Database Design
Lee
Jones
21
CS377 Database Design
Brian
McMullen
18
CS173 Intro to Computer Science
Samantha
Johnson
22
CS174 Object Oriented Programming
Lee
Jones
24
CS174 Object Oriented Programming
Lee
Jones
21
CS275 Software Engineering
Questions
What is unfortunate about the structure of these tables? What could we do to improve upon it?
Modify the tables above to add a "serial number" or a primary key to each of the tables.
Can you think of a way to identify courses without having to number them? What might a more "natural key" be?
What disadvantage could arise from using the data itself (like "CS377") as a key, particularly if that data changes over time?
Eliminate the redundant data in these tables by replacing any redundancies with a new column and row specifying the appropriate key value that can be looked up in the corresponding table. This is called a foreign key.
Model 3: Aggregation
FirstName
LastName
Age
Course
Alex
Smith
20
CS377 Database Design
Lee
Jones
21
CS377 Database Design
Brian
McMullen
18
CS173 Intro to Computer Science
Samantha
Johnson
22
CS174 Object Oriented Programming
Lee
Jones
24
CS174 Object Oriented Programming
Lee
Jones
21
CS275 Software Engineering
Questions
Using your new table structure, describe an algorithm to determine the average age of students enrolled in CS377.
What other types of aggregation functions might be useful across these tables?
Extend the course database to show enrollments in a particular semester; once the class agrees on a design, draw out the tables and data including the key values.
Submission
Submit your answers to the questions using the Class Activity Questions discussion board. You may also respond to questions or comments made by others, or ask follow-up questions there. Answer any reflective prompt questions in the Reflective Journal section of your OneNote Classroom personal section.