SPJ数据库分析和总结.pdfVIP

设有一个 SPJ数据库，包括 S，P，J，SPJ四个关系模式： 供应商 S(SNO , SNAME , STATUS , CITY) 零件 P(PNO , PNAME , COLOR , WEIGHT) 工程 J(JNO , JNAME , CITY) SPJ(SNO , PNO , JNO , QTY) 创建数据库： create database SPJ ; 创建表： CREATE TABLE S ( SNO char ( 2 ) primary key , SNAME varchar ( 20 ), STATUS char ( 4 ), CITY char ( 10 )); create table P ( PNO char ( 2 ) primary key , PNAME varchar ( 20 ), COLOR char ( 4 ), WEIGHT int ); create table J ( JNO char ( 2 ) primary key , JNAME varchar ( 20 ), CITY char ( 10 )); create table SPJ ( SNO char ( 2 ), PNO char ( 2 ), JNO char ( 2 ), QTY int , primary key ( SNO, PNO, JNO ), foreign key ( SNO) references S( SNO) on delete cascade , foreign key ( PNO) references P( PNO) on delete cascade , foreign key ( JNO ) references J ( JNO ) on delete cascade ); 插入数据： insert into S values ( S1 , 供应商一 , 良好 , 郑州 ); insert into S values ( S2 , 供应商二 , 良好 , 天津 ); insert into S values ( S3 , 供应商三 , 良好 , 上海 ); insert into S values ( S4 , 供应商四 , 优秀 , 郑州 ); insert into P values ( P1 , 管材 , 红色 , 20 ); insert into P values ( P2 , 管线 , 绿色 , 30 ); insert into P values ( P3