Database Concepts and SQL

Flashcards based on lecture notes to help students review and prepare for exams.

What increases a user's knowledge through processed data?

Information

What are facts concerning objects and events recorded on computer media called?

Data

What is established between entities in a structured database to retrieve information?

Relationships

What type of attribute can be broken down into smaller parts?

Composite

An attribute or a combination of attributes that uniquely identifies each row in a relation.

Primary key

What is a generic entity type with relationships with one or more subtypes?

Supertype

What are some properties of relations?

Each attribute has a unique name, no two rows are identical, and there are no multivalued attributes.

What is an operation to join a table to itself called?

Self-join

What rule states that each foreign key value must match a primary key value?

Referential integrity constraint

When all multivalued attributes are removed, what normal form is it in?

First normal form

Which command may reverse any create command?

Drop

What is a reusable template data model called?

Pre-packaged data model

In a supertype/subtype hierarchy, how many supertypes does each subtype have?

Only one supertype

Which SQL command adds new columns to a table?

Alter table

What is the wildcard operator in SQL statements?

%

What is a single value returned from an SQL query with an aggregate function called?

Scalar aggregate

What is a join operation?

Causes two tables with a common domain to be combined into a single table or view.

What is an entity that associates instances of entity types and contains specific attributes?

Associative entity

What is a rule that cannot be violated by users?

Constraint

What rule specifies that each supertype instance must be a member of some subtype?

Total specialization

To eliminate duplicate rows in a query, the ________ qualifier is
used in the SQL

Distinct

What does a curly brace mean in sql?

a multivalued attribute.

Refer to the EMPLOYEE information, which attribute is multivalued?

Skill

For employee and deparment, which is true?

A department can have more than one employee.

What is true about the CARE CENTER relationship?

NurseInCharge is a foreign key to NurseID in NURSE entity

To what entities are CAR and TRUCK generalized?

Vehicle

What form of normalization are we in?

first normal form

In the figure below, the primary key for "Order Line" is which type of key?

Composite

In the PART figure, what is the subtype discriminator?

Part_Type.

What does the following SQL statement do?

“Delete from Customer_T where state = 'HI';”

Deletes all records from Customer_T where the state is equal to HI

What action does the SQL statement do?

SELECT * FROM EMPLOYEE

Lists all of the information from the Employee table

Given a table named store with 5 fields:

store_id, address, city, state, zipcode,

why would the following insert command not work?:
insert into store values ('234 Park Street')

You must specify the fields to insert if you are only inserting some of the fields

What does the following SQL statement do?

“Delete from Customer_T where state = 'HI';”

Deletes all records from Customer_T where the state is equal to HI

What results will be produced by the following SQL query?

Select sum(standardprice) as TotalPrice from ProductV where ProductType = 'WOOD';

The total price of all products that are of type wood

A way to represent possible values for relationships constraints

Cardinality Constraints

Associative, Simple, Composite, and Complex are all types of?

Attributes

Column, Foreign field, Primary key, and Duplicate key refers to?

Keys

Megatype, Supertype, Subgroup, Class refers to?

Entity type

Sufficient-join, Inner join, Outer join, Self-join are types of?

Joins

Truncate, drop, delete and unpack are types of?

Commands

Create table, alter table, create view and create relationship are ?

SQL commands

The following figure shows an example of:

composite attribute

In the figure shown below, which of the following is true?

A person can marry at most one person.

The figure below is a

Schema Layout Diagram

What does the following SQL statement do?
Alter Table Customer_T
Add (Type Varchar (2));
-Alters the Customer_T table to accept Type 2 Varchars
- Alters the Customer_T table to be a Type 2 Varchar
- Alters the Customer_T table, and adds a field called "Type"
- Alters the Customer_T table by adding a 2-byte field called
"Varchar"

Alters the Customer_T table, and adds a field called "Type"

What does the following SQL statement do?
SELECT * FROM EMPLOYEE;

List all of the information from the Employee table

Given a table named store with 5 fields:
store_id, address, city, state, zipcode,
why would the following insert command not work?
insert into store values ('234 Park Street')
• It would work just fine.
• You must specify the fields to insert if you are only inserting some of the
fields.
• There is no table keyword.
• None of the above.

You must specify the fields to insert if you are only inserting some of the fields.

What does the following SQL statement do?
Delete from Customer_T where state = 'HI';
• Deletes all records from customer_t where the state is equal to HI
• Removes the Customer_T table from the database
• Deletes all records from the Customer_T table
• Deletes no record from the Customer_T table

Deletes all records from Customer_T where the state is equal to HI

the following figure is an example of

total specialization

What result will the following SQL statement produce?

Select Avg(standard_price) as average from Product_V;

• The average of all products in Product_V
• The average Standard_Price of all products in Product_V
• The average price of all products
• The sum of all of the standard_price for Product_V

The average Standard_Price of all products in Product_V

What results will be produced by the following SQL query?

Select sum(standard_price) as Total_Price
from Product_V
where Product_Type = 'WOOD';

• The total price of all products that are of type wood
• The total price of all products
• The Standard_Price of the first wood product in the table
• The Standard_Price of any wood product in the table

The total price of all products that are of type wood

A simultaneous relationship among the instances of
three entity types is called a(n) ________
relationship

ternary

The property by which subtype entities possess the values of all attributes of a

Attribute inheritance

A person, place, an object , an event or concept about which the organization wishes to maintain data is called a(n):

Entity

In an E-R diagram, there are ________ business
rule(s) for every relationship

Two

Customers, cars, and parts are examples of:

entities

A characteristic of an entity type that is of interest to the organization is called a(n):

Attribute

The ________ rule specifies that an entity can be a
member of only one subtype at a time, not both subtypes.

disjoint

A generic or template data model that can be reused
as a starting point for a data modeling project is

Universal data model

The process of defining a more general entity type
from a set of more specialized entity types is called

generalization

A relationship where the minimum and maximum
cardinality are both one is a(n) ________ relationship.

Mandatory one

The SDLC phase in which every data attribute is
defined, every category of data is listed and every
business relationship between data entities is defined
is called the ________ phase.

Design

The process of defining one or more subtypes of the
supertype and forming supertype/subtype relationships.

Specialization

An attribute in a relation that serves as the primary key of
another relation in the same database.

Foreign Key

A rule that specifies the number of instances of one entity
that can (or must) be associated with each instance of
another entity.

Cardinality Constrain

Each employee can supervise one employee, no employees or many employees

The ________ rule specifies that an entity instance of a
supertype that doesn't have to belong to any subtype.

partial-specialization

A rental unit must be either an apartment or a house, and could be both.

A database is an organized collection of ________ related
data.

ogically

Name an example of a composite attribute from this ER diagram

Customer Address (has multiple attributes in it)

This is an example of what type of schema?

Unary (involves one entity type)

A serial number or other system-assigned primary key for a relation 

Surrogate primary key

What is normalization?

The process of decomposing relations with anomalies to produce smaller, well-structured relations 

Which of the following is an integrated decision support database with content derived from various operational databases? 

Data warehouse (an integrated decision support system designed for analysis and reporting)

A database is an organized collection of ________ related data

logically

What business rule is shown here

Customers place zero or more Orders

The figure is an example of __

total specialization (two lines)

What is the users front end that accesses a database system.

Database Application

What is the next thing that we build in the Database development process after the Business Rules are established?

Entity relationship tabel modle

Two (or more) attributes that have different names but the same meaning 

synonyms

 An attribute that must have a value for every entity (or relationship)

Required attribute

An error or inconsistency that may result when a user attempts to update a table that contains redundant data.

Anomoly

A relation in first normal form in which every non-key attribute is fully functionally dependent on the primary key 

second normal form

The maximum number of instances of one entity that may be associated with each instance of another entity 

Maximum Cardinality