Проектирование реляционной базы данных автоматизации учета методом нормализации

  • 56 страниц
  • 7 источников
  • Добавлена 09.02.2022
800 руб.
  • Содержание
  • Часть работы
  • Список литературы
Оглавление
Введение 3
1. Общая характеристика и анализ объекта исследования 5
2. Инфологическое моделирование системы 9
3. Датологическое моделирование системы 15
3.1. Разработка таблиц базы данных 15
3.2. Взаимосвязи таблиц базы данных 19
4. Разработка программного обеспечения для ЭВМ 21
4.1. Разработка запросов 21
4.2. Разработка форм 22
4.3. Настройка запуска системы 28
4.4. Защита данных 29
Заключение 30
Список использованных источников 31
Анотация 32

Фрагмент для ознакомления

idbalanceacc,BFB_AccAPI.GetMnemoCode(r.idbalanceacc) idbalanceaccHL,r.idbalancetax,BFB_AccAPI.GetMnemoCode(r.idbalancetax) idbalancetaxHL,r.idbalancemng,BFB_AccAPI.GetMnemoCode(r.idbalancemng) idbalancemngHL,r.idspendacc,BFB_AccAPI.GetMnemoCode(r.idspendacc) idspendaccHL,r.idspendtax,BFB_AccAPI.GetMnemoCode(r.idspendtax) idspendtaxHL,r.idspendmng,BFB_AccAPI.GetMnemoCode(r.idspendmng) idspendmngHL,r.idamortacc,BFB_AccAPI.GetMnemoCode(r.idamortacc) idamortaccHL,r.idamorttax,BFB_AccAPI.GetMnemoCode(r.idamorttax) idamorttaxHL,r.idamortmng,BFB_AccAPI.GetMnemoCode(r.idamortmng) idamortmngHL , trim(replace(replace(to_char(mr2.fSumBalanceAccEnd - mr2.fSumAmortAccEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumRemainAccEnd , trim(replace(replace(to_char(mr2.fSumBalanceTaxEnd - mr2.fSumAmortTaxEnd - mr2.fSumAmortBonusEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumRemainTaxEnd , trim(replace(replace(to_char(mr2.fSumBalanceAccEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumBalanceAccEnd , trim(replace(replace(to_char(mr2.fSumBalanceTaxEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumBalanceTaxEnd , trim(replace(replace(to_char(mr2.fSumAmortAccEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumAmortAccEnd , trim(replace(replace(to_char(mr2.fSumAmortTaxEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumAmortTaxEnd,au.sDescription,i.sFactoryNum,i.sModel,(select ii.sDescription from asf_invnumb ii where ii.id = i.id) as sPosition,max(tp.sCode) as sCode-- ,EQP_EquipmentAPI.GetIDTypeEquipment_MC(ee.IDTypeEquipment) as IDTypeEquipment_MC , case when ASF.bZalog1(t.idInvNumb, null, au.ddate,1) = 1 then 'V' else '' end as sZalog , case when nvl(i.bIsRealProperty,0) = 1 then 'V' else '' end as bIsRealProperty , case when nvl(r.idstate, 0) = asf_stateapi.findByMnemocode('INPRESERVE') then 'V' else '' end as bConservfromASF_ActUnionObjectDet t,asf_actunionobject au,ASF_InvNumbi,ASF_InvCardClassItem c,Asf_Classifiercf,Asf_Classitem ci,eqp_equipmentee,eqp_techplacetp, (select *fromAsf_Registry rwherer.nperiod = to_number(to_char(:super$dDate, 'yyyymm'))) r, (selectr.asf_invnumb as invnumb, sum(nvl(case when b.sCost in ('BALANCEACC','OUTBALANCEACC') and r.idaccfrom != nvl(s.idretireacc,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumBalanceAccEnd,sum(nvl(case when b.sCost in ('BALANCETAX','OUTBALANCETAX') and r.idaccfrom != nvl(s.idretiretax,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumBalanceTaxEnd,sum(nvl(case when b.sCost = 'AMORTACC' and r.idaccfrom != nvl(s.idretireacc,-1) then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAmortAccEnd,sum(nvl(case when b.sCost = 'AMORTTAX' and r.idaccfrom != nvl(s.idretiretax,-1) then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAmortTaxEnd,sum(nvl(case when b.sCost = 'AMORTBONUS'andr.idaccfrom != nvl(s.idretiretax,-1) then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAmortBonusEnd,sum(nvl(case when s.idAdditionalCapitalAcc = r.idAccFrom then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAddCapitalAccEnd,sum(nvl(case when s.idAdditionalCapitalTax = r.idAccFrom then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAddCapitalTaxEnd,sum(nvl(case when b.sCost = 'OUTBALANCEACC' and r.idaccfrom != nvl(s.idretireacc,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumOutBalanceAccEnd,sum(nvl(case when b.sCost = 'OUTBALANCETAX' and r.idaccfrom != nvl(s.idretiretax,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumOutBalanceTaxEndfromASF_ActUnionObjectDet a,ACT_EntTransRepFull r, ASF_CostAcc b,BFB_AccAccMap c,ASF_SettingMap s,ASF_DepOwnerChangeSettingMap dwherea.idActUnionObject = :super$idandr.asf_invnumb = a.idinvnumbandto_number(to_char(r.dDate, 'yyyymm')) <= to_number(to_char(:super$dDate, 'yyyymm'))andd.IDDepOwnerInvNumb = :super$idDepOwnerandc.idAccSlave = r.idAccFromandc.idAccOwner = b.idAccandd.IDDepOwnerEntTrans = r.iddepownerandd.IDAdjustMethod = r.idadjustmethodands.iddepowner = d.IDDepOwnerInvNumbgroup by r.ASF_INVNUMB ) mr2 wheret.idActUnionObject = :super$idandt.idInvNumb = i.id(+)andt.idInvNumb = c.idinvcard(+)andc.idclassitem = ci.id(+)andc.idclassifiers = cf.id(+)andt.idInvNumb = r.idInvNumb(+)andt.idInvNumb=mr2.invnumb(+) -- tvorogov - (+) надообязательноand t.idActUnionObject=au.idandt.idInvNumb=ee.idInvNumb(+)andee.IDTechPlace=tp.id(+)group by t.ID,t.idActUnionObject,t.idInvNumb,i.sNumber,i.sCaption,t.idClass,r.nusefullifeacc,r.nusefullifetax,r.nusefullifemng,r.iddepartment,r.idresponsibleperson,r.idbalanceacc,r.idbalancetax,r.idbalancemng,r.idspendacc,r.idspendtax,r.idspendmng,r.idamortacc,r.idamorttax,r.idamortmng,t.nOrder, mr2.fSumBalanceAccEnd, mr2.fSumAmortAccEnd, mr2.fSumBalanceTaxEnd, mr2.fSumAmortTaxEnd, mr2.fSumAmortBonusEnd,au.sDescription,au.DDATE,i.sFactoryNum,i.sModel,i.id,i.nUsefulLifeRef,i.bIsRealProperty,r.idstateorder by t.nOrder --i.sNumber#][ #&Second=select t.ID,t.idActUnionObject,t.idInvNumb,i.sNumber as idInvNumb_MC,i.sCaption as idInvNumb_HL,t.idClass,t.nOrder,max(casewhen upper(cf.smnemocode) = 'OKOF' thenci.sClassCodeend) as sOkofMC,max(casewhen upper(cf.smnemocode) = 'OKOF' thenci.sCaptionend) as sOkof,max(casewhen upper(cf.smnemocode) = 'NGROUP' thenci.sClassCodeend) as sAmortMC,max(casewhen upper(cf.smnemocode) = 'NGROUP' thenci.sCaptionend) as sAmort,max(casewhen upper(cf.smnemocode) = 'ENAO' thenci.sClassCodeend) as sEnaoMC,max(casewhen upper(cf.smnemocode) = 'ENAO' thenci.sCaptionend) as sEnao,nvl(r.nusefullifeacc,i.nUsefulLifeRef)nusefullifeacc,nvl(r.nusefullifetax,i.nUsefulLifeRef)nusefullifetax,nvl(r.nusefullifemng,i.nUsefulLifeRef)nusefullifemng,r.iddepartment as idDep,bs_departmentapi.GetMnemoCode(r.iddepartment) as idDepMC,bs_departmentapi.GetHeadLine(r.iddepartment) as idDepHL,r.idresponsibleperson as idRespPerson,bs_responsiblepersonapi.GetMnemoCode(r.idresponsibleperson) as idRespPersonMC,bs_responsiblepersonapi.GetHeadLine(r.idresponsibleperson) as idRespPersonHL,r.idbalanceacc,BFB_AccAPI.GetMnemoCode(r.idbalanceacc) idbalanceaccHL,r.idbalancetax,BFB_AccAPI.GetMnemoCode(r.idbalancetax) idbalancetaxHL,r.idbalancemng,BFB_AccAPI.GetMnemoCode(r.idbalancemng) idbalancemngHL,r.idspendacc,BFB_AccAPI.GetMnemoCode(r.idspendacc) idspendaccHL,r.idspendtax,BFB_AccAPI.GetMnemoCode(r.idspendtax) idspendtaxHL,r.idspendmng,BFB_AccAPI.GetMnemoCode(r.idspendmng) idspendmngHL,r.idamortacc,BFB_AccAPI.GetMnemoCode(r.idamortacc) idamortaccHL,r.idamorttax,BFB_AccAPI.GetMnemoCode(r.idamorttax) idamorttaxHL,r.idamortmng,BFB_AccAPI.GetMnemoCode(r.idamortmng) idamortmngHL , trim(replace(replace(to_char(mr2.fSumBalanceAccEnd - mr2.fSumAmortAccEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumRemainAccEnd , trim(replace(replace(to_char(mr2.fSumBalanceTaxEnd - mr2.fSumAmortTaxEnd - mr2.fSumAmortBonusEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumRemainTaxEnd , trim(replace(replace(to_char(mr2.fSumBalanceAccEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumBalanceAccEnd , trim(replace(replace(to_char(mr2.fSumBalanceTaxEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumBalanceTaxEnd , trim(replace(replace(to_char(mr2.fSumAmortAccEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumAmortAccEnd , trim(replace(replace(to_char(mr2.fSumAmortTaxEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumAmortTaxEnd,au.sDescription,i.sFactoryNum,i.sModel,(select ii.sDescription from asf_invnumb ii where ii.id = i.id) as sPosition,max(tp.sCode) as sCode-- ,EQP_EquipmentAPI.GetIDTypeEquipment_MC(ee.IDTypeEquipment) as IDTypeEquipment_MC , case when ASF.bZalog1(t.idInvNumb, null, au.ddate,1) = 1 then 'V' else '' end as sZalog , case when nvl(i.bIsRealProperty,0) = 1 then 'V' else '' end as bIsRealProperty , case when nvl(r.idstate, 0) = asf_stateapi.findByMnemocode('INPRESERVE') then 'V' else '' end as bConservfromASF_ActUnionObjectDet t,asf_actunionobject au,ASF_InvNumbi,ASF_InvCardClassItem c,Asf_Classifiercf,Asf_Classitem ci,eqp_equipmentee,eqp_techplacetp, (select *fromAsf_Registry rwherer.nperiod = to_number(to_char(:super$dDate, 'yyyymm'))) r, (selectr.asf_invnumb as invnumb, sum(nvl(case when b.sCost in ('BALANCEACC','OUTBALANCEACC') and r.idaccfrom != nvl(s.idretireacc,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumBalanceAccEnd,sum(nvl(case when b.sCost in ('BALANCETAX','OUTBALANCETAX') and r.idaccfrom != nvl(s.idretiretax,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumBalanceTaxEnd,sum(nvl(case when b.sCost = 'AMORTACC' and r.idaccfrom != nvl(s.idretireacc,-1) then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAmortAccEnd,sum(nvl(case when b.sCost = 'AMORTTAX' and r.idaccfrom != nvl(s.idretiretax,-1) then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAmortTaxEnd,sum(nvl(case when b.sCost = 'AMORTBONUS'andr.idaccfrom != nvl(s.idretiretax,-1) then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAmortBonusEnd,sum(nvl(case when s.idAdditionalCapitalAcc = r.idAccFrom then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAddCapitalAccEnd,sum(nvl(case when s.idAdditionalCapitalTax = r.idAccFrom then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAddCapitalTaxEnd,sum(nvl(case when b.sCost = 'OUTBALANCEACC' and r.idaccfrom != nvl(s.idretireacc,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumOutBalanceAccEnd,sum(nvl(case when b.sCost = 'OUTBALANCETAX' and r.idaccfrom != nvl(s.idretiretax,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumOutBalanceTaxEndfromASF_ActUnionObjectDet a,ACT_EntTransRepFull r, ASF_CostAcc b,BFB_AccAccMap c,ASF_SettingMap s,ASF_DepOwnerChangeSettingMap dwherea.idActUnionObject = :super$idandr.asf_invnumb = a.idinvnumbandto_number(to_char(r.dDate, 'yyyymm')) <= to_number(to_char(:super$dDate, 'yyyymm'))andd.IDDepOwnerInvNumb = :super$idDepOwnerandc.idAccSlave = r.idAccFromandc.idAccOwner = b.idAccandd.IDDepOwnerEntTrans = r.iddepownerandd.IDAdjustMethod = r.idadjustmethodands.iddepowner = d.IDDepOwnerInvNumbgroup by r.ASF_INVNUMB ) mr2 wheret.idActUnionObject = :super$idandt.idInvNumb = i.id(+)andt.idInvNumb = c.idinvcard(+)andc.idclassitem = ci.id(+)andc.idclassifiers = cf.id(+)andt.idInvNumb = r.idInvNumb(+)andt.idInvNumb=mr2.invnumb(+) -- tvorogov - (+) надообязательноand t.idActUnionObject=au.idandt.idInvNumb=ee.idInvNumb(+)andee.IDTechPlace=tp.id(+)group by t.ID,t.idActUnionObject,t.idInvNumb,i.sNumber,i.sCaption,t.idClass,r.nusefullifeacc,r.nusefullifetax,r.nusefullifemng,r.iddepartment,r.idresponsibleperson,r.idbalanceacc,r.idbalancetax,r.idbalancemng,r.idspendacc,r.idspendtax,r.idspendmng,r.idamortacc,r.idamorttax,r.idamortmng,t.nOrder, mr2.fSumBalanceAccEnd, mr2.fSumAmortAccEnd, mr2.fSumBalanceTaxEnd, mr2.fSumAmortTaxEnd, mr2.fSumAmortBonusEnd,au.sDescription,au.DDATE,i.sFactoryNum,i.sModel,i.id,i.nUsefulLifeRef,i.bIsRealProperty,r.idstateorder by t.nOrder --i.sNumber#]

Список использованных источников
1. Дунаев В. В. Базы данных. Язык SQL для студента. СПб: БХВ-Петербург, 2006, 288 с.
2. Гетц К., Литвин П., Гилберт М. Access 2000: Руководство разработчика. Киев: «Ирина», BHV, 2000, 912 с.
3. Карпова Т.С. Базы данных: модели, разработка, реализация. – СПб.: Питер, 2002. – 304 с.
4. Смирнова Г.Н. и др. Проектирование экономических информационных систем: Учебник / Под ред. Ю.Ф. Тельнова. — М.: Финансы и статистика, 2002 — 512 с.
5. Пенова И.П., MSAccess для начинающих – Москва, Вильямс, 2008 г., 213 с.
6. URL:http://www.sql.ru
7. URL:http://support.microsoft.com

Узнать стоимость работы