1 / 24

Introduction to Relational Databases - SQL and Oracle

Learn the basics of relational databases, SQL language, and Oracle database management system. Understand data modeling, normalization, and potential dangers in a relational database.

ltaylor
Download Presentation

Introduction to Relational Databases - SQL and Oracle

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Department of Computer and Information Science,School of Science, IUPUIFall 2003 Introduction to Relational Databases Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu

  2. Sharing Knowledge and Success • Oracle is a relational database management system (RDBMS). • RDBMSs allow users to... • put data in • keep/manage the data • get data out and work with it

  3. Sharing Knowledge and Success • The Language of Oracle: SQL • Structured Query Language, English like query language • keywords: select, from, where, and order by • Example query:select city, temperature from weather where temperature > 80 order by temperature; • Examples of relational databases • stock tables in newspaper • sports scores • 100 year example in book

  4. Database Management • Disadvantages of traditional file processing systems… • uncontrolled redundancy • inconsistent data • inflexibility • limited data sharing • poor enforcement of standards • low programmer productivity • excessive program maintencancefrom Database Management, McFadden and Hoffer, pp. 8-14

  5. Database Management: Data Models • Hierarchical Data Model (IMS) • nested sets of 1:1 or 1:M relationships • Network Data Model (IDMS, TOTAL) • multiple sets of 1:1, 1:M, M:1, or M:N relationships • Relational Data Model (Oracle, SQL Server, DB2) • relationships are NOT physically implemented • uses primary keys to represent associations • terminology: relations (tables), columns, tuples (rows), domain, degree, cardinality, primary keys, concatenated keys, alternate keys, foreign keys, Referential Integrity or R.I. • relational algebra, three main operators: select, project, join • normalization theory:1NF, 2NF, 3NF

  6. Database Management: Normalization • First Normal Form (1NF) • Each column contains values about the same attribute, and each table cell value must be a single value. • Each column has a distince name, order of columns is immaterial. • Each row is distinct, rows cannot be duplicate for the same key • The sequence of rows is immaterial. • Second Normal Form (2NF) • All non-key attributes must be fully dependent on the whole key. • Third Normal Form (3NF) • Each nonkey attribute should be dependent only on the relation’s key, not on any other nonkey.

  7. The Dangers in a Relational Database • It looks very easy to use a RDBMS • learning about normalization, SQL, etc. make for instant “experts.” • lack of experience with major production systems can create catastrophic project failures. • Testing cycles are getting shorter • newer development tools make software development quicker, usually systems testing gets shortened. • Recent college grads... • least experienced developers usually have more training with relational database technology. • veteran developers are busy with older projects.

  8. The Dangers in a Relational Database • How to reduce the confusion… • Normalization1NF:2NF:3NF: English names for tables and columns, English code names

  9. The Dangers in a Relational Database

  10. The Dangers in a Relational Database • Bad Examples of Table and Column Names: • TablesDEPT EMP EMPS MYEMPSPE PROJ TITLES PERSONNEL • ColumnsAD1 AU_LNAME AU_ORD BLOC CDLEXP DEPTNO DNAME DISCOUNTTYPEEMPNO ENAME ENUMBER ESALHIGHQTY HIRANGE LORANGE LOWQTYNOTE ORD_NUM PNAME PROJNOPUBDATE QTYOH SLSTAXPCT WORKHRS • Reasons • abbreviation used without good reason • inconsistent abbreviations, underlines, and use of plurals • purpose not apparent from name • name rules have limitations

  11. Chapter 2: The Dangers in a Relational Database • Use English Name for Data: • Poor Example:Better Example:

  12. The Basic Parts of Speech in SQL • SQL is a language. Oracle7 SQL is a superset of the American National Standards Institute (ANSI) and the International Standards Organization (ISO) SQL92 standard at entry level conformance. • PL/SQL is Oracle’s procedural language extension to SQL. It allows you to link several SQL commands through procedural language. • SQL*Plus (SQLPLUS from command line) is a tool that allows users to interact with Oracle. SQL*Plus enables you to manipulate SQL commands and PL/SQL blocks, and to perform many additional tasks as well. Through SQL*Plus, you can: • enter, edit, store, retrieve, and run SQL commands and PL/SQL blocks • format, perform calculations on, store, and print query results in the form of reports • list column definitions for any table • access and copy data between SQL databases • send messages to and accept responses from an end user

  13. The Basic Parts of Speech in SQL • SQL*Plus,… very quick overview • first, create/run a script to start an Oracle database instance • from the command line:sqlplusSQL> • Common commands in SQL*Plus: • get <filename> • @<filename> • save <filename> • list or l • change or c • edit • save • ! (shell to OS) • Capitalization generally doen’t matter

  14. The Basic Parts of Speech in SQL • Relational Algebra: • manipulates 1 or 2 relations (tables) and returns 1 new relation as a result. • basic relational algebra operators are SELECT, PROJECT and JOIN. • SELECT returns a horizontal subset of a relation. • PROJECT returns a vertical subset of a relation • JOIN returns the combination of 2 relations based on common attributes.from Database Management, McFadden and Hoffer, pp. 214-216

  15. The Basic Parts of Speech in SQL • Select, Project, Select and Project • Join

  16. The Basic Parts of Speech in SQL • Simple Select Examples • Select statement, single table query, all rows: • SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • ORDER BY CUSTOMER_NUMBER; • ... specific rows (where clause): • SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE CUSTOMER_NUMBER > 500; • ... with a calculation: • SELECT CUSTOMER_NUMBER, LAST, (CREDIT_LIM - BALANCE) • FROM CUSTOMER • WHERE CUSTOMER_NUMBER > 500; • ... multiple where conditions (and): • SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE BALANCE >= 500 • AND BALANCE <= 1000;

  17. The Basic Parts of Speech in SQL • The SELECT clause: • can be a list of columns, • an asterik * for all columns, • a calculation (or other expressions), • group functions (chapter 9), • distinct keyword -- removes duplicates. • SELECT DISTINCT SUPPLIER • FROM PRODUCTS; • column names can be renamed in the output with an alias.SELECT CUSTOMER_NUMBER, LAST, FIRST, (CREDIT_LIM - BALANCE) AS REMAINING_CREDIT • FROM CUSTOMER; • The FROM clause: • in single tables queries, just provide your table’s name …. more on this in multi-table queries.

  18. Chapter 3: The Basic Parts of Speech in SQL • The WHERE Clause: • this is a logical, boolean expression which must evalute to true for each row in the query’s output. • where clause comparison operators: • = equal to • <, > less than, greater than • <=, >= less than or equal, greater than or equal • <>, != , ^= not equal • compound conditions are built with the boolean operators AND, OR. • AND is evaluated first by default. Use parenthesis ( ) to force the order of OR/AND. • a compliment of a condition can be evaluated with NOT. Examples below are equivalent: • SELECT PART_DESCRIPTION FROM PART WHERE WAREHOUSE_NUMBER != ‘3’; • SELECT PART_DESCRIPTION FROM PART WHERE NOT (WAREHOUSE_NUMBER=‘3’);

  19. The Basic Parts of Speech in SQL • The WHERE Clause (continued): • BETWEEN keyword is the same as >= and =<. For example, these 2 statements are the same: SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE BALANCE >= 500 AND BALANCE <= 1000; • SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE BALANCE BETWEEN 500 AND 1000; • LIKE keyword is used for pattern matching. ‘%’ is for any number of characters.‘_’ (underscore) is for one character. • SELECT CUSTOMER_NUMBER, LAST, FIRST • FROM CUSTOMER WHERE LAST LIKE ‘JONE%’; • SELECT CUSTOMER_NUMBER, LAST, FIRST • FROM CUSTOMER WHERE LAST LIKE ‘JONE_’;

  20. The Basic Parts of Speech in SQL • The WHERE Clause (continued): • IN keyword provides a list of numbers or strings to compare to. This is similar to using OR with =. For example, these 2 statements are the same: SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE CUSTOMER_NUMBER IN (10, 11, 12); • SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE (CUSTOMER_NUMBER = 10) • OR (CUSTOMER_NUMBER = 11) • OR (CUSTOMER_NUMBER = 12); • NULL and NOT NULL keywords:NULL does not mean 0, null means no value!Example- this statement only gets customers whom we have a complete name for: SELECT CUSTOMER_NUMBER, LAST, FIRST FROM CUSTOMER WHERE (FIRST IS NOT NULL) AND (LAST IS NOT NULL);

  21. The Basic Parts of Speech in SQL • The WHERE Clause (continued): • Values can also be compared to another query in the where clause. This is called a subquery. For example: SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE CUSTOMER_NUMBER IN (SELECT CUSTOMER_NUMBER FROM CUSTOMER WHERE BALANCE > 500); • This however may not work sometimes with a subquery: SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE CUSTOMER_NUMBER = (SELECT CUSTOMER_NUMBER FROM CUSTOMER WHERE BALANCE > 500);because subqueries return sets of values, not a single value.

  22. The Basic Parts of Speech in SQL • The WHERE Clause (continued): • Tables can be joined by common attributes. This is done in the where clause. SELECT WEATHER.CITY, CONDITION, TEMPERATURE, LATITUDE,. . . FROM WEATHER, LOCATION WHERE WEATHER.CITY = LOCATION.CITY; • A shortcut (not in book). Tablenames can be aliased to save typing, reduce wordiness. SELECT W.CITY, CONDITION, TEMPERATURE, LATITUDE,. . . FROM WEATHER W, LOCATION L WHERE W.CITY = L.CITY; • Joining data from different tables is one of the more powerful parts of SQL. It is not without its dangers. • Joining tables the wrong way (not on common attributes), the wrong data comes back. • Forgetting to specify common keys in the where clause returns a “cartesian product” (each row of both tables joined to each other, m times n rows)

  23. The Basic Parts of Speech in SQL • The ORDER BY Clause: • Specifies the ordering of the returned data. • Multiple columns can be selected to sort on. • ASC is ascending order order, default. • DESC is descending sort order. SELECT FEATURE, SECTION, PAGE • FROM NEWSPAPER • WHERE SECTION = ‘F’ ORDER BY PAGE DESC, FEATURE ASC; • Views: • Makes a query act similar to a table. • Views are “describe”-able, “select”-able • The view’s select statement is ran everytime rows are selected from the view--ie data is not saved somewhere (like a table) • CREATE VIEW INVASION AS • SELECT W.CITY, CONDITION, TEMPERATURE, LATITUDE FROM WEATHER W, LOCATION L WHERE W.CITY = L.CITY;

  24. Acknowledgements • These slides were originally prepared by Tony Teal as part of the IUPUI’s N311 Advanced Database Programming course.

More Related