Serving up data

1
Serving up data

• SQLServer and the SQL Language.
• Csc 96c

2
Topics

• A review of the Client/Server Picture as it
  applies to web development
• Database theory
• SQLServer and Tools
• Demo of SQLServer Tools
• Practice Database (Pubs, Others)

3
C/S Visually
This is what all IIS Applications are
4
Database Design Principles

• A quick overview of the theory and nomenclature

5
What do databases do?

• A database must provide a place to store
  information. In the case of most databases the
  system of storage managed by the database itself.
• The must organize and safeguard the data from
  unauthorized or unwise changes. In most cases,
  operating system security can be imposed on the
  various parts of the database allowing selective
  access by individuals or groups.
• Most production databases are relational. That
  means that the data is normalized and that the
  system can provide transactions.
• Transactions follow a more general principle
  called the ACID (Atomic, Consistent, Independent,
  and Durable) principals.
• Together these factors provide for a reliable and
  fast way to store large quantities of information.

6
Compare and Contrast

• Spreadsheets
• Sheets
• Rows
• Columns
• Cell Values
• Flat organization, data is repeated in the
  columns over and over.

• Databases
• Tables
• Rows
• Fields/Columns
• Field Values
• "Normalized" organization. Data is broken into
  tables based on cardinality.

7
Spreadsheet Example
Notes Authors names are repeated over and
over again A book with multiple authors is in
twice or more So, if we make a correction to one
record it will not match the others. Thus a
spreadsheet can be said to have poor data
integrity and poor use of space. However, it is a
handy way to look at the data.
8
Database Design (Normalization)

• Start with "flat" tables like this spreadsheet.
• Break out repeating data into its own table
• Authors
• Titles
• Encode the data (e.g. make a Categories table)
• Develop the relationships (one field that ties
  the information together AU_ID or Title_ID)
• Identify Cardinality (1 Author, Many Titles)

9
5 Rules of Normalization (FYI)

• 1. Eliminate Repeating Groups
• 2. Eliminate Redundant Data
• 3. Eliminate Columns Not Dependent on the Key
• 4. Isolate Independent Multiple Relationships
• 5. Isolate Semantically Related Multiple
  Relationships

Database Programming and Design Miller Freeman,
Inc.
10
Rules for Good Database Designs

• The more data you have the more important a good
  design following "normalization" Rules
• Faster
• More Flexible
• Safer
• Names containing weird characters like (_at_) and
  spaces in field, table and other system names are
  to be avoided as they cause trouble for some
  front end and query engines.

11
Understanding databases and their documentation.

• Relating Tables.
• ERDs and Data Dictionaries.

12
Primary Keys, a brief explanation.

• In most well built databases every table has one
  field that uniquely identifies each row. The
  field is called the PRIMARY KEY.
• Some times, more then one field is used to
  uniquely used to identify each row, then the
  aggregate of those fields is the PRIMARY KEY.
• In the Authors table the AU_ID field is the
  unique value used to uniquely identify the entire
  row, hence it is the PRIMARY KEY.

13
Foreign Keys A brief explanation.

• A foreign key is a field in a table that refers
  to the primary key field in another table.
• For example the TitleAuthor table has two "glue
  fields". One field AU_ID to refer to the authors
  table and another Title_ID to refer to the titles
  table
• In theory this would be a space saving move. In
  practice, this intermediate table allows us to
  maintain the relationship that an author may have
  many books and that a book may have many authors.
  
• Tables like TitleAuthor are sometimes called
  glue table or join tables

14
Relationships PrimaryForeign Keys.

• The link between the Authors table and the
  TitleAuthor table is a relationship based on one
  table having a field (a foreign key) that is the
  primary key of another table.
• In an ERD there would be a line between the two
  tables to represent the relationship.
• There can be many TitleAuthor records for a
  particular Authors. This is its cardinality.

15
Cardinality

• Cardinality expresses the numerical or
  quantitative relationship between one table and
  another
• Cardinality is also a good indicator of parent
  and child relationships.
• Consider, for there to be an TitleAuthor record,
  which has a AU_ID, the AU_ID must exist 1st in
  the authors table.
• This is data integrity.

16
Data Integrity

• Is the set of rules that govern relationships
  between tables to safe-guard the data.
• If a table has a foreign key (it is called a
  child table) that is the primary key of another
  table (the parent table), the other table's
  record must exist first.
• The complement to this rule is that we may not
  delete the parent record while there are still
  child records as this would result in "bad"
  records in the child table.
• Cardinality and Relationships therefore partially
  define data integrity.

17
Documenting Databases

• To communicate the database's structure and rules
  we need
• Entity Relationship Diagrams (ERDs)
• Data Dictionaries. (DDs)
• Poorly documented databases are hard to work
  with, especially if definitions for the meaning
  of fields are absent.

18
This ERD adds some of the missing information

• Cardinality
• "One"
• "Many"
• "One (Optional)"
• "Many (Optional)"
• Field Types
• Text (Text, Memo)
• Numeric (Integer, Long, Double)
• Other (Boolean, Date, etc)

19
Mini-Pubs Database ERD
This is the partial ERD and fields you will need
for the assignment. Notice the PKFK
relationships.
20
Big Pubs ERD
21
The rest of information must be supplied by a
"Data Dictionary"

• An overview of the system and what it does.
• A list of all of the table names, what they are
  for, each tables fields and what they are for.
• For each field its type, size and other
  parameters
• Explanation of calculations and definitions used
  in the system. (If any)
• Relationships between tables. E.g. Primary Keys
  and Foreign Keys
• A cross Reference of what fields occur in what
  tables.

22
Web Application Data Tools

• SQLServer, SQL and ADO

23
Server Side Data

• What do you need?
• A Database Engine
• SQLServer
• Oracle
• File based like MS Access or Fox Pro
• Client Side Connector
• ActiveX Data Objects - ADO
• The SQL Language itself.

24
Diagrammatically
ASP Page
SQLServer Databases
mySQL Select
Network
Set rs CreateObject(ADODB.RecordSet)
ADODB Object
The ASP Page Creates a ADO object to communicate
SQL Language Messages to the server and get back
results and recordsets. A recordset is a series
of rows.
25
Data Services/Servers

• The example database for this class is Microsoft
  SQLServer (SS).
• Like most other big database engines it operates
  as a set of services under NT. (On Unix it would
  be a Daemon).
• Version 7 has a capacity roughly equal to its two
  biggest competitors Oracle and DB2.
• Microsoft and Compaq have built a site that
  supports a database in the one terabyte region.

26
SQLServer Specifics

• Tables
• Indexes, Permissions, Defaults, Validation
• Views
• Canned select statements without parameters
• Stored Procedures
• Scripts capable of taking parameters
• Other.
• Rules, User Defined Data Types.

27
The SQL Language

• In order to communicate with SQLServer you need
  to know the SQL language.
• Four fundamental database queries
• Select - Get records from the DB
• Insert - Insert Records in to a table
• Update - Update a record(s) in a table(s)
• Delete - Delete record(s) in a table
• Other
• Scripting, Table Creation, Security, etc.

28
Database Tools

• Enterprise Manager
• Interface for designing and maintaining SQL
  server databases
• Query Tool/Analyzer
• Create and test SQL queries
• ODBC data source manager
• Client Configuration Manager

29
SQLServer 7

• Tools Demo

30
Tools for success

• Know what the database is for.
• Entity Relationship Diagram (ERD)
• Data Dictionary.
• Know the SQL language
• Know what you are trying to accomplish. Be
  specific, identify the affected tables and which
  rows and columns

31
SQL Queries Demo

• Exploring the Pubs Database.