Understanding Data Models

Importance of Data Models

• Data models offer simple representations of real-world data structures, often in a graphical format
• Database design tools like Visio aid in creating drawings and automating the design process
• Data models reduce costs, development time and errors
• They enhance understanding of the business
• They improve communication among project role players

Data Model Basic Building Blocks

• An entity is an object in the real world and is distinguishable from others
  • For example, a specific person, place, thing, or event
• Entities have attributes that describe characteristics
  • For example, people
• A relationship describes the association between entities

Relationships

• Relationships represent associations among entities
  • One to many (1:*) indicates one lecturer and many students
  • Many to many (*: *) shows that many students study many courses
  • One to one (1:1) indicates one department has one HOD
• A constraint is a restriction that is placed on data
• Constraints ensure data integrity
  • For example, a student's DP percentage should fall between 0 and 100
  • Each class should a teacher assigned per subject

Business Rules

• Business rules are precise, unambiguous descriptions of an organization's policies, procedures, or principles
• Business rules apply to data storage and the generation of information

Business Rules - Continued

• Business rules should be written down and regularly updated
• They need to be easy to understand and widely disseminated
• Business rules should describe the characteristics of the data

Discovering Business Rules: Sources

• Company Managers
• Policy Makers
• Department Managers
• Written Documentation:
  • Procedures
  • Standards
  • Operations Manuals
• Direct Interviews With End Users

Discovering Business Rules: Nouns and Verbs Relationships

• Nouns typically translate into entities like customer, invoice, course, and classroom
• Verbs usually translate into relationships among entities like purchase, pay, generate invoice, and attend course

Discovering Business Rules: Relationships

• Relationships are bi-directional
  • A customer may generate many invoices
  • An invoice is generated by one customer
• A relationship involves the association among entities
  • One to many (1:*)
  • Many to many (*: *)
  • One to one (1:1)

Evolution of Data Models

• Hierarchical
• Network
• Relational
• Entity relationship
• Object oriented (OO)

The Relational Model

• E.F. Codd developed the relational model in 1970
• Codd was a mathematician, a computer scientist, and a pilot during World War II
• In 1948 he moved to New York to work for IBM as a mathematical programmer and then worked for IBM in California until the 1980s
• Codd invented the relational model for database management as the theoretical basis for relational databases
• Codd received the Turing Award in 1981

Codd: Career Highlights

• Codd published “A Relational Model of Data for Large Shared Data Banks” in 1970, marking the creation of relational databases
• Initially, IBM didn’t implement the model to protect revenues from IMS/DB
• Codd demonstrated to IBM customers the model potential, who pressured IBM to include a System R subproject in its Future Systems project
• A language called SEQUEL was developed, but not by Codd, and was named SQL when it was launched
• Codd fought to prevent database vendors from misusing the term relating to older technology in the early 1980s as the term was becoming fashionable
• Codd coined the term OLAP which expanded to the twelve laws of online analytical processing

The Relational Model - Continued

• “A relational model for Large Shared Databanks” was considered impractical in 1970, but ingenious
• The model is conceptually simple
• Relational models lacked the computing power for use
• Today, microcomputers can run relational database system software

Relational Database Management Systems (RDBMS)

• The relational model implements the Relational Database Management System (RDBMS)
• The advantageous aspect of the RDBMS, is to hide the complexities of the relational model

Tables and Relations

• Tables, also known as relations, consist of a series of row/column intersections in a matrix consisting
• Tables are related by sharing a common entity characteristic

Relational Models as Logical Structures

• Tables of rows and columns provide a framework for understanding if individuals understand spreadsheets
• How the data is physically stored is not relevant to the designer or user
• This logical abstraction became the source of change for revolutionizing databases

Relational Diagrams

• A relational diagram represents entities in a relational database
• Attributes are held within those entities
• The relationships are described in their entities

Benefits of the Relational Model

• The rise to dominance of the relational model emerged due to its powerful and flexible query language
• Structured Query language, also known as SQL, provides the user what must be done, without specifying how
• SQL Based Relational database applications:
  • User interface
  • A set of tables stored in the database
  • SQL engine
• Information is represented via entity-relationship models

Degrees of Abstraction

• Data abstraction simplifies a data portion for a simple presentation
• Abstraction reduces unnecessary features
• The way to classify data models
• Many processes begin with high level of abstraction and proceed to ever-increasing level of detail
• Design of usable database follows the same basic process

American National Standards Institute (ANSI)

• The Standards Planning and Requirements Committee (SPARC) of ANSI is an institute
• SPARC defined a framework for data modeling on degrees of abstraction in the 1970s

External Models

• Highest level of abstraction
• End users view the data environment
• Business rules implemented

External Models Advantages

• Easy data identification to support each business unit's operations
• Feedback to designer to indicate model adequacy
• Creation to ensure security constraints
• Simplification Program in Development

Conceptual Models

• Conceptual models represent global view of entire database
  • View of data from the entire organization
• High-level description of main data objects
  • Basis for identification, while avoiding details
• The entity relationship is the most commonly used model

Conceptual Model Features

• Provides easy macro level view of data environment - Software and hardware independent
• Not dependent on DBMS software used to implement the model
• Not dependent on the hardware used in the implementation of the model
• No effect on the database design at the conceptual level occurs from hardware or DBMS software changes

The Internal Model

• Represents the database according to the DBMS
• Maps conceptual models within
• Internal schema depicts an existing internal model

Physical Models

• The physical model operates at the lowest abstraction level
• Describes data storage on storage media - Disks and Tapes
• Hardware and software dependent
• Detailed knowledge on database designers related to hardware and software implementations