ERD's / business rules Flashcards
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.
What is the HAVING clause?
Not a predicare. A second WHERE clause used with GROUP BY.
eg.
SELECT nursing_unit_id, COUNT(*)
FROM admissions
GROUP BY nursing_unit_id
HAVING COUNT(*) >= 340
What is an Entity Relationship Model?
A form of semantic modeling. A description of the data in a system. Represented by Entity Relationship Diagrams.
What are entitites?
A person, place, thing or event about which we keep information.
Shown as a rectangular box, labeled with the name of the entity.
What are the two steps in designing a Database?
Step 1. Identify the Vital entities.
- Interview representatives
- examine existing systems
Step 2. Define entities and relationships.
- Determine the key and dependent attributes
What is a relationship?
A relationship is an association between entities.
eg. A nursing unit may have many admitted patients, each admitted patient resides in one nursing unit. Therefore the relationship is one to many.
What is Cardinality?
The number of entity occurrences pissible on the two sides of a relationship. Cardinality leads to an important conclusion, whether a particular entity’s participation is mandatory or optional.
What is a Cardinality range?
Professors teach classes. However, sometimes there are specific ranges. A professor must teach at least one class, but no more than 4. The database doesn’t enforce this, it must be done with program logix; this is known as a business rule.
What is a weak entity?
There are two conditions that make an entity weak;
- Its existence depends on the existence of another entity (called a parent entity)
- It has a concatenated key, one of whose parts is taken from the parent entity
How would you spot a weak entity?
What is a degree?
- A unary relationship has one entity
- Employee supervisor
- A peer-tutor program at the college
- Also known as a recursive relationship
- A binary relationship has two entities
- A ternary relationship has three entities
- A four-entity relationship has 4 entities etc.
- Ternary (and higher) are urually represented by a series of binary relationships
Give an example of Degree relationships
What are business rules?
- Specifications that preserve the integrity of a conceptual or logical data model.
- Stored as part of the database
- Help Standardize the company’s view of data
- Four types
Examples of business rules?
- An employee id must be unique
- A customer province must be valid
- Account status can be active or inactive
- A machine operator may not work more than
- 10 hours in any 24 hour period
- Reorder items when inventory falls below
What are the 4 types of business rules?
- Entity integrity
- Referential integrity
- Domains
- Triggering operation
What is Entity Integrity?
Each instance of an entity must have a unique identifier that is not null. The primary key.
What is referential integrity?
Rules governing the relationships between entities. Refer to the foreign keys which link tables.
Give an example of referential integrity.
What is a domain?
Associated with constraints that restrict the values permitted in a column. (all the possible values a column can have)
What are the advantages of using domains?
- Verify values for an attribute (during INSERT or UPDATE operations)
- Ensure data manipulation operations (koins, unions) are logical.
eg. patient_height > 0
What are Triggering operations?
Rules that are invoked on the action of data manipulation operations (INSERT, UPDATE, DELETE)
What are some uses of triggering operations?
– Automatically generate derived column values
– Prevent invalid transactions
– Enforce complex security authorizations
– Provide transparent event logging/auditing
– Used as a last resor
Tiggers are used as a last resort, give examples of when they can be used.
When a required referential integrity rule cannot be enforced
using the following integrity constraints:
– NOT NULL
– UNIQUE key
– PRIMARY KEY
– FOREIGN KEY
- NO ACTION (delete and update)
- RESTRICT (delete and update)
- CASCADE (delete)
- SET NULL (delete)
- SET DEFAULT (delete)
– CHECK
Triggers as a Last Resort
- To enforce referential integrity when child and parent tables are on different nodes of a distributed database
- To enforce complex business rules not definable using integrity constrain
How would this business rule be implemented?
An employee id must be unique
Entity integrity
How would this business rule be implemented?
A customer province must be valid
Referential integrity
How would this business rule be implemented?
Account status can be active or inactive
Domain
How would this business rule be implemented?
A machine operator may not work more than 10 hours in an 24 hour period
Trigger
How would this business rule be implemented?
reorder items when inventory falls below 5
trigger
