第1页 / 共8页
第2页 / 共8页
第3页 / 共8页
第4页 / 共8页
第5页 / 共8页
第6页 / 共8页
第7页 / 共8页
第8页 / 共8页
2020春季（数据库系统及安全）期中考试试卷_20200529-答案-发学生.pdf
四川大学期中考试试题及答案
题目全为非标准题，答案仅供复习使用
课程号：314038030
学号：
课序号：01、02、03
姓名：
课程名称：数据库系统及安全
班级：
1
(20%)
2
(28%)
3
(27%)
4
(25%)
卷面
成绩
题
得
号
分
共4大题，共3页
1
(ISBN, title, edition, year)
(copyNo, ISBN, available)
(borrowerNo, borrowerName, borrowerAddress)
(copyNo, dateOut, dateDue, borrowerNo)
(Total marks: 20) The following tables form part of a Library database held in an RDBMS:
Book
BookCopy
Borrower
BookLoan
Where
Book
BookCopy
Borrower
BookLoan
contains details of book titles in the library and the ISBN is the key.
contains details of the individual copies of books in the library and copyNo
is the key. ISBN is a foreign key identifying the book title.
contains details of library members who can borrow books and borrowerNo
is the key.
contains details of the book copies that are borrowed by library members
and copyNo/dateOut
the key. borrowerNo is a foreign key
identifying the borrower.
forms
(1)
(Marks: 3) List all book titles published in the year 2012.
SELECT title
FROM Book
WHERE year = ‘2012’;
(Marks: 3) List all copies of the book title “Lord of the Rings” that are available for
(2)
borrowing.
SELECT copyNo
FROM BookCopy bc, Book b
WHERE bc.ISBN = b.ISBN AND bc.available = ‘Y’AND title = ‘Lord of the Rings’;
第 1 页 共 8 页
(Marks: 4) List the names of borrowers who currently have the book title “Lord of the
(3)
Rings” on loan.
SELECT borrowerName
FROM Borrower bw, Book b, BookCopy bc, BookLoan bl
WHERE bw.borrowerNo = bl.borrowerNo AND bl.copyNo = bc.copyNo
AND bc.ISBN = b.ISBN AND bc.available = ‘N’AND title = ‘Lord of the Rings’;
(Marks: 3) How many times has the book with ISBN “0-321-52306-7” been
(4)
borrowed?
SELECT COUNT(*)
FROM BookCopy bc, BookLoan bl
WHERE bc.copyNo = bl.copyNo AND ISBN = ‘0-321-52306-7’;
(5)
(Marks: 3) List the details of borrowers who currently have books overdue.
SELECT borrowerName, borrowerAddress
From Borrower bw, BookLoan bl
WHERE bw.borrowerNo = bl.borrowerNo and dateDue > today’s date;
(6)
(Marks: 4) List detailing how many times each book title has been borrowed.
SELECT ISBN, COUNT(*)
FROM BookCopy bc, BookLoan bl
WHERE bc.copyNo = bl.copyNo
GROUP BY ISBN;
第 2 页 共 8 页
2
(Total marks: 28) You are required to create a conceptual data model of the data
requirements for a company that specializes in IT training. The Company has 30 instructors
and can handle up to 100 trainees per training session. The Company offers five advanced
technology courses, each of which is taught by a teaching team of two or more instructors.
Each instructor is assigned to a maximum of two teaching teams or may be assigned to do
research. Each trainee undertakes one advanced technology course per training session.
Identify the main entity types for the company. Identify the main relationship types and
specify the multiplicity for each relationship. State any assumptions you make about the
data. Draw a single ER diagram to represent the data requirements for the company.
写明分析解释
第 3 页 共 8 页
3
(Total marks: 27) The table shown in Figure 1 lists dentist/patient appointment data. A
patient is given an appointment at a specific time and date with a dentist located at a
particular surgery. On each day of patient appointments, a dentist is allocated to a specific
surgery for that day.
Figure 1
(1)
(Marks: 3) The table shown in Figure 1 is susceptible to update anomalies. Provide
examples of insertion, deletion, and update anomalies.
The student should provide examples of insertion, deletion and update anomalies using the
data shown in the table. An example of a deletion anomaly is if we delete the details of the
dentist called ‘Helen Pearson’, we also loose the appointment details of the patient called
‘Ian MacKay’.
(2)
(Marks: 7) Identify the functional dependencies represented by the data shown in
the form in Figure 1. State any assumptions you make about the data shown in this
table.
写明分析解释，答案如下图 3NF 转化
(3)
(Marks: 11) Describe and illustrate the process of normalizing the table shown in
Figure 1 to 3NF. Identify the primary, alternate, and foreign keys in your 3NF
relations.
The student should state any assumptions made about the data shown in the table. For
example, we may assume that a patient is registered at only one surgery. Also, a patient may
have more than one appointment on a given day.
第 4 页 共 8 页
(4)
(Marks: 6) Determine whether these relations are also in BCNF. If not, transform
the relations that do not conform into BCNF.
The only relations that may violate BCNF are those that have more than one candidate key.
Therefore we need only re-examine the Appointment relation, which has (staffNo, aDate,
aTime)
as a PK and (patNo, aDate, aTime) as an alternate key. This relation contains the
following functional dependencies:
第 5 页 共 8 页
The presence of fd5 does not break BCNF because (patNo, aDate, aTime) is a candidate key
for this relation. Hence the Appointment relation is in BCNF.
As the other relations shown in the answer for Exercise 14.15 have only one candidate key,
they must also be in BCNF.
第 6 页 共 8 页
4
(Total marks: 25) The ER diagram in Figure 2 shows only entities and primary key
attributes. Answer the following questions with reference to how the ER model in Figure 2
maps to relational tables. State detailed analysis to support your answers.
Figure 2
(1)
(Marks: 3) How many relations will represent the ER model?
6 个，并写明分析解释。
(2)
(Marks: 3) How many foreign keys are mapped to the relation representing X?
2 - The primary keys of entities X and D as they have a 0ne-to-many relationship with X
and they are on the one side and therefore send a copy of their primary keys to X. Once
mapped to X – the copies of primary keys are referred to as foreign keys.
(3)
(Marks: 3) Which relation(s) will have no foreign key?
Relations A, C and D，并写明分析解释。
(4)
(Marks: 12) Derive relations for the following conceptual data model in Figure 2.
Relation A
Relation B
Relation C
Relation CX (Representing the many-to-many relationship between entities C and X)
Relation XE (Entity X and E merge as there is a one-to-one relationship with total (i.e. full)
第 7 页 共 8 页
participation relating these entities).
Relation D
(Marks: 4) If the cardinality for each relationship is changed to one-to-one with total
(5)
participation for all entities; how many relations would be derived from this version of the
ER model?
1 (Relation ABCXDE)，并写明分析解释。
第 8 页 共 8 页
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
