AngularJS : Create a web site using Angular JS, ASP.net MVC, web api - PART 2 : Conception

Download Source Code

PLAN

• PART 1 : Introduction
• PART 2 : Conception
• PART 3 : Coding Server side
• PART 4 : Coding client side

Introduction

In this chapter we will define the model of the database, and start its implementation all using the syntax SQLServer and the Entity Framework ORM.

Using the code

I) DataBase Diagram

Our web site is ECommerce application, that include two actors :

1) Consumer

Is our customer, once his registered, he will have access to his space, across which he can browse product details, add product to shopping cart, send a command and show his commands status.

2) Administrator

he have a possibility to administer the dataBase through : management of product table and Commands table. the management of Command Table consists of changing the state of command to be 'Received', 'Processing' or 'Finish'.

the following DataBase Diagram, explains better the relation between Consumer and Administrator.


      3) Definition of Entities
 
a) EC_User table :

• Description

It contains a list of user  authorized to connect to our web application.

• Properties 

loginUser : is the user name.
mpUser :  is the password of user.

b) EC_Product table :

• description

It contains the details of a product.

• Properties

idPorduct : is a unique identifier of Product.
nameProduct : is a product name.
descriptionProduct : contains some characteristic of product.
imageProduct : contains a path to a physical product image file.

c) EC_Command table :

• description

This table stores history of differents purchases done by user.

• Properties

C_id : is unique identifier of command.
loginUser : is a  reference key of loginUser attribute in EC_User table.
idProduct : is a reference key of idProduct attribute in EC_Product table.
C_status : it refers to the state of command, At first, it receives 'Received' value, after it can change to 'Processing' or 'Finish' state.

II) Implementation

Now, we will show you the differents steps for creation of DataBase named E-Commerce, that implements the above DataBase Diagram.

1) creating Local DataBase (.mdf)


   
      2) creating tables 

In this section we will create our table usign SQL statements :

• EC_User table

 create table EC_User(  
 loginUser varchar(20) Primary key,  
 mpUser varchar(20) NOT NULL,  
 _Type varchar check( _Type in ('A','U'))   
 );   

• EC_Product table

 create table EC_Product(  
 idProduct int identity (1,1) primary key ,  
 nameProduct varchar(100) NOT NULL,  
 descriptionProduct varchar(500),  
 imageProduct varchar(100),  
 price int NOT NULL,  
 );  

• EC_Command table

 create table EC_Command(  
 _id int identity(1,1) primary key,  
 loginUser varchar(20) references EC_User(loginUser) on delete cascade on update cascade,  
 idProduct int references EC_Product(idProduct) on delete cascade on update cascade,  
 _status NOT NULL check(_status between 0 and 2),  
 commandDate DATE DEFAULT GETDATE(),   
 );  

3) Using Entity Framework (EF)

In our example we will use EF Database First approch (we starts from an existing DataBase to have entities) :



   
   
   

Conclusion

Once we finished the implementation of our DataBase Model, we will start the coding of web Api.