database schema

structure that describes in a formal language the association of data as a blueprint of how the database can be constructed

Entity type vs Entity Set

Car entity vs Car 1 Car 2 and Car 3

Key attribute

attribute used to uniquely identify an entity (underlined)

Weak entity

Entity with no key attribute. Employee can have dependents but dependents cant exist without an employee

Unary Relation

one to one. A person can only be married to one person

Binary Relationship

two entities related. A student enrolls in a course

one to one relationship

each takes part only one

Properties of relations (tables really)

-Each table in a database has a unique indentity

-Any entry has only a single value. no multi valued entries in a relation

-each row is unique

-each column has a unique name

-order of columns doesnt matter

-order of rows doesnt matter

candidate key

element that controls all other columns (ie is a candidate for primary key)

primary key

specific choice of minimal set of attributes. can be one column or composite

surrogate key

artificial column added when the primary key is too large. the values are meaningless but they make organization easier

foreign key

a column that is the primary key of a different table

anomalies

problems that occur in poorly planned database

insertion anomaly

when you want to add something to your table, but your are missing part of the information (like i dont have unit price or something)

deletion anomaly

when i want to delete an entry but deleting it gets rid of information i might need or want

normalization def

technique in database systems to organize tables and reduce redundancy and dependency. then link the tables with ERs

First normal form requirements

Meets criterion for relation and has a primary key

Second normal form requirements

Is in 1NF(is a relation and has a primary key), and all attributes/non-key columns are dependent on the primary key. Also must have single column primary key

Third normal form requirements

Is in 2NF, and no non-key attributes are dependent on other non-key attributes (attributes must only depend on primary key)

Boyce Codd form (BCNF) requirements

Is in 3NF and All determinant columns are candidate keys

What forms have functional dependencies

1NF, 2NF, 3NF, BCNF

What form solves multi valued dependencies

4NF- move each multi valued dependency to its own table

What forms have data constraints

5NF- make every constraint a logical consequence of candidate keys and domains

two properties of candidate keys

the attribute used for identification must remain the same and the value cant be null

relational DBMS products

store data about entities in relations, type of table

how to map ER to schema

create table

add primary keys

add attributes

declare primary key

declare foreign keys

composite attribute

attribute made of other attributes (an address is made of a street, city, county, etc)

multi valued attribute

can have more than one value

derived attribute

like how an age attribute can be derived from the DOB attribute

domain integrity constraint

requirement that all the values in a column are of the same kind

domain

grouping of data that meets specific criterion. ex: FirstName with domain Albert, James, etc…

4 parts of an uml diagram

class,attributes, functions, and additional components

codd relation in theory

rows must be unique but no primary key is required. in application though we always have a primary key

functional dependencies

tells how stuff is related. If cost=item*5 then item determines cost. the item is the determinant

composite functional dependencies

determinant that consists of more than one attribute

functional dependencies composition and union rule

A→(B,C) = A→B and A→C and if A→B and A→C then A→(B,C). but no rule if (A,B)→C.

a determinant in a relation is unique iff ____

it determines every other column. you cant find them just by looking at the unique values in one column

Entity integrity constraint

the primary key must has unique data in each row (and no nulls)

referential integrity constraint

if you have a foreign column it must already exist as primary key values, ie SKU is ODER_ITEM must already exist as SKU in SKU_DATA

data integrity

the data in our database will be meaningful and useful

update anomaly

when you update an entry in a row and it causes other parts to no longer be in date

process for putting a relation into BCNF

-identity functional dependencies

-identify candiate key

-if there is a dependency that has a determinant thats not candidate key turn it into a new table as the primary key

-make it a foreign key

-ref integrity constraint check

normalization steps

-check dependencies

-if attributes not dependent on key, make new table

-if dependencies between two non key attributes (for 3NF), make new tables for them

normalization achieves its most practical form in

3NF

multi valued dependencies def

when a determinant is matched with a particular set of values. like employee→degree employee→sibling

4nf solved multi valued dependencies by

putting each in their own relation