Registrar's Office Database System

This case study requires students to design and implement the database component of a software system for a college Registrar's Office. The assignment involves creating a full Entity Relationship Model (ERM), designing database tables, and writing SQL code to create, manage, and query data related to courses, sections, students, and instructors. Specific tasks include creating courses and sections, adding students to courses, and writing SQL queries to retrieve data in specific formats as shown in provided screen mockups. The assignment emphasizes practical application of database design principles and SQL programming skills. The completed work must be submitted as a Word document including all code and screenshots via a specified online dropbox.

Case Study
PLEASE READ THE FOLLOWING NOTES CAREFULLY BEFORE YOU START:
• This case study is worth 20% of the course grade, and must be completed in groups of 3 or less
(No exception)
• You must submit a Word document containing all code and screenshot the SLATE drop box on or
before beginning of the second class, Week 13. All email submissions will be given the grade of
zero. All late submissions will also be given a grade of zero
-----------------------------------------------------------------------------------------
In this case study, you and your team members are asked to design and implement the database
component of a software system that could be used by a Registrar's Office of a College. The various
screens of the systems are shown in the screen mockups in the Appendix below.
More specifically, your tasks are:
1. Create a full Entity Relationship Model describing the system
2. Design all the necessary tables where all the relevant data can be efficiently stored
3. Write all the SQL code to support the application
Pay attention to the following requirements:
• A course might be offered in multiple sections (Note that on the mockups, a Section is called a
“Course Offering” – They are just synonyms).
• A course (eg DBAS27198) can be offerred in multiple sections (eg., 1145_24828, 1145_24123,
etc.). Each section is associated with a specific semester (e.g. Fall 2013, Winter 2014), and an
instructor.
• Students are registered with a Section, never with a course.
• You must write the SQL code to create courses, sections, and add students to course.
• You must also write the SQL queries to pull the data from the various tables into the grid
formats shown on the mockup pages. (Please note that you might have to join or sub-query your
tables together to produce the grids exactly as shown.)
Please feel free to approach the instructor and clarify anything that is unclear about this case study.
Remember to treat this case study as a learning challenge and not just a “burden” that you need to get
over with. You will learn a lot of useful skills from this project if you treat it as a learning opportunity.

Have fun!
Appendix: Screen Mockups