HCPP Final Review Flashcards
(129 cards)
1
Q
database that has a collection of interrelated tables of data items
A
relational database
2
Q
how do we store relational databases
A
RBDMS
3
Q
RBDMS
A
relational database management systems
4
Q
two types of databases
A
- PC-Based
2. Client/server
5
Q
examples of PC-Based RDBMS
A
access, sql server, oracle
6
Q
examples of client/server RBDMS
A
sybase, oracle 11g, ibm
7
Q
5 uses for relational database
A
- control redundancy
- standardize data storage
- secure data
- share data
- maintain data integrity
8
Q
6 signs of effective database design
A
- simple to use
- good system performance
- efficient storage handling
- backup and recovery procedures are effective
- system is able to deal with concurrency
- organization is able to audit use
9
Q
auditing functions in database design are used to track these 2 things
A
- users
2. table access frequency
10
Q
tells the story of an ERD
A
entity relationship narrative (ERN)
11
Q
provides written descriptions of relationships in an ERD
A
entity relationship narrative (ERN)
12
Q
main table in an ERD
A
entity
13
Q
ERD consists of _____ shown as columns in the table
A
attributes
14
Q
attributes =
A
column
15
Q
a limit; something that has to be true; business rules
A
constraints
16
Q
unique identifier; column in a table whose value will uniquely identify the row of data
A
primary key
17
Q
pk symbol in an attribute list
A
#
18
Q
column in a table that is a primary key in another table that link two tables together
A
foreign keys
19
Q
t or f. foreign keys are always on the ‘many’ side of one to many
A
t
20
Q
degree sign in an attribute list
A
optional
21
Q
asterisk in an attribute list
A
mandatory
22
Q
automatically assigns the next primary key in a specified pattern
A
sequence
23
Q
expand SQL
A
structure query language
24
Q
written to extract data from the database
A
query
25
explain
SELECT sysdate
FROM dual;
find today's date from database
26
arrangement of words or phrases to create a sentence in a language; like a formula
syntax
27
t or f. query syntax using SQL is not an appropriate way to write a query
f
28
another name
alias
29
2 types of alias
1. table
| 2. column
30
type of alias that makes it easier and faster to type a query
table
31
type of alias that changes how the column heading is displayed when data is returned
column
32
must be done if you want to pull data from more than one related table
table join
33
based on the relationship between the primary and foreign key
table join
34
returns every row of data from one table with every row of data from another table
cartesian product
35
is cartesian product desirable?
no; happens when a table join is done without conditions
36
when is cartesian product done?
to run certain tests
37
example of cartesian product
10 visits from 10 patients = multiplies results to show 100 rows
38
3 data types
1. VARCHAR2
2. NUMBER
3. DATE
39
variable length characters string including letters, numbers or symbols
VARCHAR2
40
number data type can only include these 3 things
1. digits 0-9
2. decimal point
3. minus sign
41
t or f. number data type are not limited to characters
false; example: 15 char upper limit
42
data type including info about date, time and time zone
date
43
default date format in sql
dd-mon-rr
44
refers to having the same data in different places within a database (duplication)
data redundancy
45
refers to data inconsistencies
data anomalies
46
4 signs of unnormalized database
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
47
3 types of ERD
1. conceptual
2. logical
3. physical
48
helps make databases easier to use
naming guidelines
49
example of a naming guideline
use underscores instead of spaces if title has more than one word
50
normalization phase where primary key is identified
first normal form (1NF)
51
normalization phase where repeating groups are eliminated e.g. separating authors into their own rows in case of books with multiple authors
first normal form (1NF)
52
more than one column is required to uniquely identify a row
composite primary key
53
composite primary key can lead to a ______
partial dependency
54
a column is only dependent on a portion of the primary key
composite pk
55
t or f. make sure attributes in an entity solely relate to the entity title
t
56
normalization phase where partial dependency must be eliminated
second normal form (2NF)
57
normalization phase where composite pk is broken into two parts, each part representing a separate table
2NF
58
at least one value in the record isn't dependent on the primary key but on another field in the record
transitive dependency
59
normalization phase where transitive dependencies are eliminated
third normal form (3NF)
60
3 steps in normalization process; remove:
1NF repeating groups
2NF partial dependency (separate into two tables)
3NF transitive dependency (separate further into another table)
61
3 steps in normalization process, general:
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
5 types of constraints
1. primary key
2. foreign key
3. not null (asterisk)
4. unique
5. check
63
t or f. every table must have a primary key
t
64
primary key enforces and captures both ____ and ____ constraints
not null, unique
65
constraint that ensures that the value entered already exists in the original table
foreign key
66
foreign keys are added to the ____ table in a "one-to-many" relationship
many
67
t or f. foreign keys don't require a value
t
68
a ____ constraint must be used along with foreign keys to require input for column
not null
69
constraint where value must be unique and can't be repeated anywhere else
unique (UKL)
70
unique constraint differs from primary key in that it may be _____
optional
71
a _____ constraint must be used along with unique constraints to require input for the column
unique
72
example of unique constraint
social insurance number in a table with an HCN primary key
73
constraint in which data must meet specified condition before being added to the table
check (CK)
74
example of check constraitn
admit date can't be before bday, province must be AB, BC etc
75
a _____ constraint must be used along with check constraint to make it mandatory
not null
76
not null needed for these constraints to be mandatory
foreign key, unique, check
77
creates, changes or removes database tables ex: CREATE, ALTER, DROP, and rename
data definition language (DDL)
78
manages data within existing database objects ex: SELECT, INSERT, UPDATE, DELETE
data manipulation language (DML)
79
maximum no of characters for tables, columns, and constraints
30 characters
80
tables, columns and constraints must begin with a _____
letter
81
characters appropriate for table column and constraint names
letters, numbers, underscores, numbers
82
t or f. table and constraint names must be unique within the user's account
t
83
t or f. column names must be unique in each table
t
84
t or f. naming can't use oracle's reserved words like SELECT, DISTINCT, CHAR NUMBER
t
85
constraints can be created at these 2 levels
1. table
| 2. column
86
NOT NULL constraints must always be done at the ____ level
column
87
Composite PK must always be done at the ____ level
table
88
column level can only be used for ____ attribute constraints
single
89
first tables to be created
parent tables: 1 in 1:M relationships
90
second tables to be created
child tables: m in 1:M relationships
91
third tables to be created
bridging entity
92
tables with fk must be made before or after the table it will be referencing
after
93
t or f. date datatype has no data length
t
94
syntax that sets the first value of the PK
START WITH
95
syntax that indicates what you want the PK to increase by
INCREMENT BY
96
syntax that instructs not to hold a set of numbers in memory
NOCACHE
97
syntax: do not cycle back at any time and reuse the numbers
NOCYCLE
98
2 functions of DROP command
1. starts from zero point
| 2. erases board
99
drop the tables in the ____ order of how you create them
opposite
100
syntax that deletes the FK constraints in the child table before deleting the parent table constraints
CASCADE CONSTRAINTS
101
if you need to make a structural change to a table use the ____ command
ALTER TALE
102
similar in purpose to the index of a textbook providing a guide to the contents of a databse
indexes
103
t or f. modifying existing constraints is not possible
t
104
t or f. the only option to deleting a constraint is the DROP then create command
t
105
command that is the "save" function for SQL
COMMIT
106
t or f. COMMIT saves changed data in a table permanently
t
107
2 methods of data insertion
1. explicit
| 2. implicit
108
data insertion method used when you want to enter data into some but not all of the columns in the table
explicit
109
t or f in explicit data insertion the order that you list the columns in doesn't matter
t
110
data insertion method used when you want to enter data into all columns in table
implicit
111
in implicit data insertion, all columns must have an entry in the ____ line
VALUES
112
t or f in implicit data insertion data must be entered in the same order as columns listed on table
t
113
t or fin data insertion, the upper and lower case consistency matters
t
114
single quotes
delimiters
115
delimiters are required for these data types
VARCHAR2 and date
116
delimiters are not allowed around this data type
NUMBER
117
sequence command added to the end of the sequence name to add the next number in the sequence
NEXTVAL
118
sequence command used when inserting a sequence value into multiple tables (parent-child relationships) when a sequence is a foreign key in another table
CURRVAL
119
oracle stores the last generated sequence value as _____
CURRVAL
120
CURRVAL used when the sequence is a _____ in another table
foreign key
121
when sequence value is being used for a PK column, use ____
.NEXTVAL
122
when sequence value is being used for FK column, use ____
.CURRVAL
123
for CURRVAL, determine which rows belong to which ____. must insert directly underneath the NEXTVAL command
NEXTVAL
124
6 insertion steps
1. ERD
2. normalization
3. create
4. drop
5. sequences
6. insert
125
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
SELECT
126
added to a SELECT statement to narrow down query results
WHERE
127
condition identifies a requirement that must be met for a record to be included in the query results
WHERE
128
columns can be compared against fixed values using ____
relational operators
129
an oracle built-in that references the system date (today's date)
SYSDATE
