Transcript Introduction

Course Goals
• Introduce Terms
• Skills
–
–
–
–
Modern DBMS (SQL Server 2008)
SQL querying and data access
Stored procedures including parameters
Brief introduction to using code to interact with
dbms
Information Resource
Management
• What is a Database?
• Database vs. DBMS
• What are the Advantages of the Database
Approach?
Retail Sales with Computer
System and Bar Codes
• “What Happens when you bar-code a can of
beans at Walmart?
• Initial Goal: improve speed & accuracy of
checkout
– Wipe barcodes to get product ID
– Look up price
– Calculate total amount
But with this information, we can
do much more
• Reduce Errors
– Happier customers
• Less skilled checkout people
– No need to type #s or memorize prices
• Can change prices much faster
• Checkout efficiency
– Credit Check
– How fast people working
• Automatic Inventory Control
– JustInTime Wholesale ordering – fedex option
– Reduced inventory costs
– Tax calculations for inventory
• Automatic Floor space v. sales calculations
What Business Functions
Represented by This Function?
•
•
•
•
•
•
Sales and Marketing
Logistics
Operations
Human Recourses
Management
Accounting
Information Systems
Management
• Data
– Raw facts
• Information
– Processed to be useful to particular users
What is a Database?
• A database is a shared collection of
logically related data to help multiple
particular users solve multiple particular
problems, possibly in the future, and as yet
unknown
• A database management system (DBMS) is
a tool to access a database
Vision
• Database that contains
ALL corporate data
Hard to achieve in practice
Department DBs already exist
– Older non-homogeneous software
• Even if not, have to start at department level to keep
tractable
• Many often unwilling to start over
– Ego & $$
• Different data organization and data dictionary
– E.g. how to store a name
• Extra work for some departments so other departments can
do their job better
– Turf wars
Function of CIO
• Operations Management
– Scheduling, Capacity planning, Operations security,
Disaster recovery
• Quality Assurance
• Communications Management
– Local/Wide area, Long haul
• Data Resources Management
– Data analysis, Database design, Data administration,
Database Administration
• Project Management
• Corporate-Wide IS planning
• Information Center
• Systems development and Maintenance
Centralized Database
• All data at a single site
– Personal computer
– Central Corporate database (big iron)
– Client/Server on network
DB Analysts and DB administration
have to understand how organization
works
• DB analyst a good path to becoming a CIO
• Requires both solid business AND technical
skills including coding and complex query
writing.
SQL Server Basics
(Chapter 1)
• Overview of Database Objects
–
–
–
–
–
–
Database itself
Transaction log
Tables
Filegroups
Diagrams
Views
Overview of Database Objects
•
•
•
•
•
Indexes
Assemblies
Reports
Full-text Catalogs
User-defined data types
– e.g. ‘zip code’ instead of char(9)
• Roles
• Users
Four System Databases
• Master
– Contains meta data about other databases
including user-defined databases
• Model
– Template for creating of user-defined DBs
• Tempdb
– Working area for server
• Don’t touch any of these
MS Sample Databases
•
•
•
•
AdventureWorksDW
Pubs
Northwinds
We will use pubs and northwind as
examples in this class
Transaction Log
• Transaction is a set of operations that must
happen together (more on this later in the
course)
• Actions first written to transaction log.
• When that is complete, operations applied
to database.
• Rollback and data recovery
Objects within a Particular DB
• Table
• Index
– Clustered and non clustered
• Triggers
• Constraints
• Filegroups
– All tables default to single file, but can be
changed to multiple files, disks, RAIDS, etc
Objects within a Particular DB
• Diagrams (page 9)
– Diagram to DB
– DB to diagram (!!!)
– Views
• Hard to update
–
–
–
–
–
–
–
Stored procedures (e.g. subs w/ parameters)
User-defined functions
Users and Roles
Rules
Defaults
User-defined data types
Full-text (images?)
SQL server data types
• See table page 12
• Note
–
–
–
–
Varchar
Nvarchar
Unique identifier
Conversion matrix
NULLS (page 17)
• Means ‘don’t know’ but might also mean
‘not applicable’ – no way for others to tell
• Nulls are BAD
• Poor DB design sometimes leads to nulls!
– If your database design requires nulls you are
doing something wrong
NAMES
• To know something’s true name is to be
able to control it (once and future king)
• Everything in sql server has a name (pg 17)
• SQL server naming rules (pg 18)
• My naming rules
–
–
–
–
Relations are plural
Attributes are generally singular
All lowercase
No SPACES!!! Or underscores (innercap)