Transcript presentation source

Databases and Connectivity
MIS 497
Relational Databases


Dominant database technology today.
Stores data in relational format in the form of tables:
– Data are organized in the form of tables with rows and
columns
– Powerful manipulations (using the SQL language) are
used to manipulate data stored in the tables.
– Facilities are included to specify business rules that
maintain integrity of data when they are manipulated.
Most popular products
Oracle (Oracle Corp)
 DB2 (IBM)
 SQL Server (Microsoft)
 Adaptive Server IQ (Sybase)
 Informix products (Dynamic Server, Red
Brick)
 Teradata (NCR Corp.)

Tables


Table is a named two-dimensional array of data. Each table consists
of a set of named columns and an arbitrary number of unnamed
rows.
Table properties:
1.
2.
3.
4.
5.
6.
Each table in a database has a unique name
An entry at the intersection of each row and column is atomic (or singlevalued). There can be no multi-valued attributes in a table
Each row is unique; no two rows in a table are identical
Each attribute (or column) within a table has a unique name.
The sequence of columns (left to right) is insignificant. The columns of a
table can be interchanged without changing the meaning or use of a table.
The sequence of rows (top to bottom) is insignificant. As with columns, the
rows of a table may be interchanged or stored in any sequence.
Tables (continued)
Relational Keys
A primary key is an attribute (or
combination of attributes) that uniquely
identifies each row in a table.
 A composite key is a primary key that
consists of more than one attribute.
 A foreign key is an attribute (possibly
composite) in a table of a database that
serves as a primary key of another table in
the same database.

Connectivity
To use a database, one must connect to it.
 ODBC – Open Database Connectivity, defacto connectivity standard created by
Microsoft.
 We’ll use ODBC to connect to our
databases.

ODBC
DSN


Data Source Name –
stores information on
how to connect to the
indicated data provider.
To connect one must
know location and type
of the data provider and
have appropriate logon
credentials (login and
password).
Database Security



Unauthorized users must be prevented from accessing information.
Databases have User Management tools.
Each project group in the class will be assigned a unique user ID and
password to access their Metadata and Data Warehouse databases.
Resources Used

Modern Database Management – Fifth Edition, Fred R.
McFadden and others, Addison-Wesley 1999.
Workshop
Creating DSNs for the project
First we’ll create a DSN to connect to our Data Warehouse
 Go to
StartControl PanelData Sources (ODBC)System DSN.
 Select Add
 Scroll down, select SQL Server and click Finish

Creating DSNs for the project
Complete the fields as laid out below:
Name
VMALL_WH
Description
VMALL Data Warehouse
Server
ADA
Creating DSNs for the project



Make sure that you are using SQL Server authentication mode.
Enter the name of your group as your login and the password that was
assigned to you.
Click Next
Creating DSNs for the project

Ensure that VMALL_WH is your default database and click Next
Creating DSNs for the project



Click Finish and then Test Your Data Source
If you see the following screen then your DSN is created!
Click OK couple of times
Creating DSNs for the project

Now follow the same procedure to create a DSN for connection to your
Metadata database according to the following information:
DSN Name:
Server:
Login:
Password:
Default DB:
YourGroupName_MD (I.e. Group3_MD)
ADA
YourGroupName
Use the one assigned to you by Dr. Sauter
YourGroupName
Using DSNs for the project

All changes to your project will be stored in the SQL Server database on
ADA. You’ll be able to access your project using MicroStrategy Desktop
from any machine in the MIS Lab. Most probably that machine will
already have your DW DSN and even your metadata DSN if you worked
on that machine before. However, if they are not there, you should create
them manually (it takes less than a minute for each of them).