CIT 288 Database Management Concepts

1
CIT 288 Database Management Concepts

• Introduction to Database Management
• Chapter 1 and 2 p33

2
Objectives

• Introduce Premiere Products, the company that is
  used as the basis for many of the examples
  throughout the text
• Introduce basic database terminology
• Describe database management systems
• Explain the advantages and disadvantages of
  database processing
• Introduce Henry Books, the company that is used
  in the case that runs throughout the text

3
Premiere Products

• Distributor of appliances, house wares, and
  sporting goods
• Uses spreadsheet software to maintain important
  data
• Recent growth has made the spreadsheet approach
  problematic
• Redundancy
• Difficulty accessing data
• Limited security
• Size limitations

4
Premiere Products Required Information

• Sales Reps
• Sales rep number, last name, first name, address,
  total commission, commission rate
• Customers
• Customer number, name, address, current balance,
  credit limit, customer sales rep
• Parts Inventory
• Part number, description, number units on hand,
  item class, warehouse number, unit price

5
Premiere Products Sample Order Figure 1.2
6
Premiere Products Customer Order

• Order
• Order number, order date, customer number
• Order line
• Order number, part number, number units ordered,
  unit price
• Overall order total
• Not stored since it can be calculated

7
Database Background

• Database
• Structure to store information about multiple
  types of entities, attributes, and relationships
• Entity
• Person, place, thing, or event
• Premiere Products has sales reps, customers,
  orders, and parts
• Attribute
• Property of an entity
• Customer has name, street, city, et cetera
• Relationship An Association between entities
• Customers are related Sale Reps and vice versa.

8
Entities and Attributes Figure 1.3
9
Database Background (cont.)

• Relationship
• Association between entities
• Rep is related to many customers
• Customer is related to a single rep
• Data file
• File used to store data
• Computer counterpart to ordinary paper file

10
One-to-Many Relationship Figure 1.4
11
Rep and Customer Tables Figure 1.5
12
Orders and OrderLine Tables Figure 1.5 (cont.)
13
Part Table Figure 1.5 (cont.)
14
Alternative Orders Table Figure 1.6
15
Non-Database Approach to Data Storage
Class Programs
Class Files
Advising Programs
Advising Files
Intramural Programs
Intramural Files
16
Disadvantages of the Non-Database approach

• Separated and Isolated Data
• Data Duplication
• Application Program Dependency
• Incompatible File Formats

17
Disadvantages of the Non-Database approach

• Separated and Isolated Data
• Each application has its own private files and
  users have little opportunity to share data
  outside their own application
• Data Duplication
• Since applications are usually developed
  independently, unplanned duplicate data files are
  the rule rather than the exception. Example The
  same data may be represented in different
  applications by different names.

18
Disadvantages of the Non-Database approach

• Application Program Dependency
• File descriptions are stored within each
  application program that accesses a given file.
  Any changes made to the file structure requires
  changes to the files descriptions as well.
• Incompatible Files
• Even if different applications can share their
  data, the files might be structures in
  incompatible formats and not allow the files to
  be read by the other application.

19
Database Management Systems

• Program(s) through which users interact with
  database
• Popular DBMSs include Access, Oracle, DB2, SQL
  Server
• Premiere Products decides to use Access

20
Building a Database

• Database design determines the structure of a
  database
• Design entered into DBMS during Construction
• Tables
• Forms
• Reports
• Switchboards

21
Database Management Approach to Data Storage
The Database Contains all of the information
necessary to supportthe Class, Advising and
Intramural Applications
Class Application
Advising Application
Intramural Application
22
Advantages of Database Processing Figure 1.14
23
Advantages of Database Processing

• Getting more information from the same amount of
  data
• When all the data for various systems are stored
  in a single database, the information becomes
  available, as well as the process of retrieving
  the information can be quick and easy

24
Advantages of Database Processing

• Sharing of Data
• Several users can have access to the same piece
  of data
• Balancing conflicting requirements
• A person or group, often called Database
  Administration/Administrator (DBA) can structure
  the database in such a way that it benefits the
  entire organization, not just a single group

25
Advantages of Database Processing

• Controlling redundancy
• Not only saves space, but makes the updating
  process easier
• Consistency
• Consistency is a direct result of redundancy, so
  by reducing redundancy, there is much less
  potential for this sort of inconsistency with the
  database approach

26
Advantages of Database Processing

• Data Integrity
• An integrity constraint is a rule that must be
  followed by data in the database
• Example Not allowing a persons age to be lower
  than zero
• Data Security
• The prevention of access to the database by
  unauthorized users

27
Advantages of Database Processing

• Increasing productivity
• A good DBMS comes with many features that allow
  users to gain access to data without having to do
  any programming at all
• Data independence
• A property that allows the structure of a
  database to be changed without the programs that
  access the database having to change

28
Disadvantages of Database Processing Figure 1.15
29
Disadvantages of Database Processing

• Larger File Size
• DBMSs are large programs that occupy a large
  amount of disk space as well as internal memory
• Increased Complexity
• The complexity and breadth of the functions
  provided by a DBMS make it a complex product to
  use

30
Disadvantages of Database Processing

• Greater Impact of Failure
• A failure on the part of any one user that
  damages the database in some way may affect all
  the other users on the system
• More Difficult Recovery
• If the database is being updated by a large
  number of users, all updates must be redone since
  the time of its restoration

31
Relational Databases

• Collection of tables
• Each entity in own table
• Attributes are fields (columns) in table
• Relationships are common columns in two or more
  tables
• Order of rows and columns is immaterial
• Repeating groups are not permitted
• Entries with repeating groups are unnormalized

32
Relations

• Two dimensional table in which
• Entries are single-valued
• Each column (field or attribute) has a distinct
  name
• All values in a column represent the same
  attribute
• Order of columns is immaterial
• Each row (record or tuple) is distinct
• Order of rows is immaterial

33
Relational Database Model Relationships
Entity 1 (PRIMARY KEY, ATTRIBUTES.)
Entity 2 (PRIMARY KEY, FOREIGN KEY, ATTRIBUTES.)
34
Introduction to Henry Books Database Case

• Book store chain operated by Ray Henry
• Henry decided to use database to gather and store
  information on
• Branches
• Publishers
• Authors
• Books

35
Sample Branch Data Figure 1.16
Branch
Book Branch
Author
Publisher
Book
Book Author
36
Relational Database Shorthand
Table Name(Primary Key, Attributes.., Foreign
Keys )
Book (Book Code, Title, Publisher Code, Author
Number) Author (Author Number, Name) Publisher
(Publisher Code, Name, City)
37
Components of a Database System
Developers
Database Class Files Advising Files Intramural
Files
Application Programs
Users
Application Programs