Database Management 1 Week1 Lecturer: Ms Pearl Teng/Mr Seetoh

Managing Business Systems (MBS)
BBT1002
Database Management 1
Week1
Lecturer: Ms Pearl Teng/Mr Seetoh

Management Information Systems
Chapter 6: Foundations of Business Intelligence
Learning Objectives
• Describe basic file organization concepts

• Describe how the problems of managing data resources in a
traditional file environment are solved by a database
management system.
• Apply important database design principles.
6.2 Copyright © 2014 Pearson Education, Inc.

The Value of Databases

• Databases are a key ingredient of today’s most
popular Internet services, and the backbone of our
information-driven economy.

File organization concepts
– Database: Group of related files

– File: Group of records of same type
– Record: Group of related fields
– Field: Group of characters as word(s) or
number
• Describes an entity (person, place, thing on which we
store information)
• Attribute: Each characteristic, or quality, describing
entity
– Example: Attributes DATE or GRADE belong to entity COURSE
THE DATA
Chapter 6: FoundationsHIERARCHY
of Business Intelligence

Entities and Attributes


Problems with the traditional file environment
(files maintained separately by different departments)
– Data redundancy:
• Presence of duplicate data in multiple files
– Data inconsistency:
• Same attribute has different values
– Program-data dependence:
• When changes in program requires changes to data
accessed by program
– Lack of flexibility
– Poor security
– Lack of data sharing and availability

TRADITIONAL FILE PROCESSING

The Database Approach to Data Management

• Database
– Serves many applications by centralizing data and
controlling redundant data
• Database management system (DBMS)
– Interfaces between applications and physical data files
– Separates logical and physical views of data
– Solves problems of traditional file environment
– Examples of DBMS: Microsoft Access, DB2, Oracle
Database, Microsoft SQL Server, MYSQL

HUMAN RESOURCES DATABASE WITH
MULTIPLE VIEWS


• Relational DBMS
– Represent data as two-dimensional tables
– Each table contains data on entity and attributes
• Table: grid of columns and rows

– Rows (tuples): Records for different entities
– Fields (columns): Represents attribute for entity
– Primary key: Attribute used to uniquely identified a record
– Foreign key: Attribute used to link two or more tables or
entities together
– Secondary key: Attribute used to search records

Relational Database Tables

A relational database organizes
data in the form of two-
dimensional tables. Illustrated
here are tables for the entities
SUPPLIER and PART showing
how they represent each entity
and its attributes. Supplier
Number is a primary key for
the SUPPLIER table and a
foreign key for the PART table.
FIGURE 6-4


• Designing Databases
– Conceptual (logical) design: abstract model from business perspective
– Physical design: How database is arranged on direct-access storage
devices
• Design process identifies:

– Relationships among data elements, redundant database elements
– Most efficient way to group data elements to meet business
requirements, needs of application programs
• Normalization
– Streamlining complex groupings of data to minimize redundant data
elements and awkward many-to-many relationships

AN UNNORMALIZED RELATION FOR ORDER
Orders (Before Normalization)
Ord Ord Part Part Unit Part Supplier S S S S S

No Date No Nme Price Qty No. Nme Street City State Zip
3502 1/15 137 Latch 22.0 10 8259 CBM 74 Ave Dayton OH 45220
3502 1/15 152 Lock 31.0 20 8259 CBM 74 Ave Dayton OH 45220
3502 1/15 178 Handle 10.0 5 8259 CBM 74 Ave Dayton OH 45220
FIGURE 6-9 An unnormalized relation contains repeating groups. For example, there can be many parts and suppliers for
each order. There is only a one-to-one correspondence between Order_Number and Order_Date.

NORMALIZED TABLES CREATED FROM ORDER
FIGURE 6-10 After normalization, the original relation ORDER has been broken down into four smaller relations. The
relation ORDER is left with only two attributes and the relation LINE_ITEM has a combined, or concatenated,
key consisting of Order_Number and Part_Number.

The Final Database Design with
Sample Records
Figure 5-5
The final design of the
database for suppliers,
parts, and orders has
four tables. The
LINE_ITEM table is a
join table that
eliminates the many-
to-many relationship
between ORDER and
PART.
6.16 16 Pearson Education, Inc.

Copyright © 2014
Sample Order Report
Figure 5-4
The shaded areas
show which data
came from the
SUPPLIER,
LINE_ITEM, and
ORDER tables. The
database does not
maintain data on
Extended Price or
Order Total because
they can be derived
from other data in the
tables.
6.17 17 Pearson Education, Inc.

Copyright © 2014

• Referential integrity rules
• Used by RDMS to ensure relationships between tables
remain consistent
• Entity-relationship diagram
– Used by database designers to document the data model
– Illustrates relationships between entities (one-to-one,
one-to-many, many-to-many)
– Caution: If a business doesn’t get data model
right, system won’t be able to serve business
well
AN ENTITY-RELATIONSHIP DIAGRAM
FIGURE 6-11 This diagram shows the relationships between the entities SUPPLIER, PART, LINE_ITEM, and ORDER that
might be used to model the database in Figure 6-10.

• Operations of a Relational DBMS

– Three basic operations used to develop useful
sets of data
• SELECT: Creates subset of data of all records that
meet stated criteria
• JOIN: Combines relational tables to provide user
with more information than available in individual
tables
• PROJECT: Creates subset of columns in table,
creating tables with only the information specified

THE Chapter
THREE 6: Foundations of Business Intelligence
BASIC OPERATIONS OF A
RELATIONAL DBMS
FIGURE 6-5 The select, join, and project operations enable data from two different tables to be combined and only selected
attributes to be displayed.

References
• Chapter 6, Essentials of Management

Information Systems, 11th Edition,
Kenneth C. Laudon & Jane P.
Laudon, Pearson Education

Database Management 1 Week1 Lecturer: Ms Pearl Teng/Mr Seetoh

Uploaded by

Copyright:

Available Formats

Database Management 1 Week1 Lecturer: Ms Pearl Teng/Mr Seetoh

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Database Management 1 Week1 Lecturer: Ms Pearl Teng/Mr Seetoh

Uploaded by

Copyright:

Available Formats

Managing Business Systems (MBS)

Lecturer: Ms Pearl Teng/Mr Seetoh

• Describe basic file organization concepts

6.2 Copyright © 2014 Pearson Education, Inc.

The Value of Databases

6.3 Copyright © 2014 Pearson Education, Inc.

– Database: Group of related files

6.5 Copyright © 2014 Pearson Education, Inc.

Entities and Attributes

6.6 Copyright © 2014 Pearson Education, Inc.

6.7 Copyright © 2014 Pearson Education, Inc.

TRADITIONAL FILE PROCESSING

6.8 Copyright © 2014 Pearson Education, Inc.

The Database Approach to Data Management

6.9 Copyright © 2014 Pearson Education, Inc.

6.10 Copyright © 2014 Pearson Education, Inc.

The Database Approach to Data Management

• Table: grid of columns and rows

6.11 Copyright © 2014 Pearson Education, Inc.

Relational Database Tables

6.12 Copyright © 2014 Pearson Education, Inc.

The Database Approach to Data Management

• Design process identifies:

6.13 Copyright © 2014 Pearson Education, Inc.

AN UNNORMALIZED RELATION FOR ORDER

Orders (Before Normalization)

Ord Ord Part Part Unit Part Supplier S S S S S

6.14 Copyright © 2014 Pearson Education, Inc.

NORMALIZED TABLES CREATED FROM ORDER

6.15 Copyright © 2014 Pearson Education, Inc.

6.16 16 Pearson Education, Inc.

6.17 17 Pearson Education, Inc.

The Database Approach to Data Management

6.19 Copyright © 2014 Pearson Education, Inc.

The Database Approach to Data Management

• Operations of a Relational DBMS

6.20 Copyright © 2014 Pearson Education, Inc.

6.21 Copyright © 2014 Pearson Education, Inc.

• Chapter 6, Essentials of Management

6.22 Copyright © 2014 Pearson Education, Inc.

You might also like