DBS quiz 1

  1. What is a database system?
    DBS- a computerized record keeping system
  2. Components of a DBS
    The database, the software, hardware, and its users
  3. What is Data?
    • Facts about an entity or an event. (Raw data that needs to be processed in order to become useful)
    • Individual test scores
  4. What is information?
    • Processed data
    • Average test score of a class
  5. Input data
    Anything you type into a database
  6. Output data
    Response to a query
  7. What are the subsets of persistent data?
    Operational data and decision support data (DSD)
  8. What is persistent data?
    • Data that cannot be 100% removed from the database
    • (Can’t delete 100% of students or their IDs, their grades, etc. only if they move schools)
  9. What is operational data
    • Subset of persistent data.
    • Data subject to operations.
    • Core data and relationships about core entities
  10. What are subsets of operational data?
    Core data and associate data
  11. What is core data
    • Subset of operational data.
    • Data about the core entities of a database (what the database is focused on)
    • For example: students, banking customers
  12. Associate data
    • Subset of operational data.
    • Data about relationships among core entities.
    • For example: if name (core data) and age (core data) are represented in a column next to each other- then they are associate data
  13. Different relationships of associate data
    Unary, binary, ternary
  14. Unary relationship
    • Object is related to itself
    • ie. Parts made of sub-parts
  15. Binary relationship
    • Two involved in relationship.
    • ie. Part is produced by a producer
  16. Ternary relationship
    • 3 parts.
    • Part, producer, seller
  17. Decision support data
    • DSD- subset of persistent data.
    • Data used to perform operations, not physically stored.
    • ie. Total number of students in class
  18. What is a database?
    A collection of persistent data regarding an enterprise.
  19. Components of a database system
    Database, software, hardware, users
  20. Software component of a database system
    Database management system (DBMS)
  21. What is a DBMS in charge of?
    Intercepting query, checks validity of query (in terms of syntax and semantics), and if ok- it implements and resolves to entity the issued query.
  22. What are data languages and how they relate to DBMS?
    • Incomplete languages with only minimum # of statements needed to issue queries (no do while like java).
    • DBMS must always be able to handle a data language (like SQL).
  23. Hardware component of a database system
    Need to have hardware to run software
  24. User component of a database system and sub components
    • People who use DBS....
    • End users, application programmer, database administrator
  25. End users of a DBS
    Customers, people who know nothing about database systems (yet have all authority)
  26. Application programmer of a database system
    Knows at least one data language (can use queries, create reports, etc.)
  27. Database administrator (DBA) of a database
    • Person or team in charge of collecting needs of the user, and designing, creating, populating, and protecting the DB.
    • If the organization is big (Amazon/Walmart), designing responsibilities go to Data Manager.
  28. More specific responsibilities of DBA
    • Designs DB
    • Decides content of DB
    • Ensures satisfaction of the user and their requests
    • Provides security
    • Creates data dictionary
  29. Data dictionary role of a DBA
    Data about data. This is your job security, don’t make it easy for someone else to take over.
  30. General issues with a DBS
    • Integrity
    • Inconsistency
    • Redundancy
    • Security
    • Balancing of conflicts
    • Data independence
  31. Integrity issues in a DBS
    • Correctness of data...
    • If you say nobody makes more than 100 grand but John is listed having 125 grand
    • Make sure there is a threshold/restriction for data entries?
  32. Inconsistency issues in DBS
    • Data is correct but not matching...
    • ie . Two different salaries under a name (after raise) OR two different last names (after a divorce)
    • Make sure ALL data is updated after changes
  33. Uncontrolled redundancy issues in a DBS
    • Having more than one copy of data...
    • Uncontrolled redundancy- Storing the same information unnecessarily (like a customers address who buys several things)
  34. Controlled redundancy in a DBS
    • Getting minimum redundancy for sake of navigating among tables.
    • ie. Backing up a companies files nightly
  35. Security issues in a DBS
    • Internal- security against people who have access to database (housing dept. doesn’t need to see your grades, lvl 5 SCP clearance)
    • External- security against people who are outside your organization.
  36. Interpersonal conflicts in a DBS
    • ie. One department wants day/month/year, other wants month/day/year.
    • OR one wants all metric units, other wants empirical (caused NASA explosion)
  37. Data independence issues in a DBS
    Not sure yet
  38. What is a domain
    A collection of data that have the same type
  39. What is a attribute
    Subset of a domain
  40. What is a relation
    A subset of Cartesian product of not necessarily distinct domains
  41. What is a tuple
    (A row in a table) An instance of Cartesian product of not necessarily distinct domains that appear in a relation
  42. Rules of a tuple
    • Every value in a given attribute is atomic
    • All attribute values of a given attribute come from same domain
    • Order of tuple and attributes is insignificant
    • No duplicate tuples
  43. Cardinality of a relation
    Number of tuples
  44. Degree of a relation
    Number of attributes
  45. Candidate key
    Attribute or group of attributes whose values can UNIQUELY identify every tuple in a relation
  46. Conditions for candidate key
    Uniqueness and minimality
  47. Primary key
    The selected candidate key
  48. Alternative key
    Candidate key that’s not your primary key
  49. Super key
    Candidate key that satisfies ONLY uniqueness (ST-ID, Name, Age)
  50. Entity integrity
    A primary key or any component of it must have a value (can’t be null) and cannot be known
  51. Referential integrity
    Referring to a tuple that doesn’t exist ( student listed one class but not database)
  52. Primary domain
    Location you’re extracting primary key from
  53. Foreign key
    An attribute (or group) that appears in multiple relations (and comes from same domain) but is not used as the primary key
  54. Union compatible conditions
    • The degree (# of attributes) of both relations must be the same
    • Data for corresponding attributes (A and A’) must be from same domain
    • No duplicates
  55. intersecting relations
    • Must be union compatible
    • Common elements/ tuples is result
  56. Subtract relations
    Result is unique element in first set. S1={1,2,3}, S2={1,4,3}, S1-S2=2... S2-S1=4
  57. What does select do?
    Takes horizontal subset of relation where __ = __ and places that tuple into relation __
  58. Times relations
    Concatenation every tuple in one relation with every tuple in another
  59. Equi join
    (W1 = R1 equi join R2 where R1.B = R2.E) Concatenation every tuple in one relation with every tuple in the other. Result is many attributes
  60. Natural join
    Concatenation all tuples with each other and only keep ONE copy of attribute values
  61. Theta-join
    Equi join where there are duplicates under a condition
  62. Different inner join
    • Equi join
    • Natural join
    • Theta join
  63. Different outer join
    • Left outer join
    • Right outer join
    • Full outer join
  64. Left outer join
    • (X1 = R1 left outer join R2 where __ = __;)
    • Normally join where there are duplicates then add tuples from the left relation that didn’t have a match
  65. Right outer join
    • (X1 = R1 right outer join R2 where __ = __;)
    • Normally join where there are duplicates then add tuples from the right relation that didn’t have a match
  66. Full outer join
    • (X1 = R1 left outer join R2 where __ = __;)
    • Normally join where there are duplicates then add tuples from the left relation and then the right one that didn’t have a match
  67. Semi join
    • (X1 = R1 semi join R2 where __ = __;)
    • Normally join and then drop all R2 attributes and duplicates
  68. Dividing relations rules
    • N = degree of dividee, K = degree of dividend.
    • K must be > n
    • K = m+n
    • N must come from same domain
  69. Dividing relations steps
    • Find n (degree of divisor)
    • Find k in dividend (remember k=m+n)
    • Makes sure n is from same domain
    • Get each corresponding value for values in divisor and place in their own set
    • Intersect sets
    • New set equals the result of intersection
Card Set
DBS quiz 1