Database and Design- Chapter 3

46 terms by csaaved4 Plus

Create a new folder

Advertisement Upgrade to remove ads

TPS

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

DSS

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

ERD

Entites: named as singular nouns
Attributes: names as singular nouns
Relationships: named as verbs or verb phrases

Entity

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

Attribute

- 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)

Identifiers

- 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

relationship

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)

Cardinality

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)

Modality

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
(child)

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

Supertype

a generic entity that has a relatioship with one or more subtypes

subtype

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

Please allow access to your computer’s microphone to use Voice Recording.

Having trouble? Click here for help.

We can’t access your microphone!

Click the icon above to update your browser permissions above and try again

Example:

Reload the page to try again!

Reload

Press Cmd-0 to reset your zoom

Press Ctrl-0 to reset your zoom

It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

Please upgrade Flash or install Chrome
to use Voice Recording.

For more help, see our troubleshooting page.

Your microphone is muted

For help fixing this issue, see this FAQ.

Star this term

You can study starred terms together

NEW! Voice Recording

Create Set