CSc 4402        Homework 1

Due 9/12

            a. Describe 4 important advantages of a DBMS over a traditional file processing system for managing data (Hint – look at section 1.2 of the text)

            b. Define physical vs. logical data independence, DBMS, DBA, DB, data integrity, data inconsistency, data abstraction, physical versus logical schema, data model, data dictionary (define all terms)

            c. What problems are caused by the design in the table in Fig. 1.5 on p. 19 of the text?  What are some of the languages involved with a DBMS?

            d. Consider the one-table (called CSC4402) relational database (also called CSC4102) with attributes: (S#, Name, Dept, Program, City) below, where S# is the primary key and the records are shown below.  Write SQL on paper (Chap 1, p. 13 of text and Chap. 3, pp. 103-5 of text) to do the following: i) retrieve S# and Name and Program for all students from the ‘Math” department; ii) Change the department for student ‘S4’ to ‘CSC’; iii) Remove all students from ‘Houston’; iv) Add a new record for student ‘S9’ who has the name  of ‘Joyce’ and department is ‘PHYS’ and is in the ‘Ph.D.’ program and is from ‘New Orleans’

S#

Name

Dept

Program

City

S1

Mary

CSC

Ph.D.

Baton Rouge

S2

Joe

MATH

Ph.D.

Houston

S3

John

CHEM

M.S.

Baton Rouge

S4

Laura

PHYS

Ph.D.

Houston

S5

Mike

CSC

B.S.

Dallas

S6

David

ECE

M.S.

New Orleans

S7

James

MATH

M.S.

Dallas

See if you can create the database and the queries in Access or MySQL for extra credit.

 

CSc 4402        Homework 2

Due 9/19

            a. Write relational algebra statements (not SQL) for the following queries with respect to the database below (primary keys are italicized and underlined): i) get S# and Sname for ‘New Orleans’ students in the ‘CSC’ department; ii) Get C# and Cname for courses taken by a student in the ‘CSC’ department; iii) Get S# for students either from ‘Baton Rouge’ or taking course ‘C9’ (hint: inclusive or); iv) Get S# and T# so that both the student and teacher are from the same city and that student is taking a course from that teacher; and v) Get T# for teachers who have taught a course that is not taken by any student in the ‘MATH’ department (Note: department names refer to the DEPT attribute rather than Dname and course numbers refer to the C# attribute).

            D:

(DEPT Dname Budget)

for departments

T:

(T# Tname City Dept Degree)

for teachers

S:

(S#, Sname, City Dept Degree)

for students

C:

(C# Cname Dept T#)

for courses

E:

(S# C# Grade)

for enrollments

            b. Exercise question 2.5 on p. 72 of text

 

 

 

CSc 4402        Homework 3

Due 10/10

            a. Consider the relational schema for the Professor-Student database below, describing professors, students, and the relationship ‘serving-on-committee’ between the professors and the students.  Primary key fields are italicized and underlined.  The data type for the Hours attribute in the Com table is SMALLINT, while all other attributes have the data type CHAR.  Note that the Major_Pnum attribute denotes the Pnum value for the student’s major professor (and is thus a foreign key in the table St).  Assume that every professor serves on at least one committee and every student has a committee of at least one professor.  Also assume that the student’s major professor must be on his/her committee.

Prof:

(Pnum Pname City Dept)

for professors

St:

(Snum Sname City Dept Degree Major_Pnum)

for students

Com:

(Snum Pnum Hours)

for committees

Write CREATE TABLE statements to create this database (pay attention to primary and foreign key clauses) for this schema.  Provide sample data for each table (write at least five records for each).  Write SQL statements for the following queries: i) Get Snum and Sname for ‘Ph.D.’ students where there is at least one professor from the ‘CSC’ department serving on that student’s committee; ii) Get Pnum and City and total hours and number of committees for professors from ‘CSC’; iii) Get Snum and Pnum pairs for professors serving on a student’s committee where both are from the same department but from different cities; iv) Get Pnum for professors from either ‘MATH’ or who serve on a committee of a student from ‘New Orleans’; v) Get Sname for students from ‘Baton  Rouge’ where there are at least 4 professors from that student’s department serving on that student’s committee; and vi) Get Snum and Dept for students whose major professor is from ‘Houston’.

 

CSc 4402        Homework 4

Due 10/29

            Consider the relational schema for a company and its employees with projects and training courses.  Of course, the employees participate in the projects and enroll in the courses.

CM

(Division Dept Budget)

company

EM

(SS# Name City Phone# Dept Salary)

employee  (employee, home city, department)

PR

(P# Director_SS# City Dept)

project (project, project director, city location, and department conducting the project)

EP

(P# S#)

employees in project

CR

(C# Cname Teacher_SS#)

course

ER

(C# SS# Grade)

enrollment

The data type for Budget, Grade, and Salary is INTEGER, while all other attributes have data type CHAR.  Each department has at least one employee and conducts zero or more projects.  An employee can take zero or more courses and work in zero or more projects (including projects conducted in other departments).  Each project has at least one employee working on it and each course has at least one employee taking it.  Note that fields marked SS# refer to Social Security numbers.  Write SQL statements for the following: i) Get (distinct) names for employees who are both teachers and project directors; ii) Get SS# for each employee from ‘Baton Rouge’ along with the average grade and number of courses taking (show employees who have not taken any courses, too); iii) Get (distinct) departments such that some employee in the department in question takes any course; iv) Get department and budget and total number of projects for every department – arrange the list in descending order of the budget (include departments with no projects in the list); v) Get (distinct) names for employees who take a course taught by a teacher in a different city; vi) Get P# for projects in which employees from every department in the company have participated.  Feel free to use double negation or count.

 

CSc 4402        Homework 5

Due 11/14

            a. Question 6.2 on p. 256 in text

            b. Consider the following relation schema R(ABCDE) below with relation values (instances) given for the relation variable r(R):

A

B

C

D

E

A1

B1

C1

D1

E1

A1

B2

C1

D1

E1

A2

B2

C1

D2

E3

A2

B3

C3

D2

E2

  Consider the functional dependencies (fd)  F ={AàB, BàC, DàE, CDàE}.  For each  of these fd, determine whether or not this fd is satisfied by this relation value for r(R).  Explain your answers.  Note that r(R) can have many different relation values i) for an fd (say AàB) which is not satisfied by r(R) can we conclude that the schema does not satisfy this fd? and ii) can we conclude that the schema R satisfies an fd just because we observed one r(R) for schema R which satisfies this fd?

            c. Let R(ABC) be a relation with attributes A, B, and C.  Let F1 and F2 be sets of functional dependencies where F1 = {AàB, AàC} and F2 = {AàB, BàC}.  For example, R could represent information about a person with A being a SS#, B being an address, and C being a phone number, although this example should not color your answer.  Compute F+1 and F+2 (using the compact representation).  Are these two equivalent?  Suppose F3 = {AàB, BàA, BàC}; are F+1 and F+3 equivalent?

            d. Given the relation R(ABCDE)  and fd set F = {ABàC, CEàD, AàE}, determine whether each fd below is in F+ or not: ABàD, ACàD, AàB, and BEàD

            e. For relation R(ABCDE) and F = {ABàCD, AàB, BEàDA, EàD, CàD}, compute a canonical cover for F (show all intermediate steps of your derivation)

            f. Given relation R(ABCDEG) and F = {ABDàCE, BCàD, CDàE, DEàG, AàB}, compute a canonical cover for F (show all intermediate steps of your derivation)

 

 

 

 

 

CSc 4402        Homework 6

Due 11/28

            a. Let R(ABCDEG) be a relation with a fd set F = {AàBC, BCD à E, BàC, EàB}.  Compute a computational cover for F.  Compute an LLJ BCNF decomposition of R.  Is it possible to get a BCNF decomposition for RE that is both LLJ and fd-preserving?

            b. Let R(ABCDE) be a relation with F = {AàB, ABàC, EDàA, ABàD, CàB}.  Compute a computational cover for F.  Compute an LLJ and fd-preserving 3NF decomposition of R – show all intermediate steps.

            c. Answer true or false to the statements below and explain briefly your answers;

i) if a relation R has two candidate keys K1 and K2, then K1àK2

ii) any binary relation (relation with two attributes) is in BCNF

iii) given R(ABC) and F={AàBC, BCàA}, R is in BCNF

iv) given R(ABC) and F={AàB, BàC}, R is in 3NF

v)any relation R always has the same number of attributes in every candidate key

vi) given R(ABC) and F={AàC, BàC}, R is in 3NF

vii) if AàBC and BàDG, then AàG

viii) if AàB and BCàD then AàD

ix) from ABàCD, we can obtain ABàC and ABàD

x) from ABàCD, we can obtain AàC and BàC and AàD and BàD

xi) from ABàCD, we can obtain ABEàCD

xii) from ABàC and AàD and DàBE, we can obtain AàC

 

 

 CSc 4402       Homework 7

Due 12/5

            a. Consider the following precedence graph for a non-serial schedule consisting of transactions T0, T1, T2, T3, and T4:

            Is the corresponding schedule serializable?  Why?

 

b.  Consider transactions

            T0: read(A); read(B); if A=0 then B:=B+1; write(B).

            T1: read(B); read(A); if B=0 then A:=A+1; write(A).

Let the consistency requirement be (A=0 OR B=0), with A=B=0 as the initial values.  Show that every serial schedule consisting of T0 and T1 preserve the consistency of the database.  Show a concurrent (non-serial) schedule S for T0 and T1 which is not conflict serializable and draw the precedence graph for S.

c. Given the schedule below for transactions T1, T2, and T3, draw a precedence graph for the schedule.  Is this schedule conflict serializable?  Why?

T1

T2

T3

read(C)

 

 

write(C)

 

 

 

 

read(C)

 

read(B)

 

read(B)

 

 

 

write(B)

 

 

 

write(C)

 

 

read(C)

 

 

 

write(A)

 

 

write(B)

read(D)

 

 

 

write(C)

 

d. Given the transactions T1, T2, and T3 below, Add locking and unlocking statements to T1 and T2 and T3 according to the 2-phase locking protocol and show the modified transactions T’1, T’2 and T’3.  Show a concurrent schedule S for T’1, T’2 and T’3 such that S is deadlock free.  Draw the Wait-for graphs for S.  Show a concurrent schedule S’ for T’1, T’2 and T’3 such that S’ is deadlocked.  Draw the Wait-for graphs for S’.

T1

T2

T3

read(A)

read(C)

read(B)

read(B)

read(A)

write(B)

write(A)

write(A)

read(C)

write(B)

Write(C)

write(C)

read(D)

write(B)

 

write(D)