db Cheat Sheet by abirjepatil

one to many, many to many etc. the direction of the arrow is pointed where 'one' refers, attribute types :- composite attrib­ute­s.m­ult­ivalued attrib­ute­s.d­erived attrib­utes. Genera­liz­ation : composing two or more entities together. specia­liz­ation reverse of genera­liz­ation. Disjoint :- user can be a member of at most one entity. overlap just opposite. total atleast one. disjoint can be present in both.p­artial.

total or partial. repres­ented by bold lines.

superkey one or more attributes together. candidate key is a minimal superkey. an entity that has a primary key is called as a strong entity. the entity whose primary key is being used is called as the identi­fying owner.

Client - Server Archit­ecture . 2 tier and 3 tier archit­ecture.
ODBC: Open Database Connec­tiv­ity.JDBC located in java.sql package.
JDBC-ODBC bridge • Con: ODBC must be installed
• JDBC database client • Con: JDBC driver for each server must be available
• JDBC middleware client • Pro: Only one JDBC driver is required • Applic­ation does not need direct connection

1. Load the driver 2. Define the Connection URL 3. Establish the Connection 4. Create a Statement object 5. Execute a query 6. Process the results 7. Close the Connection
commit () rollback() getMet­aDa­taO­bject getwar­nings().
Result­Set­Met­aData answers the following questions:
• How many columns are in the result set?
• What is the name of a given column?
• Are the column names case sensitive?
• What is the data type of a specific column?
• What is the maximum character size of a column?
• Can you search on a given column?

insert into table name values (select statem­ent). Aggreg­ation [MAX,M­IN,­AVG­,CO­UNT­,SUM]

SELECT product, Sum(pr­ice­*qu­antity) AS TotalSales
FROM Purchase
WHERE date > “10/1”
GROUP BY product
without group by
SELECT DISTINCT x.product, (SELECT Sum(y.p­ri­ce*­y.q­uan­tity)
FROM Purchase y
WHERE x.product = y.product
AND y.date > ‘10/1’)
AS TotalSales
FROM Purchase x
WHERE x.date > “10/1”

left outer join:For tables A and B, contains all records of the "­lef­t" table
(A), even if the join-c­ond­ition does not find any matching
record in the "­rig­ht" table

Abstra­ction: ignoring the parts that are not import­ant.focus on what an object is and what it does rather than how it is done. Encaps­ula­tion: inform­ation hiding.se­par­ating external aspects from the internal implem­ent­ation.
Class: A group of objects with the same attributes and methods.
Methods
1.Member Method: defined on Instance Data
2.Static Method : invoked on the object type.can be used that are global.
3.Constr­uctor Method: Built in constr­uctor method.

Define a special kind of member methods to compare objects.
 Define either a map method or an order method in an object type.
 Map Method
• Map object instances into one of the scalar types DATE, CHAR, NUMBER,…

Subclass: A class of objects that is defined as a special case
of a more general class,the process of forming subclasses
is called specia­liz­ation.
Superc­lass: A class of objects that is defined as a general
case of a number of special classes (the process of forming
a superclass is called genera­liz­ation). All instances of a
subclass are also instances of its superc­lass.
Inheri­tance: By default, a subclass inherits all the
properties of its superclass (or it can redefine some (or all)
of the inherited methods). Additi­onally, it may define its
own unique proper­ties.

Member Methods
CREATE OR REPLACE TYPE BODY person­_type AS
MEMBER FUNCTION get_ar­eacode RETURN VARCHAR2 IS
BEGIN
RETURN SUBSTR­(phone, 1, 3);
END get_ar­eacode;
END;
/SELECT c.cont­act.ge­t_a­rea­code()
FROM contacts c;
-- Constr­uctor Method
Every object type has a constr­uctor method implicitly defined by
system.
 Returns a new instance of the user-d­efined object type and sets up the
values of its attrib­utes.
 The name of constr­uctor method is the same as the name of the object
type.
p = person­_ty­pe(­‘Scott Tiger’, ‘321-1­23-­1234’);

CREATE TABLE person­_table OF person­_type;
INSERT INTO person­_table
VALUES (perso­n_type (‘Scott Tiger’, ‘321-1­23-­123­4’));
SELECT VALUE(p) FROM person­_table p WHERE p.name = ‘Scott

CREATE TYPE Shape_typ AS OBJECT (...,
MEMBER PROCEDURE Enlarge(x
...) NOT FINAL; /
CREATE TYPE Circle_typ UNDER
MEMBER PROCEDURE Enlarge(x
NUMBER),
Shape_typ (...,
CHAR(1))); /
--Define the inherited method Enlarge() to deal
--input parame­ters.
with different types of
CREATE TYPE Shape_typ AS OBJECT (...,
MEMBER PROCEDURE Area(),
FINAL MEMBER FUNCTION id(x NUMBER)...
) NOT FINAL; /
CREATE TYPE Circle_typ UNDER Shape_typ (...,
OVERRIDING MEMBER PROCEDURE Area(),
...); /

CREATE TYPE
circle­_type x
y
r
AS OBJECT (
NUMBER,
NUMBER,
NUMBER,
ORDER MEMBER FUNCTION match(c circle­_type)
CREATE OR REPLACE TYPE BODY circle­_type AS
ORDER MEMBER FUNCTION match (c circle­_type)
BEGIN
RETURN INTEGER ); /
RETURN INTEGER IS
IF r < c.r THEN
RETURN –1;
-- 3.14r2 < 3.14c.r2
-- any negative number
THEN
-- any positive number
ELSIF r > c.r
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
END;
SELECT FORM FROM circles c
WHERE VALUE(c) < (circl­e_t­ype(40, 25, 5)) ;