1 / 35

Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications

Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications. Lecture #19 (not in book) Database Design Methodology handout. Based on handout:. Adaptable methodology for database design by N. Roussopoulos and R.T. Yeh, IEEE Computer Vol. 17, no. 5, pp. 64-80. 1984. Goal.

teva
Download Presentation

Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications

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. Carnegie Mellon Univ.Dept. of Computer Science15-415/615 - DB Applications Lecture #19 (not in book) Database Design Methodology handout

  2. Based on handout: Adaptable methodology for database design by N. Roussopoulos and R.T. Yeh, IEEE Computer Vol. 17, no. 5, pp. 64-80. 1984 CMU SCS 15-415/615

  3. Goal • Given an English description of an enterprise • build a system to automate it and • produce the documentation In diagram form • tasks • documents CMU SCS 15-415/615

  4. description req. anal. sys. anal. conc. mod. impl.+test. task emul. top level I.F.D. task + doc forms. schema. code. tests user’s man. pseudo-code CMU SCS 15-415/615

  5. Phase-II description req. anal. sys. anal. conc. mod. impl.+test. task emul. top level I.F.D. task + doc forms. schema. code. tests user’s man. pseudo-code Phase-I CMU SCS 15-415/615

  6. Running example - ‘Mini-U’ • Students register • Students enroll in courses • Students ask for transcripts • Administrator records grades • Every semester: print class lists CMU SCS 15-415/615

  7. Requirement analysis Turn English description in to top level information flow diagram, where • boxes -> documents (~ db tables) • ovals -> tasks (= db programs) Important: system boundary CMU SCS 15-415/615

  8. Top level inf. flow diagram reg. form input reg. output student rec. CMU SCS 15-415/615

  9. System boundary internal documents -> db tables tasks -> db programs tasks: internal only reg. form reg. student rec. CMU SCS 15-415/615

  10. More on top level diagram reg. enroll student rec. reg. form enroll. form class rec. CMU SCS 15-415/615

  11. More on top level diagram reg. enroll transcr. student rec. reg. form enroll. form transc. req. transcr. class rec. CMU SCS 15-415/615

  12. Example - Mini-U • Students register • Students enroll in courses • Students ask for transcripts • Administrator records grades • every semester: print class rosters CMU SCS 15-415/615

  13. reg. enroll grades student rec. reg. form enroll. form transc. req. transcr. transcr. class rec. grades form CMU SCS 15-415/615

  14. reg. enroll grades list transcr. student rec. reg. form enroll. form transc. req. transcr. class rec. class list grades form CMU SCS 15-415/615

  15. Phase-II description req. anal. sys. anal. conc. mod. impl.+test. task emul. top level I.F.D. task + doc forms. schema. code. tests user’s man. pseudo-code Phase-I CMU SCS 15-415/615

  16. Document + Task forms Top level diagram: only half of the info - we also need: • Document forms and document list • Task forms and task list CMU SCS 15-415/615

  17. Document list • D1: registration form • D2: enrollment for • … • D7: student record • D8: class record } INTERNAL CMU SCS 15-415/615

  18. Document forms • D1: registration • ssn • name • address • D2: enrollment • ssn • name • List-of: • course id • course name CMU SCS 15-415/615

  19. Document forms - cont’d • D3: transcript request form • ssn • name • D4: transcript • ssn • name • List-of: • class-id • class name • grade CMU SCS 15-415/615

  20. Document forms - cont’d (Internal documents - VERY IMPORTANT) D7: student record • ssn • name • address CMU SCS 15-415/615

  21. Document forms - cont’d D8: class record • class-id • class-name • syllabus • List-of • ssn • grade CMU SCS 15-415/615

  22. Document forms - cont’d • IMPORTANT POINTS • avoid redundancy in internal documents: ie., grades should be stored in ONE place only • there are many, different, correct solutions CMU SCS 15-415/615

  23. Task List • T1: Registration • T2: Enrollment • T3: Transcript • ... CMU SCS 15-415/615

  24. Task forms • As in [R+Y] • not required for this homework • sub-tasks: probably there won’t be any • otherwise: ~3-7 sub-tasks per task CMU SCS 15-415/615

  25. Phase-II description req. anal. sys. anal. conc. mod. impl.+test. task emul. top level I.F.D. task + doc forms. schema. code. tests user’s man. pseudo-code Phase-I CMU SCS 15-415/615

  26. Database schema - E-R • from the internal documents • use their forms • ‘List-of’ constructs -> relationships Eg., for ‘Mini-U’: D7: Student record (ssn, name, address) D8: Class record (c-id, …, List-of … ) CMU SCS 15-415/615

  27. ssn addr. c-id grade takes E-R diagram for Mini-U ... ... N M Student Class CMU SCS 15-415/615

  28. Relational schema student( ssn, name, address) class( c-id, c-name, syllabus) takes(c-id, ssn, grade) Make sure that • Primary keys are underlined; • tables are in BCNF (or 3NF at worst) CMU SCS 15-415/615

  29. SQL DDL statements create table student (ssn char(9), … ); create table class (c-id char(5), … ); ... CMU SCS 15-415/615

  30. Phase-II description req. anal. sys. anal. conc. mod. impl.+test. task emul. top level I.F.D. task + doc forms. schema. code. tests user’s man. pseudo-code Phase-I CMU SCS 15-415/615

  31. Task emulation T1: Registration read ssn, name and address if ( ssn does not exist in ‘student’){ insert into student values ( :ssn, :name, :address); } else{print “error: duplicate ssn”} CMU SCS 15-415/615

  32. Phase-II description req. anal. sys. anal. conc. mod. impl.+test. task emul. top level I.F.D. task + doc forms. schema. code. tests user’s man. pseudo-code Phase-I CMU SCS 15-415/615

  33. Testing • For T1 (registration), we check • duplicate ssn • ssn with 9 digits • For T2 (enrollment) we check • for valid ssn (9 digits) • for registered ssn • for valid c-id • for duplicate (ssn, c-id) entry CMU SCS 15-415/615

  34. User’s manual Short (~1 page or less) - eg.,: • copy myproject.tar • do ‘make’ • follow the menu <anything else the user should know, like OS, space requirements, etc etc> CMU SCS 15-415/615

  35. Important points for Phase-I • No redundancy in the fields of internal documents • don’t forget the system boundary • make sure the top level diagram agrees with the internal document forms • explain if/when we deviate from BCNF CMU SCS 15-415/615

More Related