21 27 Apr Final Review.pptx

27 April: Announcements DB2 Wrap-up Final Exam Review Instructor Evaluation 29-Apr ? Monday Class Schedule ? no class 6-May ? 12-3 Final Exam Open book, open note Final Exam Review Suggestions Review posted sample final exam C18 XML Schema http://www.richardtwatson.com/dm5e/Reader/answers/chapter18.html Review exercises 1, 4 Question from C15 (Reading Assignment) Work examples, etc. in Ref 2, p289, Exercises, p315 Reference 1: Basic structures (p199) One entity Two entities Relationship descriptors as identifiers A tree structure One entity No relationships person personid attribute1 attribute2 ? One entity A 1:1 recursive relationship person personid attribute1 attribute2 ? spouse One entity A recursive 1:m relationship person personid attribute1 attribute2 ? father One entity A recursive m:m relationship person personid attribute1 attribute2 ? friendship personid1 personid2 attribute3 attribute4 ? Two entities A 1:1 relationship department deptid deptattrib1 deptattrib2 ? bossno employee empid empattrib1 empattrib2 ? Two entities A 1:m relationship nation natcode natname exchrate stock stkcode stkfirm stkprice stkqty stkdiv stkpe natcode Two entities An m:m relationship Note: COPY is an independent entity because bookno is a unique identifier ? otherwise it would require a concatenated key (+). book callno isbn booktitle ? borrower borrowerid ? copy bookno duedate ? callno borrowerid Relationship descriptors as identifiers A dependent entity (requires a concatenated key (+)) region regname regtype regpop regarea ? city cityname citypop cityarea ? regname Relationship descriptors as identifiers An associative entity city cityname ? store storestreet storezip ? cityname firmname firm firmname firmstreet firmzip ? A tree structure firm firmname ? division divname ? firmname department deptname ? divname firmname section sectionid ? deptname divname firmname Recursive tree unit unitid attribute1 ? superunitid Key points There are a few basic structures All data models are constructed from these basic structures Reference 2 SQL Playbook Work examples, etc. in Reference 2, p289, Exercises, p315 ? using data model and tables that follow Data model http://mistsql.terry.uga.edu/ Relational tables qsale saleno saleqty itemname deptname qitem itemname itemtype itemcolor qdel delno delqty itemname deptname splno qspl splno splname qdept deptname deptfloor deptphone empno qemp empno empfname empsalary deptname bossno