HCPP Final Review Flashcards
database that has a collection of interrelated tables of data items
relational database
how do we store relational databases
RBDMS
RBDMS
relational database management systems
two types of databases
- PC-Based
2. Client/server
examples of PC-Based RDBMS
access, sql server, oracle
examples of client/server RBDMS
sybase, oracle 11g, ibm
5 uses for relational database
- control redundancy
- standardize data storage
- secure data
- share data
- maintain data integrity
6 signs of effective database design
- 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
auditing functions in database design are used to track these 2 things
- users
2. table access frequency
tells the story of an ERD
entity relationship narrative (ERN)
provides written descriptions of relationships in an ERD
entity relationship narrative (ERN)
main table in an ERD
entity
ERD consists of _____ shown as columns in the table
attributes
attributes =
column
a limit; something that has to be true; business rules
constraints
unique identifier; column in a table whose value will uniquely identify the row of data
primary key
pk symbol in an attribute list
#
column in a table that is a primary key in another table that link two tables together
foreign keys
t or f. foreign keys are always on the ‘many’ side of one to many
t
degree sign in an attribute list
optional
asterisk in an attribute list
mandatory
automatically assigns the next primary key in a specified pattern
sequence
expand SQL
structure query language
written to extract data from the database
query
explain
SELECT sysdate
FROM dual;
find today’s date from database
arrangement of words or phrases to create a sentence in a language; like a formula
syntax
t or f. query syntax using SQL is not an appropriate way to write a query
f
another name
alias
2 types of alias
- table
2. column
type of alias that makes it easier and faster to type a query
table
type of alias that changes how the column heading is displayed when data is returned
column
must be done if you want to pull data from more than one related table
table join
based on the relationship between the primary and foreign key
table join
returns every row of data from one table with every row of data from another table
cartesian product
is cartesian product desirable?
no; happens when a table join is done without conditions
when is cartesian product done?
to run certain tests
example of cartesian product
10 visits from 10 patients = multiplies results to show 100 rows
3 data types
- VARCHAR2
- NUMBER
- DATE
variable length characters string including letters, numbers or symbols
VARCHAR2
number data type can only include these 3 things
- digits 0-9
- decimal point
- minus sign
t or f. number data type are not limited to characters
false; example: 15 char upper limit
data type including info about date, time and time zone
date
default date format in sql
dd-mon-rr
refers to having the same data in different places within a database (duplication)
data redundancy
refers to data inconsistencies
data anomalies
4 signs of unnormalized database
- redundant data
- same attributes exist in more than one entity
- updates would have to be made in every single location
- contains repeating group
3 types of ERD
- conceptual
- logical
- physical
helps make databases easier to use
naming guidelines
example of a naming guideline
use underscores instead of spaces if title has more than one word
normalization phase where primary key is identified
first normal form (1NF)
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)
more than one column is required to uniquely identify a row
composite primary key
composite primary key can lead to a ______
partial dependency
a column is only dependent on a portion of the primary key
composite pk
t or f. make sure attributes in an entity solely relate to the entity title
t
normalization phase where partial dependency must be eliminated
second normal form (2NF)
normalization phase where composite pk is broken into two parts, each part representing a separate table
2NF
at least one value in the record isn’t dependent on the primary key but on another field in the record
transitive dependency
normalization phase where transitive dependencies are eliminated
third normal form (3NF)
3 steps in normalization process; remove:
1NF repeating groups
2NF partial dependency (separate into two tables)
3NF transitive dependency (separate further into another table)
3 steps in normalization process, general:
- track main table that connects to most or all tables
- link to other tables and determine relationships
- plot each primary key as a foreign key into the main table
5 types of constraints
- primary key
- foreign key
- not null (asterisk)
- unique
- check
t or f. every table must have a primary key
t
primary key enforces and captures both ____ and ____ constraints
not null, unique
constraint that ensures that the value entered already exists in the original table
foreign key
foreign keys are added to the ____ table in a “one-to-many” relationship
many
t or f. foreign keys don’t require a value
t
a ____ constraint must be used along with foreign keys to require input for column
not null
constraint where value must be unique and can’t be repeated anywhere else
unique (UKL)
unique constraint differs from primary key in that it may be _____
optional
a _____ constraint must be used along with unique constraints to require input for the column
unique
example of unique constraint
social insurance number in a table with an HCN primary key
constraint in which data must meet specified condition before being added to the table
check (CK)
example of check constraitn
admit date can’t be before bday, province must be AB, BC etc
a _____ constraint must be used along with check constraint to make it mandatory
not null
not null needed for these constraints to be mandatory
foreign key, unique, check
creates, changes or removes database tables ex: CREATE, ALTER, DROP, and rename
data definition language (DDL)
manages data within existing database objects ex: SELECT, INSERT, UPDATE, DELETE
data manipulation language (DML)
maximum no of characters for tables, columns, and constraints
30 characters
tables, columns and constraints must begin with a _____
letter
characters appropriate for table column and constraint names
letters, numbers, underscores, numbers
t or f. table and constraint names must be unique within the user’s account
t
t or f. column names must be unique in each table
t
t or f. naming can’t use oracle’s reserved words like SELECT, DISTINCT, CHAR NUMBER
t
constraints can be created at these 2 levels
- table
2. column
NOT NULL constraints must always be done at the ____ level
column
Composite PK must always be done at the ____ level
table
column level can only be used for ____ attribute constraints
single
first tables to be created
parent tables: 1 in 1:M relationships
second tables to be created
child tables: m in 1:M relationships
third tables to be created
bridging entity
tables with fk must be made before or after the table it will be referencing
after
t or f. date datatype has no data length
t
syntax that sets the first value of the PK
START WITH
syntax that indicates what you want the PK to increase by
INCREMENT BY
syntax that instructs not to hold a set of numbers in memory
NOCACHE
syntax: do not cycle back at any time and reuse the numbers
NOCYCLE
2 functions of DROP command
- starts from zero point
2. erases board
drop the tables in the ____ order of how you create them
opposite
syntax that deletes the FK constraints in the child table before deleting the parent table constraints
CASCADE CONSTRAINTS
if you need to make a structural change to a table use the ____ command
ALTER TALE
similar in purpose to the index of a textbook providing a guide to the contents of a databse
indexes
t or f. modifying existing constraints is not possible
t
t or f. the only option to deleting a constraint is the DROP then create command
t
command that is the “save” function for SQL
COMMIT
t or f. COMMIT saves changed data in a table permanently
t
2 methods of data insertion
- explicit
2. implicit
data insertion method used when you want to enter data into some but not all of the columns in the table
explicit
t or f in explicit data insertion the order that you list the columns in doesn’t matter
t
data insertion method used when you want to enter data into all columns in table
implicit
in implicit data insertion, all columns must have an entry in the ____ line
VALUES
t or f in implicit data insertion data must be entered in the same order as columns listed on table
t
t or fin data insertion, the upper and lower case consistency matters
t
single quotes
delimiters
delimiters are required for these data types
VARCHAR2 and date
delimiters are not allowed around this data type
NUMBER
sequence command added to the end of the sequence name to add the next number in the sequence
NEXTVAL
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
oracle stores the last generated sequence value as _____
CURRVAL
CURRVAL used when the sequence is a _____ in another table
foreign key
when sequence value is being used for a PK column, use ____
.NEXTVAL
when sequence value is being used for FK column, use ____
.CURRVAL
for CURRVAL, determine which rows belong to which ____. must insert directly underneath the NEXTVAL command
NEXTVAL
6 insertion steps
- ERD
- normalization
- create
- drop
- sequences
- insert
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
added to a SELECT statement to narrow down query results
WHERE
condition identifies a requirement that must be met for a record to be included in the query results
WHERE
columns can be compared against fixed values using ____
relational operators
an oracle built-in that references the system date (today’s date)
SYSDATE
