Prelims

Question 1

Enumeration

The purpose of a database

Answer 1

To store data
To provide an organizational structure for data
To provide a mechanism for querying, creating, modifying, and deleting data

Question 2

Enumeration

Main problems with list

Answer 2

1. Redundancy (the same information may be entered several times)
2. Multiple themes (each row may contain more than one business concept)

Question 3

Enumeration

Redundancy and Multiple themes create modification problems:

Answer 3

Deletion problems
Update problems
Insertion problems

Question 4

Identification

is an international standard language for creating, processing and querying databases and their tables

Answer 4

Structured Query Language (SQL)

Question 5

Identification

This ensures that the values of a column in one table are valid based on the values in another table

Answer 5

Referential Integrity constraints

Question 6

Identification

is one or more columns of a relation whose values are used to identify a row

Answer 6

Key

Question 7

Identification

Composed of two or more attributes

Answer 7

Composite Key

Question 8

Identification

It has the potential to become the primary key

Answer 8

Candidate key

Question 9

Identification

A candidate key chosen to be the main for the relation

Answer 9

Primary key

Question 10

Identification

A unique, numeric value, that is added to a relation to serve as the primary key

Answer 10

Surrogate key

Question 11

Identification

A primary key from one table that is placed into another table

Answer 11

Foreign key

Question 12

Identification

states that every value of a foreign key must match the value of an existing primary key

Answer 12

Referential integrity

Question 13

Identification

A relationship between attributes in which one attribute (or a group of attributes) determines the value of another attribute in the same table

Answer 13

Functional Dependency

Question 14

Identification

The attribute (or attributes) that we use as the starting point (the varibale on the left side of the equation) is called a

Answer 14

Determinant

Question 15

Identification

• A process of analyzing a relation to ensure that it is well-formed
• Involves decomposing relations with anomalies to produce smaller, well-structured relations
• When we apply this to a relation, rows can be inserted, deleted, or modified without creating anomalies

Answer 15

Normalization

Question 16

Define

1NF

Answer 16

Remove multivalued attributes

Question 17

Define

2NF

Answer 17

Remove partial dependencies

Question 18

Define

3NF

Answer 18

Remove transitive dependencies

Question 19

Identification

Candidate key
Composite key
Primary key
Surrogate key

Answer 19

Unique keys

Dan’s Topology of Db keys

Question 20

Foreign key

Answer 20

Non-unique

Question 21

Enumeration

Unique keys

Answer 21

Candidate key
Composite key
Primary key
Surrogate key

Question 22

Non-unique key

Answer 22

Foreign key

Question 23

Enumeration

SQL is comprised of

Answer 23

DDL
DML
DCL

Question 24

Enumeration

DDL
DML
DCL

Answer 24

Data Definition Language
Data Manipulation Language
Data Control Language

Question 25

# Identification Used to define and manage data structures

Answer 25

Data Definition Language

Question 26

# Identification * Data definition and updating * Data retrieval

Answer 26

Data Manipulation Language

Question 27

# Identification For creating user accounts, managing permissions etc.

Answer 27

Data Control Language

Question 28

# Identification used to specify that when a row is deleted from the parent table, all rows in the child table that reference the deleted row should also be deleted

Answer 28

ON DELETE CASCADE

Question 29

# Identification used to specify that when a row is updated from the parent table, all rows in the child table that reference the updated row should also be updated

Answer 29

ON UPDATE CASCADE

Question 30

# Which normalization phase is being described? every non-key attribute is fully functionally dependent on the entire primary key not just part of it

Answer 30

2NF

Question 31

# Which normalization phase is being described? Every value is atomic

Answer 31

1NF

Question 32

# Which normalization phase is being described? no transitive dependencies ## Footnote transitive dependencies = functional dependencies on non-primary-key attributes

Answer 32

3NF

Question 33

# Identification is a type of dependency that occurs when a non-key attribute depends on another non-key attribute

Answer 33

Transitive Dependency

Question 34

# Identification a type of dependency that occurs when a non-key attribute depends on only one part of a composite primary key

Answer 34

Partial Dependency

Question 35

# Identification * are used to specify rules for the data in a table * are used to limit the type of data that can go into a table

Answer 35

Constraints

Question 36

# Identification this constraint specifies that the constrained columns' values must uniquely identify each row

Answer 36

Primary Key (Pk) constraint

Question 37

# Identification this constraint ensures that only valid data can be inserted into the related column, as the value must exist in the referenced table

Answer 37

Foreign Key (Fk) constraint

Question 38

# Sql Syntax Creating Table with PK constraints

Answer 38

``` CREATE TABLE Employee ( empID Integer NOT NULL, empName Char(25) NOT NULL, CONSTRAINT PRIMARY KEY (empID) ); ```

Question 39

# Sql Syntax Creating Table with PK and FK constraints

Answer 39

`CREATE TABLE` EmployeeSkill ( empID `Integer` NOT NULL, skillId `Integer` NOT NULL, skilllevel `Integer` NULL, `CONSTRAINT PRIMARY KEY` (empId, skillId), `CONSTRAINT FOREIGN KEY` (empID) `REFERENCES` employee(empID), `CONSTRAINT FOREIGN KEY` (skillId) `REFERENCES` Skill (skillid) );

Question 40

# Sql Syntax Create Table with update and delete cascades

Answer 40

`CREATE TABLE` Employeeskill ( empID `integer` NOT NULL, skillId `integer` NOT NULL, skilllevel `integer` NULL, `CONSTRAINT PRIMARY KEY` (empId, skillId), `CONSTRAINT FOREIGN KEY` (empId) `REFERENCES` employee (empId) `ON DELETE CASCADE`, `CONSTRAINT FOREIGN KEY` (skillId) `REFERENCES` Skill (skillId) `ON UPDATE CASCADE` );

Question 41

# Identification the same information may entered several times

Answer 41

Redundancy

Question 42

# Identification each row may contain more than one business concept

Answer 42

Multiple themes

Question 43

# Identification stores information in tables Each business concept is stored in its own table

Answer 43

Relational Database

Question 44

# Uniqueness of keys Data value is unique for each row Consequently, the key will uniquely identify a row

Answer 44

Unique Key

Question 45

# Uniqueness of keys Data value may be shared among several rows Consequently, they key will identify a set of rows

Answer 45

Non-unique key

Question 46

# Identification adds, deletes, modifies, renames the attributes of the relation

Answer 46

ALTER

Question 47

# Identification modifies the values of the records in the relations

Answer 47

UPDATE