Essay: Database of Online-ordering system

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.
Create backup directory. It is recommended to backup your pfile or spfile by copying it to backup location.
Copy all the files from Step 2 to backup directory.
Set database back. alter database end backup;
Verify current log sequence number same as step 3.
Switch the logfile in order to archive current log sequence number.alter system switch logfile;
Use select group#, sequence#, status from v$log; to get current log sequence number.
Verify log sequence number have been archived to archive log location select SEQUENCE#,ARCHIVED,STATUS from v$archived_log where SEQUENCE#=11;
Copy all archived logs (i.e. log sequence number noted in step-3 and all archived logs generated during backup) from archived log location (i.e. FRA) to the backup location.
Backup the database control file. alter database backup controlfile to ‘path/control.ctl’;
Recovery
Redo log file
The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.
Multiplexing Redo Log Files
two or more identical copies of the redo log can be automatically maintained in separate locations. the redundancy can help protect against I/O errors, file corruption, and so on.
Multiplexing is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of the group. Each redo log group is defined by a number, such as group 1, group 2, and so on.
Creating Redo Log Groups and Members
Demonstration of Recovery from Cold Backup:
Recovery is means roll back or return to the previous status, when database is going down. To restore from the cold backup, a valid cold backup is required. In this database system, we’ve already made a cold backup above, but a schedule cold backup is recommended.
Steps:
From “Back up -> cold backup -> making cold backup” you already have a cold backup;
Make a screenshot first to recording the state;
Delete some rows or drop some table;
Shutdown database;
Copy the backup files back to the place;
Start database;
Check the state;
Advance
Security through views
A View in database systems is the representation of a SQL statement that is stored in memory so that it can easily be reused. As views can be used to restrict the columns that a given user has access to, it is also considered as a security method.
Demonstration of table partitioning options
Maintenance of large tables can become very time and resource consuming. Also data access performance become reducing dramatically.
Partition means backup and recovery operations can good performed, plus partition can improved the speed of query performance(Partitioned Tables And Indexes. n.d.).
Oracle VPD
Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied (Database Security Guide. 2012, July 20).
For this demo, I create a new user vpd, and create a new table which have a column “PRICE”, only the owner can select the entire table, but other user (not include sys) only can get the row as “PRICE” higher than 10.
Steps:
Create a new table and insert some rows;
Create function.
“f_limited_query” is a function, only shows the price higher than 20 to other users.
Add policy
Policy is
Connect with different user, dianyi, and dianyi only can select the rows as price higher than 20;
Demonstration of XML database
Oracle DB provides full support for the key XML standards, developers are able to use XML to store, manage, organize, and manipulate XML content.
In this Demo, I’m going to export the food table.
Right click the table which you want to export
Unselect the Export DDL, select XML from for format
Select next to the end, you will get a xml file
Reference
SQL Developer. (n.d.). Retrieved April 11, 2018, from http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
Database Administrator’s Guide. (2008, March 13). Retrieved April 11, 2018, from https://docs.oracle.com/cd/B28359_01/server.111/b28310/control001.htm#ADMIN11281
Database Security Guide. (2014, January 15). Retrieved April 11, 2018, from https://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm#DBSEG007
Jethwa, J. (2017, July 25). Oracle 11g Manual Online Hot Backup. Retrieved April 11, 2018, from https://dbatricksworld.com/oracle-11g-manual-online-hot-backup/
Partitioned Tables And Indexes. (n.d.). Retrieved April 11, 2018, from https://oracle-base.com/articles/8i/partitioned-tables-and-indexes
Database Security Guide. (2012, July 20). Retrieved April 11, 2018, from https://docs.oracle.com/cd/B19306_01/network.102/b14266/apdvpoli.htm#CHDFGBEB