Test 1 Information Management

Exact minimum and maximum cardinality in relationships

*In some cases, the exact minimum and/or maximum cardinality in relationships is known in advance *Exact min/max cardinalities can be depicted in ER diagrams

maximum cardinality

*One (represented by a straight bar: |) *Many (represented by a crow's foot symbol)

minimum cardinality (participation)

*Optional (represented by a circular symbol (O) *Mandatory (represented by a straight bar: |)

Identifying relationship is either 1:M or 1:1 relationship -- Weak entity

1:M identifying relationship -- a weak entity must have a partial key attribute 1:1 identifying relationship -- a weak entity doesn't need to have a partial key attribute

Database vs. Database Management System (DBMS)

A database is a structural collection of data. A database management system is the software that manages databases and lets you create, edit, and delete tables.

intersection table

A table (also called a relation) used to represent a many-to-many relationship. It contains the keys of the relations in the relationship. If it contains nonkey columns, it is called an association table. See association entity.

What is the purpose of ER modeling?

An entity-relationship diagram (ERD) is a data modeling technique that graphically illustrates an information system's entities and the relationships between those entities. An ERD is a conceptual and representational model of data used to represent the entity framework infrastructure.

How are exact minimum and maximum cardinalities depicted in a relationship?

Cardinality constraints depict how many instances of one entity can be associated with instances of another entity. They are shown on ER diagrams

Entity-relationship (ER) modeling

Conceptual database modeling technique. Enables the structuring and organizing of the requirements collection process. Provides a way to graphically represent the requirements.

weak entity

ER diagram construct depicting an entity that does not have a unique attribute of its own

Relationship

ER modeling construct depicting how entites are related *Within an ER diagram, each entity must be related to at least one other entity via a relationship

Naming conventions for ER diagrams

Entities and attributes: use singular nouns Relationships: use verbs or verb phrases

What are the basic ER modeling constructs?

Entities and relationships between entities

Bare minimum of intersection table

Have atleast 2 PK from table its intersecting

Entity instances (entity members)

Occurrences of an entity. Entities themselves are depicted in the ER diagrams while entity instances are not. Entity instances are eventually recorded in the database that is created based on the ER diagram.

Types of relationships (maximum cardinality-wise)

One-to-one relationship (1:1) One-to-many relationship (1:M) Many-to-many relationship (M:N)

Synonyms used in RDM

Relation = Relational table = Table Column = Attribute = Field Row = Tuple = Record

business rules

Restrictions on resulting databases that are not a part of the standard notation for creating ER diagrams

first normal form (1NF)

a table is in 1NF if each row is unique and no column in any row contains multiple values *1NF states that each value in each column of a table must be a single value from the domain of the column *Every relational table is, by definition, in 1NF *Related multivalued columns -- columns in a table that refer to the same real-world concept (entity) and can have multiple values per record *Normalizing to 1NF involves eliminating groups of related multi-valued columns

second normal form (2NF)

a table is in 2NF if it is in 1NF and if it does not contain partial functional dependencies *If a relation has a single-column primary key, then there is no possibility of partial functional dependencies *Already in 2NF

third normal form (3NF)

a table is in 3NF if it is in 2NF and if it does not contain transitive functional dependencies

relationship roles

additional syntax that can be used in ER diagrams at the discretion of a data modeler to clarify the role of each entity in a relationship

update anomalies

anomalies in relations that contain redundant (unnecessarily repeating) data, caused by update operations

multivalued attribute

attribute for which instances of an entity can have multiple values for the same attribute

partial key

attribute of a weak entity that combined with the unique attribute of the owner entity uniquely identifies the weak entity's instances *Combination of the partial key and the unique attribute from the owner entity uniquely identifies every instance of the weak entity

optional attribute

attribute that is allowed to not have a value

composite attributes

attribute that is composed of several attributes * Not an additional attribute of an entity * Its purpose is to indicate a situation in which a collection of attributes has an additional meaning, besides the individual meanings of each attribute (CustFullName) - - (CustFName) - (CustLName)

composite unique attribute

attribute that is composed of several attributes and whose value is different for each entity instance

Unique attribute

attribute whose value is different for each entity instance * Every regular entity must have at least one unique attribute

derived attribute

attribute whose values are calculated and not permanently stored in a database

relational database

collection of related relations within each relation has a unique name

primary key

column (or a set of columns) whose value is unique for each row

associative entity

construct used as an alternative way of depicting M:N relationships *Associative entities do not have unique or partially unique attributes, and often do not have any attributes at all

Entities

constructs that represent what the database keeps track of. The basic building blocks of an ER diagram Represents various real world notions, such as people, places, objects, events, items, and other concepts Within one ERD each entity must have a different name

Cardinality constraints

depict how many instances of one entity can be associated with instances of another entity

Attribute

depiction of a characteristic of an entity * Represents the details that will be recorded for each entity instance * Within one entity, each attribute must have a different name

owner entity

entity whose unique attribute provides a mechanism for identifying instances of a weak entity

relational database model

logical database model that represents a database as a collection of related tables

Relationship instances

occurrences of a relationship *Occur when an instance of one entity is related to an instance of another entity via a relationship * Relationship themselves are depicted in the ER diagram while relationship instances are not * Relationship instances are eventually recorded in the database that is created base on the ER diagram

partial functional dependency

occurs when a column of a relation is functionally dependent on a component of a composite primary key *Only composite primary keys have separate components, while single-column primary keys don't *Hence, partial functional dependency can occur only in cases when a relation has a composite primary key

full key functional dependency

occurs when a primary key functionally determines the column of a relation and no separate component of the primary key partially determines the same column *If a relation has a single component (non-composite) primary key, the primary key fully functionally determines all other columns of a relation *If a relation has a composite key, and portions of the key partially determine columns of a relation, then the primary key does not fully functionally determine the partially determine columns

unary relationship (recursive relationship)

occurs when an entity is involved in a relationship with itself

deletion anomaly

occurs when deletion of data about a real-world entity forces deletion of data about another real-world entity

insertion anomaly

occurs when inserting data about one real-world entity requires inserting data about another real-world entity

Transitive functional dependency

occurs when nonkey columns functionally determine other nonkey columns of a relation CampaignMGRID --> CampaignMGRName

functional dependency

occurs when the value of one (or more) columns in each record a relation uniquely determines the value of another column in that same record of the relation A --> B ClientID ---> ClientName

modification anomaly

occurs when, in order to modify one real world value, the same modification has to be made multiple times

normalization

process used to improve the design of relational databases

degree of a relationship

reflects how many entities are involved in the relationship

identifying relationship

relationship between a weak entity and its owner entity in which each instance of a weak entity is associated with exactly one instance of an owner entity *Each weak entity must be associated with its owner entity via an identifying relationship *Unique attribute from the owner entity uniquely identifies every instance of the weak entity via an identifying relationship

binary relationship

relationship between two entities

ternary relationship

relationship involving three entities

Denormalization

reversing the effect of normalization by joining normalized relations into a relation that is not normalized, in order to improve query performance

multiple relationships between same entities

same entities in an ER diagram can be related via more than one relationship

relation

table in a relational database * a table containing rows and columns * the main construct in the relational database model * every relation is a table, not every table is a relation * A relation must: *Each column must have a unique name *Each row must be unique * All values in each column must be from the same domain *Within each row, each value in each column must be single valued

normal form

term representing a set of particular conditions (whose purpose is reducing data redundancy) that a table has to satisfy

ER diagram (ERD)

the result of ER modeling, serves as a blueprint for the database

relational schema

visual depiction of the relational database model

Multiple unique attributes (candidate keys)

when an entity has more than one unique attribute each unique attribute is also called a candidate key

Relationship attributes

In some cases M:N relationships can actually have attributes of their own

Update operations

Insert operation: used for entering new data in the relation Deletion operation: used for removing data from the relation Modify operation: used for changing the existing data in the relation