Transcript Class Activity - Pearson Education

Before we begin…
• Introduce yourself. Do mention something
about your educational and professional
backgrounds.
• What are your goals for graduation?
What do you want to do??
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 1
IT 390 - Business Database Administration
Unit 1:
Database History, Models,
and Structure
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 2
Primary Lesson Objectives
• Explain the history, basic models and
components of databases.
• Identify the importance of Relational Algebra,
SQL, and ERD Diagrams.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 3
More Details…
•
•
•
•
•
•
•
•
•
Identify the components of a database and their uses.
Define DBMS and RDBMS.
Identify the differences between DBMS and RDBMS.
Identify the features and functionality of RDBMS.
Identify the data models used for organizing data.
Identify the types of databases and their functionality.
Analyze the E-R data model.
Identify the object-oriented logical data model.
Explain the basic components of databases.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 4
Databases: An Overview
• Databases are organized collection of related data.
• Databases can contain data of the following data
types at a single location:
 Text
 Graphic
 Audio
 Video
• An example is a Hotel which has a “guest” list with
details about guest (room number, length of stay,
mini-bar tab, etc.)
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 5
Features of a Database
• A database has the following features:
 Stores data in an organized manner.
 Searches and retrieves the stored data.
 Maintains a single copy of the data.
 Provides security to the stored data.
• Several corporations produce database software products.
Two BIG players are Microsoft and Oracle
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 6
Database?………I thought I was a __________ Guru???
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 7
Data and Information
• Data are raw facts that may or
may not be useful for the user.
• Information is processed data
that holds some meaning to the user.
• A physical database is just a file cabinet.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 8
Data Sharing instead of physical duplication
• Data sharing is defined as a process by which the
data stored at a single
location can be
simultaneously accessed
and modified by multiple users.
• Data sharing aims at reducing
the duplicity of data.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 9
Class Activity
• Which of the following has the most similar
structure to that of a database?
 A library of books
 A store containing paper and pens
 A car
 A video cassette
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 10
Solution
• A library resembles the structure of a
database. This is because as books are
arranged in each bookshelf of a library,
similarly, data is stored and arranged in
a database.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 11
Class Activity- Data vs. Information
• From the list below classify which of the
following is data and which is
information:




12
12*8=96
There are two people in the room.
Room
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 12
Solution
• Data is:


12
Room
• Information is:


12*8= 96
There are two people in the room
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 13
Class Activity- data sharing vs. non-sharing
• In a track and field sports meet, multiple events
are being conducted at different venues. The
overall results need to be declared and
compared, but the results for the track and field
events have not been updated properly or in a
timely manner.
• The result is angry participants and confused
spectators. What is this called in database terms?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 14
Solution
• Data inconsistency
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 15
Database Management Software
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 16
DBMS
Database
Database Software Vendors
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 17
Database Advantages
•
•
•
•
•
Data Integrity
Data Accuracy
Control of Data dependence
Control of Data redundancy
Better Security
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 18
Introducing Data Models
Define
the structure that needs to be conceptualized to implement
a database.
Data
models are:
A set
of data structure types
A collection
A set
of operators
of integrity rules
You can view data model as a prototype of a database. It’s the
framework for the way we want to implement our database.
Data Model
(Prototype)
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 19
Database
The Relational Database Model
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 20
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 21
Tables, also called RELATIONS
• Tables are a collection of records that are
arranged in the following format:
 Rows (Records or Tuples) / Horizontal
 Columns (Fields or Attributes) / Vertical
Columns
Rows
StudID
StudName
StudAge
S001
Jack
21
S002
Mary
20
S003
Kate
22
S004
Patrick
19
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 22
Components of a Database
• The main components of a database are:
 Record
S003

Kate
22
Table
StudID
StudName
StudAge
S001
Jack
21
S002
Mary
20
S003
Kate
22
S004
Patrick
19
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 23
Relational Database Management System
•
An RDBMS has the following components:
 Relation (table)
 Tuple (record or row)
 Attribute (column or field)
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 24
Other Types of Data Models
• Record-based logical models
• Network Model (Interconnected)
• Hierarchical Model
(Tree structure)
• Object-based logical models
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 25
File or Record-based Model
The data is structured in fixed format records.
For example, a firm that has outsourced
its processes to (partners in) multiple
locations might NEED to use a recordbased logical model.
The partners might not have the same
database system.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 26
Network Data Model
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 27
Hierarchical Data Model
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 28
Linux as an example of the Hierarchical Model
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 29
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 30
Object-Based Logical Models
Entity
Car
Color
Attributes
For example, an automobile
design firm that stores its
information for designing car
models might use an objectbased logical model.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 31
Model
More About Object-oriented Data Model
(OODM)
The Object-oriented data model (OODM) covers:
 Implementation for complex or large objects called
BLOBs (Binary Large OBjects) like images.
 High compatibility with Object-oriented programming
languages, such as C++ and Java.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 32
Class Activity
• Identify the type of database that needs to be
used for the scenario:
 A company needs to store information
including pictures on different types of
automobiles, parts and their common
characteristics, such as model, wheels, and
weights.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 33
Solution
• An Object-oriented database is suited for the scenario.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 34
Class Activity
• Identify the type of database that needs to be
used for the scenario.
 A company needs to store text information
on various departments of an organization.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 35
Solution
• A Relational database is suited for the
scenario.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 36
Class Activity
• Identify the type of database that needs to be
used for the scenario.
 A garment manufacturing unit wants to store
information of their product text feedback, such
as shrinkage, color, and texture. In addition,
the unit wants to integrate modeling video of
various garments into the existing
organizational database.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 37
Solution
• An Object-relational database is suited for the scenario.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 38
Class Activity
• Identify the components of OODM:
 Car
 Vehicle
 Ignition
 Motor-Bike
 Two-wheeler
 Shifting gears
 Four-wheeler
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 39
Solution
• The Objects are:
 Car
 Motor-Bike
• The Methods are:
 Ignition
 Shifting-gears
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 40
Solution (cont.)
• Identifying Inheritance
(letting other objects inherit characteristics)
Vehicle
Two-wheeler
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 41
Four-wheeler
Three-tiered Visualization
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 42
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 43
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 44
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 45
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 46
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 47
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 48
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 49
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 50
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 51
Database Domains
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 52
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 53
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 54
Class Activity- Keys
• What should be the primary key in the following
table structure of Employee_LoansTaken table:
 Employee name
 Amount of loan taken
 Type of security
 Employee ID code
 Amount still payable
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 55
Solutions
• Employee code should be the primary key
because:
 Employee ID code is unique for each
employee.
 Employee ID code cannot be blank (NULL).
 Other fields, such as employee name can
have same values for multiple occurrences.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 56
Class Activity
• Identify the Entities and Attributes in the
figure:
• What do the symbols mean in ERD
diagrams?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 57
Solutions
• The Entities are:
 Square
 Circle
 Triangle
• The Attributes are:
 Size
 Color
• The Symbols mean:
 Rectangle or Square is an Entity/table
 Oval or Circle is an attribute
 Diamond or Triangle is a relationship
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 58
Entity-Relationship Model
• An approach to representing data in a database
• Entities are drawn as rectangles
• Relationships are drawn as diamonds with lines
connecting the entities involved in relationships
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 59
One-to-many relationship
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 60
Many-to-Many Relationship
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 61
Many-to-Many Relationship
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 62
One-to-Many Relationship
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 63
Many-to-Many Relationship with Attributes
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 64
Composite Entity
• Composite entity - an entity that exists to implement
a many-to-many relationship

Essentially both an entity and a relationship

Represented in an E-R diagram by a diamond
within a rectangle
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 65
Composite Entity
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 66
Complete E-R Diagram
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 67
E-R Diagram with an Existence
Dependency and Weak Entity
• Existence dependency - when the existence of
one entity depends on the existence of another
related entity
• Indicate an existence dependency by placing an
“E” in the relationship diamond
• Weak entity - an entity that depends on another
entity for its own existence
• A double rectangle encloses a weak entity
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 68
E-R Diagram with an Existence
Dependency and Weak Entity
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 69
Cardinality
• Cardinality - the number of items that must be
included in a relationship
• An entity in a relationship with minimum
cardinality of zero plays an optional role in the
relationship
• An entity with a minimum cardinality of one plays
a mandatory role in the relationship
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 70
E-R Diagram that Represents Cardinality
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 71
Database Middleware
•
Integrates the existing data into a common
uniform framework.
•
Provides security and efficient
fault-and-performance management for DBAs.
•
Is of two types:

Native database middleware

Database generic middleware
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 72
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 73
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 74
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 75
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 76
Summary
• Database design is a two-part process of
determining an appropriate database structure to
satisfy a given set of requirements
• A user view is the set of necessary requirements to
support a particular user’s operations
• A database design is represented in a language
called Database Design Language (DBDL)
• Designs can be represented pictorially using entityrelationship (E-R) diagrams
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 77
Summary
• Advantages to both top-down and bottom-up
approaches
• One-to-One and One-to-Many relationships require
attention to primary keys
• Entity-relationship (E-R) model is a method of
representing the structure of a database using an ER diagram
• Nowadays, there are really only 2 kinds of
databases, Relational and Object-oriented. Other
deprecated models existed in the past like Network
and Hierarchical.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 78
• Question(s)??
• LAB TIME!!!
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 1 Slide 79