Transcript DBch1

CSE 2337
Introduction to
Data Management
Textbook:
Chapter 1
1
Overview
• Premiere Products Example used in
text
• Basic Database (DB) terminology
• Basic Database Management
Systems (DBMS)
• Advantages and disadvantages of
databases
2
Premiere Products
• Example Company used in the text
• Distribute appliances, sporting
goods, etc.
• Use a spreadsheet to maintain order,
customer, sales agent data
– Spreadsheet approach becoming
problematic
3
4
Spreadsheet Problems
• Redundant Data
– Data is duplicated in more places than it
is really needed
Consider: How many customers
purchased xyz from Premiere in
month abc?
• Very difficult to access subsets of
data
• Security and size limitations
5
Premiere’s Important Info
• Sales Reps
– Rep #, last name, first name, address,
total commission, commission rate
• Customers
– Customer #, name, address, current
balance, credit limit, Rep #
• Parts Inventory
– Part #, description, quantity on hand,
item class, warehouse number, unit
price
6
Premiere’s Important Info
• Order
– Order #, date, cust #
• Order line
– Order #, part #, quantity ordered, unit
price
• Overall Order total
– Not store because calculated
7
Database
• A database is a structure that can
store information about
– Multiple entities
– Multiple attributes for each entity
– Relationships among entities
8
Entities and Attributes
• Person, place, thing, event for which
information is being held (nouns)
– Entities for Premiere???
• Attribute
– Property of an entity (adjective)
– aka. field and column
– Attributes of entities for Premiere?
9
Entities vs. Attributes
Rep
RepNum LastName FirstName Street
City
10
Relationships
• A relationship is an association
between entities.
– Three types
• One to One
• One to Many
• Many to Many
11
One-to-One Relationship
• Customer has one and only one sales
representative
One-to-Many Relationship
• Each (one) representative is
associated with multiple customers
12
How are relationships realized?
• Using/relating common attributes
between two entities
• Example: Both the Rep and
Customer entities have a repNum
attribute
• Can be used to relate customers to
sales reps OR sales reps to
customers
More to come about this…
13
Figure 1.5: Rep and Customer Tables
14
Figure 1.5: Orders and OrderLine Tables (con’t.)
15
Entity-Relationship Diagram
• Visual representation of the logical
structure of a database
– Visualizes entities, attributes, and
relationships
– Entities are boxes
– Relationships are lines that connect
entities
16
Fig. 1.7: E-R Diagram
17
Database Management Systems
• Executable program(s) that allow the
user to interact with the database
• Examples
– Microsoft Access
– Oracle 10g
– IBM DB2
– Microsoft SQL Server
18
Where’s that Data?
• Actual data is stored in data
structures in a file
– Computer counterpart paper file
• Could be stored in multiple files or
one file
• Point: The user doesn’t have to
worry about that level of detail!
19
Building a Database
• Design of DB determines the logical
structure of the data
• Not concerned with how it is stored
in the computer
• In Access, this means designing
– Tables, attributes and relationships
– Forms - screens to maintain, view and
print from a DB
– Reports - formatted output of data from
DB
20
An Access Form
21
An Access Form
22
Advantages of DBMS
1. Getting more information from the
same amount of data
2. Allows efficient data sharing
3. Balancing conflicting requirements
4. Controls redundancy
5. Facilitates consistency
23
(more) Advantages of a DBMS
6. Improving Integrity of Data
7. Expanded security
8. Increased productivity
9. Provides data independence
24
Disadvantages of a DBMS
1. Larger file size
2. Increased complexity
3. Greater impact of failure
4. More difficult to recover
25
Summary
• Problems with nonDB data
management
• Definitions of Entity, Attribute,
Relationship
• Three types of relationships
• Entity Relationship Diagram
• + and - of DBMSs
26