4.
IMPLEMENTATION AND RESULTS
4.1 Introduction
Implementation involves the construction of a database according to the
specification of a logical schema. This will include the specification of an
appropriate storage schema, security enforcement, external schema and so on.
Implementation is influenced by the choice of available DBMSs, database tools
and operating environment. There are additional tasks beyond simply creating a
database schema and implementing the constraints such as data must be entered
into the tables, issues relating to the users and user processes need to be
addressed, and the management activities associated with wider aspects of
corporate data management need to be supported. In practice, implementation
of the logical schema in a given DBMS requires a very detailed knowledge of the
specific features and facilities that the DBMS has to offer. In an ideal world, and in
keeping with good software engineering practice, the first stage of
implementation would involve matching the design requirements with the best
available implementing tools and then using those tools for the implementation.
In database terms, this might involve choosing vendor products with DBMS and
SQL variants most suited to the database which is to be implemented. There are
many relational DBMSs, available such as Oracle Database, Microsoft SQL Server ,
MySQL, IBM DB2, IBM Informix and Microsoft Access, use SQL. In this project we
used Oracle SQL developer create the following tables of online shopping
management database.
4.2 Database Tables
Following tables table 4.1 to table 4.6 are the tables created for the schema
diagram shown in figure 3.2.
Table 4.1: Login table description
Attribute Type Constraints
login_id integer Primary key
username Character(20) Not NUll
password Character(20) Not NUll
Table 4.2: user table description
Attribute Type Constraints
Uid integer Primary key
fname Character(20) Not NUll
lname Character(20) Not NUll
email Character(20) Not NUll
address Character(20) Not NUll
Ph_no integer Not NUll
Login_id integer Foreign Key reference to login
Table 4.3: products table description
Attribute Type Constraints
Pid integer Primary key
quantity integer Not NUll
pname Character(20) Not NUll
amount integer Not NUll
category Character(20) Not NUll
color Character(20) Not NUll
Table 4.4: cart table description
Attribute Type Constraints
cid integer Primary key
Table 4.5: Adds table description
Attribute Type Constraints
A-id integer Not Null
Uid integer
Pid integer
cid integer
Table 4.6: Bill table description
Attribute Type Constraints
Bill_no integer Primar key
Delivery_date Date Not Null
Billing_date Date Not Null
Delivery_address Character(20) Not Null
cid integer Foreign Key reference to cart
Table 4.7: Payment table description
Attribute Type Constraints
Pay_id int Primary key
Card_type Character(20) Not Null
Pay_type Character(20) Not Null
Bill_no integer Foreign key refers to Bill
Following syntax shows for creating database table shown with an example for
the Table 4.1
Database Creation:
USE itdepot;
CREATE TABLE IF NOT EXISTS user (
user_id number primary key,
fname varchar(20), Not Null,
lname varchar(20), Not Null,
address varchar(50), Not Null,
email varchar(50), Not Null,
phone_no number(10), Not Null,
FOREIGN KEY (login_id) REFERENCES login(login_id) );
4.3 RESULTS