GoConqr has a new look! Let us know what you think.

Sign up free

Flashcards

CMPT 355

James Scarrow English 190 cards

CMPT 355

Cards in this deck (190)

  1. 13 Alternative Database Models to the Relational ModelHierarchical, Network, and Object Oriented Models
  2. 2When was the relational model introduced and who introduced it?1970 by E.F. Codd
  3. 3Three properties of the Hierarchical ModelUses a tree like structure.
    All relationships are one to many.
    Each record can have one parent and many children.
  4. 4Two properties of the Network ModelUses a graph-like structure
    Each record can have many parents and many children.
  5. 5Three ways the relational model is superior to past models.Describes data with its natural structure only.
    Supports operations based on set theory
    Promotes data independence
  6. 6Three types of data dependency.Ordering dependency
    Indexing dependency
    Access Path dependency
  7. 7How does the relational model overcome data dependency?Separates the presentation of data and the physical storage of data.
    Makes no assumptions regarding the internal ordering of the data.
  8. 8How does the relational model overcome Indexing dependency?Does not refer to indexes by name. (Indexing in data models is done for performance)
  9. 9What are three points about access path dependence?Applications designed to work with tree or graph like structures need to access data by predifined ways.
    Changes to this structure break the application.
    Avoiding this allows for easier data retrieval
  10. 10What are the levels of the ANSI-SPARC Architecture?External Level, Conceptual Level, Internal Level
  11. 11What does a diagram of the ANSI-SPARC architecture look like (diagram)?What does a diagram of the ANSI-SPARC architecture look like (diagram)?
  12. 12What is the Conceptual level of the ANSI-SPARC architecture and what does it do?Provides a mapping between external and internal levels as well as fitting the requirements of data independence.
    Describes what is in the database and how the items are related.
    Shows the entire relational structure as seen by the DBA.
  13. 13What does DBA stand for?Database Administrator
  14. 14What is the External level of the ANSI-SPARC architecture and what does it do?The users view of the database.
    Only includes the objects that the user is interested in seeing.
    May include items that are derived from the database but not stored directly.
  15. 15What is the Internal level of the ANSI-SPARC architecture and what does it do?The physical representation of the database on the computer.
    Describes the data stored on the computer.
    User by database system designers.
  16. 16What is a Schema? ( Term introduced by ANSI-SPARC architecture)A description of the database
    Can exist at each of the three ANSI-SPARC levels
  17. 17What is a mapping? ( Term introduced by ANSI-SPARC architecture)A consistent correspondence between schemas at different levels.
  18. 18What is an Instance? ( Term introduced by ANSI-SPARC architecture)The data in a database ( or portion thereof ) at a particular instance in time.
  19. 19What is Independence? ( Term introduced by ANSI-SPARC architecture)The immunity of one level of the database to changes at a lower level
    The ability to deal with one level of a database and for the DBMS to automatically maintain consistency
  20. 20What does DBMS stand for?Database Management System
  21. 21An architecture for implementing a DBMS with the level of data independence of the relational modelANSI-SPARC Architecture
  22. 22What is the degree of a table?The number of columns.
  23. 23What is the carnality of a table?The number of rows.
  24. 24Two other names of a Column of a table?Attribute or Field
  25. 25Two other names for a row of a table?Tuple or Record
  26. 26What is a table?A 2D data structure made up of rows and columns.
  27. 27What criteria must a table meet to be considered a relation. (8)Has a name that is distinct from others in the database.
    Each column has a distinct name.
    The sequence of columns is insignificant
    The sequence of rows is insignificant.
    There are no duplicate rows.
    Entries in the columns are atomic.
    Entries from columns are from the same domain, based on their type.
    Operations combining different data types are not allowed.
  28. 28What is a key in the relational model?A column or set of columns that uniquely identifies a row within a table. Also known as a super key.
  29. 29What are the five types of keys in the relational model?Candidate Key, Composite Key, Primary Key, Alternate Key, Foreign Key
  30. 30What is a Candidate Key? (Relational Model)A super key where no subset could be a super key itself.
  31. 31What is a Composite Key? (Relational Model)A key that is made up of more than one column.
  32. 32What is a Primary Key? (Relational Model)The candidate key that is selected to identify unique records in a table. A table always needs to have a primary key specified on creation.
  33. 33What is a Alternate Key? (Relational Model)A candidate key that is not selected as the primary key.
  34. 34What is a Foreign Key?A column, or set of columns, within a table that matches the candidate key of a table. Used to join multiple tables together
    when querying our database
  35. 35Three requirements when choosing a primary key.Should be stable (not a column(s) that will be changed)
    Can never be null
    Should be made up of the smallest number of columns possible to ensure uniqueness.
  36. 36Why should an SSN not be used as a private key?Privacy - most people don't want their SSN given out.
    Not guaranteed to be unique - bad data could be introduced
  37. 37Two classifications of keysNatural key, Surrogates
  38. 38What is a natural key?A key that is meaningful and depends on the data.
    e.g. a location name, an employee number
  39. 39What is a surrogate key?A key that is independent of the data; they don't really mean anything
    Usually an auto created sequential integer value
    Work well for foreign keys because the values will never change
  40. 40Four ways records may be related to each other:1:1 - One to One
    1:n - One to Many
    n:1 - Many to one
    n:n - Many to Many
  41. 41What is a one to one relationship?A relationship between two tables where each row in table A has at most one corresponding row in table B.
  42. 42Why aren't 1:1 relationships common?Most of the time this information would be combined into one table.
  43. 43What is a one to many relationship?A relationship between two tables where each row in table A can have several corresponding rows in table B
  44. 44What is an example of a one to many relationship?A customer has multiple sale receipt records, but each sale can only belong to one customer.
  45. 45What is a many to one relationship?A relationship between two tables where several rows in table A can correspond to a single row in table B.
  46. 46What is the difference between a one to many relationship and a many to one relationship. Give an example.It is vice versa. It depends which side of the relationship the entity is present on.
    e.g. if one department can employ for several employees then, department to employee is a one to many relationship (1 department employs many employees), while employee to department relationship is many to one (many employees work in one department).
  47. 47What is an example of a n:n relationship?A sale can contain multiple items and the same item can be purchased in multiple sales.
  48. 48How to represent a n:n relationship?Use an intermediary table to facilitate the relationship.
    If a sale can have many items and an item can be purchased in multiple sales Have a table called sale_lines with the item superKey and the sales superKey
  49. 49What is an Integrity Constraint? (The relational model)A way to ensure the data being added to the database conforms to the intended structure, preserving the integrity of the database.
  50. 50What does Null represent? (The relational Model)A column value that does not exist or is not applicable for the record.
    The same as a logical unknown.
  51. 51T/F A null value is the same as a 0 or empty space (The Relational Model)False
  52. 52What are two built in forms of Integrity Constraints?Entity Integrity and Referential Integrity
  53. 53What is Entity Integrity?No column value that is part of a primary key can be null.
  54. 54What is Referential Integrity?A record cannot be created in a table with a foreign key that doesn't exist in the parent table.
  55. 55What are General Integrity Constraints?Additional constraints specified by the users.
  56. 56What is a view? (The relational model)A virtual table that doesn't exist in the database but can be reproduced by the request of a user.
  57. 57Views are Dynamic? What does this mean?If changes are made to the base table(s) involved in the view, the view is automatically updated.
  58. 58How do views provide a security mechanism?By limiting access to only part of a record. Users may not be aware of columns or rows that are missing from the view.
  59. 59What requirements must a view meet to be used to update the database?They must be limited to individual records in a single table.
  60. 60T/F Updates are allowed for views combining multiple records?F. Since the system doesn't know how to break it out into individual updates.
  61. 61What layer of the ANSI-SPARC architecture are view part of?External Layer
  62. 62What is a database?A collection of related data.
  63. 63Disadvantages of file based systems?Don't scale well.
    Don't work well when you need complicated cross-referencing and reporting of data.
    Cause very decentralized data
    Lots of data duplication
    Program-Data Dependence
    Incompatible file types
    No way to create ad-hoc queries
    Lack of security, recovery, concurrency
  64. 64Systems used before databases?File based Systems
  65. 65Examples of DBMS'PostgreSQL, Oracle, IBM DB2, MySQL, MariaDB, Microsoft SQL Server, SQLite
  66. 66This is the software that database administrators, database designers, application developers, and some users interact with.Database Management System
    (DBMS)
  67. 67A software system that enables users to define, create, maintain, and control access to the database.Database Management System
    (DBMS)
  68. 68What does a DBMS do?It provides controlled access to the database.
  69. 69Five features may a DBMS provide?A security system, prevents unauthorized users accessing the database.
    An integrity system, which maintains the consistency of stored data.
    A concurrency system, allows shared access of the database
    A recovery control system, restores the database to a previous consistent state following hardware or software failure.
    A user-accessible catalog, which contains descriptions of the data in the database.
  70. 70How does a DBMS allow users to define the databases structure?With a Data Definition Language (DDL), Provided by a query language.
  71. 71What is a Data Definition Language (DDL)?Part of the DBMS, provided by the query language. Used by the database administrator and designer. Allows users to define the database's structure.
  72. 72How does a DBMS allow users to create, read, update, and delete (CRUD) data?With a Data Manipulation Language (DML), Provided by a query language.
  73. 73What is a Data Manipulation Language?Part of the DBMS, provided by the query language.
    Allows users to create, read, update, and delete (CRUD) data.
    Used to interface with applications, create reports or by advanced users.
  74. 74Provides the Data Definition Language (DDL) and the Data Manipulation Language (DML)Query Language (SQL)
  75. 75What does SQL stand for?Structured Query Language
  76. 76What is the standard SQL?ANSI SQL
  77. 77Advantages of a Relational Database Management System (SWL) over a file based system.Data integrity
    • Data shareability
    • Data availability
    • Database evolvability
    • Controlling redundancy
    • Improved security
    • Increase concurrency
  78. 78Disadvantages of a Relational Database Management System (SWL) over a file based system.• Complexity
    • Cost of DBMS
    • Cost of converting legacy systems – e.g. ADP
  79. 79Statement used to query data from one or more table? (SQL)SELECT
  80. 80Clause to query unique rows in a table (SQL)DISTINCT
  81. 81Clause to sort the result set (SQL)ORDER BY
  82. 82Clause to filter rows in the result set (SQL)WHERE
  83. 83Types of JOINs in SQLINNER JOIN
    LEFT RIGHT
    RIGHT JOIN
    FULL JOIN
  84. 84JOIN that returns records that have matching values in both tables (SQL)INNER JOIN
  85. 85Join that returns all records from the left table, and the matched records from the right table (SQL)LEFT JOIN
  86. 86Join that returns all records from the right table, and the matched records from the left table (SQL)RIGHT JOIN
  87. 87Join that returns all records when there is a match in either left or right table (SQL)FULL JOIN
  88. 88Used to to get the group rows into groups and apply aggregate function for each group. (SQL)GROUP BY
  89. 89Clause to filter groups (SQL)HAVING
  90. 90In a SELECT statement. This is the clause used to specify the table(s) where you want to get the data from.FROM
  91. 91In a SELECT statement. This is the clause where you specify a column or list of comma-separated columns.SELECT
  92. 92Used to terminate a statement;
  93. 93In a SELECT statement, this can be used as shorthand for all columns*
    (Should be used in testing only)
  94. 94Why should you avoid using the asterisk (*) in real application development?You should control what SQLite returns to your application.
    If someone removes a column your application would not be working properly, you wouldn't know that because the query wouldn't throw any errors.
    If someone adds more columns your application may work but gets more data than needed and creates more I/O overhead between the database and application.
  95. 95Keywords used to specify the direction of ordering of the resultASC, DESC
  96. 96When using the column position in an order by statement, is the first column 0 or 1?1
  97. 97In a SELECT statement, this clause allows you to remove the duplicate rows in the result setDISTINCT
  98. 98(T/F) When using the DISTINCT clause, all columns in a row will be used to evaluate the duplicate.False, only columns that are specified after the distinct clause will be used to evaluate the duplicate.
  99. 99(T/F) When using a distinct clause, a combination of columns specified will be used to evaluate duplicates. Must have identical values in each column to be a duplicate.True.
  100. 100What form does the search condition in the WHERE clause have?left_expression COMPARISON_OPERATOR right_expression
    (same line^)
  101. 101SQL Comparison operator meaning "equal to" (SQLite)=
  102. 102SQL Comparison operator meaning "not equal to" (SQLite)<> or !=
  103. 103Logical Operator with meaning "returns 1 if all expressions are 1." (SQLite)ALL
  104. 104Logical Operator with meaning "returns 1 if both expressions are 1, and 0 if one of the expressions is 0." (SQLite)AND
  105. 105Logical Operator with meaning "returns 1 if any one of a set of comparisons is 1." (SQLite)ANY
  106. 106Logical Operator with meaning "returns 1 if a value is within a range." (SQLite)BETWEEN
  107. 107Logical Operator with meaning "returns 1 if a subquery contains any rows." (SQLite)EXISTS
  108. 108Logical Operator with meaning "returns 1 if a value is in a list of values." (SQLite)IN
  109. 109Logical Operator with meaning "returns 1 if a value matches a pattern" (SQLite)LIKE
  110. 110Logical Operator with meaning "reverses the value of other operators such as NOT EXISTS, NOT IN, NOT BETWEEN, etc." (SQLite)NOT
  111. 111Logical Operator with meaning "returns true if either expression is 1" (SQLite)OR
  112. 112What is the syntax of the SQLite BETWEEN operator?test_expression BETWEEN low_expression AND high_expression
  113. 113(T/F) Is the SQLite BETWEEN operator inclusive?True
  114. 114What is the result if any input of the BETWEEN operator is NULL SQLiteNULL
  115. 115When does NOT BETWEEN return trueif the value of test_expression is less than the value of low_expression or greater than the value of high_expression
  116. 116The operator used to determine whether a value matches any value in a list or subquery. (SQLite)IN
  117. 117What is the syntax of the IN operator (SQLite)expression [NOT] IN (valuelist|subquery);
  118. 118Operator to use in the WHERE clause to query data based on partial information (SQLite)LIKE
  119. 119What does the % wildcard mean (SQLite)Matches any sequence of zero or more characters
  120. 120What does the _ wildcard mean (SQLite)Matches any single character.
  121. 121(T/F) Like operator is case sensitive?F. "A" Like "a" is true. however "A" LIKE "ä" ins't
  122. 122How to match a value that contains % or _ in SQLiteUse ESCAPE
    e.g.
    column_1 '%get_value%' ESCAPE '_';
  123. 123Statement to insert data into a table. SQLINSERT
  124. 124What are the three steps to using an insert statement SQLiteSpecify a table to which you want to insert data after the INSERT INTO clause.
    (Optional) Add a comma-separated list of columns separated by parenthesis ()
    Add a comma-saparated list of values after the VALUES clause.
  125. 125How to insert default values into a table SQLiteINSERT INTO *table* DEFAULT VALUES
  126. 126How to insert data into a table with data from another table SQLiteINSERT INTO *table2* SELECT
    *row1*
    ...*rowN*
    FROM
    *table&1;
  127. 127Statement to update existing data in a tableUPDATE
  128. 128What is the syntax to update atable (SQLite)UPDATE table,
    SET col1 = newval1,
    col2 = newval2
    WHERE
    search_condition
    ORDER column_or_expression
    LIMIT row_count OFFSET offset
  129. 129What happens if you use a negative value in the LIMIT clause of an UPDATE statement. (SQLite)There is no limit and updates all rows that meet the condition preceding the WHERE clause.
  130. 130Statement to remove rows from a table. (SQLite)DELETE
  131. 131What is the syntax of the SQLite DELTE statementDELETE
    FROM
    table
    WHERE
    search_condition;
  132. 132How do you represent a 1:n/n:1 relationship in a database diagram?Draw a line between the foreign key value on the one table and the primary key value (used to populate the foreign key). Write a 1 by the primary key value and a * by the foreign key value.
  133. 133Language about working with sets. Based on relational algebra/relational calculusSQL
  134. 134(T/F) SQL is proceduralFalse, SQL is declarative.
  135. 135Constants used in SQL statementsLiterals
  136. 136What is Brackus Naur Form?The conventional notation for SQL
  137. 137What are the four rules in Brackus Naur Form?Reserved keywords such as CREATE, INSERT, SELECT are written in uppercase
    User-Defined words are written in lowercase
    Each clause of a statement should start on a new line
    Clauses with several parts should appear on their own line.
  138. 138What is the form of a SELECT statement in SQL?SELECT {column expressions}
    FROM {table name(s)}
    {JOIN Clause(s)}
    [WHERE {conditions}]
    [GROUP BY {column list}]
    [ORDER BY {column list}]
  139. 139Why should you select all columns manually rather than using *If a column is added you may not want that column in the query. If a column is deleted you will want an error if that column isn't showing. It's better have control over which columns are retrieved.
  140. 140How to check if a value is or isn't null?IS NULL/ IS NOT NULL
  141. 141What are the order of operations when SELECTing with WHEREexpressions are evaluated from left to right
    subexpressions in brackets are evaluated first
    NOTs are evaluated before ANDs and
    ORs
    ANDs are evaluated before ORs
  142. 142What are the five built in ISO standard functions? (SQL)COUNT, SUM, AVG, MIN MAX
  143. 143What does the COUNT function do? (SQL)returns the number of values in a specified column
  144. 144What does the SUM function do? (SQL)returns the summation of the values in a specified column
  145. 145What does the AVG function do? (SQL)returns the average of all values in a specified column
  146. 146What does the MIN function do? (SQL)returns the smallest value in a specified column
  147. 147What does the MAX function do? (SQL)returns the maximum value in a specified column
  148. 148How to rename a column for output (Alias)?use AS
  149. 149When using the = operator with a string is it case sensitive? With the LIKE clause?When using = it is case sensitive. When using LIKE it is not case sensitive.
  150. 150What a column expression?A column expression specifies a column in a query. These can be Mathematical operations, aggregate functions or these combined.
  151. 151Column expressions can be simple column names. The can also be ____ or _______ or these combined.Mathematical operations, aggregate functions
  152. 152Which operations are case sensitive LastName = 'adams' LastName LIKE 'adams' LastName IN 'adams'= and IN, the LIKE operator is NOT CASE SENSITIVE
  153. 153By default is order by in ascending or descending order?ascending
  154. 154Does the COUNT aggregate function for a column count NULL valuesNO
  155. 155When Dividing all integers will SQL perform integer division?Yes, you need to add a .0 after one of the integers to convert it to a double and get regular division.
  156. 156How to round values (SQL)?ROUND(*value*, *decimal places*)
  157. 157If you have a result, how do you perform string contatination in column results?||
    e.g
    SELECT * minutes || ' minutes'
    Each value in the column will have the number of minutes, then the word minutes e.g.
    23 minutes
    43 minutes
  158. 158What is the default JOIN if you just enter JOIN.INNER JOIN
  159. 159This operation allows you to link related rows from two tables.JOIN
  160. 160What is the syntax of an inner joinJOIN table2
    ON
  161. 161Difference between ON table2.keyColumn = table1.keyColumn and ON table1.keyColumn = table2.keyColumnNone, they are the same.
  162. 162How to avoid ambiguous name errors in SQL when doing joinsIn your column selections prefix the columns with the table name e.g.
    SELECT
    tracks.AlbumId,
    albumns.AlbumId
  163. 163Can you do multiple inner joins?Yes e.g.
    FROM tracks
    JOIN albums
    ON albums.AlbumId = tracks.AlbumId
    JOIN artists
    ON artists.ArtistId = albums.ArtistId
  164. 164How to put a query inside a query?Use ( ) e.g.
    SELECT *
    FROM
    (
    SELECT *
    FROM invoice_items AS II
    JOIN tracks AS T ON T.TrackId = II.TrackID
    )
  165. 165How to put a query inside a where statement?SELECT *
    FROM albums
    WHERE albumId IN
    (SELECT DISTINCT (AlbumId)
    FROM tracks
    WHERE GenreId = 2);
  166. 166Operator used to determine whether a value matches any value in a list or a sub query.IN
  167. 167What is the syntax of INexpression [NOT] IN (value_list|subquery);
  168. 168The name for a SELECT statement nested in another statement.Subquery
  169. 169What is a correlated subquery?A subquery that uses the values from the outer query.
  170. 170What is an ordinal subquery?A sub query that can be executed independantly
  171. 171Why is a correlated subquery inefficient?it is evaluated for each row processed by the outer query.
  172. 172This clause is used to constrain the number of rows returned by the query.LIMIT
  173. 173What is the syntax of a LIMIT statement?SELECT column_list
    FROM table
    ORDER BY column_1
    LIMIT row_count
  174. 174This keyword is used to move the starting row in the LIMIT statement.OFFSET
  175. 175Clause used to combine rows from two or more queries into a single result set.UNION
  176. 176What is the difference between UNION and UNION ALLThe UNION clause removes dupliate rows that exist, while the UNION ALL clause does not.
  177. 177Why does the UNION ALL clause process faster than the UNION clause?It does not remove duplicate rows.
  178. 178What are the 5 rules to union data?The number of columns in all queries must be the same.
    The corresponding columns must have the compatible data type.
    The column names of the first query determine the column names of the combined result set.
    The GROUP BY and HAVING clauses are applied to each individual query, not the final result set.
    The ORDER BY clause is applied to the combind result set, not within the individual result set.
  179. 179When are teh GROUP BY, ORDER BY, and HAVING clauses applied when using UNION ALLGROUP BY and HAVING are applied to each individual query
    ORDER BY is applied to the combined result set
  180. 180This operator compares the result sets of two queries and returns distinct rows from the left query that are not output by the right query.EXCEPT
  181. 181What is the syntax of the except operator??SELECT select_list1
    FROM table1
    EXCEPT
    SELECT select_list2
    FROM table2
  182. 182What rules must the queries conform to when using the EXCEPT operator?The number of columns in the select lists of both queries must be the same.
    The order of the columns and their types must be comparable.
  183. 183This operator compares the result sets of two queries and returns distinct rows that are output by both queriesINTERSECT
  184. 184What is the syntax of the INTERSECT operator?SELECT select_list1
    FROM table1
    INTERSECT
    SELECT select_list2
    FROM table2
  185. 185What are the rules when combining the result sets of two queries with the INTERSECT operator?the number and the order of the columns in all queries must be the same.
    the data types must be comparable.
  186. 186What is the syntax of a LEFT JOIN clause?SELECT
    a,
    b
    FROM
    A
    LEFT JOIN B ON A.f = B.f
    WHERE search_condition;
  187. 187What does the LEFT JOIN RETURN?Rows in the A table (left table) that have corresponding rows in the B table.
    Rows in the A table and the rows in the B table filled with NULL values in case the row in the A table does not have any corresponding rows in the B table.
  188. 188When is a WHERE clause applied when using the LEFT JOIN clause?It is applied after the match of the LEFT JOIN completes
  189. 189This clause combines columns from correlated tablesINNTER JOIN
  190. 190This clause specifies a search condition for a group?HAVING

Information

Description
databases
Created by
James Scarrow
Views
11
Rating

Feedback

What is it about?

What do you think of the new look?

How do you like it?