Brainscape's Knowledge GenomeTM

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


See full index

Database Theory > ERD's / business rules > Flashcards

ERD's / business rules Flashcards

1
Q

What does ERD stand for?

A

Entity Relationship Diagram

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

Define: Entity

A

A person, place, thing, or an event about which we keep information. Shown as a rectangular box, labeled with the name of the entity.

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

What would a banks entities be?

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

What would a simple bank ERD look like?

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

Define: Relationships

A

Relationships exist between pairs of entities. There are three kinds:

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

What would a library ERD example look like with Relationships?

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

What are the two main steps in designing a database with ERD?

A

Step1: Identify the Vital Entities

  • Interview representatices
  • Examin existing systems

Step2: Define Entities and Relationships

  • Determine the key and dependent attributes
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What are the main ER Modeling Symbols?

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

What would a more intermediate CHDB ERD with relationships look like?

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

What does Cardinality mean?

A

It is the relationships

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

What is a predicate?

A

A predicate specifies a condition that is true, false, or unknown about a given row or group.

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

Where are predicates used?

A

In WHERE clauses

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

What are the different types of predicates?

A

Basic

BETWEEN

EXISTS

IN

LIKE

NULL

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

What are basic predicates?

A

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)

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

What is the BETWEEN predicate?

A

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

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

What is the EXISTS predicate?

A

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)

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

What is the IN predicate?

A

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)

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

What is the NOT IN predicate?

A

The opposite as the the IN predicate.

eg.

SELECT *

FROM vendors

WHERE

vendor_id NOT IN (SELECT

DISTINCT vendor_id FROM

purchase_orders)

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

What is the LIKE predicate?

A

The LIKE predicate searches for strings that have a certain pattern.

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

What are the two symbols used in the LIKE predicate and what are their purposes?

A

% 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’

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

What is the NULL predicate?

A

The NULL predicate tests for null values.

22
Q

How is the NULL predicate used?

A

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

23
Q

What is the TOP clause?

A

It is not a Predicate. The TOP clause sets a maximum number of rows that can be retieved.

eg.

SELECT TOP 5 *

FROM patients

24
Q

What is the TOP PERCENT clause?

A

Not a predicate. The TOP with PERCENT clause sets a maximum number of rows that can be retrieved.

25
Q

What is the HAVING clause?

A

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

26
Q

What is an Entity Relationship Model?

A

A form of semantic modeling. A description of the data in a system. Represented by Entity Relationship Diagrams.

27
Q

What are entitites?

A

A person, place, thing or event about which we keep information.

Shown as a rectangular box, labeled with the name of the entity.

28
Q

What are the two steps in designing a Database?

A

Step 1. Identify the Vital entities.

  • Interview representatives
  • examine existing systems

Step 2. Define entities and relationships.

  • Determine the key and dependent attributes
29
Q

What is a relationship?

A

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.

30
Q

What is Cardinality?

A

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.

31
Q

What is a Cardinality range?

A

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.

32
Q

What is a weak entity?

A

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
33
Q

How would you spot a weak entity?

A
34
Q

What is a degree?

A
  • 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
35
Q

Give an example of Degree relationships

A
36
Q

What are business rules?

A
  • 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
37
Q

Examples of business rules?

A
  • 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
38
Q

What are the 4 types of business rules?

A
  • Entity integrity
  • Referential integrity
  • Domains
  • Triggering operation
39
Q

What is Entity Integrity?

A

Each instance of an entity must have a unique identifier that is not null. The primary key.

40
Q

What is referential integrity?

A

Rules governing the relationships between entities. Refer to the foreign keys which link tables.

41
Q

Give an example of referential integrity.

A
42
Q

What is a domain?

A

Associated with constraints that restrict the values permitted in a column. (all the possible values a column can have)

43
Q

What are the advantages of using domains?

A
  • Verify values for an attribute (during INSERT or UPDATE operations)
  • Ensure data manipulation operations (koins, unions) are logical.

eg. patient_height > 0

44
Q

What are Triggering operations?

A

Rules that are invoked on the action of data manipulation operations (INSERT, UPDATE, DELETE)

45
Q

What are some uses of triggering operations?

A

– Automatically generate derived column values

– Prevent invalid transactions

– Enforce complex security authorizations

– Provide transparent event logging/auditing

– Used as a last resor

46
Q

Tiggers are used as a last resort, give examples of when they can be used.

A

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
47
Q

How would this business rule be implemented?

An employee id must be unique

A

Entity integrity

48
Q

How would this business rule be implemented?

A customer province must be valid

A

Referential integrity

49
Q

How would this business rule be implemented?

Account status can be active or inactive

A

Domain

50
Q

How would this business rule be implemented?

A machine operator may not work more than 10 hours in an 24 hour period

A

Trigger

51
Q

How would this business rule be implemented?

reorder items when inventory falls below 5

A

trigger

52
Q
A

Database Theory (7 decks)

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