Database can be considered as a bank of data. Its an essential way for data collection, storage and retrieval; facilitates the transformation of data into information and the management of both data and information. I will be describing Database System used Oman Oil Marketing Company S.A.O.G (OOMCO).
Introduction to Oman Oil Marketing Company
Oman Oil first established in 1957 as British Petroleum (BP). In August 1998 BP floated and was registered as an Omani public company putting its stock in Muscat Security Market (MSM) by 1st November 1998. BP finally sold all their shares to Oman Oil Company in early 2002. In September 2003 Oman Oil Marketing Company was established. OOMCO is a downstream fuel and oils marketing company. Involved in the marketing and distribution of both ground and aviation fuels together with lubricants throughout the Sultanate. Its major shareholders are: OMAN OIL COMPANY (49%), SUHAIL BAHWAN GROUP (9%), DIDIC (5%) and PUBLIC OF OMAN (37%). OOMCO business functions within: RETAIL / FUEL CARDS, COMMERCIAL, LUBRICANTS, AVIATION, OPERATIONS, FINANCE / IT, ENGINEERING / HSSE, HUMAN RESOURCES and GOVERNMENT PUBLIC AFFAIRS. OOMCO Retail operates petrol filling stations with Shops, Car washes, Jet washes and Oil Change facility. All F/S are operated by Oman oil or by dealer's stations. OOMCO offers to customers two cards Basma (Smart card - Prepaid Card) and Ejaba (Magnetic - Corporate Card). All their business includes a lot of data which are maintained in different Information System.
The following are the application system used in Oman Oil Marketing Company S.A.O.G (OOMCO):
Terminal Management System
Terminal Automation System
Lubes System
Fuel Card reporting Module
Invoicing System (Retail System)
Payroll System
Admin System
Business Intelligence System
Tank gauging System,
Oracle Financials
E-Forms
TASK ONE:
Q1.
Database Administrator
End User:
Oman Oil Marketing Company Staff.
System Developers:
IT Analyst.
Web Application Developer.
Application Programs:
1. Terminal Automation System
2. Terminal Management System
3. Invoicing System (Retail System)
4. Fuel Card reporting Module
5. Admin System
6. E-Forms
7. Business Intelligence System
8. Lubes System
9. Planned Delivery System(PDS)
10. Payroll System
11. Oracle Financials
User Interface:
Oracle Developers and Visual C++.
CASE Tool
.
Database: 3 Production DBs
& 3 Test DBs
Repository
Oracle DBMS
nkk
Q2:
Oman Oil Marketing Database Administrator has a very important critical role. His maintaining many applications and also his responsible and a team leader of all system developers that's besides looking after the Database server. His main responsibilities include: -
Primary responsibility:
Maintain the Existing Business Applications (TMS, Retail, Lubes, Fuel-card, Payroll, Admin, e-Forms, Lube-plus)
Supports Lube-Plus Application and trouble shooting
ORACLE Applications. One or all of the following (Release 12 suite, Financial, eAM, Order management, Inventory, Purchasing, HCM)
Testing, Implementation and Training to the users
Support the business in the day-to-day operational problems / trouble shooting issues as and when they arise.
Rollout upgrade / new changes to the system to all sites
Perform Oracle Database Administration Tasks
Perform Quarterly Health checks for all the databases
Perform Performance Tuning, as and when required
Perform Recovery of the database in the event of a crash
Secondary responsibility:
Provide Applications Helpdesk Support to the Users
Perform Troubleshooting of the issues logged in by the users
Modify the programs (Forms, Reports etc) appropriately to resolve the issues
Handle the release management process, with the approval of the IT Manager
Perform the Software Development Life Cycle for small to medium application projects, with the guidance of the IT Manager
As for the system developers who mainly required to develop new system and work on solving current system error with Database Administrator. The IT Analyst / Web Application Developer tasks include:
Primary Accountability:
Web and Mobile based Business Applications development and maintenance.
Development of Notification/Alerts systems using Mobile and SMS technology.
Support and enhancement of existing applications (e-Service Request, Payroll, HRMS, Admin, e-Forms, SMS-Pro)
Application Training, Support and Trouble shooting
Provide Support to the Users (e-Service Request, Payroll, HRMS, Admin, e-Forms, SMS-Pro)
Maintain the Existing Business Web/SMS based Applications (e-Service Request, Payroll, HRMS, Admin, e-Forms, SMS-Pro)
Perform Troubleshooting of the issues logged in by the users
Modify the programs (Forms, Reports etc) appropriately to resolve the issues
Handle the release management process, with the approval of Sr. Manager - IT
Perform the Software Development Life Cycle for web/mobile based application projects
Application Training, Rollout and Support.
End users connect to applications through an Oracle Developers and one with Visual C++. They are around 50 users using different application based on their job requirement and depts., for example administration and HR dept have access to Payroll system they can generate and view report but can't modify, add or delete records without going back to IT dept either to the database administration or developers.
CASE Tool is used by database administrators and developers. It assists and facilitates the way they develop, maintain, and view the database applications. Some example of used cast tools are: Case Studio 2 Acquired by Toad, Database Design Tool (DDT), Database Design Studio, Google WWW SQL Designer, MySQL Workbench, SQL Manager, Toad Data Modeler, Visio and many more. No CASE Tool is used in Oman Oil Marketing.
Oracle Developers and Visual C++ are the interface used in Oman Oil Marketing to enable all end users to interact with the database. Basically an interface is any object that facilitates the communication between a machine and the user in way that is simple clear and understandable to the user. All users don't have programming background neither could they understand one programming command, so an interface allows them to get their details by a click of a button. The programming is done in the background of the interface where the users can't see or feel it.
There are 13 active Application used in Oman Oil Marketing Company as followed:
Terminal Management System.
Terminal Automation System.
Lubes System.
Fuel Card reporting Module.
Invoicing System (Retail System).
Payroll System.
Admin System.
Business Intelligence System.
Tank gauging System.
Oracle Financials
E-Forms
Please refer to Appendix 2 for more details about the applications.
Oracle DBMS is usually known as Oracle RDBMS or just Oracle it's also short for Relational Database Management System. It's a system offered to people by Oracle Corporation. It consists of physical and logical structures. It depends on different processes working together in the system background to monitor and execute database operations. Is comes in 63 language versions and is being observed by the Database Administrator.
Repository is a storage where data are being stored and retrieved when necessary to be used in a specific computer or user.
Q3:
Protecting our software is an essential duty of every individual in the organization. Every country world wide has a copyright law which is a mast be followed and implemented by every citizen. Breaking the copyright law and Software piracy is considered a crime that a person will be punished for that could also lead to imprisoning or paying a lot of money. Oman has declared its copyright law June 15, 1996 by his majesty. Since the law was effective and taken very seriously people felt their software or products are protected from such crimes. Oman Oil has a limit number of people who have access to the DBMS and the system is secured no software is allowed to be copied for any kind of reasons. They have also contacted Oracle to certify some of there products which were developed by there staff.
Q4: Oman Oil Marketing Company hardware requirement
Task Two:
Using Oracle DBMS I managed to create a transaction processing database with SGA size = 600MB. Please refer to Appendix 1.
By following the Oracle Flexible Architecture (OFA) guidelines I managed to create SALES_APP_TBS and SALES_IDX tablespaces. OFA helps DBA to organize the files when creating an Oracle database.
SQL> CREATE TABLESPACE SALES_APP_TBS DATAFILE 'C:\TASK1\SALES_APP_TBS.DBF' SIZE 100M;
SQL> CREATE TABLESPACE SALES_IDX DATAFILE 'C:\TASK1\SALES_APP_TBS.DBF' SIZE 100M;
Create SALESPERSON table:
SQL> CREATE TABLE SALESPERSON(SALESPERSON_ID NUMBER NOT NULL PRIMARY KEY,SALESPERSON_NAME VARCHAR2(30),SALESPERSON_TELEPHONE NUMBER,SALESPERON_FAX NUMBER)TABLESPACE SALES_APP_TBS;
Create SALES TERRITORY table:
SQL> CREATE TABLE SALES_TERRITORY(TERRITORY_ID NUMBER NOT NULL PRIMARY KEY,TERRITORY_NAME VARCHAR2(20) NOT NULL ) TABLESPACE SALES_APP_TBS;
Create Does Business In:
SQL> CREATE TABLE DOSE_BUSINESS_IN(TERRITORY_ID NUMBER NOT NULL REFERENCES SALES_TERRITORY(TERRITORY_ID), CUSTOMER_ID NUMBER NOT NULL REFERENCES CUSTOMER(CUSTOMER_ID) TABLESPACE SALES_APP_TBS;
Create PRODUCT LINE Table:
SQL> CREATE TABLE PRODUCT_LINE(PRODUCT_ID NUMBER NOT NULL PRIMARY KEY REFERENCES PRODUCT(PRODUCT_ID),PRODUCT_LINE_NAME VARCHAR2(30)) TABLESPACE SALES_APP_TBS;
Create CUSTOMER Table:
SQL> CREATE TABLE CUSTOMER(CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,CUSTOMER_NAME VARCHAR2(20), CUSTOMER_ADDRESS VARCHAR2(15), ORDER_ID REFERENCE ORDER )TABLESPACE SALES_APP_TBS;
Create ORDER Table:
SQL> CREATE TABLE ORDER(ORDER_ID NUMBER NOT NULL PRIMARY KEY,ORDER_DATE DATE) TABLESPACE SALES_APP_TBS;
Create Table PRODUCT Table:
SQL> CREATE TABLE PRODUCT(PRODUCT_ID NUMBER NOT NULL PRIMARY KEY,PRODUCT_DESCRIPTION VARCHAR2(30), PRODUCT_FINISH DATE,STABDARD_PRICE NUMBER(38)) TABLESPACE SALES_APP_TBS;
Create ORDER LINE Table:
SQL> CREATE TABLE ORDER_LINE(PRODUCT_ID NUMBER NOT NULL PRIMARY KEY,ORDERED_QUANTITY NUMBER(30) NOT NULL ) TABLESPACE SALES_APP_TBS;
Create VENDOR Table:
SQL> CREATE TABLE VENDOR(VENDOR_ID NUMBER NOT NULL PRIMARY KEY,VENDOR_NAME VARCHAR2(20),VENDOR_ADDRESS VARCHAR2(20)) TABLESPACE SALES_APP_TBS;
Create Uses Table:
SQL> CREATE TABLE USES(MATERIAL_ID NUMBER NOT NULL REFERENCES RAW_MATERIAL_ID(MATERIAL_ID),PRODUCT_ID NUMBER NOT NULL REFERENCES PROUDCUT(PRODUCT_ID),GOES_INTO_QUANTITY VARCHAR2(20)) TABLESPACE SALES_APP_TBS;
Create Produced In Table:
SQL> CREATE TABLE PRODUCED_IN(PRODUCT_ID NUMBER NOT NULL REFERENCE PRODUCT(PRODUCT_ID),WORK_CENTER_ID NUMBER NOT NULL REFERENCE WORK_CENTER(WORK_CENTER_ID) TABLESPACE SALES_APP_TBS;
Create WORK CENTER Table:
SQL> CREATE TABLE WORK_CENTER(WORK_CENTER_ID NUMBER NOT NULL PRIMARY KEY,WORK_CENTER_LOCATION VARCHAR2(30)) TABLESPACE SALES_APP_TBS;
Create RAW MATERIAL Table:
SQL> CREATE TABLE RAW_MATERIAL(MATERIAL_ID NUMBER NOT NULL PRIMARY KEY,MATERIAL_NAME VARCHAR2(30), STANDARD_COST NUMBER,UNTI_OF_MEASURE VARCHAR2(10)) TABLESPACE SALES_APP_TBS;
Create Supplies Table:
SQL> CREATE TABLE SUPPLIES(VENDOR_ID NUMBER NOT NULL REFERENCE VENDOR,MATERIAL_ NUMBER NOT NULL ID REFERENCE RAW_MATERIAL,SUPPLY_UNIT_PRICE NUMBER) TABLESPACE SALES_APP_TBS;
Create Works In Table:
SQL> CREATE TABLE WORK_IN(WORK_CENTER_ID NUMBER NOT NULL REFERENCE WORK_CENTER(WORK_CENTER_ID) ,EMPLOYEE_ID NUMBER NOT NULL REFERENCE EMPLOYEE(EMPLOYEE_ID) ) TABLESPACE SALES_APP_TBS;
Create Employee Table:
SQL> CREATE TABLE EMPLOYEE(EMPLOYEE_ID NUMBER NOT NULL PRIMARY KEY,EMPLOYEE_NAME VARCHAR2(20), EMPLOYEE_ADDRESS VARCHAR2(10)) TABLESPACE SALES_APP_TBS;
Create Has Skill Table:
SQL> CREATE TABLE HAS_SKILL(EMPLOYEE_ID NUMBER NOT NULL REFERENCES EMPLOYEE(EMPLOYEE_ID) ,SKILL VARCHAR2(30) REFERENCES SKILL(SKILL)) TABLESPACE SALES_APP_TBS;
Create SKILL Table:
SQL> CREATE TABLE SKILL(SKILL VARCHAR2(30) PRIMARY KEY) TABLESPACE SALES_APP_TBS;
INDEX
1. SQL> CREATE UNIQUE INDEX SaleNameIndex ON SALESPERSON (SALESPERSON_NAME)TABLESPACE SALES_IDX;
Used to search for salesperson based on name.
2. SQL> CREATE UNIQUE INDEX SaleTelIndex ON SALESPERSON (SALESPERSON_TELEPHONE) TABLESPACE SALES_IDX;
Used to search for salesperson based on telephone number.
3. SQL> CREATE INDEX TerritoryNameIndex ON SALES_TERRITORY (TERRITORY_NAME) TABLESPACE SALES_IDX;
Used to search for territory detail based on territory name.
4. SQL> CREATE UNIQUE INDEX CustomerNameIndex ON CUSTOMER(CUSTOMER_NAME) TABLESPACE SALES_IDX;
Used to search customer based on name
5. SQL> CREATE INDEX CustomerAddIndex ON CUSTOMER(CUSTOMER_ADDRESS)TABLESPACE SALES_IDX;
Used to search customer based on address.
6. SQL> CREATE INDEX PostalCodeIndex ON CUSTOMER(POSTAL_CODE) TABLESPACE SALES_IDX;
Used to search customer based on postal address
7. SQL> CREATE INDEX ProductDesIndex ON PROUDCT(PRODUCT_DESCRIPTION) TABLESPACE SALES_IDX;
Used to search products based on their description.
8. SQL> CREATE INDEX ProductFinIndex ON PRODUCT(PRODUCT_FINISH) TABLESPACE SALES_IDX;
Used to search products based on products finish.
9. SQL> CREATE INDEX EmpNameIndex ON EMPLOYEE(EMPLOYEE_NAME) TABLESPACE SALES_IDX;
Used to search for e employee based on their names.
10. SQL> CREATE INDEX EmpAddIndex ON EMPLOYEE(EMPLOYEE_ADDRESS) TABLESPACE SALES_IDX;
Used to search for employee based on their address.
11. SQL> CREATE INDEX SalesPersonDetail ON SALESPERSON(SALESPERSON_NAME,SALESPERSON_TELEPHONE) TABLESPACE SALES_IDX;
Used to search salesperson details based on their names and telephone number.
12. SQL> CREATE INDEX CustomerDetails ON CUSTOMER(CUSTOMER_NAME,CUSTOMER_ADDRESS)TABLESPACE SALES_IDX;
Used to search for customer based on their names and address.
13. SQL> CREATE INDEX EmpDetail ON EMPLOYEE(EMPLOYEE_NAME,EMPLOYEE_ADDRESS) TABLESPACE SALES_IDX;
Used to search for employee based on their names and address
14. SQL> CREATE INDEX VendorDetail ON VENDOR(VENDOR_NAME,VENDOR_ADDRESS)TABLESPACE SALES_IDX;
Used to search for vendor based on their names and address.
View
SQL> CREATE VIEW C1 AS SELECT CUSTOMER_ID,CUSTOMER_NAME FROM CUSTOMER;
SQL> CREATE VIEW SC1 AS SELECT CUSTOMER_NAME,TERRITORY_NAME FROM CUSTOMER A,SALES_TERRITORY B WHERE A.CUSTOMER_ID=B.TERRITORY_ID;
3. SQL>SELECT PRODUCT_DESCRIPTION, PRODUCT_FINISH, ORDERED_QUANTITY FROM PRODUCT,ORDER_LINE WHERE PRODUCT.PRODUCT_ID=ORDER_LINE.PRODUCT_ID;
Tasting server
1st in server side:
Get DB name : ORCL
Get IP address or server name (to get IP add type in command IP CONFIG).
Port No: 1521 (Logical port of Oracle).
Disable firewall.
2nd the client side:
Get IP address of the server and test connectivity between client and sever.
Then go to ALL PROGRAM→ ORACLE-DevSuiteHome1→ CONFIGURATION & MIGRATION TOOLS → NET CONFIGURATION ASSISTANT → LOCAL NET SERVICE NAME CONFIGURATION → NEXT → ADD → SERVICE NAME: ORCL → NEXT → TCP → NEXT → HOST NAME: 192.168.1.1 (THIS IP IS JUST AN EXAMPLE) → NEXT → YES PERFORM TEST → NEXT → NET SERVICES NAME: SAMIYA → NO → FINISH. Then open SQL user name , password, host string (type here the net service name)
I recommend Automatic Storage Management (ASM). It's a feature offered by Oracle Corporation inside the Oracle DB for Oracle 10g and above. It enhances simplicity of DB files. It contains tools that manage file systems and volumes from inside the DB.
As quoted from wiki website "In summary ASM provides the following functionality:
Manages groups of disks, called disk groups.
Manages disk redundancy within a disk group.
Provides near-optimal I/O balancing without any manual tuning.
Enables management of database objects without specifying mount points and filenames.
Supports large files."
