Download Database Management Applications: Key Concepts and Queries and more Exams Computer Science in PDF only on Docsity!
1 / 18
D427 - Database Management
Applications
1. Schema: The definition of how data in a database will be organized.
2. - Create
- Read
- Update
- Delete: The basis of interacting with databases can be defined using the CRUD acronym. What does CRUD stand for?
3. DATE: YYYY-MM-DD
DATETIME: YYYY-MM-DD HH:MM:SS: What is the DATE datatype syntax? What is the DATETIME datatype syntax?
4. It will have 3 numbers before the decimal and 2 after.: What does DECIMAL(3,2) indicate?
5. Stored Procedures: a series of commands stored on the database. This allows the reuse of long or detailed queries instead of writing them for each use. It also
provides a safe way to deal with sensitive data, especially with those unfamiliar with SQL syntax.
6. Data definition language (DDL): involves instructing the DBMS software on what tables will be in the database, what attributes will be in the tables,
which attributes will be indexed, and so forth.
7. data manipulation languages (DMLs): refers to the four basic operations that can and must be performed on data stored in any DBMS (or in any
other data storage arrangement, for that matter): data retrieval, data update, insertion of new records, and deletion of existing records.
8. SELECT *
FROM CUSTOMER
2 / 18 WHERE CUSTNUMB BETWEEN 1 AND 2;: What would the SELECT statement look like if you use a BETWEEN.
9. SELECT *
FROM CUSTOMER
WHERE HQCITY IN ('Atlanta', 'Chicago', 'Washington');: What would the SELECT statement look like if you use a IN.
10. SELECT *
FROM CUSTOMER
WHERE HQCITY LIKE 'W%';: What would the SELECT statement look like if you use a LIKE.
11. A% - The "%" means that any string of characters can follow afterwards. The percent sign represents
zero or more arbitrary regular characters A_ - The _ means that there will be exactly one letter following the A. The underscore represents a single arbitrary regular character.: What are the two ways of using the LIKE command?
12. The clause can include the term ASC at the end to make ascending explicit or it can include DESC for
descending order.: The default order for ORDER BY is ascending. How can you make it descending order?
13. SELECT AVG/SUM/MIN/MAX/COUNT(Column_Name) FROM
SALES
WHERE Name = 'Matt';: Command to use AVG/SUM/MIN/MAX/COUNT aggregate functions?
14. o One is that the tables to be joined must be listed in the FROM clause.
o Two is that the join attributes in the tables being joined must be declared and matched to each other in the WHERE clause.: There are two specifications to make in the SELECT statement to make a join work.
15. SELECT SPNAME
FROM SALESPERSON, CUSTOMER
4 / 18 SHOW DATABASES - lists all databases in the database system instance. SHOW TABLES - lists all tables in the default database. SHOW COLUMNS FROM TableName - lists all columns in the TableName table of the default database. SHOW CREATE TABLE TableName - shows the CREATE TABLE statement for the TableName table of the default database.: You can use the statement to provide specified information about a database.
22. ALTAR TABLE TableName
Add ColumnName DataType ALTAR TABLE TableName DRIP ColumnName ALTAR TABLE TableName CHANGE CurrentColumnName NewColumnName NewDataType: The ALTAR Table adds, deletes, or modifies columns. What is the syntax for each of these?
23. LIMIT
Select * FROM CITY LIMIT 100; This will limit it to the first 100 rows.: Some tables may contain thousands or millions of rows, and selecting all rows can take a long time. MySQL has a clause that limits the number of rows returned by a SELECT statement.
24. CREATE TABLE Employee ( ID
INT,
5 / 18
NAME VARCHAR(60), PRIMARY KEY
(ID),
This would make the PRIMARY KEY the ID column.: To set the primary key, you would include it in the statement. The syntax is:
25. CREATE TABLE Department (
Code INT UNSIGNED, ManagerID INT UNSIGNED, PRIMARY KEY (Code), FOREIGN KEY (ManagerID) REFERENCES Employee(ID) ); This would make the ManagerID be a foreign key of the ID column on the employee table.: To set the foreign key, you would include it in the statement. The syntax is:
26. A signed number may be negative.
An unsigned number cannot be negative.: With integers, remember the ditterence between signed and unsigned numbers!
27. RESTRI
CT CASCADE SET
NULL SET
DEFAULT
7 / 18
UNSIGNED, NAME VARCHAR(60),
PRIMARY KEY (Code), CONSTRAINT DepartmentForeignKey FOREIGN KEY (ManagerID) REFERENCES Employee(ID) );: Table constraints may be named using the optional keyword, followed by the constraint name and declaration. If no name is provided, the database generates a default name.
31. INNER JOIN: An JOIN returns only those rows where the linking values match in both of the tables or in result
sets.
32. SELECT RecipeTitle, Preparation, RecipeClassDescription
FROM Recipe_Classes INNER JOIN Recipes ON Recipe_Classes.RecipeClassID = Recipes.RecipeClassID: INNER JOIN Example
33. OUTER: An JOIN asks your database system to return not only the rows that match on the criteria you specify (as the case with
INNER JOIN) but also the unmatched rows from either one or both of the two sets you want to link.
34. String
For example, SELECT LOWER('MySQL'); would return mysql.: functions manipulate string values.
35. HAVING
SELECT CountryCode, SUM(Population) FROM City GROUP BY CountryCode
8 / 18 HAVING SUM(Population) > 230000;: The clause is used with the GROUP BY clause to filter group results.
36. AS
SELECT EmployeeNames AS Names FROM Employee;: To simplify queries or result tables, a column name can be replaced with an alias. The alias follows the column name, separated by an optional keyword.
37. SELECT Department.Name AS GROUP,
Employee.Name AS SuperVisor FROM Department FULL JOIN Employee ON Manager = ID: FULL JOIN selects all left and right table rows, regardless of match.In a FULL JOIN result table, unmatched left table rows appear with NULL values in right table columns, and vice versa. What is the syntax of a full join?
38. equijoin
Most joins are equijoins.: An compares columns of two tables with the = operator.
39. non-equijoin: A compares columns with an operator other than =, such as < and
10 / 18
44. Attribute
Examples of attributes include an employee's employee number, the weight of an automobile, a company's address, or the date of a sales meeting.: a property or characteristic of an entity.
45. Cardinalities: the maximum number of entities that can be involved in a particular relationship.
46. Modality: the minimum number of entity occurrences that can be involved in a relationship.
- This is represented by an inner symbol beside the cardinality.
47. associative entity: represents a logical data structure that holds the information related to the association between two or more anchor entities.
48. Recursive Relationship
- If a part can be used to assemble several different kinds of other parts and is itself composed of many parts, two tables are required to implement the "PART contains PART" relationship.: is a relationship can exist between occurrences of the same entity set. (Naturally, such a condition is found within a unary relationship.)
49. single-level index: a file containing column values, along with pointers to rows containing the column value.
- The pointer identifies the block containing the row.
- In some indexes, the pointer also identifies the exact location of the row within the block.
- If an indexed column is unique, the index has one entry for each column value.
- If an indexed column is not unique, the index may have multiple entries for some column values, or one entry for each column value, followed by multiple pointers.
50. table scan: a database operation that reads table blocks directly, without accessing an index.
51. index scan: a database operation that reads index blocks sequentially, in order to locate the needed table blocks.
52. Hit ratio
11 / 18 When a SELECT query is executed, the database examines the WHERE clause and estimates hit ratio. If hit ratio is high, the database performs a table scan. If hit ratio is low, the query needs only a few table blocks, so a table scan would be inefficient.
o Looks for an indexed column in the WHERE clause.
o Scans the index.
o Finds values that match the WHERE clause.
o Reads the corresponding table blocks.
§ If the WHERE clause does not contain an indexed column, the database must perform a table scan.: the percentage of table rows selected by a query.
53. binary search
o The database first compares the search value to an entry in the middle of the index.
o If the search value is less than the entry value, the search value is in the first half of the index. If not, the
search value is in the second half.
o The database now compares the search value to the entry in the middle of the selected half, to narrow the
search to one quarter of the index.
o The database continues in this manner until it finds the index block contain- ing the search value.: the database repeatedly
splits the index in two until it finds the entry containing the search value:
13 / 18
63. division-remainder: - In the method of
hashing, we divide the key value of the record that we want to insert or retrieve by the number of record locations that we have reserved.
64. TINYINT UNSIGNED: 0 to 255
65. SMALLINT UNSIGNED: 0 to 65,
66. MEDIUMINT UNSIGNED: 0 to 16,777,
67. INT UNSIGNED: 0 to 4,294,967,
68. FLOAT: -3.4E+38 to 3.4E+
69. SQL Founders: American National Standards Institute (ANSI) and the International Organization for Stan- dardization (ISO)
70. DDL Commands: Create,
Alter, Drop, Rename, Truncate, Comment Change
71. DML (Dynamic Manipulation Language) Commands: Select Insert
Update Delete Merge Join
72. relational database model: database model based on a tabular data structure
14 / 18
73. How to use a database in SQL: USE DatabaseName
74. How to delete a database: DROP DatabaseName
75. How to view all the databases: SHOW DATABASES
76. How to view all columns within a specific database?: SHOW COLUMNS FROM TableName
77. What are the unary operators?: "NOT" and "-"
78. What happens if one of the sides of the operator is NULL?: The result is NULL
79. How to Insert into a table: INSERT INTO (columnName)
VALUES(rowName)
80. How to Update a table: UPDATE TableName SET
columnName = "One",columnName2 = "Two" WHERE ID = 8
81. How to delete a row from a table: DELETE FROM tableName WHERE ID = 1
82. What is a foreign Key: It refers to a primary key in another table. In the CREATE
TABLE you use this: FOREIGN KEY(ColumnName) REFERENCES OtherTableName(otherTableColumn)
83. Referential Integrity: A set of rules that Access uses to ensure that the data between related tables is valid.
84. If one foreign key in table B references table A, and a row is removed from table A what happens if
ON DELETE CASCADE is used on the foreign key?: The row in table A with the same foreign key gets deleted
85. How to add a primary key to an already existing column?: ALTER TABLE TableName ADD CONSTRAINT
newConstraintName PRIMARY KEY
16 / 18 clause
97. When can you flatten a Subquery?: if it has IN or EXISTS
98. When can you not flatten a subquery?: NOT EXISTS or GROUP BY
99. Steps for flattening a subquery?: - Keep the outer query the same.
- add INNER JOIN subqueryTableName
- add ON and put the first where clause condition in it.
- add WHERE and put the second condition in it (if it exists)
100. Does AND have a higher priority the OR?: yes, AND has higher precedence than OR
101. 4 sql components: 1.Tools
2. query processor
3. storage engine/storage manager
4. file system
102. query processor: Manages connections from multiple users and compiles queries into low-level instruc- tions for the storage engine
17 / 18
103. Storage Engine / Storage Manager: translates instructions generated by a query processor into low-level commands that access data on
storage media
104. What are the analysis steps in database design?: 1 Discover entities, relationships, and attributes
2 Determine cardinality
3 Distinguish strong and weak entities
4 Create supertype and subtype entities
105. What are the logical Steps in database design?: 5 Implement entities
6 Implement relationships
7 Implement attributes
8 Apply normal form
106. What makes a strong entity?: it must be 1-1(1) or in other words it must be unique,singular, and required.
107. What is first normal form?: must have a primary key
108. What is second normal form: non-key column depends on all composite primary keys
109. Heap Table: uses freespace and inserts happen at the first available freespace
110. Sort Table: has links and inserts of the bottom of the table but will edit the links to sorting is not out of order.
111. Hash Table: Uses a hash/modulus function, stores rows in buckets. Mutiple rows can fit into buckets
112. Single-Level Index: a file containing column values, along with pointers to rows containing the column value
