1 / 11

Database midterm2 answer

Database midterm2 answer. 張兢真 beautidays at ms11.voip.edu.tw 王鐘逸 chungyi at ms11.voip.edu.tw 陳柏州 mac at ms11.voip.edu.tw. 1-1. select a.name,b.homework from (select distinct(name) from hw) as a, (select distinct(homework) from hw) as b where b.homework not in

jaime-carpenter
Download Presentation

Database midterm2 answer

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database midterm2 answer 張兢真 beautidays at ms11.voip.edu.tw 王鐘逸 chungyi at ms11.voip.edu.tw 陳柏州 mac at ms11.voip.edu.tw

  2. 1-1 • select a.name,b.homework from • (select distinct(name) from hw) as a, • (select distinct(homework) from hw) as b • where b.homework not in • (select distinct(homework) from hw as c where c.name=a.name) • order by a.name,b.homework

  3. 1-2 • CREATE TEMPORARY TABLE T  SELECT * FROM HW;UPDATE HW  SET Score = 60  WHERE Score < 60 AND  Name IN (SELECT Name from T GROUP BY Name HAVING COUNT(*)>=3);

  4. 2-1 http://ms11.voip.edu.tw/~mac/db/1.php http://ms11.voip.edu.tw/~mac/db/1.phps • <?php • define('HOST', 'localhost'); • define('USER', 'mac'); • define('PASS', ' yourpassword '); • define('DB', 'mac'); • mysql_connect(HOST, USER, PASS); • mysql_select_db(DB); • $result = mysql_query("select homework from hw2 group by homework having count(*) >=5"); • $num=mysql_num_rows($result); • $i=0; • while($i<$num) • { • $r=mysql_fetch_array($result); • echo $r[0]."<br>"; • $result_each = mysql_query("select count(*), avg(score) from hw2 where homework = '$r[0]'"); • $num_each = mysql_num_rows($result_each); • $i_each=0; • while ($i_each<$num_each) • { • $r_each=mysql_fetch_array($result_each); • echo $r_each[0]." ".$r_each[1]."<br>"; • $i_each++; • } • $i++; • } • ?>

  5. 2-2 為了demo,這裡使用table hw2 http://ms11.voip.edu.tw/~mac/db/2.php http://ms11.voip.edu.tw/~mac/db/2.phps • <?php • define('HOST', 'localhost'); • define('USER', 'mac'); • define('PASS', ‘yourpassword'); • define('DB', 'mac'); • mysql_connect(HOST, USER, PASS); • mysql_select_db(DB); • $result = mysql_query("select name from hw2 group by name having count(homework) <(select count(distinct homework) from hw2)"); • $num=mysql_num_rows($result); • $i=0; • while($i<$num) • { • $r=mysql_fetch_array($result); • echo $r[0]."<br>"; • $i++; • } • ?>

  6. 3 (1) 6 (2) T1 -> T2 -> T3 T3 -> T2 -> T1 T2 -> T3 -> T1 T2 -> T1 -> T3 T3 -> T1 -> T2 T1 -> T3 -> T2 (3)13!

  7. T1 T2 T3 4.2 T1 T2 T3 X T1 T2 R(X) R(X) X W(X) X X T3 W(X) R(X) 4.1 T1 T2 T3 X R(X) R(X) X W(X) X X R(X) cycle W(X) cycle

  8. 4.3 T1 T2 T3 X R(X) R(X) X W(X) X X R(X) W(X) 4.4 T1 T2 T3 X T1 T1 T2 T2 R(X) R(X) X R(X) X X T3 T3 W(X) W(X) Serializable : T2-T3-T1 cycle

  9. 5.S1 T1 T2 T3 R(X) Z R(Z) R(Z) R(X) X Y R(Y) W(X) R(Y) Serializable : T3-T1-T2 R(Y) T1 T2 W(Z) W(Y) T3

  10. 5.S2 T1 T2 T3 R(X) Z R(Z) T1 T2 R(X) Y R(Z) X Y R(Y) T3 cycle R(Y) W(X) W(Z) W(Y) W(Y)

  11. 6 (1) no (2) Figure 18.4 (P.634) T1’T2’ read_lock(Y); read_lock(X); read_item(Y); read_item(X); write_lock(X); write_lock(Y); unlock(Y); unlock(X); read_item(X); read_item(Y); X := X+Y; Y := X+Y; write_item(X); write_item(Y); unlock(X); unlock(Y);

More Related