Download Database Management Applications: Key Concepts and Queries and more Exams Advanced Education in PDF only on Docsity!
D427 - Database Management Applications Schema - The definition of how data in a database will be organized.
- Create
- Read
- Update
- Delete - The basis of interacting with databases can be defined using the CRUD acronym. What does CRUD stand for? DATE: YYYY-MM-DD DATETIME: YYYY-MM-DD HH:MM:SS - What is the DATE datatype syntax? What is the DATETIME datatype syntax? It will have 3 numbers before the decimal and 2 after. - What does DECIMAL(3,2) indicate? 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. 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. 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.
SELECT *
FROM CUSTOMER
WHERE CUSTNUMB BETWEEN 1 AND 2; - What would the SELECT statement look like if you use a BETWEEN. SELECT * FROM CUSTOMER WHERE HQCITY IN ('Atlanta', 'Chicago', 'Washington'); - What would the SELECT statement look like if you use a IN. SELECT * FROM CUSTOMER WHERE HQCITY LIKE 'W%'; - What would the SELECT statement look like if you use a LIKE. 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? 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? SELECT AVG/SUM/MIN/MAX/COUNT(Column_Name) FROM SALES WHERE Name = 'Matt'; - Command to use AVG/SUM/MIN/MAX/COUNT aggregate functions?
USE
USE MainDatabase - The _____ command tells the database system which database you're going to be using for the upcoming commands. SHOW 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. 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? 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. CREATE TABLE Employee ( ID INT, 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: 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:
CREATE TABLE Department ( Code INT UNSIGNED, ManagerID INT UNSIGNED, NAME VARCHAR(60), PRIMARY KEY (Code), FOREIGN KEY (ManagerID) REFERENCES Employee(ID) CHECK NAME (Name = 'Matt'); ); - How to you implement a CHECK constraint? CREATE TABLE Department ( Code INT UNSIGNED, ManagerID INT 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. INNER JOIN - An ________ JOIN returns only those rows where the linking values match in both of the tables or in result sets. SELECT RecipeTitle, Preparation, RecipeClassDescription FROM Recipe_Classes INNER JOIN Recipes ON Recipe_Classes.RecipeClassID = Recipes.RecipeClassID - INNER JOIN Example 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. String For example, SELECT LOWER('MySQL'); would return mysql. - ______ functions manipulate string values. HAVING SELECT CountryCode, SUM(Population) FROM City GROUP BY CountryCode HAVING SUM(Population) > 230000; - The _________ clause is used with the GROUP BY clause to filter group results. 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. SELECT Department.Name AS GROUP, Employee.Name AS SuperVisor FROM Department FULL JOIN Employee
FROM table_name WHERE condition WITH CHECK OPTION; - To prevent inserts or updates that appear to fail, databases that support view updates have an optional ____________ __________ __________ clause. When this is specified the database rejects inserts and updates that do not satisfy the view query WHERE clause. Instead, the database generates an error message that explains the violation. Entity A person is an entity. So is a building, a piece of inventory sitting on a shelf, a finished product ready for sale, and a sales meeting (an event). - an object or event in our environment that we want to keep track of. 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. Cardinalities - the maximum number of entities that can be involved in a particular relationship. Modality - the minimum number of entity occurrences that can be involved in a relationship.
- This is represented by an inner symbol beside the cardinality. associative entity - represents a logical data structure that holds the information related to the association between two or more anchor entities.
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.) 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. table scan - a database operation that reads table blocks directly, without accessing an index. index scan - a database operation that reads index blocks sequentially, in order to locate the needed table blocks. Hit ratio 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.
- Instead, the database:
access method - The way that we retrieve the data, based on it being stored in a particular file organization, is called the _______ ________.
- The items of interest are copied over into the index but the original text is not disturbed in any way.
- The items copied over into the index are sorted (alphabetized in the index at the back of a book).
- Each item in the index is associated with a "pointer" (in a book index this is a page number) pointing to the place in the text where the item can be found. - The index in the back of a book has three key elements that are also characteristic of information systems indexes: indexed-sequential - In an _________ ___________ file, the file is stored on the disk in order based on a set of field values (in this case the salesperson numbers) and an index is built over that same field.
- This allows both sequential and direct access by the key field, which can be an advantage when applications with different retrieval requirements share the file. True - TRUE/FALSE The simple linear index is not a good solution for indexing the records of a file. 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. TRUE - TRUE/FALSE
A file can only be hashed once, based on the values of a single field or a single combination of fields.
