1 / 42

ISO6 Relational Databases

ISO6 Relational Databases. Simon Booth Email s.p.booth @stir.ac.uk Room Library S6 Tel: 7247. Course Aims. Develop a basic proficiency in A relational database environment The SQL database language – Oracle SQL*Plus A forms-based 4GL programming tool – Access 97

salvador-flynn
Download Presentation

ISO6 Relational Databases

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. ISO6 Relational Databases Simon Booth Email s.p.booth@stir.ac.uk Room Library S6 Tel: 7247

  2. Course Aims Develop a basic proficiency in • A relational database environment • The SQL database language – Oracle SQL*Plus • A forms-based 4GL programming tool – Access 97 • Database design and Management PF Lecture 1

  3. Lectures and Workshops • Lectures (A1) • Monday 10-11 • Workshop (2A19) • Monday 14-16 PF Lecture 1

  4. Applications and Books • SQL Plus Oracle 3.3/8.0 • Oracle 8 database • Access 97 • Database Design and Management by Nick Dowling (Letts) £10.99 PF Lecture 1

  5. Proposed Course Structure • First half – SQL Plus version 3.3/8.0 running against Oracle 8 • Second half – Access 97 • Lecture on Monday – practical issues on the upcoming workshops PF Lecture 1

  6. Database Management Systems (DBMS) Definition “ A set of programs that act as an interface between application programs and the data in the database” PF Lecture 1

  7. A Simplified Database System Environment users/programmers Database System Application Programs/Queries DBMS Software Software to process Queries/Programs Software to access Stored Data Stored Database Definition (Meta-Data) Stored Database PF Lecture 1

  8. 5 primary functions of a DBMS • Define, create and organize a database (Data Definition Language) • Input data • Process data (Data Manipulation Language) • Maintain Data integrity and security • Query database (Structured Query Language) PF Lecture 1

  9. users users users DATA users users Advantages of DBMS • Data independence • Access to data based on its contents and its associations with other data • Physical organization of data is independent of program using it • Reduction in data redundancy • Accessing and processing data more effective PF Lecture 1

  10. Disadvantages of DBMS • Cost is the primary disadvantage • Mainframe hardware expensive • Even expensive with PC based databases • Licences • Training • Maintenance and Management PF Lecture 1

  11. Database Infrastructures • Hierarchical • Network • Relational PF Lecture 1

  12. Hierarchical ROOT Father Son 1 Son 2 PF Lecture 1

  13. Network Owner Member PF Lecture 1

  14. Relational Model PF Lecture 1

  15. Structured Query Language (SQL) • Programming language (4GL) • Data definition and data manipulation language • Used in all types of database applications • Oracle and Access PF Lecture 1

  16. SQL (cont.) • Provided a degree of homogeneity • Industry standard • Databases conforming to the SQL standard can also use applications from other SQL databases • SQL standards approved by ANSI • Incorporating SQL technology provides a strong selling point PF Lecture 1

  17. SQL Plus (Sequel Plus) • SQL*Plus is a program for working with an ORACLE database • Create tables • Store information in the tables • Retrieve information in a form you choose, performing calculations on it and combining it in new ways • Maintain the database PF Lecture 1

  18. Querying & Manipulating Databases Using SQL*Plus

  19. Accessing SQL*Plus 3.3 • To access Oracle SQL*Plus you must first log-on in the normal way. • In Windows NT go to START, PROGRAMS, ORACLE, ORACLE WinNT, SQL Plus 3.3. PF Lecture 1

  20. Accessing SQL • Enter your oracle username and password. You will also be asked for a ‘host string’ enter ora1a11 PF Lecture 1

  21. Accessing SQL • Having entered these, you will then see the SQL prompt from Oracle:SQL> • You are now ready to type SQL commands PF Lecture 1

  22. Accessing SQL • SQL>SELECT job FROM emp 2 • Note: Oracle itself is not case-sensitive. I have used a mixture of upper and lower case to indicate SQL commands (uppercase) and the components I select (lowercase) PF Lecture 1

  23. Accessing SQL • SQL>SELECT job FROM emp 2 • Note: Oracle inserts the “2” to indicate that we are on the second line. To properly terminate the command, enter ;. Oracle will now process the command. PF Lecture 1

  24. A table Artist Title Format • The above is a table named called “music” with three columns: Artist, Title and Format. There are presently three rows. Verve Urban Hymns CD Oasis Be Here Now Cassette Radiohead Bends CD PF Lecture 1

  25. The Select command • To list the columns Artist and Format from the table music:SQL> SELECT artist, format FROM music; PF Lecture 1

  26. The WHERE clause • We can select based on rows that meet a certain condition. For instance, only CD’s:SQL> SELECT * FROM music 2 WHERE format LIKE ‘CD’; • * means all columns PF Lecture 1

  27. The WHERE clause(2) • Another Example:SQL> SELECT ename, deptno, sal 2 FROM emp 3 WHERE sal >= 1500; • Columns are listed in the order they appear and can appear more than once PF Lecture 1

  28. The ORDER BY clause • Oracle will list back the information we ask in order it appears in the table. If we want it alphabetically or by numerical order we must add an ORDER BY and specify the column(s) to order by. PF Lecture 1

  29. The ORDER BY clause(2) • Example: SQL> SELECT ename, deptno, sal 2 FROM emp 3 ORDER BY sal DESC; PF Lecture 1

  30. SQL • Group FunctionsThese functions (AVG, MAX, MIN, etc) can act on entire tables or subsets of the tableSELECT AVG(sal) FROM emp;SELECT MAX(sal) FROM empWHERE job = ‘MANAGER’;SELECT COUNT(*) FROM empWHERE deptno = 20; PF Lecture 1

  31. GROUP BY • Group functions can also be used with the GROUP BY clause, which splits the table into specified groups, returning one row for each groupSELECT AVG(sal), deptno FROM empGROUP BY deptno; PF Lecture 1

  32. Clauses • Clauses can be combined: SQL> SELECT ename, deptno, sal 2 FROM emp 3 WHERE sal > 1500 4 ORDER BY sal DESC; PF Lecture 1

  33. Second example • Rows maybe selected by WHERESELECT AVG(sal), deptno FROM empWHERE job != ‘MANAGER’GROUP BY deptno; PF Lecture 1

  34. Update • The SQL command update is used to modify all or some rows in the table:UPDATE tableSET column = expr [, column = expr][WHERE condition]; PF Lecture 1

  35. Update • Example: correct name for employee 7369UPDATE empSET ename = ‘jones’WHERE empno = 7369; PF Lecture 1

  36. Delete • The SQL command delete is used to remove all or some rows in the table: DELETE FROM table[WHERE condition]; PF Lecture 1

  37. Delete • Example: remove employee 7369 DELETE FROM empWHERE empno = 7369; PF Lecture 1

  38. Copying Tables • We can copy tables existing tables:CREATE TABLE stolen_table ASSELECT * FROM another_table; PF Lecture 1

  39. Creating Tables(2) • If we only want specific columns, we name them in the select;If we only want specific rows, we use a WHERE clause:CREATE TABLE stolen_table ASSELECT video_no, title FROM videoWHERE title = ‘T%’; PF Lecture 1

  40. Populating Tables • To place data in a table we use the INSERT command:INSERT INTO table [(column-name, …)]VALUES (value, … ); • Which adds one row to the table. To add more, we have more INSERT commands PF Lecture 1

  41. Populating Tables • Examples:INSERT INTO dept (deptno, dname)VALUES (50, ‘Marketing’);INSERT INTO deptVALUES (50, ‘Marketing’, ‘Stirling’); PF Lecture 1

  42. Summary of SQL commands • SQL • SELECT • UPDATE • DELETE • CREATE AS • INSERT PF Lecture 1

More Related