- Following are the entities we will be creating in the EDM.
- ServiceLine: This is contains the service line details that a resource
can belong to.
- ProjectTeam: This entity will contain the individual team types. Like
Dev, Test, DB and Support teams
- ResourceDetails: This entity will contains the individual member
details and which ServiceLine he/she belongs to and ProjectTeam
- Brand: This entity tracks the resource brand.
- Creating individual entities. Drag an entity instance from the tool box. By
default each entity should have an identity column to identity uniquely in the
object cache. Try to learn about the ObjectStateManager, ObjectStateEntity and
EntityState which is currently out of scope here.
- By selecting the Entity1 properties change the name of the Entity to
ServiceLine
- By selecting the Id property change the name to ServiceLineID. Since this id
we need as primary key make sure that the following properties set
- By right clicking on the Entity add the following scalar properties. Also
repeat the same steps for all the Entities in the table given bellow. Make sure
you are not creating the for now.
ServiceLine Entity
Column Name |
Data Type |
Description |
ServiceLineID |
Int32 |
Unique identifier |
SeviceLineName |
String |
The Service line name |
Band Entity
Column Name |
Data Type |
Description |
BrandID |
Int32 |
Unique identifier |
BrandName |
String |
The brand name of the member |
ProjectTeam Entity
Column Name |
Data Type |
Description |
TeamID |
Int32 |
Unique identifier |
TeamName |
String |
Name project team |
ResourceDetail Entity
Column Name |
Data Type |
Description |
ResourceID |
Int32 |
Unique identifier |
FirstName |
String |
Resource First Name |
LastName |
String |
Resource Last Name |
MiddleName |
String |
Resource Middle Name |
Experience |
Int32 |
The resource experience |
So for your model should look like bellow
Creating Associations and Relationships between Entities
- To create the relationship with between the table. Click on the Association
in the tool box. Drag the ServiceLineID from ServiceLine to ResourceDetail
entity and repeat the same for all of them.
Generating Schema SQL and Creating Database Schema
Before actually we create a Database Script from the model. We will need to
create an empty database where we need to create our schema. Create a database
called ProjectResource in the SQL Server 2008 (which I am using is EXPRESS
edition)
- Right click on the designer and choose 'Generate Database form Model'.
- Create a connection to the project that we created, and click next.
- In the next step the wizard created the DDL for the schema. Click on the
finish button which will create a ProjectTeam.edmx.sql file to the project.
- Open the ProjectTeam.edmx.sql file examine. The file will have the following
sections
- Create all tables
- Creating all primary key constraints
- Creating all FOREIGN KEY constraints
--
--------------------------------------------------
-- Entity
Designer DDL Script for SQL Server 2005, 2008, and Azure
--
--------------------------------------------------
-- Date
Created: 07/16/2010 16:25:10
-- Generated
from EDMX file: D:\3. Research\EF4\ModelFirstDesign\ProjectTeam\ProjectTeam\ProjectTeam.edmx
--
--------------------------------------------------
SET
QUOTED_IDENTIFIER OFF;
GO
USE
[ProjectResource];
GO
IF
SCHEMA_ID(N'dbo')
IS NULL
EXECUTE(N'CREATE
SCHEMA [dbo]');
GO
--
--------------------------------------------------
-- Dropping
existing FOREIGN KEY constraints
--
--------------------------------------------------
--
--------------------------------------------------
-- Dropping
existing tables
--
--------------------------------------------------
--
--------------------------------------------------
-- Creating
all tables
--
--------------------------------------------------
-- Creating
table 'ServiceLines'
CREATE
TABLE [dbo].[ServiceLines]
(
[ServiceLineId] int
IDENTITY(1,1)
NOT
NULL,
[ServiceLineName]
nvarchar(max)
NOT
NULL
);
GO
-- Creating
table 'ResourceDetails'
CREATE
TABLE [dbo].[ResourceDetails]
(
[ResourceId] int
IDENTITY(1,1)
NOT
NULL,
[FirestName] nvarchar(max)
NOT
NULL,
[LasteName] nvarchar(max)
NOT
NULL,
[Experience] int
NOT
NULL,
[MiddleName] nvarchar(max)
NOT
NULL,
[ServiceLine_ServiceLineId]
int NOT
NULL,
[ProjectTeam_TeamId]
int NOT
NULL,
[Band_BandId] int
NOT
NULL
);
GO
-- Creating
table 'ProjectTeams'
CREATE
TABLE [dbo].[ProjectTeams]
(
[TeamId] int
IDENTITY(1,1)
NOT
NULL,
[TeamName] nvarchar(max)
NOT
NULL
);
GO
-- Creating
table 'Bands'
CREATE
TABLE [dbo].[Bands]
(
[BandId] int
IDENTITY(1,1)
NOT
NULL,
[BandName] nvarchar(max)
NOT
NULL
);
GO
--
--------------------------------------------------
-- Creating
all PRIMARY KEY constraints
--
--------------------------------------------------
-- Creating
primary key on [ServiceLineId] in table 'ServiceLines'
ALTER
TABLE [dbo].[ServiceLines]
ADD
CONSTRAINT
[PK_ServiceLines]
PRIMARY KEY
CLUSTERED ([ServiceLineId]
ASC);
GO
-- Creating
primary key on [ResourceId] in table 'ResourceDetails'
ALTER
TABLE [dbo].[ResourceDetails]
ADD
CONSTRAINT
[PK_ResourceDetails]
PRIMARY KEY
CLUSTERED ([ResourceId]
ASC);
GO
-- Creating
primary key on [TeamId] in table 'ProjectTeams'
ALTER
TABLE [dbo].[ProjectTeams]
ADD
CONSTRAINT
[PK_ProjectTeams]
PRIMARY KEY
CLUSTERED ([TeamId]
ASC);
GO
-- Creating
primary key on [BandId] in table 'Bands'
ALTER
TABLE [dbo].[Bands]
ADD
CONSTRAINT
[PK_Bands]
PRIMARY KEY
CLUSTERED ([BandId]
ASC);
GO
--
--------------------------------------------------
-- Creating
all FOREIGN KEY constraints
--
--------------------------------------------------
-- Creating
foreign key on [ServiceLine_ServiceLineId] in table 'ResourceDetails'
ALTER
TABLE [dbo].[ResourceDetails]
ADD
CONSTRAINT
[FK_ServiceLineResourceDetail]
FOREIGN KEY
([ServiceLine_ServiceLineId])
REFERENCES [dbo].[ServiceLines]
([ServiceLineId])
ON DELETE
NO ACTION
ON UPDATE
NO ACTION;
-- Creating
non-clustered index for FOREIGN KEY 'FK_ServiceLineResourceDetail'
CREATE
INDEX
[IX_FK_ServiceLineResourceDetail]
ON
[dbo].[ResourceDetails]
([ServiceLine_ServiceLineId]);
GO
-- Creating
foreign key on [ProjectTeam_TeamId] in table 'ResourceDetails'
ALTER
TABLE [dbo].[ResourceDetails]
ADD
CONSTRAINT
[FK_ProjectTeamResourceDetail]
FOREIGN KEY
([ProjectTeam_TeamId])
REFERENCES [dbo].[ProjectTeams]
([TeamId])
ON DELETE
NO ACTION
ON UPDATE
NO ACTION;
-- Creating
non-clustered index for FOREIGN KEY 'FK_ProjectTeamResourceDetail'
CREATE
INDEX
[IX_FK_ProjectTeamResourceDetail]
ON
[dbo].[ResourceDetails]
([ProjectTeam_TeamId]);
GO
-- Creating
foreign key on [Band_BandId] in table 'ResourceDetails'
ALTER
TABLE [dbo].[ResourceDetails]
ADD
CONSTRAINT
[FK_BandResourceDetail]
FOREIGN KEY
([Band_BandId])
REFERENCES [dbo].[Bands]
([BandId])
ON DELETE
NO ACTION
ON UPDATE
NO ACTION;
-- Creating
non-clustered index for FOREIGN KEY 'FK_BandResourceDetail'
CREATE
INDEX
[IX_FK_BandResourceDetail]
ON
[dbo].[ResourceDetails]
([Band_BandId]);
GO
- Open the file and right click on the file and click on the Connection
-> Connect, which will open the SQL Server "Connect to Database Engine "window and
select the server instance and click on connect.
- Here we need to perform two steps. First verify the syntax and execute SQL
- Right click on the opened file and select Validate SQL Syntax. Make sure that
syntax validation successful.
- Now click on the Execute SQL.
- Go back to your SQL Server database and verify the all tables are created.
- One more thing that we have to confirm is that the relationships. Create a
new database diagram and add all the tables to verify the same.
- By default EDM provides only Create Operation. Select Band entity, right
click and select 'Table Mapping'notice that what is default.