《社区服务数据库设计运用学习》由会员分享，可在线阅读，更多相关《社区服务数据库设计运用学习（11页珍藏版）》请在金锄头文库上搜索。
1、1.1.1. 数据列表l 用户表sq_user列名字段名称数据类型（精度范围）空/非空约束条件备注用户IDUSERID VARCHAR2(32)非空PK手机号PHONENUMBERVARCHAR2(32)非空密码PASSWORDVARCHAR2(60)非空权限AUTHORITYVARCHAR2(10)两种所属社区COMMUNITYVARCHAR2(32)姓名NAMEVARCHAR2(20)住址ADDRESSVARCHAR2(50)文化程度CULTURAL_DEGREEVARCHAR2(32)Apk版本号APK_VERSIONVARCHAR2(10)性别SEXVARCHAR2(4)年龄AGENU
2、MBER最近访问时间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(
3、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 inde
4、x 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
5、(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 p
6、ctfree 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 st
7、orage ( 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 V
8、ARCHAR2(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 ini
9、trans 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
10、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)- Cre
11、ate 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 m
12、axextents 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
