Data Models: Entities, Attributes, and Relationships

• Data models are relatively simple representations, typically graphical, of complex real-world data structures.
• Database design tools can be used to make useful drawings and automate the design process. Visio is an example of such a tool.
• Data models save costs, reduce development time, and decrease errors.
• Data models improve understanding of the business and enhance communication between role players on a project.

Data Model Basic Building Blocks

• An entity is a real-world object distinguishable from others.
• A specific person, place, thing, or event are all examples of entities.
• Attributes are characteristics of entities. For example, people.
• A relationship describes an association among entities.

Relationships

• One-to-many (1:*) relationships involve one lecturer and many students.
• Many-to-many (:) relationships involve many students studying many courses.
• One-to-one (1:1) relationships involve one department having one Head of Department (HOD).
• A constraint is a restriction placed on the data and ensures data integrity.
• A 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 offer 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 down and kept up to date.
• Business rules are sometimes external to the organization
• Business rules must be easy to understand and widely disseminated.
• Business rules describe data characteristics as viewed by the company.
• Sources of business rules include company managers, policy makers, department managers, and written documentation.
• Procedures, standards, and operations manuals within an organization may also provide business rules.
• Business rules may be discovered through direct interviews with end users.
• Nouns typically translate into entities while verbs translate into relationships among entities.
• Example entities: Customer, Invoice, Course, Classroom
• Example Relationships: Purchase, pay, generate invoice, attend course

Discovering Business Rules

• Relationships are bi-directional, for example, a customer may generate many invoices, and an invoice is generated by only one customer.
• A relationship is an association among entities: one-to-many (1:), many-to-many (:*), or one-to-one (1:1).

Evolution of Data Models

• Hierarchical data models are one type.
• Network data models are another type.
• Relational data models exist.
• Entity-relationship data models are also a type.
• Object-oriented (OO) data models also exist.

The Relational Model

• E.F. Codd developed the relational model in 1970.
• Codd, born in 1923, was a mathematician, computer scientist, and Royal Air Force pilot in World War II.
• He moved to New York to work for IBM as a mathematical programmer in 1948.
• He worked for IBM in California until the 1980s.
• EF Codd invented the relational model for database management, the basis for relational databases.
• Codd received the Turing Award in 1981.
• Codd published his paper "A Relational Model of Data for Large Shared Data Banks" in 1970.
• Initially, IBM refused to implement the relational model to preserve revenue from IMS/DB.
• Codd demonstrated the potential of the relational model to IBM customers, who then pressured IBM to include a System R subproject in its Future Systems project.
• A language called SEQUEL was developed, later called SQL.
• Codd fought to prevent misuse of the term "relational" by database vendors in the early 1980s.
• Edgar Codd coined the OLAP term and wrote the twelve laws of online analytical processing.
• "A relational model for Large Shared Databanks" was deemed impractical but ingenious in 1970.
• There was not enough computer power to implement the relational model, but it was conceptually simple.
• Microcomputers can now run sophisticated relational database software.
• The relational model is implemented through a sophisticated Relational Database Management System (RDBMS).
• The RDBMS can hide the complexities of the relational model from the user.
• A table (relation) is a matrix of row/column intersections related through a shared entity characteristic.
• A Relational Diagram shows the relational database's entities, attributes, and relationships
• The relational model rose to prominence with the rise of its query language
• Structured Query Language (SQL) enable users to specify results without specifying how steps
• SQL-based relational database application involves the user interface, a set of tables stored in a database, and a SQL engine
• Information in the model is represented using entity relationship models
• Tables have a purely logic structure to hold data
• How the data are stored is not relevant to the user or designer
• You can think of tables as analogous to spreadsheets with rows and columns

Degrees of Data Abstraction

• Data abstraction is the reduction of a portion of data for a simpler presentation of content by removing unneeded features.

• Data abstraction helps classify different data models.

• Many processes begin at a high level of abstraction and become more detailed.

• Designing a usable database follows this process too.

• The American National Standards Institute (ANSI) Standards Planning and Requirements Committee (SPARC) defined a framework for data modeling based on degrees of data abstraction in the 1970s.

• The external model is the end users' view of the data environment, uses business rules, and operates at the highest level of abstraction.

• Advantages of the external model include easy identification of data needed for business operations, facilitating design and implementation, and simplifying application program development.

• The conceptual model represents a global view of data by the entire organization, and is independent of software and hardware.

• It forms the basis for identifying main data objects and high-level descriptions, the entity relationship (ER) model is the most common here.

• The conceptual model provides a easily understood high-level view of the data environment

• It is not dependent on DBMS or hardware, changes to either will not affect the conceptual level.

• The internal model maps the conceptual model to the DBMS, depicts a specific representation

• The physical model works at the lowest level describing how data is saved and storage details.

• Operating at the lowest level of data describes how dat is saved.

• A hospital patient receives medications that have been ordered by a particular doctor.

• Because the patient receives several medications per day, there is typically a one to many relationship between PATIENT and ORDER.

• Each order includes several medications, creating a one to many (1:) relationship between ORDER AND MEDICATION.

• Business rules for PATIENT, ORDER and MEDICATION must be identified.

• The related ERD capturing these business rules much also be created.