4.1 Entities, relationships, and attributes.
The Entity-Relationship Model
An entity-relationship model is a high-level representation of data requirements, ignoring implementation details.
An entity-relationship model guides implementation in a particular database system, such as MySQL.
An entity-relationship model includes three kinds of objects:
Entity
An entity is a thing, person, place, product, concept, or activity. In SQL, these become tables. Database tracks information about these. Relationship
A relationship is a statement about two entities or things. In SQL, these become foreign keys Attribute
An attribute is a descriptive property of an entity. In SQL, these become columns. Entity-relationship diagram and glossary
An entity-relationship diagram, commonly called an ER diagram, is a schematic picture of entities, relationships, and attributes.
Entities are drawn as rectangles. Relationships are drawn as lines connecting rectangles. Attributes appear as additional text within an entity rectangle, under the entity name.
Glossary
A glossary, also known as a data dictionary or repository, documents additional detail in text format.
A glossary includes names, synonyms, and descriptions of entities, relationships, and attributes. For simple databases with few users, a database designer may record the glossary with a text editor. For more complex databases, the designer may use a database or software tool specifically designed for glossaries. The ER diagram and glossary are complementary and, together, completely describe an entity-relationship model.
Types and Instances
In entity-relationship modeling, a type is a set:
An entity type is a set of things. Ex: All employees in a company. A relationship type is a set of related things. Ex: Employee-Manages-Department is a set of (employee, department) pairs, where the employee manages the department. An attribute type is a set of values. Ex: All employee salaries. Entity, relationship, and attribute types usually become tables, foreign keys, and columns, respectively.
An instance is an element of a set:
An entity instance is an individual thing. Ex: The employee Sam Snead. A relationship instance is a statement about entity instances. Ex: "Maria Rodriguez manages Sales." An attribute instance is an individual value. Ex: The salary $35,000.
Database Design
Complex databases are developed in three phases:
Analysis develops an entity-relationship model, capturing data requirements while ignoring implementation details. Analysis is particularly important for complex databases with many users when documenting requirements is challenging. Analysis documents database requirements, without regard to implementation details for any database system. For small databases with just a few tables and users, analysis is less important and often omitted. Logical design converts the entity-relationship model into tables, columns, and keys for a particular database system. Physical design adds indexes and specifies how tables are organized on storage media. Physical design is dependent on specific index and table structures, which vary greatly across relational databases.
4.2 Discovery
Entities, relationships, and attributes are discovered in interviews with database users and managers.
Users and managers are usually familiar with data requirements from an old database, or perhaps a manual process with paper records
Names
Entity Names
Entity names are a singular noun. Ex: Employee rather than Employees.
The best names are commonly used and easily understood by database users. Relationships names have the form Entity-Verb-Entity, such as Division-Contains-Department.
When the related entities are obvious, in ER diagrams or informal conversation, Verb is sufficient and entity names can be omitted. The verb should be active rather than passive. Ex: Manages rather than IsManagedBy. Occasionally, the same verb relates different entity pairs. Ex: Order-Contains-LineItem and Division-Contains-Department. Attribute Names
Attribute names have the form EntityQualifierType, such as EmployeeFirstName:
Entity is the name of the entity that the attribute describes. When the entity is obvious, in ER diagrams or informal conversation, QualifierType is sufficient and the entity name can be omitted. Qualifier describes the meaning of the attribute. Ex: First, Last, and Alternate. Sometimes a qualifier is unnecessary and can be omitted. Ex: StudentNumber. Type is chosen from a list of standard attribute types such as Name, Number, and Count. Attribute types are not identical to SQL data types. Ex: "Amount" might be an attribute type representing monetary values, implemented as the MONEY data type in SQL. "Count" might be an attribute type representing quantity, implemented as NUMBER in SQL.
Synonyms and Descriptions
Often, entity, relationship, and attribute names have synonyms.
Ex: Representative may be a synonym for SalesAgent. Synonyms are common in informal communications. To avoid confusion, one official name is selected for each entity, relationship, and attribute. Other names are documented in the glossary as synonyms.
Database Design
As discovery proceeds, the designer draws an ER diagram, determines standard attributed types, and documents names, synonyms, and descriptions in the glossary.
Although the step numbers suggest a sequence, database designers commonly move back and forth between steps.
As names, synonyms, and descriptions are documented, additional entities, relationships, and attributes are discovered. The ER diagram and glossary are usually developed in parallel.
4.3 Cardinality
Relationship maximum
Cardinality
1. What is Cardinality in ER Modeling?
Cardinality in ER modeling is a way to describe how many instances of one entity (like a "Customer") can be associated with instances of another entity (like an "Order"). It helps to specify the rules of association between two entities — telling us how tightly or loosely they are connected. Cardinality tells us how many instances of one entity relate to instances of another. Describes the maxima and minima of relationships and attributes. Minima and maxima for both attributes and relationships can appear in ER diagrams. Maxima and minima usually depend on business rules. In practice, cardinality is determined in parallel with discovery, and additional discovery takes place after cardinality is determined. Maximum Cardinality (Maxima)
Maximum cardinality describes the greatest number of instances of one entity that can be related to a single instance of another entity. In simpler terms, it answers the question: "How many of this entity can be associated with one of that entity?" Example of Maximum Cardinality:
Suppose we have two entities: Customer and Order. A single Customer can place many Orders. So, for each Customer, there could be many Orders. We represent this maximum with an "M" (many). However, each Order is usually placed by just one Customer. So, for each Order, there’s a maximum of one Customer associated. This maximum is represented by "1". Therefore, the maximum cardinality between Customer and Order is: Customer to Order: M (many) Order to Customer: 1 (one) Maximum cardinality (1 or M) defines the upper limit of how many connections there can be. Symbols in ER Diagrams:
In ER diagrams, maximum cardinality is shown as 1 (for one) or M (for many). Minimum Cardinality (Minima)
Minimum cardinality specifies whether an entity must have a relationship with the other entity, or if the relationship is optional. In other words, it answers the question: "Is this relationship required, or can it be zero?" If the minimum cardinality is 0, it means that an instance of an entity can exist without being associated with an instance of the other entity. If the minimum cardinality is 1, it means that an instance must have a relationship with at least one instance of the other entity. Minimum cardinality (0 or 1) defines if the relationship is required or optional. Example of Minimum Cardinality:
Continuing with Customer and Order: A Customer does not have to place an Order, so the minimum cardinality from Customer to Order could be 0 (optional). However, each Order must be placed by a Customer. So, the minimum cardinality from Order to Customer could be 1 (required). Minimum cardinality isn’t always displayed explicitly in simple ER diagrams but is essential to understand when defining relationships.
Types of Relationships Based on Cardinality
There are three common relationship types based on maximum cardinality: One-to-One (1:1): Each instance of one entity is related to only one instance of the other. Example: Each Person has one Social Security Number, and each Social Security Number is assigned to one Person. One-to-Many (1): One instance of an entity can be related to many instances of the other, but each instance of the other entity is related to only one instance of the first. Example: Each Customer can place many Orders, but each Order is placed by only one Customer. Many-to-Many (M): Many instances of one entity can be related to many instances of the other. Example: Students enroll in many Courses, and each Course has many Students enrolled. Attribute Maximum and Minimum
Attribute maximum is the greatest number of attribute values that can describe each entity instance.
Attribute maximum is usually specified as one (singular) or many (plural). Attribute minimum is the least number of attribute values that can describe each entity instance.
Attribute minimum is usually specified as zero (optional) or one (required).
Unique Attributes
What is a Unique Attribute?
A unique attribute is a type of attribute where each value uniquely identifies only one entity instance. 
Unit 4: Database Design
