Csc 471 - Test 1

Front 1

Database-Management System (DBMS)

Back 1

Collection of interrelated data and a set of programs to access those data.

Front 2

Database

Back 2

Collection of data, which contains info relevant to an enterprise.

Front 3

Levels of Abstraction

Back 3

Physical Level, Logical Level, and View Level

Front 4

Physical Level

Back 4

Describes how a record (the actual data) is stored. Lowest level of abstraction.

Front 5

Logical Level

Back 5

Describes what data are stored in the database, and what relationships exist among those data.

Front 6

View Level

Back 6

Describes only part of the entire database. Application programs hide details of data types. Highest level of abstraction.

Front 7

Data Model

Back 7

Collection of tools for describing data, data relationships, data semantics, and consistency constraints.

i.e. Relational Model, Entity-Relationship Model, Object-Based Data Model, Semistructured Data Model

Front 8

Relational Model

Back 8

Uses a collection of tables (relations) to represent both data and the relationships among those data. Each table has multiple columns.

Front 9

Entity-Relationship Model

Back 9

Uses a collection of objects, called entities, and relationships among these objects. An entity is a "thing" in the real world that's distinguishable from other objects.

Front 10

Object-Based Data Model

Back 10

Extends the relational model by including object orientation and constructs to deal with added data types.

Front 11

Semistructured Data Model

Back 11

Permits specification of data where individual data items of the same type may have different sets of attributes. i.e. Extensible Markup Language (XML)

Front 12

Data-Definition Language (DDL)

Back 12

Specifies the database schema. DDL gets as input some instructions and generates some output placed in the data dictionary.

Domain constraints, referential integrity, assertions, authorization

Front 13

Data Dictionary

Back 13

Contains metadata; data about data.

Front 14

Data-Manipulation Language (DML)

Back 14

Enables users to access or manipulate data as organized by the appropriate data model.

Retrieval, insertion, deletion, modification

Front 15

Procedural DMLs

Back 15

Require a user to specify what data are needed and how to get those data.

Front 16

Declarative DMLs

Back 16

Require a user to specify what data are needed without specifying how to get those data.

Front 17

SQL

Back 17

Widely used nonprocedural language.

Front 18

Entity-Relationship Data Models

Back 18

...

Front 19

Composite Attributes

Back 19

Can be divided into subparts (other attributes). Denoted by indentions.

Front 20

Multivalued Attributes

Back 20

Denoted by enclosing attribute name in braces {}

Front 21

Derived Attributes

Back 21

Can be computed from other attributes. Denoted with () placed after attribute name.

Front 22

Mapping Cardinalities

Back 22

Expresses the number of entities to which another can be associated via a relationship set.

One-to-one, many-to-one, one-to-many, many-to-many

Front 23

Total Participation

Back 23

Every entity in entity set E participates in at least one relationship in relationship set R.

Double lines indicate total participation of an entity in a relationship set.

Front 24

Partial Participation

Back 24

Only some entities in entity set E participate in relationships in relationship set R.

Front 25

Super Key

Back 25

A set of one or more attributes whose values uniquely determine each entity of an entity set.

Front 26

Candidate Key

Back 26

A minimal super key. Several can exist.

Front 27

Primary Key

Back 27

A candidate key chosen by the database designer as the principal means of identifying tuples within a relation.

Combination of primary keys of the participating entity sets forms a super key of a relationship set.

Underline indicates primary key attributes.

Front 28

Roles

Back 28

Entity sets of a relationship need not be distinct. Each occurrence of an entity set plays a "role" in the relationship.

Denoted with a recursive relation.

Front 29

Weak Entity Set

Back 29

An entity set that doesn't have a primary key. Depends on the existence of an identifying entity set to be meaningful. Weak entity set must relate to identifying set via a total, many-to-one (from weak to identifying) relationship set.

Depicted using a double diamond.

Front 30

Discriminator (Partial Key)

Back 30

In a weak entity set, it's the set of attributes that distinguishes among all the entities of a weak entity set.

Primary key of weak entity set formed by discriminator, plus the primary key of the strong entity set on which the weak entity set depends on.

Underlined with a dashed line.

Front 31

Specialization

Back 31

Designate subgroupings within an entity set that are distinctive from other entities in the set.

Subgroupings have attributes or participate in relationships that do not apply to higher level entity set.

Depicted by hollow arrow-head.

Front 32

Generalization

Back 32

Bottom-up design. Combine a number of entity sets that share the same features into a higher level entity set.

Front 33

Disjoint Specialization

Back 33

An entity can only belong to one lower level entity set.

Depicted by a single arrow.

Front 34

Overlapping Specialization

Back 34

An entity can belong to more than one lower level entity set.

Depicted by 2 separate arrows.

Front 35

Total Specialization

Back 35

Each higher level entity must belong to a lower level entity set.

Depicted by adding the keyword "total" in the diagram and drawing a dashed line from keyword to corresponding arrow-head.

Front 36

Aggregation

Back 36

Abstraction through which relationships are treated as higher-level entities. Allows relationships between relationships.

Front 37

SQL Commands

Back 37

...

Front 38

not null

Back 38

Specifies that the null value is not allowed for that attribute.

branch_name char(15) not null

Front 39

primary key(A1,...,An)

Back 39

Clause lists attributes that comprise the primary key.

primary key (customer_name )

Front 40

unique

Back 40

unique(A1,...An) states that the attributes A1,...,An form a candidate key.

Front 41

check(P)

Back 41

Permits domains to be restricted. P is a predicate.

create table branch
(branch_name char(15),
branch_city char(30),
assets integer,
primary key (branch_name),
check (assets >= 0))

Front 42

foreign key(A1,..,An)... references r

Back 42

Lists attributes that comprise the foreign key and the name of the relation referenced by the foreign key.

foreign key (customer_name ) references customer )

Front 43

Assertion

Back 43

A predicate expressing a condition that we wish the database always satisfies.

create assertion <assertion-name> check <predicate>

Front 44

Relation Schemas

Back 44

...

Front 45

Strong Entity Set

Back 45

Reduces to schema with the same attributes.

Foreign Keys: none

Front 46

Weak Entity Set

Back 46

Becomes a table that includes a column for the primary key of the identifying strong identity set and discriminator of the weak entity set.

Foreign Keys: On weak entity set, specifying that the primary key attributes from the strong entity set references the strong entity set.

Front 47

Many-to-Many Relationship Set

Back 47

Represented as schema with attributes for the primary keys of the 2 participating entity sets, and any descriptive attributes of the relationship set.

Foreign Keys: For each entity set E related to relationship set R, create foreign key constraint from relation schema R, with the attributes of R that were derived from primary-key attributes of E referencing the primary key of the relation schema representing E.

Front 48

Many-to-One, One-to-Many Relationship Set

Back 48

When participation is total on "many" side, represented by adding an extra attribute to the "many" side, containing the primary key of the "one" side. (Instead of creating schema for relationship set)

Foreign Keys: Referencing each of the entity sets participating in the relationship set. Add the other foreign-key constraints to the combined schema.

Front 49

One-to-One Relationship Set

Back 49

Either side can be chosen to act as the "many" side to add the extra attribute. (Instead of creating schema for relationship set)

Foreign Keys: Referencing each of the entity sets participating in the relationship set. Add the other foreign-key constraints to the combined schema.

Front 50

Composite Attributes

Back 50

Flattened out by creating a separate attribute for each component attribute. Don't create a separate attribute for the composite attribute itself.

Foreign Keys: None

Front 51

Multivalued Attributes

Back 51

A multivalued attribute M of an entity E is represented by a separate schema EM. EM has attributes corresponding to the primary key of E and an attribute corresponding to multivalued attribute M.

Foreign Keys: Create foreign-key constraint on the relation schema created from the multivalued attribute, with the attribute generated from the primary key of the entity set referencing the relation generated from the entity set.

Front 52

Specialization (1)

Back 52

Form a schema for the higher-level entity. Form a schema for each lower-level entity set, include primary key of higher-level entity set and local attributes.

Foreign Keys: On the lower-level entity sets, with their primary key attributes referencing the primary key of the relation created from the higher-level entity set.

Front 53

Specialization (2)

Back 53

Form a schema for each entity set with local and inherited attributes. If specialization is total, schema for generalized entity set not required.

Front 54

Aggregation

Back 54

Create a schema containing primary key of the aggregated relationship, primary key of the associated entity set, and any descriptive attributes.