hw4

tags db database

2023 database-systems HW4.pdf

1

$\{A,B\} \rightarrow \{C\}\\ \{A\}\rightarrow \{D,E\}\\ \{D\} \rightarrow \{I,J\}\\ \{B\}\rightarrow \{F\}\\ \{F\} \rightarrow \{G,H\}\\$

1.a

$A,B$ is Key because

• $A,B$ can get $C$
• $A$ can get $\{D,E,I,J\}$
• $B$ can get $\{F,G,H\}$

1.b

• 2NF: no partial dependcy

$\{A,B\} \rightarrow \{C\}\\ \{A\}\rightarrow \{D,E\}\\ \{D\}\rightarrow \{I,J\}\\ \{B\}\rightarrow \{F\}\\ \{F\} \rightarrow \{G,H\}\\$

1.c

$\{A,B\} \rightarrow \{C\}\\ \{A\}\rightarrow \{D,E\}\\ \{D\} \rightarrow \{I,J\}\\ \{B\}\rightarrow \{F\}\\ \{F\} \rightarrow \{G,H\}\\$

2

FD1={Course_no} → {Offering_dept, Credit_hours, Course_level}

FD2={Course_no, Sec_no, Semester, Year} → {Days_hours, Room_no, No_of_students, Instructor_ssn}

FD3={Room_no, Days_hours, Semester, Year} → {Instructor_ssn, Course_no, Sec_no}

2.a

1NF because:

• FD1 have partial function since Course_no is not key

2.b

• {Course_no, Sec_no, Semester, Year}
• {Room_no, Days_hours, Semester, Year}

2.c

use {Course_no, Sec_no, Semester, Year} as PK

loss FD3 R1 = {Course_no, Offering_dept, Credit_hours, Course_level}

R2 = {Course_no, Sec_no, Semester, Year, Days_hours, Room_no, No_of_students, Instructor_ssn}}

is BCNF

3

• Caching: move disk data to RAM or SSD
• Indexing: use B-trees or hash indexes to speedup the time of searching
• organization of data on disk: keep related data on continuous block

4

4.a

4.b

4.c

4.c.i

4.c.ii

4.c.iii

4.c.iv

4.c.v

4.d

4.d.i

4.d.ii

4.d.iii

4.d.iv

4.d.v

5

• primary index: must be defined on an ordered key field.
• clustered index: must be defined on an order field (not keyed) allowing for ranges of records with identical index field values.
• secondary index: is defined on any non-ordered (keyed or non-key) field.