数据库原理练习题

• (2)用关系代数语言或SQL语句完成如下操作： （5分） • 1)往student表中增添一条关于学生信息的数据，值为： Sno是11000，Sname是张三，Ssex是女，Sage为20 岁，Sdept为计算机应用系。（用SQL语句完成） • 2）检索张三同学所在的系。（用关系代数完成） • 3)检索姓名以“王”开头的所有学生的姓名和年龄。 （用SQL语句完成）
数据库原理练习题
综合题
• 1.有一个应用，包括三个实体集。 实体类型“商店” 的属性有：商店编号，店名，店址，店经理。 实体类 型“会员”的属性有：会员编号，会员名，地址。 实 体类型“职工”的属性有：职工编号，职工名，性别， 工资 。 每家商店有若干职工，但每个职工只能服务 于一家商店。 每家商店有若干会员，每个会员可以属 于多家商店。在联系中应反映出职工参加某商店工作 的开始时间，会员的加入时间。 • (1)试画出反映商店、职工、会员实体类型及联系类型 的ER图； • (2)将ER图转换成关系模式，并指出每个表的主键和 外键；
• （2） • 商店模式（商店编号，店名，店址，店经理） 主键：商店编号 • 职工模式（职工编号，职工名，性别，工资，商店编号，开始参 加工作时间） • 主键：职工编号 外键：商店编号 • 会员模式（会员编号，会员名，地址） • 主键：会员编号 • SM模式（会员编号，商店编号，会员加入时间） • 主键：会员编号，商店编号 • 外键：会员编号；商店编号
• （2）. • 1) insert into student • Values(11000,‘张三’，‘女’，20，‘计算 机应用系’) • 2)、πSdept（σSname =‘张三’（student）） • 3)、SELECT Sname, Sage FROM student WHERE Sname LIKE ‘王%’
• 2. 设关系模式R(S#,C#,GRADE,TNAME,TADDR),其 属性分别表示学生学号、选修课程的编号、成绩、任 课教师姓名、任课教师地址等意义。如果规定，每个 学生每学一门课只有一个成绩； 每门课只有一个教师 任教；每个教师只有一个地址(此处不允许教师同名同 姓)。 (1) 试写出关系模式R的候选码，主属性和非主属性。 • （2）试写出关系模式R基本的函数依赖。 • （3）试述2NF的定义，并把R分解成2NF模式集，同 时说明理由。 • (4) 试述3NF的定义，在第2NF基础上把R分解成3NF 模式集，同时说明理由。
• 2．设有关系EMPLOYEE (E#,ENAME,EDEPT,DNAME,C#,PRIZE)， E#+C#为候选码，设关系中有如下函数依赖： • (E#,C#)→ENAME,EDEPT,DNAME E#→ENAME,EDEPT,DNAME (E#,C#)→PRIZE EDEPT→DNAME 试求下列问题： （1）关系EMPLOYEE属于第几范式？ （2）如果关系EMPLOYEE不属于第三范式， 请将关系EMPLOYEE逐步分解为第三范式。 要求：写出达到每一级范式的分解过程，并指 明消除什么类型的函数依赖。
• • • • • • • • • • • • • • • • • • • • • •
2（1）． CREATE TABLE Student （Sno Number(5), Sname Varchar(10), Ssex Varchar(2), Ssage Number(3) CONSTRAINT C1 CHECK(Ssage<25), Sdept Varchar(15), CONSTRAINT PK-Sno Primary Key(Sno)); CREATE TABLE COURSE (Cno Number(5), Cname Varchar(10), Cpno Number(5), Ccredit Number(5), CONSTRAINT PK-Cno Primary Key(Cno)); CREATE TABLE SC (Sno Number(5), Cno Number(5), Grade Number(5), CONSTRAINT PK-SC Primary Key(Sno,Cno), CONSTRAINT FK-Sno FOREIGN KEY (Sno) REFRENCES Student(Sno), CONSTRAINT FK-Cno FOREIGN KEY (Cno) REFRENCES Course(Cno));
• • • •
3. 1)、πSname，Dept（σsname=‘张三’（S）） 2)、πS#，cname（σGarde>90（C∞SC） 3)、SELECT C#，AVG（GRADE） FROM SC，C WHERE SC．C#＝C．C# AND TEACHER =‘张’ group by C# • 4)、SELECT SNAME,AGE FROM S WHERE SNAME LIKE ‘王%’
• 解： （1）关系EMPLOYEE是1NF。 （2）首先消除部分函数依赖 E#→ENAME,EDEPT,DNAME ) 将关系分解为： R1(E#, ENAME,EDEPT,DNAME) R2(E#, C#,PRIZE) 在关系R1中存在非主属性对候选码的传递函数 依赖E#→EDEPT，EDEPT→DNAME，所以 以上关系模式还不是第三范式， • 进一步分解R1： R11(E#, ENAME,EDEPT) R12(EDEPT,DNAME) R11,R12都是3NF。
• （2） • 商店模式（商店编号，店名，店址，店经理） 主键：商店编号 • 职工模式（职工编号，职工名，性别，工资，商店编号，开始参 加工作时间） • 主键：职工编号 外键：商店编号 • 会员模式（会员编号，会员名，地址） • 主键：会员编号 • SM模式（会员编号，商店编号，会员加入时间） • 主键：会员编号+商店编号 • 外键：会员编号；商店编号
• 1.有一个应用，包括三个实体集。 实体类型 “商店”的属性有：商店编号，店名，店址， 店经理。 实体类型“会员”的属性有：会员 编号，会员名，地址。 实体类型“职工”的 属性有：职工编号，职工名，性别，工资 。 每家商店有若干职工，但每个职工只能服务于 一家商店。 每家商店有若干会员，每个会员 可以属于多家商店。在联系中应反映出职工参 加某商店工作的开始时间，会员的加入时间。 • (1)试画出反映商店、职工、会员实体类型及 联系类型的ER图； • (2)将ER图转换成关系模式，并指出每个表的 主键和外键；
• • • • •
2.假设wenku.baidu.com下面的三个关系模式： Student（Sno，Sname，Ssex，Sage，Sdept） Course（Cno，Cname，Cpno，Ccredit） SC（Sno，Cno，Grade） （1）用SQL语言定义这三个关系模式，要求在模式 中完成以下完整性约束条件的定义（： • a. 定义每个模式的主码； • b. 定义参照完整性； • c. 定义学生年龄不得超过25岁。
• • • • • • •
2. （1）候选码为：S#+C# 主属性：S#,C# 非主属性：GRADE,TNAME,TADDR （2）函数依赖： F={(S#,C#)→GRADE，C#→TNAME，TNAME→TADDR} （3）2NF: 若R∈1NF,且每一个非主属性完全函数依赖于码，则 R∈2NF. 在模式R中，TNAME不完全依赖于键(S#，C#),因此需进行分解，可分 解为下列两个关系。 SC={S#,C#,GRADE} C={C#,TNAME,TADDR} 分解后，SC中，GRADE完全依赖于侯选键(S#,C#),在C中，主属性是 C#，TNAME、TADDR均完全依赖于C#。 因此，该分解符合2NF模 式。 (4)3NF：若每个关系模式是2NF，则每个非主属性都不传递于R的候选 码。 按上述已分好的两个模式，SC中已满足“每个非主属性都不传递于R的 候选键”，已是3NF,而在C中， C#→TNAME，TNAME→TADDR， TADDR传递依赖于C#，因此还需分成两个模式：CT(C#,TNAME), T(TNAME,TADD)。 分解后，总共有SC={S#,C#,GRADE},CT(C#,TNAME), T(TNAME,TADD)三个模式。 该分解符合3NF模式。
• 3．试用关系代数或SQL语句表达下列数据库 中3个基本表S、SC、C的查询： • S(S#,Sname,Age,Sex,Dept) • SC(S#,C#,Grade) • C(C#,Cname,Teacher) • 1)、查询张三同学的所在系(用关系代数完成) • 2)、查询成绩在90分以上的学生学号和课程 名(用关系代数完成)； • 3)、求张老师所授课程的每门课程的学生平均 成绩(用SQL查询语句完成)； • 4)、检索姓名以“王”开头的所有学生的姓名 和年龄(用SQL查询语句完成)；