Data Modeling
Terms
undefined, object
copy deck
- Database
- logically related collection of data
- Schema
- a description of a particular collection of data, using the a given data model
- DBMS
- a software package designed to store and manage databases
- Four reasons to use a relational DBMS
-
Data independence and efficient access.
Reduced application development time.
Data integrity and security.
Uniform data administration.
Concurrent access, recovery from crashes - Advantages of the relational database approach
-
Controlled redundancy
Security / Access
Security subsystem - roles, privileges
Multiple User Interfaces
Match interface to the use/user
Data integrity
Enforcing integrity constraints
Backup and recovery - Disadvantages of the relational database approach
-
Start up costs
Hardware
Software
Personnel and Training
Complexity
Performance (in some situations)
Many benefits are lost if standards are not enforced - Least specific to most specific (concrete), the three data models
-
Conceptual
Logical
Physical - Three schema structure
-
External Level > External View
mapping | Logical Ind.
Conceptual Level > Conceptual Schema
mapping | Physical Ind.
Internal Level > Internal Schema - Physical Independence
- Protection from changes in physical structure of data
- Logical Independence
- Protection from changes in logical structure of data
- Three major database activities
-
Define - Specify data types, structure, and contraints
Construct - Storing data on a storage medium that is controlled by DBMS
Manipulate - Querying, updating, and reporting - Phases of DB development
-
Enterprise Modeling
Conceptual Data Modeling
Logical Database Design
Physical Database Design
Database Implementation
Maintenance - Entity
- Real-world object distinguishable from other objects; something you want to store data about
- Attribute
- property or characteristic of an entity type
- Identifier
- attribute or combination of attributes that uniquely identifies individual instances of an entity type
- Entity Type
- Collection of entities that have same attributes
- Entity Instance
- Single occurrence of an entity type
- Candidate Key
- An attribute or set of attributes that uniquely identifies each instance of an entity
- Degree of a relationship
- number of entity types that participate in it
- Maximum/Minimum cardinality
- the number of instances of one entity that can or must be associated with each instance of another entity
- Unary relationship
- Relationship of an entity with itself
- Ternary relationship
- A shared relationship between three entities
- Derived attribute
- Attribute value created by performing a function with other attributes as inputs
- Multivalued attribute
- An attribute that contains more than one value for its entry
- Composite attribute
- An attribute created by the combination of multiple attributes
- Composite key
- A unique identifier created by combining multiple attributes of an entity
- Completeness constraint
- Whether an instance of a superclass entity type must also be a member of at least one subclass entity type
- Disjoint contraint
- Whether an instance of a superclass entity type can be a member of two or more subclass entity types
- Domain
- Set of values that may be assigned to an attribute for each individual entity
- Strong entity
- Independent entity - Would exist without strong entity type
- Weak entity
- Dependent entity - Would not exist without strong entity type
- Superclass
- An entity type that has offspring entity types that inherit the parent’s attributes and relationships
- Subclass
- An offspring entity type of a superclass entity type. The subclass entity type inherits all of the attributes and relationships of the parent entity type
- 6 Phases of DB development
-
Enterprise Modeling
Conceptual Data Modeling
Logical Database Design
Physical Database Design
Database Implementation
Maintenance - Criteria for selecting good identifiers
-
Value never changes
Dataless (no underlying meaning)
Never null
Substitute new, simple keys for long, composite keys - Name and describe four degrees of relationships
-
Unary (or Recursive) Relationship: 1 entity
Binary Relationship: 2 entities
Ternary Relationship: 3 entities
n-ary: n entities - Generalization
- The process of defining a more general entity type from a set of more specialized entity types
- Specialization
- The process of defining one or more subclasses of the superclass
- Total specialization
-
Superclass must be an instance of a subclass
Double line notation - Partial specialization
-
Superclass need not have an instance of a subclass
Single line notation - Disjoint rule
-
An instance of a superclass cannot be an instance of two or more subclasses simultaneously
Place a “d†in the circle - Overlap rule
-
An instance of a superclass can be an instance of two or more subclasses simultaneously
Place an “o†in the circle - Relation
-
Instance : a table, with rows and columns. #Rows = cardinality, #fields = degree / arity.
Schema : specifies name of relation, plus name and type of each column - Integrity constraint
- Condition that must be true for any instance within the database
- Foreign key
- Set of fields in one relation that is used to `refer’ to a tuple in another relation. (Must correspond to primary key of the second relation.) Like a `logical pointer’.
- Referential integrity
- No dangling references are left when a change is made to an instance of an entity
- Discriminating attribute
- An attribute added to a superclass to distinguish each subclass from one another
- Selection
- Selects a subset of rows from relation
- Projection
- Deletes unwanted columns from relation
- Cross-product join
- Allows us to combine two relations
- Steps to mapping ERD to logical model
-
1. Convert Multivalued Attributes to Relations
2. Convert Regular Entities to Relations
3. Convert Weak Entities to Relations
4. Convert Superclasses/Subclasses to Relations
5. Map Binary Relationships
6. Map Unary Relationships
7. Map Ternary (n-ary) Relationships - Rules for mapping entities
-
Each entity becomes a relation.
Decompose all composite attributes to simple, component attributes - Rules for mapping attributes
-
The multivalued attribute becomes a separate relation.
The new relation has a composite key of the attribute name and the key of the entity it used to describe - Rules for mapping super/subtypes
-
One relation for each superclass and one for each subclass.
The primary key of the superclass also appears in the subclass relation.
Disjointness Constraints
If Overlap, add a discriminating attribute to the superclass to represent each subclass.
If Disjoint, add one discriminating attribute to the superclass to specify the applicable subclass - Rules for mapping unary relationships
-
Create a new relation. Similar to binary M:N
Rename to reflect relationship.
Make sure PK is unique - Rules for mapping binary relationships
-
One-to-Many(1:N): Primary key on the one side becomes a foreign key on the many side.
Many-to-Many(M:N): Create a new relation from the relationship. If the relationship does not have a unique identifier, then use the primary keys of the two entities as the primary key.
One-to-One(1:1): Primary key on the mandatory side becomes a foreign key on the optional side - Rules for mapping ternary relationships
-
Create a relation for the relationship.
If there isn’t already an identifier, create a composite key from the primary keys of the three (n) entities.
Ensure that the composite key is unique - 3NF
- requires that there are no non-trivial functional dependencies of non-key attributes on something other than a superset of a candidate key. A table is in 3NF if none of the non-primary key attributes is a fact about any other non-primary key attribute. In summary, all non-key attributes are mutually independent