Relationship Tables – Data Definition Language (DDL)

UniversityDataHub example for Lion State University

CREATE TABLE dim_address (
    address_id UNIQUEIDENTIFIER PRIMARY KEY,
    person_id UNIQUEIDENTIFIER NOT NULL,
    address_type_id INT NOT NULL,
    address_line1 VARCHAR(255),
    address_line2 VARCHAR(255),
    city VARCHAR(100),
    state_province_id INT,
    country_id INT,
    postal_code VARCHAR(20),
    FOREIGN KEY (person_id) REFERENCES dim_person(person_id),
    FOREIGN KEY (address_type_id) REFERENCES dim_address_type(address_type_id),
    FOREIGN KEY (state_province_id) REFERENCES dim_state_province(state_province_id),
    FOREIGN KEY (country_id) REFERENCES dim_country(country_id)
);

CREATE TABLE dim_country (
    country_id INT PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL,
    country_code VARCHAR(10)
);

CREATE TABLE dim_course_prerequisite (
    course_id UNIQUEIDENTIFIER NOT NULL,
    prerequisite_course_id UNIQUEIDENTIFIER NOT NULL,
    PRIMARY KEY (course_id, prerequisite_course_id),
    FOREIGN KEY (course_id) REFERENCES dim_course(course_id),
    FOREIGN KEY (prerequisite_course_id) REFERENCES dim_course(course_id)
);

CREATE TABLE dim_email (
    email_id UNIQUEIDENTIFIER PRIMARY KEY,
    person_id UNIQUEIDENTIFIER NOT NULL,
    email_address VARCHAR(255) NOT NULL,
    contact_type_id INT NOT NULL,
    FOREIGN KEY (person_id) REFERENCES dim_person(person_id),
    FOREIGN KEY (contact_type_id) REFERENCES dim_contact_type(contact_type_id)
);

CREATE TABLE dim_enrollment (
    enrollment_id UNIQUEIDENTIFIER PRIMARY KEY,
    person_id UNIQUEIDENTIFIER NOT NULL,
    course_section_id UNIQUEIDENTIFIER NOT NULL,
    enrollment_status_type_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    FOREIGN KEY (person_id) REFERENCES dim_person(person_id),
    FOREIGN KEY (course_section_id) REFERENCES dim_course_section(course_section_id),
    FOREIGN KEY (enrollment_status_type_id) REFERENCES dim_enrollment_status_type(enrollment_status_type_id)
);

CREATE TABLE dim_faculty_assignment (
    faculty_assignment_id UNIQUEIDENTIFIER PRIMARY KEY,
    faculty_id UNIQUEIDENTIFIER NOT NULL,
    course_section_id UNIQUEIDENTIFIER NOT NULL,
    FOREIGN KEY (faculty_id) REFERENCES dim_faculty(faculty_id),
    FOREIGN KEY (course_section_id) REFERENCES dim_course_section(course_section_id)
);

CREATE TABLE dim_phone (
    phone_id UNIQUEIDENTIFIER PRIMARY KEY,
    person_id UNIQUEIDENTIFIER NOT NULL,
    phone_number VARCHAR(50) NOT NULL,
    contact_type_id INT NOT NULL,
    FOREIGN KEY (person_id) REFERENCES dim_person(person_id),
    FOREIGN KEY (contact_type_id) REFERENCES dim_contact_type(contact_type_id)
);

CREATE TABLE dim_state_province (
    state_province_id INT PRIMARY KEY,
    state_province_name VARCHAR(100) NOT NULL,
    state_province_code VARCHAR(10)
);