IM 22 Current Issues in Information Management

1
IM 22Current Issues in Information Management

• Department of Computer Science
• Xavier University

2
Database Concepts

• A database
• An electronic collection of texts, numeric
  values, graphs, or images which are
  systematically processed and stored in computers
• a structure that can house information about
  multiple types of entities, as well as
  relationships among the entities

3
Database Concepts

• An entity is really just like a noun it is a
  person, place or thing. It is represented as a
  record in a database.
• An attribute is a property of an entity. It is
  represented as a field in a database record.
• A relationship is an association between
  entities. It is represented using a common field
  in a set of records.

4
Database Concepts
attributes

• ID Number
• Name
• Address
• Salary

Employee
relationship
entities
works
attributes
Department

• Dept Code
• Name
• Location

5
Database Concepts

• Database Management System (DBMS)
• software product through which users interact
  with a database (e.g. querying, updating data)
• handles the actual manipulation of the underlying
  database structure and its content

6
Database Concepts

• Functions of a DBMS
• data storage, retrieval and update
• a user-accessible catalog or data dictionary
• recovery services
• authorization services
• support for data communication and networking

7
Database Concepts

• Stages of Database Development
• Planning
• Analysis and Design
• Implementation/Programming
• Operation and Maintenance
• Growth and Change

8
Database Concepts

• Analysis and Design
• defining content, scope and users
• defining output requirements - e.g. screen and
  printed reports
• defining database record structures (i.e. in
  terms of attributes, entities and relationships)
• defining data entry screens
• defining user-interface

9
Database Concepts

• Implementation
• selecting the appropriate DBMS software package
• creating database structure (i.e. records,
  fields, relationships, constraints, indexes,
  etc...)
• creating data entry screens
• creating report and display formats
• developing the application user-interface

10
Database Concepts

• Operation and Maintenance
• entering and editing records
• retrieving records using queries and reports
• back-up and recovery

11
Database Concepts

• Database System
• the DBMS software together with the data itself
• sometimes, the applications are also included

12
Database Concepts

• Example of a Database Conceptual Data Model
• Mini-world for the example Part of a UNIVERSITY
  environment
• What are the entities?
• What are the attributes of these?
• How are the entities related?

13
Database Concepts

• Example of a Database Conceptual Data Model
• Some mini-world entities (Data elements)
• STUDENTs
• COURSEs
• SECTIONs (of COURSEs)
• DEPARTMENTs
• INSTRUCTORs

14
Database Concepts

• Example of a Database Conceptual Data Model
• Some mini-world relationships
• SECTIONs are of specific COURSEs
• STUDENTs take SECTIONs
• COURSEs have prerequisite COURSEs
• INSTRUCTORs teach SECTIONs
• COURSEs are offered by DEPARTMENTs
• STUDENTs major in DEPARTMENTs

15
Database Concepts

• Data Modeling ER Model Constructs
• Entity Instance person, place, object, event,
  concept (often corresponds to a row in a table)
• Entity Type collection of entities (often
  corresponds to a table)

16
Database Concepts

• Data Modeling ER Model Constructs
• Attribute property or characteristic of an
  entity type (often corresponds to a field in a
  table)
• Relationship Instance link between entities
  (corresponds to primary key-foreign key
  equivalencies in related tables)
• Relationship Type category of relationshiplink
  between entity types

17
Database Concepts
Sample E-R Diagram
18
Database Concepts

• Entity-Relationship Data Model (E-R Model) Basic
  Symbols

Entity
Primary Key
Multivalued Attribute
Relationship
Derived Attribute
Attribute
19
Database Concepts

• Entity-Relationship Data Model (E-R Model)
  Relationship Degree

Unary
Ternary
Binary
20
Database Concepts

• Entity-Relationship Data Model (E-R Model)
  Relationship Cardinality

Mandatory 1 Cardinality
Many (M) cardinality (1,2, .., many)
Optional 0 or 1 cardinality
Optional Zero-many cardinality (0, 1, .., many)
21
Database Concepts

• What Should an Entity Be?
• SHOULD BE
• An object that will have many instances in the
  database
• An object that will be composed of multiple
  attributes
• An object that we are trying to model
• SHOULD NOT BE
• A user of the database system
• An output of the database system (e.g. a report)

22
Database Concepts
System User
System output
Inappropriate entities
Appropriate entities
23
Database Concepts

• Attribute
• property or characteristic of an entity type
• Classifications of Attributes
• Simple versus Composite Attribute
• Single-Valued versus Multivalued Attribute
• Stored versus Derived Attributes
• Identifier Attributes

24
Database Concepts

• Basic Elements of a Database Table
• the largest of the elements
• in order of creation, it is the second in line
  after the database

25
Database Concepts

• Basic Elements of a Database Fields
• are pieces of data that help to identify a row of
  information in a table (a row is also called a
  record
• has two types primary and foreign

26
Database Concepts

• Basic Elements of a Database Fields
• Primary Key
• always has a value
• has a value that remains the same (never changes)
• unique value for each record in the table
• designated by using an underline
• In ER Model, identified with an underline

27
Database Concepts

• Basic Elements of a Database Fields
• Foreign Key
• an attribute that appears as a non-key attribute
  in one relation and as a primary key attribute
  (or part of a primary key) in another relation
• designated by using a dashed underline
• In ER Model, identified with an broken underline

28
Database Concepts
Multivalued An employee can have more than one
skill
Derived From date employed and current date
29
Database Concepts
An attribute broken into component parts
30
Database Concepts
The Composite Key is composed of two subparts
31
Database Concepts
Unary Relationships
32
Database Concepts
Combination of Unary and Binary Relationships
33
Database Concepts
Binary Relationships
34
Database Concepts

• Normalization
• process of applying a series of rules called
  normal forms that help organize data

35
Database Concepts
36
Database Concepts

• First Normal Form
• Rules
• Identify any field that contains multiple pieces
  of information (not for all cases)
• Create separate tables for related data
• Remove Calculated Data (data can be derived and
  need not be stored (not for all cases)

37
Database Concepts
Non Atomic
Repeating
38
Database Concepts
Remove Redundancies
v
Complied
39
Database Concepts
v
Complied
40
Database Concepts

• Second Normal Form
• Rule
• No non-key attributes depend on a portion of the
  primary key (fields must entirely be related to
  the primary key)

41
Database Concepts
Not entirely related to the primary key (ID)
42
Database Concepts
v
v
Complied
Complied
43
Database Concepts

• Third Normal Form
• If First 2 normalization steps are followed
  properly, this final step may not be necessary.
  But if changes have been made in the first 2
  steps, this could be a last check.
• Rules
• No attributes depend on other non-key attributes
  (fields of a table other than the key mutually
  independent)
• Break down the table if more fields exist that
  are not dependent on the key

44
Database Concepts
Can be derived and need not be stored
45
Database Concepts
v
Complied