Transcript Databases

Databases
• A file is a set of related records
– A file is named by its owner
– It is a user’s minimal accessible unit on secondary
storage
• What if multiple people want to share
information (perhaps about employees)
– Do multiple files have the same information?
• Then how do we guarantee consistency when an
update is made?
– Can we maintain a single file?
• Then how do we guarantee proper access?
Chapter11 Databases
1
Database
• A database is a collection of organized
information
– In computer terms, information is contained in files
• Databases organize multiple files
– Provide answers to complex queries
• Databases control access
– Restrict rights to information
• Databases support data consistency
– Every user’s view has the most current values
Chapter11 Databases
2
Database Management
• Database Management tasks include:
– Collecting data
– Updating data (adding and deleting)
• Working with static data is called data mining
– Storing data for efficient retrieval
– Organization of data
• Sorting; securing access, etc.
– Outputting data – concurrency handled
Chapter11 Databases
3
Database Management
• Data input
– The most problematic part of database
management
– Humans may enter incorrect or partial values
• Handwriting may be illegible
• Keys or buttons may be punched incorrectly
– Sensors may enter incorrect data values
– It is difficult to correct false information
Chapter11 Databases
4
Data Organization
• If you have a telephone directory, how do
you insert a name in the middle?
• If you have a database, data can be inserted
at the end (or wherever there is room) and
the system will order it logically
• Do you have several lists of people that you
have invited to different parties. Do they
overlap? What if an address gets changed?
Chapter11 Databases
5
Databases in Use
• Banks, Credit Card Companies, Search
engines, Super Markets with customer
cards, charities all maintain databases
– Mailing lists are generated by selecting:
• Name and Addresses from perhaps multiple files
– Select name, address from *
– Selected names can be targeted based on
attributes
– Select from * where last-payment-year < 2011
Chapter11 Databases
6
Data mining
• Data extracted from multiple databases and
stored in data warehouse
• Data from operational (active) databases
may also be extracted and copied to data
warehouse
• Analysis can result in, for example,
– Lower insurance rates based on expectations
– Smaller reorders of inventory items
Chapter11 Databases
7
Predictive analytics
• Statistical algorithms, neural networks, and
optimization research applied to data
mining to find patterns in data
– Predict the amount of money a state must set
aside for employee pensions
– Predict the lottery numbers to be chosen this
May
– Predict how much a house will appreciate in
value from 2007 to 2011
Chapter11 Databases
8
How to store data
• Simplest way to store data is in a flat file
– For example, an Excel spreadsheet
– Rows and columns in an Excel flat file
• Field- smallest building block
– Has a unique identifier
– Variable or fixed length
• Record: collection of related fields
– Medical records
Chapter11 Databases
9
Record type
• Student record
–
–
–
–
Name
ID
Starting Date
Courses enrolled in
• Note: typically an ID is the unique key for
a specific student record
• Name, Id, date, courses are fields
(attributes)
Chapter11 Databases
10
Record occurrence (instance)
•
•
•
•
Name
ID
Starting Date
Courses
:
:
:
:
John Doe
0003456
1/12/09
CSCI 1105; ENG 2001
Data fields are variable in length; data values are typically assigned
and changed dynamically.
Course attributes doesn’t fit with the other static data.
Chapter11 Databases
11
Relationships
• “A relationship is an association between
data that is stored in different record types”
• Given: a file of student records
• Given: a file of courses (with attributes of
course #, room #)
• Relationship
– For each student in the student record file, find
the classroom that they are in on Monday April
7th (if any)
Chapter11 Databases
12
Relational Database Mgmt Systems
• Collections of related tables
– Think of an Excel spreadsheet
– Each table is a sequence of records
• Each row is a record instance
• Each column defines attributes (field)
Chapter11 Databases
13
Vintage Music Shop (from text)
• Table 1
• Albums
Cat# Title ArtistName ReleaseDate
1011 AA
Ella
2/10/59
1010 BB
George
2/15/64
1101 CC
William 4/1/00
InStock
no
yes
yes
SellingPrice
45.95
22.05
5.85
Each record must have a unique key (possibly combined fields)
Chapter11 Databases
14
Vintage Music Shop
• Table 2
• Orders
OrderNumber CustomerNumber Total OrderDate
Chapter11 Databases
15
Vintage Music Shop
• Table 3
• OrderDetails
OrderNumber
CatNumber
Quantity
Chapter11 Databases
DiscountPrice
16
Vintage Music Shop
• Table 4
• Customers
Cust ID FirstName
LastName Street City Tel#
Chapter11 Databases
17
Vintage Music Shop
• Table 5
• AlbumDetails
Cat# TrackTitle TrackLength TrackSample
Chapter11 Databases
18
How to partition data into tables?
• You don’t want customers and orders in one
table.
– A customer might have many orders
• You don’t want orders and order details in
one table
– Order details lets you access the specific item
and quantity for inventory purposes
– Orders are for billing customer
Chapter11 Databases
19
Database design is not obvious
• Just like PowerPoint, it takes some practice
to separate information
• Placing information in different “modules”
makes it easier to handle
Chapter11 Databases
20
Dimensional Database Systems
• Your multi-worksheets for drugstore
inventory were 3-dimensional
– Rows – different items handled
– Columns – sales each month
– Years 2009/2010 were a 3rd dimension
• Relational database systems can be 3dimensional
Chapter11 Databases
21
Object oriented databases
• Data are stored as objects
• Objects are grouped into classes
– Each object is an instance of a class
• Attributes for the class are the same as fields
– Valid methods are also defined for the class
• For Vintage Record Company, method for the class
Orders might include checkInventory,
checkCustomerCredit
Chapter11 Databases
22
Inheritance in Object-oriented databases
• If different records have some different
attributes, the database might have to
contain different record types
– Customer form for phone or web customers
• In object-oriented databases, methods are
defined on the class type and differentiated
for different types of orders
Chapter11 Databases
23
Relational and OO databases
• Some relational databases have been
modified to include object-oriented features
– SQL, besides being used for queries, can also
be used to define functions, such as for playing
a song
Chapter11 Databases
24
Data management with spreadsheets
• Columns// fields or attributes
• Rows// records or instances
• Methods// sort, search, some mathematical
functions, graphs
• Visual Basic code, macros can be inserted
into Excel for other functions
Chapter11 Databases
25
Types of DBMS
• Entry level
– Microsoft Access
• Cannot scale up to large amounts of traffic
• Public domain SQLite
• Database server software
–
–
–
–
Oracle Database
IBM DB2
Microsoft SQL Server
Sun’s open source MySQL
Chapter11 Databases
26
Database access through the Web
• Static Web publishing
– Convert database report into HTML document
• Displayed by browser
– Data is read only; it’s not changed dynamically
– Secure (read access-- to web page , not to
database)
– Simple (access allows you to “export” the data
as an HTML or XML file)
Chapter11 Databases
27
“view” in databases
• It is possible to allow different users to see
only part of the data
– This is called a view
• Providing views on the web requires serverside programming
– Cookie on customer (client) site provides client
identification
– Client id is used to generate a query to the DB
Chapter11 Databases
28
Updating a database
• Clients can be provided rights to make
changes in the data values
– Not changes in attributes – that is DBMS
function
• On the web, a form is provided to customers
– HTML forms, for example, with JavaScript
• Server-side programs such as ASP, CGI,
PHP, ColdFusion process client input
Chapter11 Databases
29
XML
• XML as a standard for client output and
input
• alpha.fdu.edu/~levine/xml/albums.xml
• alpha.fdu.edu/~levine/xml/album1.xml
Chapter11 Databases
30