Database Design Test 2 - John Stone

Who introduced the relational model

E.F Codd in 1970 paper

Entity

some identifiable thing that users want to track

Relation

a dimensional table

Characteristics of a relation

- Rows contain data about an entity

- column contain data about attributes of entity

- all entries in a column are of the same kind

- unique name for each column

- cells of a table hold a single value

- row and column order are unimportant

- no 2 rows are identical

Functional dependency

when the value of one attribute determines the value of the second

Determinant

Determines the outcome of another attribute in Functional dependency

(the left side of the arrow)

candidate key

key that can determine all other columns in a relation

key

a combination of 1 or more columns that is used to identify particular rows in a relation

composite key

key that consists of 2 or more columns

primary key

a candidate key selected as primary to identify a particular row in a relations

Unique and NO NULL Values

surrogate key

an artificial column added to relation to serve as primary key. Has no value outside of the database

Foreign key

a column or composite of columns that is the Primary key of a table other than the one in which it appears

referential integrity constraint

limits the values of the foreign key to those already existing as the primary key values in the corresponding relation.

(the foreign key column has to match the table it comes from primary key)

Modification Anomalies

deletion

insertion

update

1NF

meets the conditions for a relation, has a defined primary key

2NF

in 1NF and all non-key attributes are determined by the entire primary key (partial dependency)

3NF

2NF and there is no non-key attributes determined by other non-key attributes (transitive dependcy)

BCNF

Boyce-Codd Normal Form

Every determinate is a candidate key

4NF

3NF and all multivalued dependencies are in their own relation

putting a relation into BCNF steps:

1. identify every functional dependency

2. identify every candidate key

3. if there is a functional dependency with a determinant that is not a candidate key:

a) move column to new relation

b) make determinant primary key in new relation

c) leave copy of determinant as foreign key in old relation

d) make referential integrity constrains between old and new relation

4. repeat step 3 until every determinant of every relation is a candidate key

Multivalued dependencies

a determinant is matched with a particular set of values

Ex. Employee ---> ---> degrees

(Determinant of MVD cannot be primary key)

advantages of normalization

reduced redundant data

data consistency

flexibility

quicker execution of queries

disadvantages of normalization

increase in the number of tables

requires more joins so it slows down apps

may not suit some types of data (analytical)

when do you not use BCNF

with attributes like zip codes

its just easier to leave them and they rarely change

read only database

non-operational database used in business intelligence for producing information for decision making

updatable database

production DB concerned with modification anomalies, users will be modifying the database, carefully consider normalization

denormalization

joining of data in normalized tables prior to storage

null values

they are ambiguous, meaning you have to assume what NULL means in every case

general-purpose columns

a comment column

users can type whatever they want

very inconsistent and can end up holding multiple data items

Who invented the ER model

Peter Chen 1976

The E-R model

a set of concepts and graphical symbols that can be used to create conceptual designs.

attributes

describe an entity's characteristics

identifiers

attributes that name or identify entity instances

(become keys in database design)

relationships

how entities are associated with eachother

cardinalities

Max= max number of relationship instances

Min= min number of relationship instances that an entity must participate in

Max cardinalities

[1:1]

[1:N]

[N:M]

Min cardinalities

O-O

O-M

M-O

M-M

(don't use the last 2)

Crows foot notation

mandatory one = exactly one ------||---

mandatory many = one or more -----|-<

optional to one = zero or one ------o-|--

optional to many = zero or more ------o-<

strong entities

entity that represents something that can exist on its own

Ex. (PERSON, BUILDING)

weak entities

entity whose existence depends on another entity

Ex (APARTMENT depends on BUILDING)

ID-Dependent entities

a child or weak entity whose identifier includes the identifier of their entity parent

exclusive subtypes

1 supertype relates to AT MOST 1 subtype

(O with an X through it)

inclusive subtype

1 supertype can relate to zero or many subtypes

(open O)

discriminator

attributes that indicate the subtype

Ex. a student can be in many clubs, each club has its own entity. The discriminator is the attribute that tells us which clubs they are in so we know which entities to use.

strong entity relationships

[1:1]

[1:N]

[N:M]

ID-Dependent relationships

multivalued attribute pattern

archetype/instance pattern

association pattern/line item pattern

Intersection table

id dependent table that connects 2 entities and has no attributes

multivalued attributes

one to many relationships

Archetype/Instance pattern

always weak

the id dependent child is the physical manefestation (instance) of the parent.

Ex. (CLASS:SECTION)

Line-Item Patterns

baseline for associative tables

used for selling multiple things on one order

for use by pattern

you have a discriminator and certain attributes are for certain things. Usually used on forms

Used with subtypes

Recursive relationship

self referencing

Ex. (EMPLOYEE and SUPERVISOR)

Transforming Data Models into DB Design: 3 basic steps

1. create a table for each entity

2. create relationships by placing foreign keys

3. specify logic for enforcing minimum cardinality

Transforming Data Models into DB Design: Step 1

1. create a table for each entity

- specify the primary key (consider surrogate)

- specify alternate key

- specify properties for each column

-Null status

-Data types

-default value (if any)

-data constraints (if any)

-verify normalization

Transforming Data Models into DB Design: Step 2

2. create relationships by placing foreign keys

-relationships between strong entities

-identifying relationships with id dependednt entities

-relationships between strong and weak non id dependent entities

-mixed relationship

-relationships between supertype/subtype entities

-recursive relationships

Transforming Data Models into DB Design: Step 3

3. specify logic for enforcing minimum cardinality

O-O relationships

O-M relationships

M-O relationships

M-M relationships