데이터베이스

Design theory for relational databases

GOOD DB SCHEMA DESIGN 하기 위해서.

K is a superkey if k functionally determines all of R.

K is a key if k is a superkey, but no proper subset of k is a superkey.

Closure : y+ = y -> look for an fd’s left side x that is a subset of the current y+

Nontrivial = right side not contained in the left.

q) a->b and b->c

a+ = abc, a->b, a->c

b+ = bc, b->c

goal of relational schema design is to avoid anomalies and redundancy.

Update anomaly : one occurrence of a fact is changed, but not all occurences.

Deletion anomaly : valid fact is lost when a tuple is deleted.

BCNF : Boyce-codd normal form

X->Y is a nontrivial and X is a superkey. <- 이런 상태가 bcnf.

3NF : Third normal form

Bcnf로 decomposition 하고 다시 조인하면 기존의 fd가 지켜지지 않을 수 있다.

Prime : member of any key

X->Y is a nontrivial and X is a superkey or Y is prime. <- 이런 상태가 3nf.

Loseless join : decompose 한 후에 reconstruct해도 original 이 되어야 한다.

Use the chase to show that row for the relation that contains a key can be made all-unsubscripted var.

Dependency preservation : 모든 fd가 만족해야 한다.

Each fd from a minimal basis is contained in a relation, thus preserved.

Testing for a loseless join = the chase test. (원래 없던게 생기는지 테스트)

1.     Right sides are single attributes.

2.     No fd can be removed.

3.     No attribute can be removed from a left side.

 

Multivalued dependencies

X->->Y if two tuples of R agree on all the attributes of X, then their components in Y may be swapped, and the result will be two tuples that are also in the relation.

Every fd is an mvd.

4nf : X->->Y is a nontrivial MVD, and X is a superkey. (trivial 이면 무시)

Nontirivial mvd : Y is not a subset of X, X and Y are not, together, all the attributes.

 

Entity-relationship model

Er model allows us to sketch database schema designs.

Include some constraints, but not operations.

Entitiy = “thing” or object

Entitiy set = collection of similar entities.

Attribute = property of an entity set.

-> many one, -) exactly one, - many many

In an isa hierarchy, only root entity set has a key.

Weak entity set : entities of an entity set need help to identify them uniquely. Double diamond, rectangle.

Supporting relationships must have a rounded arrow.

1.     Avoid redundancy. – wastes space, encourages inconsistency.

2.     Limit the use of weak entity sets

3.     Don’t use an entity set when an attribute will do.

It is more than the name of something, it has at least one nonkey attribute

It is the “many” in a many-one or many-many relationships.

If the relations for many-one relationships of which E is the “many”, ok to combine.

Weak entity sets = must include attributes for its complete key. Relationship은 제거.

Subclasses

1.     Object-oriented : one relation per subset of subclasses, with all relevant attributes.

2.     Use nulls : one relation; entities have null in attributes that don’t belong to them.

3.     Er styles : one relation for each subclass.

 

On-line application processing

OLTP : ONLINE TRANSACTION PROCESSING

OLAP : ONLINE APPLICATION PROCESSING

-       Few, but complex query, not depending on having absolutely up-to-date database.

Star schema – fact table(a very large accumulation on facts), dimension table(smaller, static information about the entities involved in the facts)

Fact table – dimension attributes(the key of a dimension table), dependent attributes(a value determined by the dimension attributes of the tuple)

Rolap(relational olap) : tune a relational dbms to support star schemas

Molap(multidimensial olap) : data cube, keys of dimension tables are the dimensions of a cube, dependent attributes appear at the points of the cube

Marginal : cube를 잘랐을 때 생기는 aggregation   ex) sales(“joe’s bar”, “bud”, *, *)

Drill down(break an aggregate), roll up(aggregate)

Select b1.item, b2.item from b1, b2 where b1.basket = b2.basket and b1.item < b2.item group by b1.item, b2.item having count(*) >= s;

A priori trick : select item from baskets group by item having count(*) >= s; 를 통하여 가능성이 없는 데이터들을 제거하고 위의 쿼리를 수행하여 속도 향상.

 

xml

Well formed xml : 자기 마음대로 태그

Valid xml : dtd를 사용한 xml

<?xml version =”1.0” standalone = “yes” ?> = dtd 사용하지 않겠다.

<!DOCTYPE BARS [

           <!ELEMENT BARS (BAR*)>

           <!ELEMENT PRICE (#PCDATA)>

]>

Dtd 사용 방법 : 1. Dtd를 xml document안에 적는다. 2. <!DOCTYPE BARS SYSTEM “bar.dtd”> 를 지정해 준다.

<!ELEMENT BAR (NAME, BEER*)>

           <!ATTLIST BAR kind CDATA #IMPLIED>

 

<!ELEMENT SELLS (#PCDATA)>

           <!ATTLIST SELLS theBeer IDREF #REQUIRED>

<!ELEMENT BEER EMPTY>

           <!ATTLIST BEER name ID #REQUIRED>

<SELLS theBeer = “Bud”/> -> ref <BEER name=”Bud”/>

Dtd가 별로이므로 xml schema 사용. More powerful way to describe the structure of xml documents.

<?xml version = …?> <xs:schema xmlns:xs=http://...></xs:schema>

<xs:element name=”NAME” type=”xs:string”/>

<NAME>Joe’s bar</NAME>

 

<xs:complexType name=”beerType”>

           <xs:sequence>

                     <xs:element name=”NAME” type=”xs:string” minOccurs=”1” maxOccurs=”1”/>

                     Occurs에 “unbounded “ 사용 가능. Unlimited.

           </xs:sequence>

</xs:complexType>

<xxx>

           <NAME>Bud></NAME>

</xxx>

 

<xs:complexType name=”beerType”>

           <xs:attribute name=”price” type=”xs:float” use=”optional”/> // use에 required.

</xs:complexType>

<xxx price=”2.50”/>

 

<xs:simpleType name=”license”>

           <xs:restriction base=”xs:string”>

                     <xs:enumeration value=”full”/>

           </xs:restriction>

-----------------------------------------------------------------------------------------

           <xs:restriction base=”xs:float” minInclusive=”1.00” maxExclusive=”5.00/> // 1<=x<5

</xs:simpleType>

 

<xs:key name=”barKey”> = BEER element안의 name attribute가 key가 됨.

           <xs:selector xpath=”BEER”/>       

           <xs:field xpath=”@name”/>

</xs:key>

<xs:keyref name=”barRef” refers=”barKey”> = DRINKER/FREQ element의 bar attr이 barKey ref함.

           <xs:selector xpath=”DRINKER/FREQ”/>     

<xs:field xpath=”@bar”/>

</xs:keyref>

 

sql

True = 1, False = 0, Unknown = 1/2

AND = MIN, OR = MAX, NOT(X) = 1-X

In, not in, exists, not exists, any, all, union, intersection, difference

(left – 왼쪽 보존, right, full) outer join x on

Create table drinkers( name char(30) primary key, addr char(50) DEFAULT ‘123’);

Insert into drinkers values (); Insert into drinkers (subquery);

Delete from drinkers where - ; Delete from drinkers where exists (subquery);

Update drinkers set -  where - ;

Create table sells ( beer char(20) references beers(name) );

Create table sells( beer char(20), foreign key(beer) references beers(name) on delete set null on update cascade );

Create table sells ( beer char(20) check (beer in (select name from beers))); - insert, update때만 체크

Create teble sells ( beer char(20), check (bar=’joe’ or price < 5.00)); - insert, update 때만 체크

Create assertion noripoffbars check (not exists (select bar from sells group by bars having avg(price)>5.0));

Create trigger beertrig after insert on sells referencing new row as newtuple

For each row when (newtuple.beer not in (select name from beers))

Insert into beers(name) values(newtuple.beer);

Create trigger pricetrig after update of price on sells referencing old row as ooo new row as nnn

For each row when (nnn.price > ooo.price + 1.00)

Insert into ripoffbars values (nnn.bar);

 

Transaction, views, indexes

Atomic : whole transaction or none is done.

Consistent : database constraints preserved.

Isolated : it appears to the user as if only one process executes at a time.

Durable : effects of a process survive a crash.

Set transaction isolation level x;

1.     Serializable

2.     Repeatable read – 처음 읽은 데이터와 같아야 함, 추가는 될 수 있음.

3.     Read committed

4.     Read uncommitted

Create [materialized] view <name> as <query>;

Create trigger viewtrig instead of insert on synergy referencing new row as n

For each row begin

Insert into *3;

End;

Create index beerindex on beers(manf);