CS 410 Midterm Review Chapter 1 Database - large collection of ...

gasownerData Management

Jan 31, 2013 (4 years and 9 months ago)

186 views

CS 410 Midterm Review

Chapter 1



Database
-

large collection of related data



Data


Known facts that can be recorded and have an implicit meaning



Database Management System (DBMS)


a software package system to facilitate the creation and
maintenance of a computerized database.



Database System


DBMS software together with the data itself. Sometimes the applications are
also included.



History

o

Hier
archical Data Model: by IBM, tree
-
based corresponding to a number of natural
hierarchically organized domains, little room for query optimization, navigational and
procedural nature of processing

o

Network Model: graph
-
based, models complex relationships and

represents semantics of
add/delete on the relationships, can handle most situations for modeling using record types
and relationship types. Navigational and procedural nature of processing, contains a complex
array of pointers and thread through a set of
records. Little room for automated “query
optimization”.

o

Relational Model



Proposed by E.F. Codd (1970, IBM)



First commercial system in 1981
-
82, now in several commercial products such as Oracle,
Sybase, Informix, etc.



Data should be presented to user as ta
bles (relations)



Queries expressed in a very high
-
level language



SQL (Structured Query Language)


most important language based on relational model

o

Object Oriented Model: one set comprises models of persistent O
-
O Proogramming Languages
such as C++ and Sm
alltalk, additionally systems like O2, Orion, etc

o

Object
-
Relational Model: most recent trend, exemplified in Oracle 10g, PostgreSQL 8.0



Three
-
Level Database Schema

o

Database Schema: Logical structures of the data. User Data Definition Language (DDL),
inclu
des
descriptions of the databse structures and the constraints that should hold on the database

o

Define Database Schemas as Three Levels:



Internal Schema:

to describe data storage structures and access paths at the internal level.
Typically uses a physical
data model



Conceptual Schema:
to describe the structure and constraints for the whole database at
the conceptual level. Uses a conceptual or an implantation data model.



External Schemas:
to describe the various user views at the external level.



DBMS
Components

o

Data Definition Language (DDL)


used by the DBA and database designers to specify the
conceptual schema of a database. IN some DBMSs, the DDL is also used to define internal and
external schemas (views), in some DBMSs, separate storage definiti
on language (SDL) and view
definition language (VDL) are use dto define internal and external schemas.

o

Storage Manager: Responsible for storing data, metadata, indexes and logs



Indexes are part of the data, their description is part of the metadata

o

File M
anager: keeps track of the location of files and disk and obtains the blocks containing the
requested data

o

Buffer Manager: keeps portions of the disk contents in main memory, obtains disk blocks form
the file manager, and tries to optimize the access to da
ta

o

Query Manager: Parse and optimize the query using a query compiler ( query parser, query
processor, and query optimizer)

o

Execution Engine: execute the resulting query plan (sequence of actions for the DBMS to
perform). Issues a sequence of requests to
s
torage manager

for small pieces of data. Interacts
with the

scheduler

to avoid accessing ‘locked’ data. Makes record of all database changes (
log
manager
)



Transaction Processing

o

Responsible for logging database changers to support recovery after a system c
rashes

o

Supports concurrent execution of transactions in a way that assures atomicity and isolation.

o

ACID properties of “proper” execution:



`
Atomicity: A transaction is performed either completely or not al all



Consistency: Transactions are expected to pres
erve the consistency (constraints) of the
database



Isolation: Each transcation, when executed concurrently with other transactions, should
have the same effect as if it had been executed by itself.



Durability: once a transaction has completed (commit succe
ssfully), its changes to the
database should be permanent. Even serious failures should not affect the permanence of
transactions.

o

Techniques to enforce ACID



Locking


granularity of locks is important



Logging


write a log to nonvolatile storage, assure
durability



Transaction Commitment, for durability and atomicity, transactions are computed
“tentatively”, recorded, but no changes are made to the DB until the transaction gets
committed. Changes are copied to the log, then copied to DB



When not to use a
DBMS:

o

If the database and applications are simple, well defined, and not expected to change

o

If there are stringent real
-
time requirements that may not be met because of DBMS overhead

o

If access to data by multiple users is not required.



Chapter 2

The E
-
R

Data Model



Ovals are attributes



Diamonds are relationships



Rectangles are entity sets



One



one



Many


one



Many


many



Entity is a record and object, entity set is a table and a class of object.



E
-
R Model is a static concept involving the structure of d
ata and not the operations on data



Entity set and attributes

o

Entity: rel
-
world object distinguishable from other objets. An entity is described (in DB) using a
set of attributes

o

Entity set: a collection of similar entities, like: all employees. Usually all

entities in an entity set
have the same et of attributes. (Until we consider ISA hierarchies, anyway!). Each entity set has
a key. Each attribute has a domain.



Relationships: association among two or more entities.
Binary relationships involve two entity

sets.
Relationship set: collection of similar relationships. An n
-
ary relationship set R relates n entity set
E1… En; each relationship in R involves entities e1 (E) E1, en (E) En. Same entity set could
participate in different relationship sets, or in di
fferent “roles” in same set



Look over the psql commands



Multiplicity of Binary E
-
R Relationships: Many
-
one, one
-
one, many
-
many.



Many
-
One

o

Some binary relationships are many
-
one from one entity set to another

o

Each entity of the first set is connected to at m
ost one entity of the second set

o

But an entity of second set can be connected to zero, one, or many entities of the first



One
-
One

o

Each entity of either entity set is related to at most one entity of the other set. If r is both
many
-
one from E to F and many
-
one from F to E, then R is one
-
one



Many
-
many

o

Think of a relationship between two entity sets, such as Sells between bars and beers.

o

In a many
-
many relationship, an entity of either set can be connected to many entitites of the
other set.

o

If R is neither

many

one from E to F or from F to E, then R is many
-
many



Multiway Relationships: Sometimes we need a relationship that connects more than two entity
sets. Supposed that drinkers will only drink certain beers at certain bars. Our three binary
relationship

Likes, Sells and Frequents do not allow us to make this distinkction, but a 3
-
way
relationship would.

o

Can be converted to a collection of binary, many
-
one relationships.

o

Introduce new entity set (connecting entity set). Entities are tuples of the relati
onship set for
the multiway relationship.

o

Introduce many
-
one relationships from the connecting entity sets to each of the entity sets
that provide components of tuples in the original multiway relationships.



Roles

o

Sometimes an entity set appears more th
an once in a relationship

o

Label the edges between the relationship and the entity set with names called roles.



Attributes on a relationship are sometimes useful, think of this attribute as a property of tuples in
the relationship set. Can also be represen
ted by another entity set.



Subclass: special case, fewer entities have more properties. Example is cartoon is a type of a movie.
Some movies are cartoons. IN addition to all the properties (attributes and relationships) of a
movies, cartoons also have an
additional relationship called Voices. In the object
-
oriented world,
objects are in one class only. Sublcasses inherit properties from superclasses. In contrast E/R
entities have components in all subclasses to which they belong. Matters when we convert r
elatns



Keys: Entities have attributes, the key is underlined.

o

Attributes which uniquely identify an entity in an entity set are called keys

o

An inherent property of the data(movie title and year), serve as a constraint.

o

Definition: attribute or combinatio
n of attributes that uniquely identifies individual entity of an
entity set. Simple keys vs. Composite keys.

o

Candidate Key: an attribute or combination of attributes) that could be a key, as in satisfies the
requirements for being a key.

o

In an ISA hierar
chy, only one root entity set has a key, it must serve as they key for all entities in
the hierarchy.

o

Criteria for selecting keys:



Will not change in value (time
-
invariance)



Will not be null/unknown



No intelligent identifiers (containing locations or peop
le that might change)



Substitute new, simple keys for long, composite keys



Design principles



Avoid redundancy, duplicate representations of data, lead to inconsistency, violates data integrity,
wastes space.



Entity sets vs

Attributes: An entity set should satisfy at least one of the following conditions: 1. It is
more than the name of something; it has at least one non
-
key attribute, 2. it is the many in a many
one or many many
relationship.



Constraints: part of the schema
, not of an instance of a database. Classification of Constraints:

o

Keys

o

Single
-
value constraints



Assertion: AT MOST one value exists in a given context



Ways to express single value constraints in the E/R are:



Each attribute of an enity set has a single va
lue



Some attributes may allow null, key attributes should not be null



No formal representation in E/R diagram, make a notation beside the attribute. Many


one relationship.

o

Referential integrity constraints



EXACTLY one value exists in a given role



Enfor
ced at database implantation: forbid the deletion of a referential entity, require that if
referenced entity is deleted then all entities that reference it are deleted as well.



Use a rounded arrow on the diagram

o

Domain constraints



Restrict the value of an

attribute to be in a limited set.



No formal notation in E/R. place a notation next to the attribute, for example declaring the
type an attribute or specify range for the attribute value

o

General constraints



Weak Entity Sets: an entity set whose key is com
posed of attributes some or all of which belong to
another entity set.

o

Entity set E is said to be weak if in order to identify entities of E uniquely, we need to follow
one or more many
-
one relationships from E and include the key(s) of the related entiti
es from
the connected entity sets.

o

A weak entity set has one or more many
-
one relationships to other (supporting) entity sets.
Not every many
-
one relationship from a weak entity set need to be supporting.

o

The key of a weak entity set is its own underline
d attributes and the keys for the supporting
entity sets. For example player
-
number and team
-
name is a key for players



Strong vs Weak Entities and Supporting relationships

o

Strong entities



Exist independently of other types of entities



Has its own unique
identifier



Represented with single
-
line rectangle

o

Weak entities



Dependent on a strong entity, cannot exist on its own



Does not have unique identifier

o

Supporting relationship



Links strong entities to weak entities



Represented with double line diamond



Weak
Entity Set Requirements

o

Key consists of zero or more of its own attributes

o

Key attributes from entity sets that are reached by supporting relationships (many
-
one)

o

If R is supporting relationship from a weak entity set E to some entity set F, R must:



Be a b
inary, any
-
one relationship from E to F



R must have referential integrity from E to F



The attri
b
u
tes that F supplies for the key of E must be key attributes of F



IF F is weak, then key attributes of F supplied to E may not be key attributes of more entity
set to which F is connected by a supporting relationship (recursive)



For multiple supporting relationship from E to F, each relationship is used to supply a copy
of the key attributes of F



Do Not Overuse Weak Entity Sets

o

Beginning database designers often
doubt that anything could be a key by itself


they make all
entity sets weak, supported by all other entity sets to which they are linked.

o

In reality, we usually create unique IDs for entity sets


examples include social security
numbers, automobile VIN
s, etc



When do we need weak entity sets?

o

Usual reason is that there is no global authority capable of creating unique IDs, for example: it
is unlikely that there could be an agreement to assign unique players numbers across all
football teams in the world



DB Design

o

Centers on entity sets and relationships making up the database.

o

Users and designers must work together to:



Specify the entity sets and relationships and their meanings



Specify the attributes and the meaning of it



Specify the constraints



Identif
y key attributes of entity sets



Specify how the data would be viewed



Specify who can view and modify what and when

o

Better to do a good job in the original design, need collaboration of users and designers

o

Reminder: Changing database design later is usuall
y very costly

Chapter 3

Relational Data Model



Why Relations?

o

Very simple model, often matches how we think about data, abstract model that underlies SQL,
the most important database language today. But SQL uses bags, while the relational model is a
set
-
bas
ed model.



Relational Model

o

Based on mathematically defined relations of entities

o

Consists of: Attributes (fields), domain legitimate values of attributes (data range)

o

Views of data presented in table format

o

Create new views by projections of the database

o

Records are n
-
tup
les, where n = # of attributes

o

User relational algebra operations to compute data

o

Result of the operations are also relations



Relational Database: set of relations

o

Made up of 2 parts:



Instance: a table, with rows and columns. #Rows = cardinality, # fields = degree / arity



Schema: specified name of a relation, plus name and type of each column/attribute/field

o

Can think of a relation as a set of rows or tuples (all rows are distinct)

o

Data
base scheme: set of relation schemas in the database



E/R Diagram to relational Design

o

Turn each entity set into a relation with the same set of attributes (E. G. Star (
name
, address))

o

Represent relationships by relations

o

Combining relations when appropriat
e

o

Handle weak entity sets

o

Convert subclass structures to relations



E/R Relationship


Relations

o

Replace a relationship by a relation (say R):



For each entity set involved in relationship R, take its key attribute(s) as part of the schema
of the relation fo
r R.



If the relationship has attributes, add to Rs attribute set



If one entity set has multiple roles in a relationship, then its key attributes appear as many
times as there are roles. Need to rename the attributes to avoid name duplication.



Discovering

Keys for Relations

o

If relation R is constructed from:



Entity set


key attributes of the entity set



Relationship R, key of R is:



Many to many
: composed of keys of both connected entity sets



Many
-
one from E1 to E2: consisted of key of E1



One
-

one between

E1 and E2: either the key of E1 or E2



Combining Relations

o

In general, if 2 or more relations have exactly the same keys, combine them

o

It is ok to combine the relation for an entity set E with the relation R for a many
-
one
relationship from E to another en
tity set.

o

For many
-
one relationship



E(
ea1
, ea2)



F(fa1,
fa2
, fa3



R(
ea1
, fa2, ra1)


key is from the many relation



Result: E(
ea1
, ea2, fa2, ra1)



Example: Drinkers(
name
, addr) and Favorite(
drinker
, beer) combine to make
Drinekr1(
name
, addr, favBeer)



For an e
ntity e of E that is not related to any entity in F, the attributes form R and F will
have null value in e.



Advantage: more efficient to answer queries involving attributes of one relation than to
answer queries involving attributes of several relations.



There is a risk creating redundancy



Handling Weak Entity Sets

o

Relation for the weak entity set includes its own attributes and the key attributes of the other
entity sets that help form its key: Contracts(
starName
,
studioName
,
title
,
year
, salary),
Crew(
n
umber
,
studioName
)

o

Weak entity set and supporting relationship set are translated into a single table.



Converting Subclass Structures (ISA) to relations

o

Strategies:



E/R approach

o

For each entity set E in the hierarchy, create a relation that includes the k
ey attributes form the
root and any attributes belonging to E

o

Do not create a relation for ISA relationship

o

Example:



Movies(
title
,
year
, length, filmType)



MurderMysteries(
title
,
year
, weapon)



Cartoons(
title
,
year
)



Voices(
title
,
year
,
StarName
)



OO approach

o

Treat entities as objects belonging to a single class


for each possible subtree including the
root, create one relation, whose schema includes all the attributes of all the entity sets of the
subtree

o

Example:



Movies(
title
,
year
, length, filmType)



MoviesC
(
title
,
year
, length, filmType)



MoviesMM(
title
,
year
, length, filmType, weapon)



MoviesCMM(
title
,
year
, length, filmType, weapon)



Voices(
title
,
year
,
starName
)

o

Then combine relations that have the same set of attributes.



Use null values

o

Create one relatio
n with all the attributes of all the entity sets in the hierarchy, each entity is
represented by one tuple, and that tuple has a null value for whatever attributes the entity
does not have.

o

Example: Movie(title, year, length, filmType, weapon); Voices(…)



C
omparison of the three approaches

o

Pros and Cons in each approach. Factors to consider:



Number of relations involved in a query (depends on the query of interest)



Total number of relations in the schema



Space usage

o

Examples:



“find the color of ales made by
Pete’s”



O
-
O approach is good. Just look in Ales relation



“find all beers (including ales) made by Pete’s”



E/R approach is good, just look in Beers relation.



Using nulls saves space unless there are lots of attributes that are usually null



Functional
Dependencies

o

X


A is an assertion about a relation R that whenever two tuples of R agree on all the
attributes of X, then they must also agree on the attribute A.



Say “X


A holds in R”



Notice convention: …, X, Y, Z represent sets of attributes; A, B, C,

… represent single
attributes.



Convention: no set formers in sets of attributes, just ABC, rather than {A, B, C}.

o

Drinkers (name, addr, beersLiked, manf, favBeer)

o

Reasonable FDs to assert:



Name


addr



Name


favBeer



beersLiked


manf

o

Example (Movie): tit
le, year


length

o

Splitting/Combining rule



FDs



A1A2…An


B1B2…Bm I



A1A2…An

B1 II



A1A2…An

Bm II



Splitting Rule: replace FD1 by the set of FDs II



Combining Rule: replace the set of FDs II by FD I

o

Trivial Depe
ndency Rule



FD A1A2..An


B is trivial if B is one of the A’s



Every trivial dependency holds in every relation



For FD A1A2…An


B1B2…Bm



Trivial if the Bs are a subset of the As (like title year


title)



Nontrivial if at least one of the Bs is not among the As



Completely nontrivial if none of the Bs is also one of the As



Trivial Dependency Rule:



A1A2…An


B1B2…Bm = A1A2…An

C1C2…Ck where the Cs are all those of Bs that
are not also As

o

Armstrong’s Axioms:

S
tudy the slide



Keys of Relations

o

K is a key for a relation R if:



1. Set K functionally determines all attributes of R (superkey)



2. For no proper subset of K is (1) true. (minimality)

o

If K satisfies (1), but perhaps not (2), then K is a superkey



1. Any key

is a superkey!



2. Example:{title, year} forms a key for the movie relation

o

Note E/R keys have no requirement for minimality, as in (2) for relational keys

o

Example:



Consider relation Drinkers(name, addr, beersLiked, manf, favBeer)



{name, beersLiked} is a s
uperkey because together these attributes determine all the other
attributes.



Name


addr favBeer



beersLiked


manf



{name, beersLiked} is a key because neither {name} nor {beersLiked} is a suprkey.



Name doesn’t


manf;



beersLiked doesn’t


addr.



In this example there are no other keys, but lots of superkeys, any superset of {name,
beersLiked}



Where Do Keys Come From?

o

We could simply assert a key K. Then the only FDs are K


A for all attributes A, and K turns out
to be the only key obtainable from

the FDs

o

We could assert FDs and deduce the keys by systematic exploration.

o

E/R gives us FDs form entity
-
set keys and many
-
one relationships

o

Example :”no two courses can meet in the same room at the same time” tells us
{hour, room}


C
ourse



Inferring FDs Motivation

o

In order to design relation schemas well, we often need to tell what FDs hold in a relation.

o

We are given FDs X1


A1, X2


A2, … , Xn

An, and we want to know whether an FD Y


B
must hold in any relation that satisfies the
given FDs (Y


B follows the given FDs)

o

Example: If A


B and B


C hold, surely A


C holds, even if we don’t say so.



Closure Test

o

An easier way to test is to compute the closure of Y, denoted Y+.

o

Basis: Y+ = Y.

o

Induction: Look for an FD’s left side X
that is a subset of the current Y+. If the FD is X


A, add A
to Y+.

o

Example:



A relation with attributes A, B, C, D, E, F.



FDs:



AB

C



BC


AD



D


E



CF


B



What is {A,B}+



{A,B}+ = {A, B, C, D, E}

o

To Test if a FD follows a set of FD in a relation



Does D


A follow from the dependencies in the Previous example?



Compare {D}+ , if A ends up in the closure, then D


A follows from the dependencies



But {D}+ = {D,E},.: D


A does not follows.

o

Finding all implied FDs

o

Motivation: “normalization,” the process weh
re we break a relation schema into two or more
schemas:



ABCD with FDs:



AB


C



C


D



D


A



Decompose into ABC, AD, what FDs hold in ABC?



Not only AB

C, but also C

A!

o

Basic Idea



To know what FDs hold in a projection, we start with given FDs and find all FDs

that follow
from given ones



Then restrict to those FDs that involve only attributes of the projected schema.

o

Simple Exponential Algorithm



For each set of attributes X, compute X+.



Add X


A for all A in X+
-

X.



However, drop XY


A whenever we discover
X


A because XY


A follows form X


A



Finally, use only FDs involving projected attributes.



Read Examples



Database Design Problems

o

Principal kinds of anomalies that constitute bad DB design:



Redundancy



Update Anomalies



Deletion Anomalies



Insertion
Anomalies

o

Want to find a good relation schema design for the relational model



Redundancy

o

Information repeated unnecessarily in several tuples

o

Example:



Drinkers (
name
, addr,
beersLiked
, manf, favBeer)



Data is redundant, because each address and favBeer can

be figured out by using the
FDs name


addr favBeer and Each Manf can be determined by beersLiked


manf



Update Anomalies

o

Change information in one tuple but leave the same information unchaged in another

o

A consequence of redundancy

o

Cause potential incons
istency

o

Example: change the address of ‘Jane’ in one tuple but not the others



Deletion Anomalies

o

A set of values becomes empty (deleted), may lose other inofmration as a side effect

o

Example: Drinkers(
name
, addr,
beersLiked
, manf, favBeer)



If nobody likes
Bud, we lose track of the fact that Anheuser
-
Busch manufactures Bud.



Insertion Anomalies

o

Cannot insert a tuple because some of the data not yet available

o

Inverse to deletion anomalies

o

Problem of using null value to fill the missing / unavailable data:



When the data becomes available, will we remember to update the one with nulls



If the missing data is part of a key, then cant use null

o

Example: Cannot start to keep track of the information of a new beer if nobody likes it



Normalization

o

Purpose: process t
o eliminate redundancy in relations due to functional or multi
-
valued
dependencies.

o

Normal Forms:



Boyce
-
Codd Normal Form (BCNF)

o

Definition: a relation R is in BCNF if whenever there is a nontrivial dependency A1A2…An


B
holds for R, {A1, A2, … , An} must
be a superkey for R

o

Alternative definition: after applying the combining rule of all FDs with a common L.S.:



A relation R is inBCNF iff whenever nontrivial dependency A1A2…An


Bm holds for R, {A1,
A2, … , An} must be a superkey for R

o

Guarantees no redunda
ncy caused by FD, prevents update, deletion, and insertion anomalies.

o

Example:



Drinkers (
name
, addr,
beersLiked
, manf, favBeer)



FDs: name


addr favBeer, beersLiked


manf



Only key is {name, beersLiked}.



In each FD, the left side is not a superkey.



Any on
e of these FDs shows drinkers is not in BCNF

o

Decomposition to BCNF



Given: relation R, FDs F.



Decomposition Strategy:



1. Look for a non
-
trivial FD A1A2…An


B1B2…Bm that violates BCNF. (Heuristic : add to
the RS as many attributes as are functionally determ
ined by A1A2…An)



2. Compare {A1,A2,…, An}+ //all the attributes involved in the violating dpendency



3. Decompose R into



R1: {A1A2…An}+



R2: (R
-
{A1, A2, …, An}+
)U{A1A2,…,An} // LS+ all attributes not involved in the violating
dependency



R
-
X+ is R2 together
with X



X is R1 together with X+
-
X



All of the above are part of R



Project given FDs F onto the two new relations



1. Compute the closure of F = all nontrivial FDs that follow from F.



2. Use only those FDs whose attributes are all in R1 or all in R2



Example:

Drinkers(
name
, addr,
beersLiked
, manf, favBeer)



FD = name


addr fav Beer, beersLiked


manf



Pick BCNF violation name


addr favBeer.



Close the left side : {name}+={name, addr, favBeer}



Decomposed relations:

o

1. Drinkers1(
name
, addr, favBeer)

o

2. Drinkers2(
name
,
beersLiked
, manf)



We are not done: we need to check Drinkers1 and Drinkers2 for BCNF



Projecting FDs is complex in general, easy here



For Drinker1(
name
, addr, favBeer), relevant FDs are name

addr, and name

favBeer

o

Thus, name is the only key and
Drinekrs1 is in BCNF



For Drinker2(
name
,
beersLiked
, manf), the only FD is beersLiked


manf, and the only
key is {name, beersLiked}.

Violation of BCNF



beersLiked+ = {
beersLiked, manf}, so we decompose Drinekrs2 into:

o

Drinekrs3(
beersLiked
, manf)

o

Drinkers4
(
name
,
beersLiked
)



The resulting decomposition ofDrinkers:

o

Drinkers1(
name
, addr, favBeer)

o

Drinkers3(
beersLiked
, manf)

o

Drinekrs4(
name
,
beersLiked
)



Notice: Drinkers1 tells us about drinkers, Drinkers 3 tells us about beers, and
Drinkers4 tells us the relatio
nship between drinkers and the beers they like

o

Additional points about BCNF



A relation may have more than 1 key



Need some key in the LHS of any nontrivial FD



Only nontrivial FDs are potential BCNF violation candidates



Any 2
-
attribute relations is in BCNF



Decomposition must be based on FD that holds in the relation otherwise cant recover the
original relation form the new relations



Eliminates all redundancies due to FD



Some decompositions may not preserve the FDs

o

Recovering Information from a Decomposition



R (A, B, C ) with B


C, which suppose is a BCNF violation



It is possible to have a transitive dependency chain, with another FD A


B

o

A is the only key, and B


C violates BCNF



Another possibility is that B


C is the only nontrivial FD

o

The only key is AB, and B


C violates BCNF.



In either case, the required decomposition based on the FD B


C separates the
attributes into schemas AB and BC



If we decompose a relation according to the BCNF method, then the original relation can
be recover
ed exactly by joining the tuples of the new relations in all possible ways



If we decompose relations in a way that is not based on a FD, then we might not be able to
recover the original relation



Consider a relation with schema R(ABCD) and FDs AB


C, C


D, and D

A

o

Singleton:



A+ = A, B+ = B,



C+ = ACD, and D+ = AD

o

Doubleton:



AB+ = ABCD, AC+= ACD,



AD+ = AD, BC+ = ABCD, BD+ = ABCD, CD+ = ACD

o

Triplet:



ACD+ = ACD



(ABC+= ABCD, BCD+ = ABCD, ABD+ = ABCD)



Third Normal Form (3NF)



Fourth Normal Form (4NF)