Top of Form
Bottom of Form
Goal of design is to generate a formal specification of the database schema Methodology:
1. Use E-R model to get a high-level graphical view of essential components of enterprise and how they are related 2. Then convert E-R diagram to SQL DDL, or whatever database model you are using E-R Model is not SQL based. It's not limited to any particular DBMS. It is a conceptual and semantic model – captures meanings rather than an actual implementation The E-R Model: The enterprise is viewed as set of
* Relationships among entities
Symbols used in E-R Diagram
* Entity – rectangle
* Attribute – oval
* Relationship – diamond
* Link - line
Entities and Attributes
Entity: an object that is involved in the enterprise and that be distinguished from other objects. (not shown in the ER diagram--is an instance) * Can be person, place, event, object, concept in the real world * Can be physical object or abstraction
* Ex: "John", "CSE305"
Entity Type: set of similar objects or a category of entities; they are well defined * A rectangle represents an entity set
* Ex: students, courses
* We often just say "entity" and mean "entity type"
Attribute: describes one aspect of an entity type; usually [and best when] single valued and indivisible (atomic) * Represented by oval on E-R diagram
* Ex: name, maximum enrollment
* May be multi-valued – use double oval on E-R diagram
* May be composite – attribute has further structure; also use oval for composite attribute, with ovals for components connected to it by lines * May be derived – a virtual attribute, one that is computable from existing data in the database, use dashed oval. This helps reduce redundancy
An entity type is named and is described by set of attributes * Student: Id, Name, Address, Hobbies
Domain: possible values of an attribute.
* Note that the value for an attribute can be a set or list of values, sometimes called "multi-valued" attributes * This is in contrast to the pure relational model which requires atomic values * E.g., (111111, John, 123 Main St, (stamps, coins))
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 May have null values for some entity attribute instances – no mapping to domain for those instances
Superkey: 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; the idea that a fact is stored once is not undermined.
Graphical Representation in E-R diagram
Rectangle -- Entity
Ellipses -- Attribute (underlined attributes are [part of] the primary key) Double ellipses -- multi-valued attribute
Dashed ellipses-- derived attribute, e.g....