Skip to content

Latest commit

 

History

History
366 lines (219 loc) · 10.9 KB

database_documentation.md

File metadata and controls

366 lines (219 loc) · 10.9 KB

Schema documentation

Generated by MySQL Workbench Model Documentation v1.0.0 - Copyright (c) 2015 Hieu Le

Table: certificates

Description:

Courses associated with SIM graduation certificates.

Columns:

Column Data type Attributes Default Description
course_code VARCHAR(45) PRIMARY, Not null Course code name
certificate_id INT PRIMARY, Not null ID number associated with each SIM graduation certificate.
required TINYINT NULL comment here

Indices:

Name Columns Type Description
PRIMARY course_code, certificate_id PRIMARY

Table: competencies

Description:

List of program competencies.

Columns:

Column Data type Attributes Default Description
competency_id SMALLINT PRIMARY, Not null ID number assigned to a competency.
competency VARCHAR(45) NULL Name of competency.
description LONGTEXT NULL Description of competency.

Indices:

Name Columns Type Description
PRIMARY competency_id PRIMARY

Table: course_types

Description:

List of course types.

Columns:

Column Data type Attributes Default Description
coursetype_id VARCHAR(30) PRIMARY, Not null ID number assigned to each course type.
course_type VARCHAR(45) NULL Course type (elective, core, etc.)
type_definition LONGTEXT NULL Course type description.

Indices:

Name Columns Type Description
PRIMARY coursetype_id PRIMARY

Table: courses

Description:

Courses offered at Dalhousie's School of Information Management.

Columns:

Column Data type Attributes Default Description
course_code VARCHAR(45) PRIMARY, Not null Code associated with each course.
course_title VARCHAR(100) NULL Name of course.
coursetype VARCHAR(45) NULL ID number assigned to each course.

foreign key to column coursetype_id on table course_types.
offered VARCHAR(45) NULL Time of year that a course is offered.
frequency VARCHAR(50) NULL How often a course is offered (every year, alternatiing odd years, etc.)
summary LONGTEXT NULL Description of course.
additionalinfo VARCHAR(250) NULL General notes.

Indices:

Name Columns Type Description
PRIMARY course_code PRIMARY
coursetype_id_idx coursetype INDEX

Table: courses_assignments

Description:

List of assignments in each course.

Columns:

Column Data type Attributes Default Description
course_code VARCHAR(45) PRIMARY, Not null Course code name.

foreign key to column course_code on table courses.
assignment_no VARCHAR(10) PRIMARY, Not null Number of assignemnt.
assignment_title VARCHAR(145) NULL Name of assignment.
description LONGTEXT NULL Assignment description.
group VARCHAR(5) NULL Assignment is either group work or individual. 0 = individual, 1 = group
weight VARCHAR(45) NULL How much the assignment is worth towards your course grade.

Indices:

Name Columns Type Description
PRIMARY course_code, assignment_no PRIMARY

Table: courses_certificates

Description:

List of certificates and the courses one can take to obtain them.

Columns:

Column Data type Attributes Default Description
certificate_name VARCHAR(150) PRIMARY, Not null Name of certificate.
course_code VARCHAR(45) PRIMARY, Not null Code name of course associated with a certificate

foreign key to column course_code on table courses.
certificate_requirements LONGTEXT NULL Description of what is required to receive a specific certificate.
additional info LONGTEXT NULL Notes about certificates and certain courses.

Indices:

Name Columns Type Description
PRIMARY certificate_name, course_code PRIMARY
course_code_idx course_code INDEX

Table: courses_competencies_assignments

Description:

Columns:

Column Data type Attributes Default Description
course_code VARCHAR(45) PRIMARY, Not null foreign key to column course_code on table courses.
competency_id SMALLINT PRIMARY, Not null foreign key to column competency_id on table competencies.
assignment VARCHAR(250) PRIMARY, Not null

Indices:

Name Columns Type Description
PRIMARY course_code, competency_id, assignment PRIMARY
competency_id_idx competency_id INDEX
course_code_idx course_code INDEX

Table: courses_crosslist

Description:

List of courses with an alternate code name.

Columns:

Column Data type Attributes Default Description
course_code VARCHAR(45) PRIMARY, Not null Code name for course.

foreign key to column course_code on table courses.
other_course_code VARCHAR(15) PRIMARY, Not null Alternate code name for a course.

Indices:

Name Columns Type Description
PRIMARY course_code, other_course_code PRIMARY

Table: courses_instructor

Description:

List of instructors and the courses they teach.

Columns:

Column Data type Attributes Default Description
instructor_id VARCHAR(10) PRIMARY, Not null ID number assigned to an instructor.

foreign key to column instructor_id on table instructor.
course_code VARCHAR(45) PRIMARY, Not null Course code name.

foreign key to column course_code on table courses.

Indices:

Name Columns Type Description
PRIMARY course_code, instructor_id PRIMARY
instructor_id instructor_id INDEX

Table: courses_learning_objectives

###Description:

List of learning objectives associated with each course.

Columns:

Column Data type Attributes Default Description
course_code VARCHAR(45) PRIMARY, Not null Code name of course.

foreign key to column course_code on table courses.
learning_objectives VARCHAR(250) PRIMARY, Not null Learning objective associated with a course.

Indices:

Name Columns Type Description
PRIMARY course_code, learning_objectives PRIMARY

Table: courses_prerequisites

Description:

List of courses and their pre-requisites courses.

Columns:

Column Data type Attributes Default Description
course_code VARCHAR(45) PRIMARY, Not null Code name for a course.

foreign key to column course_code on table courses.
prerequisite VARCHAR(200) PRIMARY, Not null Previously taken course(s) required to take a specific class.

Indices:

Name Columns Type Description
PRIMARY course_code, prerequisite PRIMARY

##Table: courses_programs

Description:

List of courses and the SIM program they are associated with.

Columns:

Column Data type Attributes Default Description
course_code VARCHAR(45) PRIMARY, Not null Code name of a course.

foreign key to column course_code on table courses.
program_id VARCHAR(45) PRIMARY, Not null Program a course is associated with (ex. MI).

foreign key to column program_id on table programs.

Indices:

Name Columns Type Description
PRIMARY course_code, program_id PRIMARY
courses_programs_ibfk_1 program_id INDEX

Table: courses_technologies

Description:

List of tecnologies each course uses.

Columns:

Column Data type Attributes Default Description
course_code VARCHAR(45) PRIMARY, Not null Code name of course.

foreign key to column course_code on table courses.
technology_id SMALLINT PRIMARY, Not null Technology used in a course.

foreign key to column technology_id on table technologies.

Indices:

Name Columns Type Description
PRIMARY course_code, technology_id PRIMARY
course_code_idx course_code INDEX
technology_id_idx technology_id INDEX

Table: instructor

Description:

List of instructors for SIM courses.

Columns:

Column Data type Attributes Default Description
instructor_id VARCHAR(10) PRIMARY, Not null ID number assigned to an instructor.
first_name VARCHAR(45) NULL Instructor's first name.
last_name VARCHAR(45) NULL Instructor's last name.
status LONGTEXT NULL Instructor status (ex. Lecturer, Adjunct Professor).
email VARCHAR(45) NULL Email address of instructor.

Indices:

Name Columns Type Description
PRIMARY instructor_id PRIMARY

Table: programs

Description:

List of programs offered by Dalhousie's School of Information Management.

Columns:

Column Data type Attributes Default Description
program_id VARCHAR(45) PRIMARY, Not null ID number assigned to a program.
name VARCHAR(45) NULL Name of program.

Indices:

Name Columns Type Description
PRIMARY program_id PRIMARY

Table: technologies

Description:

List of possible technologies used in SIM courses.

Columns:

Column Data type Attributes Default Description
technology_id SMALLINT PRIMARY, Auto increments, Not null ID number assigned to a technology.
technology VARCHAR(255) NULL Name of technology.

Indices:

Name Columns Type Description
PRIMARY technology_id PRIMARY