Introduction to Relational Databases.ppt

1、8/12/2020,1,Department of Computer and Information Science,School of Science, IUPUIFall 2003,Dale Roberts, Lecturer Computer Science, IUPUI E-mail: ,Introduction to Relational Databases,2,Sharing Knowledge and Success,Oracle is a relational database management system (RDBMS). RDB

2、MSs allow users to. put data in keep/manage the data get data out and work with it,3,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 temper

3、ature; Examples of relational databases stock tables in newspaper sports scores 100 year example in book,Sharing Knowledge and Success,4,Disadvantages of traditional file processing systems uncontrolled redundancy inconsistent data inflexibility limited data sharing poor enforcement of standards low

4、 programmer productivity excessive program maintencancefrom Database Management, McFadden and Hoffer, pp. 8-14,Database Management,5,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

5、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

6、R.I. relational algebra, three main operators: select, project, join normalization theory:1NF, 2NF, 3NF,Database Management: Data Models,6,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,

7、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

8、relations key, not on any other nonkey.,Database Management: Normalization,7,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 sho

9、rter 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.,The Dangers in a Relational Data

10、base,8,How to reduce the confusion Normalization1NF:2NF:3NF: English names for tables and columns, English code names,The Dangers in a Relational Database,9,The Dangers in a Relational Database,10,The Dangers in a Relational Database,Bad Examples of Table and Column Names: TablesDEPTEMPEMPSMYEMPSPEP

11、ROJTITLESPERSONNEL ColumnsAD1AU_LNAMEAU_ORDBLOCCDLEXPDEPTNODNAMEDISCOUNTTYPEEMPNOENAMEENUMBERESALHIGHQTYHIRANGELORANGELOWQTYNOTEORD_NUMPNAMEPROJNOPUBDATEQTYOHSLSTAXPCTWORKHRS Reasons abbreviation used without good reason inconsistent abbreviations, underlines, and use of plurals purpose not apparent

12、 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

13、 Standards Organization (ISO) SQL92 standard at entry level conformance. PL/SQL is Oracles 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

14、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 d

15、efinitions 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 command

16、s in SQL*Plus: get save list or l change or c edit save ! (shell to OS) Capitalization generally doent 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 a

17、nd 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 an

18、d 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 WH

19、ERE 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 Spe

20、ech 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 CUSTO

21、MER_NUMBER, LAST, FIRST,(CREDIT_LIM - BALANCE) AS REMAINING_CREDIT FROM CUSTOMER; The FROM clause: in single tables queries, just provide your tables 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 whi

22、ch must evalute to true for each row in the querys 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 par

23、enthesis ( ) 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 PARTWHERE WAREHOUSE_NUMBER != 3; SELECT PART_DESCRIPTION FROM PARTWHERE NOT (WAREHOUSE_NUMBER=3);,19,The Basic Parts of Speech in SQL,The WHER

24、E Clause (continued): BETWEEN keyword is the same as = and = 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

25、 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 wi

26、th =. 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 N

27、OT 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)

28、: 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

29、: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 a

30、ttributes. This is done in the where clause.SELECT WEATHER.CITY, CONDITION,TEMPERATURE, LATITUDE,. . .FROM WEATHER, LOCATIONWHERE 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 LWHERE W.CITY = L.CITY; Joining data from different tables is one of the more powerful parts of SQL. It is not without its dangers.