create table project
	(project_id integer primary key,
	project_name varchar[100] not null,
	owner_id integer references developer(user_id),
	description varchar[200] not null,
	status char[1] check status in ('y','n'),
	download_link varchar[50] not null,
	homepage_link varchar[50] 
	rank integer,
	activity  numeric(4,2),
	downloads integer
	);
create table project_patch
	(project_id integer references project(project_id),
	patch_id integer,
	patch_name varchar[50] not null,
	description varchar[200] not null,
	status char[1] check status in ('y','n'),
	primary key(project_id,patch_id));
create table developer
	(user_id integer primary key,
	user_login varchar[30] not null unique,
	passwd varchar[10] not null,
	name varchar[50] not null,
	email varchar[50] not null,
	homepage varchar[50]
	);
create table non_developer
	(user_id integer primary key,
	user_login varchar[30] not null unique,
	passwd varchar[10] not null,
	name varchar[50] not null,
	email varchar[50] not null,
	homepage varchar[50]
	);
create view subscribed_user(user_id,user_login,passwd,name,email)
	((select user_id,user_login,passwd,name
	from developer)
	union
	(select user_id,user_login,passwd,name
	from non_developer));
create table proj_category
	(project_id integer references project(project_id),
	category varchar[100],
	primary key(project_id,category));
create table project_rank 
	(project_id integer references project(project_id),
	user_id integer references subscribed_user(user_id),
	rank integer not null
	) 
create table proj_dependency
	(project_id integer references project(project_id),
	dependent_proj integer references project(project_id),
	primary key(project_id,dependent_proj));
create table subscribe_proj
	(project_id integer references project(project_id),
	user_id integer references subscribed_user(user_id),
	status char[1] check status in ('y','n'),
	join_date date,
	designation varchar[10] check designation in ('admin','developer','pending'),
	primary key(project_id,user_id)
	);
create table transact_addon
	(
	transcation_id integer primary key
	); 
create table develop_project
	(user_id integer references subscribed_user(user_id),
	submit_date date,
	description varchar[100],
	transaction_id integer references transact_addon(transaction_id) primary key,
	addon_type varchar[10] not null,
	project_id integer references project(project_id)
	);
create table addon
	(addon_id integer,
	addon_name varchar[20] not null,
	addon_type varchar[10] not null,
	status char[1] check status in ('y','n'),
	description varchar[200] not null,
	project_id integer references project(project_id),
	primary key(addon_id,project_id)
	);
create table bug
	(
	bug_id integer primary key
	);
create table submit_bug
	(user_id integer references subscribed_user(user_id),
	project_id integer references project(project_id),
	description varchar[200] not null,
	bug_id integer references bug(bug_id) primary key
	)