ist 220 – intro to databases
Post on 15-Jan-2016
20 Views
Preview:
DESCRIPTION
TRANSCRIPT
IST 220 – Intro to Databases
Lecture 1
An Overview
Course Objectives
In this course, we will learn Fundamentals of (relational) databases Analyzing data needs and coming up with good DB design Developing simple DB applications using Access Basics of SQL using Access and Oracle
Textbook
Succeeding in Business with Microsoft Office Access 2003 Bast et al Course Technology
Features Emphasize on solving real problems Demonstrating database design and implementation
in a step-by-step setting Introducing how to build interactive DB applications
using macros and VBA modules Providing the SAM software for building basic skills
The Approach
Learn relational database through practices Getting started with data modeling and design Building simple DB incrementally using MS Access
Convenient templates for DB design and implementation Easy-to-use tools Extended functionalities Widely used in various applications
Getting exposed to SQL with Access and Oracle universal standard query language Industry strength RDBMS
Major Topics
Introductions to RDBMS and MS Access Data modeling & RDB design (processes and rules) DB building blocks
Tables – data storage Queries – data retrieval and manipulation (insert, delete, update) Forms and reports – user interfaces for displaying data/info
Application development using switchboards, as well as macros & VBA modules
SQL (using Access and Oracle)
Class Plan & Grade Breakdown
Quizzes (3 at 40 points each) 120 points
Exams (3 at 100 points each) 300 points
Labs and course portfolio 280 points
Team Project 300 points
8 increments 240 points
Docs & Demo 60 points
What Is a Database?
To answer this question, we will discuss the following related concepts Data Information Database Relational database Database management system Database applications
Data
Definition by Merriam-Webster’s factual information (as measurements or statistics) used as a
basis for reasoning, discussion, or calculation
Raw data Raw data (pieces of information) may not be so helpful
without proper manipulation
Data is the set of original value you store in the tables of the database (You store data.)
Information
Information is Derived from raw data Specially prepared for a certain audience
Before useful information can be obtained Data needs to be defined and stored in a highly organized
manner Data should be easy to add into and retrieve from a database
Information is data that is retrieved and organized in a way that is meaningful to the person viewing it. (You retrieve & present information.)
Database
A database is a collection of related raw data that is well-defined and organized.
Aug 13 Adah Talbot ½ day in the afternoon$0.50
Banked for Hara Talbot
A 100-Year-Old Example
Relational Database
Backed by sound mathematical theories, RDBs provide a better way to organize user data Usually with data for several subjects (entities) within a
certain application (domain) Teams Players Games Scores and technical statistics
In a relational database, data are stored in tables each of which is designated for a specific subject
The mathematical term “relation” defines a data structure for which a table is the exact incarnation
Example of a Table – Hawks Roster
Hawks Roster
2002-03 Roster
NUM PLAYER POS HT WT DOB FROM YRS
3Shareef Abdur-Rahim
F 6-9 230 12/11/76 California '99 6
David Andersen F 7-0 242 6/23/80 Australia R
6 Cal Bowdler (FA) F 6-10 253 3/31/77 Old Dominion '99 3
4 Chris Crawford F 6-9 235 5/13/75 Marquette '97 5
15 Emanual Davis G 6-5 195 8/27/68 Delaware State '91 5
12 Dan Dickau G 6-0 190 9/16/78 Gonzaga '02 R
5 Dion Glover G 6-5 228 10/22/78 Georgia Tech '02 3
44 Alan Henderson F-C 6-9 240 12/02/72 Indiana '95 7
1 DerMarr Johnson F 6-9 201 5/05/80 Cincinnati '03 2
2 Nazr Mohammed C 6-10 250 9/05/77 Kentucky '99 4
14 Ira Newble (FA) F 6-7 215 1/20/75 Miami (Ohio) '97 2
42 Theo Ratliff C-F 6-10 230 4/17/73 Wyoming '95 7
13 Glenn Robinson F 6-7 230 1/10/73 Purdue '94 8
30 Mark Strickland (FA) F 6-10 220 7/14/70 Temple '92 7
31 Jason Terry G 6-2 176 9/15/77 Arizona '99 3
Row (orrecord)
Column (or field)
Database Management System
• A DBMS is a software system which takes care of Data definition Data storage and retrieval Data manipulation
Insertion Modification Deletion
Other functions, such as security, transaction control, archiving, backup and recovery
By using a DBMS, applications programmer can focus on business logic
File-Processing Systems
X
File-Processing Systems
Records are stored in separate files Drawbacks
Separated and isolated data Data duplication Application program dependency
Read from the product file for 20 bytes starting from location 1234, from the customer file ...
The difficulty of synchronize data changes The difficulty of representing data in the users’ perspectives
Why DBMS?
Database Systems
DB technology was developed largely to overcome the limitations of file systems
Advantages Integrated data Reduced data duplication Program/data independence, through the use of SQL
SELECT ProductName FROM Product WHERE ProductId = ‘P010’
Easier to update Easier representation of the users’ views
Relational Databases
The term relational is used because In RDB, data are stored in tables. Each table is a mathematical relation on a number of sets
Related tables are linked with shared columns
Aug 13 Adah Talbot ½ day in the afternoon$0.50
Month {Jan, …, Dec}Day {1, 2, …, 31}Worker {…}Time worked {…}Wage {…}
RDBMS Capabilities
Data definition Attributes – columns of your tables Data types – date, text, numeric, money, etc Relationships – among data in different tables
Data manipulation Data control – permission to use data
Fans can read statistics on website The Hawks manager can add, remove, and modify player
records
Data Definition
A database is self-describing Metadata – data used to define user data
or data about data
Aug 13 Adah Talbot ½ day in the afternoon$0.50
MonthDayWorkerTime workedWage
Text Number Text Text Money
Metadata, or schemaUser data
Data type Valid range
{Jan, …, Dec}{1, 2, …, 31}{…}{…}{…}
Field name
Example – Data Definition in Access
Example – Relationship in Access
Components of DB Systems
DB Users
Types of Users End users (naïve users)
Limited access to data, through forms or reports Application developers
Use programming languages w/ DB accessibility Sophisticated users
Use query languages and tools Specialized users
Write special applications, such as CAD, GIS, etc
DB Administrators
DBA is the central control over the system DBA functions include
Schema definition and modification Storage structure definition Granting authorization for data access
Read only Change (add and/or modify) Create tables and modify schema
Routine operation and maintenance
When to Use a DBMS?
A significant amount of related data Data about different subjects Multiple uses of these data
Data used by multiple users Changes to data should be synchronized
Accesses to data must be controlled
Sample DB Applications
In a local area network
Sample DB Applications
In a wide area network or over the Internet
Characteristics of Types of DBs
Type Sample Application
Number of Concurrent users
Typical Size of Database
Personal Wedding list 1 < 10 MB
Small Business
Entertainment scheduling
<25 < 100 MB
Organizational Licensing and registration
Hundreds > 1 trillion bytes
Internet Microsoft Press Books
Possibly Hundreds
Any
top related