Transcript Document 7830710

Analysis of Midterm-Examination
1. Explain the following concepts of the ER data model:
Entity type and Entity type classification.
Attribute and attribute classification.
Constraint and constraint classification.
Entity type: the structure description of some kind of entities.
Entity type classification: strong entities and weak entities.
Attribute: a property of some kind of entities with the same
structure.
Attribute type classification: simple attribute, multi-valued
attribute, composite attribute, complex attribute, derived
attribute, and stored attribute.
Sept. 2004
91.3902 Yangjun Chen
1
Analysis of Midterm-Examination
Constraint: constraints on relationships between or among
entity types.
Constraint classification:
cardinality constraints: 1:1, 1:M, N:M.
Participation constraints: total constraint
partial constraint
Sept. 2004
91.3902 Yangjun Chen
2
Analysis of Midterm-Examination
2. Draw an ER-diagram to describe the following real world problem.
At a university, instructors offer courses during particular
semesters. Each instructor can teach some courses. Some courses
are taught during a specific semester and some instructors teach
during a specific semester. Each instructor has a name and a
number. Each course has a name and a number. Each semester can
be identified by the year and the season (spring, summer, fall and
winter) when the semester is held.
Sept. 2004
91.3902 Yangjun Chen
3
Analysis of Midterm-Examination
Taught_During
M
Iname
N
Instructor
N
M
Offers
M
Semester
P
Can_Teach
year
M
season
Offered_During
N
Course
N
Cnumber
Sept. 2004
91.3902 Yangjun Chen
4
Analysis of Midterm-Examination
3. Linear Hashing
- collision resolution strategy: chaining
- split rule: load factor > 0.7
- initially M = 4 (M: size of the primary area)
- hash functions: hi(key) = key mod 2i  M (i = 0, 1, 2, …)
- bucket capacity = 2
Trace the insertion process of the following keys into a linear
hashing file:
3, 2, 4, 1, 8, 14, 5, 10, 7, 24, 17, 13, 15.
Sept. 2004
91.3902 Yangjun Chen
5
Analysis of Midterm-Examination
The first phase – phase0
•when inserting the sixth record we would have
4
8
1
0
1
2
14
3
2
3
n=0 before the split
(n is the point to the
bucket to be split.)
•but the load factor 6/8= 0.75 > 0.70 and so bucket 0 must be
split (using h1 = Key mod 2M):
8
2
14
1
0
Sept. 2004
1
2
3
n=1 after the split
load factor: 6/10=0.6
no split
4
3
4
91.3902 Yangjun Chen
6
Analysis of Midterm-Examination
insert(5)
8
0
8
0
Sept. 2004
1
1
1
5
1
2
14
3
2
3
2
14
3
2
3
4
4
4
n=1
load factor: 7/10=0.7
no split
4
91.3902 Yangjun Chen
7
Analysis of Midterm-Examination
insert(10)
8
0
8
1
5
1
1
5
2
14
3
2
3
2
14
3
4
4
4
n=1
load factor: 8/10=0.8
split using h1.
overflow
10
Sept. 2004
91.3902 Yangjun Chen
8
Analysis of Midterm-Examination
8
0
1
1
2
14
3
2
3
4
4
5
5
overflow
10
n=2
load factor: 8/12=0.66
no split
Sept. 2004
91.3902 Yangjun Chen
9
Analysis of Midterm-Examination
insert(7)
8
1
2
14
3
4
n=2
load factor: 9/12=0.75
split using h1.
overflow
10
8
0
1
1
5
2
14
3
7
2
3
4
4
5
5
overflow
10
Sept. 2004
91.3902 Yangjun Chen
10
Analysis of Midterm-Examination
8
1
2
10
3
7
4
5
14
n=3
load factor: 9/14=0.642
no split.
insert(24)
8
Sept. 2004
1
2
10
3
7
4
91.3902 Yangjun Chen
5
14
11
Analysis of Midterm-Examination
8
24
1
2
10
3
7
4
5
14
n=3
load factor: 10/14=0.71
split using h1.
8
24
Sept. 2004
1
2
10
3
4
91.3902 Yangjun Chen
5
14
7
12
Analysis of Midterm-Examination
8
24
1
2
10
3
4
5
14
7
n=4
The second phase – phase1
n = 0; using h1 = Key mod 2M to insert and
h2 = Key mod 4M to split.
insert(17)
8
24
Sept. 2004
1
2
10
3
4
91.3902 Yangjun Chen
5
14
7
13
Analysis of Midterm-Examination
8
24
1
17
2
10
3
4
5
14
7
n=0
load factor: 11/16=0.687
no split.
insert(13)
8
24
Sept. 2004
1
17
2
10
3
4
91.3902 Yangjun Chen
5
14
7
14
Analysis of Midterm-Examination
8
24
1
17
2
10
3
5
13
4
14
7
n=0
load factor: 12/16=0.75
split bucket 0, using h2.
1
17
Sept. 2004
2
10
3
4
5
13
14
91.3902 Yangjun Chen
7
8
24
15
Analysis of Midterm-Examination
insert(15)
1
17
2
10
1
17
2
10
3
3
4
5
13
4
5
13
14
7
8
24
14
7
15
24
n=1
load factor: 13/18=0.722
split bucket 1, using h2.
1
17
Sept. 2004
2
10
3
4
5
13
91.3902 Yangjun Chen
14
7
15
8
24
16
Analysis of Midterm-Examination
4. Given the following B+-tree, trace the deletion sequence: 8, 3, 5, 6.
Here, we assume that each internal node can contain at most two
keys and each leaf node can contain at most two value/point pairs.
3
2
1
Sept. 2004
2
5
3
5
6
91.3902 Yangjun Chen
8
17
Analysis of Midterm-Examination
3
2
1
2
5
3
5
6
3
1
1
Sept. 2004
5
2
5
91.3902 Yangjun Chen
6
18
Analysis of Midterm-Examination
3
1
1
2
2
Sept. 2004
6
5
1
1
5
1
6
91.3902 Yangjun Chen
1
2
19
Analysis of Midterm-Examination
5. Given the relation schemas shown in Fig. 2, construct expressions
(using relational algebraic operations) to evaluate the following query:
Find the names of employees who work on all the projects controlled by a
department that controls a project ’Web-DB’ and some other projects. Each
project is controlled by one department. But each department can control more
than one projects.
EMPLOYEE
fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno
DEPARTMENT
Dname, dnumber, mgrssn, mgrstartdate
PROJECT
WORKS_ON
Pname, pnumber, plocation, dnum
Sept. 2004
91.3902 Yangjun Chen
Essn pno, hours
20
Analysis of Midterm-Examination
DN Dnum(Pname = ‘Web-DB’(PROJECT))
DEPT_P PNUMBER(DNDN.Dnum = PROJECT.Dnum(PROJECT))
EMP_PNOS  ESSN,PNO(WORK_ON)
SSNS EMP_PNOS : DEPT_P
RESULT  FNAME, LNAME(SSNS * EMPLOYEE)
Sept. 2004
91.3902 Yangjun Chen
21
Analysis of Midterm-Examination
6. Given the relation schemas as shown in Fig. 2, construct a SQL
clause to evaluate the following query:
Find department names, and the number of employees in each
department, who take part in at least one project.
SELECT Dname, count(distinct SSN)
FROM DEPARTMENT, EMPLOYEE, WORKS_ON
WHERE DNUMBER = DNO AND
SSN = ESSN
Group By Dname
Sept. 2004
91.3902 Yangjun Chen
22