24-02-2010, 11:24 PM
[attachment=2424]
DATABASE ENTERPRISE MANAGER
By
Aswathy.S Jyothy S Menon Seethu Sabu
ABSTRACT
The proposed Database Enterprise Manager is a windows application tool for Microsoft SQL Server 2000 and provides a user interface that allows users to:
> Create and administer all SQL Server databases in each registered server.
> Define and execute all SQL Server administrative tasks on each registered server.
> Using the Database Enterprise Manager, any user can easily create and manage databases in SQL server.
> Tables, Stored Procedures and Views can be created in each database.
Database Creation
A database in Microsoft SQL Server 2000 consists of a collection of tables that contain data and other objects, such as views, stored procedures etc, defined to support activities performed with the data. SQL Server can support many databases where the user can store the data in the relational model. The database enterprise manager facilitates the creation and manipulation of databases.
Table Creation
Tables are database objects that contain all the data in a database. A table definition is a collection of columns. Using the database enterprise manager the users can create the tables in Microsoft SQL Server. The user can give the table name and set the other properties of tables like table columns, table rows etc.
View Creation
A view is a virtual table whose contents are defined by a query. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.
Procedure Creation
Stored procedures in SQL Server can accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch. It can contain programming statements that perform operations in the database, including calling other procedures and it returns a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).
CONTENTS
SLNO TOPIC PAGE NO
1. Introduction 01
2. System Requirements 02
3. System Environment 04
3.1. The Microsoft.NET 04
3.2. Visual Basic.NET 19
3.3. Microsoft SQL Server2000 25
4. System Study 31
4.1. Existing system 31
4.2. Proposed System 32
4.3. Feasibility Analysis 33
5. System Design 35
5.1. Data Flow Diagram 37
5.2. Input and Output Design 41
5.3. Code Design 59
5.4. Database Design 62
5.5. Process Design 66
6. System Testing 71
7. Implementation and Maintenance 74
8. System Security Measures 77
9. Future scope and enhancement 80
10. Conclusion 81
11. References 82
LIST OF TABLES
Table No Description Page no.
1 .NET Enterprise Servers 06
2 Common Language Runtime Components 13
3 AdminLogin Table 65
4 UserPermission Table 65
5 UserRegistration Table 65
LIST OF FIGURES
Description Page no
The Microsoft .NET Platform . 4
Building Components in the .NET Framework 7
The components of the .NET Framework 11
Common Language Runtime 12
.NET Framework Class Library 14
ADO .NET: Data and XML 15
ASP .NET: Web Forms and Services 16
User Interface 18
SQL Server Enterprise Manager 27
Basic DFD symbols 38
Context Level DFD 39
System Level DFD 39
Administrator Level DFD 40
INTRODUCTION
Business today demands a different kind of data management solution. Performance, scalability, and reliability are essential, but businesses now expect more from their key IT investments. The ability to transform corporate data into meaningful and actionable information is the single most important source of competitive advantage in today's business world. Harnessing the data explosion to better understand the past and get direction for the future has turned out to be one of the most challenging ventures for enterprise Information Technology departments in global organizations.
SQL Server is commonly used by businesses for small- to medium-sized databases but the past five years have seen greater adoption of the product for larger enterprise databases. It delivers an entirely new set of management tools for both DBAs and developers.
The proposed Database Enterprise Manager is a windows application tool for Microsoft SQL Server that brings together a bunch of monitoring and management tools in a single interface .It allows users to create and administer all SQL Server databases in each registered server, define and execute all SQL Server administrative tasks on each registered server, provide user accounts, set access privileges.
PROCESSOR
PENTIUM
CLOCK SPEED
800 MHZ
SYSTEM BUS
32 BIT
RAM
128 MB
DD
5GB
MONITOR
SVGA COLOR
KEY BOARD
108 KEYS
MOUSE
LOGITECH
FDD
1.44 MB
Software Requirements
OPERATING SYSTEM
WINDOWS SERVER'OO, WINDOWS XP
ENVIRONMENT
MICROSOFT .NET
MICROSOFT .NET FRAME WORK MICROSOFT MOBILE EXPLORER
[.NET assemblies,
Windows Services,
ADO.NET]
FRONT END
VB.NET
BACKEND
MICROSOFT SQL SERVER 2000
Available Today
New
Capabilities
Orchestration |
Internet .NET Enterprise Servers Building
Block Services :
_ :
.NET
Framework COM+
Windows
With .NET Enhancements
Microsoft released the .Net framework in February 2002.lt is the biggest initiative since the launch of windows in 1991. .Net is a revolutionary Multilanguage platform that knits various aspects of application development together with the internet. The framework covers all layers of software development above the operating system. Several softwares will be developed by Microsoft to achieve this goal. It is accepted that every player in the industry, be it a software developer or a device manufacturer, adopt .Net so that they can be integrated.
The .NET Platform is a set of technologies designed to transform the Internet into a full-scale distributed computing platform. It provides new ways to build applications from collections of Web Services. The .NET Platform fully supports the existing Internet infrastructure, including HTTP, XML, and SOAP.
Core Technologies in the .NET Platform
.NET Framework
The .NET Framework is based on a new common language runtime. This runtime provides a common set of services for projects built in Visual Studio .NET, regardless of the language. These services provide key building blocks for applications of any type, across all application tiers.
Microsoft Visual Basic, Microsoft Visual C++, and other Microsoft programming languages have been enhanced to take advantage of these services. Third-party languages that are written for the .NET Platform also have access to the same services. Because of the common language runtime; all .NET languages will use the same run-time files. This means that there is no need to distribute Visual Basic-specific run-time libraries because .NET run-time files will be installed automatically in future versions of Microsoft Windows.
.NET Building Block Services
The .NET Building Block Services are distributed programmable services that are available both online and offline. A service can be invoked on a stand-alone computer not connected to the Internet, provided by a local server running inside a company, or accessed by means of the Internet. The .NET Building Block Services can be used from any platform that supports SOAP. Services include identity, notification and messaging, personalization, schematized storage, calendar, directory, search, and software delivery.
Visual Studio .NET
Visual Studio .NET provides a high-level development environment for building applications on the .NET Framework. It provides key enabling technologies to simplify the creation, deployment, and ongoing evolution of secure, scalable, highly available Web applications and Web Services. It also enables a new generation of Windows-based applications with many new features available through the .NET Framework.
.NET Enterprise Servers
The .NET Enterprise Servers provide scalability, reliability, management, and integration within and across organizations, and many other features as described in the following table 1.
I
Server Description
Microsoft SQL Server 2000 Includes rich XML functionality, support for Worldwide Web Consortium (W3C) standards, and the ability to manipulate XML data by using Transact SQL (T-SQL), flexible and powerful Web-based analysis, and secure access to your data over the Web by using HTTP.
Microsoft BizTalk Server 2000 Provides enterprise application integration (EAI), business-to-business integration, and the advanced BizTalk Orchestration technology to build dynamic business processes that span applications, platforms, and organizations over the Internet.
Microsoft Host Integration Server 2000 Provides the best way to embrace Internet, intranet, and client/server technologies while preserving investments in existing systems. Microsoft Host Integration Server 2000 is the replacement for SNA Server.
Microsoft Exchange 2000 Enterprise Server Builds on the powerful Exchange messaging and collaboration technology by introducing several important new features and further increasing the reliability, scalability, and performance of it's core architecture. Other features enhance the integration of Exchange 2000 with Microsoft Windows 2000, Microsoft Office 2000, and the Internet.
Microsoft Application Center 2000 Provides a deployment and management tool for high availability Web applications.
Microsoft Internet Security and Acceleration Server 2000 Provides secure, fast, and manageable Internet connectivity. Internet Security and Acceleration Server integrates an extensible, multilayer enterprise firewall and a scalable, high performance Web cache. It builds on Windows 2000 security and directory for policy-based security, acceleration, and management of internetworking.
Microsoft Commerce Server 2000 Provides an application framework, sophisticated feedback mechanisms, and analytical capabilities.
Overview of the .NET Framework
The .NET Framework provides all the common services required to run your Visual Basic .NET applications.
Building Components in the .NET Framework
Before COM, applications were completely separate entities with little or no integration. Using COM, you can integrate components within and across applications by exposing common interfaces. However, as a developer, you must still write the code to wrap, manage, and clean up after components and objects.
In the .NET Framework, components are built on a common foundation. You no longer need to write the code to allow objects to interact directly with each other. In the .NET environment, you no longer need to write component wrappers because components do not use wrappers. The .NET Framework can interpret the constructs that developers are accustomed to using in object oriented languages. The .NET Framework fully supports class, inheritance, methods, properties, events, polymorphism, constructors, and other object oriented constructs.
The Common Language Specification
The Common Language Specification (CLS) defines the common standards to which languages and developers must adhere if they want their components and applications to be widely useable by other .NET-compatible languages. The CLS allows Visual Basic .NET developers to create applications as part of a multiple-language team with the knowledge that there will be no problems integrating the different languages. The CLS even allows Visual Basic .NET developers to inherit from classes defined in different languages.
Visual Studio .NET
In the .NET Framework, Visual Studio .NET provides the tools you can use for rapid application development.
Goals of the .NET Framework
The .NET Framework was designed to meet the following goals:
¦ Based on Web standards and practices
The .NET Framework fully supports the existing Internet technologies; including Hypertext Markup Language (HTML), XML, SOAP, and Extensible Style sheet Language for Transformations (XSLT), Xpath, and other Web standards. The .NET Framework favors loosely connected, stateless Web services.
¦ Extensible
The hierarchy of the .NET Framework is not hidden from the developer. You can access and extend .NET classes (unless they are sealed) by using inheritance. You can also implement cross-language inheritance.
¦ Easy for developers to use
In the .NET Framework, code is organized into hierarchical namespaces and classes. The Framework provides a common type system, referred to as the unified type system that is used by any .NET-compatible language. In the unified type system, everything is an object. There are no variant types, there is only one string type, and all string data is Unicode.
¦ Designed using unified application models
A .NET class' functionality is available from any .NET-compatible language or programming model.
Languages in the .NET Framework
The .NET Platform is language neutral†all common services needed to enable programs to run are provided by the .NET Framework. The .NET Framework provides support for numerous programming languages, including Microsoft and third-party languages.
Microsoft Visual Basic .NET
Visual Basic .NET provides substantial language innovations over previous versions of Visual Basic. Visual Basic .NET supports inheritance, constructors, polymorphism, overloading, structured exceptions, stricter type checking, free threading, and many other features. With this release, Visual Basic Scripting Edition provides full Visual Basic functionality.
Microsoft Visual C#
C# was designed for the .NET Platform and is the first modern component-oriented language in the C and C++ family. It can be embedded in ASP .NET pages. Some of the key features of this language include classes, interfaces, delegates, boxing and unboxing, namespaces, properties, indexers, events, operator overloading, versioning, attributes, unsafe code, and XML documentation generation. No header or Interface Definition Language (IDL) files are needed.
Managed Extensions C++
The Visual C++ upgrade is a managed, minimal extension to the C++ language. This extension provides access to the .NET Framework that includes garbage collection, single-implementation inheritance, and multiple-interface inheritance. This upgrade also eliminates the need to write plumbing code for components. It offers low-level access where useful.
Microsoft JScript .NET
JScript .NET has been rewritten to be fully .NET compatible. It includes support for classes, inheritance, types, and compilation. It provides improved performance and productivity features. JScript .NET is also integrated with Visual Studio .NET. You can use of any .NET Framework class in JScript .NET.
Third-party languages
Various third-party languages are supporting the .NET Platform. These languages include APL, COBOL, Pascal, Eiffel, Haskell, ML, Oberon, Perl, Python, Scheme, and SmallTalk.
The .NET Framework Components
ASP NET: Web User
Forms & Services Interface
ADO .NET: Data & XML
.NET framework Claw Library
Common Language Runtime
The components of the .NET Framework are as follows:
¦ Common language runtime
¦ .NET Framework Class Library
¢ ADO .NET: data and XML
¦ ASP .NET: Web Forms and Services
¦ User interface
Common Language Runtime
" 1-
Thread ^pporl L COI $ Marshal er
-
w ..
Type Checker Exception Manager
mm >ug Engine
MSIL to Native [ Compilers Code Manager r
Garb Colle< age rtion
w â€â€ -* Class Loader |^ , __J
. The Common Language Runtime simplifies application development, provides a robust and secure execution environment, supports multiple languages, simplifies application deployment and management, and provides a managed environment.
A managed environment is one in which the environment provides common services automatically. Examples of the types of services a managed environment provides are garbage collection and security.
Common Language Runtime Components
Component Description
Class loader Loads the implementation of a loadable type into memory and prepares it for execution.
Microsoft intermediate language (MSIL) to native compiler Converts MSIL to native code (just-in-time).
Code manager Manages code execution.
Garbage collection Provides automatic lifetime management of all of your objects. This is a multiprocessor, scalable garbage collector.
Security engine Provides evidence-based security based on the origin of the code as well as the user.
Debug engine Allows you to debug your application and trace the execution of code.
Type checker Will not allow unsafe casts or uninitialized variables. IL can be verified to guarantee type safety
Exception manager Provides structured exception handling, which is integrated with Windows Structured Exception Handling (SEH). Error reporting has been improved.
Thread support Provides classes and interfaces that enable multithreaded programming.
COM marshaler Provides marshaling to and from COM.
.NET Framework Class Library support Integrates code with the runtime that supports the .NET Framework Class Library.
The .NET Framework Class Library exposes features of the runtime and provides other high-level services that every programmer needs through an object hierarchy. This object hierarchy is known as a namespace.
System Namespaces
The System namespace contains fundamental classes and base classes that define commonly-used value and reference data types, events and event handlers, interfaces, attributes, and processing exceptions. Other classes provide services supporting data type conversion, method parameter manipulation, mathematics, remote and local program invocation, application environment management, and supervision of managed and unmanaged applications. The System.Collections namespace provides sorted lists, hash tables, and other ways to group data. The System.10 namespace provides file I/O, streams, and so on. The System.NET namespace provides Transmission Control Protocol/Internet Protocol (TCP/IP) and sockets support.
ADO .NET is the next generation of ActiveX Data Object (ADO) technology. ADO .NET provides improved support for the disconnected programming model. It also provides rich XML support.
System.Data Namespace
The System.Data namespace consists of classes that constitute the ADO .NET object model. At a high level, the ADO .NET object model is divided into two layers: the connected layer and the disconnected layer. The System.Data namespace includes the DataSet class, which represents multiple tables and their relations. These data sets are completely self-contained data structures that can be populated from a variety of data sources. One data source could be XML, another could be OLE DB, and a third data source could be the direct adapter for SQL Server.
System.Xml Namespace
The System.Xml namespace provides support for XML. It includes an XML parser and a writer, which are both W3C compliant. Transformations are provided by the System.Xml.Xsl namespace and the implementation of Xpath that allows data graph navigation in XML. The System.XML.Serialization namespace provides the entire core infrastructure for Web Services, including features such as moving back and forth between objects and an XML representation.
ASP .NET: Web Forms and Services
ASP .NET is a programming framework that is built on the common .language runtime and that can be used on a server to build powerful Web implications. ASP .NET Web Forms provide an easy and powerful way to build dynamic Web user interfaces (Uls). ASP .NET Web Services provide the building blocks for constructing distributed Web-based applications. Web Services are based on open Internet standards, such as HTTP and XML. You can think of a Web Service as a reusable component that is accessible across the Internet, rather than being limited to Windows clients on a local area network.
The common language runtime provides built- in support for creating and exposing Web Services by using a programming abstraction that is consistent and familiar to both Active Server Pages (ASP) and Visual Basic developers. The resulting model is both scalable and extensible. This model is based on open Internet standards (HTTP, XML, SOAP, and SDL) so that it can be accessed and interpreted from any client or Internet-enabled device. Some of the more common ASP .NET classes are described in the following paragraphs.
System.Web Namespace
In the System.Web namespace, there are services such as caching, security, configuration, and others that are shared between Web Services and Web user interface (Ul).
System.Web.Services Namespace
The System.Web.Services namespace handles Web Service requirements such as transportation protocols and service discovery.
System.Web.UI Namespace
The System.Web.UI namespace provides two classes of controls: HTML controls and Web controls. The HTMLControls provide direct mapping of HTML tags, such as <INPUT>. There are also WebControls that are richer and allow you to structure controls with templates (for example, a grid control).
Windows applications can provide more powerful user interfaces than ever by using the NET Framework's System.Windows.Forms and System.Drawing namespaces. The new .NET Windows Forms will look very familiar to Visual Basic developers.
System.Windows.Forms Namespace
You can use the System.Windows.Forms namespace classes to build the client Ul. This class lets you implement the standard Windows Ul in your .NET applications. Many functions that were previously only accessible by means of application programming interface (API) calls are now available as part of the forms themselves, making development much easier and more powerful.
System.Drawing Namespace
The System.Drawing namespace provides access to GDI+ basic graphics functionality. More advanced functionality is provided in the System.Drawing.Drawing2D, System.Drawing.Imaging and System.Drawing.Text namespaces.
3.2. Visual Basic .NET
Earlier versions of Visual Basic were targeted for Microsoft Windows client applications, the Visual Basic .NET, next generation of the Visual Basic language, is a fast and easy way to create .NET-based applications, including XML Web Services and Web applications. Visual Basic .NET has many new and improved features that make it a powerful object-oriented programming language, including inheritance, interfaces, and overloading. Other new language features include free threading and structured exception handling.
f Visual Basic .NET also fully integrates the .NET Framework and the Common Language Runtime, which provide language interoperability, garbage collection, enhanced security, and improved versioning support. This also includes custom attributes, and common language specification (CLS) compliance.
The changes in Visual Basic .NET are intended to:
¢ Simplify the language and make it more consistent.
¢ Add new features requested by users.
¢ Make code easy to read and maintain.
I Help programmers avoid introducing coding errors.
¢ Make applications more robust and easier to debug.
Features of Visual Basic.NET CLS
The CLS is a set of rules that standardizes such things as data types and how objects are exposed and interoperate. Visual Basic .NET adds several features that take advantage of the CLS. Any CLS-compliant language can use the classes, objects, and components you create in Visual Basic .NET. And you, as a Visual Basic user, can access classes, components, and objects from other CLS-compliant programming languages without worrying about language-specific differences such as data types. CLS features used by Visual Basic .NET programs include assemblies, namespaces, and attributes.
Inheritance
Visual Basic .NET supports inheritance by allowing you to define classes that serve as the basis for derived classes. Derived classes inherit and can extend the properties and methods of the base class. They can also override inherited methods with new implementations. All classes created with Visual Basic .NET are inheritable by default. Because the forms you design are really classes, you can use inheritance to define new forms based on existing ones.
Exception Handling
Visual Basic .NET supports structured exception handling, using an enhanced version of the TRY...CATCH..FINALLY syntax supported by other languages such as C++. Structured exception handling combines a modern control structure (similar to Select Case or While) with exceptions, protected blocks of code, and filters. Structured exception handling makes it easy to create and maintain programs with robust, comprehensive error handlers
Overloading
Overloading is the ability to define properties, methods, or procedures that have the same name but use different data types. Overloaded procedures allow you to provide as many implementations as necessary to handle different kinds of data, while giving the appearance of a single, versatile procedure.
Overriding Properties and Methods
The Overrides keyword allows derived objects to override characteristics inherited from parent objects. Overridden members have the same arguments as the members inherited from the base class, but different implementations. A member's new implementation can call the original implementation in the parent class by preceding the member name with My Base.
Constructors and Destructors
Constructors are procedures that control initialization of new instances of a class. Conversely, destructors are methods that free system resources when a class leaves scope or is set to nothing. Visual Basic .NET supports constructors and destructors using the Sub New and Sub Finalize procedures.
Data Types
Visual Basic .NET introduces three new data types. The Char data type is an unsigned 16-bit quantity used to store Unicode characters. It is equivalent to the NET Framework System. Char data type. The Short data type, a signed 16-bit integer, was named Integer in earlier versions of Visual Basic. The Decimal data type is a 96-bit signed integer scaled by a variable power of 10. In earlier versions of Visual Basic, it was available only within a Variant.
Interfaces
Interfaces describe the properties and methods of classes, but unlike classes, do not provide implementations. The Interface statement allows you to declare interfaces, while the Implements statement lets you write code that puts the items described in the interface into practice.
References
References allow you to use objects defined in other assemblies. In Visual Basic .NET, references point to assemblies instead of type libraries.
Namespaces
Namespaces prevent naming conflicts by organizing classes, interfaces, and methods into hierarchies.
Assemblies
Assemblies replace and extend the capabilities of type libraries by, describing all the required files for a particular component or application. An assembly can contain one or more namespaces.
Attributes
Attributes enable you to provide additional information about program elements. For example, you can use an attribute to specify which methods in a class should be exposed when the class is used as a XML Web service.
Multithreading
Visual Basic .NET allows you to write applications that can perform multiple tasks independently. A task that has the potential of holding up other tasks can execute on a separate thread, a process known as multithreading. By causing complicated tasks to run on threads that are separate from your user interface, multithreading makes your applications more responsive to user input.
Visual Basic .NET Enhancements
Visual Basic .NET provides major language enhancements, full access to the .NET Framework, and enhanced Web development.
Major Language Enhancements
In Visual Basic .NET, Visual Basic has undergone some significant changes to allow Visual Basic developers to create powerful, robust, and scalable enterprise applications.
¢ Enhanced object-oriented support
Allows Visual Basic NET developers to use class inheritance, constructors, destructors, overloading, interfaces, and polymorphism. This gives Visual Basic .NET as much object-oriented power as any other .NET language, such as C# or Visual C++ with managed extensions.
¢ Structured exception handling
Simplifies exception handling, and allows you to use powerful features such as nested exceptions. This greatly improves the previous Visual Basic error handler.
Full Access to the .NET Framework
Visual Basic .NET developers have full access to the .NET Framework, including the entire .NET Framework Class Library.
¢ New threading options
Allow you to create applications that use multithreaded capabilities. For the first time, Visual Basic developers will be able to create applications that are capable of rivaling Visual C++-based applications in this area.
¢ Garbage collection
. Ensures that applications created in Visual Basic .NET do not hold onto unreferenced memory. This background process addresses memory-related issues such as memory leaks and circular references that existed in previous versions of Visual Basic and other languages.
Enhanced Web Development
Visual Basic .NET enables developers to quickly create powerful Web applications.
¢ Create Web Forms easily
You can do this as easily as Windows Forms by using the familiar technique of writing code behind the event of a control. No longer do developers need to use one integrated development environment (IDE) for Web page development (such as Microsoft Visual InterDev®) and another for component development. All aspects of a Web application are now simply created in a single environment in a way that will be easy for Visual Basic developers to understand.
¢ Create Web Services quickly
You can do this quickly in a fashion similar to creating components in previous versions of Visual Basic. Web Services is a powerful technology that allows you to access your components (or other people's components) across the Internet by means of HTTP. Any Visual Basic developer who has created components will easily make the transition to Web Services.
3.3. MICROSOFT SQL SERVER 2000
Microsoft SQL server 2000 is a relational database management system (RDBMS) that is used to store and organize related data. This is based on the client/server architecture in which data is stored on a centralized computer called server. Other computers called clients can access the data stored on the server through a network. SQL server 2000 is an advanced version of SQL server version 7.0.
Microsoft SQL Server2000 extends the performance, reliability, quality, and ease-of-use of Microsoft SQL Server version 7.0. Microsoft SQL Server 2000 includes several new features that make it an excellent database platform for large-scale online transactional processing (OLTP), data warehousing, and e-commerce applications.
The OLAP Services feature available in SQL Server version 7.0 is now called SQL Server 2000 Analysis Services. The term OLAP Services has been replaced with the term Analysis Services. Analysis Services also includes a new data-mining component.
The Repository component available in SQL Server version 7.0 is now called Microsoft SQL Server 2000 Meta Data Services. References to the component now use the term Meta Data Services. The term repository is used only in reference to the repository engine within Meta Data Services.
SQL Server Enterprise Manager
SQL Server Enterprise Manager is the Microsoft SQL Server 2000 MMC snap-in. SQL Server Enterprise Manager is the primary administrative tool for Microsoft SQL Server 2000 and provides a Microsoft Management Console (MMC)-compliant user interface that allows users to:
¢ Define groups of servers running SQL Server.
¢ Register individual servers in a group.
¢ Configure all SQL Server options for each registered server.
I ¢ Create and administer all SQL Server databases, objects, logins, users, and permissions in each registered server.
I ¢ Define and execute all SQL Server administrative tasks on each registered server.
, ¢ Design and test SQL statements, batches, and scripts interactively by invoking SQL Query Analyzer.
¢ Invoke the various wizards defined for SQL Server.
\ MMC is a tool that presents a common interface for managing different server applications in a Microsoft Windows network. Server applications provide a component called an MMC snap-in that presents MMC users with a user interface for managing the server application.
1 HE mui *» Toots window MAP
In a relational database such as Microsoft SQL Server 2000, all operations on the tables in the database produce a result in the form of a table. The result set of a SELECT statement is in the form of a table. Traditional client/server applications that execute a SELECT statement process the results by fetching one row or block of rows from the tabular result set at a time and mapping the column values into program variables. Web application programmers, on the other hand, are more familiar with working with hierarchical representations of data in XML or HTML documents.
The results of SELECT statements can be returned as XML documents. Complex queries, or queries that need be made secure, can be stored as templates in an IIS virtual root, and executed by referencing the template name.
: The data from an XML document can be exposed as a relational rowset using the new OPENXML row set function. This allows using the data in XML
t Depar
documents to insert, update, or delete data in the tables of the database, including modifying multiple rows in multiple tables in a single operation.
Accessing Data Stored In SQLSERVER 2000
Before one access data from an SQL server, one need to perform the following steps in a specified sequence.
¦
1. Establish a connection with SQL server.
2. Write the actual command to access or manipulate data.
3. Create a result set of the data from the data source with which the application can work. This result set is called data set and is disconnected from the actual source. The application accesses and updates data in the data set which is later reconciled with the actual data source.
Main Components of SQL Database
The main components of an SQL database are tables, views and stored procedures.
Tables
A SQL database stores data in tables which consists of rows and columns. A column stores information regarding properties of an item, while a row stores the complete information of an item. Each co\umn has a spec\f\c data type. Each table must have at least one column that uniquely identifies each row in the table. This column is called the primary key. Tables can be created, inserted, updated and deleted.
Views
A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a ;table is referenced.
A view is used to do any or all of these functions:
I ¢ Restrict a user to specific rows in a table. For example, allow an employee
' to see only the rows recording his or her work in a labor-tracking table. J ¢ Restrict a user to specific columns. For example, allow employees who do not work in payroll to see the name, office, work phone, and department columns in an employee table, but do not allow them to see any columns with salary information or personal information. ! ¢ Join columns from multiple tables so that they look like a single table. ¢ Aggregate information instead of supplying details. For example, present the sum of a column, or the maximum or minimum value from a column.
Views can also be created and deleted. But when a view is updated or inserted it will be looking in to the fact that only one table is affected by the operation.
Stored Procedures
A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. Microsoft SQL Server 2000 stored procedures return data in four ways:
1. Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time). I 2. Return codes, which are always an integer value.
3. A result set for each SELECT statement contained in the stored procedure or any other procedures called by the stored procedure.
4. A global cursor that can be referenced outside the stored procedure.
Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.
Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server. The IF statement in this example shows embedding conditional logic in a procedure to keep from sending a result set to the application. Stored Procedures can also be created, updated and deleted.
SYSTEM STUDY
The Enterprise Manager is a graphical interface tool used to administer your SQL Servers. You can use the Enterprise Manager to configure SQL Server options, ipreate/edit/view databases, perform maintenance and backups. The Administrator can also create tables, views and stored procedures, and modify them. By using the concept of user authentication he can provide more security to the databases. The administrator can create or delete users. Also he can register various users and grant them permissions to access the databases in MS-SQL Server.
4.1. EXISTING SYSTEM
The development of our project needed the study of the existing systems if any. The existing system requires more memory and a lot of file management. When running the Enterprise Edition of SQL Server 2000 on either the Windows 2000 Server or Windows 2000 Datacenter Server platforms, more than 4GB of imemory is required.
Also the administrator cannot authenticate the users and grant then permissions to access the databases. So security to the databases is not assured.
In a centralized system such as Bank, there will be one server and many clients. So the Administrator will have to install the SQL Enterprise manager in all clients, which leads to lot of memory wastage.
4.2. PROPOSED SYSTEM
Enterprise Manager is the graphical way to manage SQL Server, and with it you can easily access, edit the databases and manage users and security.
All databases in SQL are displayed in a tree structure. On selecting each database, corresponding tables, views and stored procedures are listed. He can modify each of them according to his needs using a Graphical User Interface System. Accesses to all the tables in the database along with their contents are easily accessible. From the database listing, you can also drill into views, stored procedures and run some basic SQL statements.
With the enterprise manager, we can do the following things:
¢ Configure all SQL Server options for each registered server.
¢ Create and administer all SQL Server databases, objects, logins, users, and permissions in each registered server.
¢ Define and execute all SQL Server administrative tasks on each registered server
¢ Manage users and security
I # Perform maintenance and backups of databases.
4.3. FEASIBILITY ANALYSIS
Feasibility analysis is the procedure for identifying the candidate system, evaluating and electing the most feasible system. This is done by investigating the existing system in the area under investigation or generally ideas about a new system. It is a test of a system proposal according to its workability, impact on the organization, ability to meet user needs, and effective use of resources. The objective of feasibility study is not to solve the problem but to acquire a sense of its scope. Feasibility analysis involves 8 steps:
1. Form a project team and appoint a project leader.
2. Prepare system flow charts.
3. Enumerate potential candidate system.
4. Describe and identify characteristics of candidate systems.
5. Determine and evaluate performance and cost effectiveness of each candidate system.
6. Weigh system performance and cost data.
7. Select the best candidate system.
8. Repair and report final project directive to management.
Three key considerations are involved in the feasibility analysis: economic, technical and behavioral.
Economic feasibility
Economic analysis is the most frequently used method for evaluating the effectiveness of a candidate system. It is more commonly known as cost benefit analysis, the procedure to determine the benefits and saving that are expected from a candidate system and compare them with costs. If the benefits outweigh costs then a decision is made to design and implement the [system. Otherwise make alterations in the proposed system.
Technical feasibility
The assessments of technical feasibility centers on the existing system and to what extent it can support the proposed addition. This was based on an outline design of system requirements in turns of inputs, files, programs, procedures, and staff. It involves financial considerations to accommodate technical enhancements.
Behavioral feasibility
People are inherently resistant to change, and computers have been known to facilitate change. An estimate should be made about the reaction if the user staff towards the development of a computerized system. Computer Installations have something to do with turnover, transfers and changes in job (status. The introduction of a candidate system requires special effort to educate, sell and train the staff for conducting the business.
The candidate system was found to be technically, economically, and behaviorally feasible. The system was developed user friendly, needless training and improves the working environment. Justification for any capital outlay is that it will increase profit, reduce expenditure or improve the quality of service or goods, which in turn may be expected to provide increased profits. Disregarding the initial expenses, the candidate system was assessed to be feasible in all ways.
SYSTEM DESIGN
System design refers to the technical specifications that will be applied in implementing the candidate system. This involves input/output, database, and processing design. System design is a solution, a how to approach to the creation of the new system. The system objectives outlined during the feasibility study serve as the basis from which the work of system design is initiated. Much of the activities involved at this stage are of technical nature requiring a certain degree of experience in designing systems, sound knowledge of computer related technology and through understanding of computers available in the [market and various facilities provided by the vendors.
Nevertheless, a system cannot be designed in isolation without the active involvement of the user. The user has a vital role to play at this stage too. As we know that data collected during feasibility study will be utilized systematically during the system design. It should, however, be kept in mind that detailed study of the existing system is not necessarily over with the completion of the feasibility study. Depending on the plan of feasibility study, the level of detailed study will vary and the system design stage will also vary in the amount of investigation that still needs to be done. This investigation is generally an urgent activity during the system design as the designer needs to study minute details in aspects of the system.
Designing a new system is a creative process, which calls for logical as well as lateral thinking. The logical approach involves systematically moves towards the end product keeping in mind the capabilities of the personnel and the equipment at each decision making step. Lateral thought implies encompassing of ideas beyond the usual functions and equipments. This is to ensure that no efforts are being made to fit previous solutions into new situations.
The real essence of a system design is devising a system that makes the processing requirements. The design of an information system produces the details that state how a system will need the requirements identified during the system analysis.
Design goes through the logical and physical states of development. Logical design reviews the present physical system; prepares the input-output specifications; makes edit; security and control specification; details of implementation plan and prepares a logical design path.
Logical design proceeds from the top-down. General features, such as reports and inputs are identified first. Then each studied individually and more detail. Hence, the structured design partitions a program into small, independent modules. They are arranged in a hierarchy that approximates a model of the business are and is organized in a top-down manner. Thus, structured design is an attempt to minimize the complexity and make a problem manageable by subdividing it into smaller segments, which is called modularization or decomposition. In this way structuring minimizes intuitive reasoning and promotes maintainable provable systems.
The physical design maps out the details of the physical system and plans the system implementation and specifies the hardware and software requirements.
5.1. DATA FLOW DIAGRAM
A Data Flow Diagram (DFD) or a Bubble chart is a network that describes the flow of data and processes that change, or transform data through out the system. This network is constructed by using a set of symbols that do not imply a physical implementation. It is a graphical tool for structured analysis of system requirements DFD models a system by using external entities from which data flows to a process, which transfer the data and creates, output-data-flows which go to other external entities or files.
There are various symbols used in a Data Flow Diagram. Bubbles represent the processes. Named arrows indicate the data flow. External entities are represented by rectangles and are outside the system such as vendors or customers with whom the system interacts. They either supply or consume data. Entities supplying data are known as sources and those that consume data are called sinks. Data are stored in a data store by a process in the system. Each component in a Data Flow Diagram is labelled with a descriptive name. Process names are further identified with a number.
Data Flow Diagram can be hierarchically organized, which help in partitioning and analyzing large systems. As a first step, one Data Flow Diagram can depict an entire system, which gives the system overview. It is called context Diagram of level 0 DFD. The Context Diagram can be further expanded. The successive expansion of a Data Flow Diagram from the context diagram to those giving more details is known as levelling of Data Flow Diagram. Thus a top down approach is used, starting with an overview and then working out the details.
The main merit of Data Flow Diagram is that it can provide an overview of system requirements, what data a system would process, what transformation of data are done, what files are used, and where the results flow.
Basic DFD symbols
â€> A data flow is a route, which enables packets travel form one point
to another. Data may flow from a source to a processor and from data store or process. An arrow line depicts the flow, with arrow head pointing in the direction of the flow.
A process represents transformation where incoming data flows are changed into outgoing data flows.
A data store is a repository of data that is to be stored for use by a one or more process may be as simple as buffer or queue or sophisticated as relational database. They should have clear names. If a process merely uses the content of store and does not alter it, the arrowhead goes only from the store to the process. If a process alters the details in the store then a double -headed arrow is used.
A source or sink is a person or part of an organization which enter or receives information from the system but is considered to be the contest of data flow model.
System Level DFD
Administrator
fUser Y l Login j
Database
Enterprise
Manager
Create/Edit/
Delete
Create/Edit/ Delete Procedures
Databases
1
DATABASE
(MS-SQL Server 2000)
Administrator Level DFD
5.2. INPUT & OUTPUT DESIGN
The goal of designing input data is to make data entry as easy, logical, and free from errors as possible. In entering data users need to know the following:
> The allocated space for each field.
> Field sequence, which must match that in the source document.
> The format in which data fields are entered.
Collection of input data is the most expensive part of the system, in terms of both equipments used and the number of people involved. While designing the form, great care is to be taken to simplify the work of the person filling it, minimize the errors made and also to reduce the effort of the person reading the form.
Data enters the system as input and this is the data on which the process is performed. It is necessary to ensure that the input design is suitable. A well -designed input should serve the following purposes:
> To control work flow
> To reduce redundancies in recording data.
> To increase clerical accuracy.
> To allow easier checking of data.
> The input is the link that ties the system with its users.
In the system design phase the expanded data flow diagram identifies logical data flows, data sources, stores and destination. The objectives of input design must be effectiveness, accuracy, and ease of use, consistency and simplicity.
For many users output is the main reason to opt for a new system and on which they evaluate the usefulness of the system. Once the output requirements are determined the system designer can decide what to include in the system and how to structure it so that the required output can be produced.
The main input and output designs used in the project are given below:
Login form
The inputs given here are the username and password that are entered into the corresponding data fields. These two inputs are of much importance with regard to the security of the system.
The primary concept behind is that there are objects in the left pane of the tool in a tree format; that is, there are parents and children that logically belong together. For instance, the databases object has databases that are underneath it. If these objects and children are nouns (things), then verbs (actions) applying to those things are accessed by right-clicking them. The actions change based on the object; for example, the databases object has Create new database, while a particular database object has Backup database.
Creating a database
Right click on the databases node in the left pane and choose the New Database option. The create database dialog will appear:
t I i : t I X
fcyratwe
All we really need to do to create a new database is give it a name, so enter the name of the database in the name field and click the "Create" button.
Restore Database
This option is provided in the shortcut menus of "Databases" node and also for each database.
amss.
On clicking that the restore database dialog appears. Enter the database name. Select the backup which is to be restored and click OK.
Backup Database
This option is provided in the shortcut menus of "Databases" node and also for each database.
On clicking that the backup database dialog appears. Select the database of which the backup is to be created. Specify the location where the backup of the particular database is to be created and click OK.
[ Attach Database
| Right-click 'Databases' node and choose Attach Database from the shortcut ' menu. Select the database to be attached.
L
A message box prompts you to confirm the action. Choose OK.
Right-click 'Databases' node and choose Detach Database from the shortcut menu. Select the database to be detached. A message box prompts you to confirm the action. Choose OK.
Delete Database
Right-click 'Databases' node and choose Delete Database from the shortcut menu. Enter the name of the database to be deleted. A message box prompts you to confirm the deletion. Choose Yes.
Creating a Table
You can create a new table in the database using Table Design wizard, or using query.
To create a new table with Table Design Wizard
To create a new table with query
Table is created by using the "create table" query.
1. Make sure you are connected to the database in which you want to create the table. Right-click the Tables node of the target database and choose New Table.
2. Add the columns and other details of the table.
From the list of tables in a database, select the table you want to insert the data. Right-click the table and choose Return all Rows from the shortcut menu. Insert the data into each row of the table and save it.
Design Table
Right-click the table, and choose Design Table from the shortcut menu, to view the table design.
Right-click the table, and choose Return Top from the shortcut menu, to view the specified number of rows from the top.
Table Information
Right-click the table, and choose Table Information from the shortcut menu, to view the details about the table.
In your database, select the table you want to rename. Right-click the table, and choose Rename from the shortcut menu. In the Table name box, type a new name. Be sure to choose a name that does not duplicate one in the tables of the database list. Click Close.
Deleting a Table from a Database
o
From the list of tables in a database, select the table you want to delete. Right-click the table and choose Delete from the shortcut menu. A message box prompts you to confirm the deletion. Choose Yes.
Right-click the 'Views' node of the target database and choose New View. The view is created by executing the "create view" query.
Return all rows of the view
From the list of views in a database, select the view. Right-click the view and choose Return all Rows from the shortcut menu.
Right-click the view, and choose Return Top from the shortcut menu, to view the specified number of rows from the top.
View Information
Right-click the view, and choose View Information from the shortcut menu, to view the details about the view.
In your database, select the view you want to rename. Right-click the view, and choose Rename from the shortcut menu. In the View name box, type a new name. Be sure to choose a name that does not duplicate one in the views of the database list. Click Close.
Delete View
From the list of views in a database, select the view you want to delete. Right-click the view and choose Delete from the shortcut menu. A message box prompts you to confirm the deletion. Choose Yes.
Right-click the 'Stored Procedures' node of the target database and choose New Stored Procedure. The stored procedure is created using the "create proc" query.
Rename Stored Procedure
a i"ml"
In your database, select the stored procedure you want to rename. Right-click the stored procedure, and choose Rename from the shortcut menu. In the Stored Procedure name box, type a new name. Click Close.
From the list of stored procedures in a database, select the stored procedure you want to delete. Right-click the stored procedure and choose Delete from the shortcut menu. A message box prompts you to confirm the deletion. Choose Yes.
Execute Query
The Execute Query option is provided in the shortcut menus of Tables, Views and Stored Procedures. It gives the result after the execution of the query which is typed into the text field.
a ; * ! * 1 0 | X
Granting Access Permission to User
5.3. CODE DESIGN
The design must be translated in to a machine-readable form. The code generation step performs this task. If design is performed in a detailed manner, code generation can be accomplished mechanistically. It usually involves transformation from one representation to another called an intermediate form. A code model must be chosen as an intermediate form. Code generation is closely tied to the instruction scheduling policies used.
The common type of reengineering is code restructuring. Some legacy systems have relatively solid program architecture, but individual modules were coded in a way that makes them difficult to understand, test, and maintain. In such cases, the code within the suspect modulus can be restructured.
To accomplish this activity, the source code is analyzed using a restructuring tool. Violations of structured programming constructs are noted and code is then restructured. The resultant restructured code is reviewed and tested to ensure that no anomalies have been introduced.
OPTIMIZATION OF CODE
Compilation is a software technique, which transforms the source program to generate better object code, which can reduce the running time and memory requirement. Program optimization often demands an effort from both the programmer and the compiler. Code scheduling methods ensure that control dependence, data dependence, and resource limitations are properly handled during concurrent execution. The goal is to produce a code schedule that minimizes the execution times or the memory demand, in addition to the enforcing correctness of execution. Static scheduling at compile time requires intelligent compilation support, whereas dynamic scheduling is assisted by static scheduling in improving performance and reducing hardware costs. On the other hand, static scheduling is assisted by hardware interlocking in enforcing correctness of execution.
VALIDATION
Validation success when software functions in a manner that can be reasonably expected by the customer. Reasonable expectations are defined in the software Requirements specification. The specification contains a section called validation criteria. Information contained in that section forms the basis for a validation testing approach.
Validation Test Criteria
Software validation is achieved through a series of black-box tests that demonstrate conformity with requirements. A set plan outlines the classes of tests to be conducted and test procedure defines specific test classes that will be used to demonstrate conformity with requirements. A test plan outlines the classes of tests to be conducted and a test procedure defines specific test cases that will be used to demonstrate conformity with requirements. Both the plan and procedure are designed to ensure that all functional requirements are satisfied, all behavioral characteristics are achieved, all performance requirements are allured, documentation is correct and human engineered and other requirements are not i.e. transportability, compatibility, error recovery and maintainability.
After each validation test case has been conducted, one of the possible conditions exists. (1) The function or performance characteristics conform to specification and accepted or (2) a deviation from specification is uncovered and a deficiency list is created. In this project, there are three validation criteria, which are to be satisfied.
Configuration Review
This is an important element in validation process. The intent of the review is to ensure that all element of the software configuration have been properly developed.
Alpha and Beta Testing
When custom software is built for one customer, a series of acceptance tests are conducted to enable the customer to validate all requirements. If software is developed as a product to be used by many customers, it is impractical to perform formal acceptance test with each one. Most software product builders use a process called alpha and beta testing to uncover errors that only the end-user seems able to find.
A customer conducts the alpha test at the developer's site. The software is used on natural selling with the developer "looking over the shoulder" of the user and recording errors and users problems. Alpha tests are conducted in a controlled environment.
The beta test is conducted at one or more customer sites by the end-user of the software. The customer records all problems that are encountered during beta testing and reports these to the developer at regular intervals.
5.4. DATABASE DESIGN
The objectives of database design are to provide effective auxiliary storage and to contribute to the overall efficiency of the computer program component of the business information system. It can be defined as a representation of an information system in a computer. The general theme defined a database is to handle information as an integrated whole.
A database is a collection of interrelated data stored with minimum redundancy to serve many users quickly and efficiently. The general objective is to make information access easy, quick, inexpensive, and flexible for the user. In data base design, several specific objectives are considered:
¢ Controlled redundancy
¢ Ease of learning and use
¢ Data independence
¢ More information at low cost
¢ Accuracy and integrity
¢ Recovery from failure
¢ Privacy and security
¢ Performance
A database-management system (DBMS) consists of a collection of interrelated data and a set of programs to access those data. The collection of data usually referred to as the database contains information about the particular enterprise. The primary goal of a DBMS is to provide an environment that is both convenient and efficient to use in retrieving and storing database information. Database systems are designed to manage large bodies of information. The person who has the central control over the database management system is called database administrator (DBA).
Database Objectives
The organization of data in a database aims to achieve three major objectives: Data Integration, Data Integrity and Data Independence.
Data Integration
In the database, information from several files is co-ordinated, accessed and operated up on as through it is in a single file. Logically, the information is centralized; physically, the data may be located on different devices and in widely scattered geographical locations, connected through data communications is facilities. In order to achieve the objectives of data centralization, links between data must be maintained; direct-access techniques are used to permit efficient and flexible linking, although sequential organization can be used in a database.
Data Integrity
In order to avoid data inconsistency, all data must be stored only in one place and allow each application to access it. This is the database approach. This approach to data integrity results in more consistent information; one update being sufficient to achieve a new record status for all application, which uses it. This leads to less data redundancy: data items need not be duplicated.
Data Independence
Data independence is the insulation of application programs from the changing aspects of the physical data organization. This objective seeks to allow modifications to application programs without reorganizing the physical data.
The schema is the view that helps the DBMS decide what data in storage it should act upon as requested by the application program. The subschema is concerned with a relatively small part of schema. In data base design, several views of data must be considered along with the persons who use them. The logical view is what the data look like, regardless of how they are stored. The physical view is the way data exits in physical storage. It deals with how data are stored, accessed, or related to other data in storage. The logical views are the user's view the programmer's view and the overall, logical view, called a schema. The candidate key is non-redundant. That is, no proper subset of K has the above property. A field, which is a member of any candidate key, is called a prime field. Any field of an n-set that is not prime field will be referred to as a nonprime field. Every n-set has a primary key which is an arbitrary choice of one of the candidate keys of the n-set, It is usually a requirement that the primary key is fully defined or, in other words, that undefined value is not permitted in any of the component fields of the primary key.
Normalization is a formal process of developing data structures in a manner that eliminates redundancy and promotes integrity. The process of normalization is concerned with the transformation of the conceptual schema into a computer represent able form. There are four steps to data normalization, which are called the "normal forms"
1. The first normal form: elimination of repeating groups
DATABASE ENTERPRISE MANAGER
By
Aswathy.S Jyothy S Menon Seethu Sabu
ABSTRACT
The proposed Database Enterprise Manager is a windows application tool for Microsoft SQL Server 2000 and provides a user interface that allows users to:
> Create and administer all SQL Server databases in each registered server.
> Define and execute all SQL Server administrative tasks on each registered server.
> Using the Database Enterprise Manager, any user can easily create and manage databases in SQL server.
> Tables, Stored Procedures and Views can be created in each database.
Database Creation
A database in Microsoft SQL Server 2000 consists of a collection of tables that contain data and other objects, such as views, stored procedures etc, defined to support activities performed with the data. SQL Server can support many databases where the user can store the data in the relational model. The database enterprise manager facilitates the creation and manipulation of databases.
Table Creation
Tables are database objects that contain all the data in a database. A table definition is a collection of columns. Using the database enterprise manager the users can create the tables in Microsoft SQL Server. The user can give the table name and set the other properties of tables like table columns, table rows etc.
View Creation
A view is a virtual table whose contents are defined by a query. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.
Procedure Creation
Stored procedures in SQL Server can accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch. It can contain programming statements that perform operations in the database, including calling other procedures and it returns a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).
CONTENTS
SLNO TOPIC PAGE NO
1. Introduction 01
2. System Requirements 02
3. System Environment 04
3.1. The Microsoft.NET 04
3.2. Visual Basic.NET 19
3.3. Microsoft SQL Server2000 25
4. System Study 31
4.1. Existing system 31
4.2. Proposed System 32
4.3. Feasibility Analysis 33
5. System Design 35
5.1. Data Flow Diagram 37
5.2. Input and Output Design 41
5.3. Code Design 59
5.4. Database Design 62
5.5. Process Design 66
6. System Testing 71
7. Implementation and Maintenance 74
8. System Security Measures 77
9. Future scope and enhancement 80
10. Conclusion 81
11. References 82
LIST OF TABLES
Table No Description Page no.
1 .NET Enterprise Servers 06
2 Common Language Runtime Components 13
3 AdminLogin Table 65
4 UserPermission Table 65
5 UserRegistration Table 65
LIST OF FIGURES
Description Page no
The Microsoft .NET Platform . 4
Building Components in the .NET Framework 7
The components of the .NET Framework 11
Common Language Runtime 12
.NET Framework Class Library 14
ADO .NET: Data and XML 15
ASP .NET: Web Forms and Services 16
User Interface 18
SQL Server Enterprise Manager 27
Basic DFD symbols 38
Context Level DFD 39
System Level DFD 39
Administrator Level DFD 40
INTRODUCTION
Business today demands a different kind of data management solution. Performance, scalability, and reliability are essential, but businesses now expect more from their key IT investments. The ability to transform corporate data into meaningful and actionable information is the single most important source of competitive advantage in today's business world. Harnessing the data explosion to better understand the past and get direction for the future has turned out to be one of the most challenging ventures for enterprise Information Technology departments in global organizations.
SQL Server is commonly used by businesses for small- to medium-sized databases but the past five years have seen greater adoption of the product for larger enterprise databases. It delivers an entirely new set of management tools for both DBAs and developers.
The proposed Database Enterprise Manager is a windows application tool for Microsoft SQL Server that brings together a bunch of monitoring and management tools in a single interface .It allows users to create and administer all SQL Server databases in each registered server, define and execute all SQL Server administrative tasks on each registered server, provide user accounts, set access privileges.
PROCESSOR
PENTIUM
CLOCK SPEED
800 MHZ
SYSTEM BUS
32 BIT
RAM
128 MB
DD
5GB
MONITOR
SVGA COLOR
KEY BOARD
108 KEYS
MOUSE
LOGITECH
FDD
1.44 MB
Software Requirements
OPERATING SYSTEM
WINDOWS SERVER'OO, WINDOWS XP
ENVIRONMENT
MICROSOFT .NET
MICROSOFT .NET FRAME WORK MICROSOFT MOBILE EXPLORER
[.NET assemblies,
Windows Services,
ADO.NET]
FRONT END
VB.NET
BACKEND
MICROSOFT SQL SERVER 2000
Available Today
New
Capabilities
Orchestration |
Internet .NET Enterprise Servers Building
Block Services :
_ :
.NET
Framework COM+
Windows
With .NET Enhancements
Microsoft released the .Net framework in February 2002.lt is the biggest initiative since the launch of windows in 1991. .Net is a revolutionary Multilanguage platform that knits various aspects of application development together with the internet. The framework covers all layers of software development above the operating system. Several softwares will be developed by Microsoft to achieve this goal. It is accepted that every player in the industry, be it a software developer or a device manufacturer, adopt .Net so that they can be integrated.
The .NET Platform is a set of technologies designed to transform the Internet into a full-scale distributed computing platform. It provides new ways to build applications from collections of Web Services. The .NET Platform fully supports the existing Internet infrastructure, including HTTP, XML, and SOAP.
Core Technologies in the .NET Platform
.NET Framework
The .NET Framework is based on a new common language runtime. This runtime provides a common set of services for projects built in Visual Studio .NET, regardless of the language. These services provide key building blocks for applications of any type, across all application tiers.
Microsoft Visual Basic, Microsoft Visual C++, and other Microsoft programming languages have been enhanced to take advantage of these services. Third-party languages that are written for the .NET Platform also have access to the same services. Because of the common language runtime; all .NET languages will use the same run-time files. This means that there is no need to distribute Visual Basic-specific run-time libraries because .NET run-time files will be installed automatically in future versions of Microsoft Windows.
.NET Building Block Services
The .NET Building Block Services are distributed programmable services that are available both online and offline. A service can be invoked on a stand-alone computer not connected to the Internet, provided by a local server running inside a company, or accessed by means of the Internet. The .NET Building Block Services can be used from any platform that supports SOAP. Services include identity, notification and messaging, personalization, schematized storage, calendar, directory, search, and software delivery.
Visual Studio .NET
Visual Studio .NET provides a high-level development environment for building applications on the .NET Framework. It provides key enabling technologies to simplify the creation, deployment, and ongoing evolution of secure, scalable, highly available Web applications and Web Services. It also enables a new generation of Windows-based applications with many new features available through the .NET Framework.
.NET Enterprise Servers
The .NET Enterprise Servers provide scalability, reliability, management, and integration within and across organizations, and many other features as described in the following table 1.
I
Server Description
Microsoft SQL Server 2000 Includes rich XML functionality, support for Worldwide Web Consortium (W3C) standards, and the ability to manipulate XML data by using Transact SQL (T-SQL), flexible and powerful Web-based analysis, and secure access to your data over the Web by using HTTP.
Microsoft BizTalk Server 2000 Provides enterprise application integration (EAI), business-to-business integration, and the advanced BizTalk Orchestration technology to build dynamic business processes that span applications, platforms, and organizations over the Internet.
Microsoft Host Integration Server 2000 Provides the best way to embrace Internet, intranet, and client/server technologies while preserving investments in existing systems. Microsoft Host Integration Server 2000 is the replacement for SNA Server.
Microsoft Exchange 2000 Enterprise Server Builds on the powerful Exchange messaging and collaboration technology by introducing several important new features and further increasing the reliability, scalability, and performance of it's core architecture. Other features enhance the integration of Exchange 2000 with Microsoft Windows 2000, Microsoft Office 2000, and the Internet.
Microsoft Application Center 2000 Provides a deployment and management tool for high availability Web applications.
Microsoft Internet Security and Acceleration Server 2000 Provides secure, fast, and manageable Internet connectivity. Internet Security and Acceleration Server integrates an extensible, multilayer enterprise firewall and a scalable, high performance Web cache. It builds on Windows 2000 security and directory for policy-based security, acceleration, and management of internetworking.
Microsoft Commerce Server 2000 Provides an application framework, sophisticated feedback mechanisms, and analytical capabilities.
Overview of the .NET Framework
The .NET Framework provides all the common services required to run your Visual Basic .NET applications.
Building Components in the .NET Framework
Before COM, applications were completely separate entities with little or no integration. Using COM, you can integrate components within and across applications by exposing common interfaces. However, as a developer, you must still write the code to wrap, manage, and clean up after components and objects.
In the .NET Framework, components are built on a common foundation. You no longer need to write the code to allow objects to interact directly with each other. In the .NET environment, you no longer need to write component wrappers because components do not use wrappers. The .NET Framework can interpret the constructs that developers are accustomed to using in object oriented languages. The .NET Framework fully supports class, inheritance, methods, properties, events, polymorphism, constructors, and other object oriented constructs.
The Common Language Specification
The Common Language Specification (CLS) defines the common standards to which languages and developers must adhere if they want their components and applications to be widely useable by other .NET-compatible languages. The CLS allows Visual Basic .NET developers to create applications as part of a multiple-language team with the knowledge that there will be no problems integrating the different languages. The CLS even allows Visual Basic .NET developers to inherit from classes defined in different languages.
Visual Studio .NET
In the .NET Framework, Visual Studio .NET provides the tools you can use for rapid application development.
Goals of the .NET Framework
The .NET Framework was designed to meet the following goals:
¦ Based on Web standards and practices
The .NET Framework fully supports the existing Internet technologies; including Hypertext Markup Language (HTML), XML, SOAP, and Extensible Style sheet Language for Transformations (XSLT), Xpath, and other Web standards. The .NET Framework favors loosely connected, stateless Web services.
¦ Extensible
The hierarchy of the .NET Framework is not hidden from the developer. You can access and extend .NET classes (unless they are sealed) by using inheritance. You can also implement cross-language inheritance.
¦ Easy for developers to use
In the .NET Framework, code is organized into hierarchical namespaces and classes. The Framework provides a common type system, referred to as the unified type system that is used by any .NET-compatible language. In the unified type system, everything is an object. There are no variant types, there is only one string type, and all string data is Unicode.
¦ Designed using unified application models
A .NET class' functionality is available from any .NET-compatible language or programming model.
Languages in the .NET Framework
The .NET Platform is language neutral†all common services needed to enable programs to run are provided by the .NET Framework. The .NET Framework provides support for numerous programming languages, including Microsoft and third-party languages.
Microsoft Visual Basic .NET
Visual Basic .NET provides substantial language innovations over previous versions of Visual Basic. Visual Basic .NET supports inheritance, constructors, polymorphism, overloading, structured exceptions, stricter type checking, free threading, and many other features. With this release, Visual Basic Scripting Edition provides full Visual Basic functionality.
Microsoft Visual C#
C# was designed for the .NET Platform and is the first modern component-oriented language in the C and C++ family. It can be embedded in ASP .NET pages. Some of the key features of this language include classes, interfaces, delegates, boxing and unboxing, namespaces, properties, indexers, events, operator overloading, versioning, attributes, unsafe code, and XML documentation generation. No header or Interface Definition Language (IDL) files are needed.
Managed Extensions C++
The Visual C++ upgrade is a managed, minimal extension to the C++ language. This extension provides access to the .NET Framework that includes garbage collection, single-implementation inheritance, and multiple-interface inheritance. This upgrade also eliminates the need to write plumbing code for components. It offers low-level access where useful.
Microsoft JScript .NET
JScript .NET has been rewritten to be fully .NET compatible. It includes support for classes, inheritance, types, and compilation. It provides improved performance and productivity features. JScript .NET is also integrated with Visual Studio .NET. You can use of any .NET Framework class in JScript .NET.
Third-party languages
Various third-party languages are supporting the .NET Platform. These languages include APL, COBOL, Pascal, Eiffel, Haskell, ML, Oberon, Perl, Python, Scheme, and SmallTalk.
The .NET Framework Components
ASP NET: Web User
Forms & Services Interface
ADO .NET: Data & XML
.NET framework Claw Library
Common Language Runtime
The components of the .NET Framework are as follows:
¦ Common language runtime
¦ .NET Framework Class Library
¢ ADO .NET: data and XML
¦ ASP .NET: Web Forms and Services
¦ User interface
Common Language Runtime
" 1-
Thread ^pporl L COI $ Marshal er
-
w ..
Type Checker Exception Manager
mm >ug Engine
MSIL to Native [ Compilers Code Manager r
Garb Colle< age rtion
w â€â€ -* Class Loader |^ , __J
. The Common Language Runtime simplifies application development, provides a robust and secure execution environment, supports multiple languages, simplifies application deployment and management, and provides a managed environment.
A managed environment is one in which the environment provides common services automatically. Examples of the types of services a managed environment provides are garbage collection and security.
Common Language Runtime Components
Component Description
Class loader Loads the implementation of a loadable type into memory and prepares it for execution.
Microsoft intermediate language (MSIL) to native compiler Converts MSIL to native code (just-in-time).
Code manager Manages code execution.
Garbage collection Provides automatic lifetime management of all of your objects. This is a multiprocessor, scalable garbage collector.
Security engine Provides evidence-based security based on the origin of the code as well as the user.
Debug engine Allows you to debug your application and trace the execution of code.
Type checker Will not allow unsafe casts or uninitialized variables. IL can be verified to guarantee type safety
Exception manager Provides structured exception handling, which is integrated with Windows Structured Exception Handling (SEH). Error reporting has been improved.
Thread support Provides classes and interfaces that enable multithreaded programming.
COM marshaler Provides marshaling to and from COM.
.NET Framework Class Library support Integrates code with the runtime that supports the .NET Framework Class Library.
The .NET Framework Class Library exposes features of the runtime and provides other high-level services that every programmer needs through an object hierarchy. This object hierarchy is known as a namespace.
System Namespaces
The System namespace contains fundamental classes and base classes that define commonly-used value and reference data types, events and event handlers, interfaces, attributes, and processing exceptions. Other classes provide services supporting data type conversion, method parameter manipulation, mathematics, remote and local program invocation, application environment management, and supervision of managed and unmanaged applications. The System.Collections namespace provides sorted lists, hash tables, and other ways to group data. The System.10 namespace provides file I/O, streams, and so on. The System.NET namespace provides Transmission Control Protocol/Internet Protocol (TCP/IP) and sockets support.
ADO .NET is the next generation of ActiveX Data Object (ADO) technology. ADO .NET provides improved support for the disconnected programming model. It also provides rich XML support.
System.Data Namespace
The System.Data namespace consists of classes that constitute the ADO .NET object model. At a high level, the ADO .NET object model is divided into two layers: the connected layer and the disconnected layer. The System.Data namespace includes the DataSet class, which represents multiple tables and their relations. These data sets are completely self-contained data structures that can be populated from a variety of data sources. One data source could be XML, another could be OLE DB, and a third data source could be the direct adapter for SQL Server.
System.Xml Namespace
The System.Xml namespace provides support for XML. It includes an XML parser and a writer, which are both W3C compliant. Transformations are provided by the System.Xml.Xsl namespace and the implementation of Xpath that allows data graph navigation in XML. The System.XML.Serialization namespace provides the entire core infrastructure for Web Services, including features such as moving back and forth between objects and an XML representation.
ASP .NET: Web Forms and Services
ASP .NET is a programming framework that is built on the common .language runtime and that can be used on a server to build powerful Web implications. ASP .NET Web Forms provide an easy and powerful way to build dynamic Web user interfaces (Uls). ASP .NET Web Services provide the building blocks for constructing distributed Web-based applications. Web Services are based on open Internet standards, such as HTTP and XML. You can think of a Web Service as a reusable component that is accessible across the Internet, rather than being limited to Windows clients on a local area network.
The common language runtime provides built- in support for creating and exposing Web Services by using a programming abstraction that is consistent and familiar to both Active Server Pages (ASP) and Visual Basic developers. The resulting model is both scalable and extensible. This model is based on open Internet standards (HTTP, XML, SOAP, and SDL) so that it can be accessed and interpreted from any client or Internet-enabled device. Some of the more common ASP .NET classes are described in the following paragraphs.
System.Web Namespace
In the System.Web namespace, there are services such as caching, security, configuration, and others that are shared between Web Services and Web user interface (Ul).
System.Web.Services Namespace
The System.Web.Services namespace handles Web Service requirements such as transportation protocols and service discovery.
System.Web.UI Namespace
The System.Web.UI namespace provides two classes of controls: HTML controls and Web controls. The HTMLControls provide direct mapping of HTML tags, such as <INPUT>. There are also WebControls that are richer and allow you to structure controls with templates (for example, a grid control).
Windows applications can provide more powerful user interfaces than ever by using the NET Framework's System.Windows.Forms and System.Drawing namespaces. The new .NET Windows Forms will look very familiar to Visual Basic developers.
System.Windows.Forms Namespace
You can use the System.Windows.Forms namespace classes to build the client Ul. This class lets you implement the standard Windows Ul in your .NET applications. Many functions that were previously only accessible by means of application programming interface (API) calls are now available as part of the forms themselves, making development much easier and more powerful.
System.Drawing Namespace
The System.Drawing namespace provides access to GDI+ basic graphics functionality. More advanced functionality is provided in the System.Drawing.Drawing2D, System.Drawing.Imaging and System.Drawing.Text namespaces.
3.2. Visual Basic .NET
Earlier versions of Visual Basic were targeted for Microsoft Windows client applications, the Visual Basic .NET, next generation of the Visual Basic language, is a fast and easy way to create .NET-based applications, including XML Web Services and Web applications. Visual Basic .NET has many new and improved features that make it a powerful object-oriented programming language, including inheritance, interfaces, and overloading. Other new language features include free threading and structured exception handling.
f Visual Basic .NET also fully integrates the .NET Framework and the Common Language Runtime, which provide language interoperability, garbage collection, enhanced security, and improved versioning support. This also includes custom attributes, and common language specification (CLS) compliance.
The changes in Visual Basic .NET are intended to:
¢ Simplify the language and make it more consistent.
¢ Add new features requested by users.
¢ Make code easy to read and maintain.
I Help programmers avoid introducing coding errors.
¢ Make applications more robust and easier to debug.
Features of Visual Basic.NET CLS
The CLS is a set of rules that standardizes such things as data types and how objects are exposed and interoperate. Visual Basic .NET adds several features that take advantage of the CLS. Any CLS-compliant language can use the classes, objects, and components you create in Visual Basic .NET. And you, as a Visual Basic user, can access classes, components, and objects from other CLS-compliant programming languages without worrying about language-specific differences such as data types. CLS features used by Visual Basic .NET programs include assemblies, namespaces, and attributes.
Inheritance
Visual Basic .NET supports inheritance by allowing you to define classes that serve as the basis for derived classes. Derived classes inherit and can extend the properties and methods of the base class. They can also override inherited methods with new implementations. All classes created with Visual Basic .NET are inheritable by default. Because the forms you design are really classes, you can use inheritance to define new forms based on existing ones.
Exception Handling
Visual Basic .NET supports structured exception handling, using an enhanced version of the TRY...CATCH..FINALLY syntax supported by other languages such as C++. Structured exception handling combines a modern control structure (similar to Select Case or While) with exceptions, protected blocks of code, and filters. Structured exception handling makes it easy to create and maintain programs with robust, comprehensive error handlers
Overloading
Overloading is the ability to define properties, methods, or procedures that have the same name but use different data types. Overloaded procedures allow you to provide as many implementations as necessary to handle different kinds of data, while giving the appearance of a single, versatile procedure.
Overriding Properties and Methods
The Overrides keyword allows derived objects to override characteristics inherited from parent objects. Overridden members have the same arguments as the members inherited from the base class, but different implementations. A member's new implementation can call the original implementation in the parent class by preceding the member name with My Base.
Constructors and Destructors
Constructors are procedures that control initialization of new instances of a class. Conversely, destructors are methods that free system resources when a class leaves scope or is set to nothing. Visual Basic .NET supports constructors and destructors using the Sub New and Sub Finalize procedures.
Data Types
Visual Basic .NET introduces three new data types. The Char data type is an unsigned 16-bit quantity used to store Unicode characters. It is equivalent to the NET Framework System. Char data type. The Short data type, a signed 16-bit integer, was named Integer in earlier versions of Visual Basic. The Decimal data type is a 96-bit signed integer scaled by a variable power of 10. In earlier versions of Visual Basic, it was available only within a Variant.
Interfaces
Interfaces describe the properties and methods of classes, but unlike classes, do not provide implementations. The Interface statement allows you to declare interfaces, while the Implements statement lets you write code that puts the items described in the interface into practice.
References
References allow you to use objects defined in other assemblies. In Visual Basic .NET, references point to assemblies instead of type libraries.
Namespaces
Namespaces prevent naming conflicts by organizing classes, interfaces, and methods into hierarchies.
Assemblies
Assemblies replace and extend the capabilities of type libraries by, describing all the required files for a particular component or application. An assembly can contain one or more namespaces.
Attributes
Attributes enable you to provide additional information about program elements. For example, you can use an attribute to specify which methods in a class should be exposed when the class is used as a XML Web service.
Multithreading
Visual Basic .NET allows you to write applications that can perform multiple tasks independently. A task that has the potential of holding up other tasks can execute on a separate thread, a process known as multithreading. By causing complicated tasks to run on threads that are separate from your user interface, multithreading makes your applications more responsive to user input.
Visual Basic .NET Enhancements
Visual Basic .NET provides major language enhancements, full access to the .NET Framework, and enhanced Web development.
Major Language Enhancements
In Visual Basic .NET, Visual Basic has undergone some significant changes to allow Visual Basic developers to create powerful, robust, and scalable enterprise applications.
¢ Enhanced object-oriented support
Allows Visual Basic NET developers to use class inheritance, constructors, destructors, overloading, interfaces, and polymorphism. This gives Visual Basic .NET as much object-oriented power as any other .NET language, such as C# or Visual C++ with managed extensions.
¢ Structured exception handling
Simplifies exception handling, and allows you to use powerful features such as nested exceptions. This greatly improves the previous Visual Basic error handler.
Full Access to the .NET Framework
Visual Basic .NET developers have full access to the .NET Framework, including the entire .NET Framework Class Library.
¢ New threading options
Allow you to create applications that use multithreaded capabilities. For the first time, Visual Basic developers will be able to create applications that are capable of rivaling Visual C++-based applications in this area.
¢ Garbage collection
. Ensures that applications created in Visual Basic .NET do not hold onto unreferenced memory. This background process addresses memory-related issues such as memory leaks and circular references that existed in previous versions of Visual Basic and other languages.
Enhanced Web Development
Visual Basic .NET enables developers to quickly create powerful Web applications.
¢ Create Web Forms easily
You can do this as easily as Windows Forms by using the familiar technique of writing code behind the event of a control. No longer do developers need to use one integrated development environment (IDE) for Web page development (such as Microsoft Visual InterDev®) and another for component development. All aspects of a Web application are now simply created in a single environment in a way that will be easy for Visual Basic developers to understand.
¢ Create Web Services quickly
You can do this quickly in a fashion similar to creating components in previous versions of Visual Basic. Web Services is a powerful technology that allows you to access your components (or other people's components) across the Internet by means of HTTP. Any Visual Basic developer who has created components will easily make the transition to Web Services.
3.3. MICROSOFT SQL SERVER 2000
Microsoft SQL server 2000 is a relational database management system (RDBMS) that is used to store and organize related data. This is based on the client/server architecture in which data is stored on a centralized computer called server. Other computers called clients can access the data stored on the server through a network. SQL server 2000 is an advanced version of SQL server version 7.0.
Microsoft SQL Server2000 extends the performance, reliability, quality, and ease-of-use of Microsoft SQL Server version 7.0. Microsoft SQL Server 2000 includes several new features that make it an excellent database platform for large-scale online transactional processing (OLTP), data warehousing, and e-commerce applications.
The OLAP Services feature available in SQL Server version 7.0 is now called SQL Server 2000 Analysis Services. The term OLAP Services has been replaced with the term Analysis Services. Analysis Services also includes a new data-mining component.
The Repository component available in SQL Server version 7.0 is now called Microsoft SQL Server 2000 Meta Data Services. References to the component now use the term Meta Data Services. The term repository is used only in reference to the repository engine within Meta Data Services.
SQL Server Enterprise Manager
SQL Server Enterprise Manager is the Microsoft SQL Server 2000 MMC snap-in. SQL Server Enterprise Manager is the primary administrative tool for Microsoft SQL Server 2000 and provides a Microsoft Management Console (MMC)-compliant user interface that allows users to:
¢ Define groups of servers running SQL Server.
¢ Register individual servers in a group.
¢ Configure all SQL Server options for each registered server.
I ¢ Create and administer all SQL Server databases, objects, logins, users, and permissions in each registered server.
I ¢ Define and execute all SQL Server administrative tasks on each registered server.
, ¢ Design and test SQL statements, batches, and scripts interactively by invoking SQL Query Analyzer.
¢ Invoke the various wizards defined for SQL Server.
\ MMC is a tool that presents a common interface for managing different server applications in a Microsoft Windows network. Server applications provide a component called an MMC snap-in that presents MMC users with a user interface for managing the server application.
1 HE mui *» Toots window MAP
In a relational database such as Microsoft SQL Server 2000, all operations on the tables in the database produce a result in the form of a table. The result set of a SELECT statement is in the form of a table. Traditional client/server applications that execute a SELECT statement process the results by fetching one row or block of rows from the tabular result set at a time and mapping the column values into program variables. Web application programmers, on the other hand, are more familiar with working with hierarchical representations of data in XML or HTML documents.
The results of SELECT statements can be returned as XML documents. Complex queries, or queries that need be made secure, can be stored as templates in an IIS virtual root, and executed by referencing the template name.
: The data from an XML document can be exposed as a relational rowset using the new OPENXML row set function. This allows using the data in XML
t Depar
documents to insert, update, or delete data in the tables of the database, including modifying multiple rows in multiple tables in a single operation.
Accessing Data Stored In SQLSERVER 2000
Before one access data from an SQL server, one need to perform the following steps in a specified sequence.
¦
1. Establish a connection with SQL server.
2. Write the actual command to access or manipulate data.
3. Create a result set of the data from the data source with which the application can work. This result set is called data set and is disconnected from the actual source. The application accesses and updates data in the data set which is later reconciled with the actual data source.
Main Components of SQL Database
The main components of an SQL database are tables, views and stored procedures.
Tables
A SQL database stores data in tables which consists of rows and columns. A column stores information regarding properties of an item, while a row stores the complete information of an item. Each co\umn has a spec\f\c data type. Each table must have at least one column that uniquely identifies each row in the table. This column is called the primary key. Tables can be created, inserted, updated and deleted.
Views
A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a ;table is referenced.
A view is used to do any or all of these functions:
I ¢ Restrict a user to specific rows in a table. For example, allow an employee
' to see only the rows recording his or her work in a labor-tracking table. J ¢ Restrict a user to specific columns. For example, allow employees who do not work in payroll to see the name, office, work phone, and department columns in an employee table, but do not allow them to see any columns with salary information or personal information. ! ¢ Join columns from multiple tables so that they look like a single table. ¢ Aggregate information instead of supplying details. For example, present the sum of a column, or the maximum or minimum value from a column.
Views can also be created and deleted. But when a view is updated or inserted it will be looking in to the fact that only one table is affected by the operation.
Stored Procedures
A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. Microsoft SQL Server 2000 stored procedures return data in four ways:
1. Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time). I 2. Return codes, which are always an integer value.
3. A result set for each SELECT statement contained in the stored procedure or any other procedures called by the stored procedure.
4. A global cursor that can be referenced outside the stored procedure.
Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.
Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server. The IF statement in this example shows embedding conditional logic in a procedure to keep from sending a result set to the application. Stored Procedures can also be created, updated and deleted.
SYSTEM STUDY
The Enterprise Manager is a graphical interface tool used to administer your SQL Servers. You can use the Enterprise Manager to configure SQL Server options, ipreate/edit/view databases, perform maintenance and backups. The Administrator can also create tables, views and stored procedures, and modify them. By using the concept of user authentication he can provide more security to the databases. The administrator can create or delete users. Also he can register various users and grant them permissions to access the databases in MS-SQL Server.
4.1. EXISTING SYSTEM
The development of our project needed the study of the existing systems if any. The existing system requires more memory and a lot of file management. When running the Enterprise Edition of SQL Server 2000 on either the Windows 2000 Server or Windows 2000 Datacenter Server platforms, more than 4GB of imemory is required.
Also the administrator cannot authenticate the users and grant then permissions to access the databases. So security to the databases is not assured.
In a centralized system such as Bank, there will be one server and many clients. So the Administrator will have to install the SQL Enterprise manager in all clients, which leads to lot of memory wastage.
4.2. PROPOSED SYSTEM
Enterprise Manager is the graphical way to manage SQL Server, and with it you can easily access, edit the databases and manage users and security.
All databases in SQL are displayed in a tree structure. On selecting each database, corresponding tables, views and stored procedures are listed. He can modify each of them according to his needs using a Graphical User Interface System. Accesses to all the tables in the database along with their contents are easily accessible. From the database listing, you can also drill into views, stored procedures and run some basic SQL statements.
With the enterprise manager, we can do the following things:
¢ Configure all SQL Server options for each registered server.
¢ Create and administer all SQL Server databases, objects, logins, users, and permissions in each registered server.
¢ Define and execute all SQL Server administrative tasks on each registered server
¢ Manage users and security
I # Perform maintenance and backups of databases.
4.3. FEASIBILITY ANALYSIS
Feasibility analysis is the procedure for identifying the candidate system, evaluating and electing the most feasible system. This is done by investigating the existing system in the area under investigation or generally ideas about a new system. It is a test of a system proposal according to its workability, impact on the organization, ability to meet user needs, and effective use of resources. The objective of feasibility study is not to solve the problem but to acquire a sense of its scope. Feasibility analysis involves 8 steps:
1. Form a project team and appoint a project leader.
2. Prepare system flow charts.
3. Enumerate potential candidate system.
4. Describe and identify characteristics of candidate systems.
5. Determine and evaluate performance and cost effectiveness of each candidate system.
6. Weigh system performance and cost data.
7. Select the best candidate system.
8. Repair and report final project directive to management.
Three key considerations are involved in the feasibility analysis: economic, technical and behavioral.
Economic feasibility
Economic analysis is the most frequently used method for evaluating the effectiveness of a candidate system. It is more commonly known as cost benefit analysis, the procedure to determine the benefits and saving that are expected from a candidate system and compare them with costs. If the benefits outweigh costs then a decision is made to design and implement the [system. Otherwise make alterations in the proposed system.
Technical feasibility
The assessments of technical feasibility centers on the existing system and to what extent it can support the proposed addition. This was based on an outline design of system requirements in turns of inputs, files, programs, procedures, and staff. It involves financial considerations to accommodate technical enhancements.
Behavioral feasibility
People are inherently resistant to change, and computers have been known to facilitate change. An estimate should be made about the reaction if the user staff towards the development of a computerized system. Computer Installations have something to do with turnover, transfers and changes in job (status. The introduction of a candidate system requires special effort to educate, sell and train the staff for conducting the business.
The candidate system was found to be technically, economically, and behaviorally feasible. The system was developed user friendly, needless training and improves the working environment. Justification for any capital outlay is that it will increase profit, reduce expenditure or improve the quality of service or goods, which in turn may be expected to provide increased profits. Disregarding the initial expenses, the candidate system was assessed to be feasible in all ways.
SYSTEM DESIGN
System design refers to the technical specifications that will be applied in implementing the candidate system. This involves input/output, database, and processing design. System design is a solution, a how to approach to the creation of the new system. The system objectives outlined during the feasibility study serve as the basis from which the work of system design is initiated. Much of the activities involved at this stage are of technical nature requiring a certain degree of experience in designing systems, sound knowledge of computer related technology and through understanding of computers available in the [market and various facilities provided by the vendors.
Nevertheless, a system cannot be designed in isolation without the active involvement of the user. The user has a vital role to play at this stage too. As we know that data collected during feasibility study will be utilized systematically during the system design. It should, however, be kept in mind that detailed study of the existing system is not necessarily over with the completion of the feasibility study. Depending on the plan of feasibility study, the level of detailed study will vary and the system design stage will also vary in the amount of investigation that still needs to be done. This investigation is generally an urgent activity during the system design as the designer needs to study minute details in aspects of the system.
Designing a new system is a creative process, which calls for logical as well as lateral thinking. The logical approach involves systematically moves towards the end product keeping in mind the capabilities of the personnel and the equipment at each decision making step. Lateral thought implies encompassing of ideas beyond the usual functions and equipments. This is to ensure that no efforts are being made to fit previous solutions into new situations.
The real essence of a system design is devising a system that makes the processing requirements. The design of an information system produces the details that state how a system will need the requirements identified during the system analysis.
Design goes through the logical and physical states of development. Logical design reviews the present physical system; prepares the input-output specifications; makes edit; security and control specification; details of implementation plan and prepares a logical design path.
Logical design proceeds from the top-down. General features, such as reports and inputs are identified first. Then each studied individually and more detail. Hence, the structured design partitions a program into small, independent modules. They are arranged in a hierarchy that approximates a model of the business are and is organized in a top-down manner. Thus, structured design is an attempt to minimize the complexity and make a problem manageable by subdividing it into smaller segments, which is called modularization or decomposition. In this way structuring minimizes intuitive reasoning and promotes maintainable provable systems.
The physical design maps out the details of the physical system and plans the system implementation and specifies the hardware and software requirements.
5.1. DATA FLOW DIAGRAM
A Data Flow Diagram (DFD) or a Bubble chart is a network that describes the flow of data and processes that change, or transform data through out the system. This network is constructed by using a set of symbols that do not imply a physical implementation. It is a graphical tool for structured analysis of system requirements DFD models a system by using external entities from which data flows to a process, which transfer the data and creates, output-data-flows which go to other external entities or files.
There are various symbols used in a Data Flow Diagram. Bubbles represent the processes. Named arrows indicate the data flow. External entities are represented by rectangles and are outside the system such as vendors or customers with whom the system interacts. They either supply or consume data. Entities supplying data are known as sources and those that consume data are called sinks. Data are stored in a data store by a process in the system. Each component in a Data Flow Diagram is labelled with a descriptive name. Process names are further identified with a number.
Data Flow Diagram can be hierarchically organized, which help in partitioning and analyzing large systems. As a first step, one Data Flow Diagram can depict an entire system, which gives the system overview. It is called context Diagram of level 0 DFD. The Context Diagram can be further expanded. The successive expansion of a Data Flow Diagram from the context diagram to those giving more details is known as levelling of Data Flow Diagram. Thus a top down approach is used, starting with an overview and then working out the details.
The main merit of Data Flow Diagram is that it can provide an overview of system requirements, what data a system would process, what transformation of data are done, what files are used, and where the results flow.
Basic DFD symbols
â€> A data flow is a route, which enables packets travel form one point
to another. Data may flow from a source to a processor and from data store or process. An arrow line depicts the flow, with arrow head pointing in the direction of the flow.
A process represents transformation where incoming data flows are changed into outgoing data flows.
A data store is a repository of data that is to be stored for use by a one or more process may be as simple as buffer or queue or sophisticated as relational database. They should have clear names. If a process merely uses the content of store and does not alter it, the arrowhead goes only from the store to the process. If a process alters the details in the store then a double -headed arrow is used.
A source or sink is a person or part of an organization which enter or receives information from the system but is considered to be the contest of data flow model.
System Level DFD
Administrator
fUser Y l Login j
Database
Enterprise
Manager
Create/Edit/
Delete
Create/Edit/ Delete Procedures
Databases
1
DATABASE
(MS-SQL Server 2000)
Administrator Level DFD
5.2. INPUT & OUTPUT DESIGN
The goal of designing input data is to make data entry as easy, logical, and free from errors as possible. In entering data users need to know the following:
> The allocated space for each field.
> Field sequence, which must match that in the source document.
> The format in which data fields are entered.
Collection of input data is the most expensive part of the system, in terms of both equipments used and the number of people involved. While designing the form, great care is to be taken to simplify the work of the person filling it, minimize the errors made and also to reduce the effort of the person reading the form.
Data enters the system as input and this is the data on which the process is performed. It is necessary to ensure that the input design is suitable. A well -designed input should serve the following purposes:
> To control work flow
> To reduce redundancies in recording data.
> To increase clerical accuracy.
> To allow easier checking of data.
> The input is the link that ties the system with its users.
In the system design phase the expanded data flow diagram identifies logical data flows, data sources, stores and destination. The objectives of input design must be effectiveness, accuracy, and ease of use, consistency and simplicity.
For many users output is the main reason to opt for a new system and on which they evaluate the usefulness of the system. Once the output requirements are determined the system designer can decide what to include in the system and how to structure it so that the required output can be produced.
The main input and output designs used in the project are given below:
Login form
The inputs given here are the username and password that are entered into the corresponding data fields. These two inputs are of much importance with regard to the security of the system.
The primary concept behind is that there are objects in the left pane of the tool in a tree format; that is, there are parents and children that logically belong together. For instance, the databases object has databases that are underneath it. If these objects and children are nouns (things), then verbs (actions) applying to those things are accessed by right-clicking them. The actions change based on the object; for example, the databases object has Create new database, while a particular database object has Backup database.
Creating a database
Right click on the databases node in the left pane and choose the New Database option. The create database dialog will appear:
t I i : t I X
fcyratwe
All we really need to do to create a new database is give it a name, so enter the name of the database in the name field and click the "Create" button.
Restore Database
This option is provided in the shortcut menus of "Databases" node and also for each database.
amss.
On clicking that the restore database dialog appears. Enter the database name. Select the backup which is to be restored and click OK.
Backup Database
This option is provided in the shortcut menus of "Databases" node and also for each database.
On clicking that the backup database dialog appears. Select the database of which the backup is to be created. Specify the location where the backup of the particular database is to be created and click OK.
[ Attach Database
| Right-click 'Databases' node and choose Attach Database from the shortcut ' menu. Select the database to be attached.
L
A message box prompts you to confirm the action. Choose OK.
Right-click 'Databases' node and choose Detach Database from the shortcut menu. Select the database to be detached. A message box prompts you to confirm the action. Choose OK.
Delete Database
Right-click 'Databases' node and choose Delete Database from the shortcut menu. Enter the name of the database to be deleted. A message box prompts you to confirm the deletion. Choose Yes.
Creating a Table
You can create a new table in the database using Table Design wizard, or using query.
To create a new table with Table Design Wizard
To create a new table with query
Table is created by using the "create table" query.
1. Make sure you are connected to the database in which you want to create the table. Right-click the Tables node of the target database and choose New Table.
2. Add the columns and other details of the table.
From the list of tables in a database, select the table you want to insert the data. Right-click the table and choose Return all Rows from the shortcut menu. Insert the data into each row of the table and save it.
Design Table
Right-click the table, and choose Design Table from the shortcut menu, to view the table design.
Right-click the table, and choose Return Top from the shortcut menu, to view the specified number of rows from the top.
Table Information
Right-click the table, and choose Table Information from the shortcut menu, to view the details about the table.
In your database, select the table you want to rename. Right-click the table, and choose Rename from the shortcut menu. In the Table name box, type a new name. Be sure to choose a name that does not duplicate one in the tables of the database list. Click Close.
Deleting a Table from a Database
o
From the list of tables in a database, select the table you want to delete. Right-click the table and choose Delete from the shortcut menu. A message box prompts you to confirm the deletion. Choose Yes.
Right-click the 'Views' node of the target database and choose New View. The view is created by executing the "create view" query.
Return all rows of the view
From the list of views in a database, select the view. Right-click the view and choose Return all Rows from the shortcut menu.
Right-click the view, and choose Return Top from the shortcut menu, to view the specified number of rows from the top.
View Information
Right-click the view, and choose View Information from the shortcut menu, to view the details about the view.
In your database, select the view you want to rename. Right-click the view, and choose Rename from the shortcut menu. In the View name box, type a new name. Be sure to choose a name that does not duplicate one in the views of the database list. Click Close.
Delete View
From the list of views in a database, select the view you want to delete. Right-click the view and choose Delete from the shortcut menu. A message box prompts you to confirm the deletion. Choose Yes.
Right-click the 'Stored Procedures' node of the target database and choose New Stored Procedure. The stored procedure is created using the "create proc" query.
Rename Stored Procedure
a i"ml"
In your database, select the stored procedure you want to rename. Right-click the stored procedure, and choose Rename from the shortcut menu. In the Stored Procedure name box, type a new name. Click Close.
From the list of stored procedures in a database, select the stored procedure you want to delete. Right-click the stored procedure and choose Delete from the shortcut menu. A message box prompts you to confirm the deletion. Choose Yes.
Execute Query
The Execute Query option is provided in the shortcut menus of Tables, Views and Stored Procedures. It gives the result after the execution of the query which is typed into the text field.
a ; * ! * 1 0 | X
Granting Access Permission to User
5.3. CODE DESIGN
The design must be translated in to a machine-readable form. The code generation step performs this task. If design is performed in a detailed manner, code generation can be accomplished mechanistically. It usually involves transformation from one representation to another called an intermediate form. A code model must be chosen as an intermediate form. Code generation is closely tied to the instruction scheduling policies used.
The common type of reengineering is code restructuring. Some legacy systems have relatively solid program architecture, but individual modules were coded in a way that makes them difficult to understand, test, and maintain. In such cases, the code within the suspect modulus can be restructured.
To accomplish this activity, the source code is analyzed using a restructuring tool. Violations of structured programming constructs are noted and code is then restructured. The resultant restructured code is reviewed and tested to ensure that no anomalies have been introduced.
OPTIMIZATION OF CODE
Compilation is a software technique, which transforms the source program to generate better object code, which can reduce the running time and memory requirement. Program optimization often demands an effort from both the programmer and the compiler. Code scheduling methods ensure that control dependence, data dependence, and resource limitations are properly handled during concurrent execution. The goal is to produce a code schedule that minimizes the execution times or the memory demand, in addition to the enforcing correctness of execution. Static scheduling at compile time requires intelligent compilation support, whereas dynamic scheduling is assisted by static scheduling in improving performance and reducing hardware costs. On the other hand, static scheduling is assisted by hardware interlocking in enforcing correctness of execution.
VALIDATION
Validation success when software functions in a manner that can be reasonably expected by the customer. Reasonable expectations are defined in the software Requirements specification. The specification contains a section called validation criteria. Information contained in that section forms the basis for a validation testing approach.
Validation Test Criteria
Software validation is achieved through a series of black-box tests that demonstrate conformity with requirements. A set plan outlines the classes of tests to be conducted and test procedure defines specific test classes that will be used to demonstrate conformity with requirements. A test plan outlines the classes of tests to be conducted and a test procedure defines specific test cases that will be used to demonstrate conformity with requirements. Both the plan and procedure are designed to ensure that all functional requirements are satisfied, all behavioral characteristics are achieved, all performance requirements are allured, documentation is correct and human engineered and other requirements are not i.e. transportability, compatibility, error recovery and maintainability.
After each validation test case has been conducted, one of the possible conditions exists. (1) The function or performance characteristics conform to specification and accepted or (2) a deviation from specification is uncovered and a deficiency list is created. In this project, there are three validation criteria, which are to be satisfied.
Configuration Review
This is an important element in validation process. The intent of the review is to ensure that all element of the software configuration have been properly developed.
Alpha and Beta Testing
When custom software is built for one customer, a series of acceptance tests are conducted to enable the customer to validate all requirements. If software is developed as a product to be used by many customers, it is impractical to perform formal acceptance test with each one. Most software product builders use a process called alpha and beta testing to uncover errors that only the end-user seems able to find.
A customer conducts the alpha test at the developer's site. The software is used on natural selling with the developer "looking over the shoulder" of the user and recording errors and users problems. Alpha tests are conducted in a controlled environment.
The beta test is conducted at one or more customer sites by the end-user of the software. The customer records all problems that are encountered during beta testing and reports these to the developer at regular intervals.
5.4. DATABASE DESIGN
The objectives of database design are to provide effective auxiliary storage and to contribute to the overall efficiency of the computer program component of the business information system. It can be defined as a representation of an information system in a computer. The general theme defined a database is to handle information as an integrated whole.
A database is a collection of interrelated data stored with minimum redundancy to serve many users quickly and efficiently. The general objective is to make information access easy, quick, inexpensive, and flexible for the user. In data base design, several specific objectives are considered:
¢ Controlled redundancy
¢ Ease of learning and use
¢ Data independence
¢ More information at low cost
¢ Accuracy and integrity
¢ Recovery from failure
¢ Privacy and security
¢ Performance
A database-management system (DBMS) consists of a collection of interrelated data and a set of programs to access those data. The collection of data usually referred to as the database contains information about the particular enterprise. The primary goal of a DBMS is to provide an environment that is both convenient and efficient to use in retrieving and storing database information. Database systems are designed to manage large bodies of information. The person who has the central control over the database management system is called database administrator (DBA).
Database Objectives
The organization of data in a database aims to achieve three major objectives: Data Integration, Data Integrity and Data Independence.
Data Integration
In the database, information from several files is co-ordinated, accessed and operated up on as through it is in a single file. Logically, the information is centralized; physically, the data may be located on different devices and in widely scattered geographical locations, connected through data communications is facilities. In order to achieve the objectives of data centralization, links between data must be maintained; direct-access techniques are used to permit efficient and flexible linking, although sequential organization can be used in a database.
Data Integrity
In order to avoid data inconsistency, all data must be stored only in one place and allow each application to access it. This is the database approach. This approach to data integrity results in more consistent information; one update being sufficient to achieve a new record status for all application, which uses it. This leads to less data redundancy: data items need not be duplicated.
Data Independence
Data independence is the insulation of application programs from the changing aspects of the physical data organization. This objective seeks to allow modifications to application programs without reorganizing the physical data.
The schema is the view that helps the DBMS decide what data in storage it should act upon as requested by the application program. The subschema is concerned with a relatively small part of schema. In data base design, several views of data must be considered along with the persons who use them. The logical view is what the data look like, regardless of how they are stored. The physical view is the way data exits in physical storage. It deals with how data are stored, accessed, or related to other data in storage. The logical views are the user's view the programmer's view and the overall, logical view, called a schema. The candidate key is non-redundant. That is, no proper subset of K has the above property. A field, which is a member of any candidate key, is called a prime field. Any field of an n-set that is not prime field will be referred to as a nonprime field. Every n-set has a primary key which is an arbitrary choice of one of the candidate keys of the n-set, It is usually a requirement that the primary key is fully defined or, in other words, that undefined value is not permitted in any of the component fields of the primary key.
Normalization is a formal process of developing data structures in a manner that eliminates redundancy and promotes integrity. The process of normalization is concerned with the transformation of the conceptual schema into a computer represent able form. There are four steps to data normalization, which are called the "normal forms"
1. The first normal form: elimination of repeating groups