Brainscape's Knowledge GenomeTM

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


See full index

4. HCPP 310 SQL > HCPP Final Review > Flashcards

HCPP Final Review Flashcards

1
Q

database that has a collection of interrelated tables of data items

A

relational database

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

how do we store relational databases

A

RBDMS

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

RBDMS

A

relational database management systems

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

two types of databases

A
  1. PC-Based

2. Client/server

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

examples of PC-Based RDBMS

A

access, sql server, oracle

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

examples of client/server RBDMS

A

sybase, oracle 11g, ibm

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

5 uses for relational database

A
  1. control redundancy
  2. standardize data storage
  3. secure data
  4. share data
  5. maintain data integrity
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

6 signs of effective database design

A
  1. simple to use
  2. good system performance
  3. efficient storage handling
  4. backup and recovery procedures are effective
  5. system is able to deal with concurrency
  6. organization is able to audit use
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

auditing functions in database design are used to track these 2 things

A
  1. users

2. table access frequency

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

tells the story of an ERD

A

entity relationship narrative (ERN)

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

provides written descriptions of relationships in an ERD

A

entity relationship narrative (ERN)

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

main table in an ERD

A

entity

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

ERD consists of _____ shown as columns in the table

A

attributes

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

attributes =

A

column

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

a limit; something that has to be true; business rules

A

constraints

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

unique identifier; column in a table whose value will uniquely identify the row of data

A

primary key

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

pk symbol in an attribute list

A

#

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

column in a table that is a primary key in another table that link two tables together

A

foreign keys

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

t or f. foreign keys are always on the ‘many’ side of one to many

A

t

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

degree sign in an attribute list

A

optional

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

asterisk in an attribute list

A

mandatory

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

automatically assigns the next primary key in a specified pattern

A

sequence

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

expand SQL

A

structure query language

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

written to extract data from the database

A

query

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

explain
SELECT sysdate
FROM dual;

A

find today’s date from database

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

arrangement of words or phrases to create a sentence in a language; like a formula

A

syntax

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

t or f. query syntax using SQL is not an appropriate way to write a query

A

f

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

another name

A

alias

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

2 types of alias

A
  1. table

2. column

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

type of alias that makes it easier and faster to type a query

A

table

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

type of alias that changes how the column heading is displayed when data is returned

A

column

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

must be done if you want to pull data from more than one related table

A

table join

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

based on the relationship between the primary and foreign key

A

table join

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

returns every row of data from one table with every row of data from another table

A

cartesian product

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

is cartesian product desirable?

A

no; happens when a table join is done without conditions

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

when is cartesian product done?

A

to run certain tests

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

example of cartesian product

A

10 visits from 10 patients = multiplies results to show 100 rows

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

3 data types

A
  1. VARCHAR2
  2. NUMBER
  3. DATE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

variable length characters string including letters, numbers or symbols

A

VARCHAR2

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

number data type can only include these 3 things

A
  1. digits 0-9
  2. decimal point
  3. minus sign
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

t or f. number data type are not limited to characters

A

false; example: 15 char upper limit

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

data type including info about date, time and time zone

A

date

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

default date format in sql

A

dd-mon-rr

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

refers to having the same data in different places within a database (duplication)

A

data redundancy

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

refers to data inconsistencies

A

data anomalies

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

4 signs of unnormalized database

A
  1. redundant data
  2. same attributes exist in more than one entity
  3. updates would have to be made in every single location
  4. contains repeating group
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

3 types of ERD

A
  1. conceptual
  2. logical
  3. physical
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

helps make databases easier to use

A

naming guidelines

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

example of a naming guideline

A

use underscores instead of spaces if title has more than one word

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

normalization phase where primary key is identified

A

first normal form (1NF)

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

normalization phase where repeating groups are eliminated e.g. separating authors into their own rows in case of books with multiple authors

A

first normal form (1NF)

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

more than one column is required to uniquely identify a row

A

composite primary key

53
Q

composite primary key can lead to a ______

A

partial dependency

54
Q

a column is only dependent on a portion of the primary key

A

composite pk

55
Q

t or f. make sure attributes in an entity solely relate to the entity title

A

t

56
Q

normalization phase where partial dependency must be eliminated

A

second normal form (2NF)

57
Q

normalization phase where composite pk is broken into two parts, each part representing a separate table

A

2NF

58
Q

at least one value in the record isn’t dependent on the primary key but on another field in the record

A

transitive dependency

59
Q

normalization phase where transitive dependencies are eliminated

A

third normal form (3NF)

60
Q

3 steps in normalization process; remove:

A

1NF repeating groups
2NF partial dependency (separate into two tables)
3NF transitive dependency (separate further into another table)

61
Q

3 steps in normalization process, general:

A
  1. track main table that connects to most or all tables
  2. link to other tables and determine relationships
  3. plot each primary key as a foreign key into the main table
62
Q

5 types of constraints

A
  1. primary key
  2. foreign key
  3. not null (asterisk)
  4. unique
  5. check
63
Q

t or f. every table must have a primary key

A

t

64
Q

primary key enforces and captures both ____ and ____ constraints

A

not null, unique

65
Q

constraint that ensures that the value entered already exists in the original table

A

foreign key

66
Q

foreign keys are added to the ____ table in a “one-to-many” relationship

A

many

67
Q

t or f. foreign keys don’t require a value

A

t

68
Q

a ____ constraint must be used along with foreign keys to require input for column

A

not null

69
Q

constraint where value must be unique and can’t be repeated anywhere else

A

unique (UKL)

70
Q

unique constraint differs from primary key in that it may be _____

A

optional

71
Q

a _____ constraint must be used along with unique constraints to require input for the column

A

unique

72
Q

example of unique constraint

A

social insurance number in a table with an HCN primary key

73
Q

constraint in which data must meet specified condition before being added to the table

A

check (CK)

74
Q

example of check constraitn

A

admit date can’t be before bday, province must be AB, BC etc

75
Q

a _____ constraint must be used along with check constraint to make it mandatory

A

not null

76
Q

not null needed for these constraints to be mandatory

A

foreign key, unique, check

77
Q

creates, changes or removes database tables ex: CREATE, ALTER, DROP, and rename

A

data definition language (DDL)

78
Q

manages data within existing database objects ex: SELECT, INSERT, UPDATE, DELETE

A

data manipulation language (DML)

79
Q

maximum no of characters for tables, columns, and constraints

A

30 characters

80
Q

tables, columns and constraints must begin with a _____

A

letter

81
Q

characters appropriate for table column and constraint names

A

letters, numbers, underscores, numbers

82
Q

t or f. table and constraint names must be unique within the user’s account

A

t

83
Q

t or f. column names must be unique in each table

A

t

84
Q

t or f. naming can’t use oracle’s reserved words like SELECT, DISTINCT, CHAR NUMBER

A

t

85
Q

constraints can be created at these 2 levels

A
  1. table

2. column

86
Q

NOT NULL constraints must always be done at the ____ level

A

column

87
Q

Composite PK must always be done at the ____ level

A

table

88
Q

column level can only be used for ____ attribute constraints

A

single

89
Q

first tables to be created

A

parent tables: 1 in 1:M relationships

90
Q

second tables to be created

A

child tables: m in 1:M relationships

91
Q

third tables to be created

A

bridging entity

92
Q

tables with fk must be made before or after the table it will be referencing

A

after

93
Q

t or f. date datatype has no data length

A

t

94
Q

syntax that sets the first value of the PK

A

START WITH

95
Q

syntax that indicates what you want the PK to increase by

A

INCREMENT BY

96
Q

syntax that instructs not to hold a set of numbers in memory

A

NOCACHE

97
Q

syntax: do not cycle back at any time and reuse the numbers

A

NOCYCLE

98
Q

2 functions of DROP command

A
  1. starts from zero point

2. erases board

99
Q

drop the tables in the ____ order of how you create them

A

opposite

100
Q

syntax that deletes the FK constraints in the child table before deleting the parent table constraints

A

CASCADE CONSTRAINTS

101
Q

if you need to make a structural change to a table use the ____ command

A

ALTER TALE

102
Q

similar in purpose to the index of a textbook providing a guide to the contents of a databse

A

indexes

103
Q

t or f. modifying existing constraints is not possible

A

t

104
Q

t or f. the only option to deleting a constraint is the DROP then create command

A

t

105
Q

command that is the “save” function for SQL

A

COMMIT

106
Q

t or f. COMMIT saves changed data in a table permanently

A

t

107
Q

2 methods of data insertion

A
  1. explicit

2. implicit

108
Q

data insertion method used when you want to enter data into some but not all of the columns in the table

A

explicit

109
Q

t or f in explicit data insertion the order that you list the columns in doesn’t matter

A

t

110
Q

data insertion method used when you want to enter data into all columns in table

A

implicit

111
Q

in implicit data insertion, all columns must have an entry in the ____ line

A

VALUES

112
Q

t or f in implicit data insertion data must be entered in the same order as columns listed on table

A

t

113
Q

t or fin data insertion, the upper and lower case consistency matters

A

t

114
Q

single quotes

A

delimiters

115
Q

delimiters are required for these data types

A

VARCHAR2 and date

116
Q

delimiters are not allowed around this data type

A

NUMBER

117
Q

sequence command added to the end of the sequence name to add the next number in the sequence

A

NEXTVAL

118
Q

sequence command used when inserting a sequence value into multiple tables (parent-child relationships) when a sequence is a foreign key in another table

A

CURRVAL

119
Q

oracle stores the last generated sequence value as _____

A

CURRVAL

120
Q

CURRVAL used when the sequence is a _____ in another table

A

foreign key

121
Q

when sequence value is being used for a PK column, use ____

A

.NEXTVAL

122
Q

when sequence value is being used for FK column, use ____

A

.CURRVAL

123
Q

for CURRVAL, determine which rows belong to which ____. must insert directly underneath the NEXTVAL command

A

NEXTVAL

124
Q

6 insertion steps

A
  1. ERD
  2. normalization
  3. create
  4. drop
  5. sequences
  6. insert
125
Q

statement used to retrieve data from the database so users can view all columns and rows in a table, or specify only certain columns or rows

A

SELECT

126
Q

added to a SELECT statement to narrow down query results

A

WHERE

127
Q

condition identifies a requirement that must be met for a record to be included in the query results

A

WHERE

128
Q

columns can be compared against fixed values using ____

A

relational operators

129
Q

an oracle built-in that references the system date (today’s date)

A

SYSDATE

4. HCPP 310 SQL (2 decks)

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