The basic goal:
Track EVERYTHING you might need for a college/university as efficiently as possible...without duplication, inconsistencies, oddball relationships, or loss of archive/history as updates occur.
Considerations:
You are capturing students, teachers, schedules, grades, transcripts, registrations, finances, room assignments, building facilities & assets, payroll, non-teacher employees (from deans to janitors), popular/required class offered 3-times in same semester (may be diff teacher & room), secretary who is also a student, teacher who also takes classes, prevent conflict scheduling two classes in same room with overlapping times. List all faculty & titles, or show just professors by department. Some classes have multiple pre-requisites before registration allowed, but prof or dean can over-ride, many people have offices as well as classrooms, employees belong to departments & hierchies, which ones are salaried vs. hourly. Which students haven't paid tuition, book fees, or meal plan? What dorm room is a student living in? Is that billed differently w/o roommate? Which students have financial aide, scholarships, grants? Has aide been used as payment for tuition? How is class registration used for tuition billing? Is Tuition different for freshmen, grad-students, alumni, foreigners, elderly, faculty? Students often have multiple mailing addresses and phones and emails (ie: parents house=default+long,term... dorm room=temp+expire... off-campus apartment & summer_job=temp, but current) Billing address may be different than for notices & grades. What is the list of all buildings on campus, and their rooms (class or not), with scheduling. Is there an alternate classroom available at 2:pm in building MMM that will hold 80+ students (room 326 has a water leak). Show me all of the students who are within one semester of graduating...( How did you know they've met required courses, and what if course EEE is not offered this/next semester? )
Of course, you can create tables & indexes & constraints for this data.
( re-think your table-relationships a few times & "normalize" )
You will also want to generate reports for most of the comments above.
How is THAT for an example application you can use to develop some more skills with database design ?!?!?
And YES, I did actually use this example to teach people how to use SQL & database design, using Oracle databases.
PS: We still kept everything below 25 tables...
The core student/teacher/class/scheduling/building/transcript/look-up information was accomplished in only NINE (well crafted) tables.
Billing & finance took most of the rest.
Lookup+Ref info can merge into one multi-topic dynamic table. Views can help.
|
