Erp Database Example

Erp Database Example Average ratng: 4,8/5 2452 votes

During a complete installation of your Oracle Database, the sample schemas can be installed automatically with the seed database. If the seed database is removed from the system, you will need to reinstall the sample schemas before you can perform the steps given in the examples that you find in Oracle documentation and training materials.

Like most other open source ERP solutions, ADempiere is targeted at. Which is written in Python and uses the PostgreSQL database engine,. An enterprise database is used by enterprises and large organizations to manage their huge collection of data. Such a database helps companies improve their efficiency. An enterprise database is robust enough to successfully handle the queries of multiple users simultaneously, and is capable of handling a range of 100 to 10,000 users at a time.

This chapter describes how to install Product_Name. It contains the following sections:

Caution:

By installing any of the Oracle Database sample schemas, you will destroy any previously installed schemas that use any of the following user names: HR, OE, PM, SH, IX, BI.

Data contained in any of these schemas will be lost if you run any of the installation scripts described in this section. You should not use the sample schemas for your personal or business data and applications. They are meant to be used for demonstration purposes only.

Using the Database Configuration Assistant

When you install Oracle Database with the Oracle Universal Installer, the sample schemas are installed by default if you select the Basic Installation option. Selecting the sample schemas option installs all sample schemas (HR, OE, PM, SH, IX) in the database. If you choose not to install the sample schemas at that time, you can add them later by following the instructions in section 'Manually Installing Sample Schemas'.

At the end of the installation process, a dialog box displays the accounts that have been created and their lock status. By default, all sample schemas are locked and their passwords are expired. Before you can use a locked account, you must unlock it and reset its password. You can unlock the accounts at this point in the installation process. Alternatively, after the installation completes, you can unlock the schemas and reset their passwords by using the ALTER USER .. ACCOUNT UNLOCK statement. For example:

See Also:

'Guidelines for Securing Passwords' in Oracle Database Security Guide for guidelines related to creating secure passwords

The sample schemas available to you depend on the edition of Oracle Database that you have installed and its configuration. Refer to the following table:

SchemaOracle Database Personal editionOracle Database Standard editionOracle Database Enterprise edition
HROKOKOK
OEOKOKOK
PMOKOKOK
IXOKOKOK
SHNot availableNot availableNeeds Partitioning Option installed

Manually Installing Sample Schemas

If you decide not to install the sample schemas at the time of your initial database installation using DBCA, then you can also create the sample schemas manually by running SQL scripts. Install Oracle Database Examples (Companion CD, part of the media kit) to include these scripts in the demo directory under $ORACLE_HOME.

See Also:

Oracle Database Examples Installation Guide for download and installation information

Schema Dependencies

Various dependencies have been established among the schemas. So, when you create the schemas manually, you must create them in the following order: HR, OE, PM, IX, and SH.

Use this sequence to create the schemas:

  1. Create schema HR.

  2. Create schema OE. Schema HR is already present, and you must know the password for schema HR to grant HR object privileges to OE. Some HR tables are visible to user OE with the use of private synonyms. In addition, some OE tables have foreign key relationships to HR tables.

  3. Create schema PM: Foreign key relationships require that schema OE already exist when schema PM is created. You must know the password for OE, to grant to PM the right to establish and use these foreign keys.

    Note:

    Schema PM requires that the database be enabled for the Java Virtual Machine (JVM) and interMedia. You can accomplish this during installation or later using the DBCA.

  4. Create schema IX: The information exchange schema, IX, is based on order entry data in schema OE. Again, foreign key relationships require that schema OE already be present when schema IX is created. You must know the password for OE to grant to IX, the right to establish and use the foreign keys.

  5. Create schema SH. Schema SH logically depends on schema OE, but you can create schema SH without creating the other four schemas.

Guidelines for Installing Sample Schemas

All scripts necessary to install sample schemas reside in $ORACLE_HOME/demo/schema directory. Before you install sample schemas by running these scripts, follow these guidelines:

  1. You must connect as a system administrator using the SYSDBA privilege.

  2. When prompted to enter a password for the schema, enter a secure password that meets the requirements described in Oracle Database Security Guide.

  3. When prompted for tablespace names while running scripts:

    • Enter an appropriate tablespace name, for example, users as the default tablespace for a schema

    • Enter temp as the temporary tablespace for a schema

  4. When prompted for the log directory name, enter $ORACLE_HOME/demo/schema/log/ or any other existing directory name.

Note:

Make sure that you end the log directory name with a trailing slash, for example, $ORACLE_HOME/demo/schema/log/

Installing the HR Schema

All scripts necessary to create the Human Resource (HR) schema reside in $ORACLE_HOME/demo/schema/human_resources.

You need to call only one script, hr_main.sql, to create all the objects and load the data. The following steps provide a summary of the installation process:

  1. Log on to SQL*Plus as SYS and connect using the AS SYSDBA privilege.

  2. To run the hr_main.sql script, use the following command:

  3. Enter a secure password for HR

    See Also:

    Oracle Database Security Guide for the minimum password requirements

  4. Enter an appropriate tablespace, for example, users as the default tablespace for HR

  5. Enter temp as the temporary tablespace for HR

  6. Enter your SYS password

  7. Enter the directory path, for example, $ORACLE_HOME/demo/schema/log/, for your log directory

After script hr_main.sql runs successfully and schema HR is installed, you are connected as user HR. To verify that the schema was created, use the following command:

Running hr_main.sql accomplishes the following tasks:

  1. Removes any previously installed HR schema

  2. Creates user HR and grants the necessary privileges

  3. Connects as HR

  4. Calls the scripts that create and populate the schema objects

For a complete listing of the scripts and their functions, refer to Table 4-1.

A pair of optional scripts, hr_dn_c.sql and hr_dn_d.sql, is provided as a schema extension. To prepare schema HR for use with the directory capabilities of Oracle Internet Directory, run the hr_dn_c.sql script. If you want to return to the initial setup of schema HR, use script hr_dn_d.sql to undo the effects of script hr_dn_c.sql.

You can use script hr_drop.sql to drop schema HR.

Installing Schema OE and Subschema OC

All scripts necessary to create the Order Entry (OE) schema and its Online Catalog (OC) subschema reside in $ORACLE_HOME/demo/schema/order_entry.

See Also:

Guidelines for Installing Sample Schemas before you run oe_main.sql

You need to call only one script, oe_main.sql, to create all the objects and load the data. Running oe_main.sql accomplishes the following tasks:

  1. Removes any previously installed OE schema

  2. Creates schema (user) OE and grants it the necessary privileges

  3. Connects as OE

  4. Calls the scripts that create and populate the schema objects

After the oe_main.sql script runs successfully and schema OE is installed, you are connected as user OE. To verify that the schema was created, use the following command:

For a complete listing of the scripts and their functions, refer to Table 4-10.

Barbie in a christmas carol movie. Download Barbie In A Christmas Carol (2008)dvdrip torrent or any other torrent from category. Direct download via HTTP available as well. Download barbie in a christmas carol 2008torrent for free, barbie in a christmas carol 2008 torrent download, download barbie in a christmas carol 2008. Barbie.In.A.Christmas.Carol.STV.FRENCH.DVDRiP.XViD.avi flac Barbie in A Christmas Carol Soundtrack avi Barbie in a Christmas Carol 2008.avi. Movie info: On Christmas Eve, Kelly is reluctant to go to a Christmas Eve ball, so Barbie tells her the story of Eden Starling, a glamourous singing diva in the Victorian England and the owner of a theatre house. However, Eden is self-centred and loves only herself. She is frequently accompanied by her snooty cat, Chuzzlewit. She does not believe in Christmas and orders all her employees to.

You can use scripts oe_drop.sql and oc_drop.sql to drop schema OE and subschema OC, respectively.

Installing the PM Schema

Erp

All scripts necessary to create the Product Media (PM) schema reside in $ORACLE_HOME/demo/schema/product_media.

See Also:

Guidelines for Installing Sample Schemas before you run pm_main.sql

You need to call only one script, pm_main.sql, to create all the objects and load the data. Running pm_main.sql accomplishes the following tasks:

  1. Prompts for passwords and tablespace names used within the scripts as well as datafile and log file directories

  2. Removes any previously installed PM schema

  3. Creates user PM and grants it the necessary privileges

  4. Connects as PM

  5. Calls the scripts that create and populate the schema objects

After script pm_main.sql runs successfully and schema PM is installed, you are connected as user PM. To verify that the schema was created, use the following command:

For a complete listing of the scripts and their functions, refer to Table 4-19 .

You can use script pm_drop.sql to drop schema PM.

Note:

The SQL*Loader data file pm_p_lob.dat contains hard-coded absolute path names that have been set during installation. Before attempting to load the data in a different environment, you should first edit the path names in this file.

Installing the IX Schema

All scripts necessary to create the Information Exchange (IX) schema reside in $ORACLE_HOME/demo/schema/info_exchange.

See Also:

Guidelines for Installing Sample Schemas before you run ix_main.sql

To install schema IX, you need to call only one script, ix_main.sql, which creates all the objects and loads the data.

Running ix_main.sql accomplishes the following tasks:

  1. Prompts for passwords and tablespace names used within the scripts

  2. Removes any previously installed IX schema

  3. Creates user IX and grants the necessary privileges

  4. Connects as IX

  5. Calls the scripts that create and populate the schema objects

After the ix_main.sql script runs successfully and schema IX is installed, you are connected as user IX. To verify that the schema was created, use the following command:

For a complete listing of the scripts and their functions, refer to Table 4-23.

You can use script dix_v3.sql to drop schema IX.

Installing the SH Schema

All scripts necessary to create the Sales History (SH) schema reside in $ORACLE_HOME/demo/schema/sales_history.

See Also:

Guidelines for Installing Sample Schemas before you run sh_main.sql

You need to call only one script, sh_main.sql, to create all the objects and load the data. Running sh_main.sql accomplishes the following tasks:

  1. Prompts for passwords and tablespace names used within the scripts as well as datafile and log file directories

  2. Removes any previously installed SH schema

  3. Creates user SH and grants the necessary privileges

  4. Connects as SH

  5. Calls the scripts that create and populate the schema objects

After script sh_main.sql runs successfully and schema SH is installed, you are connected as user SH. To verify that the schema was created, use the following command:

For a complete listing of the scripts and their functions, refer to Table 4-27.

Note:

The dimension tables PROMOTIONS, CUSTOMERS, PRODUCTS and the fact table SALES are loaded by SQL*Loader, after which directory paths are created inside the database to point to the load and log file locations. This allows the loading of the COSTS table by using the external table sales_transactions_ext.

A pair of optional scripts, sh_olp_c.sql and sh_olp_d.sql, is provided as a schema extension. To prepare schema SH for use with the advanced analytical capabilities of OLAP Services, run the sh_olp_c.sql create script. If you want to return to the initial setup of schema SH, then use script sh_olp_d.sql to undo the effects of sh_olp_c.sql and reinstate dimensions as they were before.

You can use script sh_drop.sql to drop schema SH.

Resetting Sample Schemas

To reset sample schemas to their initial state, use the following syntax from the SQL*Plus command-line interface:

The mksample script expects 11 parameters. Provide the password for SYSTEM and SYS, and for schemas HR, OE, PM, IX, and SH. Specify a temporary and a default tablespace, and make sure to end the name of the log file directory with a trailing slash.

The mksample script produces several log files:

  • mkverify.log is the Sample Schema creation log file.

  • hr_main.log is the HR schema creation log file.

  • oe_oc_main.log is the OE schema creation log file.

  • pm_main.log is the PM schema creation log file.

  • pm_p_lob.log is the SQL*Loader log file for PM.PRINT_MEDIA.

  • ix_main.log is the IX schema creation log file.

  • sh_main.log is the SH schema creation log file.

  • cust.log is the SQL*Loader log file for SH.CUSTOMERS.

  • prod.log is the SQL*Loader log file for SH.PRODUCTS.

  • promo.log is the SQL*Loader log file for SH.PROMOTIONS.

  • sales.log is the SQL*Loader log file for SH.SALES.

  • sales_ext.log is the external table log file for SH.COSTS.

In most situations, there is no difference between installing a Sample Schema for the first time or reinstalling it over a previously installed version. The *_main.sql scripts drop the schema users and all of their objects.

In some cases, complex interobject relationships in schema OE or IX prevent DROPUSER .. CASCADE operations from completing normally. To correct these rare cases, use one of the following procedures:

For the OC catalog subschema of schema OE:

  1. Connect as user OE.

  2. Run script oc_drop.sql.

  3. Connect as SYSTEM.

  4. Ensure that user OE is not connected:

  5. Drop user OE:

For the IX schemas:

  1. Connect as SYSTEM.

  2. Ensure that no user is connected as an IX user:

  3. Drop the schemas by running script dix.sql. You will be prompted for passwords for the individual users.

Uninstalling Sample Schemas

If you need to remove the sample schemas from the installation, run script drop_sch.sql on the SQL*Plus command line. This script ships with Oracle Database.

Script drop_sch.sql uses two parameters: systempwd is the password for SYSTEM user, and spool_file_name is the name of the spool file that captures the log of the operation.

What is Normalization?

Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data.

It divides larger tables to smaller tables and links them using relationships.

In this tutorial, you will learn-

The inventor of the relational model Edgar Codd proposed the theory of normalization with the introduction of First Normal Form, and he continued to extend theory with Second and Third Normal Form. Later he joined with Raymond F. Boyce to develop the theory of Boyce-Codd Normal Form.

Theory of Data Normalization in SQL is still being developed further. For example, there are discussions even on 6th Normal Form. However, in most practical applications, normalization achieves its best in 3rd Normal Form. The evolution of Normalization theories is illustrated below-

Database Normalization Examples -

Assume a video library maintains a database of movies rented out. Without any normalization, all information is stored in one table as shown below.

Here you see Movies Rented column has multiple values.

Database Normal Forms

Now let's move into 1st Normal Forms

1NF (First Normal Form) Rules

  • Each table cell should contain a single value.
  • Each record needs to be unique.

The above table in 1NF-

1NF Example

Before we proceed let's understand a few things --

What is a KEY?

A KEY is a value used to identify a record in a table uniquely. A KEY could be a single column or combination of multiple columns

Note: Columns in a table that are NOT used to identify a record uniquely are called non-key columns.

What is a Primary Key?

A primary is a single column value used to identify a database record uniquely.

It has following attributes

  • A primary key cannot be NULL
  • A primary key value must be unique
  • The primary key values should rarely be changed
  • The primary key must be given a value when a new record is inserted.

What is Composite Key?

A composite key is a primary key composed of multiple columns used to identify a record uniquely

In our database, we have two people with the same name Robert Phil, but they live in different places.

Hence, we require both Full Name and Address to identify a record uniquely. That is a composite key.

Let's move into second normal form 2NF

The Da Vinci Code 2006 BluRay Hindi Dual Audio 720p IMDB Ratings: 6.6/10 Genre: Mystery, Thriller Director: Ron Howard Stars Cast: Tom Hanks, Audrey Tautou, The Da Vinci Code 2006 BluRay Hindi Dual Audio 720p IMDB Ratings: 6.6/10 Genre: Mystery, Thriller Director: Ron Howard Stars Cast: Tom Hanks, Audrey Tautou. Mojin The Lost Legend 2015. The da vinci code 2006 extended 720p hindi brrip dual audio. The Da Vinci Code 2006 Extended Dual Audio Hindi – English 720p 1.4GB BluRay. October 31, 2018 Admin Leave a Comment.

2NF (Second Normal Form) Rules

  • Rule 1- Be in 1NF
  • Rule 2- Single Column Primary Key

It is clear that we can't move forward to make our simple database in 2nd Normalization form unless we partition the table above.

We have divided our 1NF table into two tables viz. Table 1 and Table2. Table 1 contains member information. Table 2 contains information on movies rented.

We have introduced a new column called Membership_id which is the primary key for table 1. Records can be uniquely identified in Table 1 using membership id

Database - Foreign Key

In Table 2, Membership_ID is the Foreign Key

Foreign Key references the primary key of another Table! It helps connect your Tables

  • A foreign key can have a different name from its primary key
  • It ensures rows in one table have corresponding rows in another
  • Unlike the Primary key, they do not have to be unique. Most often they aren't
  • Foreign keys can be null even though primary keys can not

Why do you need a foreign key?

Suppose, a novice inserts a invalid record in Table B

You will only be able to insert values into your foreign key that exist in the unique key in the parent table. This helps in referential integrity.

The above problem can be overcome by declaring membership id from Table2 as foreign key of membership id from Table1

Now, if somebody tries to insert a value in the membership id field that does not exist in the parent table, an error will be shown!

What are transitive functional dependencies?

A transitive functional dependency is when changing a non-key column, might cause any of the other non-key columns to change

Consider the table 1. Changing the non-key column Full Name may change Salutation.

Let's move into 3NF

3NF (Third Normal Form) Rules

  • Rule 1- Be in 2NF
  • Rule 2- Has no transitive functional dependencies

To move our 2NF table into 3NF, we again need to again divide our table.

3NF Example

We have again divided our tables and created a new table which stores Salutations.

There are no transitive functional dependencies, and hence our table is in 3NF

In Table 3 Salutation ID is primary key, and in Table 1 Salutation ID is foreign to primary key in Table 3

Now our little example is at a level that cannot further be decomposed to attain higher forms of normalization. In fact, it is already in higher normalization forms. Separate efforts for moving into next levels of normalizing data are normally needed in complex databases. However, we will be discussing next levels of normalizations in brief in the following.

Boyce-Codd Normal Form (BCNF)

Even when a database is in 3rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key.

Sometimes is BCNF is also referred as 3.5 Normal Form.

4NF (Fourth Normal Form) Rules

If no database table instance contains two or more, independent and multivalued data describing the relevant entity, then it is in 4th Normal Form.

5NF (Fifth Normal Form) Rules

A table is in 5th Normal Form only if it is in 4NF and it cannot be decomposed into any number of smaller tables without loss of data.

6NF (Sixth Normal Form) Proposed

6th Normal Form is not standardized, yet however, it is being discussed by database experts for some time. Hopefully, we would have a clear & standardized definition for 6th Normal Form in the near future..

That's all to Normalization!!!

Summary

  • Database designing is critical to the successful implementation of a database management system that meets the data requirements of an enterprise system.
  • Normalization helps produce database systems that are cost-effective and have better security models.
  • Functional dependencies are a very important component of the normalize data process
  • Most database systems are normalized database up to the third normal forms.
  • A primary key uniquely identifies are record in a Table and cannot be null
  • A foreign key helps connect table and references a primary key

Related Post