Posts: 366
Name: Steve
Location: Miami, FL, Earth
|
That's not a schema, that's a diagram.
A schema should look like this:
Code:
CREATE TABLE student (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL INDEX,
etc,
etc
);
One thing I notice in your diagram is that none of your tables have auto-numbering identity columns... didn't you learn this in school?
A relational database should work as follows:
- Always use lower_case_underscore naming conventions for databases... this is the universally supported standard between all databases (some don't support upper-case, some don't support spaces, etc.).
- For 3rd normal form, every data type that has a repeatable value should be contained in a unique table. For example, in your design, there should be a table called Semester, with the values 1-Fall, 2-Spring, 3-Summer. That table should then be referenced by semester_id
- Each table has a unique ID (preferably an auto-incremented number - every database has the ability to handle this)
- Relationships are handled as a numeric ID referencing the parent table..., for example the "course" in table "section" should be course_id INT NOT NULL REFERENCES course(id).
- Composite Keys, such as Grade Report, should be a combination of two foreign key relationships to form a unique id, e.g. student_id INT NOT NULL REFERENCES student(id), section_id INT NOT NULL REFERENCES section(id), PRIMARY KEY(student_id, section_id)
__________________
- Steve
President, Please login or register to view this content. Registration is FREE
Last edited by smoseley; 09-19-2010 at 07:26 PM..
|