北邮数据库实验二实验报告

1、实验报告课程名称_数据库_实验名称 _E-R建模与关系表转换_计科_系_8_班姓名_郭思琦_教师_ 成绩_2015年4月8日实验目的：1 理解和掌握E-R图的基本概念。 2 培养根据实际应用领域数据对象描述，抽取数据对象特征、关联关系等信息，设计数据库概念结构的能力。 3 选做：学习Power Designer或其它工具，进行数据模型转换和关系表的自动创建，培养软件辅助设计工具的使用能力。 实验内容：1 根据数据需求描述抽象出E-R图阅读GSM移动通信网络配置数据库课程实验背景资料-11-v4.doc，根据GSM的基本概念，分析其中的数据需求，将其描述抽象成实体和联系，并确定实体和联系的属性，

2、特别要注意标明其主键和外键等约束关系，最终形成E-R图。2 将E-R图输入相关设计工具（ERWin、Power Design、SQL Server自带设计工具或者Visio等）形成概念模型。3 使用工具将E-R图转换为数据库物理结构。4 使用将物理模型转化为生成数据库中的表和视图的脚本，注意要选择数据库为SQL Server。 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名字Char

4、acters(20)MscCompanyMSC-经度Characters(20)MscLongitudeMSC-经度Long floatMscLatitudeMSC-纬度Long floatMscAltitudeMSC-海拔Long floatc)BSCBscIDid号Number(10)主键BscName名字Characters(20)BscCompany生产厂家Characters(20)Longitude经度Long floatLatitude纬度Long floatd)BTSBtsNameBTS名字Characters(20)主键Longitude经度Long floatLatitud

5、e纬度Long floatAltitude海拔Long float BtsCompany生产厂家Characters(20)BtsPowerBTS功率Integere)CellCellID小区id号Number(10)主键AreaName所属地区Characters(20)LACLAC号Number(20)Longitude经度Long floatLatitude纬度Long floatDirection扇区方向角IntegerBcchBcch号Integerf)话务量Data日期Data主属性Time时间Time主属性nTCH业务信道数IntegerTraff全速率话务量Long float

6、Rate半速率话务比例Long floatThtraff呼叫数Long floatCallnum拥塞数IntegerCongsnum半速率话务量IntegerCallcongs拥塞率Float(20)g)频点信息FreqFreq频点Integer主键h)AntennaAntennaID天线IDInteger主键AntennaHigh天线高度Float(20)HalfPAngle天线半功率角Float(20)MaxAttenuation天线最大衰减IntegerGain天线最大增益IntegerAntTilt天线下倾角Float(20)Pt天线发射功率IntegerMsPwr移动台接受功率Int

7、egeri)路测信息KeyNum路测标示Number(10)主键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，多对多，两端均为完全参与。可引入cardinality limi

8、ts。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都有一幅全向或定向天线。天线”配置给”小区/扇区，一对一，天线部分参与，小区/扇区完全参与。f)地理位置上相邻、并且定义为邻区的2个小区之间有邻区关系，邻区关系是一种非对称关系，多对多，两端均为完全参与，可引入角色表示法。g)路测数据“来自于”小区，多对一，小区部分参与，路测数据完全参与。h)每个小区都用分钟级话务数据加以“描述”，话务数据与小区的关系为多对一，小区和话务量都是完全参与。i)频点“分配给”小区，多对多，频点部分参与，小区完全参与，联系“分配给”的属性包括(1)上行频率(2)下行频率 (3)分配的信道类型，如BCCH、TCH、DCCH、CCCH等 (4) cardi

10、nality limits：BCCH有且只有一个。3. 形成E-R图如下4. 由E-R图形成概念模型如下5. 由E-R图生成物理结构模型如下6. 此时生成SQL脚本如下/*=*/* DBMS name: Microsoft SQL Server 2000 */* Created on: 2015/4/15 星期三 17:15:50 */*=*/if exists (select 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyi

11、d = object_id('Antenna') and = 'FK_ANTENNA_配置给_CELL')alter table Antenna drop constraint FK_ANTENNA_配置给_CELLgoif exists (select 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('BSC') and

12、= 'FK_BSC_管理_MSC')alter table BSC drop constraint FK_BSC_管理_MSCgoif exists (select 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('BTS') and = 'FK_BTS_对应于_CELL')alter table BTS drop constraint

13、FK_BTS_对应于_CELLgoif exists (select 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('Cell') and = 'FK_CELL_对应于2_BTS')alter table Cell drop constraint FK_CELL_对应于2_BTSgoif exists (select 1 from dbo.sysref

14、erences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('Cell') and = 'FK_CELL_配置给2_ANTENNA')alter table Cell drop constraint FK_CELL_配置给2_ANTENNAgoif exists (select 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id = r

15、.constid and o.type = 'F') where r.fkeyid = object_id('分配给') and = 'FK_分配给_分配给_CELL')alter table 分配给 drop constraint FK_分配给_分配给_CELLgoif exists (select 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = obje

16、ct_id('分配给') and = 'FK_分配给_分配给2_FREQ')alter table 分配给 drop constraint FK_分配给_分配给2_FREQgoif exists (select 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('控制') and = 'FK_控制_控制_BSC'

17、;)alter table 控制 drop constraint FK_控制_控制_BSCgoif exists (select 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('控制') and = 'FK_控制_控制2_BTS')alter table 控制 drop constraint FK_控制_控制2_BTSgoif exists (sele

18、ct 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('覆盖') and = 'FK_覆盖_覆盖2_CELL')alter table 覆盖 drop constraint FK_覆盖_覆盖2_CELLgoif exists (select 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id

19、 = r.constid and o.type = 'F') where r.fkeyid = object_id('话务量') and = 'FK_话务量_描述_CELL')alter table 话务量 drop constraint FK_话务量_描述_CELLgoif exists (select 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = ob

20、ject_id('路测信息') and = 'FK_路测信息_来自于_CELL')alter table 路测信息 drop constraint FK_路测信息_来自于_CELLgoif exists (select 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('邻区') and = 'FK_邻区_邻区_CEL

21、L')alter table 邻区 drop constraint FK_邻区_邻区_CELLgoif exists (select 1 from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F') where r.fkeyid = object_id('邻区') and = 'FK_邻区_邻区2_CELL')alter table 邻区 drop constraint FK_邻区_邻区2_CELLgoif exis

22、ts (select 1 from sysindexes where id = object_id('Antenna') and name = '配置给_FK' and indid > 0 and indid < 255) drop index Antenna.配置给_FKgoif exists (select 1 from sysobjects where id = object_id('Antenna') and type = 'U') drop table Antennagoif exists (select 1

23、 from sysindexes where id = object_id('BSC') and name = '管理_FK' and indid > 0 and indid < 255) drop index BSC.管理_FKgoif exists (select 1 from sysobjects where id = object_id('BSC') and type = 'U') drop table BSCgoif exists (select 1 from sysindexes where id = ob

24、ject_id('BTS') and name = '对应于_FK' and indid > 0 and indid < 255) drop index BTS.对应于_FKgoif exists (select 1 from sysobjects where id = object_id('BTS') and type = 'U') drop table BTSgoif exists (select 1 from sysindexes where id = object_id('Cell') and

25、name = '配置给2_FK' and indid > 0 and indid < 255) drop index Cell.配置给2_FKgoif exists (select 1 from sysindexes where id = object_id('Cell') and name = '对应于2_FK' and indid > 0 and indid < 255) drop index Cell.对应于2_FKgoif exists (select 1 from sysobjects where id = ob

26、ject_id('Cell') and type = 'U') drop table Cellgoif exists (select 1 from sysobjects where id = object_id('Freq') and type = 'U') drop table Freqgoif exists (select 1 from sysobjects where id = object_id('MSC') and type = 'U') drop table MSCgoif exists

27、 (select 1 from sysindexes where id = object_id('分配给') and name = '分配给2_FK' and indid > 0 and indid < 255) drop index 分配给.分配给2_FKgoif exists (select 1 from sysindexes where id = object_id('分配给') and name = '分配给_FK' and indid > 0 and indid < 255) drop index

28、 分配给.分配给_FKgoif exists (select 1 from sysobjects where id = object_id('分配给') and type = 'U') drop table 分配给goif exists (select 1 from sysindexes where id = object_id('控制') and name = '控制2_FK' and indid > 0 and indid < 255) drop index 控制.控制2_FKgoif exists (select

29、 1 from sysindexes where id = object_id('控制') and name = '控制_FK' and indid > 0 and indid < 255) drop index 控制.控制_FKgoif exists (select 1 from sysobjects where id = object_id('控制') and type = 'U') drop table 控制goif exists (select 1 from sysindexes where id = obje

30、ct_id('覆盖') and name = '覆盖2_FK' and indid > 0 and indid < 255) drop index 覆盖.覆盖2_FKgoif exists (select 1 from sysobjects where id = object_id('覆盖') and type = 'U') drop table 覆盖goif exists (select 1 from sysindexes where id = object_id('话务量') and name =

31、'描述_FK' and indid > 0 and indid < 255) drop index 话务量.描述_FKgoif exists (select 1 from sysobjects where id = object_id('话务量') and type = 'U') drop table 话务量goif exists (select 1 from sysindexes where id = object_id('路测信息') and name = '来自于_FK' and indid &g

32、t; 0 and indid < 255) drop index 路测信息.来自于_FKgoif exists (select 1 from sysobjects where id = object_id('路测信息') and type = 'U') drop table 路测信息goif exists (select 1 from sysindexes where id = object_id('邻区') and name = '邻区2_FK' and indid > 0 and indid < 255) d

33、rop index 邻区.邻区2_FKgoif exists (select 1 from sysindexes where id = object_id('邻区') and name = '邻区_FK' and indid > 0 and indid < 255) drop index 邻区.邻区_FKgoif exists (select 1 from sysobjects where id = object_id('邻区') and type = 'U') drop table 邻区go/*=*/* Table:

34、 Antenna */*=*/create table Antenna ( AntennaHigh float(20) null, HalfPAngle float(20) null, MaxAttenuation int null, Gain int null, AntTilt float(20) null, Pt int null, MsPwr int null, AntennaID int not null, CellID numeric(10) null, constraint PK_ANTENNA primary key nonclustered (AntennaID)go/*=*/

35、* Index: 配置给_FK */*=*/create index 配置给_FK on Antenna (CellID ASC)go/*=*/* Table: BSC */*=*/create table BSC ( BscID numeric(10) not null, MscID numeric(10) not null, BscName char(20) null, BscCompany char(20) null, Longitude double precision null, Latitude double precision null, constraint PK_BSC pr

36、imary key nonclustered (BscID)go/*=*/* Index: 管理_FK */*=*/create index 管理_FK on BSC (MscID ASC)go/*=*/* Table: BTS */*=*/create table BTS ( BtsName char(20) not null, CellID numeric(10) not null, Longitude double precision null, Latitude double precision null, Altitude double precision null, BtsComp

37、any char(20) null, BtsPower int null, constraint PK_BTS primary key nonclustered (BtsName)go/*=*/* Index: 对应于_FK */*=*/create index 对应于_FK on BTS (CellID ASC)go/*=*/* Table: Cell */*=*/create table Cell ( CellID numeric(10) not null, BtsName char(20) not null, AntennaID int not null, AreaName char(2

38、0) null, LAC numeric(20) null, Longitude double precision null, Latitude double precision null, Direction int null, Bcch int null, constraint PK_CELL primary key nonclustered (CellID)go/*=*/* Index: 对应于2_FK */*=*/create index 对应于2_FK on Cell (BtsName ASC)go/*=*/* Index: 配置给2_FK */*=*/create index 配置给2_FK on Cell (AntennaID ASC)go/*=*/* Table: Freq */*=*/create