PCS Modeling Data (MCQ)

  1. Meeting types
    Joint Requirement Definition (JRD)
    define theory of what is needed with written explainations (typically a meeting among users and main developers or team leads)
  2. Meeting types
    Joint Application Development (JAD)
    • devise pictorial veiw of needed application features
    • will create Function Requirement Documnt(FRD)
    • typically a meeting with devopers, project managers, and techs
  3. Meeting types
    typical process of a project
    development> QA > Staging/UAT(sample users)> Production (public)
  4. Methodology
    Waterfall
    straight shot through the process, resulting less with user input
  5. methodology
    agile
    do circles in process until users are satisfied with product
  6. methodology
    scrum
    time limited meetings each day by scrum master
  7. methodology
    spiral
    mix of scrum and agile methods
  8. Developing Database Types
    Online Transation Processing (OLTP)
    the current database in use for users, holds only current info or liimited historical
  9. Developing Database Types
    Online Analysis Processing (OLAP)
    Separate database holding historical data, used for business decisions and trends
  10. Developing Database Types
    Relational Database Management System (RDBMS & DBMS)
    • RDBMS - Oracle, SQ, Sybase, DB2
    • DBMS - Excel, Flat/Text files
  11. Data Modeling
    Row, Coumns Labels, Table, Column Data
    • Rows = tuples
    • Column Labels = Attributes (simple/composite/single/multi-valued)
    • Table = Entity (strong/weak)
    • Cloumn Data = Domain

    Image Upload 1
  12. Data Modeling
    Schemas
    the structure described in formal language supported by DBMS (database management structure)

    the blueprint of coding from using DBMS
  13. Data Modeling
    concept phase
    paper rough draft model, find relations
  14. data modeling
    logical phase
    paper, develop constraints, normalization, ER tool/visio
  15. data modeling
    physical phase
    implemented in server to be tested
  16. Business Rules
    primary key
    unique identifier row, no nulls, only one primary key allowed per table
  17. Business Rules
    foreign key
    key in another table that refers to the primary key of another
  18. Business Rules
    unique key
    a set of attributes st. the relation does not have repeated tuple with the same values; thus leads to improper subset in the relation
  19. Business rules
    surogate key
    Used in OLAP to play the role of primary key, automated sequence
  20. Business Rules
    Check
    verify data (type, range, etc.)
  21. Business Rules
    Default
    a define value to fill in null areas
  22. Business rules
    Nullibility
    the allowance of nulls
  23. Relationships
    1:1
    parent to child/employee to spouse
  24. Relationships
    1:M
    Employee to children
  25. Relationships
    M:M
    broken into conjunction table with composite primary keys
  26. Relationships
    1:Fixed Cardinality
    set max/min relationship limit
  27. Degree
    unary, binary, tenary
    number of entities in relationship
  28. Entity Integrity
    Entity integrity involves the structure (primary key and its attributes) of the entity. If the primary key is unique and all attributes are scalar and fully dependent on the primary key, then the integrity of the entity is good. In the physical schema, the table’s primary key enforces entity integrity.
  29. Domain Integrity
    In relational theory terms, a domain is a set of possible values for an attribute, such as integers, bit values, or characters. Domain integrity ensures that only valid data is permitted in the attribute. Null ability (whether a null value is valid for an attribute) is also a part of domain integrity. In the physical schema, the data type and null ability of the row enforce domain integrity.
  30. referential integrity
    is a subset of Domain Integrity that all its references are valid. In the context of relational databases, it requires that if a value of one attribute of a relation references a value of another attribute, then the referenced value must exist.
  31. User-Defined Integrity
    Simple business rules, such as a restriction to a domain, limit the list of valid data entries. Check constraints are commonly used to enforce these rules in the physical schema. Complex business rules limit the list of valid data based on some condition. For example, certain tours may require a medical waiver. Implementing these rules in the physical schema generally requires stored procedures or triggers.
  32. Database Objects
    P-tables
    • naming schema
    • <dbname>.<schemaname>.<tablename>
  33. Database Objects
    P-views
    create a version of table with only needed data(filtered window)
  34. Database Objects
    P-store procedures
    a set of SQL queries that can be saved and called
  35. Database Objects
    P-User Defined Functions
    similar to stored procedure with different properties
  36. Database Objects
    P-trigger
    act as constraints during certain events
  37. Database Objects
    P-indexes
    map data and improve query performance
  38. Database Objects
    P-cursors
    aka pointers; must be opened and closed when working with data
  39. SQL Query Types
    Data Definition Language (DDL)
    Data Modification Language (DML)
    Data Query Language (DQL)
    Data Controlling Language (DCL)
    • DDL - Create, Alter, Drop
    • DML - Insert, Delete, Update, Truncate
    • DQL - Select, From, Where, Group by, Having, Order by
    • DCL - Revoke, Deny, Grant
  40. Set Operators
    Except
    returns distinct values from left query that are not found on the right
  41. Set Operators
    Intersect
    Returns distinct values found in both queries
  42. Set Operators
    Union
    Combines unique results from both queries into single result set
  43. Set Operators
    Union All
    combines all results from both queries, including duplicates
  44. Rank
    1,1,3,3,5
  45. Dense Rank
    1,1,2,2,3
  46. N tile
    breaks up data in random order and ranking
  47. row number
    • 1,2,3,4,5
    • ranking based on table locationgoing down rows
Author
wrathes
ID
351011
Card Set
PCS Modeling Data (MCQ)
Description
initial test
Updated