Show Menu
Cheatography

database

key constr­aints

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.

Partic­ipation Constr­aints

total or partial. repres­ented by bold lines.

superkey and candidate key

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.

JDBC Applic­ation Progra­mming

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

JDBC Steps

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­nin­gs().
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?
 

Query

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”

Joins

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

OORDBMS

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.
Meth­ods
1.Member Method: defined on Instance Data
2.Static Method : invoked on the object type.can be used that are global.
3.Co­nst­ructor Method: Built in constr­uctor method.
 

Methods to compare objects: Member 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,…

Class Concepts

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.

Oracle Methods

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’);

Creating Object Table

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
 

overlo­adi­ng/­ove­rriding

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(),
...); /

Order Methods

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)) ;
   

Help Us Go Positive!

We offset our carbon usage with Ecologi. Click the link below to help us!

We offset our carbon footprint via Ecologi
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          m1 Cheat Sheet

          More Cheat Sheets by abirjepatil