Database Programming with SQL PDF

Database Programming with SQL 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Objectives This lesson covers the following objectives: −Define and give an example of a relational database −Identify table-k...

Database Programming with SQL 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. Objectives This lesson covers the following objectives: −Define and give an example of a relational database −Identify table-key terms, including row, column, field, primary key, and foreign key −Relate the importance of databases to everyday life DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 3 Purpose Databases are part of our everyday lives even though most of the time we don't even think about them If you have ever made an airline reservation, used an ATM machine, or made a mobile-phone call, you've used a database In fact, many cities use intelligent traffic guiding system databases to control stoplights So the next time you're waiting at a red light, it may be a database that is responsible for your delay! In this lesson, you will learn more about databases and how they're organized and created DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 4 Relational Databases A relational database allows tables to be related by means of a common field As few as two tables can be considered a relational database if they share a common field COUNTRY_ID COUNTRY_NAME REGION_ID CA Canada 2 DE Germany 1 UK United Kingdom 1 US United States of America 2 DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 5 Relational Databases Realistically, databases used in business have many tables, each table sharing a common field with another table The "countries" table shown is one of several tables in the Employees database and just one example of the many tables that will be used in this course COUNTRY_ID COUNTRY_NAME REGION_ID CA Canada 2 DE Germany 1 UK United Kingdom 1 US United States of America 2 DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 6 Relational Databases To understand how important databases have become in today's world, consider the following statistics: −Currently 20% of the world's data resides in RDBMSs −In the next two years, databases are expected to grow larger than 100 terabytes −A database this big would be able to store 100,000 copies of the Encyclopedia Britannica or 200,000 hours of music or about 10 billion web pages DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 7 Relational Databases Some of the top 10 world's largest databases using the Oracle RDBMS are: −France Telecom, 29.2TB -- a communications company (a TB is a terabyte equivalent to 1,000 gigabytes) −Amazon.com with, 13 TB -- selling books and merchandise −The Claria Corporation,12TB -- Internet behavioral marketing company tracking Internet user behavior DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 8 Review Key Terms Let's review the following key terms: −table -- basic storage structure −column -- one kind of data in a table −row -- data for one table instance −field -- the one value found at the intersection of a row and a column −primary key -- unique identifier for each row −foreign key -- column that refers to a primary-key column in another table DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 9 Properties of Tables There are six properties of tables in a relational database: −Property 1: Entries in columns are single-valued −Property 2: Entries in columns are of the same kind −Property 3: Each row is unique −Property 4: Sequence of columns is insignificant −Property 5: Sequence of rows is insignificant −Property 6: Each column has a unique name DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 10 Accessing Data in an RDBMS A relational database-management system (RDBMS) organizes data into related rows and columns To access the data in a database, you do not need to know where the data is located physically, nor do you need to specify an access route to the tables You simply use structured query language (SQL) statements and operators DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 11 Communicating with Databases Working with the database is very similar to calling up and talking to a friend on the phone −First, you must choose a method to communicate (the phone) −Once connected, you ask your friend a question (a query) −In response to your question, your friend answers (return of data) Pretty simple, and most of us are experts at this In this class, our method of communication with the database will be through Oracle Application Express When you ask a question using SQL, the application will return an answer DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 12 Communicating With Databases As shown in the diagram, communicating with an RDBMS is accomplished by entering a SQL statement in Oracle Application Express SQL statement is entered Statement is sent to the Oracle Server SELECT department_name FROM departments; Oracle server Data is returned from the Oracle Server DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 13 Communicating With Databases The request is then sent to the Oracle Server (a database running on a computer), the request is processed and the data returned is displayed SQL statement is entered Statement is sent to the Oracle Server SELECT department_name FROM departments; Oracle server Data is returned from the Oracle Server DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 14 Communicating With Databases In very large database systems, many users, servers, and tables make up the RDBMS SQL statement is entered Statement is sent to the Oracle Server SELECT department_name FROM departments; Oracle server Data is returned from the Oracle Server DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 15 Categories of SQL Statements SQL statements are grouped into several categories depending on the functions they perform During this course, you will learn how to use SQL to execute these statements The data retrieval statement retrieves data from the database using the keyword SELECT DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 16 Categories of SQL Statements There are four main categories of SQL statements: −Data manipulation language (DML) −Data definition language (DDL) −Transaction control language (TCL) −Data control language (DCL) DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 17 Categories of SQL Statements Data manipulation language (DML) −DML statements begin with INSERT, UPDATE, DELETE, or MERGE and are used to modify the table data by entering new rows, changing existing rows, or removing existing rows Data definition language (DDL) −DDL statements create, change, and remove data structures from the database −The keywords CREATE, ALTER, DROP, RENAME, and TRUNCATE begin DDL statements DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 18 Categories of SQL Statements Transaction control language (TCL) −TCL statements are used to manage the changes made by DML statements −Changes to the data are executed using COMMIT, ROLLBACK, and SAVEPOINT −TCL changes can be grouped together into logical transactions Data control language (DCL) −DCL keywords GRANT and REVOKE are used to give or remove access rights to the database and the structures within it DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 19 Terminology Key terms used in this lesson included: −Data control language (DCL) −Data definition language (DDL) −Data manipulation language (DML) −Field −Foreign key −RDBMS DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 20 Terminology Key terms used in this lesson included: −Primary key −Relational database −Row −Table −Transaction control (TCL) DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 21 Summary In this lesson, you should have learned how to: −Define and give an example of a relational database −Identify table-key terms, including row, column, field, primary key, and foreign key −Relate the importance of databases to everyday life DP 1-2 Relational Database Technology Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 22