MySQL - School Management (Final)

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

SQL

CREATE TABLE account
(
    account_id      int(12) AUTO_INCREMENT,
    biographic_id   int(12),
    username        varchar(50),
    password        varchar(50),
    role            int(2),
    PRIMARY KEY (account_id)
);

CREATE TABLE biographic
(
    biographic_id   int(12) AUTO_INCREMENT,
    first_name      varchar(30),
    last_name       varchar(30),
    email           varchar(120),
    contact_number  int(11),
    PRIMARY KEY (biographic_id),
    FOREIGN KEY(biographic_id)
    REFERENCES account(biographic_id)
);

CREATE TABLE address
(
    biographic_id   int(12),
    address         varchar(100),
    postal_code     varchar(6),
    FOREIGN KEY(biographic_id)
    REFERENCES student(biographic_id)
);

CREATE TABLE courses
(
    biographic_id   int(12),
    course_id       int(12),
    year_completed  int(4),
    grade           int(3),
    FOREIGN KEY(biographic_id)
    REFERENCES student(biographic_id),
    FOREIGN KEY(course_id)
    REFERENCES courses_lookup(course_id)
);

CREATE TABLE extra
(
    biographic_id   int(12),
    description     varchar(200),
    FOREIGN KEY(biographic_id)
    REFERENCES student(biographic_id)
);

CREATE TABLE courses_lookup
(
    course_id       int(12)     AUTO_INCREMENT,
    course_code     varchar(10),
    term            int(2),
    course_name     varchar(30),
    PRIMARY KEY (course_id)
);

INSERT INTO biographic (first_name, last_name, email, contact_number) VALUES
("Steven","Bartsch","EMAIL","PHONE");

INSERT INTO courses_lookup (course_code, term, course_name) VALUES
("COMP 203",1,"Introduction to Networking"),
("COMP 205",1,"Web Development"),
("COMP 1100",1,"Introduction to C++"),
("COMP 1111",1,"Introduction to Databases"),
("COMP 30",2,"PC Hardware & Software"),
("COMP 69",2,"MySQL"),
("COMP 211",2,"Program Design"),
("COMP 1101",2,"Visual Basic.Net Programming I"),
("COMP 1200",2,"C++ Programming"),
("COMP 210",3,"Introduction to COBOL"),
("COMP 206",3,"Enterprise Web Development"),
("COMP 333",3,"Object Oriented C++ Programming"),
("COMP 1081",3,"Systems and Mainframe Introduction"),
("COMP 1102",3,"Visual Basic.Net Programming"),
("COMP 32",4,"Game Programming using Flash"),
("COMP 52",4,"JCL & Utilities"),
("COMP 220",4,"Client Server"),
("COMP 251",4,"Advanced COBOL Programming"),
("COMP 305",4,"JAVA Programming I");