Data Models and Relationships

Data Models

• Data models offer a relatively simple and graphical representation of real-world data structures.
• Database design tools make useful drawings and automate a lot of the design.

Importance of Data Models

• Saves costs, reduces development time, and decreases errors.
• Improves understanding of the business.
• Improves communication between role players on a project.

Data Model Building Blocks

• An entity is a real-world object that can be distinguished from other objects, like a specific person, place, thing, or event.
• Entities have attributes (characteristics), such as people.
• A relationship describes an association among entities.

Types of relationship

• One-to-many: one lecturer, many students (1:*)
• Many-to-many: Many students study many courses (:)
• One-to-one: One department has one HOD (1:1)

Contraints

• Constraint is a restriction placed on data and ensures data integrity.
• Student's DP percentage must be between 0 and 100.
• Each class must have one, and only one, teacher per subject.

Business Rules

• Business rules provide brief, precise, and unambiguous descriptions of policies, procedures, or principles within an organization.
• Business rules applied to any organization that stores and uses data to generate information.
• Needs to be written down and kept up to date.
• Sometimes are external to the organization
• Must be easy to understand and widely dessiminated.
• Describe characteristics of the data as viewed by the company
• Nouns are generally translated into entities (Customer, Invoice, Course, Classroom).
• Verbs are generally translated into relationships among entities (Purchase, pay, generate invoice, attend course).
• A customer may generate many invoices.
• An invoice can be generated by only one customer.

Sources to Discover Business Rules

• Company managers.
• Policy makers.
• Department managers.
• Written documentation like procedures, standards, and operations manuals.
• Direct interviews with end-users.

Evolution of Data Models

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

The relational Model

• Developed by EF Codd in 1970.
• Codd was a Mathematician and computer scientist.
• Codd was moved to New York in 1948 to work for IBM as a mathematical programmer.
• Codd worked for IBM in California until the 1980s.
• Codd received the Turing Award in 1981.
• In 1970, Codd published his paper called "A Relational Model of Data for Large Shared Data Banks," marking the beginning of relational databases.
• Initially, IBM hesitated to implement the relational model to maintain revenue from IMS/DB.
• Codd showed IBM customers the potential of its model, who then pressured IBM. IBM eventually included a System R subproject in its Future Systems project.
• Codd coined the term OLAP and wrote the twelve laws of online analytical processing.
• Conceptually simple.
• Implemented through a sophisticated Relational Database Management System (RDBMS).
• The relational model is implemented through RDBMS
• Important advantage of the RDBMS is its ability to hide the model's complexities from the user.
• A table (relations) is a matrix consisting of a series of row/column intersections.
• Relations are related to each other through sharing a common entity relationship
• The representation of the relational database's entities, attributes within those entities, and relationships between those entities is a relational diagram.

The Relational Model Key language

• Structured Query Language (SQL) allows the user to specify what must be done without specifying how it must be done
• Rise to dominance of the relational model was due in part to its powerful and flexible query language

Degrees of Data Abstraction

• Data abstraction is reduction of a defined portion of data for a simple presentation of that.
• Way of classifying data models, begins at high level of abstraction and proceed to an ever-increasing level of detail.
• American National Standards Institute (ANSI) Standards Planning and Requirements Committee (SPARC) defined a framework for data modeling based on degrees of data abstraction(1970s)

The External Model

• End users' view of the data environment.
• Uses business rules and has the highest level of abstraction.

Advantages of the External Model

• Easy to identify specific data required to support each business unit's operations.
• Facilitates designers job by providing feedback about the model's adequacy.
• Helps to ensure security constraints in the database design.
• Simplifies application program development.

The Conceptual Model

• Represents a global view of the entire database.
• Represents data as viewed by the entire organization.
• The most used conceptual model is entity relationship (ER) model.

Characteristics of The Conceptual Model

• Provides a relatively easily understood macro-level view of the data environment.
• Does not depend on the DBMS software used to implement the model and independently from software and hardware.
• Changes in either hardware or DBMS software have no effect on the database design at the conceptual level.

The Internal Model

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

The Physical Model

• Operates at lowest level of abstraction.
• Hardware and software dependent.
• Requires that database designers have a detailed knowledge of the hardware and software used to implement database design.