Technology Review

1
Technology Review

• Professor Martin
• Professor Xiong
• CSUS
• This lecture is based primarily on Romney
  Steinbart(2003). It also draws on Martin (2002).
• Updated on Monday, January 27, 2003

2
Agenda

• Database ManagementAn Introduction
• Relational Database
• Entity-Relationship Diagram

3
WHAT IS DATA MANAGEMENT?(The first seven slides
are based on Watson (2002))

• The management of organizational memory
• Involves designing, using, and managing memory
  systems of modern organizations

4
EXAMPLES OF INDIVIDUAL AND ORGANIZATIONAL MEMORY
SYSTEMS

• INDIVIDUAL
• Internal memory
• External memory (diaries, bookmarks, address
  books)
• ORGANIZATIONAL
• examples people, filing cabinets, policy manuals,
  planning boards, and computers. (Do organizations
  have external memories?)
• Characteristics similar to Individual memory

5
DESIRABLE ATTRIBUTES OF ORGANIZATIONAL MEMORY

• Shareable
• readily accessed by more than one person at a
  time
• Transportable
• Easily moved to a decision maker
• Secure
• Protected from destruction and unauthorized use
• Accurate
• Reliable, precise records
• Timely
• Current and up-to-date
• Relevant
• Appropriate to the decision

6
TYPICAL PROBLEMS WITH FILE-BASED SYSTEMS

• Organizational memory may be seen as a vast,
  disorganized data warehouse. Problems include
• Redundancy same data stored in different
  memories
• Data control data not managed as a valuable
  resource
• Interface difficult to access data
• Delays long delays in responding to requests for
  data
• Lack of reality data do not reflect the
  complexity of the real world
• Lack of data integration data dispersed across
  different systems also where data is stored may
  not be known.

7
File-Oriented Approach
File 1
Application program 1
Item A Item B Item C
File 2
Application program 2
Item B Item D Item E
8
DATABASE APPROACH TO MANAGING PERSISTENT DATA

• The database approach emphasizes the integration
  and sharing of data across the organization.

9
Database Approach
Application program 1
Database
Item A Item B Item C Item D Item E
Application program 2
Database management system
Application program 3
10
BENEFITS OF THE DATABASE APPROACH

• Redundancy can be reduced
• Thus, inconsistency can be avoided
• Integration of data
• Data can be shared among applications
• Standards can be enforced by the DBA
• formats, representation, naming, documentation
• Security restrictions can be applied

11
BENEFITS OF THE DATABASE APPROACH

• Data integrity can be maintained
• by minimizing inconsistency
• by having controls to check against incorrect
  updates, especially in the multi-user context

12
BENEFITS OF THE DATABASE APPROACH

• Data independence
• Broadly -- the immunity of applications to change
  in storage structure and access technique
• Logical -- capacity to change conceptual schema
  without changing application programs (e.g.,
  adding an attribute or an entity type)
• Physical -- capacity to change internal schema
  without having to change external or conceptual
  schema (e.g., creating additional access
  structures to improve retrieval performance)
• Ease of Application Development
• Data accessibility and responsiveness enhanced
• Reduced program maintenance

13
SOME DEFINITIONS

• What is a database?
• a shared collection of logically related
  persistent data, designed to meet the needs of
  multiple users usually within an organization.
• What is a database management system?
• DBMS is a collection of programs that enables
  users to define, construct and manipulate a
  database. (More detailed defn. later).
• What is a database system?

14
FUNCTIONS OF A DBMS

• Data definition using DDL
• Data manipulation using DML
• Data security and integrity
• Data recovery and concurrency control
• Data dictionary
• Satisfactory performance

15
STEPS IN DATABASE DEVELOPMENT PROCESS

• Analysis
• creation of the Entity-Relationship Model
• Design
• Logical Database Design
• creation of normalized relations
• Physical Database Design
• specification storage technology requirements
• specification/ creation of appropriate file
  structures

16
Schemas

• What are schemas?
• A schema describes the logical structure of a
  database.
• There are three levels of schemas
• Conceptual-level schema
• External-level schema
• Internal-level schema

17
Schemas

• The conceptual-level schema is an
  organization-wide view of the entire database.
• The external-level schema consists of a set of
  individual user views of portions of the
  database, also referred to as a subschema.
• The internal-level schema provides a low-level
  view of the database.

18
Agenda

• Database ManagementAn Introduction
• Relational Database
• Entity-Relationship Diagram

19
Relational Databases

• A data model is an abstract representation of the
  contents of a database.
• The relational data model represents everything
  in the database as being stored in the form of
  tables.
• Technically, these tables are called relations.

20
Basic Requirements of the Relational Data Model

• Primary keys must be unique.
• Every foreign key must either be null or have a
  value corresponding to the value of a primary key
  in another relation.
• Each column in a table must describe a
  characteristic of the object identified by the
  primary key.

21
Basic Requirements of the Relational Data Model

• Each column in a row must be single-valued.
• The value in every row of a specific column must
  be of the same data type.
• Neither column order nor row order is significant.

22
Accessing records

• Records are typically updated,
  stored, and retrieved using an
  identifier called a primary key
• customer number for customer file
• invoice number for invoice file
• stock number for inventory file

23
Accessing Records

• A secondary key is another field used
  to identify a record
• Secondary keys do not uniquely identify
  individual records
• Examples of secondary keys
• invoice due date
• zip code
• bank customer last name

24
Accessing Records

• Foreign key attribute
  (field) in one table (record) that
  matches primary key in another table
• Used to link tables together

25
Relational Database
Vendor Table

Product Table
26
Relational Databases
27
Agenda

• Database ManagementAn Introduction
• Relational Database
• Entity-Relationship Diagram

28
ENTITY-RELATIONSHIP MODEL (proposed by CHEN,
1976)

• A detailed logical representation of data for an
  organization or business area
• Four Basic Constructs -Entity -Relationship -A
  ttribute -Cardinality (participation)

29
ENTITY Entities are named objects in the
universe of discourse

• Types of entities
• Thing (truck, building)
• Person (customer, employee)
• Event
• Instant duration (sale, purchase, cash receipt)
• Extended duration (month-long use of a truck, a
  course offering that starts on JAN 3 ends on 15
  May)
• Concept (category of customer, course)
• SYMBOL -- Rectangle

30
2. RELATIONSHIP Association between two (or
more ?) entities

• Examples
• employee assigned to building
• customer participates in sale
• professor teaches course-offering
• SYMBOL -- Diamond

participates in
Customer
Sale
31
3. ATTRIBUTE Characteristics or elementary
properties of entities or relationships. They
are used for actual communication about the real
world phenomena represented by entities or
relationships

• Example attributes for the entity INVENTORY
• stock, color, price, cost, weight
• A primary key is a special attribute used to
  represent an instance of an entity or
  relationship in a database
• Must be unique and universal
• Can be a concatenated (combined key)
• No representation without identification
• For this class, we assume that relationships are
  identified by the keys of their participating
  entities
• SYMBOL small connected circle (filled in for
  primary key)

Stock Color Price
32
4. Participation CARDINALITY (min, max) These
show the correspondence of entities and
relationships
A
B
rel
(min, max)
Entity A participates in relationship rel at
a maximum of - 1 time (single time only) -
n times (many times)
Entity A participates in relationship rel at
a minimum of - 0 times (optional) - 1 time
(mandatory)
33
4. Participation CARDINALITY (min, max)
(other side of relationship)
B
A
rel
(min, max)
Entity B participates in relationship rel at
a maximum of - 1 time (single time only) -
n times (many times)
Entity B participates in relationship rel at
a minimum of - 0 times (optional) - 1 time
(mandatory)
34
An Example

• Assuming two entities (EMPLOYEE and COURSE), draw
  an E-R diagram for the following (sample data).
  Assume that Employee_name and Course_titles are
  unique. Also assume other attributes such as
  Employee Address, and Course Credits.
• Employee_name Course_title Date_completed
• Chen C 06/98
• Chen Java 09/98
• Lisa C 06/98
• Lisa SQL 03/99
• Trina Java 03/98
• Heikki Perl 06/98
• Heikki Java 09/98
• . . ..
• . . ..

35
More Examples

• A company has a number of employees. The
  attributes of EMPLOYEE include NAME, ADDRESS, and
  BIRTH-DATE. The company also has several
  projects. The attributes of Project include
  PROJECT_CODE, DESCRIPTION, and START_DATE. Each
  employee may be assigned to one or more projects,
  or may not be assigned to any project. A project
  is required to have at least one employee
  assigned, but may have several employees
  assigned.
• A university has a large number of courses in its
  catalog. Attributes of courses include CRS_NO,
  CRS_NAME, and UNITS. Each course may have one or
  more other courses as prerequisites, or may have
  no prerequisite.

36
Assignment

• A college course may have one or more scheduled
  sections, or may not have a scheduled section.
  COURSE attributes include CRS_ID, CRS_NAME, and
  UNITS. Attributes of SECTION include SECTION_NO
  and INSTRUCTOR.
• A laboratory has several chemists who work on
  various projects, and who may use certain kinds
  of equipment on each project. Attributes of
  CHEMIST include CHEMIST_ID, NAME, and PHONE.
  Attributes of PROJECT include PROJ_ID and
  START_DATE. Attributes of EQUIPMENT include
  EQUIP_NO and COST.

37
Topics Discussed

• Database ManagementAn Introduction
• Relational Database
• Entity-Relationship Diagram