→ tedcat:看到一大堆英文敘述就頭昏 推140.112.244.180 04/23
1. (30%) Suppose that we are going to create a database for a university.
The specification of the database is shown as follows.
i) The university is organized into departments, each of which has a
unique name, a unique number, and a particular faculty member who
chairs the department. We keep track of the starting date when
he/she began chairing the department. A department may own several
buildings.
ii) Each faculty member may conduct a number of projects, each of which
has a unique name, a unique number, and a single location. Most of
projects are controlled by one department; however, some of them may
be controlled by several departments.
iii) We store each faculty member's name, identity card number, address,
phone, salary, sex, and birthday. A faculty member may join several
departments. We keep track of the number of hours per week that a
faculty member works on each project. We also keep track of the
direct supervisor of each faculty member.
iv) Each faculty member may lead some students to conduct his/her
projects. For each student, we store his/her name, identity card
number, address, phone, salary, sex, and birthday, the department
he/she belongs to. Each student must take at least three courses.
v) Each faculty member may teach several courses, each of which has a
unique name, a unique number, and a single classroom. For each course,
the faculty member may use several textbooks for the class. For each
textbook, we store its title, publisher, location of the publisher and
publishing year.
vi) We would like to keep track of the dependents of each faculty member
for insurance purpose. We keep's each dependent's first name, sex,
birthday, and relationship to the faculty member.
Answer the following questions according to the above specification.
(a) Show the ER schema for this university database application.
(b) Map the ER schema in (a) into the relational database schema.
2. (21%) Answer the following questions with respect to the relational
database schema in 1.(b).
(a) Write a relational algebra to retrieve the names and phones of the
faculty members who work in IM department.
(b) Write a relational algebra to retrieve the name of the chairperson
of the IM department.
(c) Write a relational algebra to retrieve the names of departments which
control all the projects that department D controls.
(d) Write a relational algebra to retrieve the names of faculty members
who conduct two or more projects.
(e) Is it possible to write a relational algebra to retrieve all
supervisors of faculty member F? Why/why not?
(f) Write an SQL to retrieve the department name, the number of faculty
members, and their average salary for each department.
(g) Write an SQL to list the names of chairpersons who have at least one
dependent.
3. (10%) For a delete operation, what constraints could it violate? What
actions should we take to process such violations?
4. (15%) Let F={Book_titleRPublisher, Book_type; Book_typeRListprice;
AuthornameRAuthor-affiliation; PublisherRPublisher_location;
{Book_title,Publisher}RListprice; {Book_title, Version}RPublishing_year}.
(a) Find the minimal cover of F.
(b) Find a decomposition of R={Book_title, Version, Publisher, Book_type,
Listprice, Authorname, Author-affiliation, Publisher_location,
Publishing_year} into 3NF, having a lossless join and preserving
dependencies.
(c) Find a primary key for each decomposed relation in (b).
5. (10%) In the relational model, a relation is defined as a set of
tuples. Mathematically, elements of a set have no order among them;
hence tuples in a relation don't have any particular order. Can a
relation be used to model ordered data? Why/why not?
6. (14%) A relation schema R is in 3NF if, whenever a nontrivial
functional dependency XRA holds in R, either (a) X is a superkey
of R, or (b) A is a prime attribute of R. The only difference between
BCNF and 3NF is that condition (b) is absent from BCNF. What are the
major concerns of both normal forms?
--
※ 發信站: 批踢踢實業坊(ptt.csie.ntu.edu.tw)
◆ From: 140.112.242.210