Essay: Database of Online-ordering system

Essay details:

  • Subject area(s): Computer science essays
  • Reading time: 8 minutes
  • Price: Free download
  • Published on: July 14, 2019
  • File format: Text
  • Number of pages: 2
  • Database of Online-ordering system Overall rating: 0 out of 5 based on 0 reviews.

Text preview of this essay:

This page of the essay has 1547 words. Download the full version above.

This database is designed for the online ordering system.
Auditing is on to recording who delete the table, also views and VPD are used as security reason. Cold backup and hot backup to protect the data. Some table using partitioning to provide a better performance of backup and data-read. There are also use XML and multiplexing redo logfile.
Scenario
Traditionally people going to the restaurant to have food or order food via phone, as the internet widely used, people prefer to ordering food online, which have pictures, comment, and price on it, especially for lunch as they don’t have too much time to wait and it also has a great range of choice for them.
Our customer is both restaurant and their customers. We provide a platform, which allows restaurant manager to modify their foods, and allows customers to make orders.
Logical design
E-R diagram
E-R diagram is refer to entity-relationship model, shows the relationships of entity.

Database Tables
Loc
Loc table is used to save the location information for customers and shops.
CREATE TABLE LOC(
STREET_NUMBER VARCHAR2(20) NOT NULL,
STREET_NAME VARCHAR2(50) NOT NULL,
SUBURB VARCHAR2(50) NOT NULL,
CITY VARCHAR2(50) NOT NULL,
ID NUMBER(38) NOT NULL PRIMARY KEY
);
Shop
Shop is used to save the information of the shops. The “DES” key allows shops to put more information to attract customers.
CREATE TABLE SHOP(
NAME VARCHAR2(50) NOT NULL,
MOBILE VARCHAR2(50) NOT NULL,
DES VARCHAR2(50),
LOC_ID REFERENCES LOC(ID),
ID NUMBER(38) NOT NULL PRIMARY KEY
);
Customer
Customer contains name, mobile, email and location id.
CREATE TABLE CUSTOMER(
NAME VARCHAR2(50) NOT NULL,
MOBILE VARCHAR2(50) NOT NULL,
EMAIL VARCHAR2(50) NOT NULL,
LOC_ID REFERENCES LOC(ID),
ID NUMBER(38) NOT NULL PRIMARY KEY
);
FOOD
Each shop can have different kinds of food, and it has a note key, which used to describe the food.
CREATE TABLE FOOD(
NAME VARCHAR2(50) NOT NULL,
SHOP_ID REFERENCES SHOP(ID) NOT NULL,
PRICE NUMBER(38,2) NOT NULL,
NOTE VARCHAR2(1000),
ID NUMBER(38) NOT NULL PRIMARY KEY
);
MYORDER
Myorder is going to save the users’ ordering history. It has the amount of the order and the time of order.
CREATE TABLE MYORDER(
CUSTOMER_ID REFERENCES CUSTOMER(ID) NOT NULL,
ORDER_TIMESTAMP TIMESTAMP NOT NULL,
AMOUNT NUMBER(38,2) NOT NULL,
NOTE VARCHAR2(1000),
ID NUMBER(38) NOT NULL PRIMARY KEY
);
ORDER_FOOD_REF
ORDER_FOOD_REF assist customer to get their order history.
CREATE TABLE ORDER_FOOD_REF (
ORDER_ID REFERENCES CUSTOMER(ID) NOT NULL,
FOOD_ID REFERENCES FOOD(ID) NOT NULL,
AMOUNT NUMBER(38,2) NOT NULL,
NOTE VARCHAR2(1000),
QUANTITY NUMBER(38) DEFAULT 1,
ID NUMBER(38) NOT NULL PRIMARY KEY
);
Requirements
System Requirements
CPU Intel(R) Core(TM) i7-4770 CPU @ 3.40GHz
Memory 8GB
Hard Disk 1TB
Windows 7 x64 Professional
VMware® Workstation 14 Pro
RDBMS Requirements
Oracle Database 11g release 2
Oracle Database 11g is an entry-level, small-footprint database based on the Oracle Database 11g Release 2 code base. It’s free to develop, deploy, and distribute; fast to download; and simple to administer.
Hard Disk 1TB
Oracle SQL Developer Version 17.4.1.054
Oracle SQL Developer is a free, integrated development environment that simplifies the development and management of Oracle Database in both traditional and Cloud deployments. (SQL Developer. n.d.).
Installation
Installation is following the instruction, but this demo is use Desktop Class, as This installation class is appropriate for laptop or desktop computers, It also includes a starter database and requires minimal configuration.

SID – The Oracle System ID, is used to uniquely identify a particular database on a system.
Connections
There are different ways to connect to oracle database. We can use ASP, JAVA, Python, etc and also we can use the way below.
Use SQLPlus
SQLPlus is a command tool that is installed with Oracle Database installation, it is a component of Oracle Database. You can find from “Start menu – all programs – oracle – application development – SQL Plus”.
if you want to connect as sys, you should add “as sysdba” after username “sys”, as above.
SQL Developer is a free and integrated development environment for Oracle Database, which you can download from here.

SID
System ID, is used to uniquely identify a particular database on a system, as we can consider it as instance name.
Port
port 1521 is Oracle database default listener.
Connection Type
“basic” is used when connect to a local or remote Oracle Database instance.
Role
set to default unless you want to connect as “sys”
User, Roles and Privileges
Roles
A role is a group of privileges, ”role” can be granted to users or revoked from users.
The role is used as an efficient way to manage user’s privileges, and it is simple for DBA to understand what the user’s privileges by the appropriate name.
There is two type of privileges, system privileges, and object privileges.
Role
Description
MYADMIN
MYADMIN is an admin role, like Chief Programmer or Project manager, has privileges with manipulating tables, views, session, index, sequence, etc.
MYOPER
Role of MYOPER is for developers, who can connect to the database, manipulate their own table, views, etc.
MYGUEST
MYGUEST is for limited users, except connect to the database, who only can “select” on specific tables.

Role
Privileges
MYADMIN
GRANT
ALTER ANY INDEX,
ALTER ANY SEQUENCE,
ALTER ANY PROCEDURE,
ALTER ANY TABLE,
CREATE ANY INDEX,
CREATE ANY PROCEDURE,
CREATE ANY SEQUENCE,
CREATE ANY SYNONYM,
CREATE SEQUENCE,
CREATE SESSION,
CREATE TABLE,
CREATE VIEW,
DELETE ANY TABLE,
DROP ANY INDEX,
DROP ANY PROCEDURE,
DROP ANY SEQUENCE,
DROP ANY SYNONYM,
DROP ANY VIEW,
INSERT ANY TABLE,
SELECT ANY SEQUENCE,
SELECT ANY TABLE,
UPDATE ANY TABLE
To
MYADMIN
MYOPER
GRANT
ALTER ANY INDEX,
ALTER ANY SEQUENCE,
ALTER ANY PROCEDURE,
ALTER ANY TABLE,
CREATE ANY INDEX,
CREATE ANY PROCEDURE,
CREATE ANY SEQUENCE,
CREATE ANY SYNONYM,
CREATE SEQUENCE,
CREATE SESSION,
CREATE TABLE,
CREATE VIEW,
DROP ANY INDEX,
DROP ANY PROCEDURE,
DROP ANY SEQUENCE,
DROP ANY SYNONYM,
DROP ANY VIEW,
INSERT ANY TABLE,
SELECT ANY SEQUENCE,
SELECT ANY TABLE,
UPDATE ANY TABLE
To
MYOPER

MYGUEST
GRANT
CREATE SESSION,
SELECT on SYSTEM.FOOD
To
MYGUEST

Users
User is someone who can connect to a database (if privileges granted).
Privileges can be granted from role or granted directly
In this database system, we have 3 users, Admin_dianyi, Oper_dianyi and guest_dianyi.
Admin_dianyi, is act as
User
Privis
admin_dianyi
GRANT MYADMIN TO ADMIN_DIANYI
oper_dianyi
GRANT MYOPER TO OPER_DIANYI
guest_dianyi
GRANT MYGUEST TO GUEST_DIANYI

Profiles
Profile is a collection of limits on the database of resources and password.
As the guest user only have limited privileges, their password can last for a year, and the sessions are limited to 2, etc.

Password_life_time 365
Password can be used for 365 days.
Password_lock_time 1/24
lock the account 1/24 day after the failed login attempts is met
Password_reuse_time 365
365 days before which a password cannot be reused
Password_reuse_max 10
10 password changes required before the current password can be reused.
Failed_login_attemptes 5
In this case If someone attempt 5 times to login and failed, this account is going to be lock for 1/24 days
Session_per_user 2
One user max have 2 connection to database
Idle_time 5
5 mins idle state Oracle automatically terminate connections.
Security
Audit
Auditing is the monitoring and recording the actions of the selected user. Security policies can trigger auditing when specified elements in an Oracle database are accessed or altered, including the contents of a specified object.
Steps:
Check auditing state. show parameter audit
There are no “Drop table” option so I use audit table by oper_dianyi.
Connect by oper_dianyi to create and drop table.
Back to system account to check the record from dba_audit_trail table.
Backup
Cold backup:
The cold backup is a physical backup. Shutdown the database is required and also database should run in NOARCHIVELOG mode.
If you do the backup without shutting the database down, the files are unavailable when you do recovery.

The files below should backup.
All datafiles
All control files:
The control files of a database store the status of the physical structure of the database. The control files contains Database information, Archive log history, Tablespace and datafile records, Database ID, etc. (Database Administrator’s Guide. (2008, March 13)).
All online redo log files.
The init.ora file, config.ora files.

Making a cold backup:

Save all the files above.
Connect database as sys user.
Shutdown the existing database. shutdown immediate;
Backup datafiles, control files, online redo log files and init.ora file.
Place files to somewhere safe.
Hot backup:
Hot backup, also known as dynamic backup, is a backup performed on data while the database is actively online and accessible to users.
database should in ARCHIVELOG mode. (Jethwa, J. (2017, July 25). Oracle 11g Manual Online Hot Backup.)

Steps to turn archivelog mode on,
Connect database as sys user
Shutdown database: shutdown immediate;
Startup database in mount state: startup mount;
Configure database: alter database archivelog;
Alter database in open mode: alter database open;

Making hot backup:

Verify database is in ARCHIVELOG mode: archive log list;
List down the all oracle data files which going to backup, select file_id,file_name from dba_data_files;
Get current online log sequence number: select group#, sequence#, status from v$log;

To start with hot backup, all the online datafile status should be in NOT ACTIVE mode in V$BACKUP dictionary view, that displays the backup status of all online datafiles. Then alter database as backup mode.

...(download the rest of the essay above)

About this essay:

This essay was submitted to us by a student in order to help you with your studies.

If you use part of this page in your own work, you need to provide a citation, as follows:

Essay Sauce, Database of Online-ordering system. Available from:<https://www.essaysauce.com/computer-science-essays/database-of-online-ordering-system/> [Accessed 14-10-19].

Review this essay:

Please note that the above text is only a preview of this essay.

Name
Email
Review Title
Rating
Review Content

Latest reviews: