Set Operations

• Set Operations - Union, Intersect, Minus

This video discusses how to combine two sets of results together in SQL. The following SQL keywords are covered: UNION, UNION ALL, INTERSECT, MINUS, and EXCEPT.
The MINUS and EXCEPT commands are the same. Some databases use MINUS while other databases use EXCEPT.
http://www.1keydata.com/sql/union-intersect-minus-video.html

•  difference between union,union all,intersect,minus

The purpose of the SQL UNION ALL command is to combine the results of two queries together
UNION vs UNION ALL
UNION and UNION ALL both combine the results of two SQL queries. The difference is that, while UNION only selects distinct values, UNION ALL selects all values.
http://www.1keydata.com/sql/sqlunionall.html

• The purpose of the SQL UNION query is to combine the results of two queries together. In this respect, UNION is somewhat similar to JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need to be of the same data type. Also, when using UNION, only distinct values are selected

http://www.1keydata.com/sql/sqlunion.html

• Similar to the UNION command, INTERSECT also operates on two SQL statements. The difference is that, while UNION essentially acts as an OR operator (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator (value is selected only if it appears in both statements).

http://www.1keydata.com/sql/sql-intersect.html

• The MINUS command operates on two SQL statements. It takes all the results from the first SQL statement, and then subtract out the ones that are present in the second SQL statement to get the final answer. If the second SQL statement includes results not present in the first SQL statement, such results are ignored.

http://www.1keydata.com/sql/sql-minus.html

opensource GEO

• PostGIS is a spatial database extender for PostgreSQL object-relational database

http://postgis.net/

• GeoServer is an open source server for sharing geospatial data

http://geoserver.org/

• A high-performance, feature-packed library for all your mapping need

http://openlayers.org/

ER diagram

6. For the following Presidential ER diagram develop the relational schema using the pattern TableName(attribute-list).


http://jcsites.juniata.edu/faculty/rhodes/dbms/funcdep.html

• 2. Principles of mapping ER diagrams to relational schemas. Fill in the blanks with relation and attribute names.

a. If you have an entity E with attributes A, B and C in your ER diagram, with A as the primary key, what is its corresponding relation in relational schema? [3]
a.  ____E____ (    ____A, B, C_____________  )

b. If entity F has attributes  G, H and I with G as the primary key, but is related to entity E in a 1-many relationship, what is its corresponding relation? [4]
b.  ___F____ (    _____G, H, I, A________________  )

c. If entity L with attributes M, N and P with M as the primary key, and it is related to entity E in a many-many relationship called R, what is the corresponding relation that properly establishes the relationship?  [4]

                  ___R____ (    _______A,M__________________  )


jcsites.juniata.edu/faculty/rhodes/dbms/exams/mid2f13key.docx

• Limitations of E-R Designs

E-R modeling provides a set of guidelines, but does not result in a unique database schema.
Normalization theory provides a mechanism for analyzing and refining the schema produced by an E-R design, or any other design.
http://jcsites.juniata.edu/faculty/rhodes/dbms/funcdep.html

Functional Dependency

• Example Functional Dependencies

Let R be
NewStudent(stuId, lastName, major, credits, status, socSecNo)

FDs in R include

    {stuId}→{lastName}, but not the reverse
    {stuId} →{lastName, major, credits, status, socSecNo, stuId}
    {socSecNo} →{stuId, lastName, major, credits, status, socSecNo}
    {credits}→{status}, but not {status}→{credits}

ZipCode→AddressCity

    16652 is Huntingdon’s ZIP

ArtistName→BirthYear

    Picasso was born in 1881

Autobrand→Manufacturer, Engine type

    Pontiac is built by General Motors with gasoline engine

Author, Title→PublDate

    Shakespeare’s Hamlet was published in 1600

Trivial Functional Dependency

The FD X→Y is trivial if set {Y} is a subset of set {X}

Examples: If A and B are attributes of R,

    {A}→{A}
    {A,B} →{A}
    {A,B} →{B}
    {A,B} →{A,B}

are all trivial FDs and will not contribute to the evaluation of normalization.

http://jcsites.juniata.edu/faculty/rhodes/dbms/funcdep.html

•  (Rel. DB Design) Consider the EMP_PROJ relation schema with the attributes SSN, PNUMBER, HOURS, ENAME, PNAME, PLOC (project location). The following set of functional dependencies hold on this schema:

SSN->ENAME
PNUMBER -> PNAME,PLOC
SSN,PNUMBER -> HOURS.

(5) Compute the closure of {SSN,PNUMBER} from these functional dependencies.
SSN,PNUMBER->ENAME, PNAME, PLOC, HOURS.

(1) What is a candidate key for this relation schema?
   Candidate key is {SSN, PNUMBER}.

(4) Is this schema in BCNF? State one key problem with this schema.
It is not in BCNF because SSN->ENAME is not a superkey dependency.
     Therefore, all tuples containing the same SSN will also have the same ENAME, causing redundancy.
This results in waste of space and problems with updating the data consistently.

(10) Decompose this schema into BCNF or 3NF (your choice).
3NF decomposition: {SSN, ENAME},  {PNUMBER, PNAME, PLOC},  {SSN, PNUMBER, HOURS}
     BCNF decomposition: same.

http://cs.nyu.edu/courses/spring00/G22.2433-001/answers.html

• 5. For parts a-c, assume we have a relation with the scheme

Book (Title, Author, Publisher, PubAddress, PubZip, CopyrightYear, ISBN )
//ISBN = International Standard Book Numbers

a.  What would be the likely primary key attribute(s)? ________ISBN____________________[2]

b. List all non-trivial functional dependencies [7]?

ISBN -> {Title, Author, Publisher, PubAddress, PubZip, CopyrightYear }

{Title, Author} ->{ Publisher, PubAddress, PubZip, CopyrightYear, ISBN}

Publisher -> PubAddress, PubZip


c. If this relation were used as defined (not normalized), describe the insertion and deletion anomalies that could arise. [3]
A publisher’s address cannot be stored additionally without at least a book
A publisher’s address is replicated and if changed, would have to update many records




http://jcsites.juniata.edu/faculty/rhodes/dbms/funcdep.html

• FD Axioms

Understanding: Functional Dependencies are recognized by analysis of the real world; no automation or algorithm.
Finding or recognizing them are the database designer's task.

Axiom Name      Axiom  Example
Reflexivity    if a is set of attributes, b ⊆ a, then a →b    SSN,Name → SSN
Augmentation    if a→ b holds and c is a set of attributes, then ca→cb  SSN → Name then
SSN,Phone → Name, Phone
Transitivity    if a →b holds and b→c holds, then a→ c holds    SSN →Zip and Zip → City then SSN →City
Union or Additivity *  if a → b and a → c holds then a→ bc holds      SSN→Name and SSN→Zip then SSN→Name,Zip
Decomposition or Projectivity*  if a → bc holds then a → b and a → c holds      SSN→Name,Zip then SSN→Name and SSN→Zip
Pseudotransitivity*    if a → b and cb → d hold then ac → d holds      Address → Project and Project,Date →Amount then Address,Date → Amount
(NOTE)  ab→ c does NOT imply a → b and b → c

*Armstrong's Axioms (basic axioms)


http://jcsites.juniata.edu/faculty/rhodes/dbms/funcdep.html

• Consider relation R = (A, B, C, D) and the following statements for functional dependencies.

For each statement, if it is true, prove it. Otherwise, show a counter-example to disprove it.
(4 points)
(a) If A → B and A → C, then A → BC (2 points)
⇒ Answer: True.
For A → B, then A → AB (augmentation rule);
For A → C, then AB → BC (augmentation rule);
Then A → BC (transitivity rule)
(b) If A → B and C → D, then AC → BD (2 points)
⇒ Answer: True.
AC → A (reflexivity rule);
For A → B, s.t. AC → B (transitivity rule);
AC → C(reflexivity rule);
For C → D, s.t. AC → D (transitivity rule);
So AC → BD
Rubric: Each correct proof gets two points. If the answer is FALSE, the student gets zero
points

webcache.googleusercontent.com/search?q=cache:8vRupC7L9OYJ:https://wiki.engr.illinois.edu/download/attachments/227743489/CS411-F2011-Final-Sol.pdf%3Fversion%3D1%26modificationDate%3D1380470739000+&cd=3&hl=tr&ct=clnk&gl=tr&client=firefox-a

• Schema Design Example

Problem 2 (10 points) Schema Design Using the following database description,
create a relational schema. Remember to identify primary keys and foriegn keys correctly. Select
approaches that yield the fewest number of relations. Remember to state any assumptions you use
while creating the schema.
Let’s create a movie database, similiar to what IMDB would use.
• A movie has an ID number, a release date, a title, and a running time.
• A movie has a number of people that work on it, including 1 director and 1 producer.
• A movie has many actors. Each actor has a character name for a particular movie.
• Each person has an ID Number, a real name, a birthday, and an address
• Our website will have users that log on. These users are different from the people associated
with the movies.
• Each user had a unique logon name and a password.
• A user can leave reviews of movies. They can only leave one review per movie, but can review
as many movies as they like.
Solution
Movie(MovieID,ReleaseDate,Title,RunningTime,DirectorID,ProducerID)
Person(PersonID,Name,Birthday,Address)
Actors(MovieID,PersonID,CharacterName)
Users(UserName,Password)
Reviews(UserName,MovieID,Review)
Foriegn Keys: Movie.DirectorID is a foriegn key to Person. Movie.ProducerID is a foriegn key
to Person. Actors.MovieID is a foriegn key to Movie. Actors.PersonID is a foriegn key to Person.
Reviews.UserName is a foriegn key to Users. Reviews.MovieID is a foriegn key to Movie.

webcache.googleusercontent.com/search?q=cache:8vRupC7L9OYJ:https://wiki.engr.illinois.edu/download/attachments/227743489/CS411-F2011-Final-Sol.pdf%3Fversion%3D1%26modificationDate%3D1380470739000+&cd=3&hl=tr&ct=clnk&gl=tr&client=firefox-a

• For the remaining questions, use the following relational schema for a music albums database.  Keys are (mostly) underlined.  The attributes should be self-evident.  If not, please ask for clarification.  For a given music track, we code the title, its play length in time (minutes:seconds), its genre (pop, metal, jazz, etc.) and a 5 star maximum rating.  The musicians, singers and instrumentalists are all listed in on their contribution to the track.  A person may have 1 or more listing for a track. For example someone may both sing and play the piano.  The album is a collection of tracks.  An album is distributed and owned by a company called the label and has a producer and an engineer.

PEOPLE (PID, name, address, zip, phone)
CSZ (zip, city, state)
TRACKS (trID, title, length, genre, rating, albID) //trID is unique across all albums
ALBUMS (albID, albumTitle, year, label, prodPID, engPID, length, price)
CONTRIBS (trID, PID, role)


a) List all names and phone numbers of people from zip 90210. [5]


SELECT P.name, P.phone
        FROM PEOPLE P
        WHERE zip = ‘90210’;  --may or may not be quoted, alias not necessary


        b) List album titles and labels and producer names with a list price of more than $18. [5]


SELECT A.albumTitle, A.label, A.price, P.name
FROM Albums A, People P
WHERE A.price > 18 AND A,prodPID=P.PID

c) List all the musicians by name and what they played or contributed to on all jazz type tracks. [5]

SELECT P.name, C.role
FROM TRACKS T NATURAL JOIN CONTRIBS C
      NATURAL JOIN PEOPLE P
WHERE T.genre = ‘JAZZ’


d) Get a list of names and addresses of people who produced OR engineered an album, but did not perform on any track.  (Hint: subselect and set operations are very helpful). [6]



SELECT P.name, P.address, Z.city, Z.state, Z.zip
FROM PEOPLE P
WHERE P.PID IN
  (SELECT A.prodPID
    FROM ALBUMS A
      UNION
  SELECT B.engPID
    FROM ALBUMS B
    EXCEPT
  SELECT C.PID
    FROM CONTRIBS
)


e) List names of musicians who have contributed in at least two different roles on the same tracks with ratings 4 or higher. (Use group by… having and not a self-join). [6]



SELECT P.name
FROM PEOPLE P NATURAL JOIN CONTRIBS C NATURAL JOIN TRACKS T
WHERE T.rating>4
GROUP BY C.trID, C.PID
HAVING COUNT(DISTINCT C.role)>=2




f) What is the average price of albums for each year of release (show years), but only for albums with 6 or more tracks and length of 30 or more minutes. (Need a subselect and group by having.) [8]

SELECT AVG(A.price), A.year
FROM ALBUMS A
WHERE A.length >=30 and A.albID IN
    (SELECT T.albID
      FROM TRACKS T
      GROUP BY T.albID
      HAVING COUNT (*)>=6
)
GROUP BY A.years


jcsites.juniata.edu/faculty/rhodes/dbms/exams/mid2f13key.docx

data independence in relational model

• According Codd's 12 rules, there are two kinds of data independence:

Physical Data Independence requires that changes at the physical level (like data structures) have no impact in the applications that consume the database. For example, let's say you decide to stop using a Hash Index in your table and decide to use a B-Tree Index instead: Your application that executes queries against this table doesn't have to change at all.

Logical Data Independence states that changes at the logical level (tables, columns, rows) will have no impact in the applications that access the database. As you already noticed, this feature is harder to implement that Physical Data Independence but there are still cases when this feature works. For example, if you add Tables, Columns or Rows to your current scheme the already working queries aren't affected at all.
http://stackoverflow.com/questions/10861501/data-independence-in-relational-database

• Logical Data Independence

Logical data independence is a kind of mechanism, which liberalizes itself from actual data stored on the disk. If we do some changes on table format it should not change the data residing on disk.
Physical Data Independence
All schemas are logical and actual data is stored in bit format on the disk. Physical data independence is the power to change the physical data without impacting the schema or logical data.
For example, in case we want to change or upgrade the storage system itself, that is, using SSD instead of Hard-disks should not have any impact on logical data or schemas.
http://www.tutorialspoint.com/dbms/dbms_data_independence.htm

HSQLDB

• HSQLDB (HyperSQL DataBase) is the leading SQL relational database engine written in Java. It offers a small, fast multithreaded and transactional database engine with in-memory and disk-based tables and supports embedded and server modes. It includes a powerful command line SQL tool and simple GUI query tools.

http://hsqldb.org/

• HSQLDB (Hyper Structured Query Language Database) is a relational database management system written in Java. It has a JDBC driver and supports a large subset of SQL-92 and SQL:2008 standards.[1] It offers a fast,[2] small (around 1300 kilobytes in version 2.2) database engine which offers both in-memory and disk-based tables. Both embedded and server modes are available for purchase.

Additionally, it includes tools such as a minimal web server, command line and GUI management tools (can be run as applets), and a number of demonstration examples. It can run on Java runtimes from version 1.1 upwards, including free Java runtimes such as Kaffe.

HSQLDB is available under a BSD license. It is used as a database and persistence engine in many open source software projects, such as OpenOffice Base, LibreOffice Base, and the Standalone Roller Demo,[3] as well as in commercial products, such as Mathematica or InstallAnywhere (starting with version 8.0)
http://en.wikipedia.org/wiki/HSQLDB

• 1. The dialect is set to the database we are using which is HSQLDB

2. The JDBC driver is also set to HSQLDB
3. We set the database to one called testdb and request that the database be shutdown when our program exits
4. The default user name and passwords are used (change if yours differs)
5. I use the create-drop option to create the database and table(s) if they doesn’t exist, if they do they are dropped leaving me with a clean database each time I run the program. Other options are: create, update and validate

http://www.giantflyingsaucer.com/blog/?p=2902

• querying using hsql database manager :

select your connection

    type: HSQL DATABASE ENGINE SERVER
    Driver: jdbc.hsqldb.jdbcDriver
    URL: jdbc:hsqldb:hsql://localhost/

What is MultiValue Database?

• What is MultiValue Database?

MultiValue is a type of NoSQL and multidimensional database, typically considered synonymous with PICK, a database originally developed as the Pick operating system.
These databases differ from a relational database in that they have features that support and encourage the use of attributes which can take a list of values, rather than all attributes being single-valued.
Unlike SQL-DBMS tools, most MultiValue databases can be accessed both with or without SQL.

Data model example
In a MultiValue database system:
a Database is called an "Account"
a Table is called a "file"
a Column is an "Attribute" or "Dictionary": attributes generally point at raw data, while dictionaries apply some transformation to the raw data

For example, assume there's a file (table) called "PERSON". And in this file there is a Dictionary (column) called "eMailAddress". The eMailAddress field can store a variable number of email address values in the single record.
So the list [joe@abc132.info, jdb@gbmail.net, joe_bacde@thisorthat.edu] can be stored and accessed via a single query / disk read when accessing the associated record.

To achieve the same (1-to-many) relationship within a Relational Database system one would be required to create an additional table to store the variable number of email Addresses associated to a single "PERSON" record.
http://en.wikipedia.org/wiki/MultiValue

jbase database

jbase database
jBase is a MultiValue Database software developed by jBASE International
http://www.jbase.com/

PostgreSQL

PL/pgSQL
PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language) is a procedural programming language supported by the PostgreSQL ORDBMS. It closely resembles Oracle's PL/SQL language.
http://en.wikipedia.org/wiki/PL/pgSQL

mysql installation

MySQL community server database,  mysql connector, mysql workbench download

MySQL Community Server: http://dev.mysql.com/downloads/mysql/


MySQL Connector/J : http://dev.mysql.com/downloads/connector/j/


MySQL Workbench : http://dev.mysql.com/downloads/workbench/


mysql connection glass fish server config

Copy the MySQL Connector/j jar file into your glassfish lib folder.
On windows, this folder is located at ‘glassfish installation location’\glassfish\modules.

http://www.greenkode.com/2011/08/install-and-configure-mysql-for-eclipse-and-oracle-glassfish-3-1/



mysql connection pool-glassfish-eclipse config

https://blogs.oracle.com/davisn/entry/create_mysql_jdbc_connection_pool

• Download and Install MySQL

For Windows

    Download MySQL from www.mysql.com ? Select top-level tab "Downloads (GA)" ? MySQL Community Edition (GPL):
        Under "MySQL Community Server (GPL)" ? Select DOWNLOAD.
        Under "General Available (GA) Release", "MySQL Community Server 5.6.{xx}" (where {xx} is the latest upgrade number) ? In "Select Platform", Select "Microsoft Windows".
        Download the 32-bit or 64-bit ZIP Archive (mysql-5.6.{xx}-win32.zip or mysql-5.6.{xx}-winx64.zip, about 210 MB).
        You can check whether your Windows is 32-bit or 64-bit from "Control Panel" ? System ? System Type.
        There is NO need to "Sign-up" - Just click "No thanks, just start my downloads!".
    UNZIP into a directory of your choice. DO NOT unzip into your desktop (because it is hard to locate the path). I suggest that you unzip into "d:\myProject" (or "c:\myproject" if you do not have a D drive). MySQL will be unzipped as "d:\myProject\mysql-5.5.{xx}-win32". For ease of use, we shall shorten and rename the directory to "d:\myProject\mysql".


I recommend using the "ZIP" version, instead of the "Windows Installer" version for academic learning. You can simply delete the entire MySQL directory when it is no longer needed (without running the un-installer). You are free to move or rename the directory. You can also install (unzip) multiple copies of MySQL in the same machine on different directories.

(For Advanced Users Only) A better approach is to keep the original folder name, such as mysql-5.6.{xx}-win32, but create a symlink called mysql via command "mklink /D mysql mysql-5.6.{xx}-win32". Symlink is available in Windows Vista/7/8.


MySQL Distribution

The MySQL distribution includes:

    A SQL server (mysqld);
    A command-line client (mysql);
    Utilities: Database administration (mysqladmin), backup/restore (mysqldump), and others;
    Client libraries for you to write your own client.


Explanation

    [mysqld]
    [client]
    The MySQL operates as a client-server system, and consists of a server program and a client program. There are two sections in the configuration: [mysqld] for the server program, and [client] for the client program.
    basedir=<MYSQL_HOME>
    datadir=<MYSQL_HOME>/data
    "basedir" and "datadir" specify the MySQL installed directory and data directory for storing the databases, respectively. Make sure that you set their values according to your own installation. You need to use Unix-style forward-slash (/) as the directory separator, instead of Windows-style backward-slash (\).
    port=8888
    MySQL is a TCP/IP application. The default TCP port number for MySQL is 3306. However, it may crash with a MySQL server already running in some lab machines. You may choose any port number between 1024 to 65535, which is not used by an existing application. I choose 8888 for our server.
    This configuration file specifies the bare minimum. There are many more configuration options. Sample configuration files (*.ini, *.cnf) are provided under <MYSQL_HOME>.

The server program is called "mysqld" (with a suffix 'd', which stands for daemon - a daemon is a non-interactive process running in the background).
The client program is called "mysql" (without the 'd')


http://www.ntu.edu.sg/home/ehchua/programming/sql/MySQL_HowTo.html

MSSQL

• Database Mirroring Terms and Definitions

Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.


automatic failover

    The process by which, when the principal server becomes unavailable, the mirror server to take over the role of principal server and brings its copy of the database online as the principal database.
 
    High-performance mode

    The database mirroring session operates asynchronously and uses only the principal server and mirror server. The only form of role switching is forced service (with possible data loss).

High-safety mode

    The database mirroring session operates synchronously and, optionally, uses a witness, as well as the principal server and mirror server.
 
    mirror database

    The copy of the database that is typically fully synchronized with the principal database.
 
    principal database

    In database mirroring, a read-write database whose transaction log records are applied to a read-only copy of the database (a mirror database).
 
    Witness

    For use only with high-safety mode, an optional instance of SQL Server that enables the mirror server to recognize when to initiate an automatic failover. Unlike the two failover partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness.
 
    http://msdn.microsoft.com/en-us/library/ms189852.aspx

• Replication Agents Overview

SQL Server Agent
SQL Server Agent hosts and schedules the agents used in replication and provides an easy way to run replication agents.

Snapshot Agent
The Snapshot Agent is typically used with all types of replication

Log Reader Agent
The Log Reader Agent is used with transactional replication

Distribution Agent
The Distribution Agent is used with snapshot replication and transactional replication

Merge Agent
The Merge Agent is used with merge replication.

Queue Reader Agent
The Queue Reader Agent is used with transactional replication with the queued updating option

http://msdn.microsoft.com/en-us/library/ms152501.aspx

Two Phase Locking

Two Phase Locking

The most commonly implemented locking mechanism is called
Two Phased Locking or 2PL
2PL is a concurrency control mechanism that ensure serializability.

2PL has two phases : Growing and shrinking.

A transaction acquires locks on data items it will need to complete the transaction.This is called the
growing phase

Once one lock is released, all no other lock may be acquired. This is called the
shrinking phase



The most common way in which access to items is controlled is by “locks.”
Lock manager is the part of a DBMS that records, for each item I, whether one or more transactions are reading or writing any part of I.

online sql practices

Test your SQL Skills
http://www.w3schools.com/sql/sql_tryit.asp

sqlcourse
http://www.sqlcourse2.com/orderby.html

Primary Key

Primary Key:
A primary key is a field or combination of fields that uniquely identify a record in a table, so that an individual record can be located without confusion.
http://www.databasedev.co.uk/primary_foreign_key_constraints.html


A primary key is one which uniquely identifies a row of a table. this key does not allow null values and also does not allow duplicate values
http://www.allinterview.com/showanswers/15625.html


A primary key in a database, is simply a useful device (key) that makes each record unique.
Read more: http://wiki.answers.com/Q/What_is_the_purpose_of_a_primary_key_in_a_database#ixzz1sIudT06W

foreign key

foreign key - a foreign key is one which will refer to a primary key of another table
http://www.allinterview.com/showanswers/15625.html

A foreign key is a relationship or link between two tables which ensures that the data stored in a database is consistent.
The foreign key link is set up by matching columns in one table (the child) to the primary key columns in another table (the parent)
http://www.visualcase.com/kbase/database_basics_-_foreign_keys.htm


Foreign Key:
A foreign key (sometimes called a referencing key) is a key used to link two tables together.
Typically you take the primary key field from one table and insert it into the other table where it becomes a foreign key
(it remains a primary key in the original table).

A foreign key constraint specifies that the data in a foreign key must match the data in the primary key of the linked table, in the above example we couldn't set the DeptID in the Employee table to 04 as there is no DeptID of 04 in the Department table. This system is called referential integrity, it is to ensure that the data entered is correct and not orphaned (i.e. there are no broken links between data in the tables)
http://www.databasedev.co.uk/primary_foreign_key_constraints.html

EXISTS Condition

EXISTS Condition

The EXISTS condition is considered "to be met" if the subquery returns at least one row.

The syntax for the EXISTS condition is:

SELECT columns
FROM tables
WHERE EXISTS ( subquery );




Example #1:

Let's take a look at a simple example. The following is an SQL statement that uses the EXISTS condition:

SELECT *
FROM suppliers
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);

This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.



Example #2 - NOT EXISTS:

The EXISTS condition can also be combined with the NOT operator.

For example,

SELECT *
FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

This will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.

http://www.techonthenet.com/sql/exists.php

course pages,quizes,tests,exams

course pages,quizes,tests,exams


Bil354 Veri Tabani Sistemleri
http://web.cs.hacettepe.edu.tr/~ssen/teaching/bil354.html

BIL106 Veritabani Sistemleri
http://edogdu.etu.edu.tr/course/bil106/


Database Management Systems
http://myweb.brooklyn.liu.edu/gnarra/database/

Database Management Systems CIS 3400
http://cisnet.baruch.cuny.edu/holowczak/classes/3400/notes.html


CSC1270: Database Management Systems
http://www.cs.brown.edu/courses/cs127/

Relational Algebra - Select and Project Operators

• Relational Algebra - Select and Project Operators

http://www.youtube.com/watch?v=yVh_LcOcQdg

• Relational Algebra

Relational SELECT

SELECT is used to obtain a subset of the tuples of a relation that satisfy a select condition.

For example, find all employees born after 1st Jan 1950:

SELECTdob '01/JAN/1950'(employee)


Relational PROJECT

The PROJECT operation is used to select a subset of the attributes of a relation by specifying the names of the required attributes.

For example, to get a list of all employees surnames and employee numbers:

PROJECTsurname,empno(employee)

http://db.grussell.org/section010.html#_Toc67114472

• Relational Algebra: 5 Basic Operations

• Selection () Selects a subset of rows from
relation (horizontal).
• Projection () Retains only wanted columns
from relation (vertical).

• Cross-product (x) Allows us to combine two
relations.
• Set-difference (–) Tuples in r1, but not in r2.

• Union ( ) Tuples in r1 and/or in r2.

https://docs.google.com/viewer?a=v&q=cache:VDokuEkCX5wJ:inst.eecs.berkeley.edu/~cs186/sp06/lecs/lecture8Alg.ppt+&hl=en&pid=bl&srcid=ADGEESgiCeJZcOiv5iPRotaxu6pomoztERrMYuEVScwpi1kqlrF3ep4OJFlHIAWi4oJY0lFzFdq_eN73o0g7LQQo0Hvq34G_A9_pPIHPycr-NpyCL8B4brQhGmZwtReFMTuvHpynj-w5&sig=AHIEtbS3ZRzsDc-Udg4I5DzR4P1muwA-VA

• Relational Algebra

An algebra is a formal structure consisting of sets and operations on those sets.
Relational algebra is a formal system for manipulating relations.

Operands of this algebra are relations.
Operations of this algebra include the usual set operations (since relations are sets of tuples), and special operations defined for relations
selection
projection
join
http://www.cs.rochester.edu/~nelson/courses/csc_173/relations/algebra.html

• relational schema for a music albums database.

Keys are (mostly) underlined.
The attributes should be self-evident.
For a given music track, we code the title, its play length in time (minutes:seconds), its genre (pop, metal, jazz, etc.) and a 5 star maximum rating.
The musicians, singers and instrumentalists are all listed in on their contribution to the track.
A person may have 1 or more listing for a track. For example someone may both sing and play the piano.
The album is a collection of tracks.  An album is distributed and owned by a company called the label and has a producer and an engineer.
For a given music track, we code the title, its play length in time (minutes:seconds), its genre (pop, metal, jazz, etc.) and a 5 star maximum rating.
The musicians, singers and instrumentalists are all listed in on their contribution to the track.
A person may have 1 or more listing for a track. For example someone may both sing and play the piano.
The album is a collection of tracks.
An album is distributed and owned by a company called the label and has a producer and an engineer.


PEOPLE (PID, name, address, zip, phone)
CSZ (zip, city, state)
TRACKS (trID, title, length, genre, rating, albID) //trID is unique across all albums
ALBUMS (albID, albumTitle, year, label, prodPID, engPID, length, price)
CONTRIBS (trID, PID, role)


Use the R.A. notation below.
BE EXPLICIT in the join condition which attributes make the join where necessary.

Syntax reminder for Relational Algebra expressions:
SELECT :  condition(relation)
PROJECT : attribute-list(relation)
SET Operations and JOIN:  relation1 OP relation2, where OP is  , , - , , , and  ||condition
RENAME:  relation[new attribute names]
ASSIGN:    new-relation(attrs)  R.A. expression


a) List all names and phone numbers of people from zip 90210.
name, phone(zip=90210(PEOPLE))

b) List album titles and labels with a list price of more than $18.
albumTitle, label(price>18(ALBUMS))


c) List all the musicians and what they played or contributed to on all jazz type tracks.
name, role(genre= ‘jazz’(TRACKS |X| trID=trID CONTRIBS |X| PID= PID PEOPLE))


d) Get a list of names of people who produced OR engineered an album, but did not perform on any track.  (Hint: set operations are very helpful)
d) name(((prodPID ALBUMS)[PID]  (engrPID ALBUMS)[PID]) - PID CONTRIB)

          |X| PID= PID PEOPLE)


e) List names of musicians who have contributed in at least two different roles on the same tracks with ratings 4 or higher. (Hint: self-join)
name, role(rating>= 4 and role <>role2
(CONTRIBS |X| trID=trID and PID=PID CONTRIBS[trID, PID, role2] )
|X| PID= PID PEOPLE))


http://jcsites.juniata.edu/faculty/rhodes/dbms/funcdep.html

• relational algebra

Consider the following relation database
schema of people who places book orders.
Book(BookID,title,price)
Person(PersonID,Name,Zip)
Orders(PersonID,BookID,quantity,BillingID)
Billing(BillingID,PersonID,CreditCardNum)
Answer the following questions based on this schema. Pay particular attention to the language we
ask for the query in.
(a) Write a query in Relational Algebra to find the title of the book(s) with the lowest price. (3
points)
(b) Write a query in Relational Algebra to find Zip of every person who ordered the book with
the title ’Database Systems’. (4 points)
(c) Write a query in SQL to create the table Billing. Remember to specify the Primary Key and
the foriegn key constraints. All columns are of type Varchar(255). No column is allowed to
be NULL. (4 points)
(d) Write a query in SQL to find how much money has been spent on the books. (4 points)

Solution:
(a) πtitle(Book) − πB1.title(ρB1(Book) c ρB2(Book))
Where c = B1.price > B2.price
(b) πZip(σtitle= DatabaseSystems ((Book c1 Orders) c2 P erson))
Where c1 = Book.BookID = Orders.BookID
Where c2 = Orders.PersonID = Person.PersonID
(c) CREATE TABLE Billing (
BillingID Varchar(255) PRIMARY KEY,
PersonID Varchar(255) NOT NULL,
CreditCardNum Varchar(255) NOT NULL,
FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
)
(d) SELECT SUM(booksales) FROM (SELECT (price*quantity) AS booksales FROM Orders o
LEFT JOIN Book b ON o.BookID = b.BookID)

webcache.googleusercontent.com/search?q=cache:8vRupC7L9OYJ:https://wiki.engr.illinois.edu/download/attachments/227743489/CS411-F2011-Final-Sol.pdf%3Fversion%3D1%26modificationDate%3D1380470739000+&cd=3&hl=tr&ct=clnk&gl=tr&client=firefox-a

What is the difference between an incremental backup and a differential backup?

An Incremental backup backs up only the selected files that have their archive bit set to ON, setting them back to OFF.
a backup of all files that are new or changed since the last backup whether it was a full or an incremental.
The advantage of an Incremental is that it takes the least amount of time and media of all the backup methods.
In the case of restoring with Incremental backups, all the Incremental backups since the last full backup plus the last full backup would be necessary.




A Differential backup backs up only the selected files that have their archive bit set to ON but does not set the archive bit back to OFF.
A Differential backup will back up all selected files that are new and changed since the last full backup.
at restore time; you'll need only the last full backup and the last differential to get a complete restore

Reference:
http://wiki.answers.com/Q/What_is_the_difference_between_an_incremental_backup_and_a_differential_backup

• Continuous data protection (CDP), also called continuous backup or real-time backup, refers to backup of computer data by automatically saving a copy of every change made to that data, essentially capturing every version of the data that the user saves. In its true form it allows the user or administrator to restore data to any point in time

https://en.wikipedia.org/wiki/Continuous_Data_Protection

Simply stated, continuous data protection (CDP), also called continuous backup, is a storage system that backs up data whenever any change is made in it. In effect, CDP creates an electronic journal of complete storage snapshots, one for every instant in time that data modification occurs.

Why continuous data protection?
IT complexity: Continuous data protection helps ensure continuous availability of the varied, cross-platform environments.
Administrative capability:
Cost factor:Continuous data protection may prove to be a cheaper solution than traditional backup and recovery solutions. Software-based continuous data protection solutions today are easy to deploy and manage,
Data growth:  Remote users in geographically dispersed locations have access to email, core systems and other mission-critical applications. In such cases, continuous data protection makes perfect sense for enterprises with locations in multiple geographies.
Criticality of data:Continuous data protection technologies enable seamless backup and restore at the backend without affecting end users. Many continuous data protection solutions can recover data from any point in time within less than a minute.

Most of the challenges with continuous data protection emanate from the environment being targeted. A true continuous data protection solution will support real-time protection rather than scheduled snapshots. The change rate of the data sets that are being protected could pose challenges to the continuous data protection solution.
https://www.computerweekly.com/tip/Continuous-data-protection-Do-you-need-it

MySQL tools

• Percona Toolkit(Maatkit)

Percona Toolkit is a collection of advanced command-line tools used by Percona support staff to perform a variety of MySQL and system tasks that are too difficult or complex to perform manually

http://www.percona.com/software/percona-toolkit/