Database Exam

Supertype

- A generic entity type that has a relationship with one or more subtypes

Entities

- The real-world objects in the user enviorment about which the organization wishes to maintain data. - Entity Type - collection of entities that share common properties or characteristics (often corresponds to a table) - Entity instance - A single occurrence of an entity type » person, place, object, event, concept (often corresponds to a row in a table)

information

- data processed to increase knowledge in the person/business using the data

Database

- organized collection of logically related data - Representations of a set of real world objects, e.g., people, cars, buildings etc. - Set of desired attributes of these objects, e.g., tall, red, heavy etc. - Relationship among these objects, e.g., John is taking DB course - Set of rules for incorporating changes to these objects, e.g., John's improving his grade etc

Second Normal Form

A relation in 2NF if it is in 1NF and contains no partial functional dependencies. • A partial functional dependency exists when a nonkey attribute is functionally dependent on part (but not all) of the primary key. • In another words,every non-key attributemust be fully functionally dependent on the ENTIRE primary key - Every non-key attribute must be defined by the entire key, not by only part of the key

First Normal Form

A relation that has a primary key and in which there are no repeating groups - No repeating groups means that single fact at the intersection of each row and column of the table - Primary key uniquely identifies each row in the relation • All relations are in 1st Normal Form

Database management system

A software system that is used to create, maintain, and provide controlled access to user databases

Subtype

A subgrouping of the entities in an entity type that is meaningful to the organization and that shares common attributes or relationships distinct from other subgroupings.

Disjointness Constraints

Address Whether an instance of a supertype may simultaneously be a member of two (or more) subtypes Disjoint Rule: (graphical notation: d in circle) • An instance of the supertype can be only ONE of the subtypes Overlap Rule: (graphical notation: o in circle) • An instance of the supertype could be more than one of the subtypes

Completeness Constraint

Address the question of Whether an instance of a supertype must also be a member of at least one subtype • Total Specialization Rule: Yes (double line) - A rule that specifies that each entity instance of a supertype must be a member of some subtype in the relationship • Partial Specialization Rule: No (single line) - A rule that specifies that an entity instance of a supertype is allowed not to be a member of any subtype in the relationship

Anomaly

An error or inconsistency that may result when a user attempt to update a table that contains redundant data. • Three types of anomalies are insertion, deletion and modification anomalies.

Business Rule

Business Rules and Data modeling • Business Rules: the foundation of data modeling - Are statements that define or constrain some aspect of the business - Are derived from policies, procedures, events, functions - Assert business structure - Control/influence business behavior - Should be expressed in terms familiar to end users - Should be reflected in data modeling and automated through DBMS software

Why data modeling is important

Characteristics of data captured during data modeling are crucial in the design of databases, programs, and other system components. - Facts and rules that are captured during this process are essential in assuring data integrity in an information system. • Data, rather than processes, are the most important aspects of many modern information systems and hence, require a central role in structuring system requirements. • Data tend to be more stable than the business processes that use the data.

DDL

Commands that define a database, including creating, altering, and dropping tables and establishing constraints

DML

Commands that maintain and query a database

Weak Entity

Create a relation with a composite primary key(which includes the primary key of the other entity) and monkey attributes

Regular Entity

Create a relation with a primary key and monkey attributes

Multivalued attribute

Create a separate relation for multivalued attribute with composite primary key, including the primary key of the entity.

Composite attribute

Each component of a composite attribute becomes a separate attribute in the target relation

E-R Model

Entity-relationship model: - A detailed, logical and graphical representation of the data for an organization or for a business area. - It is represented in terms of Entities, Relationships and Attributes

Physical Database Design

Physical Database Design translates the logical description of data into the technical specifications for storing and retrieving data - What DBMS will be used • Goal-create a design for storing and processing data that will provide adequate performance and efficiency, and insure database integrity, security, and recoverability

System Development Life Cycle

Planning, Analysis, Logical Design, Physical Design, Implementation, Maintenance. Planning Purpose-preliminary understanding of the business situation and how information systems might help solve a problem or make an opportunity possible Analysis Purpose - to analyze the business situation thoroughly to determine requirements, to structure those requirements, and to select among competing system features Logical Design Purpose - requirements in detail; integrate database views into conceptual data model Physical Design Purpose- develop technology and organizational specifications Implementation Purpose - programming, testing, training, installation, documentation Maintenance Purpose -monitor, repair, enhance

Advantages of Database Approach

Program-data independence • Planned data redundancy • Improved data consistency • Improved data sharing • Increased application development productivity • Enforcement of standards • Improved data quality,data accessibility and responsiveness • Reduced program maintenance • Improved decision support

Supertype/Subtype Representation

Relationships at the supertype level indicate that all subtypes will participate in the relationship • The instances of a subtype may participate in a relationship unique to that subtype. In this situation, the relationship is shown at the subtype level

Relationships

Relationships between entities that exist in organization data so that desired information can be retrieved. - Relationship type- A meaningful association between (or among) entity types - Relationship instance -An association between or among entity instances where each relationship instance associates exactly one entity instance from each participating entity type ------------ Relationship cardinality is a constraint on the number of instances of one entity that can (or must) be associated with each instance of another entity. • MinimumCardinality - If zero, then optional - If one or more, then mandatory • MaximumCardinality - The maximum number, one or many • Better use "one or more" instead of "many" in business rule description

SQL

Structured Query Language • The standard for relational database management systems (RDBMS) • RDBMS: A database management system that manages data as a collection of tables in which all relationships are represented by common values in related tables

When to use Supertype/Subtype

Subtype ------------- There are attributes that apply to some (but not all) of the instances of an entity type. - The instances of a subtype participate in a relationship unique to that subtype.

Enhanced Entity-Relationship (EER) Model

The model that has resulted from extending the original E-R model with new modeling constructs such as supertypes and subtypes

Data Normalization

The process of decomposing relations with anomalies to produce smaller, well- structured relations - A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies

Generalization

The process of defining a more general entity type from a set of more specialized entity types. BOTTOM UP

Specialization

The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships. - TOP-DOWN

Denormalization

The process of transforming normalized relations into nonnormalized physical record specifications by combining several logical tables into one physical table to avoid doing joins. - Benefits: • Can improve data processing efficiency by reducing number of table access or lookups (i.e. reduce number of join queries) - Costs (due to data duplication) • Wasted storage space • Data integrity/consistency threats - Common denormalization opportunities • Two entities with a one-to-one relationship • Many-to-many relationship (associative entity) with non-key attributes • Reference data (1:N relationship where 1-side entity does not participate in any other relationship)

Binary or Unary M:N relationship or associative without its own key

create a relation with composite primary key using the primary keys of the related entities plus any monkey attributes of the relationship or associative entity

Binary or Unary M:N relationship or associative with its own key

create a relation with the primary key associated with the associative entity plus any monkey attributes of the relationship or associative entity.

Logical data modeling

creating stable database structures - expressing the organizational requirements and business rules. developed before the physical model. Normalization of tables

metadata

data that describes the properties and context of user data

Foreign keys

identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship).

Binary or Unary 1:1 relationship

place the primary key of either entity in the relation for the other entity. if one side of the relationship is optional, place the foreign key of the entity on the mandatory side in the relation.

Binary or Unary 1:N

place the primary key of the entity on the one side of the relationship as a foreign key for the entity with the many side

Referential Integrity constraint

rule that maintains consistency amongst the rows of data. fi there is a foreign key, it must match the foreign key in another row or be null. shows relationship between models

Conceptual data modeling

understanding the organization - getting the right requirements built into the database design. E-R modeling

Primary keys

unique identifiers of the relation in question. Examples include employee numbers, social security numbers, etc. This is how we can guarantee that all rows are unique.

Third Normal Form

• A relation is in 3NF if it is in 2NF and no transitive dependencies - A transitive dependency is a functional dependency between the primary key and one or more nonkey attributes that are dependent on the primary key through another nonkey attribute

Attributes

- properties or characteristics of an entity or relationship type (often corresponds to a field in a table) ----------------------------------------- RequiredAttribute • must have a value for each entity (relationship) instance, • OptionalAttribute • may not have a value for every entity (relationship) instance SimpleAttribute • Cannot be broken down into smaller components that are meaningful to the organization • Composite Attribute • Has meaningful component parts Single-valued Attribute • Can take on only one value for a given entity(relationship ) instance • Multivalued Attribute • May take on more than one value for a given entity(relationship ) instance • DerivedAttribute • Whose values can be calculated from related attribute values

data

- stored representations of meaningful objects and events • Structured: numbers, text, dates • Unstructured: images, video, documents

Relation

-A relation is a named, two-dimensional table of data. It must have a unique name. 2. Every attribute value must be atomic (single-valued, not multivalued and not composite). 3. Every row must be unique (can't have two rows with exactly the same values for all their fields). 4. Attributes (columns) in tables must have unique names. 5. The order of the columns must be irrelevant. 6. The order of the rows must be irrelevant.