ACS3 Data Model

Main

Description

Columns

Primary key

GENERAL_LINK_USER_RATINGS

Columns

Name	Type	Nullable
USER_ID	NUMBER(38)	N
LINK_ID	NUMBER(38)	N
RATING	NUMBER(38)	N

Primary key:

Constraint Name	Columns
SYS_C002087913	LINK_ID , USER_ID

Check Constraints:

Constraint Name	Check Condition
SYS_C002087909	"USER_ID" IS NOT NULL
SYS_C002087910	"LINK_ID" IS NOT NULL
SYS_C002087911	"RATING" IS NOT NULL
SYS_C002087912	rating between 0 and 10

Foreign Keys:

Constraint Name	Columns	Referenced table	Referenced Constraint	On Delete Rule
SYS_C002087914	USER_ID	USERS	SYS_C002087135	NO ACTION
SYS_C002087915	LINK_ID	GENERAL_LINKS	SYS_C002087892	NO ACTION

Options:

Option	Settings
Index Organized	No
Generated by Oracle	No
Clustered	No
Nested	No
Temporary	No

Indexes:

Index Name	Type	Unuqueness	Columns
SYS_C002087913	NORMAL	UNIQUE	LINK_ID , USER_ID

Triggers

Name: GENERAL_LINKS_RATING_UPDATE
CREATE TRIGGER general_links_rating_update after insert or update on general_link_user_ratings REFERENCING NEW AS NEW OLD AS OLD declare cursor c1 is select gl.link_id, count(*) as n_ratings, avg(rating) as avg_rating from general_links gl, general_link_user_ratings glr where gl.link_id = glr.link_id group by gl.link_id; begin for c_ref in c1 loop update general_links set n_ratings = c_ref.n_ratings, avg_rating = c_ref.avg_rating where link_id = c_ref.link_id; end loop; end;

Name: GENERAL_LINKS_RATING_UPDATE

CREATE TRIGGER general_links_rating_update
after insert or update on general_link_user_ratings
REFERENCING NEW AS NEW OLD AS OLD
declare
 cursor c1 is select gl.link_id, count(*) as n_ratings, avg(rating) as avg_rating from general_links gl, general_link_user_ratings glr where gl.link_id = glr.link_id group by gl.link_id;
begin
  for c_ref in c1 loop

   update general_links
   set n_ratings = c_ref.n_ratings,
   avg_rating = c_ref.avg_rating
   where link_id = c_ref.link_id;

  end loop;
end;

Generated by OraSchemaDoc, (c) Aram Kananov, 2002