Section 2

Question 1

A database model is a conceptual framework for database systems, with three parts:

Answer 1

-Data structures that prescribe how data is organized
-Operations that manipulate data structures
-Rules that govern valid data

Question 2

What is a Tuple

Answer 2

It is an ordered collection of elements enclosed in parentheses Ex: (a,b,c) and (c,b,a) are different, since tuples are ordered.

Question 3

A table

Answer 3

Has a name, a fixed tuple of columns, and a varying set of rows

Question 4

A column

Answer 4

Has a name and a data type

Question 5

A row

Answer 5

Is an unnamed tuple of values. Each corresponds to a column and belongs to the column’s data type

Question 6

A data type

Answer 6

Is a named set of values, from which column values are drawn

Question 7

Join

Answer 7

Combines two tables by comparing related columns

Question 8

Union

Answer 8

Selects all rows of two tables

Question 9

Aggregate

Answer 9

Computes functions over multiple table rows, such as sum and count

Question 10

Business rules

Answer 10

Are based on business policy and specific to a particular database

Question 11

SQL

Answer 11

Is the standard language for relational databases, and is commonly supported in non-relational databases

Question 12

Type: Literals
Definition: ?
Examples: ?

Answer 12

Description: 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 0 is any hex value.
Ex: ‘String’, “String”, 123, x’0fa2’

Question 13

Type: Keywords
Description:?
Ex:?

Answer 13

Description: Words with special meaning.
Ex: SELECT, WHERE, FROM

Question 14

Type: Identifiers
Description: ?
Ex: ?

Answer 14

Description: Objects from the database like tables, columns, etc.
Ex: City, Name, Population

Question 15

The SQL language is divided into five sublanguages

Answer 15

-Data definition language(DDL)
-Data Query Language(DQL)
-Data Manipulation Language(DML)
-Data control Language(DCL)
-Data Transaction Language(DTL)

Question 16

Data Definition Language

Answer 16

Defines the structure of the data

Question 17

Data Query Language

Answer 17

Retrieves data from the database

Question 18

Data Manipulation Language

Answer 18

Manipulates data stores in a database

Question 19

Data Control Language

Answer 19

Controls database user access

Question 20

Data Transaction Language

Answer 20

Manages database transactions

Question 21

What is rule 7 of a table?

Answer 21

No row order: Rows are not ordered. the organization of rows on a storage device, such as a disk drive, never affects query results

Question 22

Rule 7 is called?

Answer 22

Data independence, it allows database administrators to improve query performance by changing the organization of data on storage devices, without affecting query results.

Question 23

Example data types

Answer 23

-INT or INTEGER : Integer values
-VARCHAR(N) : values with 0 to N characters
-DATE : date values
-DECIMAL(M,D) : numeric values with M digits, of which D digits follow the decimal point

Question 24

ALTER TABLE statement..

Answer 24

Adds, deletes, or modifies columns on an existing table.

Question 25

A data type is

Answer 25

A named set of values from which column values are drawn

Question 26

Integer data types

Answer 26

Represent positive and negative integers. Several integer data types exist, varying by the number of bytes allocated for each value. Common integer data types include INT, implemented as 4 bytes of storage, and SMALLINT, implemented as 2 bytes.

Question 27

Null

Answer 27

Is a special value that represents either unknown or inapplicable data

Question 28

The update statement

Answer 28

Modifies existing rows in a table.

Uses the SET clause to specify the new column values.

Question 29

The DELETE statment

Answer 29

Deletes existing rows in a table.

The FROM keyword is followed by the table name whose rows are to be deleted.

Question 30

The TRUNICATE statement

Answer 30

deletes all rows from a table.
It is nearly identical to a DELETE statment with no WHERE clause except for minor differences that depend on the database system.

Question 31

The MERGE statement

Answer 31

Selects data from one table, called the source, and inserts the data to another table, called the target.

Question 32

A primary key

Answer 32

Is a column, or group of columns, used to identify a row. The —- is usually the table’s first column.

Question 33

A single primary key

A composite primary key

Answer 33

Consists of a single-column

Consists of multiple columns

Question 34

An auto-increment column

Answer 34

is a numeric column that is assigned an automatically incrementing value when a new row is inserted.

Question 35

Database users occasionally make the following errors when inserting primary keys

Answer 35

-Inserting values for auto-incrementing primary keys
-Omitting values for primary keys that are not auto-increment columns

Question 36

A foreign key

Answer 36

Is a column, or group of columns, that refers to a primary key.
When a foreign key constraint is specified, the database rejects insert, update, and delete statements that violate referential integrity.

Question 37

RESTRICT

Answer 37

rejects an insert, update, or delete that violates referential integrity

Question 38

CASCADE

Answer 38

Propagates primary ley changes to foreign keys.

Question 39

A constraint

Answer 39

Is a rule that governs allowable values in a database. —- are based on relational and business rules.

Question 40

The UNIQUE constraint

Answer 40

Ensures that values in a column, or group of columns are unique

Question 41

The CHECK constraint

Answer 41

Specifies an expression on one or more columns of a table

Question 42

Adding and dropping contraints

Answer 42

Constraints are added and dropped with the ALTER TABLE TableName followed by an ADD, DROP, or CHANGE clause.