Karteikarten
CMPT 355
Karten in diesem Stapel (190)
- 13 Alternative Database Models to the Relational ModelHierarchical, Network, and Object Oriented Models
- 2When was the relational model introduced and who introduced it?1970 by E.F. Codd
- 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. - 4Two properties of the Network ModelUses a graph-like structure
Each record can have many parents and many children. - 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 - 6Three types of data dependency.Ordering dependency
Indexing dependency
Access Path dependency - 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. - 8How does the relational model overcome Indexing dependency?Does not refer to indexes by name. (Indexing in data models is done for performance)
- 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 - 10What are the levels of the ANSI-SPARC Architecture?External Level, Conceptual Level, Internal Level
- 11What does a diagram of the ANSI-SPARC architecture look like (diagram)?
- 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. - 13What does DBA stand for?Database Administrator
- 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. - 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. - 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 - 17What is a mapping? ( Term introduced by ANSI-SPARC architecture)A consistent correspondence between schemas at different levels.
- 18What is an Instance? ( Term introduced by ANSI-SPARC architecture)The data in a database ( or portion thereof ) at a particular instance in time.
- 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 - 20What does DBMS stand for?Database Management System
- 21An architecture for implementing a DBMS with the level of data independence of the relational modelANSI-SPARC Architecture
- 22What is the degree of a table?The number of columns.
- 23What is the carnality of a table?The number of rows.
- 24Two other names of a Column of a table?Attribute or Field
- 25Two other names for a row of a table?Tuple or Record
- 26What is a table?A 2D data structure made up of rows and columns.
- 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. - 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.
- 29What are the five types of keys in the relational model?Candidate Key, Composite Key, Primary Key, Alternate Key, Foreign Key
- 30What is a Candidate Key? (Relational Model)A super key where no subset could be a super key itself.
- 31What is a Composite Key? (Relational Model)A key that is made up of more than one column.
- 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.
- 33What is a Alternate Key? (Relational Model)A candidate key that is not selected as the primary key.
- 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 - 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. - 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 - 37Two classifications of keysNatural key, Surrogates
- 38What is a natural key?A key that is meaningful and depends on the data.
e.g. a location name, an employee number - 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 - 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 - 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.
- 42Why aren't 1:1 relationships common?Most of the time this information would be combined into one table.
- 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
- 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.
- 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.
- 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). - 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.
- 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 - 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.
- 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. - 51T/F A null value is the same as a 0 or empty space (The Relational Model)False
- 52What are two built in forms of Integrity Constraints?Entity Integrity and Referential Integrity
- 53What is Entity Integrity?No column value that is part of a primary key can be null.
- 54What is Referential Integrity?A record cannot be created in a table with a foreign key that doesn't exist in the parent table.
- 55What are General Integrity Constraints?Additional constraints specified by the users.
- 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.
- 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.
- 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.
- 59What requirements must a view meet to be used to update the database?They must be limited to individual records in a single table.
- 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.
- 61What layer of the ANSI-SPARC architecture are view part of?External Layer
- 62What is a database?A collection of related data.
- 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 - 64Systems used before databases?File based Systems
- 65Examples of DBMS'PostgreSQL, Oracle, IBM DB2, MySQL, MariaDB, Microsoft SQL Server, SQLite
- 66This is the software that database administrators, database designers, application developers, and some users interact with.Database Management System
(DBMS) - 67A software system that enables users to define, create, maintain, and control access to the database.Database Management System
(DBMS) - 68What does a DBMS do?It provides controlled access to the database.
- 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. - 70How does a DBMS allow users to define the databases structure?With a Data Definition Language (DDL), Provided by a query language.
- 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.
- 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.
- 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. - 74Provides the Data Definition Language (DDL) and the Data Manipulation Language (DML)Query Language (SQL)
- 75What does SQL stand for?Structured Query Language
- 76What is the standard SQL?ANSI SQL
- 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 - 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 - 79Statement used to query data from one or more table? (SQL)SELECT
- 80Clause to query unique rows in a table (SQL)DISTINCT
- 81Clause to sort the result set (SQL)ORDER BY
- 82Clause to filter rows in the result set (SQL)WHERE
- 83Types of JOINs in SQLINNER JOIN
LEFT RIGHT
RIGHT JOIN
FULL JOIN - 84JOIN that returns records that have matching values in both tables (SQL)INNER JOIN
- 85Join that returns all records from the left table, and the matched records from the right table (SQL)LEFT JOIN
- 86Join that returns all records from the right table, and the matched records from the left table (SQL)RIGHT JOIN
- 87Join that returns all records when there is a match in either left or right table (SQL)FULL JOIN
- 88Used to to get the group rows into groups and apply aggregate function for each group. (SQL)GROUP BY
- 89Clause to filter groups (SQL)HAVING
- 90In a SELECT statement. This is the clause used to specify the table(s) where you want to get the data from.FROM
- 91In a SELECT statement. This is the clause where you specify a column or list of comma-separated columns.SELECT
- 92Used to terminate a statement;
- 93In a SELECT statement, this can be used as shorthand for all columns*
(Should be used in testing only) - 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. - 95Keywords used to specify the direction of ordering of the resultASC, DESC
- 96When using the column position in an order by statement, is the first column 0 or 1?1
- 97In a SELECT statement, this clause allows you to remove the duplicate rows in the result setDISTINCT
- 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(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.
- 100What form does the search condition in the WHERE clause have?left_expression COMPARISON_OPERATOR right_expression
(same line^) - 101SQL Comparison operator meaning "equal to" (SQLite)=
- 102SQL Comparison operator meaning "not equal to" (SQLite)<> or !=
- 103Logical Operator with meaning "returns 1 if all expressions are 1." (SQLite)ALL
- 104Logical Operator with meaning "returns 1 if both expressions are 1, and 0 if one of the expressions is 0." (SQLite)AND
- 105Logical Operator with meaning "returns 1 if any one of a set of comparisons is 1." (SQLite)ANY
- 106Logical Operator with meaning "returns 1 if a value is within a range." (SQLite)BETWEEN
- 107Logical Operator with meaning "returns 1 if a subquery contains any rows." (SQLite)EXISTS
- 108Logical Operator with meaning "returns 1 if a value is in a list of values." (SQLite)IN
- 109Logical Operator with meaning "returns 1 if a value matches a pattern" (SQLite)LIKE
- 110Logical Operator with meaning "reverses the value of other operators such as NOT EXISTS, NOT IN, NOT BETWEEN, etc." (SQLite)NOT
- 111Logical Operator with meaning "returns true if either expression is 1" (SQLite)OR
- 112What is the syntax of the SQLite BETWEEN operator?test_expression BETWEEN low_expression AND high_expression
- 113(T/F) Is the SQLite BETWEEN operator inclusive?True
- 114What is the result if any input of the BETWEEN operator is NULL SQLiteNULL
- 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
- 116The operator used to determine whether a value matches any value in a list or subquery. (SQLite)IN
- 117What is the syntax of the IN operator (SQLite)expression [NOT] IN (valuelist|subquery);
- 118Operator to use in the WHERE clause to query data based on partial information (SQLite)LIKE
- 119What does the % wildcard mean (SQLite)Matches any sequence of zero or more characters
- 120What does the _ wildcard mean (SQLite)Matches any single character.
- 121(T/F) Like operator is case sensitive?F. "A" Like "a" is true. however "A" LIKE "ä" ins't
- 122How to match a value that contains % or _ in SQLiteUse ESCAPE
e.g.
column_1 '%get_value%' ESCAPE '_'; - 123Statement to insert data into a table. SQLINSERT
- 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. - 125How to insert default values into a table SQLiteINSERT INTO *table* DEFAULT VALUES
- 126How to insert data into a table with data from another table SQLiteINSERT INTO *table2* SELECT
*row1*
...*rowN*
FROM
*table&1; - 127Statement to update existing data in a tableUPDATE
- 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 - 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.
- 130Statement to remove rows from a table. (SQLite)DELETE
- 131What is the syntax of the SQLite DELTE statementDELETE
FROM
table
WHERE
search_condition; - 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.
- 133Language about working with sets. Based on relational algebra/relational calculusSQL
- 134(T/F) SQL is proceduralFalse, SQL is declarative.
- 135Constants used in SQL statementsLiterals
- 136What is Brackus Naur Form?The conventional notation for SQL
- 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. - 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}] - 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.
- 140How to check if a value is or isn't null?IS NULL/ IS NOT NULL
- 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 - 142What are the five built in ISO standard functions? (SQL)COUNT, SUM, AVG, MIN MAX
- 143What does the COUNT function do? (SQL)returns the number of values in a specified column
- 144What does the SUM function do? (SQL)returns the summation of the values in a specified column
- 145What does the AVG function do? (SQL)returns the average of all values in a specified column
- 146What does the MIN function do? (SQL)returns the smallest value in a specified column
- 147What does the MAX function do? (SQL)returns the maximum value in a specified column
- 148How to rename a column for output (Alias)?use AS
- 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.
- 150What a column expression?A column expression specifies a column in a query. These can be Mathematical operations, aggregate functions or these combined.
- 151Column expressions can be simple column names. The can also be ____ or _______ or these combined.Mathematical operations, aggregate functions
- 152Which operations are case sensitive LastName = 'adams' LastName LIKE 'adams' LastName IN 'adams'= and IN, the LIKE operator is NOT CASE SENSITIVE
- 153By default is order by in ascending or descending order?ascending
- 154Does the COUNT aggregate function for a column count NULL valuesNO
- 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.
- 156How to round values (SQL)?ROUND(*value*, *decimal places*)
- 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 - 158What is the default JOIN if you just enter JOIN.INNER JOIN
- 159This operation allows you to link related rows from two tables.JOIN
- 160What is the syntax of an inner joinJOIN table2
ON - 161Difference between ON table2.keyColumn = table1.keyColumn and ON table1.keyColumn = table2.keyColumnNone, they are the same.
- 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 - 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 - 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
) - 165How to put a query inside a where statement?SELECT *
FROM albums
WHERE albumId IN
(SELECT DISTINCT (AlbumId)
FROM tracks
WHERE GenreId = 2); - 166Operator used to determine whether a value matches any value in a list or a sub query.IN
- 167What is the syntax of INexpression [NOT] IN (value_list|subquery);
- 168The name for a SELECT statement nested in another statement.Subquery
- 169What is a correlated subquery?A subquery that uses the values from the outer query.
- 170What is an ordinal subquery?A sub query that can be executed independantly
- 171Why is a correlated subquery inefficient?it is evaluated for each row processed by the outer query.
- 172This clause is used to constrain the number of rows returned by the query.LIMIT
- 173What is the syntax of a LIMIT statement?SELECT column_list
FROM table
ORDER BY column_1
LIMIT row_count - 174This keyword is used to move the starting row in the LIMIT statement.OFFSET
- 175Clause used to combine rows from two or more queries into a single result set.UNION
- 176What is the difference between UNION and UNION ALLThe UNION clause removes dupliate rows that exist, while the UNION ALL clause does not.
- 177Why does the UNION ALL clause process faster than the UNION clause?It does not remove duplicate rows.
- 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. - 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 - 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
- 181What is the syntax of the except operator??SELECT select_list1
FROM table1
EXCEPT
SELECT select_list2
FROM table2 - 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. - 183This operator compares the result sets of two queries and returns distinct rows that are output by both queriesINTERSECT
- 184What is the syntax of the INTERSECT operator?SELECT select_list1
FROM table1
INTERSECT
SELECT select_list2
FROM table2 - 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. - 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; - 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. - 188When is a WHERE clause applied when using the LEFT JOIN clause?It is applied after the match of the LEFT JOIN completes
- 189This clause combines columns from correlated tablesINNTER JOIN
- 190This clause specifies a search condition for a group?HAVING