Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Database Design > Quiz 2 > Flashcards

Quiz 2 Flashcards

(98 cards)

Start Studying
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
Indexed file organization
Records are stored sequentially or nonsequentially, and an index is created that allows the application software to locate individual records.
26
Hashed file organization
In a hashed file organization, the address of each record is determined using a hashing algorithm.
27
SQL
Structured Query Language, developed by IBM
28
DDL
Data definition language. Commands that define a database, including creating, altering, and dropping tables and establishing constraints
29
DML
Data Manipulation Language. Commands that maintain and query a database
30
DCL
Data Control Language. Commands that control a database, including administering privileges and committing data
31
DDL Commands
Create, Alter, Drop, Rename
32
DML Commands
Select, Update, Insert, Delete
33
DCL Commands
Grant, Revoke
34
DDL is used for
physical design and maintenance
35
DML is used for
implementation and maintenance
36
DCL is used for
implementation and maintenance
37
T/F SQL statements are case sensitive
FALSE
38
T/F SQL statements can be on more than one line
TRUE
39
Each SQL statement ends in a
semicolon
40
T/F keywords cannot be split across lines
TRUE
41
Table commands and SQL type
DDL, create drop alter
42
Index, View, and Schema commands and SQL type
DDL, create and drop
43
Write a Table Syntax
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
How would you do a range check?
At the end of the statement CHECK (AGE >0 and AGE <150)
45
What are all of the alter funtions
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
Define a view
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
Write a view statement
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
48
Add a new column
ALTER TABLE table_name ADD column_name datatype;
49
Drop a column
ALTER TABLE table_name DROP COLUMN column_name, DROP COLUMN column_name;
50
Rename a column
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
51
Set a default value for a column
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default-value;
52
Change the starting value of auto-increment
ALTER TABLE table_name AUTO_INCREMENT=5;
53
Insert a record/row into a table
INSERT INTO tb_Student (StudentName, StudentAddress, Age) VALUES ('Jerry', 'UC Dorm', 18), (‘Alice’, ‘UC Dorm', 20);
54
Update data in a table
UPDATE tb_Student SET SeniorMentorID = 1 WHERE StudentName = 'Jack';
55
Delete data based on a condition
DELETE FROM tb_Student WHERE StudentName = 'Alice';
56
Select statement order
FROM, WHERE, GROUP BY, HAVING, ORDER BY
57
SELECT definition
identifies what columns
58
FROM definition
identifies what table
59
WHERE definition
imposes constraints for which rows show up
60
ORDER BY definition
sort results rows in ascending (default) or descending (desc) order
61
Write a simple select statement example
SELECT CustomerName, Phone FROM CUSTOMER WHERE CITY = 'Atlanta' AND STATE = 'GA' ORDER BY CustomerName;
62
=
equal to
63
<
less than
64
<=
less than or equal to
65
>
greater than
66
>=
greater than or equal to
67
<>
not equal to
68
!=
not equal to
69
List the logical operators
NOT, AND, OR
70
List the processing order of logical operators
NOT, AND, OR
71
list the special operators
between, is null, like, in, exists
72
Write an IN statement
SELECT PID, Name, State FROM NBAPlayer WHERE State IN ('TX', 'GA', 'VA'); Is equivalent to multiple OR statements
73
write a not in statement
SELECT PID, Name, State FROM NBAPlayer WHERE State NOT IN ('TX', 'GA', 'VA'); equivalent to multiple != and statements
74
write a between statement
SELECT PID, Name, State FROM NBAPlayer Where PID BETWEEN 3 and 5; equivalent to Where PID >=3 and PID<=5;
75
IS NULL
list of records with missing values
76
IS NOT NULL
list of records with no missing values
77
LIKE
records matching a string pattern
78
like statement for beginning with a letter
LIKE ‘A%’
79
like statement for ending with a letter
LIKE ‘%A’
80
LIKE statement for containing a letter
LIKE ‘%A%’
81
like statement for record containing one letter before the A
LIKE ‘_A’)
82
List the aggregate functions
count, sum, avg, max, min
83
COUNT
the number of rows containing the specified column (attribute)
84
SUM
the sum of all values for a selected column
85
AVG
the arithmetic mean (average) for the specified column (attribute)
86
MAX
the largest value in the column
87
MIN
the smallest value in the column
88
What is the average age and maximum age of students
SELECT AVG(Age) AS AverageAge, MAX(Age) AS MaxAge FROM tb_Student;
89
How many of the students live in UC Dorm
SELECT COUNT(*) FROM tb_Student WHERE StudentAddress = 'UC Dorm'
90
Show each student’s name and age difference from the average
SELECT StudentName, Age - (SELECT AVG(Age) FROM tb_Student) AS AgeDiff FROM tb_Student;
91
DISTINCT
designed to produce a list of only those values that are different from one another
92
List distinct student addresses
SELECT DISTINCT StudentAddress FROM tb_Student;
93
T/F SQL does not allow the use of DISTINCT with COUNT(*) together
TRUE
94
GROUP BY
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
GROUP BY example
SELECT State, AVG(PerHour) FROM NBAPlayer GROUP BY State;
96
HAVING definition
like where but used with group by
97
HAVING example
SELECT City, State, Avg(PerHour) FROM NBAPlayer GROUP BY City, State HAVING COUNT( * ) >= 3;
98
Example of a full select with where, group by, and having
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.
© 2025 Bold Learning Solutions. Terms and Conditions