MySQL - School Management (Pseudo)

A simple database containing courses, grades, student information, etc.

SQL

CREATE DATABASE AsstTwo;
USE AsstTwo;

CREATE TABLE Biographic
(
    BiographicID        INT         NOT NULL AUTO_INCREMENT,
    FirstName           VARCHAR(30),
    LastName            VARCHAR(40),
    PRIMARY KEY (BiographicID)
);

CREATE TABLE Address
(
    BiographicID        INT,
    Address             VARCHAR(200),
    PostalCode          VARCHAR(6),
    FOREIGN KEY(BiographicID)
    REFERENCES Biographic(BiographicID)
);

CREATE TABLE Courses
(
    BiographicID        INT,
    CourseID            INT,
    YearCompleted       DATE,
    FOREIGN KEY(BiographicID)
    REFERENCES Biographic(BiographicID)
);

CREATE TABLE Grades
(
    BiographicID        INT,
    CourseID            INT,
    Grade               INT,
    FOREIGN KEY(BiographicID)
    REFERENCES Biographic(BiographicID)
);

CREATE TABLE Extracurriculars
(
    BiographicID        INT,
    Description         VARCHAR(250),
    FOREIGN KEY(BiographicID)
    REFERENCES Biographic(BiographicID)
);

CREATE TABLE PostalCodeLookup
(
    PostalCode          VARCHAR(6),
    CityName            VARCHAR(50),
    Province            VARCHAR(50),
    FOREIGN KEY(PostalCode)
    REFERENCES Address(PostalCode)
);

CREATE TABLE CourseCodeLookup
(
    CourseID            INT,
    CourseCode          VARCHAR(6),
    CourseName          VARCHAR(30),
    FOREIGN KEY(CourseID)
    REFERENCES Courses(CourseID)
);

/*
    A Biographic can have one or more addresses, courses, grades, and extracurriculars. 
    There-for, Primary keys are not required for addresses, courses, grades, and 
    extracurriculars; unless a person requests for organization purposes. Each student
    will have their own ID for organization and linking purposes.

    Instead of entering a city and province for a student, a PostalCode Lookup can do
    the work instead of wasting extra bytes on the database/server. This also applies
    to CourseCode Lookup for repeating the course name. Also, the PostalCode field will
    be applied in the Address Table because a student can have multiple addresses
    from different villages/towns/cities/provinces.
*/