HW$1$ Data Dictionary

Assumptions:

Each student will only have one major.

Each major only belongs to one department.

This project does not care about semesters.

Each course can only be taken once by each student

Each course in the system has been minimally taken by one students

Workday $($WD$\_),$ EngageSC $($EN$\_),$ and Financial Aid $($FA$\_)$ systems include information about all students

Not all the states in the admission $($AD$\_)$ system will be included in OASIS $($OA$\_)$ system.

OA$\_$Student Table

Oasis Transactional System $-$ Students Table: Contains records on every USC students and their major

Column name

Key specifications

Datatype

Short description

Long description

Sample values

USC$\_$ID

Primary key

Integer $($not null$)$

Student Identifier

Unique identifier representing a student instance

$2020001,$

$2020099$

NAME

Varchar $(40)$

Student Name

Student$$s Name

Mike,

Tom

MAJOR$\_$ID

Integer $($not null$)$

Major Identifier

Unique identifier representing a major instance

$1001,$

$2001$

OA$\_$Major Table

Oasis Transactional System $-$ Majors Table: Contains a subset of USC$$s list of majors.

Column name

Key specifications

Datatype

Short description

Long description

Sample values

MAJOR$\_$ID

Primary key

Integer $($not null$)$

Major Identifier

Unique identifier representing a major instance

$1001,$

$2001$

MAJOR$\_$NAME

Varchar $(40)$

Major Name

Full name of the major

Computer Science,

Business Administration

DEPT$\_$ID

Integer $($not null$)$

Department Identifier

Department the major belongs to

FL

TX

OA$\_$Dept Table

Oasis Transactional System $-$ Department Table: Contains a subset of USC$$s list of departments$/$schools

Column name

Key specifications

Datatype

Short description

Long description

Sample values

DEPT$\_$ID

Primary key

Integer $($not null$)$

Department Identifier

Unique identifier representing an academic department instance

$1000,$

$2000$

DEPT$\_$NAME

Varchar $(80)$

Department Name

City where the customer lives.

Viterbi School of Engineering

OA$\_$Grade Table

Oasis Transactional System $-$ Grades Table: Contains records of every student$$s grades for every class they take.

Column name

Key specifications

Datatype

Short description

Long description

Sample values

USC$\_$ID

Primary key

Integer $($not null$)$

Student Identifier

Unique identifier representing a student instance

$2020001,$

$2020099$

COURSE$\_$ID

Primary key

Integer $($not null$)$

Course Identifier

Unique identifier representing a course instance

$320,$

$487$

GRADE

Float $($not null$)$

Grade

Grade of the student in the course

$3\mathrm{.}3,$

$3\mathrm{.}0$

OA$\_$Course Table

Oasis Transactional System $-$ Courses Table: Contains a subset of USC$$s list of courses offered

Column name

Key specifications

Datatype

Short description

Long description

Sample values

COURSE$\_$ID

Primary key

Integer $($not null$)$

Course Identifier

Unique identifier representing a course instance

$320,$

$487$

COURSE$\_$NAME

Varchar $(80)$

Course Name

Full name of the course

Enterprise Information System

FA$\_$Type Table

Financial Aid transactional system$-$ Financial Aid Table: Contains records of everyone student$$s financial aid status

Column name

Key specifications

Datatype

Short description

Long description

Sample values

USC$\_$ID

Primary key

Integer $($not null$)$

Student Identifier

Unique identifier representing a student instance

$2020001,$

$2020099$

FINANCIALAIDTYPE

Char$(1)$

Financial Aid Type

Type of Financial Aid the student receives

G: Grant

W: Workstudy

L: Loan

N: N$/$A

G$,$

W$,$

L$,$

N

EN$\_$CampusInvolvement Table

EngageSC transactional system$-$ involvement table: contains records of whether or not a student is part of a campus organization

Column name

Key specifications

Datatype

Short description

Long description

Sample values

USC$\_$ID

Primary key

Integer $($not null$)$

Student Identifier

Unique identifier representing a student instance

$2020001,$

$2020099$

INVOLVED

Char$(1)$

Campus Involved

Indicator of whether the student is involved in any campus organization

Y: Yes

N: No

Y$,$

N

WD$\_$CampusJob Table

Workday transactional system $-$ job table: contains records of whether or not every student is working on campus

Column name

Key specifications

Datatype

Short description

Long description

Sample values

USC$\_$ID

Primary key

Integer $($not null$)$

Student Identifier

Unique identifier representing a student instance

$2020001,$

$2020099$

JOB

Char$(1)$

Campus Job

Indicator of whether the student is working for a campus job

Y: Yes

N: No

Y$,$

N

AD$\_$Lookup Table

Admissions transactional system $-$ contains a subset of all admitted applicants and their respective, assigned USC IDs

Column name

Key specifications

Datatype

Short description

Long description

Sample values

APPLICANT$\_$ID

Primary key

Integer $($not null$)$

Applicant Identifier

Unique identifier representing a USC applicant instance

$200001,$

$200099$

USC$\_$ID

Integer $($not null$)$

Student Identifier