/*
--  CopyRight(C) 2003，NTT All rights reserved
--
--  XMS2データベーステーブルの作成
--
--  スクリプト概要
--
--      XMS2データベースのテーブルを新規に作成します
--
--  変更履歴
--    2004.01.08 新規作成 haga
--               XMS2データベースの表領域にテーブルを新規作成します。
--    2004.02.01 修正     野沢智也
--               SCORM LO 追加に伴うテーブル追加
*/

CREATE TABLE PropagateRelation (
	LOID char (10) NULL ,
	RelatedID char (10) NULL
);

CREATE TABLE AiccComment (
	UserID char (8) NOT NULL ,
	LOID char (10) NOT NULL ,
	SessionID char (128) NOT NULL ,
	"UID" char (255) NOT NULL ,
	course_id varchar2 (255) NULL ,
	student_id char (10) NULL ,
	lesson_id varchar2 (255) NULL ,
	"DATE" date NULL ,
	time char (16) NULL ,
	location varchar2 (255) NULL ,
	"COMMENT" LONG NULL
);

CREATE TABLE AiccCore (
	UserID char (8) NOT NULL ,
	LOID char (10) NOT NULL ,
	SessionID char (128) NOT NULL ,
	"UID" char (255) NOT NULL ,
	Lesson_Status varchar2 (30) NULL ,
	RawScore float NULL ,
	MaxScore float NULL ,
	MinScore float NULL ,
	Time char (16) NULL ,
	Lesson_Location varchar2 (255) NULL ,
	exist char (10) NULL
);

CREATE TABLE SectionLog (
	UserID char (8) NOT NULL ,
	LOID char (10) NOT NULL ,
	SessionID char (128) NOT NULL ,
	"UID" char (255) NOT NULL ,
	Lesson_Status varchar2 (30) NULL ,
	Time char (16) NULL
);


CREATE TABLE AiccInteraction (
	UserID char (8) NOT NULL ,
	LOID char (10) NOT NULL ,
	SessionID char (128) NOT NULL ,
	"UID" char (255) NOT NULL ,
	course_id varchar2 (255) NULL ,
	student_id char (10) NULL ,
	lesson_id varchar2 (255) NULL ,
	response_times int NOT NULL ,
	"DATE" date NULL ,
	time char (16) NULL ,
	interaction_id varchar2 (255) NULL ,
	objective_id varchar2 (255) NULL ,
	type_interaction char (30) NULL ,
	correct_response varchar2 (4000) NULL ,
	response_value char (30) NULL ,
	student_response varchar2 (255) NULL ,
	result char (30) NULL ,
	weighting float NULL ,
	latency char (16) NULL
);


CREATE TABLE AiccObjectives (
	UserID char (8) NOT NULL ,
	LOID char (10) NOT NULL ,
	SessionID char (128) NOT NULL ,
	"UID" char (255) NOT NULL ,
	course_id varchar2 (255) NULL ,
	student_id char (10) NULL ,
	lesson_id varchar2 (255) NULL ,
	"DATE" date NULL ,
	time char (16) NULL ,
	objective_id varchar2 (255) NULL ,
	RawScore float NULL ,
	MaxScore float NULL ,
	MinScore float NULL ,
	status char (30) NULL ,
	mastery_time char (16) NULL
);


CREATE TABLE Assignments (
	assignID char (10) NOT NULL ,
	memberShipID char (30) NULL ,
	materialID char (20) NULL ,
	study_start_date date NULL ,
	enddate date NULL ,
	assigner char (15) NULL ,
	learn_authority char (1) NULL ,
	management_authority char (1) NULL ,
	opening_date date NULL ,
	closing_date date NULL ,
	study_limit_cnt int NULL ,
	study_cnt int NULL 
);


CREATE TABLE ConferenceLo (
	LOID char (10) NOT NULL ,
	UrlPath varchar2 (256) NOT NULL ,
	MeetingTitle varchar2 (64) NOT NULL ,
	StartDate date NOT NULL ,
	EndDate date NOT NULL ,
	ResourceMail varchar2 (128) NOT NULL ,
	LogonUser varchar2 (128) NOT NULL ,
	w_height NUMBER(4) NOT NULL ,
	w_width NUMBER(4) NOT NULL
);


CREATE TABLE Evaluate (
	user_id char (8) NOT NULL ,
	LOID char (10) NOT NULL ,
	score_date date NOT NULL ,
	score int NULL ,
	custom_msg_dir varchar2 (256) NULL
);


CREATE TABLE ExchangeLo (
	LOID char (10) NOT NULL ,
	UrlPath varchar2 (256) NOT NULL ,
	w_height NUMBER(4) NOT NULL ,
	w_width NUMBER(4) NOT NULL
);


CREATE TABLE Face2FaceLo (
	LOID char (10) NOT NULL ,
	TeacherID char (16) NULL
);


CREATE TABLE GroupAndPeople (
	memberShipID char (30) NOT NULL ,
	sort_no int DEFAULT 0 NOT NULL ,
	parentID char (15) NOT NULL ,
	childID char (15) NOT NULL
);


CREATE TABLE Groups (
	group_id char (8) NOT NULL ,
	group_name varchar2 (64) NULL ,
	group_type char (4) NOT NULL ,
	delete_flag char (1) NULL ,
	foreignID varchar2 (256) NULL ,
	custom_msg_dir varchar2 (128) NULL ,
	oid varchar2 (256) NULL ,
	ad_dn varchar2 (256) NULL
);


CREATE TABLE LO (
	LOID char (10) NOT NULL ,
	LOType char (3) NULL ,
	LOTitle varchar2 (64) NULL ,
	Loexplanation varchar2 (256) NULL ,
	foreignID varchar2 (256) NULL ,
	KeyWord varchar2 (256) NULL ,
	delete_flag char (1) NULL ,
	LOVersion varchar2 (10) NULL ,
	conclusion varchar2 (1024) NULL ,
	RealLOID char (10) NOT NULL ,
	Prere varchar2 (1024) NULL,
	MentorEmailAddress varchar2 (256) NULL
);


CREATE TABLE LOLog (
	UserID char (8) NOT NULL ,
	LOID char (10) NOT NULL ,
	study_start_date date NULL ,
	residanceTime float NULL ,
	passday date NULL ,
	study_end_date date NULL ,
	highScore float NULL ,
	lowsScore float NULL ,
	execise_point float NULL ,
	fullscore float NULL,
	Access_cnt int NULL,
	last_access date NULL
);


CREATE TABLE LOLogSession (
	UserID char (8) NOT NULL ,
	LOID char (10) NOT NULL ,
	SessionID char (128) NOT NULL ,
	study_start_date date NULL ,
	residanceTime float NULL ,
	passday date NULL ,
	study_end_date date NULL ,
	highScore float NULL ,
	lowsScore float NULL ,
	execise_point float NULL ,
	fullscore float NULL,
	Lunchtimes int DEFAULT 0
);


CREATE TABLE LORelation (
	materialID char (20) NOT NULL ,
	sort_no int DEFAULT 0 NOT NULL ,
	parentID char (10) NULL ,
	childID char (10) NULL
);


CREATE TABLE LOType (
	LOType char (3) NOT NULL ,
	TypeName varchar2 (64) NULL ,
	LoSubType char (3) NULL
);


CREATE TABLE OfficeLo (
	LOID char (10) NOT NULL ,
	LOUrl varchar2 (256) NULL ,
	w_height NUMBER(4) NOT NULL ,
	w_width NUMBER(4) NOT NULL
);


CREATE TABLE People (
	user_id char (8) NOT NULL ,
	user_name varchar2 (64) NULL ,
	encrypt_password char (32) NULL ,
	contact_address varchar2 (256) NULL ,
	sex char (1) NULL ,
	contact_email varchar2 (128) NULL ,
	contact_email2 varchar2 (128) NULL ,
	reading_name varchar2 (64) NULL ,
	birthday date NULL ,
	contact_tel char (13) NULL ,
	custom_msg_dir varchar2 (128) NULL ,
	delete_flag char (1) NULL ,
	foreignID varchar2 (256) NULL ,
	loginID varchar2 (16) NULL ,
	ad_user_id varchar2 (255) NULL ,
	user_type char (4) NOT NULL ,
	oid varchar2 (256) NULL ,
	ad_dn varchar2 (256) NULL
);


CREATE TABLE RoleAssignments (
	Role_assign_id char (15) NOT NULL ,
	user_id char (8) NULL ,
	Role_id char (10) NULL ,
	GroupID char (8) NULL
);


CREATE TABLE Roles (
	RoleID char (10) NOT NULL ,
	RoleName varchar2 (64) NULL ,
	Deleted char (1) NULL
);


CREATE TABLE XmoRelation (
	xmo_id char (15) NOT NULL ,
	related_id char (15) NOT NULL
);


CREATE TABLE RolesFunction (
	RoleID char (10) NOT NULL ,
	Function char (15) NOT NULL
);


CREATE TABLE SPSLo (
	LOID char (10) NOT NULL ,
	UrlPath varchar2 (256) NOT NULL ,
	w_height NUMBER(4) NOT NULL ,
	w_width NUMBER(4) NOT NULL
);


CREATE TABLE ServerInformation (
	server_id char (8) NOT NULL ,
	server_name varchar2 (128) NULL ,
	ip_address char (15) NULL ,
	os_kind char (1) NULL ,
	server_kind char (1) NULL ,
	access_account varchar2 (32) NULL ,
	access_password varchar2 (32) NULL ,
	proxy_server_name varchar2 (32) NULL ,
	access_timeout int NULL ,
	http_port_number int NULL ,
	ftp_port_number int NULL ,
	protocol_kind int NULL ,
	access_type int NULL ,
	url_host_name varchar2 (32) NULL ,
	virtual_dir_name varchar2 (128) NULL ,
	W3S_interface char (1) NULL
);


CREATE TABLE LaunchForgetLo (
	LOID char (10) NOT NULL ,
	LOUrl varchar2 (256) NOT NULL ,
	w_height NUMBER(4) NOT NULL ,
	w_width NUMBER(4) NOT NULL
);


CREATE TABLE SmartForceLo (
	LOID char (10) NOT NULL ,
	CourseCode varchar2 (255) NOT NULL
);


CREATE TABLE URLLo (
	LOID char (10) NOT NULL ,
	LOUrl varchar2 (256) NOT NULL ,
	w_height NUMBER(4) NOT NULL ,
	w_width NUMBER(4) NOT NULL
);


CREATE TABLE WbtLo (
	LOID char (10) NOT NULL ,
	materialID varchar2 (32) NOT NULL ,
	w_height NUMBER(4) NOT NULL ,
	w_width NUMBER(4) NOT NULL
);


CREATE TABLE XMOEntry (
	xmo_id char (15) NOT NULL ,
	xmo_name varchar2 (64) NULL ,
	xmo_out_line varchar2 (100) NULL ,
	xmo_uri varchar2 (100) NULL ,
	xmo_on_flg char (1) DEFAULT '1' NULL ,
	class_name varchar2 (256) NULL ,
	tree_view_type char (1) NULL ,
	ini_menu varchar2 (64) NULL ,
	priority int NULL
);


CREATE TABLE PerceptionLo (
	LOID char (10) NOT NULL ,
	assessment_id varchar2 (16) NOT NULL ,
	session_name char (50) NOT NULL ,
	w_height NUMBER(4) NOT NULL ,
	w_width NUMBER(4) NOT NULL
);


CREATE TABLE LOHistory (
	LOID char (10) NOT NULL ,
	time_stamp date NOT NULL ,
	UserID char (8) NOT NULL
);


CREATE TABLE OL_AiccComment (
	UserID char (8) NOT NULL ,
	LOID char (10) NOT NULL ,
	SessionID char (128) NOT NULL ,
	"UID" char (255) NOT NULL ,
	course_id varchar2 (255) NULL ,
	student_id char (10) NULL ,
	lesson_id varchar2 (255) NULL ,
	"DATE" date NULL ,
	time char (16) NULL ,
	location varchar2 (255) NULL ,
	"COMMENT" LONG NULL
);


CREATE TABLE OL_AiccCore (
	UserID char (8) NOT NULL ,
	LOID char (10) NOT NULL ,
	SessionID char (128) NOT NULL ,
	"UID" char (255) NOT NULL ,
	Lesson_Status varchar2 (30) NULL ,
	RawScore float NULL ,
	MaxScore float NULL ,
	MinScore float NULL ,
	Time char (16) NULL ,
	Lesson_Location varchar2 (255) NULL ,
	exist char (10) NULL
);


CREATE TABLE OL_SectionLog (
	UserID char (8) NOT NULL ,
	LOID char (10) NOT NULL ,
	SessionID char (128) NOT NULL ,
	"UID" char (255) NOT NULL ,
	Lesson_Status varchar2 (30) NULL ,
	Time char (16) NULL
);


CREATE TABLE OL_AiccInteraction (
	UserID char (8) NOT NULL ,
	LOID char (10) NOT NULL ,
	SessionID char (128) NOT NULL ,
	"UID" char (255) NOT NULL ,
	course_id varchar2 (255) NULL ,
	student_id char (10) NULL ,
	lesson_id varchar2 (255) NULL ,
	response_times int NOT NULL ,
	"DATE" date NULL ,
	time char (16) NULL ,
	interaction_id varchar2 (255) NULL ,
	objective_id varchar2 (255) NULL ,
	type_interaction char (30) NULL ,
	correct_response varchar2 (4000) NULL ,
	response_value char (30) NULL ,
	student_response varchar2 (255) NULL ,
	result char (30) NULL ,
	weighting float NULL ,
	latency char (16) NULL
);


CREATE TABLE OL_AiccObjectives (
	UserID char (8) NOT NULL ,
	LOID char (10) NOT NULL ,
	SessionID char (128) NOT NULL ,
	"UID" char (255) NOT NULL ,
	course_id varchar2 (255) NULL ,
	student_id char (10) NULL ,
	lesson_id varchar2 (255) NULL ,
	"DATE" date NULL ,
	time char (16) NULL ,
	objective_id varchar2 (255) NULL ,
	RawScore float NULL ,
	MaxScore float NULL ,
	MinScore float NULL ,
	status char (30) NULL ,
	mastery_time char (16) NULL
);


CREATE TABLE OfflineLo (
	LOID char (10) NOT NULL ,
	materialID varchar2 (32) NOT NULL ,
	w_height NUMBER(4) NOT NULL ,
	w_width NUMBER(4) NOT NULL
);


CREATE TABLE CatalogLo (
	LOID char (10) NOT NULL ,
	w_height NUMBER(4) NOT NULL ,
	w_width NUMBER(4) NOT NULL
);


CREATE TABLE CatalogLo_LoList (
	LOID char (10) NOT NULL ,
	CatalogListLOID char (10) NOT NULL ,
	sort_no NUMBER(4) NOT NULL
);


CREATE TABLE prerequisite (
	LOID char (10) NULL ,
	PrereID char (10) NULL
);


CREATE TABLE ScormLo (
	LOID 					char(10) NOT NULL ,
	materialID 				varchar2(32) NOT NULL ,
	w_height 				NUMBER(4) NOT NULL ,
	w_width 				NUMBER(4) NOT NULL
);


CREATE TABLE Scorm_Attempt (
	UserID 					char(8) NOT NULL,
	LOID 					char(10) NOT NULL,
	StudySessionCount 		NUMBER(4)	NOT NULL,
	ActivityID 				char(255) NOT NULL,
	AttemptCount 			NUMBER(4) NOT NULL,
	LearnSessionCount		NUMBER(4) NOT NULL,
	SessionID				char(128) NOT NULL,
	LearnSession_Date		date NOT NULL,
	Completion_Status 		varchar2(30) NULL,
	ScaledScore 			float NULL,
	RawScore 				float NULL,
	MaxScore 				float NULL,
	MinScore 				float NULL,
	Attempt_Session_Time 	float NULL,
	Success_Status 			varchar2(30) NULL,
	location				varchar2(1000) NULL,
	exit					varchar2(10) NULL
);


CREATE TABLE Scorm_Interaction (
	UserID 					char(8) NOT NULL,
	LOID 					char(10) NOT NULL,
	StudySessionCount 		NUMBER(4)	NOT NULL,
	ActivityID 				char(255) NOT NULL,
	AttemptCount 			NUMBER(4) NOT NULL,
	LearnSessionCount		NUMBER(4) NOT NULL,
	InteractionCount 		NUMBER(4) NOT NULL,
	SessionID				char(128) NOT NULL,
	InteractionID 			varchar2(4000) NULL,
	InteractionType 		varchar2(30) NULL,
	ObjectiveId 			varchar2(4000) NULL,
	Timestamp 				date NULL,
	CorrectResponse1 		varchar2(4000) NULL,
	CorrectResponse2 		varchar2(4000) NULL,
	CorrectResponse3 		varchar2(4000) NULL,
	CorrectResponse4 		varchar2(4000) NULL,
	CorrectResponse5 		varchar2(4000) NULL,
	Weighting 				float NULL,
	LearnerResponse 		varchar2(4000) NULL,
	Learner_Result 			varchar2(30) NULL,
	Latency	 				float NULL,
	Description 			varchar2(255) NULL
);


CREATE TABLE Scorm_LearnerComment (
	UserID 					char(8) NOT NULL,
	LOID 					char(10) NOT NULL,
	StudySessionCount 		NUMBER(4) NOT NULL,
	ActivityID 				char(255) NOT NULL,
	AttemptCount 			NUMBER(4) NOT NULL,
	LearnSessionCount		NUMBER(4) NOT NULL,
	LearnerCommentCount 	NUMBER(4) NOT NULL,
	SessionID				char(128) NOT NULL,
	LearnerComment 			varchar2(4000) NULL,
	LearnerCommentLocation 	varchar2(255) NULL,
	LearnerCommentTime 		varchar2(255) NULL
);


CREATE TABLE Scorm_LMSComment (
	UserID 					char(8) NOT NULL,
	LOID 					char(10) NOT NULL,
	StudySessionCount 		NUMBER(4) NOT NULL,
	ActivityID 				char(255) NOT NULL,
	AttemptCount 			NUMBER(4) NOT NULL,
	LearnSessionCount		NUMBER(4) NOT NULL,
	LMSCommentCount 		NUMBER(4) NOT NULL,
	SessionID				char(128) NOT NULL,
	LMSComment 				varchar2(4000) NULL,
	LMSCommentLocation 		varchar2(255) NULL,
	LMSCommentTime 			varchar2(255) NULL
);


CREATE TABLE Scorm_Objective (
	UserID 					char(8) NOT NULL,
	LOID 					char(10) NOT NULL,
	StudySessionCount 		NUMBER(4) NOT NULL,
	ActivityID 				char(255) NOT NULL,
	AttemptCount 			NUMBER(4) NOT NULL,
	LearnSessionCount		NUMBER(4) NOT NULL,
	ObjectiveCount			NUMBER(4) NOT NULL,
	SessionID				char(128) NOT NULL,
	ObjectiveId				varchar2(4000) NULL,
	ScaledScore 			float NULL,
	RawScore 				float NULL,
	MaxScore 				float NULL,
	MinScore 				float NULL,
	SuccessStatus			varchar2(30) NULL,
	CompletionStatus		varchar2(30) NULL
);


CREATE TABLE ScormSession (
	UserID 					char(8) NOT NULL,
	LOID 					char(10) NOT NULL,
	StudySessionCount 		NUMBER(4) NOT NULL,
	SessionID	 			char(128) NOT NULL
);
exit;
