1、實驗報告課稈名稱 數據庫實驗名稱e-r建模與關系表轉換計科系8班姓名郭思琦教師 成績2015年4月8曰實驗目的：1. 理解和掌握e-r圖的基本概念。2. 培養根據實際應用領域數據對象描述，抽取數據對象特征、關聯關系等信息，設il數據 庫概念結構的能力。3. 選做：學習power designer或其它工具，進行數據模型轉換和關系表的自動創建，培養 軟件輔助設工具的使用能力。實驗內容：1. 根據數據需求描述抽象出e-r圖閱讀gsm移動通信m絡配置數據庫課程實驗背景資料-ll-v4.doc,根據gsm的基本 概念，分析其屮的數據需求，將其描述抽象成實體和聯系，并確定實體和聯系的屬性，特別 要注意標

2、明其主鍵和外鍵等約束關系，最終形成e-r閣。2. 將e-r圖輸入相關設計工具（erwin、power design、sql server自帶設計工具或者visio 等）形成概念模型。3. 使用工具將e-r圖轉換為數據庫物理結構。4. 使用將物理模型轉化為生成數據庫中的表和視圖的腳本，注意要選擇數據庫為sqlserver。5. 執行sql腳本，生成表和視圖。6. 成功后，查看生成的表和視圖的情況。實驗環境：1. 數據庫軟件：sql server 20122. 建模.丁具：powerdesigner 153. 實驗人員：郭思琦實驗步驟及結果分析：1. 閱讀gsm移動通信網絡配置數據庫課程實驗背景資

3、料-11-v4.doc，根據gsm的基本 概念，分析其中的數據需求，可抽象出實體及其屬性如下。a) msimei國際移動設備識別碼number(30)主鍵msisdn移動用戶號碼number(20)username用戶名稱characters(20)mscompany手機制作商characters(20)gsmmspsense移動臺接收機靈敏度integergsmmsheight移動臺天線高度float(20)gsmmspfout移動臺發射機功率float(20)mzone是否為mzone客戶characters(20)b) mscmscidid號number (10)主鍵mscname名字c

4、haracters(20)msccompanymsc-經度characters(20)msclongitudemsc-經度long floatmsclatitudemsc-緯度long floatmsc altitudemsc-海拔long floatc) bscbscidid號number (10)主鍵bscname名字characters(20)bsccompany生產廠家characters(20)longitude經度long floatlatitude緯度long floatd) btsbtsnamebts名字characters(20)主鍵longitude經度long float

5、latitude緯度long floataltitude海拔long floatbtscompany生產廠家characters(20)btspowerbts功率integere) cellcellld小區id號number (10)主鍵areaname所屬地區characters(20)laclac號number(20)longitude經度long floatlatitude緯度long floatdirection扇區方向角integerbcchbcch 號integerf）話務量data曰期data主屬性time時間time主屬性ntch業務信道數integertraff全速率話務ff

6、llong floatrate半速率話務比例long floatthtraff呼叫數long floatcallnum擁塞數integercongsnum半速率話務量integercallcongs擁塞率float(20)g）頻點信息freqfreq頻點integer主鍵h) antennaantennald天線idinteger主鍵antennahigh天線高度float(20)halfpangle天線半功率角float(20)maxattenuation天線最大衰減integergain天線最大增益integeranttilt天線下傾角float(20)pt天線發射功率integermsp

7、wr移動臺接受功率integeri）路測信息keynum路測標示number(lo)主鍵latitude接收電平long floatlongitude經度long floatrxlev緯度long float2.各實體間的聯系如下a）1個msc可以連接管理兒個至數十個bsc，而每個bsc可以連接至1個msc, msc” 管理” bsc，一對多，兩端均為完全參與。可引入cardinality limits。b）1個bsc可連接控制多達數十個甚至上百個bts,每個bts （以及其所對應的 cell/sector）可以連接至1-3個bsc。bsc “控制” bts,多對多，兩端均為完全參與?？梢隿

8、ardinality limits。c）bts “對應于” cell/sector，一對一，兩端均為完全參與。d）1個ms可以位于1到6個bts的覆蓋范圍內（當ms同時處于多于1個bts的覆蓋范圍 閃時，ms處于如閣2所示的切換區域內），但任一吋刻只能與其中的1個bts建立有 效的無線通信鏈路，此bts對應的小區/扇區覆蓋范圍成為ms的服務小區，可作為 ms的屬性servingcellid。具體情況如下：1）移動臺空閑狀態下，cell “覆蓋” ms，多對多，ms完全參與，cell部分參與。2）移動臺通話狀態下，cell “服務于” ms，一對多，ms完全參與，cell部分參與。e）根據小區/

9、扇區為全向還是定向小區，每個小區的bts都有一幅企向或定向天線。 天線”配置給”小區/扇區，一對一，天線部分參與，小區/扇區完全參與。0 地理位罝上相鄰、并且定義為鄰區的2個小區之間有鄰區關系，鄰區關系是一種非對稱關系，多對多，兩端均為完全參與，可引入角色表示法。g）路測數據“來自于”小區，多對一，小區部分參與，路測數據完全參與。h）每個小區都用分鐘級話務數據加以“描述”，話務數據與小區的關系為多對一，小 區和話務量都是完全參與。i）頻點“分配給”小區，多對多，頻點部分參與，小區完全參與，聯系“分配給”的 屬性包括上行頻率（2）下行頻率（3）分配的信道類型，如bcch、tch、dcch、 cc

10、ch 等 cardinality limits: bcch 有且只有一個。3.形成e-r圖如下f 125 2sblerror ho sos."xi4. 巾e-r圖形成概念模型如丁bsc5. itl e-r閣生成物理結構模型如下mscm>dd<ek>kusnan叫20>miocwy州23>mfrclxnuoom>cavtudebacd叫rycho)<pk»usconunw<(1<fk>bscnimechw(20)bsccomacy ch>f(2o)lcrgtmooubttutrtudedovnebtsbtany

11、ne狀tdlo*><udt lsbtudc,ctl：bis company 20； btspa*e* riegerc2oi o»»d”)c糾idantannahftwhrftnqiekiaxxknsnua&orgtinamr*ptm，pwr antennal dc«ildam®nnano仰n5e©c»megern?eoe<wr<p«>numcrc(lo) <fk*kovnumnum®fto(10)<0*0ciidnumcnc(lc)<?k>uuwm勿相l

12、oogiuidodoutmrjcltv>wb<col=k>xtsxx、f 置* -«ix.aic«idbumar-earcennaida/eanjr-lactortudalatitudeortctcrboohnumenodo)ch«»(2q)rc»9».charfx,r»w*ne6c(20)doumaduwer:©9®r<fk1><fk2>c«aiddaotirhentchtraff*1bwmonouo) <fk>sa:«r.tog

13、eroxz4c«<nurnex<lo) <pk仆t-4t«sa.c2l：kjr 史 it.分 e 途：.<u:ce»dre»y灼 uftxdcoq).:必,dcwm _| 0oubic <pt.fk2> cpk.fkub floot(20)tljre 金sjs-fm<freq6.此時生成sql腳本如下/* dbms name:microsoft sql server 2000*/* created on:2015/4/15 星期三 17:15:50*/ 木=if exists (select 1from dbo.

14、 sysreferences r join dbo. sysobjects o on (o. id = r. constid and o. type = i7)where r. f key id = object id (antenna)and o. name = fk antenna 配置給 _cell) alter tabic antennadrop constraint fk antenna 配置給 cell goif exists (select 1from cibo, sysrcfcrcnccs r join dbo. sysobjccts o on (o. id = r. cons

15、tid and o. type = f) where r. fkeyid = object id(bsc)and o. name = fk bsc 管理 msc)alter table bscdrop constraint fk bsc_'管理_msc goif exists (select 1from dbo. sysreferences r join dbo. sysobjects o on (o. id = r. constid and o. type = f) where r. fkeyid = object_id(bts)and o. name = fk_bts_對應于_ce

16、ll)alter table btsdrop constraint fk_bts_對應于_cell goif exists (select 1from dbo. sysrcfcrcnccs r join dbo. sysobjects o on (o. id = r. constid and o. type 二 )where r. fkeyid = objcct_id(cell)and o. name = fk_cell_ 對應于 2_bts)alter table celldrop constraint fk_cellj、j"應于 2_btsg() if exists (selec

17、t 1from dbo. sysreferences r join dbo. sysobjects o on (o. id = r. constid and o. type = i7) where r. fkeyid = object_id(cell)and o. name = fk_cell_ 配置給 2_antenna)alter table celldrop constraint fk cell 酉己置給 2 antenna goif exists (select 1from dbo.sysreferences r join dbo. sysobjects o on (o. id = r

18、. constid and o.type = f) where r. fkeyid = object id(分配給)and o. name = fk 分配給分配給* cell)alter table 分配給drop constraint fk_分配給_分配給_cellgo if exists (select 1from dbo. sysrcfcrcnccs r join dbo. sysobjects o on (o. id = r. constid and o. type 二 )where r. fkeyid = objcct_id(分配給)and o. name = fk_分配給_分配給

19、2_freq)alter table 分配給drop constraint ek 分配給分配給 2 ereqgo if exists (select 1from dbo. sysreferences r join dbo. sysobjects o on (o. id = r. const id and o. type = f) where r. f key id = object_id(控制)and o. name = fk 控制控制 bsc)alter tabic 拽制drop constraint fk 控制控制 bscgo if exists (select 1from cibo, s

20、ysrcfcrcnccs r join dbo. sysobjects o on (o. id = r. constid and o. type = f) where r. fkeyid = object id(控制)and o. name = * fk 控制控制 2 bts)alter table 控制drop constraint fk_控制_控制 2_btsgo if exists (select 1from dbo. sysreferences r join dbo. sysobjects o on (o. id = r. constid and o. type = f) where

21、r. fkeyid = object_id(,覆蓋)and o. name = fk_覆蓋_覆蓋 2_cell)alter table 覆盃drop constraint fk_複益_覆益 2_cellgo if exists (select 1from dbo. sysreferences r join dbo. sysobjects o on (o. id = r. constid and o. type 二 )where r. fkeyid = object_id(話務量)and o. name = fkj5務量_描述_cell)alter table 話務最drop constrain

22、t fk_i?i務景_描述_6£1±g() if exists (select 1from dbo. sysreferences r join dbo. sysobjects o on (o. id = r. constid and o. type = i7) where r. fkeyid = object_id(路測信息)and o. name = fk_路測信息_來自于_cell)alter tabic路測信息drop constraint ek路測信息來自于cellgo if exists (select 1from dbo. sysreferences r joi

23、n dbo. sysobjects o on (o. id = r. constid and o. type = f) where r. fkeyid = object_id(鄰區)and o. name 二 fk_鄰區_鄰區_cell)alter table 鄰區drop constraint hk_鄰區_鄰區1±from dbo. sysreferences r join dbo. sysobjects o on (o. id = r. constid and o. type = i7)where r. fkeyidalter table 鄰ixdrop constrainlgo

24、object_id(鄰區)and o. namet fk鄰區鄰區2 cellif exists (select1fromsysindexeswhereid= object_id(antenna)andname =配置給_fkandindid > 0andindid < 255)drop index antenna.配s給_fkgoif exists (select1fromsysobjectswhereid = object_id(antenna)andtype =，u，）drop tabic antennagoif exists (select1fromsysindexeswhe

25、reid= objcct_id(bsc)andname = 管理 fkandindid > 0andindid < 255)drop index bsc.管理 1:kgoif exists (select1fromsysobjectswhereid = object_id(bsc)andtype =，u，）drop tabic bscgoif exists (select1fromsysindexeswhereid= objcct_id(bts)andname = 對應于fkandindid > 0andindid < 255)drop index bts.對應于 fk

26、 if exists (select 1from sysobjcctswhere id = object id (bts) and type = u)drop table bts goif exists (select 1fromsysindexeswhereid=object_id( cel 1)andname二配置給2_fk,andindid> 0andindid< 255)drop index cell.配置給 2_fk goif exists (select 1from sysindexeswhereid=object_id(，cell)andname=對應于2_fkand

27、indid> 0andindid< 255)drop index cell.對應于 2_fk if exists (select 1from sysobjcctswhere id = object id (cell) and type = u)drop table cell goif exists (select 1from sysobjectswhere id = object_id(，frecf ) and type = u)drop table ercqfrom sysobjccts where id = object id(msc)and type = u) drop ta

28、ble mscgoif exists (select 1from sysindexeswhereid=object_id(分配給)andname=分配給2ek*andindid>0andindid<255)drop index 分?e給.分配給 2_fk goif exists (select 1from sysindexeswhereid=object_idc 分配給)andname =，分配給_fk，andindid >0andindid <255)drop index分配給.分配給fk if exists (select 1from sysobjcctswhere

29、 id = object (分配給)andtype:it)drop table分配給exists (select1fromsysindexeswhereid=objcct_id(控制)andname二控制2 randindid> 0andindid< 255)drop index 控制.控制 2 fkif exists (select 1from sysindexeswhere id = objeetjd (控制)andname = 控制 _fkandindid > 0andindid < 255）drop index 控制.控制_fkexists （select1fr

30、omsysobjectswhereid = object_icl（控制）andtype = u，）drop table 控制exists （select1fromsysindexeswhereid= object_id（，援益）andname = 覆蓋 2_fkandindid > 0andindid < 255）drop index 覆蓋.覆蓋 2_fkexists （select1fromsysobjectswhereid = object_id（覆蓋）andtype =，u，）drop tabic 覆益exists （select1fromsysindexeswhereid=

31、 objcct_id（話務量）andname = 描述 fkandindid > 0andindid < 255）drop index話務量.描述_kkexists （select1fromsysobjectswhereid 二 object_id（話務覺）andtype = , u,）gogogogogodrop tabic 話務ttgoifg()fromsysindexeswhereid二 object_id(路測信息)andname=來自于_fkandindid> 0andindid< 255)drop index路測信息.來自于_fkexists (select

32、 1from sysobjcctswhere id = object id (路測信息)andtype:indrop table路測信息exists (select1fromsysindexeswhereid=object_id(鄰區)andname=鄰區2 randindid> 0andindid< 255)drop index 鄰區.鄰區 2_fkexists (select1fromsysindexeswhereid=object_idf 鄰區)andname二鄰區fkandindid> 0andindid< 255)drop index鄰區.鄰區fkexists

33、 (select1ifg()ifgoiffrom sysobjccts where id = object_id(鄰區)and type = u)drop table 鄰區/* table: antennacreate table antenna (antennallighfloat(20)null,halfpanglefloat(20)null,maxattenuat ionintnull,gainintnull,anttiltfloat(20)null,ptintnull,mspwrintnull,antennaidintnot null,cellll)numeric 10)null,co

34、nstraint pk_antena primary key nonclustered (antennaid)go/*=/* index:配置給_fk/*=create index 配置給_fk on antenna (cellid asc)go/* table: bsccreate tabic bsc (bscidnumeric 10not null,mscidnumeric:10not null,bscnamechar(20null,bsccompanychar(20null,longitudedouble precisionnull,latitudedouble precisionnul

35、l,constraint pk_bsc primary key nonclustered (bscid)go/*=/* index:管理 fkf«f.create index 管理_fk on bsc mscii) asc)/*=/* table: bts create table bts (btsxamechar (20not null，ccllidnumeric(10)not null,longitudedouble precisionnull,latitudedouble precisionnull,a1 ti tildedouble precisionnull,btscomp

36、anychar(20null,btspowcrintnull,constraint pk bts primary key nonclustered (btsxame)/*=/* index:對應于_fkfmmmimtmimmiwmiwcreate index 對應于_fk on bts (cellid asc)go65:=/* table: cell/*=create table cell (cellldnumeric 10not null,btsnamechar(20not null,antenna 11)intnot null，arcanamcchar(20null,lacnumeric(

37、20)null,longitudedouble precisionnull,latitudedouble precisionnull,directionintnull,bcchintnull,constraint pk_cell primary key nonclustered (cellw)go/*=/* index:對應于 2_fk/*=create index 對應于 2 l;k on cell ( btsxamc asc )go/*=/* index:配置給 2_fk/*=create index 配置給 2_fk on cell (antennatd asc)go/* table:

38、freq/*= create tabic frcq (freqintnot null,constraint pk_freq primary key nonclustered (freq)/*=/* table: msc/create table msc (mscidnumeric(10)not null，mscnamechar (20null,msccompanychar(20null,msclongitudedouble precisionnull,msckatitudcdouble precisionnull,mscaltitudcdouble precisionnull,constrai

39、nt pk msc primary key nonclustered (mscid)/*=/* tabic:分配給/*=create table 分配給(cel 1 idnumeric(10)not null,kreqintnot null，constraint pk_分配給 primary key (cel111), freq)go/*=*/*/* index:分配給_fk/#=*/create index分配給fk on分配給(cellid asc)go/*=二二二二二二二二二二二=二=二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二=二=二二二二二二二二二二木/* in

40、dex:分配給 2_fk*/*=*/create index分配給2_fk on分配給(freq asc)go/*=*/* table:控制*/*=二二二二二二二二二二二=二=二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二二=二=二二二二二二二二二二木/create tabic 投制(bscidnumeric(10)not null,btsnamechar(20)not null,constraintprimary key (bscid, btsname)go/* index:控制_fk/*=create index 控制_fk on 控制( bscid asc)g()/*=

41、*/* index:控制 2_fk*/*=*/create index 控制 2_fk on 控制(btsname asc) go/*=/* tabic:覆蓋/=create table 覆蓋(tmetnumeric(30)not null,cel 111)numeric (10)not null,constraint pk 箱益 primary key (i me i, cell id)/* index:覆盡 2_fk/*=create index 覆證 2_fk on 覆蓋(cellid asc)gof/* table:話務景 create tabic 話務量(cellldnumeric

42、10not null,datadatetimenot null,timedatetimenot null,ntciiintnull,traffdouble precisionnull,ratedouble precisionnull,thtraffdouble precisionnull,callnumintnull,congsnumintnull,callcongsfloat(20)null)go/* index:描述jfk/*=create index 描述 fk on 話務單：(cellid asc)go/*/* table:路測信息/*=create table路測信息(keyxumn

43、umeric 10not null,cellidnumeric 10not null,latitudedouble precisionnull,longitudedouble precisionnull,rxlevdouble precisionnull,constraint 卩1<_路測佶息 primary key nonclustered (keynuni)go/*=/* index:來自于_fk/1create index來白j* fk on路測信息(cellid asc)go/*=/* tabic:鄰區 /*=create table 鄰區(ccllidnumeric(10)no

44、t null,cel cel 1 idnumeric(10)not null，constraint i)k 鄰區 primary key (ccllid, ccl_ccllid)go/*=/* index:鄰區_fk/*=create index 鄰區_fk on 鄰區(cellid asc)go:*/:=*/*=/* index:鄰區 2 fkcreate index 鄰區 2 l?k on 鄰區( ccl_ccllid asc) go alter table antennaadd constraintforeign key (cellld)references cel 1 (cel 1td

45、)go alter table bscadd constraint fk_bscjt?理isc foreign key (mscid) references msc (msctd)go alter table btsadd constraint fk_bts_對應于_cell foreign key (cellld) references cel 1 (cel 1td)go alter table celladd constraint fk_cell_對應于 2_bts foreign key (btsname) refcrcnces bts (btsxame)go alter table celladd constraint fk_cell_?ew.給 2_antenna foreign key (antennald) references antenna (antennald)go alter table 分配給add constraint fk_分配給_分配給_cell foreign key (cellld) references cel 1 (cell id)go alter table 分配給add constraint 1_分配給_分配給 2_freq foreign key (freq) references freq (freq)go alter tabic