2/16/10&1&Database&Design&CISC437/637,&Lecture&#2&Ben&Cartere=e&1&Copyright&©&Ben&Cartere=e&IntroducDon&to&Database&Design&• Why&use&a&database?&– You&have&data&– You&want&to&be&able&to&ask&quesDons&of&that&data&and&get&answers&fast&• How&do&you&design&a&database&in&general?&• How&do&you&design&a&database&specifically&to&support&the&quesDons&you&want&to&ask?&2&Copyright&©&Ben&Cartere=e&2/16/10&2&Design&Decisions&• Designing&something&is&a&process&of&making&decisions&and&implemenDng&them&– Choices&should&be&analyzed&in&terms&of&how&well&they&support&what&you&want&to&do&and&their&cost&• Not&so&much&as&“right”&versus&“wrong”&• Yet&each&choice&must&be&carefully&analyzed&– In&some&cases,&higherUlevel&decisions&force&certain&lowerUlevel&decisions&Copyright&©&Ben&Cartere=e& 3&Database&Design&• A&database&is&designed&within&a&parDcular&data&model&– Recall:&&a&data$model&is&a&collecDon&of&highUlevel&data&descripDon&constructs&• A&data&model&imposes&limitaDons&on&what&decisions&can&be&made&– LimitaDons&can&be&good…&&with&no&limitaDons,&anything&goes&– LimitaDons&can&be&bad…&&they&restrict&the&realUworld&scenarios&that&can&be&accommodated&Copyright&©&Ben&Cartere=e& 4&2/16/10&3&Designing&a&Database&• Basic&steps&in&designing&a&database:&– Understand&the&data&and&what&applicaDons&it&needs&to&support&– Use&that&understanding&to&model&the&data&with&conceptual$schema&– Implement&the&conceptual&schema&with&logical$schema&in&the&DBMS&– Refine—you&won’t&get&it&right&the&first&Dme!&Copyright&©&Ben&Cartere=e& 5&Understanding&the&Data&• Requirements$analysis&i s&the&process&of&understanding&the&data&and&what&it&is&used&for&• Example&requirements:&Copyright&©&Ben&Cartere=e& 6&“I&would&like&my&customers&to&be&able&to&browse&my&catalog&of&books&and&place&orders&over&the&Internet.&&Currently,&I&take&orders&over&the&phone.&&I&have&mostly&corporate&customers&who&call&me&and&give&me&the&ISBN&number&of&a&book&and&a&quanDty;&they&oden&pay&by&credit&card.&&I&then&prepare&a&shipment&that&contains&the&books&they&ordered.&&If&I&don’t&have&enough&copies&in&stock,&I&order&addiDonal&copies&and&delay&the&shipment&unDl&the&new&copies&arrive;&I&want&to&ship&a&customer’s&enDre&order&together.&&My&catalog&includes&all&the&books&I&sell.&&For&each&book,&the&catalog&contains&its&ISBN&number,&Dtle,&author,&purchase&price,&sales&price,&and&the&year&the&book&was&published.&&Most&of&my&customers&are&regulars,&and&I&have&records&with&their&names&and&addresses.&&New&customers&have&to&call&me&first&and&establish&an&account&before&they&can&use&my&website.&On&my&new&website,&customers&should&first&idenDfy&themselves&by&their&unique&customer&idenDficaDon&number.&Then&they&should&be&able&to&browse&my&catalog&and&to&place&orders&online.”&2/16/10&4&EnDtyURelaDonship&Model&• The&en5ty6rela5onship$model&describes&data&in&terms&of&objects&and&relaDonships&between&them&– A&conceptual&descripDon&• The&ER&model&is&a&highUlevel,&seman5c&model&of&data&meant&to&be&a&simple&representaDon&of&data&as&users&think&about&it&Copyright&©&Ben&Cartere=e& 7&EnDDes&• An&en5ty&is&an&object&in&the&real&world&– A&university&called&“The&Ohio&State&University”&– A&person&named&“Ben&Cartere=e”&• En5ty$sets&are&collecDons&of&similar&enDDes&– UniversiDes,&people,&…&• EnDDes&are&described&by&a8ributes&– A&university&has&a&name,&locaDon,&president,&…&– A&person&has&a&name,&address,&SS&number,&…&• All&of&the&enDDes&in&a&enDty&set&have&the&same&a=ributes&Copyright&©&Ben&Cartere=e& 8&2/16/10&5&What&are&the&EnDty&Sets?&Copyright&©&Ben&Cartere=e& 9&“I&would&like&my&customers&to&be&able&to&browse&my&catalog&of&books&and&place&orders&over&the&Internet.&&Currently,&I&take&orders&over&the&phone.&&I&have&mostly&corporate&customers&who&call&me&and&give&me&the&ISBN&number&of&a&book&and&a&quanDty;&they&oden&pay&by&credit&card.&&I&then&prepare&a&shipment&that&contains&the&books&they&ordered.&&If&I&don’t&have&enough&copies&in&stock,&I&order&addiDonal&copies&and&delay&the&shipment&unDl&the&new&copies&arrive;&I&want&to&ship&a&customer’s&enDre&order&together.&&My&catalog&includes&all&the&books&I&sell.&&For&each&book,&the&catalog&contains&its&ISBN&number,&Dtle,&author,&purchase&price,&sales&price,&and&the&year&the&book&was&published.&&Most&of&my&customers&are&regulars,&and&I&have&records&with&their&names&and&addresses.&&New&customers&have&to&call&me&first&and&establish&an&account&before&they&can&use&my&website.&On&my&new&website,&customers&should&first&idenDfy&themselves&by&their&unique&customer&idenDficaDon&number.&Then&they&should&be&able&to&browse&my&catalog&and&to&place&orders&online.”&EnDDes&and&A=ributes&• Each&a=ribute&has&a&domain&of&possible&values&– University&name:&&domain&is&all&256Ucharacter&strings&– SS&number:&&all&9Udigit&numbers&• A=ributes&do&not&have&to&be&unique&to&enDDes&• A&key&is&a&set&of&a=ributes&that&uniquely&idenDfy&an&enDty&in&the&set&– Person:&&SS&number&(in&the&U.S.)&– University:&&(name,&campus,&state)&• The&primary$key&is&one&of&those&a=ributes&Copyright&©&Ben&Cartere=e& 10&2/16/10&6&RelaDonships&• A&rela5onship&is&an&associaDon&between&two&or&more&enDDes&– Ben&Cartere=e&owns&Database&Management&Systems&(3rd&ed.)&• A&rela5onship$set&collects&similar&relaDonships&together&– {&(e1,&…,&en)&|&e1&∈&E1,&…,&en&∈&En&}&– {&(p,&b)&|&p&in&Person,&b&in&Book&}&&the&set&of&all&persons&p&and&books&b&such&that&p&owns&b&Copyright&©&Ben&Cartere=e& 11&What&are&the&RelaDonship&Sets?&Copyright&©&Ben&Cartere=e&