CSC343H1 Final: 4. DB Design
Document Summary
Entity&relationship&model: modeling!=!mapping!entities!and!relationships!of!world!into!concepts!of!a!db, relational!model!represent!entities,!relationships!w/!relations, mapping!is!not!deterministics, entity!relationship!model!elements, entity&set!=!a!class!of!objects!that!have!properties!in!common,!and! autonomous!existence! ex. 1 1 n 1, 1 n n n ! of!the!relationships! !o example!er!diagram!with!keys: weak&entities!=!entities!that!requires!other!entities!to!identify!itself!uniquely, need!to!follow!one!or!more!manykone!relationships!from!the!entity! itself,!and!include!the!key!of!the!related!entities!from!the!connected! entity!sets! (foreign!keys), weak!entities!never!exist!alone, example:!uniquely!identify!a!player!in!the!players!entity!set! !for!each! course&participant! (about!5,000),!identified!by!a!code,!we!want!to!store! her!social!security!number,!surname,!age,!sex,!place!of!birth,!employer"s! name,!address!and!telephone!number,!previous!employers! (and!periods! employed),!courses!attended! (there!are!about!200!courses)!and!the!final! assessment!for!each!course. Thanks to ryan johnson, john mylopoulos, arnold rosenbloom and renee miller for material in these slides. Database design theory: guides systematic improvements to database schemas, general idea: Goal #1: remove redundancy: consider this schema. Need to create a new course, csc222 (insertion anomaly) Goal #2: expressing constraints: consider the following sets of schemas: Functional dependencies: let x, y be sets of attributes from relation r, x -> y is an assertion about tuples in r. Any tuples which agree in all attributes of x must also agree in all attributes of y: x functionally determines y . Or, the values of attributes y are a function of those in x . Not necessarily an easy function to compute, mind you.
