Set 1

storage manager

translates the query processor instructions into low-level file-system commands that modify or retrieve data.

Database design begins with

verbal or written requirements for the database. Requirements are formalized as an entity-relationship model and then implemented in SQL.

The following terms describe Entity-Has-Attribute minima:

-Required attribute — each entity instance has at least one attribute instance. -Optional attribute — each entity instance can have zero attribute instances.

The following terms describe Entity-Has-Attribute maxima:

-Singular attribute — each entity instance has at most one attribute instance. -Plural attribute — each entity instance can have many attribute instances. -Unique attribute — each attribute instance describes at most one entity instance.

An entity-relationship model includes three kinds of objects:

1. An entity is a person, place, product, concept, or activity. 2. A relationship is a statement about two entities. 3. An attribute is a descriptive property of an entity.

Database design process

1. Analysis 2. Logical design 3. Physical design

The relational data structure is based on three mathematical concepts:

A domain is a named set of possible database values, such as integers, dictionary words, or logical values TRUE and FALSE. A tuple is a finite sequence of values, each drawn from a fixed domain. Ex: (3, apple, TRUE) is a tuple drawn from domains (Integers, DictionaryWords, LogicalValues). A relation is a named set of tuples, all drawn from the same sequence of domains. Ex: The relation below is named Grocery and contains three tuples.

Referential integrity can be violated in four ways:

A primary key is updated. A foreign key is updated. A row containing a primary key is deleted. A row containing a foreign key is inserted.

All data in a relational database is structured in tables:

A table is a collection of data organized as columns and rows. A column is a set of values of the same type. Each column has a name, different from other column names in the table. A row is a set of values, one for each column. A cell is a single column of a single row. In relational databases, each cell contains exactly one value.

Database

A very large, integrated collection of Data.

What is the result of a relational operation?

All relational operations result in a table. The result table is not stored in the database but has the same structure as stored tables.

Composite primary keys obey three rules:

Column values, when grouped together, must be unique. Ex: The combination (2538, 1) is unique within (ID, Number). Columns may not contain NULL. Composite primary keys must be minimal.

what is the fourth analysis step?

Creating supertype and subtype entities

Entities, relationships, and attributes are depicted in

ER diagrams. Rectangles represent entities. Entity names appear at the top of rectangles. Lines between rectangles represent relationships. Text inside rectangles and below entity names represent attributes.

What does ER model stand for?

Entity Relationship Model

After the 'implement entities' step is completed, table and column specifications are final.

FALSE

Foreign keys always have the same name as the referenced primary key.

False Foreign key names include the name of the referenced primary key and an optional prefix, derived from the relationship name

Many-one and one-one relationships are always implemented before many-many relationships.

False, can be implemented in any order

During logical design, an SQL data type is defined for each attribute type and documented in the

Glossary

The dependency relationship from subtype to supertype is called an

IsA relationship.

NoSQl

Optimized for big data generated on the internet

Databases can automatically correct referential integrity violations with any of four actions, which are specified in SQL when creating a table with a foreign key:

RESTRICT rejects an insert, update, or delete that violates referential integrity. SET NULL sets invalid foreign keys to NULL. SET DEFAULT sets invalid foreign keys to a default primary key value, specified in SQL. CASCADE propagates primary key changes to foreign keys.

Representations in a table diagram

Rectangles represent tables. Table names appear at the top of rectangles. Text within rectangles and below table names represents columns. Solid bullets (●) indicate key columns. Empty bullets (○) and arrows indicate columns that refer to keys.

Implementing plural attributes

Singular attributes remain in the initial table, but plural attributes move to a new table: The new table contains the plural attribute and a foreign key referencing the initial table. The primary key of the new table is the composite of the plural attribute and the foreign key. The new table is dependent on the initial table, so primary key cascade and foreign key restrict rules are specified. The new table name consists of the initial table name followed by the attribute name.

primary keys should be:

Stable. Primary key values should not change. Unstable primary keys cause database management problems. When a primary key value changes, statements that specify the old value must also change, and the new primary key value must cascade to foreign keys. Simple. Primary key values should be easy to type and store. Small values are easy to specify in an SQL WHERE clause and speed up query processing. Ex: A 2-byte integer is easier to type and faster to process than a 15-byte character string. Meaningless. Primary keys should not contain descriptive information. Descriptive information occasionally changes, so primary keys containing descriptive information are unstable.

The primary key of a table that implements a many-many relationship is composite.

TRUE

Tables obey three structural rules:

Tables are normalized — exactly one value exists in each cell. No duplicate column names — duplicate column names are not allowed in one table. However, the same column name can appear in different tables. No duplicate rows — no two rows may have identical values in all columns.

A one-one relationship becomes a foreign key:

The foreign key can go in the table on either side of the relationship. Usually, the foreign key is placed in the table with fewer rows, to minimize the number of NULL values. The foreign key refers to the primary key on the opposite side of the relationship. The foreign key name is the primary key name with an optional prefix. The prefix is derived from the relationship name and clarifies the meaning of the foreign key.

A many-one or one-many relationship becomes a foreign key:

The foreign key goes in the table on the 'many' side of the relationship. The foreign key refers to the primary key on the 'one' side. The foreign key name is the primary key name with an optional prefix. The prefix is derived from the relationship name and clarifies the meaning of the foreign key.

A many-many relationship becomes a new table:

The new table contains two foreign keys, referring to the primary keys of the related tables. The primary key of the new table is the composite of the two foreign keys. The new table is dependent on the related tables, so primary key cascade and foreign key restrict rules are specified. The new table name consists of the related table names with an optional qualifier in between. The qualifier is derived from the relationship name and clarifies the meaning of the table.

data independence

The principle that physical design never affects query results Data independence allows database designers to tune query performance without changes to application programs. When database designers modify indexes or row order, applications run faster or slower but always generate the same results.

what is a dependency relationship?

The way that the dependent and master entities are related

Attributes can be singular or plural

True

The foreign key goes in the table on the 'many' side of the relationship.

True

When a plural attribute becomes a new dependent table, the primary key of the initial table becomes a foreign key.

True

Attribute names always include an attribute type

True Every attribute name includes the associated attribute type, preceded by an optional entity name and qualifier.

What does the term 'UNIQUE' mean in the CREATE TABLE statement?

UNIQUE is specified on columns derived from unique attributes.

Primary keys obey two rules:

Values must be unique within the column. This rule ensures that each value identifies at most one row. Values may not be NULL. This rule ensures that each value identifies at least one row.

A set is

a collection of values, or elements, with no inherent order. Sets are denoted with braces. Ex: {apple, banana, lemon} is the set containing three kinds of fruit. Since sets have no order, {apple, banana, lemon} is the same set as {lemon, banana, apple}.

key

a column used to identify individual rows of a table. Tables, keys, and columns are specified in SQL with CREATE TABLE statements.

A foreign key is

a column, or group of columns, that refer to a primary key.

A primary key is

a column, or group of columns, used to identify a row.

The relational model is

a database model based on mathematical principles, with three parts: A data structure that prescribes how data is organized. Operations that manipulate data structures. Rules that govern valid relational data.

log

a file containing a complete record of all inserts, updates, and deletes processed by the database. The transaction manager writes log records before applying changes to the database. In the event of a failure, the transaction manager uses log records to restore the database

A partition of a supertype entity is

a group of mutually exclusive subtype entitie

transaction

a group of queries that must be either completed or rejected as a whole.

An entity-relationship model is

a high-level representation of data requirements, ignoring implementation details. An entity-relationship model guides implementation in a particular database system, such as MySQL.

application programming interface, or API,

a library of procedures or classes that links a host programming language to a database. The host language calls library procedures, which handle details such as connecting to the database, executing queries, and returning results.

A database system is composed of

a query processor, storage manager, transaction manager, log, and catalog.

An artificial key is

a single-column primary key created by the database designer when no suitable single-column or composite primary key exists. Usually artificial key values are integers, generated automatically by the database as new rows are inserted to the table. Artificial keys are stable, simple, and meaningless.

NULL is

a special value that represents missing data. NULL represents either 'unknown' or 'inapplicable'.

query language

a specialized programming language, designed specifically for database systems. ex: SQL

database design

a specification of database objects such as tables, columns, data types, and indexes. Database design also refers to the process used to develop the specification.

A glossary, also known as a data dictionary or repository, documents what?

additional detail in text format. A glossary includes names, synonyms, and descriptions of entities, relationships, and attributes. For simple databases with few users, a database designer may record the glossary with a text editor.

Database Physical Design Phase

adds indexes and specifies how tables are organized on storage media

catalog

also known as a data dictionary, is a directory of tables, columns, indexes, and other database objects. Other components use catalog information to process and execute queries.

An instance is

an element of a set: An entity instance is an individual thing. Ex: The employee Sam Snead. A relationship instance is a statement about entity instances. Ex: "Maria Rodriguez manages Sales." An attribute instance is an individual value. Ex: The salary $35,000.

A dependent entity depends on?

another entity, called the master entity.

What is included in a glossary? a) Descriptions only b) Names and synonyms only c) Names, synonyms, and descriptions only d) Names, synonyms, descriptions, and ER diagrams

c) Names, synonyms, and descriptions only

The foreign key in the subtype table usually has the referential integrity action ________ on primary key delete.

cascade

database user

consumer of data in a database. Database users request, update, or use stored data to generate reports or information. Database users usually access the database via applications but can also submit queries directly to the database system.

The foreign key in a subtype table implements the ________ relationship between subtype and supertype entities.

dependency

database designer

determines the format of each data element and the overall database structure. Database designers must balance several priorities, including storage, response time, and support for rules that govern the data.

database programmer

develops computer programs that utilize a database. Database programmers write applications that combine database query languages and general-purpose programming languages.

The first step of the analysis phase is

discovery of entities, relationships, and attributes in interviews and document review.

transaction manager

ensures transactions are properly executed. The transaction manager prevents conflicts between concurrent transactions. The transaction manager also restores the database to a consistent state in the event of a transaction or system failure.

Similar entities are

entities that have many common attributes and relationships. Similar entities become subtypes of a new supertype entity,

The primary key of a subtype table is also a ________.

foreign key Since subtype tables are dependent on the supertype table, the primary key of a subtype is a foreign key that references the supertype table.

database schema.

he logical design, as specified in SQL and depicted in a table diagram,

Database Logical Design Phase

implements database requirements in a specific database system. For relational database systems, logical design converts entities, relationships, and attributes into tables, keys, and columns.

query processor

interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application.

Relational rules, also known as integrity rules, are

logical constraints that ensure data is valid and conforms to business policy.

In entity-relationship modeling, cardinality refers to

maxima and minima of relationships and attributes.

_______ columns contain no descriptive information and make good primary keys.

meaningless

An aggregate function operates on

numeric values from multiple rows, including only rows selected by the WHERE clause. Aggregate functions include: SUM, which returns the total of selected values. AVG, which returns the average of selected values. MAX, which returns the largest selected value. MIN, which returns the smallest selected value.

Business rules are

relational rules specific to a particular database and application. Example business rules include: Unique column values — in a particular column, values may not be repeated. No missing values — in a particular column, all rows must have known values. Delete cascade — when a row is deleted, automatically delete all related rows.

Structural rules are

relational rules that govern data in every relational database. The relational model stipulates a number of structural rules, such as: Unique primary key — all tables have a column, or group of columns, called the primary key. Each primary key value, or group of values, appears in one row only and identifies the row in queries. Unique column names — different columns of the same table have different names. No duplicate rows — no two rows of the same table have identical values in all columns.

A ______ attribute becomes a column that is never NULL.

required

Database Administrator (DBA)

responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability.

An entity-relationship diagram, commonly called an ER diagram, is

schematic picture of entities, relationships, and attributes. Entities are drawn as rectangles. Relationships are drawn as lines connecting rectangles. Attributes appear as additional text within an entity rectangle, under the entity name. *Entities and relationships always appear in ER diagrams. Attributes are optional and only appear when additional detail is needed.

A ______ primary key is easy to specify in a WHERE clause.

simple

Open source software

software that anyone can inspect, copy, and modify with no licensing fee.

database application

software that helps business users interact with database systems.

Database Management System (DBMS)

software that reads and writes data in a database.

Database Analysis Phase

specifies database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes. An entity is a person, place, activity, or thing. A relationship is a link between entities, and an attribute is a descriptive property of an entity.

A _______ primary key reduces cascading updates in the database.

stable

SQL

stands for Structured Query Language and includes statements that read and write data, create and delete tables, and administer the database system.All relational database systems support the SQL query language

A relational database

stores data in tables, columns, and rows, similar to a spreadsheet. All data in a column has the same format. All data in a row represents a single object, such as a person, place, product, or activity.

A subtype entity is

subset of another entity type, called the supertype entity.

The subtype table primary key is identical to the _______ table primary key.

supertype

Relationship maximum is

the greatest number of instances of one entity that can relate to a single instance of another entity. A relationship has two maxima, one for each of the related entities. Maxima are usually specified as one or many, and denoted as 1 or M in ER diagrams.

Relationship minimum is

the least number of instances of one entity that can relate to a single instance of another entity. A relationship has two minima, one for each of the related entities. Minima are usually specified as zero or one. On ER diagrams, minima are shown after maxima in parentheses. Ex: M(1) or M(0).