CMU SCS Carnegie Mellon Univ Dept of Computer

CMU SCS Based on handout: Adaptable methodology for database design by N. Roussopoulos and

CMU SCS Goal • Given an English description of an enterprise • build a

CMU SCS description req. anal. top level I. F. D. conc. mod. schema. impl.

CMU SCS Phase-II Phase-I description req. anal. top level I. F. D. conc. mod.

CMU SCS Running example - ‘Mini-U’ • • • Students register Students enroll in

CMU SCS Requirement analysis Turn English description in to top level information flow diagram,

CMU SCS Top level inf. flow diagram reg. form input reg. output student rec.

CMU SCS System boundary reg. form reg. • internal documents -> db tables •

CMU SCS More on top level diagram reg. form reg. student rec. Faloutsos &

CMU SCS Example - Mini-U • • • Students register Students enroll in courses

CMU SCS reg. form reg. enroll. form transc. req. enroll student rec. transcr. class

CMU SCS reg. form enroll. form reg. transc. req. enroll student rec. transcr. class

CMU SCS Document + Task forms Top level diagram: only half of the info

CMU SCS Document list • • • D 1: registration form D 2: enrollment

CMU SCS Document forms • D 1: registration – ssn – name – address

CMU SCS Document forms - cont’d • D 3: transcript request form – ssn

CMU SCS Document forms - cont’d (Internal documents - VERY IMPORTANT) D 7: student

CMU SCS Document forms - cont’d D 8: class record – class-id – class-name

CMU SCS Document forms - cont’d • IMPORTANT POINTS – avoid redundancy in internal

CMU SCS Task List • • T 1: Registration T 2: Enrollment T 3:

CMU SCS Task forms • As in [R+Y] • not required for this homework

CMU SCS Database schema - E-R • from the internal documents • use their

CMU SCS E-R diagram for Mini-U ssn . . . addr. grade . .

CMU SCS Relational schema student( ssn, name, address) class( c-id, c-name, syllabus) takes(c-id, ssn,

CMU SCS SQL DDL statements create table student (ssn char(9), … ); create table

CMU SCS Task emulation T 1: Registration read ssn, name and address if (

CMU SCS Testing • For T 1 (registration), we check – duplicate ssn –

CMU SCS User’s manual Short (~1 page or less) - eg. , : •

CMU SCS Important points for Phase-I • No redundancy in the fields of internal

Slides: 35

Download presentation

CMU SCS Carnegie Mellon Univ. Dept. of Computer Science 15 -415/615 - DB Applications Lecture #19 (not in book) Database Design Methodology handout

CMU SCS 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 Faloutsos & Pavlo CMU SCS 15 -415/615 2

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

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

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

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

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

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

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

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

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

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

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

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

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

CMU SCS Document list • • • D 1: registration form D 2: enrollment for … D 7: student record D 8: class record Faloutsos & Pavlo } INTERNAL CMU SCS 15 -415/615 17

CMU SCS Document forms • D 1: registration – ssn – name – address Faloutsos & Pavlo D 2: enrollment ssn name List-of: course id course name CMU SCS 15 -415/615 18

CMU SCS Document forms - cont’d • D 3: transcript request form – ssn – name Faloutsos & Pavlo D 4: transcript ssn name List-of: class-id class name grade CMU SCS 15 -415/615 19

CMU SCS Document forms - cont’d (Internal documents - VERY IMPORTANT) D 7: student record – ssn – name – address Faloutsos & Pavlo CMU SCS 15 -415/615 20

CMU SCS Document forms - cont’d D 8: class record – class-id – class-name – syllabus – List-of • ssn • grade Faloutsos & Pavlo CMU SCS 15 -415/615 21

CMU SCS 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 Faloutsos & Pavlo CMU SCS 15 -415/615 22

CMU SCS Task List • • T 1: Registration T 2: Enrollment T 3: Transcript. . . Faloutsos & Pavlo CMU SCS 15 -415/615 23

CMU SCS 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 Faloutsos & Pavlo CMU SCS 15 -415/615 24

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

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

CMU SCS 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 3 NF at worst) Faloutsos & Pavlo CMU SCS 15 -415/615 28

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

CMU SCS Task emulation T 1: 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”} Faloutsos & Pavlo CMU SCS 15 -415/615 31

CMU SCS Testing • For T 1 (registration), we check – duplicate ssn – ssn with 9 digits • For T 2 (enrollment) we check – for valid ssn (9 digits) – for registered ssn – for valid c-id – for duplicate (ssn, c-id) entry Faloutsos & Pavlo CMU SCS 15 -415/615 33

CMU SCS 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> Faloutsos & Pavlo CMU SCS 15 -415/615 34

CMU SCS 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 Faloutsos & Pavlo CMU SCS 15 -415/615 35