数据库

13．对于学生选课关系，其关系模式为：学生（学号，姓名，年龄，所在系）；课程（课程名，课程号，先行课）；选课（学号，课程号，成绩）。用关系代数完成如下查询。l）求学过数据库课程的学生的姓名和学号。2）求学过数据库和数据结构的学生姓名和学号。3）求没学过数据库课程的学生学号。4）求学过数据库的先行课的学生学号。答：1)Ⅱ学号姓名，（''数据库课程名（学生选课课程））2）Ⅱ学号姓名，（''数据库课程名（学生选课课程））∩Ⅱ学号姓名，（''数据库课程名（学生选课课程））3）Ⅱ学号姓名，（学生）-Ⅱ学号姓名，（''数据库课程名（学生选课课程））4）设课程的两个副本为课程A和课程B，Ⅱ学号课程A，（（''数据库课程名程名课程A，（课程A课程号课程先行课课程B，A，课程B））课程号选课课程号课程，B，选课））14．设有一个SPJ数据库，包括S,P,J,SP.J四个关系模式：S(SNO，SNAME，STATUS，CITY)；P(PNO，PNAME，COLOR，WEIGHT)；J(JNO，JNAME，CITY)；SPJ（SNO，PNO，JNO，QTY）。其中，供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成；零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成；工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成；供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商供应某种零件给某工程项目的数量为QTY。试用关系数完成如下查询：1）求供应工程J1零件的供应商号码SNO。2）求供应工程J1零件Pl的供应商号码SNO。3）求供应工程J1零件为红色的供应商号码SNO。4）求没有使用天津供应商生产的红色零件的二程号。5）求至少用了供应商S1所供应的全部零件的工程号。答：1）ⅡSNO（σJNO=’J1’（SPJ））2）ⅡSNO（σJNO=’J1’∧PNO=‘P1’（SPJ））3）ⅡSNO（σJNO=’J1’∧COLOR=‘红色’（SPJP））4）ⅡJNO（J）-ⅡJNO（σJNO=’天津’∧COLOR=‘红色’（SPJSP））5）ⅡJNO，PNO（SPJ）÷ⅡPNO（σSNO=’S1’（SSPJ））15．设有3个关系S(S#,SNAME,AGE,SEX）、C(C#，CNAME,TEACHER）和SC(S#,C#,GRAOE）。1）用关系代数表达式检索年龄大于21岁男学生的学号（S#）和姓名（SNAME）。2）用关系代数表达式检索全部学生都选修的课程的课程号（C#）和课程名（CNAME）。3）用关系代数表达式检索籍贯为上海的学生的姓名、学号和选修的课程号。4）用关系代数表达式检索选修了全部课程的学生姓名和年龄。5）用元组关系演算表达式检索选修了“程军”老师所授课程之一的学生学号。6）用元组关系演算表达式检索年龄大于21的男生的学号和姓名。答：l）ⅡS#，CNAME（σAGE＞21∧SEX=’男’（S））2）ⅡC#，CNAME（C（ⅡS#，C#（SC）÷ⅡS#（S））3）Ⅱ2,6,7（σA籍贯=’上海’（SSC））4）Ⅱ2,3（S（Ⅱ1，2（SC）÷Ⅱ1（C））5）{T(2)∣(V)(W)(SC(V)∧C(W)∧T［1］=V［1］∧V［2］=W［1］∧W［3］=’程军’)}6）{t(2)∣(r)(S(r)∧t［1］=r［1］∧t［2］=r［2］∧r［3］＞21∧r［4］=’男’)}16．某医院病房计算机管理中需要如下信息。科室：科名，科地址，科电话，医生姓名病房：病房号，床位号，所属科室名医生：姓名，职称，所属科室名，年龄，工作证号病人：病历号，姓名，性别，诊断，主管医生，病房号其中，一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只有一个。完成如下设计：1）设计该计算机管理系统的E一R图。2）将该E一R图转换为关系模型结构。3）指出转换结果中每个关系模式的候选码。答：1）本题的E一R图如图6一19所示。一科名）（科地址）（科电话科室从属病人医生缄）（眯病历号）（姓名翌夕图6一19工作证号）（姓名职称）（年龄题16的E一R图2）对应的关系模型结构如下：科室（科名，科地址，科电话）病房（病房号，床位号，科室名）医生（工作证号，姓名，职称，科室名，年龄）病人（病历号，姓名，性别，诊治，主管医生，病房号）3）每个关系模式的候选码如F:科室的候选码是科名；病房的候选码是科室名＋病房号；医生的候选码是工作证号；病人的候选码是病历号。6.5关系数据库标准语言―SQL习题及解答1．试述SQL语言的特点。答：SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，它除了具有一般关系数据库语言的特点外，还具有3个特点：①SQL具有自含式和嵌人式两种形式；②SQL具有语言简洁、易学易用的特点；③SQL支持三级模式结构。2．在嵌人式SQL中，如何区分SQL语句和主语言语句？答：在嵌人方式下SQL语句在嵌入主语言的程序时其前缀加EXECSQL；其结束处用END＿EXEC或用分号“;”。3．在嵌人式SQL中，如何解决数据库工作单元与源程序工作单元之间的通信？答：数据库的工作单元和源程序工作单元之间通信的主要方式有：主语言通过主变量向SQL语句提供参数；SQL语句的当前工作状态和运行环境数据要反馈给应用程序。4．在嵌人式SQL中，如何协调SQL语言的集合处理方式和主语言单记录处理方式？答：使用游标解决SQL一次一集合的操作与主语言一次一记录操作的矛盾。游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。用户可以通过游标逐一获取记录，并将记录赋给主变量，交给主语言作进一步处理。5．设职工一社团数据库有3个基本表：职工（职工号，姓名，年龄，性别）；社会团体（编号，名称，负责人，活动地点）；参加（职工号，编号，参加日期）。其中：l）职工表的主码为职工号。2）社会团体表的主码为编号；外码为负责人，被参照表为职工表，对应属性为职工表中的职工号。3）参加表的职工号和编号为主码；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。试用SQL语句表达下列操作：l）定义职工表、社会团体表和参加表，并说明其主码和参照关系。2）建立下列两个视图。社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）；参加人情况（职工号，姓名，社团编号，社团名称，参加日期）。3）查找参加歌唱队或篮球队的职工号和姓名。4）查找没有参加任何社会团体的职工情况。5）查找参加了全部社会团体的职工情况。6）查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。7）求每个社会团体的参加人数。8）求参加人数最多的社会团体的名称和参加人数。9）求参加人数超过100人的社会团体的名称和负责人。10）把对社会团体和参加两个表的数据查看、插人和删除数据的权力赋给用户李平，并允许他再将此权力授予其他用户。答：CREATETABLE职工（职工号CHAR（10）NOTNULLUNIQUE,姓名CHAR（8）NOTNULL,年龄SMALLINT,性别CHAR(2),CONSTRAINTC1CHECK（性别IN（‘男’,’女’))；CREATETABLE社会团体（编号CHAR（8）NOTNULLUNIQUE,名称CHAR（12）NOTNULL,负责人CHAR（8）,活动地点VARCHAR（50）,CONSTRAINTC2FOREIGNKEY（负责人）REFERENS职工（职工号））；CREATETABLE参加（职工号CHAR（8）,编号CHAR（8）,参加日期DATE，CONSTRAINTC3PRIMARYKEY（职工号，编号），CONSTRAINTC4FOREIGNKEY（职工号）REFEREN职工（职工号）；2）CREATEVIEW社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）ASSELECT编号，名称，负责人，姓名，性别FROM社会团体，职工WHERE社会团体．负责人＝职工．职工号；CREATEVIEW参加人情况（职工号，姓名，社团编号，社团名称，参加日期）ASSELECT参加，职工号，姓名，社会团体，编号，名称，参加日期FROM职工，社会团体，参加WHERE职工.职工号＝参加.职工号AND参加.编号＝社会团体.编号；3）SELECT职工号，姓名FROM职工，社会团体，参加WHERE职工.职工号=参加．职工号AND参加.编号=社会团体.编号AND社会团体.名称IN（‘歌唱队‘，’篮球队’)；4)SELECT*FROM职工WHERENOTEXISTS（SELECT*FROM参加WHERE参加.职工号=职工.职工号）；5）SELECT*FROM职工WHERENOTEXISTS（SELECT*FROM参加WHERENOTEXISTS（SELECT*FROM社会团体WHERE参加.职工号=职工.职工号AND参加．编号＝社会团体．编号）);6）SELECT职工号FROM职工WHERENOTEXISTS（SELECT'*FROM参加参加1WHERE参加1，职工号='1001'ANDNOTEXISTS（SELECT*FROM参加参加2WHERE参加2.编号=参加1.编号AND参加2.职工号=职工.职工号））；7）SELECTCOUNT（职工号）FROM参加GROUPBY编号；8）SELECT社会团体.名称，COUNT（参加.职工号）FROM社会团体,参加WHERE社会团体.编号=参加.编号GROUPBY参加．编号HAVINGMAX（COUNT（参加．职工号））=COUNT（参加．职工号））；9）SELECT社会团体．名称，职工．姓名FROM职工，社会团体，参加WHERE社会团体．编号=参加．编号AND社会团体．负责人=职工．职工号GROUFBY参加．编号HAVINGCOUNT（参加．编号）100；10）GRANTSELECT,INSERT,DELECTON社会团体，参加TO李平WITHGRANTOPTION；6．设工程一零件数据库中有4个基本表：供应商（供应商代码，姓名，所在城市，联系电话）；工程（工程代码，工程名，负责人，预算）；零件（零件代码，零件名，规格，产地，颜色）；供应零件（供应商代码，工程代码，零件代码，数量）。试用SQL语句完成下列操作：l）找出天津市供应商的姓名和电话。2）查找预算在50000-100000元之间的工程的信息，并将结果按预算降序排列。3）找出使用供应商S1所提供零件的工程号码。4）找出工程项目J2使用的各种零件名称及其数量。5）找出上海厂商供应的所有零件号码。6）找出使用上海产的零件的工程名称。7）找出没有使用天津产零件的工程号码。8）把全部红色零件的颜色改成蓝色。9）将由供应商S5供给工程代码为J4的零件P6改为由S3供应，并作其他必要的修改。10）从供应商关系中删除能的记录，并从供应零件关系中删除相应的记录。答：1）SELECT姓名，联系电话FROM供应商WHERE所在城市=’天津’；2)SELECT*FROM工程WHERE预算BETWEEN50000AND100000ORDERBY预算DESC；3）SELECT工程代号FROM供应零件WHERE供应商代码='S1'；4）SELECT零件．零件名，供应零件．数量FROM零件，供应零件WHERE零件．零件代码=供应零件．零件代码AND供应零件．工程代码='J2’5）SELECT零件代号FROM供应商，供应零件WHERE供应商．供应商代码=供应零件．供应商代码AND供应商．所在城市=‘上海’；6）SELECT工程名FROM供应商，供应零件，工程WHERE供应商．供应商代码=供应零件．供应商代码AND供应零件．工程代码=工程．工程代码AND供