• 微助教 题库
  • 1.1
  • 1.2
  • 2.1
  • 3.3
  • 3.1
  • 4.1
  • 4.2
  • 5.1
  • 5.3
  • 6.1
  • 6.3
  • 作业 1
  • 作业 2
  • 第一章测验
  • 第二章测验
• 线上期末考试题
  • Factory information management system
• 模拟题
  • 1. 单选题
  • 2. 填空题
  • 3. 简答题
  • 4. 关系代数
  • 5. 完成SQL
  • 6. E-R 图
  • 7. 规范化和模式设计

微助教 题库

使用“引用”格式（即本段内容格式）表示的内容皆为博主本人的主观答案，没有评分，仅供参考，不保证正确。

1.1

1. DBMS是指 [数据库管理系统] 。

2. 数据库是指在计算机系统中按照一定的数据模型组织、存储和应用的 [数据的集合] 。

3. 文件系统和数据库系统的最大区别是 [数据结构化] 。

4. 数据库系统中的软件是指 [数据库管理系统] 。

5. 由计算机硬件、操作系统、DBMS、数据库、应用程序及用户等组成的一个整体称为 [数据库系统] 。

6. 以下关于DB、DBMS、DBS三者之间的关系的叙述中，正确的是 [DBS包括了DBMS和DB] 。

7. 在DBS中，DBMS和OS之间的关系是 [DBMS调用OS] 。

8. 数据库系统与文件系统的主要区别是 [文件系统不能解决数据冗余和数据独立性问题，而数据库系统可以解决] 。

9. 下列四项中，不属于数据库系统特点的是 [数据独立性低] 。

10. 下列有关数据库描述正确的是 [数据库是一个结构化的数据集合] 。

1.2

1. 用二维表来表示实体及实体之间联系的数据模型称为 [关系模型] 。

2. 数据模型是 [记录及其联系的集合] 。

3. 以下关于数据模型的描述中，错误的是 [数据模型表示的是数据库本身] 。

   正确项：数据模型表示的是数据库的框架；数据模型是客观事物及其联系的描述；数据模型能够以一定的结构形式表示出不同数据之间的联系。

4. 在数据库系统的组织结构中，把概念数据库与物理数据联系起来的映射是 [模式/物理模式] 。

5. 物理模式是系统程序员用一定的 [文件] 形式组织起来的一个存储文件和联系手段。

6. 三个模式反映了对数据库的三种不同观点，以下说法中正确的是 [子模式表示了数用户级数据库，体现了对数据库的用户观] 。

7. 在数据库的三级模式中，只有 [物理模式] 才是真正存储数据的。

8. 逻辑数据独立性是指 [模式变、应用程序不变] 。

9. 模式是数据库中数据的 [全局逻辑结构] 。

10. 目前，数据库系统广泛使用的数据模型是 [关系模型] 。

2.1

1. Describe the differences in meaning between the terms relation and relation schema.

   关系模式是关系的所有属性的集合， 关系是一个二维表格，表示多个实体之间的相互关联，每一张表称为该关系模式的一个具体关系。

2. 

   Consider the relation schemas in the slides. Which attributes should be choose as primary keys for each relation schema? Which attributes are foreign keys based on the primary keys?

   (1) employee.person_name works.person_name company.company_name manages.person_name

   (2) employee.person_name是manages.person_name和works.person_name的外键 works.company_name是company.company_name的外键

3.3

1. 下面书法正确的是 [在SQL中，“!=”和"<>"作用完全相同] 。

2. 下面关于DISTINCT的描述，正确的是 [删除重复的数据] 。

3. 假设订单表的关系模式为orders( oderid, oderdate, orderamout, customerid)，则查找客户订单总金额超过3000的SQL语句为 [select custommerid, sum(orderamount) from orders group by customerid having sum(orderamount)>3000] 。

4. 从products中，找出单价unitprice在6到10的产品名称和单价信息的SQL语句有 [select name, unitprice from products where unitprice between 6 and 10; select name, unitprice from products where unitprice >-6 and unitprice <=10] 。（这个题应该是错题，很明显第二个选项不可能是 >-6）

5. 

   Write the following queries in SQL, using the university schema.

   1. Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicate names in the result.
   2. For each department, find the maximum salary of instructors in that department. You may assume that every department has at least one instructor.
   3. Find the IDs and names of all students who have not taken any course offering before Spring 2009.
   4. Find the lowest, across all departments, of the per-department maximum salary computed by the preceding query.

   1.  select distinct name 
   	from student as X,takes as Y,Z 
   	where X.ID=Y.ID and Z.course_id=Y.course_id and dept_name = 'Comp.Sci';
   2.  select dept_name,salary 
   	from instructor 
   	group by dept_name 
   3.  select student.ID,student.name 
   	from student except (
           select student.ID,student.name 
           from student,takes 
           where year<2009 and student.ID= takes.ID);
   4.  select min(X) 
   	from(
           select dept_name,max(salary) as X 
           from instructor 
           group by dept_name);
   

6. 

   Consider the employee database in the figure, where the primary keys are underlined. Give an expression in SQL for each of the following queries.

   1. Find all employees who earn more than the average salary of all employees of their company.
   2. Find the company that has the smallest payroll.
   3. Find all employees in the database who live in the same cities and on the same streets as do their managers.

   1.  select employee_name 
   	from works as W 
   	where salary>(
           select avg(salary) 
           from works as X 
           where W.company_name=X.company_name);
   2.  select company_name 
   	from works group_by company_name 
   	having sum(salary)<=all(
           select sum(salary) 
           from works 
           group by company_name);
   3.  select X.employee_name 
   	from employee,emplyee,manages as X,Y,Z 
   	where X.employee_name=Z.employee_name and Z.manager_name=Y.employee_name and X.street=Y.street and X.city=Y.city
   

3.1

1. 商品表中，商品名属于 [字符类型] 数据类型。

2. 下面关于主码的说法，错误的是 [一个关系可以有多个主码] 。

   正确选项：一个关系中只能由一个主码；主码的取值不能重复；主码约束是一个很重要的实体完整性约束。

3. 如果要插入一条员工数据，下列选项正确的是 [INSERT INTO employee (name, gender, age, rank) values ('Jim,'m',48,'201')] 。

4. SELECT语句中，必选的子句是SELECT....FROM....WHERE。 [否]

5. Let the following relation schemas be given:

   $R=(A, B, C)$
   $S=(D,E,F)$

   Let relations $r(R)$ and $s(S)$ be given. Give an expression in SQL that is equivalent to each of the following queries.

   1. $\Pi_{\mathrm{A}}(r)$
   2. $\sigma_{B=17}(r)$
   3. $r \times s$
   4. $\Pi_{A, F}\left(\sigma_{C=D}(r \times s)\right)$

   1.  select A from r; 
   2.  select * from r where B=17; 
   3.  select * from r,s; 
   4.  select A,F from r,s where C=D;
   

6. 

   Consider the employee database in the figure, where the primary keys are underlined. Give an expression in SQL for each of the following queries.

   1. Find the names of all employees who work for First Bank Corporation.
   2. Find all employees in the database who live in the same cities as the companies for which they work.

   1.  select person_name 
   	from works 
   	where company_name="First Bank Corporation"; 
   2.  select person_name 
   	from employee,works,company 
   	where employee.person_name=works.person_name and company.company_name=works.company_name and employee.city = company.city;
   

4.1

1. 现有如下关系：患者（患者编号，患者姓名，性别，出生日期，所在单位）医疗（患者编号，医生编号，医生姓名，诊断日期，诊断结果）其中，医疗关系中的外码是 [患者编号] 。

2. 如果我们用“ CREATE TABLE SC (S# CHAR（6） NOT NULL，C# CHAR（3） NOT NULL，SCORE INTEGER，NOTE CHAR（20));”创建关系SC。那么，向SC中插入 [('200823', '101', NULL, NULL)] 时，可以被执行。

3. 

   Consider the insurance database in the figure, where the primary keys are underlined. Construct the following SQL queries for this relational database.

   1. Find the total number of people who owned cars that were involved in accidents in 1989.

   2. Add a new accident to the database; assume any values for required attributes.

   3. Delete the Mazda belonging to “John Smith”.

   1.  select count(driver_id)
   	from participated natural join accident
   	where `date` like "1989%"
   2.  insert into participated(driver_id,car,report_number,damage_amount)
   	values('1234','3456', '003', 15)
   3.  delete from owns
   	where driver_id=(
           select driver_id
           from person
           where name='John Smith')
       and license=(
           select license
           from car
           where model='Mazda');
   

4. 

   We expect the constraint “an instructor cannot teach sections in two different classrooms in a semester in the same time slot” to hold.

   1. Write an SQL query that returns all (instructor, section) combinations that violate this constraint.

   2. Write an SQL assertion to enforce this constraint.

   1.  select ID,name,section_id,semester,`year`,time_slpt_id,count(distinct building,room_number)
   	from instructor natural join teaches natural join section
   	group by ID,name,section_id,semester,`year`,time_slot_id
   	having count(building,room_number)>1;
   2.  create assertion check not exist(
       	select ID,name,section_id,semester,`year`,time_slpt_id,count(distinct building,room_number)
   		from instructor natural join teaches natural join section
   		group by ID,name,section_id,semester,`year`,time_slot_id
   		having count(building,room_number)>1);
   

4.2

1. Consider a view v whose definition references only relation r.

   1. If a user is granted select authorization on v, does that user need to have select authorization on r as well? Why or why not?

   2. If a user is granted update authorization on v, does that user need to have update authorization on r as well? Why or why not?

   3. Give an example of an insert operation on a view v to add a tuple t that is not visible in the result of select * from v. Explain your answer.

   1. 不需要，拥有视图的查询权限就可以间接的对视图所来源的关系进行查询，并且视图权限仅来源于r

   2. 不需要，拥有视图的更新权限就可以间接的对视图所来源的关系进行查询，并且视图权限仅来源于r

   3. insert into v values t v中包含r的主键或候选键

2. Suppose we have three relations r(A, B), s(B, C), and t(B, D), with all attributes declared as not null.

   1. Give instances of relations r, s, and t such that in the result of

      (r natural left outer join s) natural left outer join t

      attribute C has a null value but attribute D has a non-null value.

   2. Are there instances of r, s, and t such that the result of

      r natural left outer join (s natural left outer join t)

      has a null value for C but a non-null value for D? Explain why or why not.

   1. 如果r中仅有元组(1,'张三')，s中仅有元组('李四',9)，t中仅有元组('张三'，'A') 得到的元组是(1,'张三',NULL,'A')
   2. C和D要么同时为空要么同时不为空，当t中没有和s中C匹配的元组时，C和D同时为空，当t中有和s中C匹配的元组时，C和D同时不为空

5.1

1. Draw an ER diagram based on the following description: Suppose we have two entity sets, People and Email. Suppose we also use a relationship Owns, which connects these two entities. A person may own multiple email accounts, but an email account can only be owned by a single person.

   

2. Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted.

   Assume：

   1. doctors have attributes: dss#, name, specialization

   2. patients have attributes: patient_id, name, insurance, date-admitted, date-checked-out

   3. test have attributes: test_id, testname, date, time, result

   

5.3

1. Design a generalization–specialization hierarchy for a motor-vehicle sales company.
   The company sells motorcycles, passenger cars, vans, and buses. Justify your placement of attributes at each level of the hierarchy. Explain why they should not be placed at a higher or lower level.

   特化根据机动车的用油分为汽油车和柴油车

   

2. Construct appropriate tables for the E-R diagrams

   

   

6.1

1. If a relation R is in 3NF, R is also in BCNF. [否]

2. A schema in BCNF will be in 3NF as well. [是]

3. 设有关系模式 $R(A, B, C, D)$，函数依赖集：$F=\{A→B,B→C, D→B\}$，判断关系模式 $R$ 为 [都不是（3NF, BCNF）] 。

4. 在关系模式 $R$ 中，函数依赖 $X→Y$ 的语义是 [在 $R$ 的每一关系中，若两个元组的 $X$ 值相等， 则 $Y$ 值也相等] 。

5. The following table does not have an functional dependency $AB→CD$ :

   A	B	C	D	E
   1	3	2	2	1
   1	3	2	2	2
   3	1	1	5	2
   3	1	1	2	1
   4	1	1	1	3

   [是]

6. Given the snapshot of a table as below, we are certain that $AB →D$ is an functional dependency of the table.

   A	B	C	D
   1	3	2	2
   2	3	2	4
   3	1	3	6
   3	1	1	6

   [否]

7. A relation with two attributes, e.g., $R(A, B)$ , must necessarily be in BCNF. [是]

8. For a relation $R(A, B, C, D, E)$ , $A$ is a superkey if $A+ = \{A, B, C, D, E\}$. [是]

9. If $A→B$ and $C →D$ hold, then $AC → BD$ also holds. [是]

10. According to the dependency theory, it is possible for a relation not to have a key. [否]

11. Consider relation $R$ with five attributes $A,B,C,D,E$, and the following functional dependencies:

    $A →B$ ; $BC→D$; $D→E$ . Give the complete closure for $\{AC\}+$

    

12. Compute the closure of the following set $F$ of functional dependencies for relation schema $R = (A, B, C, D, E).$

    $A→BC$

    $CD→E$

    $B→D$

    $E→A$

    compute the canonical cover $Fc$.

    

6.3

1. Consider a relation with schema $R(A, B, C, D, E, G)$ and the set of functional dependency $F=\{ A→B，B→C，AD→G，D→E \}$ .

   1. Give all candidate keys of this relation, motivate.

   2. Give the closure of attribute sets $\{G\}$ ,$\{AD\}$ ,$\{CD\}$ and $\{BC\}$ ,respectively.

   3. Is this relation $R$ in BCNF？If it is not, decompose it into relations in 3NF.

   

2. Consider a relation with schema $R(A, B, C, D, E, F)$ and the set of functional dependency $F=\{E→ D，C →B, CE →F, B →A\}$ holds on relation $R$.

   1. Give all candidate keys of this relation, motivate.

   2. Is this relation R in 3NF? If it is not, decompose it into relations in 3NF.

   

作业 1

1. 

   Consider the relational database of the figure. Give an expression in the relational algebra to express each of the following queries:

   1. What are the appropriate primary keys?
   2. Given an expression in relational algebra to express each of the following queries.
      1. Find the names of all employees who live in city “Miami”.
      2. Find the names of all employees whose salary is greater than $100,000.
      3. Find the names of all employees who live in “Miami” and whose salary is greater than $100,000.
      4. Find the names of all employees who work for “First Bank Corporation”.
      5. Find the names and cities of residence of all employees who work for “First Bank Corporation”.
      6. Find the names, street address, and cities of residence of all employees who work for “First Bank Corporation” and earn more than $10,000.

   1. employee主键：person_name

      works主键：person_name

      company主键：company_name

      manages主键：person_name

      （前提：name没有重名）

   2. (1) $\Pi_{person\_name}{(\sigma_{city=\mathrm{Miami}}(employee))}$

      (2) $\Pi_{person\_name}{(\sigma_{salary>100000}(works))}$

      (3) $\Pi_{person\_name}{(\sigma_{city=Miami\ and\ salary>100000}(employee\bowtie w o r k s))}$

      (4) $\Pi_{person\_name}{(\sigma_{company\_name=First\ Bank\ Corporation}(works))}$

      (5) $\Pi_{person\_name,city}{(\sigma_{company\_name=First\ Bank\ Corporation}(employee\bowtie w o r k s))}$

      (6) $\Pi_{person\_name,street,city}{(\sigma_{company\_name=First\ Bank\ Corporation\ and\ salary>10000}(employee\bowtie w o r k s))}$

2. 

   1. What are the appropriate primary keys?
   2. Given your choice of primary keys, identify appropriate foreign keys.
   3. Consider the foreign key constraint from dept_name attribute of instructor to the department relation. Give examples of inserts and deletes to these relations, which can cause a violation of the foreign key constraint.
   4. In the instance of instructor, no two instructors have the same name, From this, can we conclude that name can be used as a superkey(or primary key) of instructor?
   5. Consider the following expressions, for each expression, explain in words what the expression does.
      1. $\sigma_{y e a r \geq 2009}( takes ) \bowtie student$
      2. $\sigma_{y e a r \geq 2009}( takes \bowtie student)$
      3. $\Pi_{ID, name,course\_id}(student\bowtie takes)$

   1. student主键：ID

      instructor主键：ID

      course主键：course_id

      section主键：course_id，sec_id，semester，year

      takes主键：ID，course_id，sec_id，semester，year

      advisor主键：s_ID

   2. section.course_id指向course.course_id

      takes.ID指向student.ID

      takes.course_id指向section.course_id

      takes.course_id指向course.course_id

      takes.sec_id指向section.sec_id

      takes.semester指向section.semester

      takes.year指向section.year

      advisor.s_id指向student.ID

      advisor.i_id指向instructor.ID

   3. 插入(10111,张三,10舍,20000)到instructor表，由于department表中没有10舍，所以违反了外键的约束；删除(2舍,浑南,20000),由于2舍还被student和instructor引用，所以违反了外键约束

   4. 不可以，现在没有重复的name不一定一直没有重名

   5. 第一条：选择出了2009届及以后的选择至少一门课的学生信息，包括ID,name,dept_name,credits,course_id,semester,year,grade;

   6. 第二条：与第一条相同

      第三条：将至少选择一门课的学生的ID,name,course_id查询出来

作业 2

Consider the relational database of the figure. Write the following queries in SQL, using the university schema.

1. Find the highest salary of any instructor.
2. Find all instructors earning the highest salary (there may be more than one with the same salary).
3. Find the enrollment of each section that was offered in Autumn 2009.
4. Find the maximum enrollment, across all sections, in Autumn 2009.
5. Find the sections that had the maximum enrollment in Autumn 2009
6. Find the names of those departments whose budget is higher than that of Philosophy. List them in alphabetic order.
7. *For each student who has retaken a course at least twice (i.e., the student has taken the course at least three times), show the course ID and the student’s ID.*Please display your results in order of course ID and do not display duplicate rows.
8. Find the IDs of those students who have retaken at least three distinct courses at least once (i.e., the student has taken the course at least two times).
9. find the names and IDs of those instructors who teach every course taught in his or her department (i.e., every course that appears in the course relation with the instructor’s department name). Order result by name.
10. Find the name and ID of each History student whose name begins with the letter ‘D’ and who has not taken at least five Music courses.
11. Find the ID and name of each instructor who has never given an A grade in any course she or he has taught. (Instructors who have never taught a course trivially satisfy this condition.)
12. Find the number of students in each section. The result columns should appear in the order “courseid, secid, year, semester, num”. You do not need to output sections with 0 students.
13. Find section(s) with maximum enrollment. The result columns should appear in the order “courseid, secid, year, semester, num”. (It may be convenient to use the with construct.)

参考答案：

1.  select max(salary)
	from instructor;
2.  select ID,name 
	from instructor 
	where salary = (
        select max(salary) 
        from instructor);
3.  select course_id,sec_id,count(ID)
	from takes
	where semester='Autumn' and year = 2009
	group by course_id,sec_id;
4.  select max(enrollment)
	from (
        select count(ID) as enrollment
    	from takes
    	where semester='Autumn' and year=2009
    	group by course_id,sec_id);
5.  with sec_enrollment as (
    	select course_id,sec_id,count(ID) as enrollment
    	from takes
    	where semester='Autumn' and year=2009
    	group by course_id,sec_id)
    select course_id,sec_id
    from sec_enrollment
    where enrollment=(
        select max(enrollment)
        from sec_enrollment);
6.  select d.dept_name 
	from department d 
	where d.budget>(
        select p.budget
        from department p
        where p.dept_name='Philosophy')
    order by dept_name asc;
7.  select id,course_id,count(*) as times
	from takes
	group by id,course_id
	having count(*)>2
	order by course_id;
8.  select t.id count(t.couse_id)
	from (
        select id,course_id,count(*)
        from takes
        group by id,course_id
        having count(*)>1)
  	as t(id,course_id,times)
    group by t.id
    having count(distinct t.course_id)>2;
9.  select i.id,i.name
	from instructor i
	where not exists(
        (select c,course_id
         from course c
         where c.dept_name=i.dept_name)
        except
        (select distinct course_id
         from instructor j natural join teaches t
         where i.id=j.id));
10. select s.id
	from student s
	where s.dept_name='history' and s.name like 'D%' and s.id in(
        select distinct ta.id
        from takes ta natural join course c
        where c.dept_name='music'
        group by ta.id
        having count(ta.id)<5);
11. select distinct i.id,i.name
	from instructor i natural join teaches t
	where(course_id,secid,semester,year) not in(
        select course_id,sec_id,semester,year
        from takes
        where grade='A');
12. #这个题好像被我给丢了
13. with num(coursed,secid,year,semester,num) as
		select course_id,sec_id,year,semester,count(distinct id)
		from takes
		group by course_id,sec_id,semester,year
		having count(distinct id)>0
		order by course_id,sec_id,year,semester,count(distinct id)
	select n.coursed,n.secid,n.year,n.semester,n.num
	from num n
	where n.num=(
        select max(m.num) 
        from num m);

12. select course_id,sec_id,year,semester,count(*)
	from SECTION natural join takes
	group by scourse_id,sec_id,year,semester;

第一章测验

1. 定义数据库模式、数据库结构以及数据特性等功能是通过 [数据定义语言DDL] 来实现的。
2. 数据冗余可能产生的问题是 [潜在的数据不一致性] 。
3. 下面关于数据库管理系统的论述中，正确的是 [应用程序只有通过数据库管理系统才能访问数据库] 。
4. 关系模型是 [用关系表示实体及联系] 。
5. 当数据库遭到破坏时，将其恢复到数据库破坏前的某种一致性状态，这种功能称为 [数据库恢复] 。
6. 能够对数据库逻辑层进行操作的人员是 [Database Administrator] 。
7. 目前，数据库系统广泛使用的数据模型是 [Relational model] 和 [Entity-Relationship data model] 。
8. Describe at least 3 tables that might be used to store information in a social network system such as WeChat(List tables with their corresponding attributes).

参考答案：

1. A users table containing users, with attributes such as account name, real name, age, gender, location, and other profile information.
2. A content table containing user provided content, such as text and images, associated with the user who uploaded the content.
3. A friends table recording for each user which other users are connected to that user. The kind of connection may also be recorded in this table.
4. A permissions table, recording which category of friends are allowed to view which content uploaded by a user. For example, a user may share some photos with family but not with all friends.

第二章测验

1. Any candidate key of a relation is a super key for that relation. [是]

2. All attributes of a relation together form a super key for the relation. [是]

3. As a query language, relational algebra is fully declarative– we simply declare what we want, but not how to compute, in formulating a query. [否]

4. Let's say you examined all the tuples in a relation at a particular time, and found that no two tuples have the same value for a particular attribute A. You can designate A to be a superkey for the relation. [是]

5. If we want to select some rows and columns in a relation, the relational algebra we can use for operations are [selection] and [projection] , respectively.

6. Write a relational algebra expression for each of the following questions, using the set of relations provided below.

   MovieFan(movieFanName, age, isStudent)

   Frequents(movieFanName, cinema)

   Likes(movieFanName, movieTitle)

   Shows(cinema, movieTitle, ticketPrice)

   1. Find all cinemas that shows movie whose price is less than $8.00.

   2. Find all cinemas that shows movies that James likes.3) Find all cinemas that are frequented by only students (Assume that isStudent is a Boolean attribute containing either true or false).

   3. Find the movieFanName of all students who frequent cinemas showing at least one movie they like

   1. $\Pi_{cinema}(\sigma_{ticketPrice<8}(Shows))$
   2. $\Pi_{cinema}((\sigma_{movieFanName=James}(Likes))\bowtie(Shows))$
   3. $\Pi_{cinema}(Frequents)-\pi_{cinema}(\sigma_{isStudent=false}(movieFan\bowtie Frequents))$
   4. $\Pi_{movieFanName}(\sigma_{isStudent=true}(movieFan\bowtie Frequents\bowtie Likes\bowtie Shows))$

线上期末考试题

Factory information management system

Design a database for a factory to provide to its managers to assist them in maintaining production records and parts inventory, and to assist factory in ordering managements. The features of factory are shown as follows.
• In the factory, each employee only works in one department and each department is led by one manager.
• Each employee has his own Employee_id. The factory needs to store their names, ages and job titles.
• Employees participate in the produce productions. And each production is managed by one employee.
• Each production has its own Production_id. The factory should register name, type and price of each production.
• The productions are produced according to the orders, which is ordered by the customers.
• In an order, it should indicate the details of productions ordered by customers, including the quantity and selling price, and this information can be found in the order details. In addition, the date of order and delivery also should be indicated in the order.
• When the productions are delivered to customers, the customer should pay for the receivable, which consists of order_id, receipt number, payment amount, payment date and the payer.
• To produce a production, it needs to use multiple parts, which are supplied by suppliers. And the production should indicate the number of parts in use.
• Each kind of part has its own Part_id. Besides, all the parts have names, types and origins. After the parts have been purchased, they will be store in the warehouses.
• Each warehouse has a name and a specific location, and they can be distinguished by their own Warehouse_ids. In the factory, each warehouse can store multiple parts and each part can be stored in different warehouses. The warehouse should register the quantity of stored parts.

1. Draw an ER diagram for the factory and add attributes if necessary. Be sure to mark the multiplicity of each relationship of the diagram (by keeping the edges undirected or providing an arrowhead or curved arrowhead for the edges). Decide the key attributes and identify them on the diagram. Please state all assumptions you make in your answers. (25 points)
2. Translate your ER diagram into relational schemas, following the methodology discussed in class. Merge relations where appropriate. Specify the key of each relation in your schema. (15 points)
3. Check whether your schema is in 3NF or not, if it is in 3NF explain the reason; if it is not in 3NF, decompose your schema and let it satisfy 3NF. (10points)
4. According the database you designed, use relation algebra to answer the following questions. (20 points)
1）Find the warehouses storing the part “bearing”; (5’)
2）Find the department managed by Jim; (5’)
3）Count the number of part “nail” stored in the whole warehouses;(5’)
4）Increase the price of productions by 10% which are made by Peter. (5’)

5. Write SQL statements to meets the requirement followed. (30 points)
1）Find the department with the most employees. (6’)
2）Find the customer ordered “steam turbine” in Mar. 25th, 2019;(6’)
3）Find the warehouse store the most kinds of parts;(6’)
4）Find the production used parts supplied by “Phoenix Corporation”; (6’)
5）Create a new order into system that Bob order 100 engines for $2500 at Apr. 1st, 2020 which while be delivered on Mar. 31st, 2021. (6’)

模拟题

ps：此部分答案可能有误

1. 单选题

1. Which one of the following is NOT TRUE for database language?

   A. Relational Algebra is a procedural database language.

   B. SQL is a declarative database language.

   C. SQL is both a declarative database language and a pure database language.

   D. SQL is both data definition language and data manipulation language.

2. Which one of the following is NOT TRUE for relational model?

   A. In a relation, a foreign key can be a subset of the primary key.

   B. In a relation, the primary key can be a subset of a candidate key.

   C. In a relation, a super key must contain a candidate key.

   D. In a relation, a candidate key can include multiple attributes.

3. Given relation schema $R1(A,B,C,D)$ and $R2(B,C)$, the schema of the relation as the result of $R1÷R2$ is

   A. $(A, R1.B, R1.C, R2.B, R2.C)$

   B. $(A)$

   C. $(A, B, C) $

   D. $(A, D)$

4. Which one of the following is NOT TRUE for SQL?

   A. Views may be defined in terms of other views.

   B. Result of where clause predicate is treated as false if it evaluates to unknown.

   C. Set operations automatically eliminate duplicates.

   D. ‘DELETE TABLE r’ deletes not only all tuples of $r$, but also the schema for $r$.

5. “Unique ( (mike, Null), (mike, Null) )” is evaluated to

   A. TRUE

   B. FALSE

   C. UNKNOWN.

   D. NULL

6. In “CREATE TABLE” statement, unique(A1, A2, …, Am) states that the attributes A1, A2, … Am form a

   A. Primary key

   B. Foreign key

   C. Candidate key

   D. Relation schema

7. If $R$ is a one-to-many relationship set from entity set $E_1$ to $E_2$, Which one of the following is TRUE?

   A. If $R$ has any descriptive attribute it can be moved to $E_1$ .

   B. The primary key of $R$ is the primary key of $E_2$.

   C. $E_1$ can be a weak entity set

   D. $E_2$ must totally participate in $R$

8. In the following statements about weak entity set, which is incorrect?

   A. Weak entity set is an entity set that does not have a super key

   B. The existence of a weak entity set depends on the existence of an identifying entity set

   C. Weak entity set is an entity set that does not have a foreign key

   D. Weak entity set must relate to the identifying entity set via a total, many to one relationship set

9. If and only if ( ), $K$ is a super key of $R$.

   A. $K\rightarrow R$

   B. $R\rightarrow K$

   C. $K\rightarrow (K-R)$

   D. $(R-K)\rightarrow K$

10. A decomposition of $R$ into $R_1$ and $R_2$ is lossless join if

    A. $R_1∩R_2 \rightarrow R_1\ is\ in\ F^+$

    B. $R_1∩R_2 = \Phi$

    C. $R_1∩R_2 \ne \Phi$

    D. $R_1-R_2 \rightarrow R_1$

答案：CBDDA ABCAA

2. 填空题

1. Database systems provide an abstract view of the data, which is achieved through 3 level of abstraction: physical level, logical level, and [view level] .
2. r and s are two relations. Suppose a tuple occurs 3 times in r and 5 times in s, then it occurs [3] times in the execution result of the SQL statement “r intersect all s”
3. Relation r has 100 tuples, among these tuples, only 2 have null values on attribute A, the result of the SQL statement “select count(*), count(A) from r” is [100,98] .
4. The grant statement is used to confer authorization, and the [revoke] statement is used to reclaim authorization.
5. Collection of operations that form a single logical unit of work in database system is called [transaction] .

3. 简答题

1. Briefly describe what is referencing constraint.
2. Briefly describe the following concepts about keys in a relational model: Super Key, Candidate Key, Primary Key.
3. In a bank database, if a loan can be borrowed by more than one customer, should we divide relation schema (customer_id, loan_number, amount), into two schemas (customer_id, loan_number) and (loan_number, amount)? Describe why.
4. Briefly describe the ACID properties of transactions.

4. 关系代数

Give the result of the following relational algebra expressions.

$R$ :

$S$ :

1. $R_1=\Pi_{B,C,200}(R)$

   B	C
   6	7	200
   2	3	200
   4	5	200
   7	9	200

2. $R_2=\sigma_{B>2}(R)$

   A	B	C
   a1	6	7
   a4	4	5
   a2	6	7
   a3	7	9

3. $R_3=R\bowtie S$

   A	B	C
   a1	6	7
   a2	2	3
   a1	2	3
   a2	6	7

4. $R_4=Ag_{sum}(B)$

   A	sum(B)
   a1	8
   a2	8
   a3	7
   a4	4

5. 完成SQL

Consider the following relations (the primary keys are underlined):

​ Students(snum:integer, sname:string, major:string, level:string, age:integer)

​ Faculty(fid:integer, fname:string, deptid:integer)

​ Class(cname:string, meets at:string, room:string, fid:integer)

​ Enrolled(snum:integer, cname:string)

​ The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class. Note that snum and cname in Enrolled should correspond with snum in Students and cname in Class respectively.

1. Write a SQL statement to create relation Class. Declare a primary key and foreign keys (if any) on this relation.

    create table Class (
   	cname char(10) not null,
   	meets_at char(20) not null,
   	room char(20) not null,
   	fid int(5) not null,
   	primary key(cname),
   	foreign key fid reference Faculty(fid));
   

2. Write a SQL statement to insert into the database the fact that the 22 year-old senior CS student ‘Kobe Bryant’, with snum 111, is enrolled in class CS411. (hint: Both Students and Enrolled tables need to be updated.)

   insert into Students(snum,sname,major,level,age) 
   values (111, ‘Kobe Bryant’,’CS’,’senior’,22);
   insert into Enrolled(snum,cname) values (111,CS411);
   

3. Write a SQL statement to delete all the classes taught by “Joe Smith”.

   delete from Class 
   where class.fid in (
       select fid 
       from Faculty 
       where fname=’Joe Smith’)
   

4. Create a view BusyFaculty that records the ids and names of faculties who teach more than 3 classes.

   保存的资料中未留下答案

5. Find the names of all students that enroll in a class where students meet in room R128 (i.e., Class.room = R128) or a class in which five or more than five students enroll.

   保存的资料中未留下答案

6. Find the names of all students who are enrolled in two classes that meet at the same time

   保存的资料中未留下答案

6. E-R 图

The club Travel-Often-And-A-Lot organizes shorter and longer tours for its members. Help them to make a model of their mini world.

Travel-Often-And-A-Lot has members. Each member is represented by her/his full name, address, and birth date.

Some members belong to the board of Travel-Often-And-A-Lot. Some members are organizers (of tours). Organizers must be stored with their cell phone number so that they can be reached anytime. Organizers organize tours. Sometimes a tour is organized by several organizers.

Each tour is denoted by a name, e.g. “Museums of Paris, 2004” or “Iceland, 2005”. Tours can take place multiple times. “Museums of Paris, 2004”, for instances, takes place twice: May 22nd to May 29th, 2004 and June 5th to June 12th, 2004. The cost of a tour depends on the date, e.g. “Museums of Paris, 2004” was cheaper in May than in June. Each travel – such as “Museums of Paris, 2004” at June 5th to June 12th, 2004 – is lead by one organizer members participate in travels.

Travel-Often-And-A-Lot wants to keep track of the payments made by its members. A payment can e.g. be the annual club fee, a donation, etc. but also the payment for a travel. Mind the subtle distinction between tour and travel.

1. Create an E-R model that fulfill above requirements.

   

2. Translate the E-R model into relation schemas.

   Member (MID, Name, Birthdate)

   Fee (Date, Description, PID, MID)

   Board (MID)

   Organizer (MID, CellPhone)

   Tour (TID, Name)

   Travel (TravelID, TID, MID, Cost, Startdate, Enddate)

   FeeforTravel (PID, TravelID)

   Orginize (MID, TID)

   Participate (MID, TravelID)

7. 规范化和模式设计

Consider a relation with schema $R=\{A, B, C, D, E, F\}$ and $F=\{AB\rightarrow CD; A\rightarrow D; D\rightarrow AE; E\rightarrow F\}$ holds on $R$ .

1. Give all candidate keys of this relation, motivate. (3 points)
   $AB, BD$

2. Indicate all extraneous attributes in F, motivate. (3 points)
   $D$ in $AB\rightarrow CD$

3. Is this relation in 3NF？If it is not, decompose it into relations in 3NF. (6 points)

   Not in 3NF.

   $F_c = \{AB\rightarrow C,A\rightarrow D,D\rightarrow AE, E\rightarrow F\}$

   $R$ can be decomposed into $\{A,B,C\}, \{A,D,E\},\{E,F\}$