Baixe o app para aproveitar ainda mais
Prévia do material em texto
28 SQL Magazine ��&TUVEPT�EF�$BTP�EF�1SPKFUPT�EF�#BODPT�EF�%BEPT Estudos de Caso de Projetos de Bancos de Dados Ary Júnior (ary@ajsolucoes.com.br) é Mestre em Ciências com ênfase em Inteligên- cia Artificial e Bancos de Dados, Professor Uni- versitário e Gerente de TI. Atua como professor desde 2000, especialmente com disciplinas na área de Bancos de Dados. Na área de TI desde 1998 com desenvolvimento, análise e projeto de Sistemas. Hoje em dia, trabalha com Gerencia- mento de Projetos e Sistemas de Bancos de Da- dos prestando serviços para diversas empresas nacionais e multinacionais. [ Desenvolvimento ] e�VDELGR�TXH�R�REMHWLYR�GH�XP�SUR-MHWR�GH�EDQFR�GH�GDGRV�p�REWHU�XP�FRQMXQWR�GH�HVTXHPDV�GH�UHODo}HV� TXH�QRV�SHUPLWD�DUPD]HQDU�GDGRV�VHP� UHGXQGkQFLD�H�TXH�DV�LQIRUPDo}HV�QHFHV- ViULDV�SDUD�WRPDGDV�GH�GHFLV}HV�SRVVDP� VHU�JHUDGDV�IDFLOPHQWH��$VVLP��SDUD�TXH� XP�SURMHWR�GH� EDQFR�GH�GDGRV�SRVVD� DWHQGHU�D�HVWHV�SUHVVXSRVWRV��DSOLFDPRV� R�TXH�SRGHPRV�FKDPDU�GH�QRUPDOL]DomR� GRV�GDGRV�� 1D� HGLomR� Q~PHUR� ���GHVWD� UHYLVWD�� DSUHVHQWDPRV�RV� FRQFHLWRV� HQYROYLGRV� QD�QRUPDOL]DomR�GH�XP�SURMHWR�GH�EDQFR� GH�GDGRV�� 1HVWH�DUWLJR��XWLOL]DUHPRV�RV�FRQFHLWRV� FLWDGRV�QD�UHIHULGD�HGLomR�H�DERUGDUHPRV� R�SURMHWR�GH�XP�EDQFR�GH�GDGRV�SDUD� XPD�ELEOLRWHFD�H�SDUD�XP�VLVWHPD�GH�RU- GHQV�GH�VHUYLoR��FRP�DOJXPDV�UHVWULo}HV� GH�HVFRSR��TXH�GHWDOKDUHPRV�D�VHJXLU�� (VFROKHPRV�HVWHV�SURMHWRV�GHYLGR�DR� IDWR� GDV� UHJUDV� GH� QHJyFLR� VHUHP�GH� IiFLO�FRPSUHHQVmR��(QWUHWDQWR��HOHV�SRV- VXHP�YiULRV�SRQWRV�GH�TXHVWLRQDPHQWR� SRU� SDUWH� GRV� SURMHWLVWDV� GH� EDQFRV� GH�GDGRV��8PD�JUDQGH�SDUFHOD�GHVWHV� SURÀVVLRQDLV�WHQGH�D�FRPHWHU�JUDQGHV� HUURV� QRV�PDLV� GLYHUVRV� SURMHWRV� GH� EDQFRV�GH�GDGRV�H�QHVWHV�GRLV�H[HPSORV�� FRQVHJXLUHPRV�DERUGDU�RV�SRQWRV�TXH� GHYHP�VHU�FRQVLGHUDGRV��GHVGH�SURMHWRV� GH�SHTXHQR�SRUWH��DWp�JUDQGHV�SURMHWRV� GH�EDQFRV�GH�GDGRV� $VVLP��LQLFLDOPHQWH�DSUHVHQWDUHPRV�R� HVWXGR�GH�FDVR�SDUD�D�ELEOLRWHFD�H�SRVWH- ULRUPHQWH�R�GH�RUGHQV�GH�VHUYLoR� Biblioteca 7RGRV�QyV�Mi�IUHTHQWDPRV�ELEOLRWHFDV� HP�DOJXP�PRPHQWR�GH�QRVVD�YLGD�DFD- GrPLFD��(P�OLQKDV�JHUDLV��XPD�ELEOLRWHFD� SRVVXL�OLYURV�TXH�GHYHP�VHU�HPSUHVWDGRV� H�GHYROYLGRV��1HVWH�HVWXGR�GH�FDVR�HP� SDUWLFXODU��UHGX]LUHPRV�R�HVFRSR�GH�XPD� ELEOLRWHFD�FRP�D�ÀQDOLGDGH�GH�IDFLOLWDU�D� FRPSUHHQVmR�GR�PRGHOR�GH�GDGRV�TXH� DSUHVHQWDUHPRV��(QWUHWDQWR��QDGD�LPSHGH� TXH�R�OHLWRU�DFUHVFHQWH�RXWUDV�IXQFLRQDOL- GDGHV�QR�PRGHOR�DTXL�DSUHVHQWDGR� SQL50.indb 28 07.01.08 18:40:52 Edição 50 - SQL Magazine 29 MODEL AGEM $VVLP��XP�VLVWHPD�GH�ELEOLRWHFDV��SDUD� HVWH�HVWXGR�GH�FDVR��FRQVLVWH�GDV�VHJXLQ- WHV�IXQFLRQDOLGDGHV� %�&DGDVWUR� GH� /LYURV��8P� FDGDVWUR� GH� OLYUR�GHYH� DUPD]HQDU� LQIRUPDo}HV� UHODWLYDV� DR� 7tWXOR� GR� OLYUR�� (GLWRUD�� (GLomR��$QR� GH� 3XEOLFDomR��$XWRUHV�� $VVXQWR��6DEH�VH�WDPEpP�TXH�XP�OLYUR� SRGH�SRVVXLU�YiULRV�H[HPSODUHV�H�QHVWH� FDVR��VmR�HPSUHVWDGRV�RV�H[HPSODUHV�H� QmR�RV�WtWXORV� %�&DGDVWUR�GH�$OXQRV��8P�FDGDVWUR�GH� DOXQR�GHYH�SRVVXLU�Q~PHUR�GH�PDWUt- FXOD��QRPH��HQGHUHoR��WHOHIRQH��WHOHIRQH� FHOXODU��&3)��5*��H�PDLO� %�&DGDVWUR�GH�3URIHVVRUHV��8P�FDGDVWUR� GH�SURIHVVRU�GHYH�SRVVXLU�QRPH��HQGH- UHoR��WHOHIRQH��WHOHIRQH�FHOXODU��&3)��5*�� H�PDLO��WLWXODomR� %�(PSUpVWLPR�GH�/LYURV��(VWD�IXQFLRQD- OLGDGH�UHIHUH�VH�DR�HPSUpVWLPR�GH�OLYURV� SURSULDPHQWH�GLWD�DRV�DOXQRV�RX�SURIHV- VRUHV�GD� LQVWLWXLomR��(VWH� HPSUpVWLPR� GHYH�DUPD]HQDU�D�GDWD�GR�HPSUpVWLPR�� GDWD�SUHYLVWD�GH�GHYROXomR�� H[HPSODU� HPSUHVWDGR� %�'HYROXomR�GH�/LYURV��$SyV� VHUHP� HPSUHVWDGRV��RV�OLYURV�SRGHP�VHU�GHYRO- YLGRV��1HVWH�SRQWR��RV�OLYURV�SRGHP�WHU� GHYROXo}HV�SDUFLDLV��RX�VHMD��XP�DOXQR� SRGH�SHJDU�HPSUHVWDGR���OLYURV�GLIHUHQ- WHV�H�TXHUHU�GHYROYHU�DSHQDV�XP� 'HVWD�IRUPD��QHVWH�DUWLJR�QmR�DERUGD- UHPRV�R�SDJDPHQWR�GH�PXOWDV�� DVVLP� FRPR� R� FDGDVWUR� GH� SHULyGLFRV� TXH� WDPEpP�H[LVWHP�HP�ELEOLRWHFDV��SHOR�IDWR� GH�HQWHQGHUPRV�TXH�IRJHP�GR�REMHWLYR� GHVWH�H[HPSOR��7DPEpP�QmR�VHUi�WUDWDGR� R�HPSUpVWLPR�GH� OLYURV�D� IXQFLRQiULRV� FRP�UHJUDV�GLVWLQWDV��SHOR�PHVPR�PR- WLYR��(P�GLYHUVDV� LQVWLWXLo}HV� H[LVWHP� FULWpULRV�GLVWLQWRV�GH�HPSUpVWLPRV�SDUD� SURIHVVRUHV�� DOXQRV� H� IXQFLRQiULRV� H� QHVWH�DUWLJR�WDPEpP�QmR�VHUmR�WUDWDGDV� HVWDV�GLIHUHQFLDo}HV�GH�UHJUDV� 7DPEpP�QmR� VHUmR�DERUGDGDV�RXWUDV� IXQFLRQDOLGDGHV� FRPR�HPSUpVWLPR�GH� H[HPSODUHV�~QLFRV�� HPSUpVWLPR�GH� Ip- ULDV��UHVHUYD�H�UHQRYDomR�GH�OLYURV� $�SDUWLU�GHVWD�SHTXHQD�GHÀQLomR�GDV� UHJUDV�GH�QHJyFLR� HP�TXHVWmR�� LUHPRV� LQLFLDU�D�PRGHODJHP�GH�GDGRV��1mR�IRU- PDOL]DPRV�HVWD�GHÀQLomR�GDV�UHJUDV�GH� QHJyFLR�WDPEpP�SRU�IXJLU�GRV�REMHWLYRV� GHVWH�DUWLJR� Na Figura 1�DSUHVHQWDPRV�D�SULPHLUD� SDUWH�GR�PRGHOR�GH�GDGRV��TXH�FRPSUH- HQGH�R�FDGDVWUR�GH�OLYURV� (P�SRVVH�GD�7DEHOD�/LYUR��Figura 1��� SUHFLVDUHPRV�YHULÀFDU� VH� HOD� HVWD�QRU- PDOL]DGD��2�SULPHLUR�SDVVR�p�YHULÀFDU� VH� HOD� HVWi� QD� �)1� �3ULPHLUD� )RUPD� 1RUPDO���$��)1�GL]�TXH�´8PD�WDEHOD�VH� HQFRQWUD�QD��)1�VH� WRGRV�RV� DWULEXWRV� SRVVXtUHP� DSHQDV� YDORUHV� DW{PLFRV� �VLPSOHV�H� LQGLYLVtYHLV��H�RV�YDORUHV�GH� FDGD�DWULEXWR�QR�UHJLVWUR�WDPEpP�GHYH� VHU�XP�YDORU�VLPSOHV��RX�VHMD��R�DWULEXWR� QmR�p�FRPSRVWR�µ� 3RGHPRV�SHUFHEHU�TXH�HVWD�WDEHOD�SRV- VXL�RV�DWULEXWRV�$XWRUHV�H�$VVXQWR��TXH� VmR�PXOWLYDORUDGRV��RX� VHMD��SDUD� FDGD� OLYUR��SRGHPRV�WHU�PDLV�GH�XP�DXWRU�H� PDLV�GH�XP�DVVXQWR��$VVLP��GHYHPRV� VHSDUDU�HVWHV�DWULEXWRV�HP�RXWUDV�WDEHODV�� $VVLP��QD�Figura 2�WHPRV�R�PRGHOR�GH� GDGRV�QRUPDOL]DGR�VHJXQGR�D���)1� 3RGHPRV�SHUFHEHU�TXH�QR�PRGHOR�GH� GDGRV�GD�Figura 2� IRUDP� LQVHULGDV� DV� Figura 1. Tabela Livro Desnormalizada Figura 2��.PEFMP�EF�%BEPT�-JWSP�o�"TTVOUP�o�"VUPSFT WDEHODV�$VVXQWR��$XWRU��/LYURB$VVXQWR�H� /LYURB$XWRU��$�WDEHOD�$XWRU�DUPD]HQD�R� FDGDVWUR�EiVLFR�GRV�DXWRUHV��DVVLP�FRPR�D� WDEHOD�DVVXQWR�DUPD]HQD�RV�PDLV�GLYHUVRV� DVVXQWRV�GH�OLYURV��$�WDEHOD�/LYURB$XWRU�p� UHVSRQViYHO�SRU�UHVROYHU�R�UHODFLRQDPHQ- WR�Q�P�HQWUH�OLYUR�H�DXWRU��RX�VHMD��XP�OLYUR� SRGH�WHU�YiULRV�DXWRUHV�H�FDGD�DXWRU�SRGH� WHU�YiULRV� OLYURV�SXEOLFDGRV��'D�PHVPD� IRUPD��D�WDEHOD�/LYURB$VVXQWR� 2�SUy[LPR�SDVVR�p�YHULÀFDU�VH�R�PR- GHOR�GH�GDGRV�GD�Figura 2�VH�HQFRQWUD� QD� �)1� �6HJXQGD� )RUPD�1RUPDO���$� �)1�GL]�TXH�´8PD� WDEHOD� VH� HQFRQWUD� QD��)1�VH�HVWLYHU�QD��)1�H�QmR�SRVVXLU� GHSHQGrQFLD� IXQFLRQDO� SDUFLDO�� &DVR� H[LVWDP�DWULEXWRV� TXH�QmR�GHSHQGDP� LQWHJUDOPHQWH�GD� FKDYH�SULPiULD�� GH- YHPRV�UHWLUDU�GD�WDEHOD�WRGRV�HOHV�H�GDU� RULJHP�D�XPD�QRYD�WDEHOD�µ�(QWUHWDQWR�� na Figura 2�SHUFHEHPRV�TXH�DSHQDV�DV� WDEHODV�/LYURB$XWRU� H� /LYURB$VVXQWR� SRVVXHP�FKDYHV�SULPiULDV� FRPSRVWDV�� $OpP�GLVVR��DPEDV�SRVVXHP�DSHQDV�RV� DWULEXWRV�TXH�FRPS}HP�D�FKDYH��/RJR�� HODV�HVWmR�QD��)1� $JRUD��LUHPRV�YHULÀFDU�VH�R�PRGHOR�GH� GDGRV�GD�Figura 2�HQFRQWUD�VH�QD��)1� �7HUFHLUD�)RUPD�1RUPDO���$��)1�GL]�TXH� ´8PD�WDEHOD�HVWi�QD��)1�VH�HVWLYHU�QD� �)1�H�QmR�SRVVXLU�QHQKXPD�GHSHQGrQ- FLD�IXQFLRQDO�WUDQVLWLYDµ��,VWR�TXHU�GL]HU� TXH�QHQKXP�DWULEXWR�SRGH�GHSHQGHU� GH�RXWUR�DWULEXWR�TXH�QmR�VHMD�D�FKDYH� SULPiULD��$VVLP�� SHUFHEHPRV� TXH� QD� WDEHOD�/LYUR�GD�Figura 2�H[LVWH�R�DWULEXWR� (GLWRUD�TXH�QmR�GHSHQGH�GH�&RG/��TXH�p� FKDYH�SULPiULD��'HVWD�IRUPD��SUHFLVDUH- PRV�FULDU�RXWUD�WDEHOD�SDUD�DUPD]HQDU�R� FDGDVWUR�GH�(GLWRUDV� SQL50.indb 29 07.01.08 18:40:55 30 SQL Magazine ��&TUVEPT�EF�$BTP�EF�1SPKFUPT�EF�#BODPT�EF�%BEPT Figura 3��.PEFMP�EF�%BEPT�-JWSP�o�"TTVOUP�o�"VUPSFT���&EJUPSB 3RGHPRV�SHUFHEHU� TXH� R�PRGHOR�GH� GDGRV�GD�Figura 3�HQFRQWUD�VH�QD��)1�� &RQIRUPH�DERUGDPRV�QR�DUWLJR�´1RU- PDOL]DomR�GH�'DGRVµ��GHVWD�UHYLVWD��HP� VXD�HGLomR�GH�Q~PHUR�����SRGHPRV�DEULU� PmR�GDV�RXWUDV�IRUPDV�QRUPDLV��%&1)� �)RUPD�1RUPDO�GH�%R\FH�&RGG���QD��)1� �4XDUWD�)RUPD�1RUPDO��H�QD��)1��4XLQ- WD�)RUPD�1RUPDO���,VWR�SRGH�RFRUUHU�VHP� SUHMXt]R�SDUD�HVWH�PRGHOR�GH�GDGRV��SRLV� QmR�YHULÀFDPRV�GHSHQGrQFLDV� IXQFLR- QDLV�FtFOLFDV�RX�PXOWLYDORUDGDV� �(QWUHWDQWR��SUHFLVDPRV�UHVROYHU�PDLV� XP�LWHP�UHODFLRQDGR�DR�FDGDVWUR�GH�OL-YURV�GHÀQLGR�QR�LQtFLR�GHVWH�WySLFR�TXH�p� D�H[LVWrQFLD�GH�H[HPSODUHV�GH�OLYURV��3DUD� LVWR�� EDVWD� FULDU� XPD� WDEHOD� H[HPSODU� FRQIRUPH�D�Figura 4� $VVLP�� R�PRGHOR�GH�GDGRV�GH� OLYUR� ÀFD�FRPSOHWR��1HVWH�FDVR��YDOH�UHVVDOWDU� XP�SRQWR� FRP�UHODomR�jV� LQIRUPDo}HV� TXH�SRGHP�VHU�JHUDGDV� D�SDUWLU�GHVWH� PRGHOR� GH� GDGRV�� 3RGHPRV� H[WUDLU� GLYHUVDV� LQIRUPDo}HV��GHQWUH� DV�TXDLV� SRGHPRV�FLWDU� %�5HODomR�GH�OLYURV�FDGDVWUDGRV� %�5HODomR�GH�HGLWRUDV�FDGDVWUDGDV� %�5HODomR�GH�DXWRUHV�FDGDVWUDGRV� %�5HODomR�GH�DVVXQWRV�FDGDVWUDGRV� %�5HODomR�GH�OLYURV�FRP�VHXV�UHVSHFWL- YRV�DXWRUHV� %�5HODomR�GH�OLYURV�FRP�VHXV�UHVSHFWL- YRV�DVVXQWRV� %�4XDQWLGDGH�GH�H[HPSODUHV�SRU�OLYUR� 5HSDUH�TXH�QHVWD�XOWLPD� LQIRUPDomR�� SUHFLVDUHPRV�HIHWXDU�XP�FiOFXOR�D�FDGD� FRQVXOWD� HIHWXDGD�QR�EDQFR�GH�GDGRV�� 2EYLDPHQWH� TXH� XP� FiOFXOR� VLPSOHV� FRPR� HVWH� SRGH� VHU� EDVWDQWH� UiSLGR� (Listagem 1��� 3RUpP��SRGHPRV�DEULU�PmR�GD�QRUPDOL- ]DomR�GH�GDGRV�H�DUPD]HQDU�XP�DWULEXWR� TXDQWLGDGH�GH�H[HPSODUHV��TGDGHBH[HP- SODU��QD�WDEHOD�OLYUR�TXH�VHMD�UHVSRQViYHO� SRU�DUPD]HQDU�D�TXDQWLGDGH�GH�H[HP- SODUHV�SDUD�R�UHIHULGR�WtWXOR��(QWUHWDQWR�� D�GHÀQLomR�SHOD�XWLOL]DomR�GHVWH�DUWLItFLR� GHYH�VHU�H[FOXVLYDPHQWH�GR�OHLWRU��$VVLP�� R�PRGHOR�GH�GDGRV�FRQWHQGR�HVWH�DWULEXWR� HVWi�UHSUHVHQWDGR�QD�Figura 5�H�D�FRQVXOWD� 64/�QD�Listagem 2��3HUFHED�TXH�D�FRQVXO- ta da Listagem 2�p�PXLWR�PDLV�VLPSOHV� TXH�D�FRQVXOWD�GD�Listagem 1 e por con- VHTrQFLD��p�PXLWR�PDLV�UiSLGD� Figura 4��.PEFMP�EF�%BEPT�-JWSP�o�"TTVOUP�o�"VUPSFT�o�&EJUPSB���&YFNQMBS Listagem 1.�$POTVMUB�RVBOUJEBEF�EF�FYFNQMBSFT�QPS�MJWSP SELECT titulo, count (codex) FROM Livro, Exemplar WHERE livro.codl = exemplar.Livro_codl GROUP BY titulo Listagem 2. $POTVMUB�RVBOUJEBEF�EF�FYFNQMBSFT�QPS�MJWSP SELECT titulo, qdade_exemplar FROM Livro SQL50.indb 30 07.01.08 18:40:58 Edição 50 - SQL Magazine 31 MODEL AGEM Figura 5��.PEFMP�EF�%BEPT�-JWSP�o�"TTVOUP�o�"VUPSFT�o�&EJUPSB�o�&YFNQMBS � com quantidade de exemplares 5HSDUH�QR�PRGHOR�GH�GDGRV�GD�Figura 5�� &DVR�TXHLUDPRV�FDGDVWUDU�RXWUD�HGLomR� GH�XP�GHWHUPLQDGR�OLYUR��GHYHUHPRV�FD- GDVWUDU�RXWUR�OLYUR�FRP�R�PHVPR�WtWXOR�� ,VWR�p�XPD�UHGXQGkQFLD�TXH�SRGHUtDPRV� WHU� HYLWDGR��1HVWH� DUWLJR� QmR� LUHPRV� HIHWXDU�HVWD�PXGDQoD��3RUpP��R�OHLWRU�ID- FLOPHQWH�SRGHULD�ID]r�OD��VLPSOHVPHQWH� FULDQGR�RXWUD�WDEHOD�SDUD�DUPD]HQDU�R� WtWXOR�H�D�HGLWRUD��UHODFLRQDQGR�RV�FRP� OLYUR��,VWR�UHWLUDULD�D�UHGXQGkQFLD�H�SHU- PLWLULD�WDO�SRVVLELOLGDGH� )HLWD� D�PRGHODJHP�GH�GDGRV�SDUD� R� FDGDVWUR�GH�OLYURV��SRGHPRV�SDUWLU�SDUD� R� FDGDVWUR� GH� DOXQRV� H� SURIHVVRUHV�� &RQIRUPH�GHÀQLomR�QR�LQtFLR�GHVWH�Wy- SLFR��WDQWR�R�FDGDVWUR�GH�DOXQRV�TXDQWR� R� FDGDVWUR�GH�SURIHVVRUHV�SRVVXHP�RV� DWULEXWRV�� QRPH�� HQGHUHoR�� WHOHIRQH�� FHOXODU��&3)��5*�� H�PDLO��(QWUHWDQWR��R� DOXQR�SRVVXL�Q~PHUR�GH�PDWUtFXOD�H�R� SURIHVVRU�SRVVXL�WLWXODomR��(VWH�p�R�WtSLFR� FDVR�GH�UHODFLRQDPHQWRV�JHQHUDOL]DomR� HVSHFLDOL]DomR��$VVLP��SRGHPRV�WHU�XPD� WDEHOD�SHVVRD�UHVSRQViYHO�SRU�DUPD]HQDU� RV�UHJLVWURV�GDV�SHVVRDV��LQGHSHQGHQWH� VH�VmR�DOXQRV��SURIHVVRUHV�RX�DPERV��(� GHVWD�WDEHOD�SHVVRD��HVSHFLDOL]DPRV�HP� GXDV�QRYDV� WDEHODV�� TXH� VmR�� DOXQRV� H� SURIHVVRUHV� Na Figura 6��DSUHVHQWDPRV�R�PRGHOR� GH�GDGRV�GH�3HVVRD��'D�PHVPD� IRUPD� FRPR�WUDEDOKDPRV�QR�PRGHOR�GH�GDGRV� GH� OLYURV��Figura 1���SUHFLVDUHPRV�QRU- PDOL]DU� HVWH�PRGHOR�GH�GDGRV��$VVLP�� LQLFLDOPHQWH�YHULÀFDUHPRV�VH�HOH�HVWi�QD� �)1��5HSDUH�TXH�RV�DWULEXWRV��HQGHUHoR�� WHOHIRQH�� FHOXODU� H� H�PDLO� SRGHP� VHU� PXOWLYDORUDGRV��$OpP�GLVVR��R�DWULEXWR� HQGHUHoR�p�FRPSRVWR��'L]HPRV�TXH�HQ- GHUHoR�p�PXOWLYDORUDGR�SRUTXH�SRGHPRV� DUPD]HQDU�YiULRV�HQGHUHoRV�SDUD�XPD� PHVPD�SHVVRD��FRPR��HQGHUHoR�FRPHU- FLDO�� UHVLGHQFLDO��GHQWUH�RXWURV��$VVLP�� na Figura 7�WHPRV�R�PRGHOR�GH�GDGRV�GD� Figura 6�GH�DFRUGR�FRP�D��)1� 3RGHPRV�SHUFHEHU� TXH� R�PRGHOR�GH� GDGRV�GD�Figura 7�HQFRQWUD�VH�QD��)1� SRLV�QmR�SRVVXL�DWULEXWRV�PXOWLYDORUDGRV� H�QHP�DWULEXWRV�FRPSRVWRV��8P�JUDQGH� SRQWR�TXH�SRGHPRV�GLVFXWLU�p�TXH�QHV- WH�PRGHOR� QRUPDOL]DPRV� WDPEpP� DV� WDEHODV�7LSR/RJUDGRXUR�� /RJUDGRXUR�� %DLUUR��&LGDGH� H�8)�� 5HSDUH� TXH� HP� 7LSR�GH�/RJUDGRXUR�DUPD]HQDUHPRV�RV� Figura 7��.PEFMP�EF�%BEPT�-JWSP�1FTTPB��'/ Figura 6. Modelo de Dados Livro Pessoa SQL50.indb 31 07.01.08 18:41:01 32 SQL Magazine ��&TUVEPT�EF�$BTP�EF�1SPKFUPT�EF�#BODPT�EF�%BEPT PDLV�GLYHUVRV�WLSRV�GH�ORJUDGRXURV�TXH� H[LVWHP��WDLV�FRPR�UXD��DYHQLGD��SUDoD�� DODPHGD��GHQWUH�RXWURV��(P�/RJUDGRXUR� DUPD]HQDUHPRV�D�GHVFULomR�GR�HQGHUH- oR��FRPR��*HW~OLR�9DUJDV��TXH�SRGH�VHU� XPD�UXD��DYHQLGD��GHQWUH�RXWUDV�RSo}HV�� GHSHQGHQGR�GD�FLGDGH�HP�TXHVWmR��2V� DWULEXWRV�%DLUUR�� FLGDGH� H�8)� WDPEpP� GHYHP�VHU�WUDWDGRV�GD�PHVPD�IRUPD��(Q- WUHWDQWR��GHFLGLPRV�QmR�UHODFLRQDU�EDLUUR� FRP�FLGDGH�� H� EDLUUR� FRP� ORJUDGRXUR�� SDUD�QmR� VHU�QHFHVViULR�PDSHDU� WRGRV� RV� HQGHUHoRV� EUDVLOHLURV��2EYLDPHQWH� TXH� LVWR�SRGH�JHUDU� LQFRQVLVWrQFLD�QRV� GDGRV��FRPR��XPD�SHVVRD�PRUDU�HP�XPD� DYHQLGD�FKDPDGD�%UDVLO�H�RXWUD�PRUDU� HP�XPD�SUDoD�%UDVLO��QD�PHVPD�FLGDGH�� FDVR� R�XVXiULR�GR� VLVWHPD� HIHWXH�XP� FDGDVWUR�HUUDGR��3RUpP��GHL[DPRV�HVWD� GLVFXVVmR�GH� ODGR� H� R� OHLWRU� WHP� WRWDO� OLEHUGDGH�SDUD�DYDOLDU�R�VHX�FDVR�SDUD� GHFLGLU�FRPR�UHODFLRQDU�WDLV�WDEHODV�� 1HVWH� FDVR�� IL]HPRV� GHVWD�PDQHLUD�� SRLV�HVWDPRV�LQWHUHVVDGRV��SRU�H[HPSOR�� HP�VDEHU� %�4XDQWLGDGH�GH�SHVVRDV�TXH�UHVLGHP� HP�DYHQLGDV� %�4XDQWLGDGH�GH�SHVVRDV�TXH�UHVLGHP� HP�GHWHUPLQDGR� ORJUDGRXUR�� EDLUUR�� FLGDGH�RX�HVWDGR� 2XWUR�SRQWR� TXH�SRGHPRV�SHUFHEHU� p�TXH� UHDOPHQWH�XPD�SHVVRD�SRGH� WHU� YiULRV�H�PDLOV�H�FDGD�H�PDLO�p�GH�DSHQDV� XPD�SHVVRD��1D�WDEHOD�H�PDLO��DGLFLRQD- PRV�XP�DWULEXWR�7LSR(PDLO�SDUD�VHSD- UDUPRV�R�H�PDLO�HP�SHVVRDO��SURÀVVLRQDO�� GHQWUH�RXWURV�WLSRV�SRVVtYHLV��'D�PHVPD� IRUPD��FULDPRV�R�DWULEXWR�7LSR(QGHUHFR� QD�WDEHOD�HQGHUHoR�SDUD�VDEHUPRV�VH�R� HQGHUHoR�p�UHVLGHQFLDO��FRPHUFLDO��GHQWUH� RXWURV�WLSRV� 5HSDUH� WDPEpP�TXH� QHVWH� FRQWH[WR� FRQWLQXDPRV�DUPD]HQDQGR�XP� WHOHIR- QH� H�XP� FHOXODU��&DVR� VHMD�QHFHVViULR� DUPD]HQDU�PDLV�YDORUHV�FRPR�QR�FDVR� GR� HQGHUHoR� H� GR� H�PDLO�� GHYH�VH� WHU� XPD� WDEHOD�SDUD� WHOHIRQHV� UHODFLRQDGD� FRP�SHVVRD��1HVWH�FDVR��VXJHULPRV�TXH� VH�DGLFLRQH�WDPEpP�XP�DWULEXWR�WLSR�GH� WHOHIRQH�SDUD� LQIRUPDU� VH�R� WHOHIRQH� p� XP�WHOHIRQH�FHOXODU�RX�XP�WHOHIRQH�À[R�� 9HMD�TXH�GHVWD� IRUPD�� D� WDEHOD�SHVVRD� QmR� HVWi� WRWDOPHQWH� QD� �)1�XPD�YH]� TXH�HVWHV�DWULEXWRV�VmR�PXOWLYDORUDGRV�� (QWUHWDQWR�� QR� FRQWH[WR� GHVWH� DUWLJR� RSWDPRV�SRU�PDQWHU�GHVWD�IRUPD��SDUD� H[HPSOLÀFDU� D�GHVQRUPDOL]DomR�GHVWD� WDEHOD�� LQIRUPDQGR� TXH� GHSHQGHQGR� GH� FDGD� FDVR�SRGHPRV�RSWDU�SHOD�QmR� QRUPDOL]DomR�GH� DOJXPDV� WDEHODV� HP� GHWULPHQWR�GRV�FRQWH[WRV�GDV�DSOLFDo}HV� D�VHUHP�GHVHQYROYLGDV� 2�SUy[LPR�SDVVR�p�YHULÀFDU�VH�R�PR- GHOR�GH�GDGRV�GD�Figura 7�VH�HQFRQWUD� QD��)1��9HULÀFDPRV�TXH� VLP� �H[FHomR� VH� ID]�QD� WDEHOD�SHVVRD��SHORV�PRWLYRV� FLWDGRV�DQWHULRUPHQWH�DRV�DWULEXWRV�WHOH- IRQH�H�FHOXODU���XPD�YH]�TXH�HVWH�PRGHOR� QmR�SRVVXL� WDEHOD� FRP�FKDYH�SULPiULD� FRPSRVWD� $VVLP��LUHPRV�YHULÀFDU�D�DSOLFDomR�GD� �)1�QR�PRGHOR�GD�Figura 7�� 7DPEpP� YHULÀFDPRV�WDO�DSOLFDomR��H[FHomR�VH�ID]� Figura 8. Modelo de Dados Empréstimo QD�WDEHOD�SHVVRD��SHORV�PRWLYRV�FLWDGRV� DQWHULRUPHQWH� DRV� DWULEXWRV� WHOHIRQH� H� FHOXODU���XPD�YH]�TXH�QHQKXP�DWULEXWR� GH�QHQKXPD� WDEHOD�GHSHQGH�GH�RXWUR� DWULEXWR�TXH�QmR�VHMD�D�FKDYH�SULPiULD� GD�UHIHULGD�WDEHOD� 'HVWD� IRUPD�� SRGHPRV� SDUWLU� DJRUD� SDUD�RV� HPSUpVWLPRV� H�GHYROXo}HV�GH� OLYURV��3DUD�HIHWXDU� WDLV�Do}HV��SUHFLVD- UHPRV�XQLU� RV�PRGHORV�GH�GDGRV�GDV� Figuras 5 e 7 FRP�RV�HPSUpVWLPRV�H�FRP� DV�GHYROXo}HV� 3DUD�WUDWDUPRV�RV�HPSUpVWLPRV�WHPRV� GXDV�KLSyWHVHV��2X�DGRWDUHPRV�RV�HP- SUpVWLPRV�GH�FDGD�OLYUR�SRU�SHVVRD�LQGL- YLGXDOPHQWH�RX�SRU�LWHQV�GH�HPSUpVWLPR�� 2X� VHMD�� FDGD�SHVVRD�SRGH�SHJDU� HP- SUHVWDGR�YiULRV�OLYURV�GH�XPD�~QLFD�YH]�� $VVLP��FDGD�OLYUR�SRGH�VHU�WUDWDGR�FRPR�XP�HPSUpVWLPR�LVRODGR�RX�WRGRV�VHUHP� WUDWDGRV�HP�XP�~QLFR�HPSUpVWLPR��1HVWH� DUWLJR�LUHPRV�DERUGDU�FRPR�HPSUpVWLPRV� LVRODGRV�SRU�VLPSOHV�GHÀQLomR�GH�HVFRSR� GHVWH�SURMHWR�GH�EDQFR�GH�GDGRV� $VVLP��D�Figura 8�DSUHVHQWD�R�PRGHOR� GH�GDGRV� FRP�RV� HPSUpVWLPRV� H� FRP� DV�GHYROXo}HV��'HYHPRV� OHPEUDU� TXH� HVWDPRV�XWLOL]DQGR�D� WDEHOD�([HPSODU� da Figura 5�H�3HVVRD�GD�Figura 7��2�PR- GHOR�GH�GDGRV�FRPSOHWR�p�DSUHVHQWDGR� na Figura 9�� 5HSDUH�TXH�SRVVLELOLWDPRV� HPSUpVWL- PR�GH� H[HPSODUHV� DR� LQYpV�GH� OLYURV�� ,VWR�SRGH�QRV�GDU�VHJXUDQoD�DR�HIHWXDU� HPSUpVWLPRV��SRLV�FRQVHJXLUHPRV�VDEHU� TXDLV� H[HPSODUHV� IRUDP� HPSUHVWDGRV� SDUD� TXDLV� SHVVRDV��2EYLDPHQWH� TXH� SQL50.indb 32 07.01.08 18:41:03 Edição 50 - SQL Magazine 33 MODEL AGEM VDEHQGR�GHVWD�LQIRUPDomR��FRQVHJXLUH- PRV�GHVFREULU�TXDLV�RV�WtWXORV�GRV�OLYURV� TXH�IRUDP�HPSUHVWDGRV��$VVLP��FDVR�XP� H[HPSODU� VH�GDQLÀTXH��SRU� H[HPSOR�� p� SRVVtYHO� HIHWXDU�XPD�DXGLWRULD��(QWUH- WDQWR��FDVR�R�OHLWRU�QmR�TXHLUD�WUDEDOKDU� FRP�WDO�KLSyWHVH��SRGH�VH�WHU�XP�DWULEXWR� HP�OLYURV��SDUD�GL]HU�TXDO�D�TXDQWLGDGH� GH� H[HPSODUHV�GR� UHIHULGR� OLYUR�� FRQ- IRUPH�DERUGDPRV�DQWHULRUPHQWH��'HVWD� IRUPD��DR�HPSUHVWDU�XP�OLYUR��R�VLVWHPD� SUHFLVDULD�WHU�XPD�UHJUD�SDUD�YHULÀFDU�VH� WRGRV�RV�H[HPSODUHV�HVWmR�HPSUHVWDGRV�� XPD� YH]� TXH� FDGD� OLYUR� SRVVXL� XPD� TXDQWLGDGH�ÀQLWD�GH�H[HPSODUHV�H�QmR� SRGHPRV�HPSUHVWDU�XP� OLYUR�TXH�QmR� HVWHMD�QD�ELEOLRWHFD� 2XWUR�SRQWR�TXH�DERUGDPRV�QHVWH�PR- GHOR�GH�GDGRV�p�R�HPSUpVWLPR�SDUD�SHV- VRDV��,VWR�SRUTXH�QR�HVFRSR�GHVWH�DUWLJR�� FRQIRUPH�DERUGDGR�DQWHULRUPHQWH��VmR� WUDWDGRV�RV� HPSUpVWLPRV� H�GHYROXo}HV� WDQWR�SDUD�DOXQRV�TXDQWR�SDUD�SURIHVVR- UHV�VHP�GLVWLQo}HV��$VVLP��FRPR�RV�GRLV� VmR� HVSHFLDOL]Do}HV� GD� JHQHUDOL]DomR� SHVVRD��SRGHPRV�UHDOL]DU�R�HPSUpVWLPR� GLUHWDPHQWH�GH�SHVVRD� 3HUFHED�WDPEpP�TXH�SDUD�RV�HPSUpVWL- PRV�HVWDPRV�DUPD]HQDQGR�DSHQDV�GDWD� GR�HPSUpVWLPR�H�GDWD�SUHYLVWD�GH�GHYR- OXomR�� 3RGHUtDPRV� DUPD]HQDU� RXWURV� DWULEXWRV��3RUpP��R�OHLWRU�SRGH�ÀFDU�OLYUH� SDUD�DGLFLRQi�ORV�FDVR� WHQKD� LQWHUHVVH�� DVVLP�FRPR�QD�WDEHOD�GHYROXo}HV��7DP- EpP�FRQIRUPH�WUDWDPRV�QR�LQtFLR�GHVWH� DUWLJR��QmR�DERUGDPRV�R�SDJDPHQWR�GH� PXOWDV� FDVR� D� GHYROXomR� VHMD� IRUD�GR� SUD]R�GHWHUPLQDGR� 3HUFHEHPRV�WDPEpP�TXH�R�PRGHOR�GH� GDGRV�GD�Figura 8�HQFRQWUD�VH�QD��)1�� ([FHomR� VH� ID]�QD� WDEHOD�SHVVRD��SHORV� PRWLYRV�FLWDGRV�DQWHULRUPHQWH�DRV�DWUL- EXWRV�WHOHIRQH�H�FHOXODU� Na Figura 9� DSUHVHQWDPRV�R�PRGHOR� GH�GDGRV�ÀQDO�DSOLFDQGR�D��)1���)1�H� �)1��&RQIRUPH�IDODPRV�DQWHULRUPHQWH��D� WDEHOD�SHVVRD�HVWi�GHVQRUPDOL]DGD��FRP� UHODomR�DRV�DWULEXWRV�WHOHIRQH�H�FHOXODU�� YLVDQGR�DSUHVHQWDU� HVWD�SRVVLELOLGDGH� DRV�OHLWRUHV��)LTXH�j�YRQWDGH�SDUD�XWLOL]DU� H�PRGLÀFDU�WDO�PRGHOR�GH�DFRUGR�FRP�DV� VXDV�QHFHVVLGDGHV� 3HUFHED� WDPEpP�TXH�SRGHUtDPRV� WHU� WUDWDGR�RV� DXWRUHV� FRPR�XPD�HVSHFLD- OL]DomR�GH�SHVVRD��3RUpP��QR� FRQWH[WR� Figura 9��.PEFMP�EF�%BEPT�#JCMJPUFDB�$PNQMFUP GHVWH� DUWLJR�� RSWDPRV� SRU� QmR� ID]HU� WDO� HVSHFLDOL]DomR�� XPD�YH]� TXH� VLP- SOLÀFDPRV�R� FDGDVWUR�GH� DXWRUHV�SDUD� DUPD]HQDU�DSHQDV�R�QRPH�GHOHV��(�FDVR� HVWD�WDEHOD�WLYHVVH�VLGR�WUDWDGD�FRPR�XPD� HVSHFLDOL]DomR�GH�SHVVRD��SUHFLVDUtDPRV� SRVVXLU� RV�GDGRV�GH� WHOHIRQH�� FHOXODU�� &3)�H�5*�SDUD�TXH�VH�HIHWXDVVH�R�FDGDV- WUR��2EYLDPHQWH�TXH�GL]HPRV�LVWR�SRU- TXH�QmR�JRVWDUtDPRV�GH�WHU�XPD�WDEHOD� FRP�YiULRV�DWULEXWRV�QXORV�FDGDVWUDGRV�� $VVLP��RSWDPRV�SRU�PDQWHU�XPD�WDEHOD� VHSDUDGD�SDUD�RV�DXWRUHV� 'HVVD�IRUPD��ÀQDOL]DPRV�HVWH�HVWXGR� GH� FDVR� DSUHVHQWDQGR�XP�PRGHOR�GH� GDGRV�SDUD� HPSUpVWLPRV�H�GHYROXo}HV� GH�OLYURV��FRQIRUPH�Figura 9� Ordens de Serviço 7RGRV� QyV� Mi� IUHTHQWDPRV� DOJXPD� HPSUHVD�TXH�QHFHVVLWD�WUDWDU�RUGHQV�GH� VHUYLoR�� FRPR� RILFLQDV�PHFkQLFDV� RX� HPSUHVDV�GH�PDQXWHQomR�GH�PLFURFRP- SXWDGRUHV��SRU�H[HPSOR��(P�WRGRV�HVWHV� FDVRV� VmR�QHFHVViULRV�DOJXQV�SURGXWRV� SDUD�D�H[HFXomR�GRV�VHUYLoRV��3RU�H[HP- SOR��OHYDPRV�QRVVR�FRPSXWDGRU�SDUD�PD- QXWHQomR�H�R�WpFQLFR�ID]�R�OHYDQWDPHQWR� GR�SUREOHPD��GL]HQGR�TXH�R�SUREOHPD�p� QD�IRQWH�GH�DOLPHQWDomR��3DUD�H[HFXomR� GHVWH�VHUYLoR��D�HPSUHVD�FREUD�R�VHUYLoR� GH�VXEVWLWXLomR�GD�SHoD�FRP�SUREOHPD� H�WDPEpP�R�YDORU�GD�SHoD��$VVLP��XPD� RUGHP�GH�VHUYLoR�SRGH�SRVVXLU�SURGXWRV� H�VHUYLoRV��0DV��SDUD�HVWH�HVWXGR�GH�FDVR�� RSWDPRV�SRU� UHGX]LU� R� HVFRSR�GH�XP� VLVWHPD�GH�RUGHQV�GH� VHUYLoR�YLVDQGR� IDFLOLWDU� D� FRPSUHHQVmR�SRU� SDUWH� GR� OHLWRU�� (QWUHWDQWR�� QDGD� LPSHGH�TXH�R� OHLWRU�DFUHVFHQWH�RXWUDV�IXQFLRQDOLGDGHV� QR�PRGHOR�DTXL�DSUHVHQWDGR� $VVLP��XP�VLVWHPD�GH�RUGHQV�GH�VHUYL- oR��SDUD�HVWH�HVWXGR�GH�FDVR��FRQVLVWH�GDV� VHJXLQWHV�IXQFLRQDOLGDGHV� %�&DGDVWUR�GH�3URGXWRV�H�6HUYLoRV��8P� FDGDVWUR�GH�SURGXWRV�SUHFLVD�WHU�D�QRPH� GR�SURGXWR��XPD�GHVFULomR�GR�SURGXWR� H� R�SUHoR�GR�SURGXWR� H� R� FDGDVWUR�GH� VHUYLoRV�SUHFLVD�GH�QRPH�GR�VHUYLoR��GHV- FULomR�GR�VHUYLoR�H�SUHoR�GR�VHUYLoR� %�&DGDVWUR�GH�2UoDPHQWR��8P�FDGDVWUR� GH�RUoDPHQWR� FRQVLVWH�GH�&OLHQWH� VROL- FLWDQWH��GDWD�GR�RUoDPHQWR��SURGXWRV�H� VHUYLoRV� TXH� FRPS}HP�R� RUoDPHQWR�� YDORU�GR�RUoDPHQWR� %�&DGDVWUR�GH�2UGHP�GH�6HUYLoR��8P� FDGDVWUR�GH�RUGHP�GH�VHUYLoR�FRQVLVWH�GD� GDWD�GD�DSURYDomR�GR�RUoDPHQWR��SRLV�D� RUGHP�GH�VHUYLoR�p�JHUDGD�DSyV�D�DSUR- YDomR�GR�RUoDPHQWR��GDWD�GH�LQtFLR�GRV� SQL50.indb 33 07.01.08 18:41:04 34 SQL Magazine ��&TUVEPT�EF�$BTP�EF�1SPKFUPT�EF�#BODPT�EF�%BEPT VHUYLoRV�H�GDWD�SUHYLVWD�SDUD�FRQFOXVmR� %�&DGDVWUR�GH�&OLHQWHV��8P�FDGDVWUR�GH FOLHQWHV�p�FRPSRVWR�SRU�QRPH�GR�FOLHQWH�� HQGHUHoR�GR�FOLHQWH�� WHOHIRQH�GR�FOLHQWH�� H�PDLO�GR�FOLHQWH��&3)�GR�FOLHQWH��5*�GR� FOLHQWH��FDVR�VHMD�SHVVRD�ItVLFD�H�&13-��,QV- FULomR�(VWDGXDO�FDVR�VHMD�SHVVRD�MXUtGLFD� $VVLP��SDUD�HVWH� HVWXGR�GH� FDVR�� LQL- FLDOPHQWH�p�JHUDGR�XP�RUoDPHQWR�SDUD� R�FOLHQWH��TXH�SRGH�FRQWHU�SURGXWRV�H�RX� VHUYLoRV��$SyV�D�DSURYDomR�GHVWH�RUoD- PHQWR�p�TXH�VHUmR�UHJLVWUDGDV�DV�RUGHQV� GH�VHUYLoR��26�� 'HVWD�IRUPD��QHVWH�DUWLJR�não aborda- UHPRV�DOJXPDV�IXQFLRQDOLGDGHV�TXH�HVWH� PRGHOR�GH�GDGRV�SRGHULD�WHU��WDLV�FRPR� %�&DGDVWUR�GH�IRUQHFHGRUHV�GH�SURGXWRV� %�&DGDVWUR�GH�IDEULFDQWHV�GH�SURGXWRV� %�&RQWUROH�GH�(VWRTXH�GRV�SURGXWRV� %�&RQGLo}HV�GH�SDJDPHQWR� %�3DJDPHQWRV�H�5HFHELPHQWRV� %�&RQWUROHV�GH�&KHTXH� 0DV��DSHVDU�GHVWDV�UHVWULo}HV�GH�HVFR- SR��R�PRGHOR�GH�GDGRV�TXH� VHUi�DSUH- VHQWDGR�QR�ÀQDO�GHVWH� HVWXGR�GH� FDVR� FRP�FHUWH]D�SRGHUi�VHU�~WLO�QmR�Vy�SDUD� YLVXDOL]DUPRV� R� SURMHWR� GR� EDQFR�GH� Figura 10��3FMBDJPOBNFOUPT�&OWPMWFOEP�$MJFOUF �/PSNBMJ[BEP GDGRV��PDV�WDPEpP�SDUD�XWLOL]DomR�HP� SURMHWRV�IXWXURV�GR�OHLWRU� $VVLP��D�SDUWLU�GHVWD�SHTXHQD�GHÀQL- omR�GDV�UHJUDV�GH�QHJyFLR�HP�TXHVWmR�� LUHPRV� LQLFLDU�D�PRGHODJHP�GH�GDGRV�� 7DPEpP�QmR�IRUPDOL]DPRV�HVWD�GHÀQL- omR�GDV�UHJUDV�GH�QHJyFLR�SRU�IXJLU�GRV� REMHWLYRV�GHVWH�DUWLJR� Na Figura 10�DSUHVHQWDPRV�D�SULPHLUD� SDUWH�GR�PRGHOR�GH�GDGRV��TXH�FRPSUH- HQGH�R�FDGDVWUR�GH�FOLHQWHV��1HVWH�HVWXGR� GH�FDVR�LUHPRV�DSUHVHQWDU�GLUHWDPHQWH�D� WDEHOD�GH�FOLHQWH�FRP�RV�UHODFLRQDPHQ- WRV� QHFHVViULRV� SDUD� D� QRUPDOL]DomR�� XPD�YH]�TXH� HVWH� FDVR� Mi� IRL�GHÀQLGR� DQWHULRUPHQWH�GH� IRUPD� VHPHOKDQWH� H� DSUHVHQWDGR�QD�Figura 7� Na Figura 10��FRQIRUPH�H[SRVWR�DQWH- ULRUPHQWH��VmR�DSUHVHQWDGRV�RV�UHODFLR- QDPHQWRV�HQYROYHQGR�D�WDEHOD�FOLHQWHV�� QRUPDOL]DGR�VHJXQGR�D��)1�GH� IRUPD� VHPHOKDQWH� j�GD�Figura 7�� (QWUHWDQWR�� QHVWH�PRGHOR�DGLFLRQDPRV�RV�UHODFLRQD- PHQWRV�HQWUH�FOLHQWH�H�DV�WDEHODV�ItVLFD�� MXUtGLFD� H� WHOHIRQH��$� WDEHOD� ItVLFD� H� D� MXUtGLFD� IRUDP�DGLFLRQDGDV�SRUTXH�QD� GHÀQLomR�GDV� UHJUDV� H[LVWHP�DWULEXWRV� TXH�GHYHP�VHU�SUHHQFKLGRV�TXDQGR�VH� WUDWDU�GH�XPD�SHVVRD�ItVLFD��&3)�H�5*�� H�DWULEXWRV�TXH�GHYHP�VHU�SUHHQFKLGRV� TXDQGR� VH� WUDWDU� GH� SHVVRD� MXUtGLFD� �&13-�H�,QVFULomR�(VWDGXDO���$OpP�GLVVR�� D�WDEHOD�WHOHIRQH�IRL�DGLFLRQDGD�SDUD�SR- GHUPRV�FDGDVWUDU�YiULRV�WHOHIRQHV�SDUD� XP�PHVPR�FOLHQWH��2�DWULEXWR�7LSR7HO� GHVWD�WDEHOD�VHUi�UHVSRQViYHO�SRU�LQIRU- PDU�VH�R�UHIHULGR�WHOHIRQH�p�XP�WHOHIRQH�UHVLGHQFLDO��FRPHUFLDO�RX�FHOXODU� $SyV�ÀQDOL]DU�R�PRGHOR�SDUD�FDGDVWUR� GH�FOLHQWHV��SRGHPRV�DYDQoDU�QR�PRGHOR� GH�GDGRV��WUDWDQGR�DJRUD�R�FDGDVWUR�GH� SURGXWRV�H�VHUYLoRV��&RQIRUPH�D�GHÀQL- omR�GDV�UHJUDV�GH�QHJyFLR�GHVWH�HVWXGR� GH�FDVR��WDQWR�SURGXWRV�TXDQWR�VHUYLoRV� SRVVXHP�RV�PHVPRV�DWULEXWRV��TXH�VmR�� QRPH��GHVFULomR�H�SUHoR��3DUD�FULDUPRV� DV�WDEHODV��WHPRV�GXDV�RSo}HV��$�SULPHL- UD�GHODV� VHULD� FULDU� DSHQDV�XPD� WDEHOD� FRP�HVWHV�DWULEXWRV�H�SRGHUPRV�LQVHULU� XP�DWULEXWR�FRP�R�QRPH�GHÀQLomR��SRU� H[HPSOR�� SDUD� LQIRUPDU� VH� R� UHJLVWUR� GHSRLV� GH� FDGDVWUDGR� WUDWD�VH� GH� XP� SURGXWR�RX�GH�XP�VHUYLoR��UHFHEHQGR�DV� OHWUDV�3�H�6�UHVSHFWLYDPHQWH��SRU�H[HP- SOR��FRQIRUPH�Figura 11� 2XWUD�RSomR� VHULD� FULDUPRV�XP�UHOD- FLRQDPHQWR�GH�JHQHUDOL]DomR�HVSHFLD- OL]DomR��&RP�HVWH� UHODFLRQDPHQWR��QmR� SUHFLVDUtDPRV� WHU�R� DWULEXWR�GHÀQLomR�� FRQIRUPH�Figura 12� 3RGHPRV�SHUFHEHU�TXH�QD�Figura 12 DSUHVHQWDPRV�D� WDEHOD�3URGXWR6HUYLFR� FRP�RV�DWULEXWRV�FRPXQV�WDQWR�D�SURGXWR� TXDQWR�D�VHUYLoR��(QWUHWDQWR��QHVWH�FDVR�� VmR�WRGRV�RV�DWULEXWRV��$VVLP��D�WDEHOD� 3URGXWR�Vy�SRVVXL�XP�DWULEXWR�TXH�p�D� FKDYH�HVWUDQJHLUD�TXH�UHIHUHQFLD�D�WDEHOD� 3URGXWR6HUYLFR��RX�VHMD��TXDQGR�IRU�XP� SURGXWR�� WDPEpP� FDGDVWUDUHPRV� XP� UHJLVWUR�QD� WDEHOD�3URGXWR��'D�PHVPD� IRUPD�SDUD�D�WDEHOD�6HUYLFR� &RPR� QHVWH� QRVVR� HVWXGR� GH� FDVR� GHÀQLPRV�RV� DWULEXWRV� FRPR� VHQGR�RV� PHVPRV�SDUD�SURGXWRV�H�VHUYLoRV��HVFR- OKHPRV�R�PRGHOR�GH�GDGRV�GD�Figura 11 SDUD�GDUPRV�VHTrQFLD�QHVWH�H[HPSOR��2� PRGHOR�GH�GDGRV�GD�Figura 12 teria me- OKRU�XWLOL]DomR�FDVR�WLYpVVHPRV�DWULEXWRV� GLIHUHQWHV�GH�SURGXWRV�H�VHUYLoRV��FRPR�� TXDQWLGDGH� GH� SURGXWR� HP� HVWRTXH�� IRUQHFHGRUHV�GH�SURGXWRV��GHQWUH�RXWURV� DWULEXWRV�TXH�6HUYLoRV�QmR�SRVVXHP� $VVLP��GH�SRVVH�GD�7DEHOD�3URGXWR�6HU- vico (Figura 11���SUHFLVDUHPRV�YHULÀFDU� SQL50.indb 34 07.01.08 18:41:05 Edição 50 - SQL Magazine 35 MODEL AGEM Figura 11. Tabela ProdutoServico Figura 12��5BCFMB�1SPEVUP4FSWJDP�DPN�3FMBDJPOBNFOUP�(FOFSBMJ[BÎÍP�&TQFDJBMJ[BÎÍP VH� HOD� HVWD� QRUPDOL]DGD��2� SULPHLUR� SDVVR�p�YHULÀFDU�VH�HOD�HVWi�QD��)1��3HU- FHEHPRV�TXH� HVWD� WDEHOD� HVWi�QD� �)1�� SRLV�QmR�SRVVXL�DWULEXWRV�PXOWLYDORUDGRV� RX�FRPSRVWRV� 8PD�YH]�GHÀQLGD�D�WDEHOD�SDUD�DUPD- ]HQDU�RV�SURGXWRV�H�VHUYLoRV��SRGHPRV� SDUWLU�SDUD�D�FULDomR�GH�RUoDPHQWRV��1D� Figura 13�DSUHVHQWDPRV�XP�PRGHOR�GH� GDGRV�SDUD�RV�RUoDPHQWRV��3DUD�TXH�XP� RUoDPHQWR� VHMD� UHDOL]DGR�� p�QHFHVViULR� XP�FOLHQWH��D�GDWD�GR�RUoDPHQWR�H�R�YDORU� WRWDO�� (QWUHWDQWR��SDUD�TXH� VH� WHQKD�R� YDORU�WRWDO��p�QHFHVViULR�FRQKHFHU�WRGRV� RV� LWHQV�GR�RUoDPHQWR��TXH�SRGHP�VHU� SURGXWRV� H�RX� VHUYLoRV�� TXH� HVWDUmR� DUPD]HQDGRV�QD�WDEHOD�,WHP2UFDPHQWR�� $VVLP��QHVWD�WDEHOD��VHUi�DUPD]HQDGR�R� FyGLJR�GR�SURGXWRBVHUYLFR��FyGLJR��R�Fy- GLJR�GR�RUoDPHQWR�FRGRUF���D�TXDQWLGDGH� GH�SURGXWRV�RX�VHUYLoRV�GR�UHIHULGR�LWHP� H�R�YDORU�GHVWH�LWHP�� 2�SUy[LPR�SDVVR� p� YHULILFDU� VH� HVWH� PRGHOR�GH�GDGRV�GD�Figura 13�HVWi�QRU- PDOL]DGR�GH�DFRUGR�FRP�D��)1��3RGHPRV� SHUFHEHU� TXH� VLP�� XPD� YH]� TXH� QmR� H[LVWHP�DWULEXWRV�FRPSRVWRV�RX�PXOWLYD- ORUDGRV��7DPEpP�SRGHPRV�YHULÀFDU�TXH� R� UHIHULGR�PRGHOR� HQFRQWUD�VH�QD��)1� XPD�YH]�TXH�QmR�H[LVWHP� WDEHODV� FRP� FKDYHV�SULPiULDV�FRPSRVWDV��-i�QD��)1� YHULÀFDPRV�TXH�DV� WDEHODV�2UFDPHQWR� H�,WHP2UFDPHQWR�SRVVXHP�RV�DWULEXWRV� YDORU�TXH�QmR�GHSHQGHP�GH�VXDV�FKDYHV� SULPiULDV��,VWR�TXHU�GL]HU�TXH�HVWDV�GXDV� WDEHODV� QmR� HVWmR� QD� �)1�� (P�RXWUDV� SDODYUDV�� SRGHPRV� YHULILFDU� TXH� HVWH� DWULEXWR�YDORU�SRGH�VHU�FDOFXODGR�D�SDU- WLU�GH�RXWURV�DWULEXWRV��RX�VHMD��HP�,WHP� 2UFDPHQWR��R�DWULEXWR�YDORU�QDGD�PDLV� p�TXH�R�UHVXOWDGR�HQWUH��4GDGH 3UHFR��� (�R�DWULEXWR�YDORU�GD�WDEHOD�2UFDPHQWR� QDGD�PDLV�p�TXH�D�VRPDWyULR�GR�DWULEXWR� YDORU�SDUD�WRGRV�RV�LWHQV�TXH�FRPS}H�R� RUoDPHQWR�HP�TXHVWmR� Figura 13��3FMBDJPOBNFOUP�0SÎBNFOUP (QWUHWDQWR�� D� DXVrQFLD�GHVWHV�YDORUHV� TXH�SRGHP�VHU�FDOFXODGRV�SRGHP�FDX- VDU�DOJXQV�GDQRV�D�ORQJR�SUD]R�SDUD�DV� LQIRUPDo}HV�D�VHUHP�JHUDGDV��3RU�H[HP- SOR��VH�QmR�H[LVWLVVH�R�DWULEXWR�YDORU�QD� WDEHOD�,WHP2UFDPHQWR��TXDQGR�R�SUHoR� GR�SURGXWR�RX�VHUYLoR�IRU�DOWHUDGR�VHULD� LPSRVVtYHO�WHU�XPD�LQIRUPDomR�GR�SUHoR� GR�SURGXWR�FRQVLGHUDGR�QR�PRPHQWR�HP� TXH�IRL�IHLWR�XP�GHWHUPLQDGR�RUoDPHQWR�� 0DV�� DR�GHVQRUPDOL]DUPRV� VHJXQGR�D� �)1�HVWD�WDEHOD�,WHP2UFDPHQWR��FRQVH- JXLUtDPRV�WHU�DFHVVR�D�HVWD�LQIRUPDomR�� 3DUD�LVWR��EDVWD�GLYLGLU�R�DWULEXWR�YDORU� SHOR�DWULEXWR�TGDGH�GD�WDEHOD�,WHP2UFD- PHQWR��PHVPR�TXH�R�SUHoR�GR�SURGXWR� Mi�WHQKD�VLGR�DOWHUDGR� 2XWUR�SRQWR�TXH�GHYH�VHU�FRQVLGHUDGR� p�R�DWULEXWR�YDORU�GD�WDEHOD�2UoDPHQWR�� (VWH�DWULEXWR�WHP�D�~QLFD�ÀQDOLGDGH�GH� Mi�DUPD]HQDU�D�VRPDWyULD�GRV�SUHoRV�SRU� LWHQV�GH�RUoDPHQWR��YLVDQGR� IDFLOLWDU�D� JHUDomR�GRV�UHODWyULRV�IXWXURV��&RP�LVWR�� DR�JHUDU�R� UHIHULGR� UHODWyULR��QmR� VHULD� QHFHVViULR�HIHWXDU�FiOFXORV��Listagem 4��� &DVR�QmR�H[LVWLVVH�HVWH�DWULEXWR��SDUD�FDO- FXODU�R�YDORU�GR�RUoDPHQWR�VHULD�QHFHV- ViULD�XPD�FRQVXOWD�SDUD�HIHWXDU�FiOFXORV� SDUD�WRGD�VROLFLWDomR�GHVWD�LQIRUPDomR�� FRQIRUPH�FRQVXOWD�GD�Listagem 3� $VVLP��QHVWH�HVWXGR�GH�FDVR�RSWDPRV� SRU� QmR� QRUPDOL]DU� DV� WDEHODV� ,WHP Orcamento e Orcamento de acordo com D��)1�SHORV�PRWLYRV�FLWDGRV� 8PD�YH]�HIHWXDGR�R�PRGHOR�GH�GDGRV� SDUD�RV�RUoDPHQWRV�SRGHPRV� LQLFLDU� D� PRGHODJHP�SDUD�R�DUPD]HQDPHQWR�GDV� RUGHQV�GH�VHUYLoR��5HSDUH�TXH�QHVWH�FDVR� SDUD�WRGD�RUGHP�GH�VHUYLoR��GHYHPRV�WHU� XP�RUoDPHQWR�SUpYLR�H�VRPHQWH�DSyV�D� DSURYDomR�GR�RUoDPHQWR�p�TXH�VHUi�GDGR� LQtFLR�j�RUGHP�GH�VHUYLoR� 3DUD�HVWH�FDVR��WHUHPRV�DOJXPDV�RSo}HV� SDUD�PRGHODJHP�H�DV�DSUHVHQWDUHPRV�D� VHJXLU��$�SULPHLUD�GHODV�VHULD�UHODFLRQDU� D� WDEHOD�2UFDPHQWR� FRP� D� WDEHOD�26� FRQIRUPH�Figura 14� (QWUHWDQWR��SDUD�HVWH�PRGHOR�GHYHPRV� Listagem 3. $ÈMDVMP�EP�WBMPS�EP�PSÎBNFOUP SELECT SUM(valor) FROM ItemOrcamento WHERE ItemOrcamento.Orcamento_CodOrc = X --Tal que X é o número do orçamento que se quer calcular o valor Listagem 4. $POTVMUB�7BMPS�EP�PSÎBNFOUP SELECT valor FROM Orcamento WHERE Orcamento.CodOrc = X --Tal que X é o número do orçamento que se quer calcular o valor SQL50.indb 35 07.01.08 18:41:06 36 SQL Magazine ��&TUVEPT�EF�$BTP�EF�1SPKFUPT�EF�#BODPT�EF�%BEPT YHULÀFDU�R�VHJXLQWH�IDWR��WRGRV�RV�LWHQV�GR� RUoDPHQWR�IRUDP�DSURYDGRV�SDUD�D�H[H- FXomR�GD�26"�3DUD�R�PRGHOR�GD�Figura 14 D�UHVSRVWD�VHULD�DÀUPDWLYD��$�~QLFD�FRLVD� TXH�HVWDPRV�DUPD]HQDQGR�VHULD�D�GDWD� GH�DSURYDomR�GR�RUoDPHQWR�'DWD$SURY��� D�GDWD�SUHYLVWD�SDUD� FRQFOXVmR� �'DWD- &RQFOXVDR��� YDORU� DSURYDGR� �9DORU$- SURY���GHVFRQWR�RX�DFUpVFLPR�� ,VWR�p��R� YDORU� DSURYDGR�SRGH� VHU�GLIHUHQWH�GR� YDORU�GR�RUoDPHQWR�� WHQGR�DSHQDV�XP� GHVFRQWR�RX�XP�DFUpVFLPR��'D�PHVPD� IRUPD� FRPR�QD�Figura 13�� HVWD� WDEHOD� QmR�HVWDULD�QRUPDOL]DGD�VHJXQGR�D��)1� YLVDQGR�IDFLOLWDU�HVWHV�FiOFXORV��SRLV�FDVR� QmR�H[LVWLVVH�HVWHV�DWULEXWRV�GHVFRQWR�H� DFUpVFLPR��RV�PHVPRV�SRGHULDP� IDFLO- PHQWH� VHU� FDOFXODGRV��%DVWD�YHULÀFDU�R� YDORU�DSURYDGR�GD�WDEHOD�26�H�R�YDORU�GD� WDEHOD�2UFDPHQWR��&DVR�D�GLIHUHQoD�GH� Figura 15��3FMBDJPOBNFOUP�0SÎBNFOUP�Y�0SEFN�EF�4FSWJÎP� 04 �Y�*UFN04 Figura 14��3FMBDJPOBNFOUP�0SÎBNFOUP�Y�0SEFN�EF�4FSWJÎP� 04 YDORU�DSURYDGR�SRU�YDORU�IRVVH�SRVLWLYD�� KRXYH�XP�DFUpVFLPR��&DVR� FRQWUiULR�� XP�GHVFRQWR��0DV��SDUD�HVWH�HVWXGR�GH� FDVR�WDPEpP�YDPRV�RSWDU�SRU�PDQWHU�GD� IRUPD�DSUHVHQWDGD�QD�Figura 14� 7RGDYLD��JRVWDUtDPRV�GH�YHULÀFDU�TXDLV� LWHQV�GR�RUoDPHQWR�IRUDP�DSURYDGRV�SDUD� H[HFXomR��3RGHPRV� WHU� FODUDPHQWH�HVWH� FDVR�XPD�YH]�TXH�YRFr�SRGH�QmR�DSURYDU� WRGRV�RV�LWHQV�SRU�GLYHUVRV�PRWLYRV��$V- VLP��SRGHPRV�JHUDU�XPD�WDEHOD�,WHP2V� TXH� VHULD� UHVSRQViYHO� SRU� DUPD]HQDU� VRPHQWH�RV� LWHQV�TXH� IRUDP�DSURYDGRV�� FRQIRUPH�Figura 15��0DV��FDVR�RSWHPRV� SRU�HVWH�PRGHOR��SRGHPRV�SHUFHEHU�TXH� p�SRVVtYHO�JHUDU�XPD� LQFRQVLVWrQFLD�GH� GDGRV�PXLWR�JUDQGH�H�JUDYH��8P�XVXi- ULR�SRGH�FRPHWHU�XP�HUUR�LQFRQVFLHQWH�� SRU�H[HPSOR��GH�DUPD]HQDU�RV� LWHQV�GD�RUGHP�GH�VHUYLoR�GLIHUHQWHV�GRV�LWHQV�GR� RUoDPHQWR��,VWR�VHULD�XP�JUDYH�HUUR��XPD� YH]�TXH�D�RUGHP�GH�VHUYLoR�p�XP�UHVXO- WDGR�GH�XP�RUoDPHQWR�SDUD�HVWH�HVWXGR� GH�FDVR��$VVLP��WUDQVIHULUtDPRV�D�UHVSRQ- VDELOLGDGH�GH�PDQWHU�D�FRQVLVWrQFLD�GRV� GDGRV�SDUD�D�DSOLFDomR��RX�VHMD��R�VRIWZDUH� GHYH�SDVVDU�D�ID]HU�HVWDV�YDOLGDo}HV� 3DUD�R�PRGHOR�GH�GDGRV�GD�Figura 15�� WDPEpP�QmR�QRUPDOL]DPRV�GH�DFRUGR� FRP�D� �)1�SHORV�PHVPRV�PRWLYRV�GR� PRGHOR� DQWHULRU��5HSDUH� TXH� D� WDEHOD� ,WHP26�SRVVXL�RV�DWULEXWRV�9DORU$SURY�� 'HVFRQWR� H�$FUHVFLPR��(VWHV� DWULEXWRV� SRVVXHP�D�PHVPD�ÀQDOLGDGH�GRV�DWUL- EXWRV� VHPHOKDQWHV�GD� WDEHOD�26��&RP� D�H[LVWrQFLD�GHOHV��SRGHUHPRV�FRQWURODU� SDUD�FDGD�UHJLVWUR�GD�WDEHOD��VH�IRL�FRQFH- GLGR�XP�GHVFRQWR�RX�XP�DFUpVFLPR� 3DUD�UHVROYHU�HVWH�SUREOHPD�GD�SRVVL- ELOLGDGH�GD� LQFRQVLVWrQFLD�GRV�GDGRV�� SRGHPRV�DOWHUDU�R�PRGHOR�GH�GDGRV�GD� Figura 14�DGLFLRQDQGR�DOJXQV�DWULEXWRV� FRQIRUPH�Figura 16� 3RGHPRV�SHUFHEHU�TXH� IRUDP�DGLFLR- QDGRV�RV� VHJXLQWHV�DWULEXWRV�QD� WDEHOD� ,WHP2UFDPHQWR�� %�$SURYDGR��TXH�UHFHEH�XP�FDUDFWHUH� 6�RX�1�SDUD�LQIRUPDU�VH�R�LWHP�IRL�DSUR- YDGR�RX�QmR� %�'HVFRQWR��TXH�UHFHEH�R�YDORU�GR�GHV- FRQWR�LQGLYLGXDO��RX�VHMD��VH�R�LWHP�HP� TXHVWmR�WHYH�XP�GHVFRQWR�QR�SUHoR� %�$FUHVFLPR�� TXH� UHFHEH� R� YDORU� GR� DFUpVFLPR�LQGLYLGXDO��RX�VHMD��VH�R�LWHP�HP� TXHVWmR�WHYH�XP�DFUpVFLPR�QR�SUHoR� %�9DORU$SURY��TXH�UHFHEH�R�YDORU�DSUR- YDGR�SDUD�D�RUGHP�GH�VHUYLoR� 'HVVD�IRUPD��R�DWULEXWR�YDORU�GD�WDEHOD� ,WHP2UFDPHQWR�PDQWpP�R�YDORU�RULJLQDO� GR�RUoDPHQWR� IHLWR�H�R�YDORU�DSURYDGR� VHULD� R� YDORU� DSURYDGR�SRU� LWHP�SDUD� IDFLOLWDU�WDPEpP�RV�FiOFXORV�SDUD�D�WDEHOD� 26��2V�DWULEXWRV�9DORU$SURY��'HVFRQWR� H�$FUHVFLPR�GD�WDEHOD�26�FRQWLQXDULDP� FRP�D�PHVPD�IXQomR��RX�VHMD��DUPD]HQDU� R�YDORU�DSURYDGR�GD�RUGHP�GH�VHUYLoR�� RV�GHVFRQWRV�RX�DFUpVFLPR��6XSRQKD�TXH� QHQKXP�LWHP�WHYH�FRQFHGLGR�GHVFRQWR� RX�DFUpVFLPR�LQGLYLGXDOPHQWH��PDV�DR� IHFKDU� D�26� WHQKD� VLGR� FRQFHGLGR�XP� GHVFRQWR��1D� WDEHOD�26� WHUtDPRV� HVWH� YDORU�DUPD]HQDGR� 'HVWD�IRUPD��YHULÀFDPRV�TXH�D�WDEHOD� ,WHP2UFDPHQWR��2UFDPHQWR� H�26�QmR� SQL50.indb 36 07.01.08 18:41:07 Edição 50 - SQL Magazine 37 MODEL AGEM Figura 16. Ordem de Serviço HVWmR�QRUPDOL]DGDV�VHJXQGR�D��)1��0DV�� GH�DFRUGR�FRP�D��)1�H��)1�HODV�HVWmR� Na Figura 17�DSUHVHQWDPRV�R�PRGHOR� GH�GDGRV�SDUD�RUGHQV�GH�VHUYLoR�FRPSOH- WR��1HOH�FRQVWD�D�RSomR�GD�Figura 16 para D�DSURYDomR�GRV�LWHQV�GR�RUoDPHQWR� Considerações finais 3RGHPRV�SHUFHEHU� TXH� D� DGRomR�GD� QRUPDOL]DomR� GH� GDGRV� p� GH� JUDQGH� YDOLD�SDUD�TXH�R�SURMHWR�GH�XP�EDQFR�GH� GDGRV�QmR�SRVVXD� UHGXQGkQFLD� H� FRQ- VHTHQWHPHQWH� LQFRQVLVWrQFLD��3RUpP�� YDOH� UHVVDOWDU�TXH�D�QRUPDOL]DomR�QmR� SRGH�JHUDU�SHUGDV�QR�SRGHU�GH�JHUDomR� GH� LQIRUPDo}HV� D� SDUWLU� GRV� EDQFRV� GH�GDGRV��&DVR� LVWR�RFRUUD��HP�PXLWRV� FDVRV�SRGH�VHU� LQWHUHVVDQWH�R�SURFHVVR� GH�GHVQRUPDOL]DomR�SDUD�PHOKRUDU� R� GHVHPSHQKR�GDV� FRQVXOWDV�� FRQIRUPH� DERUGDPRV�QR�GHFRUUHU�GRV� H[HPSORV� DSUHVHQWDGRV�H�GR�DUWLJR�´ 1RUPDOL]DomR� GH�'DGRVµ��GHVWD�UHYLVWD�HP�VXD�HGLomR� GH� Q~PHUR� ���� (QWUHWDQWR�� HVWH� FXVWR� SRGH�VHU�PXLWR�DOWR��FRPSURPHWHQGR�D� JDUDQWLD�GH�FRQVLVWrQFLD�GRV�GDGRV� (P�UHVXPR��XP�ERP�SURMHWR�GH�EDQFR� GH�GDGRV�GHYH�RIHUHFHU�LQIRUPDo}HV�FRP� TXDOLGDGH�SDUD�WRPDGD�GH�GHFLV}HV�SRU� SDUWH�GRV�JHVWRUHV�GDV�RUJDQL]Do}HV��(� SDUD�TXH� LVWR�RFRUUD��GHYHPRV�DYDOLDU� FRP�EDVWDQWH�FULWpULR�D�QRUPDOL]DomR�GH� GDGRV�SDUD�JDUDQWLU�D�FRQVLVWrQFLD�GRV� GDGRV�RX�RV�SRQWRV�RQGH�QmR�LUi�RFRUUHU� QRUPDOL]DomR�H�SRU�TXDLV�PRWLYRV��$OpP� GLVVR��GHYHPRV�DYDOLDU�VH�D�QmR�QRUPDOL- ]DomR�LUi�JHUDU�LPSDFWRV�QHJDWLYRV�SDUD� D�TXDOLGDGH�GDV�LQIRUPDo}HV� 1HVWH� DUWLJR�DSUHVHQWDPRV�R�PRGHOR� GH�GDGRV�SDUD�XPD�ELEOLRWHFD�H�SDUD�XP� VLVWHPD�GH�RUGHQV�GH� VHUYLoR��$PERV�� FRP�YiULDV�UHVWULo}HV�GH�HVFRSR�FRQIRU- PH�DERUGDGRV�QR�GHFRUUHU�GHVWH�DUWLJR�� (QWUHWDQWR��YDOH�IULVDU�TXH�PHVPR�FRP� HVWDV�UHVWULo}HV�� LQ~PHUDV� LQIRUPDo}HV� SRGHP�VHU�JHUDGDV�DWUDYpV�GHVWH�PRGHOR�� GHQWUH� DV�TXDLV�SRGHPRV� FLWDU��SDUD�R� VLVWHPD�GH�ELEOLRWHFDV� %�Livros cadastrados; %�Alunos cadastrados; %�Professores cadastrados; %�Editoras cadastradas; %�Autores cadastrados; %�Assuntos cadastrados; %�Livros por assunto; SQL50.indb 37 07.01.08 18:41:10 38 SQL Magazine ��&TUVEPT�EF�$BTP�EF�1SPKFUPT�EF�#BODPT�EF�%BEPT %�Percentuais de empréstimo por bairro das pessoas; %�Livros mais emprestados; %�Pessoas que mais pegaram livros emprestados; %�Quantidade de livros emprestados por pessoa e por período; %�Livros que não foram devolvidos; %�Livros que foram devolvidos fora do prazo; %�Quantidade de exemplares de cada livro. 3DUD�R� VLVWHPD�GH�RUGHQV�GH� VHUYLoR� WDPEpP�SRGHP�VH�JHUDGR�XP�ERP�YR- OXPH�GH� LQIRUPDo}HV�SDUD�WRPDGDV�GH� GHFLVmR��WDLV�FRPR� %�5HODomR�GH�FOLHQWHV�FDGDVWUDGRV� %�5HODomR�GH�FOLHQWHV�SRU�EDLUUR� %�5HODomR�GH�SURGXWRV�FDGDVWUDGRV� %�5HODomR�GH�VHUYLoRV�FDGDVWUDGRV� %�5HODomR�GH�2UoDPHQWRV�(IHWXDGRV� %�5HODomR�GH�2UoDPHQWRV�$SURYDGRV� % � 5HODomR � GH � 2UoDPHQWRV � QmR� $SURYDGRV� %�5HODomR�GH�2UGHQV�GH�6HUYLoR� %�'HVFRQWRV�FRQFHGLGRV�SRU�2UGHP�GH� 6HUYLoR�H�SRU�FOLHQWH� %�$FUpVFLPRV�HIHWXDGRV�SRU�2UoDPHQWR� H�SRU�FOLHQWH� %�3URGXWRV�TXH�PDLV�IRUDP�XWLOL]DGRV� QDV�RUGHQV�GH�VHUYLoR� %�6HUYLoRV�PDLV�H[HFXWDGRV� %�&OLHQWH�TXH�PDLV�HIHWXRX�VHUYLoRV� %�3HUFHQWXDLV�GH�FOLHQWHV�SRU�EDLUUR� %�%DLUURV�TXH�JHUDP�PDLV�IDWXUDPHQWR� %�4XDQWLGDGH�GH�2UGHQV�GH� 6HUYLoR� ([HFXWDGDV� 8P� VLVWHPD� GH� LQIRUPDomR� JHUHQ- FLDO� REULJDWRULDPHQWH� SUHFLVD� WHU� XP� PRGHOR� GH� GDGRV� TXH� SRVVXD� GDGRV� DUPD]HQDGRV�GH� IRUPD�TXH� VHMD� SRV- VtYHO� JHUDU� LQIRUPDo}HV�� (QWUHWDQWR�� LQIHOL]PHQWH�DLQGD�H[LVWHP�QR�PHUFDGR� GLYHUVRV�VLVWHPDV�TXH�QmR�SRVVXHP�WDO� IRFR��0XLWRV�GHOHV�DSHQDV�VXEVWLWXHP�R� PHLR�GH�DUPD]HQDPHQWR�GH�SDSHO�SDUD� RV� FRPSXWDGRUHV� H� LQIRUPDo}HV� SDUD� WRPDGDV�GH�GHFLVmR�SUDWLFDPHQWH�QmR� H[LVWHP��(VWH�WLSR�GH�VLVWHPD�QmR�SRGH� VHU�WUDWDGR�H�GHYHPRV�IRFDU�FODUDPHQWH� HP�LQIRUPDo}HV�JHUHQFLDLV��PHVPR�TXH� R�VLVWHPD�LQLFLDOPHQWH�VHMD�DSHQDV�WUDQ- VDFLRQDO��RX�VHMD��DSHQDV�SDUD�DUPD]HQDU� GDGRV�UHODWLYRV�jV�LQIRUPDo}HV�URWLQHLUDV� GD� HPSUHVD��0HVPR� HP�XP� VLVWHPD� WUDQVDFLRQDO��R�JHVWRU�GD�iUHD�SUHFLVDUi� WRPDU�GHFLV}HV�HP�DOJXP�PRPHQWR�GD� DWLYLGDGH�SURÀVVLRQDO�� DATE, C.J. Introdução a Sistemas de Bancos de Dados, Editora Campus, 2004. HARRINGTON, J. L. Projetos de Bancos de Dados Relacionais, Editora Campus, 2002. MONTEIRO, Emiliano S. Projeto de Sistemas e Bancos de Dados, Brasport, 2004. MULLER, Robert J. Projeto de Banco de Dados, Editora Berkeley, 2002. ROCHA JÚNIOR, Ary dos Santos, Normalização de Dados, SQL Magazine número 47, DevMedia, outubro 2007. SILBERSCHATZ, A. KORTH, H. F., SUDARSHAN, S. Sistemas de Bancos de Dados, Makron Books 1999. Referências Figura 17. Ordem de Serviço Final SQL50.indb 38 07.01.08 18:41:15
Compartilhar