Module 1 General Review

1
Module 1 General Review

• Dr. Stuart D. Galup, CCP

2
Chapter 1

• Database Systems
• Database Systems Design, Implementation, and
  Management, Seventh Edition, Rob and Coronel

3
Chapter 1 - Summary

• Data are raw facts. Information is the result of
  processing data to reveal its meaning.
• To implement and manage a database, use a DBMS.
• Database design defines the database structure.
• A well-designed database facilitates data
  management and generates accurate and valuable
  information.
• A poorly designed database can lead to bad
  decision making, and bad decision making can lead
  to the failure of an organization.

4
Chapter 1 - Summary

• Databases were preceded by file systems.
• Limitations of file system data management
• requires extensive programming
• system administration complex and difficult
• making changes to existing structures is
  difficult
• security features are likely to be inadequate
• independent files tend to contain redundant data
• DBMSs were developed to address file systems
  inherent weaknesses

5
Chapter 2

• Data Models
• Database Systems Design, Implementation, and
  Management, Seventh Edition, Rob and Coronel

6
Chapter 2 - Summary

• A data model is a (relatively) simple abstraction
  of a complex real-world data environment
• Basic data modeling components are
• Entities Something about which someone wants to
  store data.
• Attributes A characteristic of an entity or
  object.
• Relationships An association between entities.
• Constraints A restriction placed on data.

7
Chapter 2 - Summary

• Hierarchical model
• Depicts a set of one-to-many (1M) relationships
  between a parent and its children segments
• Network data model
• Uses sets to represent 1M relationships between
  record types
• Relational model
• Current database implementation standard
• ER model is a popular graphical tool for data
  modeling that complements the relational model

8
Chapter 2 - Summary

• Object is basic modeling structure of object
  oriented data model
• The relational model has adopted many
  object-oriented extensions to become the extended
  relational data model (ERDM)
• Data modeling requirements are a function of
  different data views (global vs. local) and level
  of data abstraction

9
Chapter 3

• The Relational Database Model
• Database Systems Design, Implementation, and
  Management, Seventh Edition, Rob and Coronel

10
Chapter 3 - Summary

• Tables are basic building blocks of a relational
  database
• Keys are central to the use of relational tables
• Keys define functional dependencies
• Superkey Any key that uniquely identifies each
  row.
• Candidate key A superkey without redundancies,
  that is, a minimal superkey.
• Primary key An attribute (or combination of
  attributes) that uniquely identifies any given
  row.
• Secondary key A key that is used strictly for
  data retrieval purposes.
• Foreign key An attribute whose values match the
  primary key values in a related table.

11
Chapter 3 - Summary

• Each table row must have a primary key which
  uniquely identifies all attributes
• Tables can be linked by common attributes. Thus,
  the primary key of one table can appear as the
  foreign key in another table to which it is
  linked
• The relational model supports relational algebra
  functions SELECT, PROJECT, JOIN, INTERSECT,
  UNION, DIFFERENCE, PRODUCT, and DIVIDE.
• Good design begins by identifying appropriate
  entities and attributes and the relationships
  among the entities. Those relationships (11,
  1M, and MN) can be represented using ERDs.

12
Chapter 4

• Entity Relationship (ER) Modeling
• Database Systems Design, Implementation, and
  Management, Seventh Edition, Rob and Coronel

13
Chapter 4 - Summary

• Entity relationship (ER) model
• Uses ERD to represent conceptual database as
  viewed by end user
• ERMs main components
• Entities
• Relationships
• Attributes
• Includes connectivity and cardinality notations

14
Chapter 4 - Summary

• Connectivities and cardinalities are based on
  business rules
• In ERM, MN relationship is valid at conceptual
  level
• ERDs may be based on many different ERMs
• Database designers are often forced to make
  design compromises

15
Chapter 5

• Normalization of Database Tables
• Database Systems Design, Implementation, and
  Management, Seventh Edition, Rob and Coronel

16
Chapter 5 - Summary

• Normalization is technique used to design tables
  in which data redundancies are minimized
• First three normal forms (1NF, 2NF, and 3NF) are
  most commonly encountered
• Table is in 1NF when all key attributes are
  defined and when all remaining attributes are
  dependent on primary key

17
Chapter 5 - Summary

• Table is in 2NF when it is in 1NF and contains no
  partial dependencies
• Table is in 3NF when it is in 2NF and contains no
  transitive dependencies
• Table that is not in 3NF may be split into new
  tables until all of the tables meet 3NF
  requirements
• Normalization is important partbut only partof
  design process

18
Chapter 5 - Summary

• Table in 3NF may contain multivalued dependencies
  that produce either numerous null values or
  redundant data
• It may be necessary to convert 3NF table to
  fourth normal form (4NF) by
• Splitting table to remove multivalued
  dependencies
• Tables are sometimes denormalized to yield less
  I/O which increases processing speed

19
Chapter 9

• Database Design
• Database Systems Design, Implementation, and
  Management, Seventh Edition, Rob and Coronel

20
Chapter 9 - Summary

• Information system is designed to facilitate
  transformation of data into information and to
  manage both data and information
• SDLC traces history (life cycle) of an
  application within the information system

21
Chapter 9 - Summary

• DBLC describes history of database within the
  information system
• Database design and implementation process moves
  through series of well-defined stages
• Conceptual portion of design may be subject to
  several variations, based on two design
  philosophies

22
Relationship Syntax
must be or may be
one or more or one and only one
relationship name
Each
entity 1
entity 2
Object entity
Subject entity
Name
Optionality
Degree
23
Attributes

• Bold attributes are required.
• Non-bold attributes are not required.
• Mark attributes as PK, UK, or FK as required.

24
Relationship Participation
25
Relationship lines

• A solid line represents a strong relationship.
  The PK of the Child entity contain the PK of the
  Parent entity.
• A dashed line represents a weak relationship.
  The PK of the Child entity does not contain the
  PK of the Parent entity.

26
Database Design Steps
27
Database Design Steps

• 1. External design Interviews
• 2. External design Gather sources (Forms, etc.)
• 3. Conceptual design Raw data list
• 4. Conceptual design
• Approach 1 Normalize (0nf, 1nf, 2nf, 3nf) and
  draw ERD
• Approach 2 Draw ERD and normalize (0nf, 1nf,
  2nf, 3nf)
• 5. Conceptual design Write Relationship
  sentences
• 6. Logical design Instance charts
• 7. Logical design Write DDL
• 8. Logical design Further DB design - DDL

28
Database Design Steps

• Questions?

29
Examination 1 - practice

• Questions
• Answers