Faloutsos CMU SCS 15-415 1 CMU SCS Carnegie Mellon Univ. Dept. of Computer Science 15-415 - Database Applications Lecture #19 (not in book) Database Design Methodology handout CMU SCS Faloutsos CMU SCS 15-415 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 Faloutsos CMU SCS 15-415 3 Goal • Given an English description of an enterprise • build a system to automate it and • produce the documentation In diagram form • tasks • documentsFaloutsos CMU SCS 15-415 2 CMU SCS Faloutsos CMU SCS 15-415 4 description req. anal. top level I.F.D. sys. anal. conc. mod. impl.+test. task + doc forms. schema. code. task emul. tests user’s man. pseudo-code CMU SCS Faloutsos CMU SCS 15-415 5 description req. anal. top level I.F.D. sys. anal. conc. mod. impl.+test. task + doc forms. schema. code. task emul. tests user’s man. pseudo-code Phase-I Phase-II CMU SCS Faloutsos CMU SCS 15-415 6 Running example - ‘Mini-U’ • Students register • Students enroll in courses • Students ask for transcripts • Administrator records grades • Every semester: print class listsFaloutsos CMU SCS 15-415 3 CMU SCS Faloutsos CMU SCS 15-415 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 Faloutsos CMU SCS 15-415 8 reg. form reg. student rec. input output Top level inf. flow diagram CMU SCS Faloutsos CMU SCS 15-415 9 reg. form reg. student rec. System boundary • internal documents -> db tables • tasks -> db programs • tasks: internal onlyFaloutsos CMU SCS 15-415 4 CMU SCS Faloutsos CMU SCS 15-415 10 reg. form reg. More on top level diagram enroll. form enroll class rec. student rec. CMU SCS Faloutsos CMU SCS 15-415 11 reg. form reg. More on top level diagram enroll. form enroll class rec. student rec. transc. req. transcr. transcr. CMU SCS Faloutsos CMU SCS 15-415 12 Example - Mini-U • Students register • Students enroll in courses • Students ask for transcripts • Administrator records grades • every semester: print class rostersFaloutsos CMU SCS 15-415 5 CMU SCS Faloutsos CMU SCS 15-415 13 reg. form reg. enroll. form enroll class rec. student rec. transc. req. transcr. transcr. grades form grades CMU SCS Faloutsos CMU SCS 15-415 14 reg. form reg. enroll. form enroll class rec. student rec. transc. req. transcr. transcr. class list grades form grades list CMU SCS Faloutsos CMU SCS 15-415 15 description req. anal. top level I.F.D. sys. anal. conc. mod. impl.+test. task + doc forms. schema. code. task emul. tests user’s man. pseudo-code Phase-I Phase-IIFaloutsos CMU SCS 15-415 6 CMU SCS Faloutsos CMU SCS 15-415 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 Faloutsos CMU SCS 15-415 17 Document list • D1: registration form • D2: enrollment for • … • D7: student record • D8: class record } INTERNAL CMU SCS Faloutsos CMU SCS 15-415 18 Document forms • D1: registration – ssn – name – address D2: enrollment ssn name List-of: course id course nameFaloutsos CMU SCS 15-415 7 CMU SCS Faloutsos CMU SCS 15-415 19 Document forms - cont’d • D3: transcript request form – ssn – name D4: transcript ssn name List-of: class-id class name grade CMU SCS Faloutsos CMU SCS 15-415 20 Document forms - cont’d (Internal documents - VERY IMPORTANT) D7: student record – ssn – name – address CMU SCS Faloutsos CMU SCS 15-415 21 Document forms - cont’d D8: class record – class-id – class-name – syllabus – List-of • ssn • gradeFaloutsos CMU SCS 15-415 8 CMU SCS Faloutsos CMU SCS 15-415 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 Faloutsos CMU SCS 15-415 23 Task List • T1: Registration • T2: Enrollment • T3: Transcript • ... CMU SCS Faloutsos CMU SCS 15-415 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 taskFaloutsos CMU SCS 15-415 9 CMU SCS Faloutsos CMU SCS 15-415 25 description req. anal. top level I.F.D. sys. anal. conc. mod. impl.+test. task + doc forms. schema. code. task emul. tests user’s man. pseudo-code Phase-I Phase-II CMU SCS Faloutsos CMU SCS 15-415 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 Faloutsos CMU SCS 15-415 27 E-R diagram for Mini-U Student Class ssn addr. c-id ... ... takes N M gradeFaloutsos CMU SCS 15-415 10 CMU SCS Faloutsos CMU SCS 15-415 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 Faloutsos CMU SCS 15-415 29 SQL DDL statements create table student (ssn char(9), … ); create table class (c-id char(5), … ); ... CMU SCS Faloutsos CMU SCS 15-415 30 description req. anal. top level I.F.D. sys. anal. conc. mod. impl.+test. task + doc forms. schema. code. task emul. tests user’s man. pseudo-code Phase-I Phase-IIFaloutsos CMU SCS 15-415 11 CMU SCS Faloutsos CMU SCS 15-415 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 Faloutsos CMU SCS 15-415 32 description req. anal. top level I.F.D. sys. anal. conc. mod. impl.+test. task + doc forms. schema. code. task emul. tests user’s man. pseudo-code Phase-I Phase-II CMU SCS Faloutsos CMU SCS 15-415 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)