Database DesignGoal of design is to generate a formal specification of the database conceptual schema. Show
Methodology:
E-R Model is not SQL-based. It's not tied to any particular logical implementation of a DBMS. It is a conceptual and semantic model, which attempts to capture meanings rather than an actual implementation. DO NOT THINK OR START WITH TABLES--YOU WILL BE MISGUIDED ON RELATIONSHIPS AND SOME ATTRIBUTES. The E-R Model:The enterprise is viewed as set of
Symbols used in E-R Diagram
We model the potential relationship. Not all entities from a set necessarily connect/relate to another entity in another set Entities and AttributesEntity: an object that is involved in the enterprise and that be distinguished from other objects. (not shown in the ER diagram--is an instance)
Entity Type or Set: set of similar objects or a category of entities; they are well defined (akin to an OO class).
Attribute: describes one aspect of an entity type; usually [and best as] a single value and indivisible (atomic)
Entity TypesAn entity type is named and is described by set of attributes
Domain: possible values of an attribute.
Key: subset of attributes that uniquely identifies an entity (candidate key) Entity Schema:The meta-information of entity type name, attributes (and associated domain), key constraints Entity Types tend to correspond to nouns; attributes are also nouns albeit descriptions of the parts of entities We may have null values for some entity attribute instances � no mapping to domain for those instances KeysSuperkey: an attribute or set of attributes that uniquely identifies an entity--there can be many of these Composite key: a key requiring more than one attribute Candidate key: a superkey such that no proper subset of its attributes is also a superkey (minimal superkey � has no unnecessary attributes) Primary key: the candidate key chosen to be used for identifying entities and accessing records. Unless otherwise noted "key" means "primary key" Alternate key: a candidate key not used for primary key Secondary key: attribute or set of attributes commonly used for accessing records, but not necessarily unique Foreign key: term used in relational databases (but not in the E-R model) for an attribute that is the primary key of another table and is used to establish a relationship with that table where it appears as an attribute also. So a foreign key value occurs in the table and again in the other table. This conflicts with the idea that a value is stored only once; however, the idea that a fact is stored once is not undermined. Graphical Representation in E-R diagramRectangle -- Entity Ellipses -- Attribute (underlined attributes are [part of] the primary key) Double ellipses -- multi-valued attribute Dashed ellipses-- derived attribute, e.g. age is derivable from birthdate and current date. [Drawing notes: keep all attributes above the entity. Lines have no arrows. Use straight lines only] RelationshipsRelationship: connects two or more entities into an association/relationship
Relationship Type: set of similar relationships
Relationship Types may also have attributes in the E-R model. When they are mapped to the relational model, the attributes become part of the relation. Represented by a diamond on E-R diagram. Relationship types can have descriptive attributes like entity sets Relationships tend to be verbs or verb phrases; attributes of relationships are again nouns, or prepositional phrases An attribute of a relationship type adds additional information to the relationship
[Drawing tips: relationship diamonds should connect off the left and right points; Dia can label those points with cardinality; use Manhattan connecting line (horizontal/vertical zigzag)] RolesThe role of a relationship type may additionally names the purpose of the entity in the relationship. Commonly the name of the entity serves asthe role name. e.g., "John" is value of Student role, "CS" value of Department role of MajorsIn relationship type The tuple (John, CS, 2000) describes a relationship Interesting situation: relationships can relate elements of same entity type
Solution: the role name of relationship type need not be same as name of entity type from which participants are drawn
It is optional to name role of each entity-relationship, but helpful in cases of
Roles are edges labeled with role names (omitted if role name = name of entity set). Most attributes have been omitted. Relationship TypeRelationship types are described by the set of roles (entities) and [optional] attributes
Think that entities are nouns; relationship types are often verbs
Here we have equate the role name (Student) the name of the entity type (Student) of the participant in the relationship. Degree of relationshipThe number of roles (entity participants) in the relationship Binary � links two entity sets; set of ordered pairs (most common) Ternary � links three entity sets; ordered triples (rare). If a relationship exists among the three entities, all three must be present. This is rare. NOTE: Ternary relationships are rare. N-ary � links n entity sets; ordered n-tuples (extremely rare). If a relationship exists among the entities, then all must be present. Cannot represesnt subsets. Note: ternary relationships may sometimes be replaced by two or more binary relationships (see book Figures 3.5 and 3.13). Semantic equivalence between ternary relationships and two binary ones are not necessarily the same. Why are non binary relationship rare?
Cardinality of RelationshipsCardinality is the number of entity instances to which another entity set can map under the relationship. This does not reflect a requirement that an entity has to participate in a relationship. Participation is another concept. One-to-one: X-Y is 1:1 when each entity in X is associated with at most one entity in Y, and each entity in Y is associated with at most one entity in X. One-to-many: X-Y is 1:M when each entity in X can be associated with many entities in Y, but each entity in Y is associated with at most one entity in X. Many-to-many: X:Y is M:M if each entity in X can be associated with many entities in Y, and each entity in Y is associated with many entities in X ("many" =>one or more and sometimes zero) From all of these choices, please use the first method!
Relationship Participation ConstraintsTotal participation
Key constraint
Partial participation
Existence Dependency and Weak EntitiesExistence dependency: Entity Y is existence dependent on entity X is each instance of Y must have a corresponding instance of X In that case, Y must have total participation in its relationship with X If Y does not have its own candidate key, Y is called a weak entity, and X is strong entity Weak entity may have a partial key, called a discriminator, that distinguishes instances of the weak entity that are related to the same strong entity Use double rectangle for weak entity, with double diamond for relationship connecting it to its associated strong entity Note: not all existence dependent entities are weak � the lack of a key is essential to definition Schema of a Relationship TypeContains the following features:
ER Diagram ExampleThis was produced with Dia. It is the same as the figure in the book using instructor's preferred style.
How many relationship types are there in EERD entity symbols
There are three types of entities commonly used in entity relationship diagrams. These shapes are independent from other entities, and are often called parent entities, since they will often have weak entities that depend on them.
What is entity instance in EA specific example of an entity is called an instance. Each instance becomes a record or a row in a table. For example: the student John Smith is a record in a table called students.
What is entityAn entity relationship diagram (ERD), also known as an entity relationship model, is a graphical representation that depicts relationships among people, objects, places, concepts or events within an information technology (IT) system.
What are the 3 main components of entity relationship modeling?ER diagrams or ERD's are composed of three main elements: entities, attributes, and relationships.
|