The school database seems easy but really isn't, especially according to your business rules. There are also some things missing in your initial statement of purpose. Are lectures 9AM to 5PM or just part of that time slot? Do students attend more than one lecture in that period? Do teachers teach more than one lecture in that period? Are lectures repeated? What does "make the attendance cumulative" mean exactly? Does or can more than one teacher conduct the same lecture? The same lecture but at different times? Are lectures tied to a course or to a school of study? Do lectures become obsolete after they are no longer taught or is there a temporal history for everything taught (this question is not as obvious as it looks)? I'm sure I can think of many more.
Your initial basic setup sounds like...
- zero or more students attend zero or more lectures
- a professor gives zero or more lectures
- a lecture is part of one (or more?) classes
- a student is registered for zero or more classes
- a professor is registered to oversee zero or more classes
- a class is managed by one (or more?) professors
- a lecture is part of a time slot
Edit... Okay, I see. Your setup does seem simpler than I thought at first. By the way, cumulative is data inherent (resultant) of the attendance, so it is not something you need to input or manually keep track of.
This is not really the right venue for conversation, though, so if you want some specific help with the SQL and general approach feel free to email me at clistbucket@gmail.com. The reason why I suggest conversation over a one-way answer is because there may be other questions that come up and Y!A is terrible for communicating code.
Edit again... I'll post a basic skeleton here that, hopefully after learning SQL, will benefit you somewhat. You'll need to modify the constraints according to your business rules (time delimiters, are weeks consistent/unique?, are courses unique or just lectures, etc.) and also add pertinent columns such as date of birth and what not. Lastly, you would archive the data every semester or other unit of time whenever the schedule/catalog changes. The constraints/keys seem complicated but are there for a reason. Untested on MySQL...
CREATE TABLE academia (
school char(4) NOT NULL PRIMARY KEY,
school_name varchar NOT NULL UNIQUE
);
CREATE TABLE people (
uid int NOT NULL PRIMARY KEY,
person varchar NOT NULL,
-- the uid should be linked to whatever identification system you use
-- include data specific to all people here
);
CREATE TABLE alumni (
uid int NOT NULL REFERENCES people (uid) PRIMARY KEY
-- info specific only to students here
);
CREATE TABLE faculty (
uid int NOT NULL REFERENCES people (uid) PRIMARY KEY
-- info specific only to professors here
);
CREATE TABLE listing (
course varchar NOT NULL,
school char(4) NOT NULL,
no integer NOT NULL,
PRIMARY KEY (school, no)
-- so a course would be identified as MCEN 1020, for example
-- note, a course is independent of when it is scheduled
);
CREATE TABLE catalog (
cid integer NOT NULL PRIMARY KEY,
school char(4) NOT NULL,
no integer NOT NULL,
offering char(1) NOT NULL,
FOREIGN KEY (school, no) REFERENCES listing (school, no) ON UPDATE CASCADE,
UNIQUE (school, no, offering)
-- a course can be offered at different times and taught be different profs
);
CREATE TABLE schedule (
cid integer NOT NULL,
day integer NOT NULL,
hour integer NOT NULL,
CHECK ((day) BETWEEN 1 AND 5),
check ((hour) BETWEEN 1 and 7)
primary key (cid, day, hour)
-- the actual schedule, we assume every week is the same over the semester
-- the schedule defines what is even possible to attend
);
CREATE TABLE attendance (
uid integer NOT NULL REFERENCES people (uid) ON UPDATE CASCADE ON DELETE CASCADE,
cid integer NOT NULL REFERENCES schedule (cid) ON UPDATE CASCADE ON DELETE CASCADE,
week integer NOT NULL,
day integer NOT NULL,
hour integer NOT NULL,
role varchar NOT NULL DEFAULT 'student',
FOREIGN KEY (cid, day, hour) REFERENCES schedule (cid, day, hour) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (week, day, hour)
-- a lecture/performance must be unique
-- a person can only be at a single lecture at any given time slot (week, day, hour)
-- the role tells in what capacity the person is there (teacher, student, overseer, etc.)
);