CSE 444 Midterm

Autumn 2001

Total number of points: 100 

1.      LivingBeing(id, name)
Human(lbID, phone)
Pet(lbID, tag)
Doctor(lbID, licence, pager)
HumanDoctor(licence)
PetDoctor(licence)
hVisit(lbID, licence, date)
pVisit(tag, ownerLbID, caregiverLbID, licence, date)

CREATE TABLE LivingBeing (id CHAR(10) PRIMARY KEY,
                                                     name CHAR (50)
);
CREATE TABLE Human(lbID CHAR (10) PRIMARY KEY,
                                            phone CHAR(20),
                                            FOREIGN KEY lbID REFERENCE LivingBeing(id)
);
CREATE TABLE  Pet(lbID CHAR (10) UNIQUE,
                                      tag CHAR(30) PRIMARY KEY,
                                      FOREIGN KEY lbID REFERENCE LivingBeing(id)
);
CREATE TABLE Doctor(licence CHAR(20) PRIMARY KEY,
                                           lbID CHAR (10) UNIQUE
                                           FOREIGN KEY lbID REFERENCE Human(lbID)
);
CREATE TABLE HumanDoctor(licence CHAR (20) PRIMARY KEY,
                                                FOREIGN KEY licence REFERENCE Doctor(licence)
);
CREATE TABLE PetDoctor(licence CHAR (20) PRIMARY KEY,
                                                FOREIGN KEY licence REFERENCE Doctor(licence)
);
CREATE TABLE hVisit (lbID CHAR (10) REFERENCE Hman(lbID),
                                          licence CHAR(20) REFERENCES HumanDoctor(licence),
                                          date DATE
);
CREATE TABLE pVisit (tag CHAR (30) REFERENCES Pet(tag),
                                          owner CHAR(10) REFENCES Human(lbID),
                                          caregiver CHAR(10) REFERENCES Human(liID),
                                          licence CHAR(20) REFERENCE PetDoctor(licence)
                                          date DATE
);

2.      Answers:

a.       Keys are: ABC, BD

b.      AD ŕ C violates BCNF, so we decompose in R1(A,C,D), R2(A, B, D).  This is now in BCNF

c.        

A

B

C

D

a

b1

c

d

a

b2

c

d

 

 

 

 

 

 

 

 

 

 

 

3. a.



     P­name(File)  -  P­name(Join(sname=”Smith”(User),
                                              Join(stype=”owner”(Relationship),
                                                       File)))

 

 

   b. i
                select x1.name
                from User x1, Relationship y1, User x2, Relationship y2, File z
                where x1.login =  y1.uLogin AND y1.type=”writePermission” AND
                           y1.fName = z.name AND

                           x2.login = y2.uLogin AND x2.name = “Mary” AND
                           y2.type = “owner” AND
                           y2.fName = z.name AND
                           x1.office = x2.office

 

      ii.
               select User.name, count(File.name), sum(File.size)

               from User, Relationship, File

               where User.login = Relationship.uLogin AND Relationship.type = “owns”
                        AND Relationship.fName = File.name

               group by User.name

               having sum(File.size) > 5000000

                



     iii  INSERT INTO Relationship(uLogin, fName, type)
               SELECT x.login AS uLogin, z.name as fName, “readPermission” as type
                FROM  User x, Relationship y, File z
               WHERE x.login = y.uLogin AND y.fName = z.name
                              AND y.type = “writePermission”
                              AND  not exists (SELECT *
                                                          FROM Relationship y2
                                                          WHERE x.login=y2.uLogin AND
                                                                         y2.fName = z.name AND
                                                                          y2.type = “readPermissions”)
          

iii.                  select x.name
from User x
where not exists (select y.name
                             from Relationship z, File y
                             where x.login=z.uLogin AND z.fName = y.name AND
                                        z.type = “readPermission” AND
                                        not exists (select *
                                                          from Relationship u, User v
                                                          where v.name = “Smith” AND
                                                                     u.uLogin = v.login AND
                                                                     u.type = “owner” AND
                                                                     u.fName = y.name))

 

]

4.
a
<!ELEMENT course (name, homework*)>
<!ELEMENT homework (number, points, turnin*)>
<!ELEMENT turnin (student, grade)>
<!ELEMENT student (name)>
<!ELMENET name (#PCDATA)>
<!ELEMENT number (#PCDATA)>
<!ELEMENT grade (#PCDATA)>
<!ELEMENT points (#PCDATA)>


b.


<!ELEMENT student (name, turning*)>
<!ELEMENT turning  (grade, homework)>
<!ELEMENT homework (course, number, points)>
<!ELEMENT course (name)>
(same #PCDATAs)