Buscar

Estudos de Caso de Projetos de Banco de Dados

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Você também pode ser Premium ajudando estudantes

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Você também pode ser Premium ajudando estudantes

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Você também pode ser Premium ajudando estudantes
Você viu 3, do total de 11 páginas

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Você também pode ser Premium ajudando estudantes

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Você também pode ser Premium ajudando estudantes

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Você também pode ser Premium ajudando estudantes
Você viu 6, do total de 11 páginas

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Você também pode ser Premium ajudando estudantes

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Você também pode ser Premium ajudando estudantes

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Você também pode ser Premium ajudando estudantes
Você viu 9, do total de 11 páginas

Faça como milhares de estudantes: teste grátis o Passei Direto

Esse e outros conteúdos desbloqueados

16 milhões de materiais de várias disciplinas

Impressão de materiais

Agora você pode testar o

Passei Direto grátis

Você também pode ser Premium ajudando estudantes

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�IUHTHQWDPRV�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-
VHTrQFLD��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� IUHTHQWDPRV� 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�VHTrQFLD�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-
VHTHQWHPHQWH� 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

Continue navegando