5TH TRAINING(RELATIONAL DATABASE COMCEPT) UNISYS
Relational Database Concepts
As an administrator or developer, it's important to understand the object-relational aspects of Oracle Database 12c. Learn about data models, the Entity Relationship Model, and the Oracle HR sample schema.
Table of Contents
Features of Oracle Database 12c
Today we're going to take a look at the main focus areas and features of the Oracle Database 12c. The first focus area is infrastructure grids. Infrastructure grids allow us to use low-cost servers and storage, and still deliver a high quality of service in the areas of high availability, manageability, and performance. In information management, the focus is on content management, information integration, and information lifecycle management. There is now also support for advanced data types such as XML, TEXT, spatial, multimedia, medical imaging, and semantic technology. Looking at application development, Oracle now has the capability to manage and use all major application development environments, such as PL/SQL, Java, .NET, PHP, SQL Developer, and Application Express. The Oracle Cloud is an enterprise cloud for business, and is based on Open Java and SQL standards. It provides an integrated collection of application and platform cloud services.
Heading: Features of Oracle Database 12c.
The focus areas include: Infrastructure grids, information management, application development, and Oracle Cloud.
Infrastructure grids allow for low cost servers and storage, while delivering a high quality of services in high availability, manageability, and performance.
Information management focuses on content management, information integration, information lifecycle management, and advanced data type support for format such as XML, TEXT, spatial, multimedia, medical imaging, and semantic technology.
Application development: Oracle has the capability to use and manage PL/SQL, JAVA/JDBC, .NET and Windows, PHP, SQL DEveloper, and Application Express.
Oracle Cloud – this is an Enterprise cloud for business, which provides integrated applications and platform cloud services. It is based on JAVA and SQL standards.
Looking now at the features of the Oracle Database 12c. Within manageability, the main features are: database replay – this allows you to record a load on one database and replay it on another, for instance during upgrade testing; the SQL performance analyzer – this assesses the performance impact of any system change, which could result in the change to a SQL execution plan; Automated SQL tuning; Real-time database operations monitoring – this monitors composite operations automatically; the Enterprise Manager Database Express 12c is used for managing and monitoring your 12c database. In the area of high availability, Oracle Database 12c focuses on reducing downtime and data loss, on improving online operations, and caters for faster database upgrades. The main features within the performance area are focused on secure files, compression for OLTP (online transaction processing), on RAC optimizations, and in the result cache.
Other features of Oracle Database 12c are manageability. The main features of manageability include database replay, SQL performance analyzer, automatic SQL tuning, real-time database operations monitoring, and Enterprise Manager Database Express 12c.
Another features is high availability, which includes include reduced downtime and data loss, improved online operations, and faster database upgrades.
Performance includes secure files, compression for OLTP, RAC optimizations, and result cache.
The security features include: unique server configurations, focus on data encryption and data masking, and focus on auditing. In information integration, the focus is on integrated data throughout the enterprise and focusing on advanced information lifecycle management. Lastly, the Oracle Fusion Middleware product set contains a portfolio of products that span a range of tools and services, from Java and developer tools, through integrated services, business intelligence, collaboration, and content management. Oracle Fusion Middleware can enable you to maximize the processes and applications that drive your business today, and to provide a foundation for innovation for the future.
The security feature includes unique server configurations, data encryption and masking, and auditing.
Information integration includes integrated data throughout the enterprise and advanced information lifecycle management.
Oracle Fusion Middleware is a portfolio of leading, standards-based, and customer-proven software products. The Development tools span services such as Enterprise Performance management, Business Intelligence, Content Management, SOA & Process Management, Application Server, and Grid Infrastructure in order to provide improved Enterprise Management and Identity Management.
Oracle Cloud and Cloud Control 12c
Enterprise Manager Cloud Control is a management tool providing monitoring and management capabilities for Oracle and non-Oracle components. It provides a complete integrated and business-driven cloud management solution in a single product. By using the Enterprise Manager Cloud Control, you can create and manage a complete set of cloud services, including Infrastructure as a Service, Database as a Service, and Platform as a Service. You can manage all phases of the cloud lifecycle. You can manage the entire cloud stack from application to disk, including engineered systems with integrated capabilities. We can monitor the health of all components, the host they run on, and key business processes supported by these components. We can identify and understand, and resolve business problems through the unified and correlated management of user experience, business transactions, and business services across all packaged and custom applications. The Oracle Cloud is an enterprise cloud for business, providing an integrated collection of application and platform cloud services, based on best-in-class products and on Open Java and SQL standards. Applications and databases deployed in the cloud are portable and can easily be moved to or from a private cloud or an on-premise environment.
Heading: Oracle Cloud and Cloud Control 12c.
By using the Enterprise Manager Cloud Control, you can create and manage a complete set of cloud services, manage all phases of the cloud lifecycle, manage the entire cloud rack, monitor the health of all components, and identify, understand, and resolve business problems.
The illustration of the slide shows that Oracle 12c allows for self-service IT to manage the complete cloud life cycle.
Simple and automated, Oracle Enterprise Manager allows you to create and control the complete stack of cloud services such as applications, middleware, virtual machines, and servers.
Oracle 12c is business-driven and ensures complete integration of user experience management, business transaction management, and business service management across cloud based custom and third-party apps.
The Oracle Cloud is an enterprise cloud for business. It consists of many different services that share some common characteristics.
The five essential characteristics are: on-demand self-service – the focus is on provisioning, monitoring, and management control; resource pooling – implies the sharing and a level of abstraction between consumers and services; rapid elasticity – allows you to quickly scale up or down as needed; measured service – the focus is on metering utilization for either internal charge-back or external billing; broad network access – allows access through a browser on any network device. The Oracle Cloud provides three types of services. Software as a Service – this refers to applications that are delivered to end users over the Internet, for example the Oracle CRM On Demand product. Platform as a Service refers to an application development and deployment platform delivered as a service to developers, allowing them to quickly build and deploy SaaS applications to end users. Typically you will find databases, middleware, and development tools all delivered as a service via the Internet. Infrastructure as a Service refers to the hardware, servers, storage, and network, and will also include the associated software such as operating systems, virtualization, and clustering.
These characteristics are on-demand self-service, resource pooling, rapid elasticity, measured service, and a broad network access.
Oracle Cloud provides three types of services: Software as a service (SaaS), Platform as a service (Paas), and Infrastructure as a service (Laas).
The four types of cloud deployment models are: the private cloud, used by a single organization and is typically controlled and managed, and hosted in a private data center. It can be outsourced to a third-party service provider. A public cloud – multiple organizations use a private cloud on a shared basis, which is hosted and managed by a third-party service provider. The community cloud is where a group of related organizations want to make use of a common cloud environment. The cloud is managed by the participating organizations or by a third-party service provider, and is either hosted internally or externally. Lastly, we look at the hybrid cloud. This is where a single organization wants to adopt both private and public clouds for a single application.
There are four types of Cloud deployment models: community cloud, private cloud, hybrid cloud, and public cloud.
Relational and Object Relational Database
The Oracle server supports both relational and object relational database models, and has extended the data modeling capabilities to provide object-orientated programming, complex and user-defined data types, complex business objects, and provides full compatibility with the relational world. It also supports multimedia and large objects. With high-quality database server features included for performance and functionality, OLTP applications benefit from better sharing of run-time data structures, larger buffer caches, and deferrable constraints. Data warehouse applications benefit from enhancements such as parallel execution of INSERT, DELETE, and UPDATE operations, of partitioning and parallel-aware query optimization. The Oracle model supports client, server, and web-based applications that are both distributed and multi-tiered.
Heading: Relational and Object Relational Database.
The Oracle server supports both the relational model and object relational model. It provides user-defined data types and objects, and is fully compatible with a relational database. It also supports multimedia and large objects and includes high-quality database server features.
Every organization has information which it needs to store. Some examples of these are, a library - would keep a list of members, books, due dates, and fines. Another example would be a company that needs to keep information about its employees, its departments, and salaries. These pieces of information are called data. Data can be stored in various media and in different formats. It could be a hard copy document stored in a filing cabinet, or it could be data stored in an electronic spreadsheet or in a database. Looking at the definition of a database, a database is an organized collection of information. To manage a database, you would need a database management system. This is a program that stores, retrieves, and modifies data in the database on request.
Data can be stored on different media and in different formats, for example electronically on a spreadsheet or database or as a hard copy in a filing cabinet.
There are four types of databases: hierarchical, network, relational, and object relational. The hierarchical database model organizes the data into a tree-like structure. Each parent has one or more child records, also known as a one-to-many relationship. The network database model is similar to a hierarchical database, with the difference being that it has a many-to-many relationship. A relational database model is a collection of data items stored as tables. A table consists of rows and columns from which data can be accessed in many different ways. Almost all relational database systems use SQL, Structured Query Language. This is the language used for querying and maintaining the database. The object relational database management system model is similar to a relational database, but it includes objects, classes, and inheritances. These are supported in the database schema and in SQL.
There are four types of databases: hierarchical, network, relational, and object relational.
In this final slide, we look at the features of a relational and object relational database management system. A relational database management system distinguishes between two types of operations, a logical operation – an application specifies what data is required. An example of this would be where an application requests a customer name, or would add a new customer record to a table. The second is a physical operation. The database determines how the request should be handled, and then carries out the operation. An example of a physical operation would be where the application queries a table, an index is used to find the requested rows in the table, and the data is read into memory. These physical operations are transparent to the application. The object relational database management system makes use of the object-orientated database model. It supports objects, classes, and inheritance. In today's lesson, we looked at the different types of databases, and focused on relational and object relational database management systems as supported by the Oracle server.
Relational Database Management System, or RDBMS, distinguishes between a logical operation and a physical operation.
Object Relational Database Management System, or ORDBMS, makes use of the Object-orientated database model, which supports objects, classes, and inheritance.
Relational Database Concepts
Dr. E. F. Codd first outlined the principles of the relational model in June 1970, in a paper titled, A Relational Model of Data for Large Shared Data Banks. It was in this paper that Dr. Codd proposed the relational model for database systems. Common models used at the time were hierarchical and network, and even simple flat file data structures. Soon relational database management systems became popular, especially for their ease of use and flexibility in structure. The relational model consists of the following components: a collection of objects or relations that store data; a set of operators that can act on the relations to produce other relations; data integrity for accuracy and consistency.
Heading: Relational Database Concepts.
Dr. E. F. Codd proposed the relational model for database systems in 1970. His paper was the basis for the relational database management systems (RDBMS).
The relational model consists of a collection of objects or relations, a set of operators to act on the relations, and data integrity for accuracy and consistency.
A database management system has the following elements: the first - kernel code. The kernel code manages the memory and storage for the database system. The second - repository of metadata. In Oracle, the metadata repository is called a data dictionary. The data dictionary is structured in tables and views, and contains information about the database, such as the definition of all schema objects, for example, tables, views, indexes, your procedures, packages, triggers, etc. It contains the space allocated for, and currently being used by, your schema objects. It contains default values for columns. It contains integrity constraint information. It contains the names of the Oracle users and their privileges and roles that are granted to them. Auditing information is stored in the data dictionary, as well as other general database information. The third element is query language. This language enables applications to access the data. In the case of Oracle, SQL is used, Structured Query Language.
A database management system has kernel code, a repository of metadata, and query language.
The major aspects of the relational model are: structures – well-defined objects store or access the data of a database; operations – clearly defined actions enable applications to manipulate the data and the structure of the database; and integrity rules – integrity rules govern operations on the data and on the structures of the database. In our final slide, a relational database is a collection of relations or two-dimensional tables that are controlled by the Oracle server. So in the example on this slide, we see we have an EMPLOYEES table, which contains information pertaining to the employee. In the DEPARTMENTS table, we have information that pertains specifically to the DEPARTMENTS table. In today's lesson, we looked at the components of the relational model, and we looked at the elements that make up the database management system.
The major aspects of the relational model are structures, operations, and integrity rules.
A relational database is a collection of relations or two-dimensional tables controlled by the Oracle server.
The illustration depicts the server as having two tables: the EMPLOYEES table and the DEPARTMENTS table.
The EMPLOYEES table has four columns: EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and EMAIL.
The DEPARTMENTS table has three columns: DEPARTMENT_ID, DEPARTMENT_NAME, and MANAGER_ID.
Data Models
Models are the cornerstone of design. As engineers would build a model of a car to work out any details before putting it into production, in the same manner, system designers develop models to explore ideas and to improve the understanding of the database design. A model helps to communicate concepts that are in people's minds, and can be used for the following: to communicate, categorize, describe, specify, investigate, evolve, analyze, and initiate. The objective is to produce a model that fits a multitude of these uses. The model should be understood by the end user, and contain sufficient detail for a developer to build a database system. A data model is the first step in a database design. And the data model starts with a conceptual model, it then evolves into a logical model, and then finally into a physical schema. A conceptual model is made up of concepts, and it is used to help people understand and simulate the subject that is represented by the model. A logical model describes in as much detail as possible, the data. This is based on the conceptual model. The logical model includes entities and the relationship among them, including the attributes for each entity.
Heading: Data Models.
The slide shows the development of a data model from a model of a system in the client's mind, to an entity model based on the client's model. Next is the table model of the entity model, and finally the table on disk in the Oracle server.
The data model is the first step in database design. The data modeling progression starts with the conceptual model and proceeds to the logical model.
Lastly we build a physical schema that is based on a logical model. A physical schema is the description of how data is stored in the database management system, for example, in tables, indexes, constraints, and so on. The Oracle SQL Developer Data Modeler is a free tool provided with SQL Developer. It is provided to enhance productivity and to simplify data modeling tasks. Looking now at some of the features of the Oracle SQL Developer Data Modeler; with the Data Modeler users can create, browse, and edit logical, relational, physical, multi-dimensional, and data type models. One can forward engineer a logical model into a physical model, and reverse engineer by taking a physical model and generating a logical model. The Oracle SQL Developer Data Modeler can be used in both a traditional and cloud computing environment. In this video, we covered the data model, describing the three main components: the conceptual model, the logical model, and physical design.
The last step in the data modeling progression is to create a physical schema.
The Oracle SQL Developer Data Modeler is a free graphical tool that enhances productivity and simplifies the data modeling task.
The features include the ability for users to create, browse, and edit logical, relational, physical, multi-dimensional, and data type models.
It has forward and reverse engineering capabilities and it can be used in both a traditional and in cloud environments.
Entity Relationship Model
In an effective system, data is divided into discrete categories or entities. An entity relationship model is an illustration of the various entities in a business and the relationships among them. An ER model is derived from business specifications or narratives, and is built during the analysis phase of the system development lifecycle. ER models separate information required by a business from the activities performed within the business. Although businesses can change their activities, the type of information tends to remain constant. Based on that, the data structures tend to remain constant. Some of the benefits of ER modeling are: it documents information for the organization in a clear precise format; it provides a clear picture of the scope of the information required; it provides an easily understood pictorial map for database design, and lastly, it offers an effective framework for integrating multiple applications.
Heading: Entity Relationship Model.
The slide contains the instruction: create an entity relationship diagram from business specifications or narratives.
Two scenarios are listed: "...Assign one or more employees to a department..." and "...Some departments do not yet have assigned employees..."
The slide contains two boxes: one is labeled EMPLOYEE, the other is labeled DEPARTMENT. Each box has three symbols and the meaning of the symbol.
The first symbol is a hash tag with an asterisk, which indicates number. The next is an asterisk, which indicates name, and lastly the letter O in lowercase, which has a different meaning in the EMPLOYEE box and in the DEPARTMENT box.
In the EMPLOYEE box, the lowercase o represents job title, in the DEPARTMENT box it represents location.
The EMPLOYEE box is on the left and is connected via a crows foot and a dashed line to the DEPARTMENT box. Next to the EMPLOYEE box is written "assigned to" and next to the DEPARTMENTS box is written composed of.
The benefits of ER modeling are that it documents information for the organization in clear, precise format, provides a clear picture of the scope of the information requirement, provides an easily understood pictorial map for database design, and offers an effective framework for integrating multiple applications.
The components in an ER model are: an entity is an aspect of significance about which information must be known. Examples are departments, employees, and orders. The attribute is something that describes or qualifies an entity. For example, for the EMPLOYEE entity, the attribute will be the employee number, name, job title, hire date, department number, and so on. Each of the attributes is either required or optional. This state is called optionality. A relationship is a named association between entities, showing optionality and degree. An example is the relationship between the EMPLOYEES and DEPARTMENTS table. To represent an entity in a model, use the following conventions: a singular, unique entity name (entity name must be in uppercase); use a soft box; optional synonym names in uppercase with parentheses. To represent an attribute, use the following conventions: a singular name in lowercase; an asterisk tag for mandatory attributes – these values must be known; a capital letter O tag for optional attributes – these values may be known.
The key components in a ER model are an entity, attributes, relationship.
Entity relationship modeling conventions.
An Entity is represented by a singular, unique name, uppercase letters, a soft box, and a synonym in parenthesis.
An Attribute is represented by a singular name, lowercase letters, mandatory attributes are marked with an asterisk, and optional ones are marked with an uppercase letter "O."
Looking now at relationships, each direction of the relationship contains the following: a label – an example would be "taught by" or "assigned to;" an optionality – either it must be or may be; and thirdly, a degree – either one and only one, or one or more. The symbols in an ER model are: dashed line – this defines an optional element indicating maybe; solid line – defines a mandatory element indicating must be; the crow's foot - the degree element indicating one or more; a single line – the degree element indicating one and only one.
Entity relationship modeling conventions.
The slide contains two entities: one is labeled EMPLOYEE, the other is labeled DEPARTMENT.
Each entity contains three attributes represented by symbols: a hash tag with an asterisk, an asterisk, and the letter o. In the EMPLOYEE entity these symbols represent the employee number, name, and job title, in the DEPARTMENT entity they represent the department number, name, and location.
The EMPLOYEE box is connected to the DEPARTMENT box via a crows foot consisting of two solid lines and a dashed line. The EMPLOYEE entity has a label "assigned to" and the DEPARTMENT entity is labeled composed of.
Some final notes, the term cardinality is a synonym for the term degree. Each source entity may or must be in a relation to one and only one, or one or more, with the destination entity. When reading an ER diagram, the convention is to read clockwise. A unique identifier is a combination of attributes or relationships, or both, that serve to distinguish occurrences of an entity. And lastly, each entity occurrence must be uniquely identifiable. Tag each attribute that is part of the UID with a hash sign, and tag the secondary UIDs with a hash sign in parentheses. In this lesson, we described an entity relationship model, we looked at the components of an ER model, and the conventions.
A message box below the diagram notes that the UID Primary is marked with a hash tag and a secondary will be marked with a hash tag within parenthesis.
Relating Multiple Tables
Each table contains data that describes exactly one entity. An example of this is the EMPLOYEES table. It contains information about the employees. Categories of data are listed across the top of each table. For example, in the EMPLOYEES table this would be the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and DEPARTMENT_ID. The individual cases are listed below. An example of this would be the EMPLOYEE_ID is 100; the FIRST_NAME, Steven; LAST_NAME, King; and the DEPARTMENT_ID of 90. Each row of data in a table can be uniquely identified by a primary key. By using a table format, you can readily visualize, understand, and use information. Data about different entities is stored in different tables, and you may need to combine two or more tables to answer a particular question. For example, you may want to know the location of the department where an employee works. To do this, you need information from the EMPLOYEES table and the DEPARTMENTS table.
Heading: Relating Multiple Tables.
Each row in a table can be uniquely identified by a primary key.
There are two tables on the slide: EMPLOYEES and DEPARTMENTS.
The EMPLOYEES table has four columns: EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and DEPARTMENT_ID. An example row is:
100: Steven: King: 90.
The DEPARTMENTS table has four columns: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID.
In a relational database, we can relate data in one table to data in another table by the use of foreign keys. A foreign key is a column or set of columns that refers to the primary key in the same, or another table. The example in this slide shows the EMPLOYEES and DEPARTMENTS table. The DEPARTMENTS table has a primary key on the DEPARTMENT_ID column. In the EMPLOYEES table, we have a column containing the DEPARTMENT_ID to which the employee belongs. We create a foreign key on this DEPARTMENT_ID, which in turn refers back to the primary key on the DEPARTMENTS table. The ability to relate data in one table to data kept in another, allows you to organize information into separate manageable units. The employee data can be kept logically distinct from the department data by storing it in separate tables.
You can logically relate data from multiple tables using foreign keys.
In the EMPLOYEES table, the EMPLOYEE_ID column is labeled primary key and the DEPARTMENT column is labeled foreign key.
In the DEPARTMENTS table, the DEPARTMENT_ID column is labeled primary key.
Some guidelines when creating primary and foreign keys: You cannot have duplicate values in a primary key. The primary key generally cannot be changed. Foreign keys are based on data values and they are purely logical pointers. A foreign key value must match an existing primary key value or unique key value, otherwise it should be null. And lastly, a foreign key must reference either a primary key or a unique key column. In our SQL Developer session, we have opened up the definition of the EMPLOYEES table, and we clicked on the Model tab. This screen shows the entity relationship diagram, showing all relationships between tables and the EMPLOYEES table. Between the EMPLOYEES table and the DEPARTMENTS table, we have a one-to-many relationship. We have a foreign key constraint for DEPARTMENT_ID on the EMPLOYEES table, and this references the primary key for the DEPARTMENTS table. In this video, we've covered how multiple tables relate to each other.
Some guidelines when creating primary and foreign keys are: you cannot use duplicate values in a primary key, primary keys generally cannot be changed, and foreign keys are based on data values and are purely logical (not physical) pointers.
A foreign key value must match and existing primary key value or unique key value, otherwise it must be null. Lastly a foreign key must reference either a primary key or a unique key column.
SQL Developer is open. The HR schema is open in the Connections navigator and the Tables folders has been expanded. The EMPLOYEES table is selected and the contents are displayed in the Worksheet area.
The Model tab is selected and the entity relationship diagram is show on the page. There are four tables in the diagram: JOB_HISTORY, JOBS, DEPARTMENTS, and EMPLOYEES.
The presenter focuses on the EMPLOYEES and DEPARTMENTS table. The EMPLOYEE table has 11 items: EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, and DEPARTMENT_ID.
The EMPLOYEE, LAST_NAME, EMAIL, HIRE_DATE, and JOB_ID are marked with an asterisk. The EMPLOYEE_ID is marked as the primary key and JOB_ID. MANAGER_IS, and DEPARTMENT_ID are marked as foreign keys.
The DEPARTMENTS table has four items: DEPARTMENT_ID. DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID.
DEPARTMENT_ID and DEPARTMENT_NAME are marked with as asterisk. DEPARTMENT_ID is the primary key and MANAGER_ID is the foreign key.
There is a crow foot made up of three sold lines, and a dashed line pointing from the EMPLOYEES table to the primary key box in the DEPARTMENTS table, that is the DEPARTMENT_ID.
There is the same type of arrow pointing from the DEPARTMENTS table to the EMPLOYEES table.
Relational Database Terminology
In this lesson, we are going to discuss relational database terminology. In an Oracle relational database, we have a user - the HR user, and the HR user owns objects such as tables, indexes, and so on. When the HR user creates objects within the database, the metadata is known as a schema. A schema defines attributes of the database, such as tables, columns, and properties. A schema will exist per database user that has created objects in the database. A relational database can hold one or more tables. A table is the basic storage structure of a relational database and holds all the data necessary about something in the real world, such as employees, invoices, and customers.
Heading: Relational Database Terminology.
An HR User owns data, which is placed into tables, which are placed into a database. These tables, along with other objects in the database, are known as schema objects. These objects make up the HR schema.
Tables are the basic storage structure of a relational database and are used to hold all of the data about something in the real world. For example, data relating to the employees in a company.
In this slide, we show the contents of the EMPLOYEE table. The numbers in the slide indicate the following: number one shows a single row, representing all the data required for a particular employee. Each row in a table should be identified by a primary key. A primary key permits no duplicate rows. The order of the rows is insignificant, as we will specify the row order when the data is retrieved. Number two shows a column or attribute containing the employee number. This number identifies a unique employee in the EMPLOYEES table. In this example, the employee number is designated as the primary key. A primary key must contain a value, and the value must be unique. Number three shows a column that is not a key value. A column represents one kind of data in a table. In this example, the data is the salaries of all employees. Number four shows a column containing the department number, which is also a foreign key. A foreign key is a column that defines how tables relate to each other and refers to a primary key or unique key in the same table, or in another table. In this example, the DEPARTMENT_ID uniquely identifies a department in the DEPARTMENTS table. In number five, a field can be found at the intersection of a row and a column, and there can only be one value in it.
The contents of the EMPLOYEE table are shown on the slide. The table has six columns: EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT, and DEPARTMENT_ID.
There are five key elements to a table. Number 1 is a row. A row travels horizontally across the table intersecting each column.
Number 2 is a column. In this example the EMPLOYEE_ID column is highlighted. Each field in the column contains a unique identifying number positioned next to the employee identified in the FIRST_NAME column. This number is known as the primary key.
Number 3 is also a column. In the example the SALARY column is highlighted but this column contains salary data rather than key data.
Number 4 is also a column. This time is the DEPARTMENT_ID column. This column contains a unique number that correspond to the departments that the employee works in.
The number in this column corresponds to the name of the department, which can be found by looking it up in the DEPARTMENTS table. This is known as a foreign key.
Number 5 is a field. A field is where a row and a column intersect. A field contains a single value that relates to the column header.
In this slide, we give an introduction to some of the Oracle Database objects. The first is an index. An index is a database object intended to improve the performance of SELECT queries. Next we have partitions. Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces. Each piece of the database object is called a partition. Each partition has its own name and may optionally have its own storage characteristic. Views – a view is a representation of a SQL statement that is stored in memory, so that it can be reused. A view is a logical entity or logical table that is essentially a SQL statement stored in the database in the SYSTEM tablespace. Sequences – the Oracle SEQUENCE function allows you to create auto-numbering fields by using sequences. An Oracle Sequence is an object that is used to generate incrementing or decrementing numbers.
Schema object types include indexes, partitions, views, sequences, synonyms, and PL/SQL subprograms and packages.
Next synonyms. In Oracle PL/SQL, the term synonym refers to schema object, which is created by a user to access an object that is owned by another user. In the process of creating a synonym, the user seeking the object access must first create a synonym for the required object. The owner user must then grant access on the synonym to the seeking user. Lastly, we look at PL/SQL subprograms and packages. In PL/SQL, a package is a group of programmatic constructs combined into a single unit. In this lesson, we discussed the relational database terminology, we looked at a table structure, and we introduced some Oracle database objects.
Using SQL to Query a Database
Today's lesson is an introduction to SQL and the development environment that will be used in the learning path. In a relational database, we do not specify the access route to tables and we do not need to know how the data is arranged physically. SQL is a set of statements which all programs and users can use to access data in the Oracle Database. SQL is an ANSI standard for using relational databases and is compliant with the ISO standard SQL:1999. It is efficient and easy to learn and use. Applications in Oracle tools allow users to access the database without using SQL directly. These applications in turn must use SQL when it executes the user's request. The tasks that can be performed using SQL: these include querying data, insert, update, and delete of rows in a table; we can create, replace, alter or drop objects; we can control the access to the database and its objects. SQL statements guarantee database consistency and integrity. SQL combines all the tasks listed into one consistent language, and it thereby enables you to work with data at a logical level.
Heading: Using SQL to Query a Database.
SQL stands for Structured Query Language and is the ANSI standard language for operating relational databases. It is efficient and easy to learn and use, and is functionally complete (with SQL you can define, retrieve, and manipulate data in tables).
The illustration on the slide shows an Oracle server. The following SQL command is applied on the server:
SELECT department_name
FROM departments;
The server responds by outputting a column labeled DEPARTMENT_NAME, which contains a list of the different departments such as Administration and Marketing.
The SQL statements supported by Oracle comply with industry standards and Oracle ensures future compliance, by actively involving key personnel in the SQL standards committee. These industry-accepted committees are ANSI and ISO. Both have accepted SQL as the standard language for relational databases. Looking now at the SQL statements, the SELECT; INSERT; UPDATE; DELETE; and MERGE statements. These retrieve data from the database, enter new rows, change existing rows, and remove unwanted rows from tables in the database. And it is collectively known as Data Manipulation Language, or DML. The second set of statements, CREATE; ALTER; DROP; RENAME; TRUNCATE; COMMENT. These set up, change, and remove data structures from tables. It is collectively known as Data Definition Language, or DDL. The GRANT and REVOKE statements provide or remove access rights to both the Oracle Database and its structures, also known as Data Control Language, DCL. The COMMIT, ROLLBACK, and SAVEPOINT statements; these manage the changes made by DML statements. Changes to the data can be grouped together into logical transactions.
A table divides the SQL statements into four types: Data manipulation language (DML), Data definition language (DDL), Data control language (DCL), and Transaction control.
The statements under DML are SELECT, INSERT, UPDATE, DELETE, and MERGE.
Under DDL, the statements are CREATE, ALTER, DROP, RENAME, TRUNCATE, and COMMENT.
The statements under DCL are GRANT and REVOKE.
Under Transaction control you find the COMMIT, ROLLBACK, and SAVEPOINT statements.
In this learning path, we will make use of two development environments provided by Oracle. The first is SQL Developer. This will be the default tool that we will use for running the SQL statements in our learning path. The second is SQL*Plus. SQL*Plus can also be used to run all SQL commands covered in this learning path. We will learn the features and functions of both SQL Developer and SQL*Plus in upcoming lessons in the learning path. In today's lesson, we provided an introduction to SQL and the development environments that will be used throughout this learning path.
There are two development environments for this course: SQL Developer and SQL*Plus.
Human Resources Schema
In today's lesson, we're going to discuss the sample schemas provided with the Oracle Database, and specifically the HR schema which we will use throughout this learning path. The Oracle Database provides a set of interlinked sample schemas. Some of these schemas are HR, OE, OC, PM, IX, and SH. The sample schemas are based on the following design principles: simplicity and ease of use, relevance for typical users, and on extensibility. These sample schemas are based on a fictitious sample company that sells goods through various channels. The company operates worldwide to fill orders for products and has several divisions, each of which is represented by a sample schema in the database.
Heading: Human Resources (HR) Schema.
The Oracle database provides a number of sample schemas including HR, OE, OC, PM, IX, and SH.
These are based on the design principles: simplicity and ease of use, relevance for typical users, and extensibility.
The HR schema is the human resources division, and it tracks information about the company employees and facilities. Next is the OE schema, this is the order entry division, and it tracks product inventories and the sales of products through various channels. The PM schema is the product media division, and it maintains description and detailed information about each product sold by the company. The IX schema is the information exchange division; it manages shipping through B2B applications. The last schema we will discuss is the SH schema. This is the sales division and it facilitates business decisions by tracking business statistics. Depending on how the database is created, these schemas may or may not exist in the database. It is not recommended for these schemas to be created in a production environment. Should the sample schemas not exist, they can be installed manually, taking into consideration that due to dependencies between the schemas, they will need to be created in a specific sequence. Refer to the sample schemas guide in the Oracle Database online documentation, for how to install these schemas.
In this learning path, we will make use of the Oracle human resources sample schema. The HR schema consists of the following tables: we start with the REGIONS table; this contains rows representing regions – for example, America and Asia. The COUNTRIES table contains rows for each country associated with a region. In the LOCATIONS table, this contains address of specific offices, warehouses, and production sites of the company in a particular country. The DEPARTMENTS table shows details about the departments in which the employees work. The EMPLOYEES table contains details about each employee working in a department; some employees may not be assigned to any department. The JOBS table contains job types that can be held by an employee. Lastly, we'll look at the JOB_HISTORY table; this contains a job history for the employees. When an employee changes departments within a job or changes jobs within a department, a new row is inserted into this table.
The HR schema consists of seven tables. The REGIONS table contains the region_id and region_name information. The region_id is highlighted. The REGIONS table has a one-to-many link to the COUNTRIES table, which contains country_id, country_name, and region_id information. Country_id is highlighted.
The COUNTRIES table has a one-to-many relationship to the LOCATIONS table, which holds the location_id, street address, postal code, city, state province, and Country_id. Location_id is highlighted.
The LOCATIONS table has a one-to-many relationship to the DEPARTMENTS table. DEPARTMENTS contains the departments_id, departments name, manager_id, and location_id information. Department_id is highlighted.
The DEPARTMENTS table has a many-to-one relationship to the EMPLOYEES table, which contains the employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission-pct, manager_id, and department_id information. Employee_id is highlighted.
The EMPLOYEES table has a many-to-one relationship back to the DEPARTMENTS table. Both the DEPARTMENTS table and the EMPLOYEE table has a one-to-many relationship to the JOB_HISTORY table, which contains the employee_id, start_date, end_date, job_id, and department_id data. The Job_id is highlighted.
The EMPLOYEES table and the JOB_HISTORY table both have a many-to-one relationship to the data in the JOBS table, which has the job_id, job_title, min_salary, and max_salary information.
The JOB_HISTORY table contains employee_id, start_date. end_date, job_id, and department_id information. The employee_id and start_date are highlighted.
Looking now in a bit more detail at some of the tables, the first table we will look at is the COUNTRIES table. In the top block you will see the definition of the table, and in the bottom block you will see the data that exists in this table. So in the country table we have a COUNTRY_ID, we have a COUNTRY_NAME, and the REGION_ID. The next table is the DEPARTMENTS table. In the DEPARTMENTS table we have the DEPARTMENT_ID, the DEPARTMENT_NAME, the MANAGER_ID, and the LOCATION_ID. The last table we will look at is the EMPLOYEES table. This table holds all the information specific to an employee, such as your employee ID, their first and last name, their e-mail address, a phone number for the employee, a hire date, a job ID, their salary, a commission percentage, the manager ID, and the department ID. In today's lesson, we discussed the Oracle provided sample schemas, and looked at the HR schema, which we will be using throughout this learning path.
The Countries table.
There are two blocks on the slide: the definition table and the data table.
The definition table has three columns: Name, Null, and Type. There are three items in the Name column: COUNTRY_ID, COUNTRY_NAME, and REGION_ID.
The data table has three columns: COUNTRY_ID, COUNTRY_NAME, REGION_ID. There are four items in the table. The values are as follows:
1: CA: Canada: 2.
2: DE: Germany: 1.
3: UK: United Kingdom: 1.
4: US: United States of America: 1.
The departments table.
The definition table has four items in the name column: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, and LOCATION_NAME.
The data table for departments has four columns: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID.
The employees table.
The definition table has 11 items: EMPLOYEE_ID. FIRST_NAME, LAST_NAME. EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, and DEPARTMENT_ID.
The data table has 11 columns that reflect the items in the definition table.
Comments
Post a Comment