46 terms

Database and Design- Chapter 3

Transaction Processing Systems: designed tu support the processing of everday operational business transactions (retrieving, adding, modifying, and deleting data)
e.g.- managing inventories or parts on assembly line, recording airline and hotel reservations, etc
Decision Support Systems: specifically designed to aid managers in decisions making tasks.
contain data that has been accumulated over time to aid in trend analysis and forecasting
*data warehouses are subsets of data warehouses designed to support a part of an organization
Business Rules
Can be used to define or constrain some aspect of a business's structure or processes
e.g - organizational policies, calculations and formulas, rules and regulations
Data modeling
is a design technique ofr capturing reality
Conceptual Data Modeling
goal is arrive at an understanding of the principal data sources and data lements of interest to the business of organization, and the relationships between the data sources, in order to satisfy requirements for information (ERD)
Logical Data Modeling
goals is to convert the conceptual model into a form that can be utilized to create an IS (relational database) (RELATIONAL SCHEMA)
Physical Database Modeling
goal is to specify all indentifying and operational characteristics of the data that will be recorded in the information system
Entites: named as singular nouns
Attributes: names as singular nouns
Relationships: named as verbs or verb phrases
something of interest in the environment (person, place, object, event, concept) that is described by attributes and that will have numerous instances we wish to track.
represented in the E-R diagram by a rectangle
- a discrete data element
- describes an entity (characteristic)
- meaningful (for the system being modeled)
- value may be required or optional
Simple Attribute
at the atomic, most basic level
Composite Attribute
a related group of attributes (address)
Single Valued Attribue
only one value per entity instance (eg. last name, Date of birth)
Multivalued Attribute
multiple values per entity instance are possible (degree, club, skill)
Derived Attribue
calculated, but not stored
Identifier Attribute
an attribute that uniquely identifies each entity (social security number)
Candidate Key
is any attribute that woul qualify as an identifier (SSN, employee iD)
- must be unique
- should not change in value
- guaranteed to have a valid, non-null value
Attributes on ERDs
Identifier Attribute - underlined
Simple Attribute - nothing
Composite Attribute - (parenthesis)
Multivalued Attribute - {curly braces}
Derived Attribute - [brackets]
Derived Attributes
Derived attributes represent values that are calculated when needed and not stored (e.g. values generated by a query for use on a report)
These values are only optionally stored on ERD
a relationship is an association between entities
Degree of a relationship
the degree of a relationship describes the number of entities involved in the relationship
- specified at unary, binary, ternary, or higher (n-ary)
the cardinality of a relationship describes the numer of instances of one entity that may be asssociated with on instance of another entity
*specified as either one or many (crows feet or stick)
indicated whether participation in the relationship is required or not
* Specified as either mandatory or optional
Unary Relationship
In a unary relatioship, only one entity is involved.
Binary Relationship
In a binary relatioship, two entities are involved.
Ternary relationship
In a ternary relationship, three entities are simultaneously involved.
Relationship Cardinality
1:1 Relationship
1:M Relationship
M:M Relationship
Associative Entities
In order to facilitate the design of a relational database, a M:M relationship may also be converted into a two 1:M relationships through the use of an associative relationship.
associative entities are simply an optional alternative notation
many to many relationships may be drawn using either M:M
Absolute Rule
this rule states that the only cardinality relationship that supports intersecting data is the many to many relationship
Two cases when you MUST use associative entities (case 1)
Case1: If it is necessary to assign an identifier to uniquely identify each occurence of the M:M relationship between the orginal entities, then an asociative entity must be created. (Remember that entities have identifiers and relationships do not)
Two cases when you MUST use associative entities (case 2)
Case2: In a ternay relationship, if the optionalities at all three entities are not identical, then an associative entity must be created. Otherwise, the ERD cannot depict the interactions between the entities.
Strong Entity
exisits independently of other entities, has it own unique identifier (shown with a single underline), represented with regular rectangle symbol (employee)
Dependant (weak) entity
dependent on a strong entity, cannot exist on its own, does not have a unique identifier, only a partial identifier (shown with a double underline), represented with rectangle symbol with lines in corners
Identifying Relationship
Links strong entities to dependent (weak) entities. They are represented with diamond symbol with lines in the corners
Two processes to develop supertype/subtype
Generalization: the process of defining a more general entity type from a set of more specialized entity types.
This is a "bottom up approach"

Specialization: the process of defining one or more subtypes of a general entity based on distinguishing attributes or relatioships

- A "top-down" approach
a generic entity that has a relatioship with one or more subtypes
a subgrouping of a supertype entity that is meaningful to an organization (not just possible)
sharesall attributes of its supertype, but also has unique attributes of its own (and/or)
has relatioships with other entities distinct from those of other subtypes
Inheritance rule
an instance of a subtype is also an instance of a supertype
subtype entities inherit values of all attributes of the supertype
this makes it unnecessary to include the supertype's attributes redundantly with the subtypes
Rules restricting the use of superytpe/subtype
- when there are attributes that apply to some (but not all) instances of an entity type
- when the instances of a subtype pariticipate in a relatioship unique to that subtype
Completenes Constraint
addressses the question whether an instance of a supertype must also be a member of at least one subtype
Disjointness Constraint
addresses the question whether an instance of a supertype may simultaneously be a member of two (or more) subtypes
Completeness Constraint (2 rules)
Total specialization rule (double line notation): specifies that each entity instance of the supertype must be a member of some subtype in the relationship (e.g. all students are either undergraduate ro graduate students)
Partial Specialization Rule (single-line notation): specifies that an entity instance of the supertype is allowed to not belong to any subtype (ex: Faculty and staff are not the only possible types of EMPLOYEE)
Disjointness Constraint (2 rules)
Disjoint Rule (letter "d" notation): specifies that if an entity instance is a member of one subtyp, it cannot simultaneously be a member of any other subtype (e.g. all PERSONS are either MALE or FEMALE)

Overlap Rule (letter "o" notation): specifies that an entity instance can simulatneously be a member of two (or more) subtypes. (e.g. an ATHLETE can be both a RUNNER and a JUMPER)
Subtype discriminator
an attribute of the supertype whose values determine the target subtype or subtypes (used to direct into which of the subtypes, is any, a new instance of the supertype should be inserted)
- Disjoint ("d")- a simple attribute with alternative values to indicate the possible subtypes
- Overlap ("o") - a composite attribute whose subparts pertain to different subtypes; each subpart contains a boolean value (yes or no) to indicate whether or not the instance belongs to the associated subtype
ERDs Purpose
- serve as a communication rule for you and your client to use to validate your understanding of the business activities pertinent to the future system.
-As an input document into the relational database design phase where the ERD will be mapped into another type of diagram known as Relational Schema