Databases and Connectivity

1
Databases and Connectivity

• MIS 497

2
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.

3
Most popular products

• Oracle (Oracle Corp)
• DB2 (IBM)
• SQL Server (Microsoft)
• Adaptive Server IQ (Sybase)
• Informix products (Dynamic Server, Red Brick)
• Teradata (NCR Corp.)

4
(No Transcript)
5
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
• Each table in a database has a unique name
• An entry at the intersection of each row and
  column is atomic (or single-valued). 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.

6
Tables (continued)
7
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.

8
Connectivity

• To use a database, one must connect to it.
• ODBC Open Database Connectivity, de-facto
  connectivity standard created by Microsoft.
• Well use ODBC to connect to our databases.

9
ODBC
10
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).

11
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.

12
Resources Used

• Modern Database Management Fifth Edition, Fred
  R. McFadden and others, Addison-Wesley 1999.

13
Workshop
14
Creating DSNs for the project

• First well 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

15
Creating DSNs for the project

• Complete the fields as laid out below
• Name VMALL_WH
• Description VMALL Data Warehouse
• Server ADA

16
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

17
Creating DSNs for the project

• Ensure that VMALL_WH is your default database and
  click Next

18
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

19
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 YourGroupName_MD (I.e. Group3_MD)
• Server ADA
• Login YourGroupName
• Password Use the one assigned to you by Dr.
  Sauter
• Default DB YourGroupName

20
Using DSNs for the project

• All changes to your project will be stored in the
  SQL Server database on ADA. Youll 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).