dbm exam 3 - database construction

How are indices created in MySQL?

They’re automatically made on foreign keys, primary keys, and UNIQUE constraints.

types of indices in MySQL server

1. B-tree

2. hash

3. R-tree

b-tree indexing

What is an SQL view? What purposes do views serve?

A virtual table made from other tables. They allow simplified looks into complex databases.

CREATE VIEW EmployeePhoneView AS

SELECT 

    EMPLOYEE.LastName AS EmployeeLastName

    EMPLOYEE.FirstName AS EmployeeFirstName 

    EMPLOYEE.OfficePhone AS EmployeePhone 

FROM EMPLOYEE;

Consider that you have run the above statement to create the view. Now, to test the view, write an appropriate SQL SELECT statement.

SELECT * FROM EmployeePhoneView;

user-defined function

stored set of SQL statements that is called by name from another SQL statement

What is this code doing?

creating a user-defined function to concatenate FirstName and LastName

trigger

stored program that is executed by the DBMS whenever a specified event occurs

trigger types

1. BEFORE

2. INSTEAD OF

3. AFTER

SQL triggers are used with what language keywords?

DML (INSERT, UPDATE, DELETE)

stored procedure

program stored within the database and is compiled when used

A _ is assigned to a table or view and does not take parameters.

trigger

A _ is called by a user or DBA. It is assigned to a database, not a table or view.

stored procedure

A _ cannot invoke a trigger, accepts parameters, and gives a return value(s).

user-defined functions