Deprecated: The each() function is deprecated. This message will be suppressed on further calls in /home/zhenxiangba/zhenxiangba.com/public_html/phproxy-improved-master/index.php on line 456
this file contains the SQL code, a lot of comments about the hard queries and alternative solutions, and the output from execution. ---------------------------------------------------------------------- SQL code and comments ---------------------------------------------------------------------- Q1. delete from OtherTrip delete from DailyCommute delete from Owns delete from Car delete from OptionalInfo delete from Lives delete from ResidenceAddr delete from CampusAddr delete from Address delete from Student delete from FacultyStaff delete from Usr Q2. insert Usr values('u1','passwd1','Johnson','Mary','6311112222','mary@yahoo.com') insert Usr values('u2','passwd2','Smith','John','1112222222','john@yahoo.com') insert Usr values('u3','passwd3','Brown','Bill','1112223333','bill@yahoo.com') insert Usr values('u4','passwd4','Kennedy','Lisa','1112224444','lisa@yahoo.com') insert Student values('u1','Biology','freshman') insert Student values('u2','Computer Science','senior') insert FacultyStaff values('u3','Computer Science','professor') insert FacultyStaff values('u4','music','administrator') insert Address values(1) insert Address values(2) insert CampusAddr values (1,'Steeley','A101') insert ResidenceAddr values (2,'East Avenue','Good Town','NY','12345','Inter Street') insert Lives values('u1',1) insert Lives values('u2',2) insert Lives values('u3',2) insert Lives values('u4',2) insert OptionalInfo values ('u1',1985,'F','N','share driving',null,'') insert OptionalInfo values ('u2',1981,'M','N','drive only',80,'') insert OptionalInfo values ('u3',1967,'M','N','share driving',120,'') insert OptionalInfo values ('u4',1947,'F','N','share driving',100,'') insert Car values (1,'Honda','Civic',2000) insert Car values (2,'Ford','Taurus',2001) insert Car values (3,'Toyota','Camry',2001) insert Car values (4,'Chevy','Cavalier',1999) insert Car values (5,'Toyota','Celica',1995) insert Owns values('u1',1) insert Owns values('u2',2) insert Owns values('u3',3) insert Owns values('u4',4) insert Owns values('u4',5) Q3. insert DailyCommute values('u2','1800-01-01 08:30:00','1800-01-01 17:00:00',40,30,2002,'Spring') insert DailyCommute values('u3','1800-01-01 09:00:00','1800-01-01 16:30:00',60,60,2002,'Spring') insert DailyCommute values('u4','1800-01-01 08:30:00','1800-01-01 16:30:00',30,30,2002,'Spring') Q4. insert OtherTrip values(1,'2002-03-31','visiting friends','2002-03-31 16:30:00','2002-03-31 17:00:00',30,45,15,0,2,1,2,'u1') insert Address values(3) insert ResidenceAddr values (3,'South Station','Boston','MA','01800','South Station') insert OtherTrip values(2,'2002-03-30','festival','2002-03-30 09:00:00','2002-03-30 15:00:00',20,60,20,0,3,2,3,'u2') insert OtherTrip values(3,'2002-03-31','meeting classmates after festival','2002-03-31 10:30:00','2002-03-31 16:20:00',60,20,20,0,3,3,1,'u2') insert OtherTrip values(4,'2002-03-30','festival','2002-03-30 09:00:00','2002-03-30 15:00:00',60,60,25,1,3,2,3,'u4') insert OtherTrip values(5,'2002-03-31','back from festival','2002-03-31 11:00:00','2002-03-31 18:00:00',60,60,25,1,3,3,2,'u4') Q5. /* the most direct solution: do all 7 queries below, and obtain all information * about user 'u1' (not including repeated data or database internal data): * 1. required user information excluding major-status/dept-position or address * 2. major and status if 'u1' is a student * 3. dept and position if 'u1' is a faculty or staff * 4. address if 'u1' lives on campus * 5. address if 'u1' lives off campus * 6. optional user information * 7. cars owned */ select U.* from Usr U where U.loginName='u1' select S.major, S.status from Student S where S.loginName='u1' select F.department, F.position from FacultyStaff F where F.loginName='u1' select P.street, P.roomNo from Lives L, CampusAddr P where L.loginName='u1' and L.addrId=P.addrId select R.street, R.town, R.state, R.zipcode, R.nearestIntersection from Lives L, ResidenceAddr R where L.loginName='u1' and L.addrId=R.addrId select datepart(yy,getDate())-birthYear, sex, smokerOrNot, preferDriveOrRide, commuteCostCompensation, currentCommuteMethod from OptionalInfo I where I.loginName='u1' select C.manufacture, C.model, C.year from Owns O, Car C where O.loginName='u1' and O.carId=C.carId /* note 1: * a much better solution would be to first declare a local variable say, @uid: * * declare @uid char(2) * select @uid='u1' * * and use @uid in place of 'u1' seven times. */ /* note 2: * if you are lazy or not careful, you can use * to return all attributes, * but it may include repeated data and database internal data. */ /* note 3: * you could also merge some of the queries. For example, you could merge * items 1, 3, 4, and 6 into the following: (I am lazy here, so used *.) * * select U.*, P.*, I.* * from Usr U, Lives L, CampusAddr P, OptionalInfo I * where U.loginName='u1' * and L.loginName='u1 and L.addrId=P.addrId * and I.loginName='u1' * * select U.*, R.*, I.* * from Usr U, Lives L, ResidenceAddr R, OptionalInfo I * where U.loginName='u1' * and L.loginName='u1' and L.addrId=R.addrId * and I.loginName='u1' */ /* note 4: * one could use the condition: * * exists (select P.addrId * from Lives L, CampusAddr P * where L.loginName=@uid and L.addrId=P.addrId) * * to test whether the user @uid has an on-campus address, and control * which one of the two select statements above is executed by using: * * if "above condition" * "the first select statement above" * else * "the second select statement above" * * rather than executing both and relying on one of them returning nothing. */ /* note 5: * to make the above "exists ..." test simpler and more efficient to * execute, one could use an attribute in Address to indicate whether * it is an on-campus or off-campus address, and test that directly. * this is a general solution to the problem caused by isA relationships. */ /* note 6: * one could also use case expression inside select, as well as * in conditions, to help make the choices, but only one attribute * can be returned by such a case expression at a time. */ Q6. select * from DailyCommute where loginName='u2' /* note: you could also declare a variable to bind 'u2' as discussed for Q5. */ Q7. /* the most direct solution: do all 4 queries below, one for each * combination of from and to addresses either on or off campus: */ select T.*, A1.*, A2.* from OtherTrip T, CampusAddr A1, CampusAddr A2 where T.loginName='u4' and T.fromAddr=A1.addrId and T.toAddr=A2.addrId select T.*, A1.*, A2.* from OtherTrip T, CampusAddr A1, ResidenceAddr A2 where T.loginName='u4' and T.fromAddr=A1.addrId and T.toAddr=A2.addrId select T.*, A1.*, A2.* from OtherTrip T, ResidenceAddr A1, CampusAddr A2 where T.loginName='u4' and T.fromAddr=A1.addrId and T.toAddr=A2.addrId select T.*, A1.*, A2.* from OtherTrip T, ResidenceAddr A1, ResidenceAddr A2 where T.loginName='u4' and T.fromAddr=A1.addrId and T.toAddr=A2.addrId /* similar issues to those in Q5 apply here also. * you can see that isA relationships may blow up code size significantly * with this language; that's a famous place that calls for OO languages. */ Q8. select count(*) from Usr select count(*) from Student select count(*) from FacultyStaff select count(*) from Usr, Lives, CampusAddr where Usr.loginName=Lives.loginName and Lives.addrId=CampusAddr.addrId /* you could use Student in place of Usr, since only they live on campus. */ select count(*) from Usr, Lives, ResidenceAddr where Usr.loginName=Lives.loginName and Lives.addrId=ResidenceAddr.addrId select count(*) from DailyCommute select count(*) from OtherTrip Q9. declare @uid char(2) select @uid='u3' select C.loginName /* login name of a matching daily commute */ from DailyCommute G, /* given daily commute */ DailyCommute C, Lives L, OptionalInfo I /* comm, addr, info to match */ where G.loginName=@uid and C.loginName<>G.loginName and C.loginName=L.loginName and C.loginName=I.loginName /* same residence address */ and L.addrId in (select addrId from Lives where Lives.loginName=@uid) /* an alternative, more modular, and more efficient way of testing * this condition is to remove Lives L from "from" and use here: * (select addrId from Lives where Lives.loginName=@uid)= * (select addrId from Lives where Lives.loginName=C.loginName') */ /* arrival time overlap, using the formula explained in class */ and G.startTime>=dateadd(mi,-C.maxMinutesEarlyArrival,C.startTime) and C.startTime>=dateadd(mi,-G.maxMinutesEarlyArrival,G.startTime) /* departure time overlap */ and G.endTime<=dateadd(mi,C.maxMinutesLateDeparture,C.endTime) and C.endTime<=dateadd(mi,G.maxMinutesLateDeparture,G.endTime) /* not both want to drive or both want to ride */ and (I.preferDriveOrRide='share driving' or I.preferDriveOrRide not in (select preferDriveOrRide from OptionalInfo where loginName=@uid)) order by /* first by cost, then by sum of minimum wait time */ I.commuteCostCompensation, (abs(datediff(mi,G.startTime,C.startTime))+datediff(mi,G.startTime,C.startTime))/2+ (abs(datediff(mi,C.endTime,G.endTime))+datediff(mi,C.endTime,G.endTime))/2 /* the formula explained in class for computing sum of min wait time: * if datediff returns a negative number, then abs is added to get 0, * otherwise, adding abs doubles it and thus is divided by 2. */ Q10. declare @tid integer select @tid=4 select T.tripId /* tripId of a matching non-daily trip */ from OtherTrip G, /* given non-daily trip */ OtherTrip T /* trip to match */ where G.tripId=@tid and G.tripId<>T.tripId /* same date, where dy means day of year (1-366) */ and datediff(dy,G.date,T.date)=0 /* same start and end addresses */ and G.fromAddr=T.fromAddr and G.toAddr=T.toAddr /* departure time overlap */ and G.departureTime>=dateadd(mi,-T.maxMinutesEarlyDeparture,T.departureTime) and T.departureTime>=dateadd(mi,-G.maxMinutesEarlyDeparture,G.departureTime) /* arrival time overlap */ and G.arrivalTime>=dateadd(mi,-T.maxMinutesEarlyArrival,T.arrivalTime) and T.arrivalTime>=dateadd(mi,-G.maxMinutesEarlyArrival,G.arrivalTime) /* matching requests and offers, first for G requests and T offers, the reverse */ and (G.numberOfPassengers>0 and G.numberOfPassengers<=T.maxNumberOfPassengers or T.numberOfPassengers>0 and T.numberOfPassengers<=G.maxNumberOfPassengers) order by /* first by cost, then by sum of minimum diff from desired time */ T.costCompensation, (abs(datediff(mi,T.departureTime,G.departureTime))+datediff(mi,T.departureTime,G.departureTime))/2+ (abs(datediff(mi,G.arrivalTime,T.arrivalTime))+datediff(mi,G.arrivalTime,T.arrivalTime))/2 Q11. declare @tid integer select @tid=5 select T1.tripId, T2.tripId from OtherTrip G, /* given non-daily trip */ OtherTrip T1, OtherTrip T2 /* two trips together to match */ where G.tripId=@tid and G.tripId<>T1.tripId and G.tripId<>T2.tripId /* same date */ and datediff(dy,G.date,T1.date)=0 and datediff(dy,G.date,T2.date)=0 /* same start and end addresses, and intermediate address */ and G.fromAddr=T1.fromAddr and T2.toAddr=G.toAddr and T1.toAddr=T2.fromAddr /* departure time overlap */ and G.departureTime>=dateadd(mi,-T1.maxMinutesEarlyDeparture,T1.departureTime) and T1.departureTime>=dateadd(mi,-G.maxMinutesEarlyDeparture,G.departureTime) /* arrival time overlap */ and G.arrivalTime>=dateadd(mi,-T2.maxMinutesEarlyArrival,T2.arrivalTime) and T2.arrivalTime>=dateadd(mi,-G.maxMinutesEarlyArrival,G.arrivalTime) /* intermediate time overlap*/ and T1.arrivalTime>=dateadd(mi,-T2.maxMinutesEarlyDeparture,T2.departureTime) and T2.departureTime>=dateadd(mi,-T1.maxMinutesEarlyArrival,T1.arrivalTime) /* matching requests and offers*/ and (G.numberOfPassengers>0 and /* G requests, and T1 and T2 offers */ G.numberOfPassengers<=T1.maxNumberOfPassengers and G.numberOfPassengers<=T2.maxNumberOfPassengers and T1.maxNumberOfPassengers>0 and T2.maxNumberOfPassengers>0 and /* reverse */ T1.numberOfPassengers<=G.maxNumberOfPassengers and T2.numberOfPassengers<=G.maxNumberOfPassengers) order by /* first by cost, then by minimum wait time */ T1.costCompensation, (abs(datediff(mi,T1.departureTime,G.departureTime))+datediff(mi,T1.departureTime,G.departureTime))/2+ (abs(datediff(mi,G.arrivalTime,T2.arrivalTime))+datediff(mi,G.arrivalTime,T2.arrivalTime))/2 Q12. /*get average age of freshman*/ select avg(datepart(yy,getDate())-I.birthYear) from Student S, OptionalInfo I where S.status='freshman' and S.loginName=I.loginName /*get average age of senior*/ select avg(datepart(yy,getDate())-I.birthYear) from Student S, OptionalInfo I where S.status='senior' and S.loginName=I.loginName /*get average age of graduate*/ select avg(datepart(yy,getDate())-I.birthYear) from Student S, OptionalInfo I where S.status='graduate' and S.loginName=I.loginName /*get average age of Faculty and Staff*/ select avg(datepart(yy,getDate())-I.birthYear) from FacultyStaff F, OptionalInfo I where F.loginName=I.loginName Q13. /* create a view for every user and the number of cars he or she owns */ create view CarCount(carCount) as select count(*) from Owns O group by O.loginName select carCount,count(*) from CarCount group by carCount order by carCount Q14. declare @uid char(2) select @uid='u2' select L2.loginName from Lives L, Lives L2 where L.loginName=@uid and L2.loginName<>L.loginName and L2.addrId=L.addrId Q15. delete DailyCommute where loginName='u2' Q16. delete OtherTrip where tripId=1 /* should delete some dead to and from addresses also, similar as in Q17, * but not all that much */ Q17. /* delete not only user table, but all info, incl cars owned and addr live. */ delete OtherTrip where loginName='u3' delete DailyCommute where loginName='u3' delete Owns where loginName='u3' delete Car where carId not in (select C.carId from Car C, Owns O where C.carId=O.carId group by C.carId) delete OptionalInfo where loginName='u3' delete Lives where loginName='u3' delete ResidenceAddr where /* if this addr is not used in Lives */ addrId not in (select A.addrId from Address A, Lives L where A.addrId=L.addrId group by A.addrId) and /* if this addr is not used in OtherTrip */ addrId not in (select T.toAddr from OtherTrip T) and addrId not in (select T.fromAddr from OtherTrip T) delete CampusAddr where /* if this addr is not used in Lives */ addrId not in (select A.addrId from Address A, Lives L where A.addrId=L.addrId group by A.addrId) and /* if this addr is not used in OtherTrip */ addrId not in (select T.toAddr from OtherTrip T) and addrId not in (select T.fromAddr from OtherTrip T) delete Address where addrId not in (select P.addrId from CampusAddr P) and addrId not in (select R.addrId from ResidenceAddr R) delete FacultyStaff where loginName='u3' delete Student where loginName='u3' delete Usr where loginName='u3' /* a much nicer way of deleting some of these is to declaratively use * constraints like "on delete cascade" in the tables. */ Q18. delete DailyCommute where /* if the current year is after the posted year */ datepart(yy,getdate())>year or /* if the current year is same as the posted year, and */ datepart(yy,getdate())=year and (season='Spring' and datepart(mm,getdate())>4 or season='Summer' and datepart(mm,getdate())>8) /* if this is posted for Spring, and now is after Apr, or * if this is posted for Summer, and now is after Aug; * if this is posted for Fall, and now is after Dec 31, then * we are into the next year, which is already taken care of. */ delete OtherTrip where datediff(dy,date,getdate())>0 ---------------------------------------------------------------------- output from execution ---------------------------------------------------------------------- Q5. loginName password lastName firstName phone email ---------- -------- -------- --------- ---------- -------------- u1 passwd1 Johnson Mary 6311112222 mary@yahoo.com major status ------------------------------ ---------- Biology freshman department position ------------------------------ ------------------------------ street roomNo -------------------------------------------------- ---------- Steeley A101 street town state zipcode nearestIntersection ------ ----- ----- ------- ------------------- sex smokerOrNot preferDriveOrRide commuteCostCompensation currentCommuteMethod -- --- ----------- ----------------- ----------------------- -------------------- 17 F N share driving NULL manufacture model year ------------------------------ -------------------- ---------- Honda Civic 2000 Q6. loginName startTime endTime maxMinutesEarlyArrival maxMinutesLateDeparture year season --------- ------------------------ ------------------------ ---------------------- ----------------------- ---- ------ u2 Jan 1 1800 8:30:00:000AM Jan 1 1800 5:00:00:000PM 40 30 2002 Spring Q7. tripId date reason departureTime arrivalTime maxMinutesEarlyDeparture maxMinutesEarlyArrival costCompensation numberOfPassengers maxNumberOfPassengers fromAddr toAddr loginName addrId street roomNo addrId street roomNo ------ ---- ------ ------------- ----------- ------------------------ ---------------------- ---------------- ------------------ --------------------- -------- ------ --------- ------ ------ ------ ------ ------ ------ tripId date reason departureTime arrivalTime maxMinutesEarlyDeparture maxMinutesEarlyArrival costCompensation numberOfPassengers maxNumberOfPassengers fromAddr toAddr loginName addrId street roomNo addrId street town state zipcode nearestIntersection ------ ---- ------ ------------- ----------- ------------------------ ---------------------- ---------------- ------------------ --------------------- -------- ------ --------- ------ ------ ------ ------ ------ ---- ----- ------- ------------------- tripId date reason departureTime arrivalTime maxMinutesEarlyDeparture maxMinutesEarlyArrival costCompensation numberOfPassengers maxNumberOfPassengers fromAddr toAddr loginName addrId street town state zipcode nearestIntersection addrId street roomNo ------ ---- ------ ------------- ----------- ------------------------ ---------------------- ---------------- ------------------ --------------------- -------- ------ --------- ------ ------ ---- ----- ------- ------------------- ------ ------ ------ tripId date reason departureTime arrivalTime maxMinutesEarlyDeparture maxMinutesEarlyArrival costCompensation numberOfPassengers maxNumberOfPassengers fromAddr toAddr loginName addrId street town state zipcode nearestIntersection addrId street town state zipcode nearestIntersection ------ -------------------------- ------------------ -------------------------- ------------------------- ------------------------ ---------------------- ---------------- ------------------ --------------------- ---------- ---------- ---------- ------ ------------- --------- ----- ------- ------------------- ------ ------------- --------- ----- ------- ------------------- 4 Mar 30 2002 12:00:00:000AM festival Mar 30 2002 9:00:00:000AM Mar 30 2002 3:00:00:000PM 60 60 25 1 3 2 3 u4 2 East Avenue Good Town NY 12345 Inter Street 3 South Station Boston MA 01800 South Station 5 Mar 31 2002 12:00:00:000AM back from festival Mar 31 2002 11:00:00:000AM Mar 31 2002 6:00:00:000PM 60 60 25 1 3 3 2 u4 3 South Station Boston MA 01800 South Station 2 East Avenue Good Town NY 12345 Inter Street Q8. 4 ---------- 2 ---------- 2 ---------- 1 ---------- 3 ---------- 3 ---------- 5 Q9. loginName ---------- u2 u4 Q10. tripId ---------- 2 Q11. tripId tripId ---------- ---------- 3 1 Q12. ---------- 17 ---------- 21 ---------- NULL ---------- 45 Q13. carCount ---------- ---------- 1 3 2 1 Q14. loginName ---------- u3 u4 Table content after all executed Usr: loginName password lastName firstName phone email --------- -------- -------- --------- ---------- -------------- u1 passwd1 Johnson Mary 6311112222 mary@yahoo.com u2 passwd2 Smith John 1112222222 john@yahoo.com u4 passwd4 Kennedy Lisa 1112224444 lisa@yahoo.com Student: loginName major status ---------- ------------------------------ ---------- u1 Biology freshman u2 Computer Science senior FacultyStaff loginName department position ---------- ------------------------------ ------------- u4 music administrator Lives: loginName addrId ---------- ---------- u1 1 u2 2 u4 2 Address: addrId ---------- 1 2 3 CampusAddr: addrId street roomNo ---------- -------------------------------------------------- ------ 1 Steeley A101 ResidenceAddr: addrId street town state zipcode nearestIntersection ---------- -------------- --------- ----- ------- ------------------- 2 East Avenue Good Town NY 12345 Inter Street 3 South Station Boston MA 01800 South Station OptionalInfo: loginName birthYear sex smokerOrNot preferDriveOrRide commuteCostCompensation currentCommuteMethod --------- --------- --- ----------- ----------------- ----------------------- -------------------- u1 1985 F N share driving NULL u2 1981 M N drive only 80 u4 1947 F N share driving 100 Owns: loginName carId ---------- ---------- u1 1 u2 2 u4 4 u4 5 Car: carId manufacture model year ----- ----------- -------- ---- 1 Honda Civic 2000 2 Ford Taurus 2001 4 Chevy Cavalier 1999 5 Toyota Celica 1995 DailyCommute: loginName startTime endTime maxMinutesEarlyArrival maxMinutesLateDeparture year season --------- ------------------------ ------------------------ ---------------------- ----------------------- ---- ------ u4 Jan 1 1800 8:30:00:000AM Jan 1 1800 4:30:00:000PM 30 30 2002 Spring OtherTrip: tripId date reason departureTime arrivalTime maxMinutesEarlyDeparture maxMinutesEarlyArrival costCompensation numberOfPassengers maxNumberOfPassengers fromAddr toAddr loginName ------ ---- ------ ------------- ----------- ------------------------ ---------------------- ---------------- ------------------ --------------------- -------- ------ ---------