Oracle Consulting and Training
For Better, Faster, Smarter
A Short Oracle Tutorial For Beginners (ctd)
What is a relational database?
As mentioned in part 1 of this Oracle tutorial, one of the underlying principles of relational database is the
separation and independence of the the logical and physical data models. This provides enormous
flexibility and means you can change change either one without affecting the other.
A relational database can be regarded as a set of 2-dimensional tables which are known as "relations" in relational database theory. Each table has rows (”tuples”) and columns ("domains"). The relationships between the tables is defined by one table having a column with the same meaning (but not necessarily the same name or value) as a column in another table. For example, let's consider a (very small) Oracle database with just two tables: emp (id number ,name varchar2(30) ,job_title varchar2(30) dept_id number)) holding employee information, and dept(id number ,name varchar2(30)) holding department information. There is an implied relationship between these tables because emp has a column called dept_id which relates to the id column in dept. In Oracle this is usually implemented by what's called a foreign-key relationship which prevents values being stored in the child table (emp in our example) that are not present in the referenced (parent) table (dept in our example). Relational databases obtain their flexibility from being based on relational calculus (similar to set theory) which enables sets or relations to be combined in various ways, including: * join/intersection * union (i.e. the sum of 2 sets); * exclusive "OR" (i.e. the difference between 2 sets) * and outer-join - a combination of intersecting and exclusive or ing. These operations translate into mathematical procedures but fortunately for us, the relational database does all the work behind the scenes, all we as users have to do is specify our requirements in SQL. The intersection (or join) between 2 sets (in this case, tables) produces only those elements that exist in both sets. Therefore, if we join emp and dept on department id, we will be left with only those employees who work for a department that is in the dept table and only those departments which have employees who are in the emp table. The union produces the sum of the tables - meaning all records in emp and all records in dept. including any duplicates.(although duplicates can be discarded if required). Let's use the following data to provide specific examples: emp dept The join of emp and dept on the department id would produce the following result: The union of emp and dept would produce these results: The union operator is only allowed when the number and data types of the columns in the 2 sets are the same. It is normally used to combine sub sections from one or more tables rather than entire tables. There are other operators and variations but this is just an overview and there isn't the space or the time to provide full details in this short Oracle tutorial. The later versions of Oracle database (from Oracle 8 onwards) support both relational and object-oriented features. In this context an object has both attributes and methods (programs stored with the object that performs a certain action or task). Go to part 1 of this Oracle tutorial or see more Oracle tutorials. --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor- led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky- rocket productivity and make your systems better, faster and smarter. © Smartsoft Computing Ltd, Bristol, England Tel: 0117 924 7646 Need help with Oracle? Contact Us This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy. View Smartsoft privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
| Id | Name | Dept Id |
| 1 | Bill Smith | 3 |
| 2 | Mike Lewis | 2 |
| 3 | Ray Charles | 3 |
| 4 | Andy Mallory | 4 |
| 5 | Mandy Randall | 6 |
| 6 | Allison White | 1 |
| Id | Name |
| 1 | HR |
| 2 | IT |
| 3 | Marketing |
| 4 | Sales |
| 5 | Finance |
| Emp.Id | Emp.Name | Dept.Id | Dept.Name |
| 1 | Bill Smith | 3 | Marketing |
| 2 | Mike Lewis | 2 | IT |
| 3 | Ray Charles | 3 | Marketing |
| 4 | Andy Mallory | 4 | Sales |
| 6 | Allison White | 1 | HR |
| Id | Name |
| 1 | Bill Smith |
| 2 | Mike Lewis |
| 3 | Ray Charles |
| 4 | Andy Mallory |
| 5 | Mandy Randall |
| 1 | HR |
| 2 | IT |
| 3 | Marketing |
| 4 | Sales |
| 5 | Finance |
For Better,
Faster, Smarter
Oracle Consulting
and Training
A Short Oracle Tutorial For Beginners (ctd)
What is a relational database?
As mentioned in part 1 of this Oracle tutorial, one of the underlying
principles of relational database is the separation and independence of the
the logical and physical data models. This provides enormous flexibility
and means you can change change either one without affecting the other.
A relational database can be regarded as a set of 2- dimensional tables which are known as "relations" in relational database theory. Each table has rows (”tuples”) and columns ("domains"). The relationships between the tables is defined by one table having a column with the same meaning (but not necessarily the same name or value) as a column in another table. For example, let's consider a (very small) Oracle database with just two tables: emp (id number ,name varchar2(30) ,job_title varchar2(30) dept_id number)) holding employee information, and dept(id number ,name varchar2(30)) holding department information. There is an implied relationship between these tables because emp has a column called dept_id which relates to the id column in dept. In Oracle this is usually implemented by what's called a foreign-key relationship which prevents values being stored in the child table (emp in our example) that are not present in the referenced (parent) table (dept in our example). Relational databases obtain their flexibility from being based on relational calculus (similar to set theory) which enables sets or relations to be combined in various ways, including: * join/intersection * union (i.e. the sum of 2 sets); * exclusive "OR" (i.e. the difference between 2 sets) * and outer-join - a combination of intersecting and exclusive or ing. These operations translate into mathematical procedures but fortunately for us, the relational database does all the work behind the scenes, all we as users have to do is specify our requirements in SQL. The intersection (or join) between 2 sets (in this case, tables) produces only those elements that exist in both sets. Therefore, if we join emp and dept on department id, we will be left with only those employees who work for a department that is in the dept table and only those departments which have employees who are in the emp table. The union produces the sum of the tables - meaning all records in emp and all records in dept. including any duplicates.(although duplicates can be discarded if required). Let's use the following data to provide specific examples: emp dept The join of emp and dept on the department id would produce the following result: The union of emp and dept would produce these results: The union operator is only allowed when the number and data types of the columns in the 2 sets are the same. It is normally used to combine sub sections from one or more tables rather than entire tables. There are other operators and variations but this is just an overview and there isn't the space or the time to provide full details in this short Oracle tutorial. The later versions of Oracle database (from Oracle 8 onwards) support both relational and object-oriented features. In this context an object has both attributes and methods (programs stored with the object that performs a certain action or task). Go to part 1 of this Oracle tutorial or see more Oracle tutorials. --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make your systems better, faster and smarter. © Smartsoft Computing Ltd, Bristol, England Contact Us This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy. View Smartsoft privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
| Id | Name | Dept Id |
| 1 | Bill Smith | 3 |
| 2 | Mike Lewis | 2 |
| 3 | Ray Charles | 3 |
| 4 | Andy Mallory | 4 |
| 5 | Mandy Randall | 6 |
| 6 | Allison White | 1 |
| Id | Name |
| 1 | HR |
| 2 | IT |
| 3 | Marketing |
| 4 | Sales |
| 5 | Finance |
| Emp.Id | Emp.Name | Dept.Id | Dept.Name |
| 1 | Bill Smith | 3 | Marketing |
| 2 | Mike Lewis | 2 | IT |
| 3 | Ray Charles | 3 | Marketing |
| 4 | Andy Mallory | 4 | Sales |
| 6 | Allison White | 1 | HR |
| Id | Name |
| 1 | Bill Smith |
| 2 | Mike Lewis |
| 3 | Ray Charles |
| 4 | Andy Mallory |
| 5 | Mandy Randall |
| 1 | HR |
| 2 | IT |
| 3 | Marketing |
| 4 | Sales |
| 5 | Finance |
