IE 4/512 Midterm 1 Content

Define “data”

Known facts that can be recorded and have implicit meaning

What is a “database”?

A collection of related data

What is a “miniworld”?

Some part of the real world about which data is stored in a database

What is a Database Management System (DBMS)?

A general-purpose software that facilitates in defining, constructing, manipulating, processing, and sharing databases among various users and applications.

What are some characteristics of a database?

• Logically coherent

• Data has inherent meaning

• Built for a specific purpose

What is a database system? What can you do on a database system?

A platform with the DBMS software and the data itself

• Issue queries or complete transactions

What does it look like to define a particular database?

Split it up in terms of its

• Data types

• Structures

• Constraints

What does it look like to construct or load a database?

“Instantiating” a database. load the database’s contents on a secondary storage medium (DBMS)

What are two ways to manipulate a database?

1. Retrieval

2. Modification

What does it mean to retrieve information from a database?

Generate reports through queries

What does it mean to modify a database?

Initiate insertions, deletions, or updates to the contents of the database.

What is a database query?

Accessing different parts of the data to create a result after a request

• “Asking a question” to the database

Is a query the same as a simple search?

No

Are changes made to the data during a query

No

What is a database transaction?

Making changes to a database by updating certain values, or generating new data to store in the database

In what way is a transaction a modified query?

There is a question asked to the database but that question changes something in the database

Define scalability

The process of keeping up with changing user requirements as more information is updated

What are business rules in database design?

Logical statements which describe the relationship between entities in a conceptual schema.

What is a DBMS catalog? And what does it store?

A place that stores the description (metadata) of a particular database

• Structures

• Types

• Constraints

What is metadata?

Data that provides information about other data

• “Data about data”

What can the DB approach be compared to?

A file processing approach

• Customized programs are written to access the data stored in files

What does it mean for a database to contain a single repository?

A single place where data is modified once and then access by various users through queries and transactions

What is Program Data Independence? And what does it allow for?

In which the structure of the data files in the DBMS catalog is stored separately from the access programs.

• Changing data structures/organization without changing the DMBS access programs

What is a Data Abstraction?

A diagram which represents the structure of the data

Define what a data model is

A set of concepts that describe the structure, operations, and constraints, of a database.

How are constructs used in a data model?

Used to define the database structure

What is the purpose of a data model? What do the constructs in them contain?

To hide storage details and show users a conceptual view of the database

• Objects and Object Properties

  1. Elements (and their data types)

  2. Groups of Elements (ATTRIBUTES)

  3. Relationships among such groups

What are data model operations?

Actions used to specify database retrievals and updates by referring to the constructs of the data model

What are the basic model operations?

• Inserting Data

• Deleting Data

• Updating Data

Name two examples of user-defined data model operations

1. “Compute-Student-GPA”

2. “Update-Inventory”

What is a conceptual data model? And what can they include?

A conceptual overview that allows users to understand the data and relationships within the system (E-R Diagram)

• Entities

• Attributes

• Relationships

What is a physical data model?

A way to provide concepts that describe details of how the data is stored in the computer

• Usually specified through DBMS design and admin manuals

What is an implementation data model?

A way to provide concepts that fall between a physical and conceptual data model

• Used in many commercial DBMS implementations

What’s the benefit in supporting multiple views of the data?

Each user can see a different view of the database, seeing data of interest to the user

What are three benefits to sharing data, and multi-user transaction processing?

Many users to retrieve information from and update the database

1. Concurrency control

2. Recovery

3. Online Transaction Processing (OLTP)

Define Concurrency Control

Each transaction is correctly executed or aborted

Define Recovery Subsystem

Each completed transaction has it's effect permanently in the database

Define Online Transaction Processing (OLTP)

Allows hundreds of concurrent transactions to occur per second

What is redundancy and why do we want to reduce it in database design?

The presence of the same data in multiple places. We want to decrease:

1. Duplication effort

2. Wasted storage space

3. Inconsistencies

What is an integrity constraint and how can we implement it in a database?

A way of restricting the attributes of a database. You do this by

• Having a data type for each item

• Restricting value ranges

• Having key's or uniqueness

• Referential Integrity

What is a Database Schema? And what does it include?

A description of a database

• Database structure

• Data types

• Constraints

What is another name for a database schema?

An intention (permanent, independent of time)

What is a schema diagram?

An illustrative display of most aspects of the database schema

What is a schema construct? Name an example of the miniworld of a university course system.

A component of the schema , or an object, within the schema

• STUDENT, COURSE

What is a database state? What is another name for it?

All the actual data stored in a database at a particular moment in time.

• Also known as an “instance”, “occurrence”, or “snapshot”

What is an initial database state?

The database state when it is initially loaded into the system.

What is a valid database state?

A state that satisfies the structure and constraints of the database.

At what rate does a database schema change?

Very infrequently

At what rate does the database state change?

Every time the database is updated.

What is a Data Definition Language (DDL)?

A language used to set up the physical description/conceptual schema of the database.

• Set SQL commands like CREATE, ALTER, DROP

What is a Data Manipulation Language (DML)?

Used to specify database retrievals and updates (queries + transactions).

SQL stands for

Structured Query Language

Outline the database conceptual design process

First design the Model which contains the rules for structuring data and concepts used to describe a set of data.

Then the schema which contains the perception of data in reality (general structure of the data)

Then the instance which gives a description of reality at a given time

What is an entity

Specific things or objects in the miniworld that are represented in the database

• Could be physical or conceptual

What are attributes, and what could they include?

Attributes are properties used to describe an entity

List the different kinds of attributes

• Simple

• Composite

• Multi-Valued

What is a simple attribute?

Each entry has an atomic value for the attribute

What is a composite attribute?

The attribute may be composed of many elements

Example:

• Address can have apartment number, street, city, zip, etc.

• Name can have first name, middle name, last name

What is a multi-valued attribute? State an example.

An entity that has multiple values for that attribute

Example:

• Color of a var, or PreviousDegrees of a STUDENT

What are NULL values for attributes? List an example or two.

An entity does not have an applicable value for that attribute

Example

• Phone extension

• Apartment number

What is a key attribute?

An attribute of an entity type for which each entry must have a unique value

What is an entity set, and how many sets are listed in this image?

Each entity set has a collection of entities stored in the database (described in subscripts)

This example has THREE CAR entity instances in the entity set for CAR

What is a value set? What is an example of one?

Specifies the set of values associated with an attribute

Example:

• LastName has a value which is a character string up to 15 characters

• Date has a value consisting of MM-DD-YYYY where each letter is an integer

What is a relationship?

A verb that relates two or more distinct entities with a specific meaning

• Works

• Manages

• Participates

What is a relationship set? What can you create with it?

The current set of relationship instances represented in the database

• These instances create a Relationship Instance Diagram

What are structural constraints (constraints on relationship types)?

Cardinality ratios and existence dependency constraints that are determined by the miniworld situation that the relationships represent.

What is a cardinality constraint?

specifies the max number of relationship instances that an entity can participate in

What is an existence dependency constraint?

Specifies if the existence of an entity depends on it being related to another entity instance through the relationship type.

What is a recursive relationship type? Name an example.

A relationship type between the same participating entity type in distinct roles

Example:

• Employee participates twice in two distinct roles

  • Supervisor (or boss) role

  • Supervisees (or subordinate) role

What is a weak entity type?

An entity that does not have a key attribute and is identification-dependent on ANOTHER entity type

What is unique about the relationships surrounding a weak entity type?

A weak entity type always has a total participation constraint with respect to its identifying relationship

A relationship type can have ___. Explain how this is shown in the following diagram

Can have attributes.

Here the value of the attribute for each relationship instance describes the number of hours a week that an EMPLOYEE works on a PROJECT.

Like filling out a data table with the two entities in them.

What kinds of relationship types can you have in the higher degree?

• Degree 1 called unary

• Degree 2 called binary

  • What we have studied so far

• Degree 3 called ternary

• Degree n called n-nary

What is a unary relationship?

A relationship between the instances of a single entity type, you can have a one-to-one or one-to-many relationship. But it is the same entity

What is a binary relationship?

The relationship between the instances of two entities

What is a ternary relationship?

The simultaneous relationship between three entities

What’s the difference between relationship constraints for E-R and EE-R database models?

Existence vs. Membership

How are EE-R diagrams split up?

• Subclasses/Superclasses

• Specialization/Generalization

• Categories

• Attribute and Relationship Inheritance

What connects an entity in a subclass with an entity in their superclass?

Inheritance

What is specialization? What is this process based on?

The process of defining a set of subclasses of a superclass

• Based on distinguishing characteristics of the entities in the superclass

What do you call an attribute associated to an entity in a subclass?

Specific or Local attribute

What does this “U” symbol refer to? And what is it pointing to?

A proper set. It is placed on top of a subclass pointing to its superclass

What is Generalization?

A processes in which many classes with common features are generalized into one superclass. The reverse of specialization.

What does it mean for an entity to be predicate-defined?

A type of specialization defined by placing a value of some attribute to an entity in a subclass.

What does it mean for an entity to be attribute-defined?

All subclasses in the specialization have their membership condition based on the same attribute of the subclass.