DBMS vs. Database vs. Database Client

It is important to make a distinction between these 3 important concepts:

DBMS - This is the database management system. The software engine which you use to create databases and store/manage the data within the database. Some of the more popular DBMS applications are:

* Oracle
* SQL Server
* Access
* Sybase
* MySQL

Database - This is a file or collection of files that holds your data (tables, indicies, records etc). DBMS applications are used to create databases.

Database clients - A database client application is an application you use to connect to a DBMS to work on a database. Review that statement carefully. You connect to a DBMS to work on a database because the DBMS manages important features such as security, data processing etc for the database. Some common Database clients are:

* Free
* SQL Server Management Studio Express
* Oracle SQL Developer
* DB Visualizer
* Eclipse Data Tools

* Commercial
* TOAD
* Enterprise Manager

SQL Assigment

For all the items below, what is required is the SQL to retrieve the answer. Please send you submissions by 4/12/08.

Connection details will be provided in the class. You can use whatever database client you want to use. A list of some free ones is provided in a different posting on this site.

1. Display all the products sorted in alphabetical order.

2. Display all the products sorted by unit price in descending order.

3. Find the most expensive product (i.e. the product with the highest unit price).

4. Get the name of the sales representative that worked on the most expensive order

5. Obtain a list of orders handled by Andrew Fuller

6. Get a list of products with the lowest unit price

7. List all employees that service the Western region.

8. List all the suppliers for products sold as part of Order 10252

9. List all customers that have made placed more than 3 orders.

Electronic Data Interchange

Electronic Data Interchange is the process by which companies share important information between them. A manufacturer for example, who is working on an order for a client would receive an inquiry about the status of the order. In response, the manufactorer would issue an order status report. While these items can be done via a phone call, imagine that the manufacturer is manufacturing lightbulbs and generates over 100,000 differnet lightbulbs in a day for different companies and stores. Attempting to do this inquiry using people would be both costly and slow. It is infinitely more efficient to allow the computers of the manufacturer and the clients share information through the use of EDI documents.

In the US. We use the X12 specification and the two documents involved above would be:

869 - Order Status Inquiry
870 - Order Status Report

The following resources are useful for further study of EDI

• EDI presentation
  
• List of X12 EDI Documents
  
• Example of EDI Document 813 - Electronic Filing of Tax Return Data
  

Lets use another example of a person (Anna) going to a hospital (Bethsida Medical) because she has a headache.

1. Anna give her insurance card to the receptionist at Bethsida Medical
2. Receptionist uses (X12/EDI 270 Eligibility, Coverage or Benefit Inquiry) to determine that Anna has coverage.
3. The doctor sees Anna and wants to get her previous health records. He uses (X12/EDi 275 Patient Information)
4. Bethsida Medical submits a claim to Cold-Cross, Anna's medical insurance using (X12/837 Health Care Claim).
5. 2 weeks later, the Cold Cross insurance pays Bethsida Medical using (X12/EDI 835 Health Care Claim Payment/Advice)

This is a snapshot of the daily usage of X12 data.