3380 Ex 2

The flashcards below were created by user fisk42 on FreezingBlue Flashcards.

  1. WHAT


    • ~ Conceptual Model ~ Designer's view
    • High Abstraction "ER" HW/SW Indep

    • ~ Internal Model ~ DBMS view
    • Med Abstraction "Relational" HW Indep SW Dep

    • ~ Physical Model
    • Low Abstraction "Network Hierarchial" HW/SW Dep
  2. SQL stands for?
    • Structured
    • Query
    • Language
  3. Major Strength of: Relational Query Languages?
    ~They support simple powerful querying of data

    ~They can be written intuitively

    ~DBMS is responsible for efficient evaluation
  4. Relation Database is:
    ~a set of relations

    • ~made up of two parts:
    •   -Schema (specifies name of relation, name and type of each column)
    •   -Instance (table with rows and columns)

    ~has a domain = type
  5. Integrity Constraint is:
    ~condition that must be true for any instance of the database; e.g. domain constraints:

    •   -IC are specified when schema is defined
    •   -IC are checked when Relations are modified

    ~ICs are based upon the semantics of the real-world enterprise
  6. Weak Entity is:
    ~one that can be identified uniquely only by considering the primary key of another entity
  7. SQL View is:
    ~a view is just a relation, but we store a definition, rather than a set of tuples
  8. Basic SQL query: composed of...
    • relation-list: list of relations referred to
    • target-list: list of attributes in relations
    • qualification: comparisons etc

    • 1. Compute join of relation-list (bowtie)
    • 2. Discard resulting tuples if fail qualifications (sigma)
    • 3. Delete attributes not in target-list (pi)
  9. SQL Range Variable is:
    S.sailors ETC.
  10. SQL COMMANDS (1):
    [op] ANY/ALL
    • LIKE - used for string matching
    • _ - one character
    • % - zero or more arbitrary characters
    • DISTINCT - removes duplicates
    • UNION
    • EXCEPT
    • IN
    • [op] ANY/ALL
    • EXISTS
    • NOT - in front of IN or EXISTS
  11. SQL COMMANDS (2):
    COUNT([distinct] A)
    SUM(distinct] A)
    AVG(distinct] A)

    GROUP BY - must be subset of relation-list and must be a single value per group
  12. ILLEGAL:

    SELECT S.sname, MAX (S.age) FROM Sailors S;

    • SELECT S.sname, S.age FROM Sailors S
    •      WHERE S.age = (SELECT MAX (S2.age) 
    •           FROM Sailors S2);
  13. Find the rating and age of the youngest sailor at least 18 years old for each rating with at least two such sailors
    • SELECT S.rating, MIN(S.age) FROM Sailors S
    •      WHERE S.age >=18
    •      GROUP BY S.rating
    •      HAVING COUNT (*) > 1;
  14. CHECK is:
    ~A general constraint, to check entries being made

    ~Constraint over multiple relations is wrong, should create an assertion instead
  15. TRIGGER is:
    ~procedure that starts automatically if specified changes occur to the DBMS

    • 1. EVENT - activates the trigger
    • 2. CONDITION - tests wether trigger should run
    • 3. ACTION - what happens if it runs

    TRIGGERS CAN: keep counts, alert users to events, keep stats etc.
  16. Relational Algebra is:

    Relational Calculus is:
    ~more operational, very useful for representing execution plans

    • ~lets users describe what they want rather than how to get it
    • :Tuple relational calculus
    • :Domain relational calculus
  17. (RA)
    PROJECTION (pi) - deletes unwanted relations 
    SELECTION (sigma) - selects subset of rows
    UNION - in 1 or 2
    INTERSECTION - in 1 and 2
    SET-DIFFERENCE - in 1 but not 2
    CROSS PRODUCT (X) - pairs each row with each row
    CONDITION JOIN (bowtie) aka theta join - similar to X but more selective; conditions are subscripted
    EQUI JOIN (bowtie) same, but with field
  18. Query By Example (QBE) is:
    GUI based on DRC

    • P. print
    • I. insert
    • D. delete
  19. Safe/Unsafe queries:
    Queries that return infinite tuples

    Any safe query in one language is safe in another.
  20. ODBC
    OLE DB
    ODBC - open database connectivity, drivers provided by database vendors so applications can access their respective databases's data sources

    OLE DB - object linking & embedding database, is a microsoft low level API (application program interface) for access to different data sources

    ADO.NET - ActivX Direct Objects, microsoft consistent access to data sources as well as data sources exposed through OLE DB and XML

    JDBC - java.sql package provided by sun which is a standard library for accessing relational databases
  21. CURSOR is:
    stored procedure. can open a cursor, repeatedly fetch a tuple, then move the cursor until all tuples have been reached

    • select blah.....
    program executed through a single SQL statement

    executed in the process space of the dbms server

    avoids tuple at a time method of cursors

    do not have to be written in java
  23. JDBC Stored Procedure

    (for when your program uses dynamic sql)
    • CallableStatement cstmt = con.prepareCall("{CALL ShowSailors}");
    • ResultSet rs = cstmt.executeQuery();
    • while rs.next()
    • {
    • ...
    • }
  24. SQLJ Stored Procedure:

    (use when you want to be able to check your program for errors at translation-time rather than run time)
    • #sql iterator ShowSailors(...);
    • ShowSailors showsailors;
    • #sql showsailors = {CALL ShowSailors};
    • while (showsailors.next())
    • {
    • ...
    • }
    • naming schema (http)
    • host computer  (www.what.com)
    • name of the resource (~index.html)
  26. HyperText Transfer Protocol
    • 1. client sends request to server
    • 2. server recieves request and replies
    • 3. client recieves reply, makes new requests

    stateless, no sessions. each message is self contained
  27. XML is:
    a heirarchy of user defined tags called elements with attributes and data
  28. DTD
    Document type definition
    is a schema for xml data, tells what elements and attributes are optional and what is not
  29. Three tier architechture
    • M: Data management (database system)
    • -duh

    • C: (Application Server)
    • -implements buisness logic; maintains steps between states of workflow; accesses different data management systems

    • V: Presentation (client program (web browser))
    • -primary interface of user, must adapt to many different displays
  30. The ____ function returns the current system date in MS Access/SQLServer.
    • A TO_DATE()
    • B SYSDATE()
    • **C DATE()
    • D TODAY()
  31. The relationships depicted within the ____ are sometimes described in terms of "IS-A" relationships.
    • A. subtype discriminator
    • B. inheritance
    • **C. specialization hierarchy
    • D. entity supertype
  32. The selected primary key must not be composed of any attribute(s) that might be considered a security risk or violation. For example, using a Social Security number as a PK in an EMPLOYEE table is not a good idea.This rationale belongs to ____.
    • A. unique values
    • B. nonintelligent
    • C. preferably single-attribute
    • **D. security complaint
  33. ____ is the bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity subtypes.
    • A. Specialization
    • **B. Generalization
    • C. Partial completeness
    • D. Total completeness
  34. Which of the following is NOT a characteristic of Internet technologies?
    • A. Rapid development at manageable costs
    • **B. Need to support different software platforms
    • C. Common and simple user interface
    • D. Location independence
Card Set:
3380 Ex 2

Show Answers: