Database Final

Business rule

Statements that define or constrain some aspect of the business; they assert business structure, control behavior, and are expressed in terms familiar to end users

. Entity-relationship model (ER model)

A detailed representation of the data for an organization or for a business scenario, typically expressed as a graphical diagram capturing the nature and relationship of data

. Entity type

A collection of instances that share common properties or characteristics; it represents a person, place, object, event, or concept in the business environment about which the organization wishes to maintain data

. Entity instance

A single occurrence of an entity type

. Strong entity type

An entity that exists independently of other types of entities and has its own unique identifier

. Weak entity type

An entity type that is dependent on a strong entity (identifying owner), cannot exist on its own, and does not have a unique identifier (only a partial identifier)

. Identifying owner

The strong entity that a weak entity is dependent on to exist

. Identifying relationship

A relationship that links strong entities to weak entities

. Attribute

A property or characteristic of an entity or relationship type

. Required attribute

An attribute that must have a value for every entity (or relationship) instance with which it is associated

. Optional attribute

An attribute that may not have a value for every entity (or relationship) instance with which it is associated

. Composite attribute

An attribute that has meaningful component parts (sub-attributes)

. Simple attribute

An attribute that cannot be subdivided

. Multivalued attribute

An attribute that may take on more than one value for a given entity or relationship instance

. Derived attribute

An attribute whose values can be calculated from related attribute values and is not physically stored in the database

. Identifier

(Primary Key) A selected attribute or combination of attributes that uniquely identifies individual instances of an entity type

. Associative entity

An entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances

. Unary relationship

A relationship where entity instances of the same entity type are related to each other

. Binary relationship

A relationship where entity instances of one type are related to entities of another

. Ternary relationship

A relationship where entity instances of three different types are involved in the same relationship

. Cardinality

Constraints that specify how many of each entity type is allowed in a relationship

. Minimum cardinality

(Outside Knowledge) The minimum number of instances of one entity that must participate in a relationship with another entity. Maximum cardinality

. Subtype

A subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings

. Supertype

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

. Generalization

The process of defining a more general entity type from a set of more specialized entity types (a bottom-up process)

. Specialization

The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships (a top-down process)

. Completeness constraint

A constraint specifying whether an instance of a supertype MUST also be a member of at least one subtype

. Total specialization rule

A completeness constraint (indicated by a double line) dictating that an instance of a supertype MUST also be a member of at least one subtype

. Partial specialization rule

A completeness constraint (indicated by a single line) dictating that an instance of a supertype DOES NOT have to be a member of any subtype

. Disjointness constraint / Disjoint rule

A rule specifying that an instance of a supertype can be a member of only ONE subtype at the same time (e.g., a patient can be either an outpatient or a resident patient, but not both)

. Overlap rule

(Outside Knowledge) A rule specifying that an instance of a supertype CAN simultaneously be a member of two or more subtypes. Subtype discriminator

. Relation

A two-dimensional table used to represent data in a relational model

. Primary key

A unique identifier of a relation (table) that guarantees all rows are unique

. Composite key

A primary key that consists of more than one field (attribute)

. Foreign key

An identifier that enables a dependent relation (on the many side) to refer to its parent relation (on the one side)

. Entity integrity

A rule stating that no primary key attribute may be null; all primary key fields must contain data values

. Domain integrity / Domain specification

The rules controlling the allowable values for attributes in a column

. Referential integrity constraint

A rule maintaining consistency between the rows of related tables, stating that any foreign key value must match a primary key value in the parent table

. Well-structured relation

(Outside Knowledge) A relation that contains minimal redundancy and allows users to insert, modify, and delete rows without errors or inconsistencies. Anomaly (three types)

. Determinant

The attribute (or set of attributes) on the left side of a functional dependency that uniquely determines the value of another attribute

. Candidate key

An attribute that could be a primary identifier because it satisfies the requirements for being an identifier

. First normal form (1NF)

(Outside Knowledge) A relation is in 1NF if it contains no repeating groups or multivalued attributes. Second normal form (2NF)

.

SQL Flashcards (Includes Outside Knowledge for Code)

Data definition language (DDL)

Commands that define database structure. Code: CREATE TABLE table_name (col1 INT); ALTER TABLE table_name ADD col2 INT; DROP TABLE table_name; Data manipulation language (DML)

. Data Integrity Control (ON UPDATE RESTRICT / CASCADE)

Referential constraints dictating what happens on update. Code: CONSTRAINT FK_Name FOREIGN KEY (ID) REFERENCES Parent_Table(ID) ON UPDATE RESTRICT; or ON UPDATE CASCADE;

. SQL CREATE, MODIFY, and DROP Tables

DDL statements. Code: CREATE TABLE Customer_T (ID NUMBER NOT NULL); ALTER TABLE Customer_T ADD Name VARCHAR2(25); DROP TABLE Customer_T;

. SQL INSERT, UPDATE, and DELETE Data

DML statements. Code: INSERT INTO Customer_T (ID) VALUES (1); UPDATE Customer_T SET ID = 2 WHERE ID = 1; DELETE FROM Customer_T WHERE ID = 2; SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING