Database Management I- Midterm

Table governing rules

1. Exactly one value per cell. 2. No duplicate column names. 3. No duplicate rows. 4. No row order.

The INSERT statement adds a student to the Student table. How many clauses are in the INSERT statement? INSERT INTO Student VALUES (888, 'Smith', 'Jim', 3.0);

2

What is Braden Smith's balance in the following INSERT statement? INSERT INTO Account VALUES (800, 'Braden Smith', 200);

200

Column characteristics

A column has a name and a data type.

Data type

A data type is a named set of values, from which column values are drawn.

Define a key.

A key is a column used to identify individual rows of a table.

MySQL

A leading relational database system sponsored by Oracle.

SHOW TABLES

A list of all tables in the default database

Row characteristics

A row is an unnamed tuple of values. Each value corresponds to a column and belongs to the column's data type.

What is the result of a relational operation?

A table

Table characteristics

A table has a name, a fixed tuple of columns, and a varying set of rows.

What is wrong with the SQL statement below? SELECT FirstName from Student

A terminating semicolon is missing.

Match the SQL sublanguage to the statement behavior: A. Insert a data row into table Product B. rollback database changes C. select all rows from table Product D. grant all permissions to user 'tester' E. Create table Product

A. DML B. DTL C. DQL D. DCL E. DDL

Define the following relational operations: A. Select B. Project C. product D. join E. Union

A. selects a subset of rows of a table B. eliminates one of more columns of a table C. lists all combinations of rows of two tables. D. combines two tables by comparing related columns. E. selects all rows of two tables

Define the following relational operations: A. Intersect B. Difference C. Rename D. Aggregate

A. selects rows common to two tables B. selects rows that appear in one table but not in the other C. changes a table name D. computes functions over multiple table rows, such as sum and count

Referring to the animation above, how is the "schedules" relationship read?

Airline-Schedules-Flight

Specification of database requirements without regard to implementation

Analysis

How can driver's license information be added to the Employee table?

By creating another column with a new name, such as ID2 or DriverLicense

Storing a student's assigned letter grade, like A or D.

CHAR(1)

Storing the date and time an item is purchased.

DATETIME

Storing the price of an item that ranges from a few dollars to a few hundred dollars.

DECIMAL(5, 2)

In the relational data structure, which components are named?

Data type, table, column

The overall process of determining and implementing database requirements.

Database design

Data Definition Language (DDL)

Defines the structure of the database.

A streaming video service's database is disabled due to a lightning strike. The IT department verifies that all database transactions performed before the lightning strike were saved on storage media.

Ensure transaction results are never lost.

Whose balance does the following UPDATE statement change? UPDATE Account SET Balance = 850 WHERE ID = 290;

Ethan Carr

___ may appear in each cell.

Exactly one value

Literals

Explicit values that are string, numeric, or binary.Strings must be surrounded by single quotes or double quotes.Binary values are represented with x'0' where the 0 is any hex value.

Adding a FOREIGN KEY constraint to a table only affects inserting new rows into the table.

False

All database systems use identical SQL statements.

False

An SQL statement can implement only one relational operation.

False

Every database query must be logged by the transaction manager to recover the database in the event of a system failure.

False

Only the ID column stores numbers.

False

SQL is a general-purpose programming language.

False

The CHAR data type represents a variable string of characters.

False

The MySQL Command-Line Client provides a graphical interface for interacting with the database server.

False

The database server must be manually started each time the user runs the MySQL Command-Line Client.

False

The insert, select, update, and delete queries are the only types of commands necessary to interact with a database system.

False

The statement CREATE DATABASE university; creates a second university database.

False

The statement DROP DATABASE nonprofit; deletes the nonprofit database.

False

Where are duplicate column names allowed?

In different tables

Where would a bank customer's account data be directly stored?

In the bank database

What indicates relationships?

Lines between rectangles.

Implementation of database requirements as tables, keys, and columns in a specific database system.

Logical design.

Which name is retrieved by the following SELECT statement? SELECT Name FROM Account WHERE Balance < 3000;

Mai Shiraishi

Which database is relational?

MySQL

Match the value to the data type: Nadia, 16, 3.14, 09/12/1986, 00011011001 VARCHAR, INT, DATE, BLOB, FLOAT

Nadia - VARCHAR 3.14 - FLOAT 16 - INT 09/12/1986 - DATE 00011011001 - BLOB

What is included in a glossary?

Names, synonyms, and descriptions only

Who is deleted by the following DELETE statement? DELETE Account WHERE ID = 999;

No one

Identifiers

Objects from the database like tables, columns, etc.

Affects query performance but not query results.

Physical design

A program is adding a penalty fee to an account that is below $1000 while another program is adding $2000 to the same account.

Prevent conflicts between concurrent transactions.

If program A writes to bookkeeping.txt the same time that program B reads from bookkeeping.txt, what can potentially go wrong?

Program B may be reading only partial data.

Define the following: RESTRICT SET NULL SET DEFAULT CASCADE

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 the foreign key default value. CASCADE- propagates primary key changes to foreign keys.

Logical design is often depicted in a table diagram. What represents the following: Tables Columns Key columns Columns that refer to keys

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.

In ER diagrams, what indicates an entity?

Rectangles. Entity names appear at the top of rectangles.

data query language (DQL)

Retrieves data from the database

Which statement shows all the columns in the Country table? SHOW COLUMNS; SHOW COLUMNS Country; SHOW COLUMNS FROM Country;

SHOW COLUMNS FROM Country

Which statement shows all databases in a database system?

SHOW DATABASES

Which statement shows all tables in the database petStore?

SHOW TABLES

Name three relational operations.

Select, project, and union

Comments

Statement intended only for humans and ignored by the database when parsing an SQL statement.

Storing a yes or no value.

TINYINT

What indicates attributes?

Text inside rectangles and below entity names represent attributes.

What is wrong with the transaction posted on 13/31 for the amount +6z@yy?

The data in the transaction contains invalid pieces of data.

What is wrong with the SQL statement below? SELECT Gpa --FROM Student;

The from clause is a comment.

The SQL statement below is used to select students with the last name "Smith". What is wrong with the statement? SELECT FirstName FROM Student WHERE LastName = Smith;

The literal "Smith" must be surrounded by single or double quotes.

Logical Design

The 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.

What does the principle of data independence state?

The result of database query is not affected by the physical organization of data on storage devices.

Why was an unauthorized user permitted to perform the transaction that added $999999 to bookkeeping.txt?

The user had write access to the text file.

How would a bank customer access their bank data?

Through the customer banking application

Maria purchases an airline ticket, but a server failure causes the ticket to become unavailable before the transaction completes. The database must reverse any partial changes.

Transaction processed completely or not at all..

The relational model was originally developed for which types of applications?

Transactional applications like banking and airline reservations.

A binary data type stores data as an exact copy of computer memory.

True

A database designer and database user both use SQL.

True

An update query cannot update data that isn't in the database.

True

Assume the database system currently contains only one user database, called 'university', in addition to system databases such as 'mySQL'. The statement CREATE DATABASE auto; creates a database called 'auto'.

True

Data in a relational database can violate relational rules

True

Each host language, such as Java or C++, has a different API.

True

Given the data in the Bank database, a select query for accounts with negative balances would return nothing.

True

In a FOREIGN KEY constraint, data types of the foreign key and primary key columns must be the same.

True

In a FOREIGN KEY constraint, parentheses are required around the foreign key column name.

True

MySQL Workbench and MySQL Command-Line Client both allow the user to type SQL statements.

True

SQL commands can create databases and tables.

True

Some date and time data types include time zone.

True

The BirthDate column stores only a date and no time.

True

The Employee table is an empty table once created.

True

The Employee table is created with 4 different data types.

True

The MySQL Workbench screenshot above shows the columns that make up the City table.

True

The SQL statements in the SQL query panel are not executed until the lightning bolt is clicked.

True

The root account password is set when installing MySQL.

True

The statement DROP DATABASE university; deletes the university database and all associated tables.

True

Unique primary key is an example of a relational rule.

True

Which statement must precede a SHOW TABLES statement to see the tables from the bikeStore database?

USE bikeStore

Storing a student's email address.

VARCHAR(100)

Are these tables the same? { (8, mango, FALSE), (-11, watermelon, FALSE) } { (-11, watermelon, FALSE), (8, mango, FALSE) }

Yes

Can a query select one specific row from a table?

Yes, by specifying one or more row values

Primary key

a column or group of columns that identifies a unique row in a table. designated with a solid circle. must be unique and not null.

Relational Model

a database model based on a tabular data structure.

Application Programming Interface (API)

a library of procedures or classes that links a host programming language to a database

data type

a named set of values from which values are drawn.

Cell

a single column of a single row

Database system instance

a single executing copy of a database system.

A schema is depicted in

a table diagram

MySQL Command-Line Client

a text interface included in the MySQL Server download. Allows developers to connect to the database server, perform administrative functions, and execute SQL statements.

Unique primary key

all tables should have a column with no repeated values, called the primary key and used to identify individual rows.

glossary

also known as a data dictionary or repository, documents additional detail in text format. A glossary includes names, synonyms, and descriptions of entities, relationships, and attributes.

Which of the following manages the Bank database? the customer banking application the banking database system the computer the banking database system resides on

banking database system

Which of the following would prevent unauthorized access to the Bank database? the banking database system bank database customer banking application

banking database system

An entity-relationship model is completely described by

both an ER diagram and glossary

foreign key

column or group of columns that refer to a primary key. data type of primary and foreign keys must be the same. Designated with an empty circle. may be repeated and may be null.

Database model

conceptual framework for database systems with three parts: data structures, operations, and rules

simple primary key

consists of a single column

Composite primary key

consists of multiple columns

Data Control Language (DCL)

controls database user access.

Which role is responsible for providing access to the database?

database administrator

Which role is responsible for defining the detailed database design?

database designer

Which role uses an application to query a database and generate a report?

database users

Logical design is

either a process or a specification

An entity-relationship model is developed for all database design projects.

false

Analysis considers implementation issues related to a specific database system.

false

Delete cascade is an example of a relational rule

false

Entities, relationships, and attributes always map directly to tables, foreign keys, and columns, respectively.

false

In a CREATE TABLE statement, the FOREIGN KEY constraint must follow all column declarations.

false

The catalog allows the storage manager to quickly locate the requested data.

false

The query processor has direct access to the database data on storage media.

false

Without query optimization, the storage manager cannot retrieve the database data.

false

Match the definition to the correct SQL statement or command: inserts rows into a table retrieves data from a table modifies data in a table deletes rows from a table

insert select update delete

primary difference between an instance and a type?

instance is usually individual. type includes a set

SHOW COLUMNS FROM TableName

lists all columns in the TableName table of the default database

SHOW DATABASES

lists all databases available in the database system instance

Data Transaction Language (DTL)

manages database transactions

Data Manipulation Language (DML)

manipulates data stored in a database

Relational rules

part of the relational model and govern data in every relational database

What was the initial impediment to commercial adoption of relational databases in the early 1980s?

processing speed

cardinality

refers to maxima and minima of relationships and attributes.

Referential integrity

relational rule that requires foreign key values are either fully null or match some primary key value.

Which terms are commonly used in database processing?

row, table, column

Constraint

rule that governs allowable values in a database

SHOW CREATE TABLE TableName

shows the CREATE TABLE statement for the TableName table of the default database.

Storing the annual gain or loss in a city's population, which ranges from -1 million to 1 million.

signed MEDIUMINT

Relationship maximum

the greatest number of instances of one entity that can relate to a single instance of another entity.

Only one of the queries does not change the database contents.

true

Storing a city's population, which ranges from a dozen to 24 million people.

unsigned INTEGER

Keywords

words with special meaning