Database Systems Exam 1

Attribute

A characteristic of an entity or object. It has a name and a data type.

Business rule

narrative descriptions of a policy, procedure, or principle within an organization. For example, a pilot cannot be on duty for more than 10 hours during a 24 hour period. Also, a professor may teach up to four classes during any one semester.

class

a collection of like objects with a shared structure (attributes) and behavior (methods). This encapsulates an objects data representation and a methods implementation. They are organized in class hierarchy.

class hierarchy

the organization of ____ in a _____ tree where each “parent” class is a superclass and each “child” class is a subclass. Think inheritance.

conceptual model

the output of this design process. This provides a global view of an entire database. It described the main data objects, avoiding details.

conceptual schema

a representation of the conceptual model, usually expressed graphically

connectivity

describes the classification of the relationship between entities. Classifications include 1:1, 1:M, and M:N

constraint

a restriction placed on data. they are normally expressed in the form of rules. for example, “a students GPA must be between 0.0 and 4.00”. These help ensure data integrity.

data definition language (DDL)

The language that allows a database administrator to define the database structure, schema, and subschema.

data manipulation language (DML)

the language (set of commands) that allows an end user to manipulate the data in the database

• SELECT

• INSERT

• UPDATE

• DELETE

• COMMIT

• ROLLBACK

data model

a representation, usually graphic, of complex real-world data structures. These are used in the database design phase of the database life cycle.

entity

Something about which someone wants to store data; typically a person, place, a thing, a concept, or an event.

entity instance

a term used in ER modeling to refer to a specific table row

ER Diagram

a diagram that depicts an entity relationship model’s entities, attributes, and relations.

entity set

in a relational model, refers to the grouping of related entities

internal model

In database modeling, refers to a level of data abstraction that adapts the conceptual model to a specific DBMS model for implementation

internal schema

Depicts a specific representation of an internal model, using the database constructs supported by the chosen database.

logical design

A stage in the design phase that matches the conceptual design to the requirements of the selected DBMS and is, therefore, software-dependent. It is used to translate the conceptual design into the internal model for a selected database management system.

logical independence

a condition that exists when the internal model can be changed without affecting the conceptual model. (The internal model is hardware independent because it is unaffected b the choice of computer on which the software is installed. Therefore, a change in storage devices or even a change in operating systems will not affect the internal model.)

many-to-many (M:N or *..*)

One of three types of relationships in which the occurrence of an entity is associated with many occurrences of a related entity and one occurrence of the related entity is associated with many occurrences of the first entity.

one-to-many (1:M or 1..*)

One of three types of relationships that are used by data models. One entity instance is associated with many instances of the related entity.

one-to-one (1:1 or 1..1)

One of three types of relationships that are used by data models. One entity instance is associated with only one instance of the related entity.

physical independence

A condition that exists when the physical/internal model can be changed without affecting the conceptual model.

physical model

A model in which the _____ characteristics (location, path, and format) are described for the data. Both hardware and software dependent.

relation

An entity set. This is implemented as tables. These tables are related to each other through the sharing of common entity characteristics.

relational database management system (RDBMS)

A collection of programs that manages a relational database. The ____ software translates a users logical requests into commands that physically locate and retrieve the requested data. A good ____ also creates and maintains a data dictionary (system catalog) to help provide the data security, data integrity, concurrent access, easy access, and systems admin to the data in the database through a query language (SQL) and application programs.

relational diagam

A graphical representation of a relational databases entities, the attributes within those entities, and the relationships among those entities.

relational model

Developed by E. F. Codd (IBM) in 1970, this represents data as tables (relations) based on set theory. Data is organized into rows (records) and columns (attributes), with relationships formed by shared values between tables. It simplifies database design and user interaction.

relationship

An association between entities

Schema

A logical grouping of database objects (tables, indexes, views, queries, etc.) that are related to each other. Usually, a schema belongs to a single user or application.

table

A (conceptual) matrix of composed intersecting rows (entities) and columns (attributes) that represents an entity set in the relational model.

domain

In data modeling, refers to the construct used ot organize and describe an attributes set of possible values.

entity integrity

The property of a relational table that guarantees that each entity has a unique value in a primary key and that there are no null values in the primary key.

Superkey

An attribute (pr combination of attributes) that uniquely identifies each entity in a table

Candidate key

A minimal attribute that is itself a superkey

Primary key (PK)

A candidate key selected as a unique entity identifier. In the relational model, an identifier composed of one or more attributes that uniquely identifies a row.

Foreign Key

An attribute (or combination of attributes) in one table whose values must match the primary key in another table or whose values must be null

key attribute

the attribute(s) that form(s) a primary key

null

In SQL, refers to the absence of an attribute value. This doesn’t equate to blank.

referential integrity

A condition by which a dependent tables foreign key must have either a null entry or a matching entry in the related table. Even though an attribute may not have a corresponding attribute, it is impossible to have an invalid entry.

relational schema

The description of the organization of a relational database administrator.

binary relationship

An ER term used to describe an association (relationship) between two entities. Example: PROFESSOR teaches COURSE

cardinality

Assigns a specific value to connectivity. Expresses the range (min to max) of allowed entity occurrences associated with a single occurrence of the related entity.

composite attribute

An attribute that can be further subdivided to yield additional attributes. For example, a phone number may be divided into area code, an exchange number, and a four digit code.

Connectivity

Describes the classification of the relationship between entities. Classifications include 1:1, 1:M, and M:N

derived attribute

An attribute that does not physically exist within the entity and is derived via an algorithm. Example: Age = current date - birth date

mandatory participation

A term used to describe a relationship in which one entity occurrence must have a corresponding occurrence in another entity. Example: EMPLOYEE works in DIVISION. (A person cannot be an employee if he or she is not assigned to a company’s division)

Multivalued attribute

An attribute that can have many values for a single entity occurrence. For example, an EMP_DEGREE attribute might store the string “BBA, MBA, PHD” to indicate three different degrees held.

Optional Participation

In ER modeling, refers to a condition where one entity occurrence does not require a corresponding entity occurrence in a particular relationship.

Participants

An ER term used to label the entities that participate in a relationship. Example: PROFESSOR teaches CLASS. (The teaches relationship is based on the participants PROFESSOR and CLASS)

Recursive relationship

A relationship that is found within a single entity type. For example, an EMPLOYEE is married to an EMPLOYEE or A PART is a component of another PART.

relationship degree

Indicates the number of entities or participants associated with a relationship. This can be unary, binary, ternary, or higher level.

required attribute

In ER modeling, refers to an attribute that must have a value. It cannot be left empty

Simple Attribute

An attribute that cannot be subdivided into meaningful components.

Single-valued attribute

An attribute that can have only one value

Strong relationship

When two entities are existence-dependent; from a database design perspective, this exists whenever the primary key of the related entity contains the primary key of the parent entity.

ternary relationship

An ER term used to describe an association (relationship) between three entities. Example: a CONTRIBUTOR contributes money to a FUND from which a RECIPIENT receives money.

unary relationship

an ER term used to describe an association within an entity. Example: a COURSE is a prerequisite to another COURSE

weak entity

An entity that displays existence dependence and inherits the primary key of its parent entity. Example: a DEPENDENT requires the existence of an EMPLOYEE

Why are tuples in a relation not ordered?

A relation is defined as a set of tuples. Mathematically, elements of a set have no order among them; hence, tuples in a relation do not have any particular order. In other words, a relation is not sensitive to the ordering of tuples. However, in a file, records are physically stored on disk (or in memory), so there always is an order among the records. This ordering indicates first, second, ith, and last records in the file. Similarly, when we display a relation as a table, the rows are displayed in a certain order.

Why are duplicate tuples not allowed in a relation?

Relations are sets, and sets require uniqueness. Duplicates would violate tuple identity and complicate data retrieval.

What is the difference between a key and a superkey?

A superkey can be any set of attributes that uniquely identifies tuples. Meanwhile, a key is a minimal superkey, where no subset of the key is a superkey).

What is a key constraint?

This ensures every relation has a primary key that is unique and non-redundant. No two tuples can have identical key values.

What is the Not NULL constraint?

This restricts an attribute from having a NULL value

What is the entity integrity constraint?

Requires that primary key attributes cannot be NULL, guaranteeing every tuple is uniquely identifiable.

Referential integrity constraints

Ensures foreign key values either match a primary key value in the referenced relation or are NULL (e.g., Employee.DeptID must exist in Department.DeptID)

Superclass/Subclass Relationship

A hierarchical connection where a superclass (general entity) has subclasses (specific entities). Subclasses inherit attributes/relationships from the superclass (e.g., Vehicle as a superclass and Car as a subclass).

Specialization and Generalization

Specialization: Top-down process of refining a superclass into subclasses (e.g., splitting Vehicle into Car and Truck).

Generalization: Bottom-up process of combining subclasses into a superclass (e.g., merging Car and Truck into Vehicle).