Data Models: Design, Implementation, and Management

• The chapter focuses on data models, their design implementation, and management.
• The chapter will cover data model building blocks, business rules, the evolution of data models, and data abstraction.

Importance of Data Models

• Data models provide a relatively simple, typically graphical, representation of complex real-world data structures.
• Database design tools like Visio can create drawings and automate design processes.
• Data models save costs, reduce development time, and decrease errors.
• Data models improve understanding of the business and communication between project role players.

Data Model Building Blocks

• An entity is a real-world object distinguishable from others,like a person, place, thing, or event.
• Attributes are characteristics of entities, for example, the attributes of people
• Relationship describes an association among entities.

Examples of Entities

• 'DOG' entity examples include name, breed, age, color, and sex.
• Instances of the 'DOG' entity might include "Buddy" who is a terrier mix, age 12, white, and male, and likes Kibbles and Bits.
• Other examples include "Annie" and "Megan" with their respective attributes.
• Data models include one-to-many, many-to-many, and one-to-one relationships.
• One lecturer has many students (1:*).
• Many students study many courses (:).
• One department has one HOD (1:1).
• A database is a container.
• Constraints are restrictions placed on data to ensure data integrity.
• A student's DP percentage must be between 0 and 100, or each class must have one teacher per subject.

Business Rules

• Business rules are brief, precise, and unambiguous descriptions of policies, procedures, or principles within an organization.
• Business rules apply to any organization that stores and uses data to generate information.
• Business rules must be written and kept up to date.
• Business rules are sometimes external to the organization.
• Business rules must be easy to understand and widely disseminated while describing the company's data characteristics.

Discovering Business Rules

• Business rules sources include company managers, policy makers, department managers, and written documentation.
• Business rules sources include procedures, standards, operations manuals, and direct interviews with end-users
• Nouns generally translate into entities such as Customer, Invoice, Course, and Classroom
• Verbs translate into relationships among entities such as Purchase, pay, generate invoice, and attend course.
• Relationships are bi-directional.
• Example: A customer may generate many invoices but an invoice is generated by only one customer.

Example Exercise

• The relationship between a university and its departments is that Fort Hare has many departments.
• The relationship between Fort Hare departments and courses is that Fort Hare has different departments that teach specific courses.
• The relationship between classrooms and lectures is that lectures are taught in a classroom specifically allocated to that course.
• The relationship between classes and students is that students attend classes taught by a lecturer.
• One lecturer teaches many students; students are taught by one lecturer
• One department offers many courses; a course is offered by one department
• A classroom is used for many lectures; a lecture takes place in one classroom.

Evolution of Data Models

• File System models emerged first.
• Hierarchical and network databases emerged sequentially.
• The Relational Model was created next.
• Then the Entity Relationship model.
• Followed by the Object-Oriented (OO) model.

The Relational Model

• The Relational Model was developed by EF Codd in 1970.
• EF Codd was a mathematician and computer scientist.
• EF Codd was a Pilot in the Royal Air Force during WWII.
• EF Codd moved to New York in 1948 to work for IBM as a mathematical programmer and in California until the 1980s.
• EF Codd invented the relational model for database management, and the theoretical basis for relational databases.
• Codd published "A Relational Model of Data for Large Shared Data Banks" in 1970.
• Originally, IBM refused to implement, but later included it in their Future Systems project as a System R subproject.
• A language called SEQUEL was developed when the Relational Model became fashionable in the 1980s, Codd fought against its misuse by database vendors.
• The language was launched as called SQL (Structured Query Language).
• Edgar Codd coined the term OLAP and wrote the twelve laws of online analytical processing
• The concept was thought ingenious but impractical in 1970 due to computers lacking power while it being conceptually simple.
• Microcomputers run sophisticated relational database software today.
• The relational model is implemented through an RDBMS.
• The most important advantage is the ability to hide the complexities of the relational model from the user.
• The relational model is implemented through sophisticated Relational Database Management Systems (RDBMS)
• Tables are relations that consist of a matrix made of rows and column intersections
• Tables are related by sharing a common entity characteristic.
• Example tables are the AGENT table with attributes such as AGENT_CODE, AGENT_LNAME, and AGENT_FNAME.
• Another table is the CUSTOMER table with attributes like CUS_CODE, CUS_LNAME, and CUS_FNAME.
• Tables link through AGENT_CODE.
• A table is purely a logical structure, and how data is physically stored is not a concern for the user or designer.
• Becoming the source of a real database Revolution.
• Relational diagrams represent the entities, attributes within entities, and relationships.
• The rise to dominance of the relational model was due to a powerful and flexible query language (SQL).
• SQL lets the user specify what must be done without specifying how to achieve it.
• SQL-based relational database applications involve a user interface, a set of tables stored in the database, and an SQL engine.
• Information is represented using entity-relationship models.

Degrees of Data Abstraction

• Data abstraction reduces a portion of data for simple presentation, removing unnecessary features.
• Data abstraction is a way of classifying data models.
• Processes start at a high level of abstraction and proceed to an ever-increasing level of detail.
• Designing a usable database follows the same basic process.
• The American National Standards Institute (ANSI) Standards Planning and Requirements Committee (SPARC) defined a framework for data modeling based on data abstraction degrees (1970s).
• ER has a high degree of abstraction.
• Relational and Object-Orientated models have medium degrees of abstraction
• Network and Hierarchical models have a low degree of abstraction.

The External Model

• Each end-user has their data enviroment view.
• The External Model applies business rules.
• The External Model has the highest level of abstraction.
• Provides the end user's view.
• Easy to identify specific data to support business operations while helping to ensure security constraints and simplifies application program development.

Conceptual Model

• Conceptual Model represents a global view of the entire database and is viewed by the entire organization.
• Describes main data objects while avoiding details.
• The most widely used conceptual model is the entity-relationship (ER) model.
• Provides a macro-level view of the data environment.
• Independent of both software and hardware.
• Not dependent on software or hardware implementation choices.
• Changes in hardware or DBMS software do not affect the database design.

Internal Model

• The Internal Model represents the database as "seen" by the DBMS and maps the conceptual model to the DBMS.
• The internal schema depicts a specific representation of an internal model.

Physical Model

• The Physical Model operates at the lowest level of abstraction describing how data is saved on storage media (disks/tapes).
• This Model's software and hardware are dependent.
• Requires detailed knowledge of the hardware and software to implement the database design.
• Global view of data vs. end-user views.
• There is an element of hardware involved.
• Specific database model.Hardware
• It uses Storage and access methods
• Is independent of neither hardware nor software

Case study

• A hospital patient receives medications ordered by a doctor while often receiving several medications per day.
• The patient order has a 1:* relationship
• Each order includes several medications.
• The order medication has a 1:*
• Identify the business rules for patients, orders, and medication, plus create an ERD to capture these business rules.