Core Entity Tables – Data Definition Language (DDL)

UniversityDataHub example for Lion State University

CREATE TABLE dim_course (
    course_id UNIQUEIDENTIFIER PRIMARY KEY,
    course_code VARCHAR(50) NOT NULL,
    course_title VARCHAR(255) NOT NULL,
    course_level_type_id INT NOT NULL,
    department_id UNIQUEIDENTIFIER NOT NULL,
    update_date DATETIME NOT NULL DEFAULT GETDATE(),
    FOREIGN KEY (course_level_type_id) REFERENCES dim_course_level_type(course_level_type_id),
    FOREIGN KEY (department_id) REFERENCES dim_department(department_id)
);

CREATE TABLE dim_course_section (
    course_section_id UNIQUEIDENTIFIER PRIMARY KEY,
    course_id UNIQUEIDENTIFIER NOT NULL,
    term_id UNIQUEIDENTIFIER NOT NULL,
    section_number VARCHAR(10),
    update_date DATETIME NOT NULL DEFAULT GETDATE(),
    FOREIGN KEY (course_id) REFERENCES dim_course(course_id),
    FOREIGN KEY (term_id) REFERENCES dim_term(term_id)
);

CREATE TABLE dim_department (
    department_id UNIQUEIDENTIFIER PRIMARY KEY,
    department_name VARCHAR(255) NOT NULL,
    update_date DATETIME NOT NULL DEFAULT GETDATE()
);

CREATE TABLE dim_faculty (
    faculty_id UNIQUEIDENTIFIER PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100),
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(50),
    update_date DATETIME NOT NULL DEFAULT GETDATE()
);

CREATE TABLE dim_major (
    major_id UNIQUEIDENTIFIER PRIMARY KEY,
    major_name VARCHAR(255) NOT NULL,
    program_id UNIQUEIDENTIFIER NOT NULL,
    update_date DATETIME NOT NULL DEFAULT GETDATE(),
    FOREIGN KEY (program_id) REFERENCES dim_program(program_id)
);

CREATE TABLE dim_person (
    person_id UNIQUEIDENTIFIER PRIMARY KEY,
    person_number VARCHAR(50) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100),
    last_name VARCHAR(100) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender_type_id INT NOT NULL,
    ethnicity_type_id INT,
    race_type_id INT,
    active_flag CHAR(1) NOT NULL DEFAULT 'Y',
    update_date DATETIME NOT NULL DEFAULT GETDATE(),
    FOREIGN KEY (gender_type_id) REFERENCES dim_gender_type(gender_type_id),
    FOREIGN KEY (ethnicity_type_id) REFERENCES dim_ethnicity_type(ethnicity_type_id),
    FOREIGN KEY (race_type_id) REFERENCES dim_race_type(race_type_id)
);

CREATE TABLE dim_program (
    program_id UNIQUEIDENTIFIER PRIMARY KEY,
    program_name VARCHAR(255) NOT NULL,
    department_id UNIQUEIDENTIFIER NOT NULL,
    update_date DATETIME NOT NULL DEFAULT GETDATE(),
    FOREIGN KEY (department_id) REFERENCES dim_department(department_id)
);

CREATE TABLE dim_term (
    term_id UNIQUEIDENTIFIER PRIMARY KEY,
    term_name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    update_date DATETIME NOT NULL DEFAULT GETDATE()
);

CREATE TABLE dim_user_account (
    user_account_id UNIQUEIDENTIFIER PRIMARY KEY,
    person_id UNIQUEIDENTIFIER NOT NULL,
    role_type_id INT NOT NULL,
    username VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    active_flag CHAR(1) NOT NULL DEFAULT 'Y',
    update_date DATETIME NOT NULL DEFAULT GETDATE(),
    FOREIGN KEY (person_id) REFERENCES dim_person(person_id),
    FOREIGN KEY (role_type_id) REFERENCES dim_role_type(role_type_id)
);