Scribd Logo
Upload

Welcome to Scribd!

  • 657 views

    Cheat Sheet On DataBase Management

    Uploaded by

    Raistlin Chan Ching Kit
    This document discusses relational database concepts including: - Primary keys and foreign keys which uniquely identify rows and define relationships between tables. - Common SQL commands like CREATE TABLE, ALTER TABLE, INSERT, DELETE, UPDATE, and SELECT for managing tables and querying data. - Relational algebra operations such as projection, selection, join, union, difference and how they map to SQL keywords. - Aggregate functions like COUNT, AVG, MIN, MAX and GROUP BY. - Various types of joins including inner joins, outer joins, and self joins.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Cheat Sheet On DataBase Management

    Uploaded by

    Raistlin Chan Ching Kit
    657 views3 pages
    This document discusses relational database concepts including: - Primary keys and foreign keys which uniquely identify rows and define relationships between tables. - Common SQL commands like CREATE TABLE, ALTER TABLE, INSERT, DELETE, UPDATE, and SELECT for managing tables and querying data. - Relational algebra operations such as projection, selection, join, union, difference and how they map to SQL keywords. - Aggregate functions like COUNT, AVG, MIN, MAX and GROUP BY. - Various types of joins including inner joins, outer joins, and self joins.

    Original Description:

    A summary on Database management

    Original Title

    Cheat Sheet on DataBase Management

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses relational database concepts including: - Primary keys and foreign keys which uniquely identify rows and define relationships between tables. - Common SQL commands like CREATE TABLE, ALTER TABLE, INSERT, DELETE, UPDATE, and SELECT for managing tables and querying data. - Relational algebra operations such as projection, selection, join, union, difference and how they map to SQL keywords. - Aggregate functions like COUNT, AVG, MIN, MAX and GROUP BY. - Various types of joins including inner joins, outer joins, and self joins.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    657 views3 pages

    Cheat Sheet On DataBase Management

    Uploaded by

    Raistlin Chan Ching Kit
    This document discusses relational database concepts including: - Primary keys and foreign keys which uniquely identify rows and define relationships between tables. - Common SQL commands like CREATE TABLE, ALTER TABLE, INSERT, DELETE, UPDATE, and SELECT for managing tables and querying data. - Relational algebra operations such as projection, selection, join, union, difference and how they map to SQL keywords. - Aggregate functions like COUNT, AVG, MIN, MAX and GROUP BY. - Various types of joins including inner joins, outer joins, and self joins.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 3

    Chapter 2

    Super Key a set of >= 1 attributes whose values uniquely determine each entity
    Candidate Key a minimal super key w/o redundant attributes
    Primary Key one of candidate key
    Element
    Notes
    Relational tables
    Attribute
    Multi-valued
    Both entries are primary key
    Derived
    No need to include in tables
    Composite
    No need to include the root of
    the composite attribute
    **Relationship set table is not
    Weak entity set
    *(Total many)-to-one
    needed

    Role
    Specialization and
    Generalization

    Hierarchical structure
    Disjoint / overlapping

    - If total specialization the head


    can be chopped
    - Primary key of head can be
    placed in regard of efficiency
    and redundancy
    *: True for other (Total many)-to-one and one-to-one relationship set
    **: Many side table include the PK of the one side and all the ATT on diamond
    Foreign Key
    Table being referred: add recordsokay; delete records may violate!!
    Table being constrained: add records may violate!! ; delete records violate!!
    Chapter 3
    CREATE TABLE Branch( id1 varchar(10), id2 int(10), PRIMARY KEY(id1), FOREIGN KEY(id2)
    REFERENCES table2(id));
    ALTER TABLE ADD/DROP column;
    ALTER TABLE ADD/DROP FOREIGN KEY() ON DELETE CASCADE;
    INSERT INTO table VALUES(1ST column),(2nd column),(3rd column);
    DELETE FROM table WHERE condition;
    UPDATE table SET att = 0 WHERE condition;
    Conditional Update: UPDATE table SET att = CASE WHEN condition1 THEN att*1.05 ELSE
    att*1.06 END;
    SELECT * FROM Customer as A, Borrower
    A ( Customer ) B ( Borrower)
    as B;

    SELECT A.id, b.name FROM Customer A,


    Borrower B
    A .id , B . id ( A ( Customer ) B (Borrower ))
    WHERE A.id=B.id AND b.name IS NOT

    N.B. If joining with non-PK, DISTINCT is need


    NULL;
    LIKE `%320_`;
    Any string end with 320 + unknown character
    ORDER BY a.id ASC, b.id DESC;
    Default is according to the order of input

    SELECT DISTINCT a.id FROM BORROWER A


    (
    WHERE a.id (NOT) IN (SELECT b.id FROM
    A .id ( A Customer ) B (Owner ))
    OWNER B);

    N.B. relational projection only includes distinct


    tuples
    AVG(), MIN(), MAX(), SUM(), COUNT()
    SELECT id, AVG(balance) FROM Account
    GROUP BY id;
    HAVING condition;
    Selection operator over above expression
    SELECT * FROM Employee E RIGHT OUTER E .id =33 ( E D)
    JOIN
    =( E D ) ( D D ( E D ) ) { ( null , ,null ) }
    Department D
    i.e. keep all the rows in the right table
    ON E.id=D.id
    WHERE E.id BETWEEN 2 AND 4;
    Chapter 3 Equivalence Rules

    1.

    Only final projection is needed

    2.

    Also the selection operator is order-independent

    3.
    (Tree rotation)

    4.
    5.
    6.

    L3 are the attribute set in common; L1 or L2 can be null

    ( E1 E2 ) E 3=( E2 E1 ) E3=E 1 (E 2 E3 )

    Similar for intersection

    7.
    8.
    Notes:
    For , , set difference , the number of attributes and the same type
    Operator
    Description
    SQL equivalent
    E.g.
    RS
    S R
    NOT IN /EXCEPT
    Find the sID and
    name of the
    RS
    Cartesian Product
    FROM table1,
    employee who
    table2
    know all the IT
    R S
    Joining table
    skills.
    RS
    all tuples in col 1 of R

    that has full combo with


    sID , name
    S
    (HAS skillID ( I T Skill ) )
    R S
    INTERSECT
    R( RS)

    keyword/ joining
    Staff
    the same table
    with alias
    R S
    SELECT E.name FROM Employee E,
    Works_in W
    WHERE E.id = W.id AND (W.deid=1 OR
    W.deid=3);
    SELECT E.name FROM W.deid=1
    UNION
    SELECT E.name FROM W.deid=3;

    Q. The greatest budget


    SELECT D.name FROM Department D
    WHERE D.budget >= ALL(
    SELECT D2.budget
    FROM
    Department D2
    Chapter
    3 Query
    ));

    Q. Assertion to check
    update
    CREATE ASSERTION label

    Q. Create View
    CREATE VIEW title(label) AS();
    Q. Find all employee names for which the salary
    is unknown
    Q. Budget>SOME(Depart em4
    SELECT name FROM Employees WHERE salary IS
    works)
    Q. Find the names of employees who work in at least 2
    SELECT D.name FROM Department departments
    D
    SELECT E.name FROM Employees E
    WHERE D.budget > SOME(
    WHERE 2 <= (
    SELECT D2.budget
    SELECT COUNT(W.department_id)
    FROM Department D2
    FROM Works_in W
    WHERE D2.department_id IN(
    WHERE W.employee_id=E.employee_id
    SELECT W.department_id
    );
    Q. AUTHORIZATION
    FROM Works_in W
    WHERE W.employee_id =4GRANT <list> ON <table/view> TO
    <user/role list>;
    ));
    CREATE ROLE role;

    You might also like