1 / 20

Databases and Connectivity

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

neron
Download Presentation

Databases and Connectivity

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

  5. Tables (continued)

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

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

  8. ODBC

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

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

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

  12. Workshop

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

  14. Creating DSNs for the project Complete the fields as laid out below: Name VMALL_WH Description VMALL Data Warehouse Server ADA

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

  16. Creating DSNs for the project • Ensure that VMALL_WH is your default database and click Next

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

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

  19. 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).

More Related