ERD's / business rules Flashcards
(52 cards)
What does ERD stand for?
Entity Relationship Diagram
Define: Entity
A person, place, thing, or an event about which we keep information. Shown as a rectangular box, labeled with the name of the entity.
What would a banks entities be?
What would a simple bank ERD look like?
Define: Relationships
Relationships exist between pairs of entities. There are three kinds:
What would a library ERD example look like with Relationships?
What are the two main steps in designing a database with ERD?
Step1: Identify the Vital Entities
- Interview representatices
- Examin existing systems
Step2: Define Entities and Relationships
- Determine the key and dependent attributes
What are the main ER Modeling Symbols?
What would a more intermediate CHDB ERD with relationships look like?
What does Cardinality mean?
It is the relationships
What is a predicate?
A predicate specifies a condition that is true, false, or unknown about a given row or group.
Where are predicates used?
In WHERE clauses
What are the different types of predicates?
Basic
BETWEEN
EXISTS
IN
LIKE
NULL
What are basic predicates?
A basic predicate compares two values (=, <>, <, >, <=, >=)
eg.
SELECT *
FROM admissions
WHERE nursing_unit_id = ‘2EAST’
SELECT first_name, last_name, patient_weight
FROM patients
WHERE patient_weight > (SELECT AVG(patient_weight) FROM patients)
What is the BETWEEN predicate?
The BETWEEN predicate compares a value inclusively with a range of values. It is a more compact version of >= AND <=.
eg.
SELECT first_name, last_name, patient_height
FROM patients
WHERE men_height BETWEEN 10 AND 200
VS
WHERE men_height >= 10 AND men_height <= 125
What is the EXISTS predicate?
The EXISTS predicate tests for the existence of certain rows using a subquery. Returns TRUE if a subquery contains any rows.
eg.
Find patients who take at least one medication
SELECT first_name, last_name
FROM patients
WHERE
EXISTS (SELECT * FROM unit_dose_orders
WHERE unit_dose_orders.patient_id =
patients.patient_id)
What is the IN predicate?
The in predicate compares a value with a set of values. For many cases, whenever the EXISTS predicate can be used, the IN predicate can be used.
eg.
SELECT first_name, last_name
FROM patients
WHERE
patient_id IN (SELECT
DISTINCT patient_id FROM
unit_dose_orders)
What is the NOT IN predicate?
The opposite as the the IN predicate.
eg.
SELECT *
FROM vendors
WHERE
vendor_id NOT IN (SELECT
DISTINCT vendor_id FROM
purchase_orders)
What is the LIKE predicate?
The LIKE predicate searches for strings that have a certain pattern.
What are the two symbols used in the LIKE predicate and what are their purposes?
% mathches any number of characters
_ mathches a single character
eg.
SELECT first_name, last_name, primary_diagnosis
FROM patients
JOIN admissions
ON patients.patient_id = admissions.patient_id
WHERE
primary_diagnosis LIKE ‘%Diab%’
SELECT first_name, last_name, nursing_unit_id
FROM patients
JOIN admissions
ON patients.patient_id = admissions.patient_id
WHERE
nursing_unit_id LIKE ‘_EAST’
What is the NULL predicate?
The NULL predicate tests for null values.
How is the NULL predicate used?
The NULL predicate tests for null values. It MUST be used with IS, not =
eg.
SELECT first_name, last_name, secondary_diagnoses
FROM patients
JOIN admissions
ON patients.patient_id = admissions.patient_id
WHERE secondary_diagnoses IS NULL
What is the TOP clause?
It is not a Predicate. The TOP clause sets a maximum number of rows that can be retieved.
eg.
SELECT TOP 5 *
FROM patients
What is the TOP PERCENT clause?
Not a predicate. The TOP with PERCENT clause sets a maximum number of rows that can be retrieved.
