9/9/08 1 Instructor:*Amol*Deshpande********************[email protected]* Data*Models*◦ Conceptual*representa8on*of*the*data* Data*Retrieval*◦ How*to*ask*ques8ons*of*the*database*◦ How*to*answer*those*ques8ons* Data*Storage*◦ How/where*to*store*data,*how*to*access*it* Data*Integrity*◦ Manage*crashes,*concurrency*◦ Manage*seman8c*inconsistencies*9/9/08 2 Overview*of*modeling* En8ty‐rela8onship*Model*(E/R*model)* Rela8onal*Model* Conver8ng*from*E/R*to*Rela8onal* Extra*slides** Goals:*◦ Conceptual*representa8on*of*the*data*◦ “Reality”*meets*“bits*and*bytes”*◦ Must*make*sense,*and*be*usable*by*other*people* We*will*study:*◦ En8ty‐rela8onship*Model*◦ Rela8onal*Model* Note*the*difference*!!*◦ May*study*XML‐based*models*or*object‐oriented*models* Why*so*many*models*?*9/9/08 3 Why*so*many*models*?* Tradeoff*between:* Descrip8ve*capabili8es* How*many*concepts*it*has*?* What*can’t*it*capture*?* Is*it*reasonably*easy*for*humans*to*use*and*reason*about*?* Performance* Can*it*be*implemented*reasonably*efficiently*?* You’ve*just*been*hired*by*Bank*of*America*as*their*DBA*for*their*online*banking*web*site.* You*are*asked*to*create*a*database*that*monitors:*◦ customers*◦ accounts*◦ loans*◦ branches*◦ transac8ons,*…* Now*what??!!!*◦ Start*with*modeling*the*informa8on*that*needs*to*be*stored..*9/9/08 4 7 Three*Levels*of*Modeling*info Conceptual Data Model Logical Data Model Physical Data Model Conceptual DB design Logical DB design Physical DB design Entity-relationship Model Typically used for conceptual database design Relational Model Typically used for logical database design Overview*of*modeling* En8ty‐rela8onship*Model*(E/R*model)* Rela8onal*Model* Conver8ng*from*E/R*to*Rela8onal* Extra*slides**9/9/08 5 Two*key*concepts*◦ En##es:* An*object*that*exists*and*is*dis#nguishable*from*other*objects* Examples:*Bob*Smith,*BofA,*CMSC424* Have*a0ributes*(people*have*names*and*addresses)* Form*en#ty3sets*with*other*en88es*of*the*same*type*that*share*the*same*proper8es* Set*of*all*people,*set*of*all*classes* En8ty*sets*may*overlap* Customers*and*Employees* Two*key*concepts*◦ Rela#onships:* Relate*2*or*more*en88es** E.g.*Bob*Smith*has3account3at*College*Park*Branch* Form*rela#onship3sets*with*other*rela8onships*of*the*same*type*that*share*the*same*proper8es* Customers*have3accounts3at*Branches* Can*have*abributes:* has3account3at*may*have*an*abribute*start‐date3 Can*involve*more*than*2*en88es* Employee*works3at*Branch*at*Job*9/9/08 6 Rectangles:*en8ty*sets* Diamonds:*rela8onship*sets* Ellipses:*abributes*11 customer has cust-street cust-id cust-name cust-city account balance number access-date Details*of*the*ER*Model*◦ How*to*represent*various*types*of*constraints/seman8c*informa8on*etc.* Design*issues** A*detailed*example*9/9/08 7 We*may*know:* One*customer*can*only*open*one*account* *****************************OR* One*customer*can*open*mul8ple*accounts* Represen8ng*this*is*important* Why*?*◦ Beber*manipula8on*of*data* If*former,*can*store*the*account*info*in*the*customer*table*◦ Can*enforce*such*a*constraint* Applica8on*logic*will*have*to*do*it;*NOT*GOOD*◦ Remember:*If*not*represented*in*conceptual*model,*the*domain*knowledge*may*be*lost* Express*the*number*of*en88es*to*which*another*en8ty*can*be*associated*via*a*rela8onship*set* Most*useful*in*describing*binary*rela8onship*sets*9/9/08 8 One‐to‐One* One‐to‐Many* Many‐to‐One* Many‐to‐Many*customer has account customer has account customer has account customer has account Express*the*number*of*en88es*to*which*another*en8ty*can*be*associated*via*a*rela8onship*set* Most*useful*in*describing*binary*rela8onship*sets* N‐ary*rela8onships*?*◦ More*complicated*◦ Details*in*the*book*9/9/08 9 Simple*vs*Composite*◦ Single*value*per*abribute*?* Single‐valued*vs*Mul8‐valued*◦ E.g.*Phone*numbers*are*mul8‐valued* Derived*◦ If*date‐of‐birth*is*present,*age*can*be*derived*◦ Can*help*in*avoiding*redundancy,*enforcing*constraints*etc…*customer has cust-street cust-id cust-name cust-city account balance number access-date9/9/08 10 customer cust-street cust-id cust-name cust-city has account balance number access-date phone no. date-of-birth age month day year Composite Attribute Key*=*set*of*abributes*that*uniquely*iden8fies*an*en8ty*or*a*rela8onship*9/9/08 11 customer cust-street cust-id cust-name cust-city phone no. age date-of-birth Possible Keys: {cust-id} {cust-name, cust-city, cust-street} {cust-id, age} cust-name ?? Probably not. Domain knowledge dependent !! Superkey3◦ any*abribute*set*that*can*dis8nguish*en88es* Candidate3key3◦ a*minimal*superkey* Can’t*remove*any*abribute*and*preserve*key‐ness* {cust‐id,*age}*not*a*candidate*key** {cust‐name,*cust‐city,*cust‐street}*is** assuming*cust‐name*is*not*unique* Primary3key3◦ Candidate*key*chosen*as*the*key*by*DBA*◦ Underlined*in*the*ER*Diagram*9/9/08 12 {cust‐id}*is*a*natural*primary*key* Typically,*SSN*forms*a*good*primary*key* Try*to*use*a*candidate*key*that*rarely*changes*◦ e.g.*something*involving*address*not*a*great*idea*customer cust-street cust-id cust-name cust-city phone no. age date-of-birth What*abributes*are*needed*to*represent*a*rela8onship*completely*and*uniquely*?*◦ Union*of*primary*keys*of*the*en88es*involved,*and*rela8onship*abributes*◦ {cust‐id,*access‐date,*account*number}*describes*a*rela8onship*completely*customer has cust-id account number access-date9/9/08 13 Is*{cust‐id,3access‐date,3account3number}3a*candidate*key3?*◦ No.*Abribute*access‐date*can*be*removed*from*this*set*without*losing*key‐ness*◦ In*fact,*union*of*primary*keys*of*associated*en88es*is*always*a*superkey*customer has cust-id account number access-date
View Full Document
Unlocking...