社区服务数据库设计【运用学习】.doc

1.1.1. 数据列表l 用户表[sq_user]列名字段名称数据类型（精度范围）空/非空约束条件备注用户IDUSERID VARCHAR2(32)非空PK号PHONENUMBER VARCHAR2(32)非空密码PASSWORDVARCHAR2(60)非空权限AUTHORITYVARCHAR2(10)两种所属社区COMMUNITYVARCHAR2(32)姓名NAMEVARCHAR2(20)住址ADDRESSVARCHAR2(50)文化程度CULTURAL_DEGREEVARCHAR2(32)Apk版本号APK_VERSIONVARCHAR2(10)性别SEXVARCHAR2(4)年龄AGENUMBER最近访问时间VISIT_TIMEDATE服务器-- Create tablecreate table SQ_USER( userid VARCHAR2(32) not null, phonenumber VARCHAR2(32) not null, password VARCHAR2(60) not null, authority VARCHAR2(10), community VARCHAR2(32), name VARCHAR2(20), address VARCHAR2(50), cultural_degree VARCHAR2(32), apk_version VARCHAR2(10), visit_time DATE, sex VARCHAR2(4), age NUMBER)tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );-- Create/Recreate primary, unique and foreign key constraints alter table SQ_USER add constraint SQ_USER_KEY primary key (USERID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );l 社区信息表[sq_community]列名字段名称数据类型（精度范围）空/非空约束条件备注社区IDCOMMUNITYID VARCHAR2(32)非空PK社区名称COMMUNITYNAMEVARCHAR2(32)非空所属区县AREANAMEVARCHAR2(20)详细地址ADDRESSNAMEVARCHAR2(50)两种社区编号COMMUNITYNUMBERVARCHAR2(32)社区负责人CHIEFNAMEVARCHAR2(20)负责人CHIEFTELVARCHAR2(32)-- Create tablecreate table SQ_COMMUNITY( communityid VARCHAR2(32) not null, communitname VARCHAR2(32), areaname VARCHAR2(20), addressname VARCHAR2(50), communitynumber VARCHAR2(32), chiefname VARCHAR2(20), chieftel VARCHAR2(32))tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );-- Create/Recreate primary, unique and foreign key constraints alter table SQ_COMMUNITY add constraint KEY_COMMUNITY primary key (COMMUNITYID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );l 用户关注表[sq_attention]列名字段名称数据类型（精度范围）空/非空约束条件备注标识IDID VARCHAR2(32)非空PK用户idUSER_IDVARCHAR2(32)FK关注idATTENTION_DIC_ID VARCHAR2(32)FK-- Create tablecreate table SQ_ATTENTION( id VARCHAR2(32) not null, user_id VARCHAR2(32) not null, attention_dic_id VARCHAR2(32))tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );-- Create/Recreate primary, unique and foreign key constraints alter table SQ_ATTENTION add constraint SQ_ATTENTION_KEY primary key (ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );alter table SQ_ATTENTION add constraint SQ_ATTENTION_FKEY foreign key (ATTENTION_DIC_ID) references SQ_ATTENTION_DIC (ATTENTION_DIC_ID) on delete cascade;alter table SQ_ATTENTION add constraint SQ_ATTENTION_FKEY2 foreign key (USER_ID) references SQ_USER (USERID) on delete cascade;l 视频信息表[sq_ videoMessage]列名字段名称数据类型（精度范围）空/非空约束条件备注视频idVIDEO_IDVARCHAR2(32)非空PK视频名称VIDEO_NAMEVARCHAR2(32)非空视频大小VIDEO_SIZWVARCHAR2(32)视频时长VIDEO_LENGTHVARCHAR2(20)录入用户UPLOAD_USER_IDVARCHAR2(32)录入时间UPLOAD_TIMEDATE审核标记UPLOAD_AUDITVARCHAE2(4)-- Create tablecreate table SQ_VIDEO( video_id VARCHAR2(32) not null, video_name VARCHAR2(32), video_size VARCHAR2(32), video_length VARCHAR2(20), upload_user_id VARCHAR2(32), upload_time DATE, upload_audit VARCHAR2(4))tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );-- Create/Recreate primary, unique and foreign key constraints alter table SQ_VIDEO add constraint SQ_VIDEO_KEY primary key (VIDEO_ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );l 图片信息表[sq_ picture]列名字段名称数据类型（精度范围）空/非空约束条件备注图片idPICTURE_IDVARCHAR2(32)非空PK图片名称PICTURE_NAMEVARCHAR2(32)非空录入用户UPLOAD_USER_IDVARCHAR2(32)审核标记UPLOAD_AUDITVARCHAE2(4)录入时间UPLOAD_TIMEDATE-- Create tablecreate table SQ_PICTURE( picture_id VARCHAR2(32) not null, picture_name 。