CC6001 Advanced Database Systems Development Report - Oracle Database

This report details the development of an e-commerce website using ASP.NET and Oracle database. The project implements features such as user registration, product viewing, and order management for both users and staff. It covers non-functional and security requirements, emphasizing efficiency and maintainability through normalized tables and an Entity-Relationship (E-R) model. The website utilizes a three-tier architecture with a backend Oracle database, a middle tier using Microsoft IIS, and a client-side interface. The report describes the technologies used, including ASP.NET, C#, and VB.NET, and explains the role of IIS in handling requests. It also includes DDL scripts for database creation, a user manual outlining registration, login, order placement, and staff management procedures, along with test results. The report concludes with reflections on database transactions and the application of ASP.NET's features for building a dynamic, database-driven web application.

Running head: CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
CC6001 Advanced Database Systems Development
Name of the Student
Name of the University
Authors note

1CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
Table of Contents
Introduction..........................................................................................................................2
Features Implemented..........................................................................................................2
Deployment and Technology used......................................................................................3
Features of the ASP.NET....................................................................................................4
DDL Script...........................................................................................................................6
User manual.........................................................................................................................7
Registration..........................................................................................................................7
Login....................................................................................................................................7
Placing an Order..................................................................................................................7
Managing the Users and Order by staff...............................................................................8
Conclusion...........................................................................................................................8

2CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
Introduction
In this era of changing environment of business, it is very essential to respond to the
requirement of the clients and customers in a manner that is timely and effective. If the clients of
an organization want to view the business online and have an instant admittance to the services
and products of the company, then it is required to do so.
In order to create a website of E-Commerce, many technologies need to be studied and
understood. These consists of server and client-side scripting technologies, architecture those are
multi-tier and the implementation technologies such ASP.NET, relational databases and the
programming languages such as C#.
Features Implemented
Any member of the website can easily register and then view the products those are
available.
 Only the customers those are registered can check out with the products irrespective of the
quantity of products.
 The Contact Us page is integrated with the website for the users so that the users contact the
admin/organization if the members customers are having any queries
 There are basically two roles available on the website which is staff and user.
 The visitor can view the products those are available in the website
 The user can do both the activities that is to see and then purchase the products
 The function of the admin is to add products, edit the information of the products and
remove/add the products

3CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
 The staff can add , edit, remove, update view user and products details in the database that
are depicted on the website
Non-Functional and Security Requirements
 The website pages must be navigated in a way they are meant to be accessed by the different
users such as staff and member. The consisting files that should not be accessed outside the
scope of the parent.
 The admin can execute the task of administration on certain pages that they have the
privilege to access. The clients or the customers are not permitted to enter the pages of
administration.
Efficiency and Maintainability
 The loads of the pages should be formatted and returned in a manner that is timely depending
upon the request that is being made
 The administrators will have the capability to edit the aspects of the forms of the orders,
descriptions of the products, prices and directly the websites.
Normalized Tables for the Application
Member (id, name , last-name, address, mail, phone)
Product(ID, name, price, description, stock)
Order(ID, customerID, Quantity, payment amount)
Quotation (ID, Name, value, customerID, DateofQuotation)
Staff (StaffID, Name, address, Contact)

4CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
E-R Model

5CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
Data Dictionary
Column Name Data Type Nullable Default Primary Key
ORDERID NUMBER No - 1
CUSTOMERID NUMBER Yes - -
ORDERQUANTITY NUMBER Yes - -
PRODUCTID NUMBER Yes - -
PAYMENT NUMBER Yes - -
Column Name Data Type Nullable
Defaul
t Primary Key
ID VARCHAR2(10) No - 1
FIRSTNAME VARCHAR2(80) Yes - -
LASTNAME VARCHAR2(80) Yes - -
ADDRESS VARCHAR2(120) Yes - -
EMAIL VARCHAR2(50) Yes - -
PHONE VARCHAR2(20) Yes - -

6CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
Column Name Data Type
Nullabl
e Default Primary Key
ID NUMBER(10,0) No - 1
PRICE NUMBER(10,0) Yes - -
PRODUCTNAME VARCHAR2(150) Yes - -
DESCRIPTION VARCHAR2(200) Yes - -
STOCK NUMBER(12,0) Yes - -
Column Name
Data
Type Nullable Default Primary Key
QUOTATIONID NUMBER No - 1
QUOTEDATE DATE Yes - -
VALUE NUMBER Yes - -
PRODUCTID NUMBER Yes - -
STAFFID NUMBER Yes - -
Deployment and Technology used
The website the user with a catalog of various drawing items and art materials such as
canvas, brush those are available for buying in the store. In order make the online ordering
process easier one for the staff and members there are grid view tables are provided. The system
is deployed in an approach that is 3 tiers, with a database running in the backend, the middle

7CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
consists of the Microsoft IIS (Internet Information Services) and web pages are running at client
end.
The technologies that are used in the project consist of the architectures those are multi-
tier, scripting methods of the server and client side, wite the programming languages and
platforms such as ASP.NET, C#, VB.NET. The used back end database is the Oracle. Aim of
this under taken project is to develop web application that makes the sales and ordering process
of the art work materials from the Islington shop. After the web application is deployed when the
users enter URL of the website in a browser, a server is requested in order to retrieve the
information that is requested. For the locally deployed website with the .NET, the IIS or the
Internet Information Service. The IIS is responsible for responding against the requested for the
home page of the site. primary responsibility of the web server is to accept the requests of HTTP
that is incoming and then return the assets those are requested in a response of HTTP. The main
thing that the Internet Information Service does when a request is incoming is to decide the way
the request can be handled.
The decision about the response against the request is mainly dependent on the
extension of file which is requested from the server. In case of using the ASP.NET platform
the possible file formats are .aspx, .ascx. Therefore, if the requested file has an extension .asp,
then the IIS (Internet Information Services) will be responsible for re-routing the request and
consequently will handled by asp.dll. The acceptable extensions are .ascx, .aspx, and others.
After the request is received then it will route the request to be handled by the engine of
ASP.NET.

8CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
Screen dumps
Add Delete or update Customers and their Orders
Order and quotation management page for staff

9CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
Features of the ASP.NET
ASP.NET is a platform that supports multiple tools and languages development
platform which is utilized on a server to assemble and execute the Web applications. Following
are some of the main features that are mainly beneficial while deploying a dynamic database
driven web application. Some of the features are discussed below;
Improved response time for Database enabled Websites: ASP.NET allows the
developers in order to develop fast and responsive websites that interact and carry out create,
read, update delete operation in tables of a database. The main feature of ASP.NET platform is, it
supports object-oriented approach and provides numerous programming tools, which take into
account quicker and faster development, deployment of the applications.
Faster and Better Accessibility: In case of ASP.NET, the platform makes the
development faster it easier are caching of the data and compiled code of the application. While
using the ASP.NET the application code is in turn compiled in the executable machine language
in order to make the execution faster and provide faster accessibility to the users.

10CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
Better memory management and improved security to Memory leak and sudden
application crash: ASP.NET consequently improves performance related to the application
memory leak issues and application crash in order to ensure that the web application is
constantly accessible for the guests. Furthermore, the ASP.NET platforms supports source codes
written in multiple .NET supported programing languages such as C#, F#, VB.NET.
Improved interaction with Database: ASP.NET makes use of the ADO.NET in
order to interact with database at the back end. The reason behind can be stated as ADO.NET
allows in-memory caching of the frequently made transactions. In this way it removes the
unnecessary interaction to the database tables to the database server most of the time and it can
without much of a stretch convey also, keep up an ASP.NET application. Oracle (11g version) is
used as database for the application since it is one of the most well-known and efficient
databases.
DDL Script
CREATE TABLE customer (
ID int,
LastName varchar(100),
FirstName varchar(100),
Address varchar(50),
City varchar(75),

11CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
Contact varchar(20)
);
CREATE TABLE Product(
ID int,
Name varchar(100),
description varchar(255),
Address varchar(255),
City varchar(255)
);
CREATE TABLE Order(
ID int,
customerID varchar(100),
ProductID varchar(255),
Count number,
Total value number
);
User manual
Viewing the Products

12CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
The data about the artwork materials is put away in product table. The members can view
detailed description of the product, title, vendor of the products, number of stocks available, cost
of the product.
Registration
New customers can register or sign up on the site by tapping on the register button on the
menu at the highest point of the page. The "*" adjacent to the mark demonstrates the necessary
field for fruitful enlistment on the site. In the event that the worth if not entered, a suitable
message is shown. In the event that a client with same UserID as of now exists, the message is
shown.
Login
After the registration the users will be able to login with the valid user ID/email and
password on the login page.
Placing an Order
At the point when a staff makes send a quote to the customer for a product is clicked it
is added database table from the order page GridView. On the off chance that that specific
product is as of now not present in the stock an error prompt is generated. All the data for the
order is put away in "OrderdItems" table. Along these lines, putting the products in the shopping
basket doesn't ensure the accessibility of the products at the hour of submitting the request.
Managing the Users and Order by staff
The staffs will be able to create, update, retrieve, delete sales record after logging in to
the application. Furthermore, the staff will be able to add record for new items added to the
stock and update the current status.
Testing and results
TestID Details Expected output Actual output
#1 The users care
capable of registering
and login to their
Successful login to
account.
Login success

13CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT
account with correct
credentials.
#2 Staff can update the
product stock using
the table on web age
Data added and
database table
refreshed.
Successful record
updated.
#3 Staff can delete stock
records using the
table
Data updated in the
database.
Successfully the
stock count is
reduced.
#4 Staffs are able to
store the quotation
value from the web
page which will
reflect in the
database.
Quotation and
concerned customer
ID stored in database.
Successfully added to
the records in
quotation table.
Conclusion and Reflection
While developing the application it is found that the database transactions are ordinarily
managed by specifying the limits in order maintain the consistent state while carrying out a lot of
operations. In ASP.NET, the data transaction limit is imposed on the execution of a single page
request from the website. The request may contain nested segments that take an interest in a
single transaction to the application or server. While the page is executing, if a procedure on the
page itself or a nested segment in a similar transaction comes up then it is efficiently handled by
the ContextUtil. SetAbort method call at the mean time. This is at that point got by the present
transaction setting, the whole transaction from the application to database is reversed and
undone.

14CC6001 ADVANCED DATABASE SYSTEMS DEVELOPMENT