Non-First Normal Form Computer Science Department

Professorship of Data Management Systems

Submitted by: Pavlo Petrenko

Advisors: Prof. Dr. W. Benn, Dipl.-Inf. J.Fliege Implementation on PostgreSQL GUI for Managing Books Technologies Architecture Demo Implementation of nest and unnest Extension of

arithmetic operators (=, !=) for sets Operators for set inclusion Justification for choosing

the programming language Problem Definition NFNF Definition Example NFNF Introduction Typical tasks of a DBMS

Shortcomings of the first normal form

Expensive JOIN-operation

Redundancy

Repetition Not normalized relation Allow sets and sets of sets to be attribute domains

Support of nest and unnest operations

Extension of relational algebra with

arithmetic comparison symbols (=, <, >, etc.)

set inclusion symbols (is subset, is proper subset, etc)

logic symbols (conjunction, disjunctions, etc.) PL/pgSQL advantages:

fully featured programming language, allows much more procedural control than SQL, including the ability to use loops and other control structures.

Extra round trips between client and server are eliminated.

Multiple rounds of query parsing can be avoided

Possible to use all the data types, operators and functions of SQL SELECT ARRAY[‘Knuth’, ‘Codd’] = ARRAY[‘Codd’, Knuth’]

=> false

SELECT set_equals_to(ARRAY ['Knuth’, ‘Codd’], ARRAY[‘Codd’, Knuth’] => true Before nest Back-end: PostgreSQL/PL pgSQL

Web services: SOAP (WCF.NET)/C#

Front-end: ASP.NET/C# Set-based search

Strict search

Search with relative complement Usage of set-valued attributes

in relations Set-valued select operations First normal form Books Authors Array of text (text[]) is taken as a container of nested attributes.

Advantages:

Any type can be converted to it

Out-of-box functions for working with text[] (e.g. <@ - "is contained by"-operator)

Disadvantages:

Caller of unnest-operation must be aware of the nested data type

Nesting of nesting types may be challenging:

Possible solution: encoding Use of sort operation Subset Trivial case

SELECT ARRAY[‘Charles Leiserson’] <@ ARRAY[ ‘Charles Leiserson’, ‘Clifford Stein’, ‘Thomas Cormen’ , ‘Ronald Rivest’] returns true

Problem

Nesting of middle names, e.g. Steven L. Coates

Solution 1

SELECT ARRAY[‘Charles Leiserson’] <@ (‘Charles$#Leiserson’, ‘Clifford$#Stein’, ‘Thomas$#Cormen’ , ‘Ronald$#Rivest’) returns false

Solution 2

RETURN $1 <@ parse_nested ($2); Is subset

Is proper subset

Union

Intersection

Relative Complement Proper Subset RETURN arg0 <@ parse_nested(arg1) AND arg0 <> parse_nested(arg1);

Operator <#@= Union Possible implementations

RETURN parse_nested($1) || parse_nested($2);

Using SQL UNION operator

Operator + Intersection RETURN array(SELECT unnest($1) INTERSECT (SELECT unnest($2)));

Operator * Relative Complement (\) RETURN array(SELECT unnest($1) EXCEPT (SELECT unnest($2)));

The allowed symbols, specified in the documentation are the following: + - * / < > = ~ ! @ # % ^ & | ` ?.

Operator - Operator <#@ After nest SELECT "ISBN", "Title", "Price", "Category", "NestedName" FROM "N1NF" WHERE ARRAY['Wall', ‘Christiansen’] <#@= "NestedName"

### Present Remotely

Send the link below via email or IM

CopyPresent to your audience

Start remote presentation- Invited audience members
**will follow you**as you navigate and present - People invited to a presentation
**do not need a Prezi account** - This link expires
**10 minutes**after you close the presentation - A maximum of
**30 users**can follow your presentation - Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

### Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.

You can change this under Settings & Account at any time.