User Management Database DETAIL DESIGN DOCUMENT Database design
Project Code: UMS Document Code: UMS_Data_Design_v1.0
Ha Noi, Sep-2012
UMS Database Database Design
v0.1 tggsfdsd
RECORD OF CHANGE
*A - Added M - Modified D Deleted Effective Date Changed Items A* M, D 11-Sep-12 A Create the document 0.1 Change Description New Version
1/11
UMS Database Database Design
v0.1 tggsfdsd
SIGNATURE PAGE
ORIGINATOR: Database Team DB Design Team 12-Sep-12
REVIEWERS:
Tn c Hi DB Designer
13-Sep-12
APPROVAL:
2/11
UMS Database Database Design
v0.1 tggsfdsd
TABLE OF CONTENTS
1 INTRODUCTION ................................................................................................. 4 1.1 Purpose....................................................................................................................4 1.2 Definitions, Acronyms and Abbreviations.....................................................................4 1.3 References................................................................................................................4 1.4 Overview..................................................................................................................4 2 DATABASE ......................................................................................................... 5 2.1 Physical database model............................................................................................5 2.2 Schema....................................................................................................................6 2.3 List of tables.............................................................................................................6 2.4 Table specifications...................................................................................................6 2.5 Initialize data............................................................................................................9
3/11
UMS Database Database Design
v0.1 tggsfdsd
INTRODUCTION
1.1 Purpose
This document details the Design Database for User Management System. This document will also help developers and testers to view database design of application.
1.2 Definitions, Acronyms and Abbreviations
Abbreviations PK/FK Description Primary key/ Foreign key Comment Use to indicate that a field is a primary or a foreign key in table
1.3 References
No 1 Title MIS_UMS_ UseCaseSpecification_v0.2.doc
1.4 Overview
This document includes following sections: This Introduction. Database. The detailed design for the data stored in relational database.
4/11
UMS Database Database Design
v0.1
DATABASE
2.1 Physical database model
5/11
UMS Database Database Design
v0.1
2.2 Schema
All of the data objects belonging to CIE Pricing Database system will be contained in a schema named dbo.
2.3 List of tables
No 01 02 03 04 05 06 Table Name User Role Application Function Privilege UserRole Description Stores information about user Stores information about role Stores information about application Stores information about function Stores information about privilege page to grant roles to access functions Stores information about granting role to user
2.4 Table specifications
2.4.1 No User table Field Name Data Type Not Null 1 2 3 4 5 AccountId Account Password Fullname Birthday int varchar(128) varchar(32) nvarchar(255) datetime X X X x X PK FK Default Value X Index Unique Value X X Store Id of user Store Account of user Store Password of user Store Full name of user Store Date of Birth of user
6/11
Description
UMS Database Database Design 6 7 8 10 11 12 13 2.4.2 No Email Cellphone Descriptions CreatedBy CreateDate ModifiedBy ModifieDate Role table Field Name Data Type Not Null 1 2 3 4 5 6 7 2.4.3 No RoleId RoleName Descriptions CreatedBy CreateDate ModifiedBy ModifieDate UserRole table Field Name Data Type Not Null 1 2 user_role_id user_id int int X X X X PK FK Default Value Index Unique Value X X Store Id of UserRole Link from User table Description int nvarchar(128) nvarchar(255) int datetime int datetime X X X PK FK Default Value X Index Unique Value X X Store Id of Role Store Name of Role Store Description of Role Store Id of creator Store Create date Store Id of modifier Store Modify date Description varchar(50) varchar(20) nvarchar(1000) int datetime int datetime Store Email of user Store Phone Number of user Store Description of user Store Id of creator Store Create date Store Id of modifier Store Modify date
v0.1
7/11
UMS Database Database Design 3 2.4.4 No role_id Project table Field Name Data Type Not Null 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 ProjectId ProjectCode ProjectName ProjectScope PlannedStartDate PlannedEndDate ActualStartDate ActualEndDate ScrumMaster ProductOwner PlannedEffort ProjectStatus SprintInterval CreatedBy CreateDate ModifiedBy ModifiedDate int varchar(30) nvarchar(128) nvarchar(255) date date date date int int int varchar(20) int int date int date X X X X X X X X X X X PK FK Default Value Index Unique Value X Store Id of Project Store code of project Store name of project Store scope of project Store the planned start date of project Store the planned end date of project Store the actual start date of project Store the actual end date of project Store Id of user that is the scrum master of project Store Id of user that is the product owner of project Store the planned effort of project Store the status of project Store the sprint interval of project Store Id of creator Store Create date Store Id of modifier Store Modify date Description int X X X Link from Role table
v0.1
8/11
UMS Database Database Design
v0.1
2.5 Initialize data
2.5.1 User table ID 1 Account Administrator Password Admin FullName Admin
2.5.2
Role table ID 1 2 3 4 5 RoleName SA AM SC BM NU Description System Administrator Application Manager Scrum Master BM Normal User
2.5.3
UserRole table UserRoleId 1 UserId 1 RoleId 1
9/11
