09-02-2008old.ppt
TRANSCRIPT
![Page 1: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/1.jpg)
CS 8630 Database Administration, Dr. Guimaraes
09-02-2008, MS-Access, QBE, Access/Oracle
ClassWill
Start Momentarily…
CS8630 Database AdministrationDr. Mario Guimaraes
![Page 2: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/2.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Install/Launch Oracle:Another Interface
• To run one command at a time, Choose Go to Database Home Page from Start Menu (see very first picture above).
• Start Menu -> Programs -> Oracle 10g Express -> Go to Database Home Page
![Page 3: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/3.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Oracle Express Home Page
Select the SQL command (third icon below)
![Page 4: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/4.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Sql Command interface
![Page 5: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/5.jpg)
CS 8630 Database Administration, Dr. Guimaraes
TOAD
• http://www.toadsoft.com/lic_agree.html
![Page 6: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/6.jpg)
CS 8630 Database Administration, Dr. Guimaraes
View user_tables
TO CHECK if ALL TABLES were created• From the oracle prompt, type in
SQL>select table_name from user_tables;
You will see the tables customers, orders, products and agents
![Page 7: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/7.jpg)
CS 8630 Database Administration, Dr. Guimaraes
MS-Access and QBE
![Page 8: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/8.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Launch MS-Access, Create a new File
![Page 9: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/9.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Select Blank Database
![Page 10: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/10.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Creating a Table
![Page 11: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/11.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Defining Datatatypes
![Page 12: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/12.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Defining Relationship
![Page 13: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/13.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Editing Relationships
![Page 14: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/14.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Creating a Form
![Page 15: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/15.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Inserting Pictures
![Page 16: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/16.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Database Utilities
![Page 17: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/17.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Import/Export
![Page 18: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/18.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Query-by-Example (QBE)
• Visual approach for accessing information in a database through use of query templates.
• Example values are entered into template to represent what access to database is to achieve, such as the answer to a query.
• Originally developed by IBM in 1970s [Zloof] and has proved so popular that QBE (or similar) is now provided by most DBMSs.
• When user constructs a QBE - in background, DBMS creates an equivalent SQL statement.
![Page 19: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/19.jpg)
CS 8630 Database Administration, Dr. Guimaraes
MS-Access – Blank Database
![Page 20: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/20.jpg)
CS 8630 Database Administration, Dr. Guimaraes
New DB - .MDB file
![Page 21: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/21.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Introduction to Microsoft Access
![Page 22: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/22.jpg)
CS 8630 Database Administration, Dr. Guimaraes
SQL View (MS-Access Query)
![Page 23: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/23.jpg)
CS 8630 Database Administration, Dr. Guimaraes
New Query
![Page 24: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/24.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Projection
![Page 25: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/25.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Restriction
![Page 26: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/26.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Restriction w/ multiple criteria
![Page 27: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/27.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Join
![Page 28: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/28.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Totals (Aggregate Functions)
![Page 29: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/29.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Projecting Expressions
![Page 30: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/30.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Specifying Criteria
![Page 31: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/31.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Find Unmatched Query (Outer Join)
![Page 32: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/32.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Creating a Table
![Page 33: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/33.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Defining Datatatypes
![Page 34: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/34.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Defining Relationship
![Page 35: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/35.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Editing Relationships
![Page 36: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/36.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Creating a Form
![Page 37: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/37.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Inserting Pictures
![Page 38: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/38.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Database Utilities
![Page 39: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/39.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Connect MS-Access & Oracle
Do a search and find odbcad32.exe (ODBC Data Source program) or
Control Panel -> Administrative Tools -> Data Sources (ODBC)Then follow the instructions at
http://science.kennesaw.edu/~mguimara/3310/odbclab.zip
Click on the System DSN panel above
(second one from the left) and the add
button on the right of A Create New Data
Source window appears as shown in
Next slide.
![Page 40: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/40.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Select Driver
Select the Microsoft ODBC for Oracle driver and click the Finish button
![Page 41: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/41.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Define Server
A Microsoft ODBC for Oracle Setup window will appear as shown in Figure 3 below (from http://science.kennesaw.edu/~mguimara/3310/odbclab.zip) . For Data Source Name you can choose any name (OracleAccess, for example). For Server Name you choose the Oracle instance (or the service name in a multi-user environment). The other two parameters are optional. User Name is the Oracle user, but you should leave it blank, that way you can login as any user. Click okay and click okay again to save the ODBC Data source and exit.
![Page 42: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/42.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Get External Data
• Launch MS-Access and select File Menu -> Get External Data -> Link (if you select import, you are simply copying the oracle tables to MS-Access)
![Page 43: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/43.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Select File type
![Page 44: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/44.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Select Data Source
• When the Select DataSource window appears (Figure 6), select the Machine Data Source tab and then select the Data Source you created.
![Page 45: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/45.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Select PK
When you click the okay button, you will get a login window. Type in your Oracle user name and Password (Server name is the database instance name and should already come typed for you). You then should get a screen with tables which you can select. Select the tables you want link to next slide. Click OK button
![Page 46: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/46.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Link Tables
• When you click the okay button (in previous slide), you will get a login window. Type in your Oracle user name and Password (Server name is the database instance name and should already come typed for you). You then should get a screen with tables which you can select. Select the tables you want link to (slide below).
![Page 47: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/47.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Select PK
After you select the tables, make sure you also select the field you want to define as a primary key. Otherwise you will only be able to view data (and not modify).
![Page 48: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/48.jpg)
CS 8630 Database Administration, Dr. Guimaraes
See Linked Tables
• You will then be able to access the oracle tables as if they were MS-Access tables. However, since you chose the Link Option (not the import), you are actually modifying the Oracle Database.
![Page 49: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/49.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Georgia Oracle User Group
• http://www.gouser.org/
![Page 50: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/50.jpg)
CS 8630 Database Administration, Dr. Guimaraes
Import/Export
![Page 51: 09-02-2008old.ppt](https://reader033.vdocuments.us/reader033/viewer/2022060110/5561e691d8b42ab1068b4ed7/html5/thumbnails/51.jpg)
CS 8630 Database Administration, Dr. Guimaraes
End of Lecture
End Of
Today’s
Lecture.