Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Database Design > Quiz 2 > Flashcards

Quiz 2 Flashcards

1
Q

Physical Modeling Purpose

A

Translating the logical description of data into the technical specifications for storing and retrieving data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Physical Modeling Goal

A

Creating a design for storing data that will provide adequate performance and insuring database integrity, security and recoverability

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Field

A

Smallest unit of data in a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Field design

A

choose data type; coding, compression, and encryption; controlling data integrity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Four objectives when choosing data types

A

represent all possible values, improve data integrity, support all data manipulations, minimize storage space

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Numeric data types

A

INT, SMALLINT, DECIMAL, NUMERIC(precision,scale)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Approximate numeric

A

FLOAT, REAL, DOUBLE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Data and Date time

A

DATE, TIME, DATETIME, TIMESTAMP, YEAR

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

String data

A

CHAR, VARCHAR(length), BINARY, VARBINARY, BLOB, TEXT, ENUM, SET

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Integrity controls

A

data type, range control, default values, null value control, referential integrity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Range Control

A

data integrity constraint which indicates the upper and lower bounds for the field.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Default values

A

where one specifies the value that the field will take if no data has been entered.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Null value control

A

allows one to specify whether null values are allowed or not in the field.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Referential integrity

A

ensures that any value entered into a foreign key must have a corresponding value in the primary key of the related table. This ensures that there are no misplaced values in the foreign key.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Denormalization

A

Transforming normalizedrelations into unnormalizedphysical record specifications

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Benefits of denormalization

A

Can improve performance (speed) by reducing the number of table lookups (i.e., reduce number of necessary join queries)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Costs of denormalization

A

wasted storage space, data integrity/consistency threats

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Situations where we would denormalize

A

two entities with a one-to-one relationship and high access frequency between them;

associative entity with nonkey attributes. If there is a high frequency of join operations between the entities, it might be more efficient to combine the associative entity with one of the other entities;
reference data. If we have a 1:M relationship and the entity on
the one side does not participate in any other relationships

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Horizontal partitioning

A

Distributing the rows of a table into several separate files; Useful for situations where different users need access to different row

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Vertical partitioning

A

Distributing the columns of a table into several separate files; Useful for situations where different users need access to different columns; The primary key must be repeated in each file

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Advantages of partitioning

A

Efficiency, local optimization, security, recovery and uptime, load balancing

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Disadvantages of partitioning

A

Inconsistent access speed, additional complexity for the programmer, extra space or update time

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Many points about data replication

A

Improves performance by allowing multiple users to access the same data at the same time with minimum contention
sacrifices data integrity due to data duplication
best for data that is not updated often

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

sequential file organization

A

Records are stored sequentially according to a primary key value

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Indexed file organization

A

Records are stored sequentially or nonsequentially, and an index is created that allows the application software to locate individual records.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Hashed file organization

A

In a hashed file organization, the address of each record is determined using a hashing algorithm.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

SQL

A

Structured Query Language, developed by IBM

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

DDL

A

Data definition language. Commands that define a database, including creating, altering, and dropping tables and establishing constraints

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

DML

A

Data Manipulation Language. Commands that maintain and query a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

DCL

A

Data Control Language. Commands that control a database, including administering privileges and committing data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

DDL Commands

A

Create, Alter, Drop, Rename

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

DML Commands

A

Select, Update, Insert, Delete

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

DCL Commands

A

Grant, Revoke

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

DDL is used for

A

physical design and maintenance

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

DML is used for

A

implementation and maintenance

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

DCL is used for

A

implementation and maintenance

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

T/F SQL statements are case sensitive

A

FALSE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

T/F SQL statements can be on more than one line

A

TRUE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

Each SQL statement ends in a

A

semicolon

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

T/F keywords cannot be split across lines

A

TRUE

41
Q

Table commands and SQL type

A

DDL, create drop alter

42
Q

Index, View, and Schema commands and SQL type

A

DDL, create and drop

43
Q

Write a Table Syntax

A

CREATE TABLE table_name (
column1 datatype [NOT NULL] [UNIQUE] [DEFAULT default-value], columns 2 datatype [NOT NULL] [UNIQUE],
PRIMARY KEY (column_name),
FOREIGN KEY (column_name) REFERENCES table_name(column_name)
);

44
Q

How would you do a range check?

A

At the end of the statement CHECK (AGE >0 and AGE <150)

45
Q

What are all of the alter funtions

A

add a new column, drop an existing column, rename a column, set a default for an existing column, change starting value of auto increment

46
Q

Define a view

A

A virtual table based on the result-set of an SQL statement. Instead of sending the complex query to the database all the time, you can save the query as a view and then use SELECT * FROM view_name to get its rows

47
Q

Write a view statement

A

CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

48
Q

Add a new column

A

ALTER TABLE table_name
ADD column_name datatype;

49
Q

Drop a column

A

ALTER TABLE table_name
DROP COLUMN column_name, DROP COLUMN column_name;

50
Q

Rename a column

A

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

51
Q

Set a default value for a column

A

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default-value;

52
Q

Change the starting value of auto-increment

A

ALTER TABLE table_name AUTO_INCREMENT=5;

53
Q

Insert a record/row into a table

A

INSERT INTO tb_Student (StudentName, StudentAddress, Age)
VALUES
(‘Jerry’, ‘UC Dorm’, 18),
(‘Alice’, ‘UC Dorm’, 20);

54
Q

Update data in a table

A

UPDATE tb_Student
SET SeniorMentorID = 1
WHERE StudentName = ‘Jack’;

55
Q

Delete data based on a condition

A

DELETE FROM tb_Student
WHERE StudentName = ‘Alice’;

56
Q

Select statement order

A

FROM, WHERE, GROUP BY, HAVING, ORDER BY

57
Q

SELECT definition

A

identifies what columns

58
Q

FROM definition

A

identifies what table

59
Q

WHERE definition

A

imposes constraints for which rows show up

60
Q

ORDER BY definition

A

sort results rows in ascending (default) or descending (desc) order

61
Q

Write a simple select statement example

A

SELECT CustomerName, Phone
FROM CUSTOMER
WHERE CITY = ‘Atlanta’ AND STATE = ‘GA’
ORDER BY CustomerName;

62
Q

=

A

equal to

63
Q

<

A

less than

64
Q

<=

A

less than or equal to

65
Q

>

A

greater than

66
Q

> =

A

greater than or equal to

67
Q

<>

A

not equal to

68
Q

!=

A

not equal to

69
Q

List the logical operators

A

NOT, AND, OR

70
Q

List the processing order of logical operators

A

NOT, AND, OR

71
Q

list the special operators

A

between, is null, like, in, exists

72
Q

Write an IN statement

A

SELECT PID, Name, State
FROM NBAPlayer
WHERE State IN (‘TX’, ‘GA’, ‘VA’);
Is equivalent to multiple OR statements

73
Q

write a not in statement

A

SELECT PID, Name, State
FROM NBAPlayer
WHERE State NOT IN (‘TX’, ‘GA’, ‘VA’);
equivalent to multiple != and statements

74
Q

write a between statement

A

SELECT PID, Name, State
FROM NBAPlayer
Where PID BETWEEN 3 and 5;
equivalent to Where PID >=3 and PID<=5;

75
Q

IS NULL

A

list of records with missing values

76
Q

IS NOT NULL

A

list of records with no missing values

77
Q

LIKE

A

records matching a string pattern

78
Q

like statement for beginning with a letter

A

LIKE ‘A%’

79
Q

like statement for ending with a letter

A

LIKE ‘%A’

80
Q

LIKE statement for containing a letter

A

LIKE ‘%A%’

81
Q

like statement for record containing one letter before the A

A

LIKE ‘_A’)

82
Q

List the aggregate functions

A

count, sum, avg, max, min

83
Q

COUNT

A

the number of rows containing the specified column (attribute)

84
Q

SUM

A

the sum of all values for a selected column

85
Q

AVG

A

the arithmetic mean (average) for the specified column (attribute)

86
Q

MAX

A

the largest value in the column

87
Q

MIN

A

the smallest value in the column

88
Q

What is the average age and maximum age of students

A

SELECT AVG(Age) AS AverageAge, MAX(Age) AS MaxAge
FROM tb_Student;

89
Q

How many of the students live in UC Dorm

A

SELECT COUNT(*)
FROM tb_Student
WHERE StudentAddress = ‘UC Dorm’

90
Q

Show each student’s name and age difference from the average

A

SELECT
StudentName,
Age - (SELECT AVG(Age) FROM tb_Student) AS AgeDiff
FROM tb_Student;

91
Q

DISTINCT

A

designed to produce a list of only those values that are different from one another

92
Q

List distinct student addresses

A

SELECT DISTINCT StudentAddress
FROM tb_Student;

93
Q

T/F SQL does not allow the use of DISTINCT with COUNT(*) together

A

TRUE

94
Q

GROUP BY

A

There are circumstances where we would like to apply the aggregate
function not only to a single set of rows, but also to a group sets of rows.

95
Q

GROUP BY example

A

SELECT State, AVG(PerHour)
FROM NBAPlayer
GROUP BY State;

96
Q

HAVING definition

A

like where but used with group by

97
Q

HAVING example

A

SELECT City, State, Avg(PerHour)
FROM NBAPlayer
GROUP BY City, State
HAVING COUNT( * ) >= 3;

98
Q

Example of a full select with where, group by, and having

A

SELECT City, COUNT()
FROM NBAPlayer
WHERE State = ‘TX’
GROUP BY City
HAVING COUNT() >= 3;

Database Design (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions