Table TD_STUDENT in DWST@WHSE.ISC-AIT.UPENN.EDU

Tables

This table is loaded from dwadmin.person_all_v where the student has registered for at least one course section during a term. There should a unique record for this student in this table. Uniqueness will be determined by student_sid and begin_eff_date. If a new value for student name, student_sid or student_pennid is extracted by SRS, then the current record should be updated by posting the sysdate in the end_eff_date, the current flag set to 'H' and a new record inserted.
Col #	Column Name	Data Type	Not Null?	Data Def.	Comments
1	STUDENT_KEY	INTEGER	Y		Primary key for this table.
2	STUDENT_SID	VARCHAR2(9 BYTE)	Y		SSN for the student.
3	STUDENT_NAME	VARCHAR2(35 BYTE)	Y		Name of the student.
4	STUDENT_PENNID	VARCHAR2(8 BYTE)			PennId of the student.
5	BEGIN_EFF_DATE	DATE	Y	sysdate	Begin Eff Date is populated when a new row is added.
6	END_EFF_DATE	DATE			End Eff Date is populated only when a newer version of this dimension record is added. The default is null.
7	CURRENT_RECORD	VARCHAR2(1 BYTE)	Y	'T'	Current Record is a T(rue)/ F(alse) Flag, indicating which of the dimension records are current. This is used for all lookups when loading the fact table.

**Indexes on TD_STUDENT**
Uniqueness	Index Name	Columns
NON-UNIQUE	TD_STUDENT_CURR_IDX	CURRENT_RECORD
UNIQUE	SYS_C00511921	STUDENT_KEY
NON-UNIQUE	TD_STUDENT_SID_IDX	STUDENT_SID
NON-UNIQUE	TD_STUDENT_PENN_ID_IDX	STUDENT_PENNID
NON-UNIQUE	TD_STUDENT_I	STUDENT_SID, STUDENT_NAME, STUDENT_PENNID
NON-UNIQUE	TD_STUDENT_DATES_IDX	BEGIN_EFF_DATE, END_EFF_DATE

Triggers on TD_STUDENT

Trigger Name

Status

Triggering Event

When Clause

Trigger Type

Body

TD_STUDENT_PK_TRIG

ENABLED

INSERT

BEFORE EACH ROW

BEGIN

   SELECT TD_STUDENT_PK_SEQ.NEXTVAL INTO :NEW.STUDENT_KEY FROM dual;
   
   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END ;

**PK, UK, & Check Constraints on TD_STUDENT**
Name	Type	Condition	Status	Columns
SYS_C00511921	Primary Key		ENABLED	STUDENT_KEY

**Granted Privileges on TD_STUDENT**
Column	Grantee	Grantable Privilege(s)	Non-Grantable Privilege(s)
	ESTEMMLE		SELECT
	DWADMIN		ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK
	YALINGX		SELECT
	DWST_SUPPORT		SELECT
	TUITION_DIST_RL		SELECT

--------- TD_STUDENT does not reference any objects ---------

**Objects which reference table TD_STUDENT**
Object Owner	Object Name	Object Type
DWST	TD_DISTRIBUTION_FACT	Table (FK)
DWST	TD_STUDENT_REGISTRATION_FACT	Table (FK)
DWST	TD_STUDENT_PK_TRIG	Trigger