Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
26 Cards in this Set
- Front
- Back
|
What is the purpose of a DBMS?
|
1. Data Integrity (Logging)
2. Simultaneous Updates (Locking) 3. Granular Access (Roles/Views) 4. Constraints (chokepoint to apply rules and ensure a defined format) 5. SQL (Standard Language for manipulating, building, and using DB) |
|
|
Server vs. Instance vs. Schema vs. Tablespace
|
*Server = computer running Linux with Oracle Installed
*Instance = A copy of Oracle *Schema = User/Account within an instance *Tablespace = table/folder created within the schema |
|
|
What is locking?
|
*Lock Manager allows only 1 user to edit a record at a time
*When done using the record, Manager allows next person in queue to use the record *Lock escalation occurs when someone is making a massive changes to multiple records, so all records are locked until the edits are done |
|
|
What is logging?
|
*Logging is a way to ensure data integrity
*Either every change is allowed to go through or none of the changes are allowed to go through. *Redo Log = snapshot of what database looked like in the past, if crash occurs then it can access the Redo Log *Undo Log = Remembers changes made within the business transaction; if the sequence of events fails midway through, the undo log will undo all of the changes made within the business transaction |
|
|
What is the data dictionary?
|
*Data dictionary holds all information about the structures
*Oracle built a SQL database to keep track of all users, tables, and views *Tables in the D.D. , can be selected like any table in the schema but they can not be edited or inserted upon |
|
|
Cardinality
|
how many relationships can be allowed between two different entities.
Must always be 1 to M or 1 to 1 If M to M relationship ... then create a table |
|
|
Normalization
|
*No arrays
*All attributes of an entity, have to DESCRIBE and entity |
|
|
Effective Dating
|
*update your data in a way that historical data is not lost
*When updating, give the item an end date and create a new record with a NULL end date...this is the new current piece of information |
|
|
Data Descriptive Language (DDL)
|
*DDL is what we use to build the structure of the data
*Data Types(required): (char, varchar2); Number;Date *Constraints (optional): Null or Not null, Primary Key, Foreign Key, etc. |
|
|
Scalar vs. Aggregate Functions
|
*Scalar functions work in every row of a clause (ex. UPPER(), LEN())
*Aggregate functions give summations and require a GROUP BY clause (Ex. SUM, COUNT) |
|
|
Having vs. Where clause difference?
|
*HAVING counts after the Aggregate function
*WHERE clause counts during the Aggregate function i.e. this can not tell us who manages more than two projects |
|
|
INTERSECT CLAUSE
|
*runs two queries and builds two answer sets (like a UNION)
*But only returns answer set of values that occur in each set |
|
|
MINUS CLAUSE
|
*Runs two queries, and builds two answer sets (like UNION)
*But only returns the values that DON’T OCCUR in the 2nd answer set |
|
|
Difference of Roles and Views, why use one instead of another?
|
*Give PRIVILEGES to ROLES
*Give VISUAL ACCESS always through VIEWS *UPDATE privileges given ROLES |
|
|
What is row level security and what three things does it link together?
|
*Grant only partial access to users, to control what they see for security measures
JOINS: 1) Actual Table 2) User ID of person accessing table 3) List of all records |
|
|
EVENT DRIVEN vs. COMMAND DRIVEN
|
*Event driven procedures are initiated by the database upon certain actions occurring
*They validate, and then change(if necessary) the data being inserted/updated *Ex. Car being priced too high or too low *Command Driven are initiated by arguments to the procedure *The user inputs values that the procedure then processes; either the values are acceptable, or the procedure comes up with an error message *Example of creating a funding record |
|
|
What is a cursor? When is it useful? What are its four parts?
|
* A procedure that allows us to go through the answer sets row by row, evaluate the row, and then run a procedure based on the data
*USEFUL if we have different circumstances in different rows that require different updates/inserts etc. *4 parts: 1. Declare -name cursor and write the SQL query 2. Open- run the query and point to the first row in the answer set 3. Fetch - retrieve rows 1 at a time from the answer set, analyzes them, then moves the cursor down to the next row 4. Close - commits any changes |
|
|
What is the EXISTS Clause? How does it differ from the IN Clause?
|
*Both EXISTS and IN are used to form subqueries.
*As soon as it finds 1 case that matches the subquery, then the clause will return True *EXIST function is more efficient as it confirms once it finds 1 row that matches the query *The IN clause looks for the value in a list of a subquery |
|
|
What is the DECODE function? How is it different than NULLS?
|
*Decode woks for any field, and NVL works on nulls
*Select emp_id, decode(emp_gender,’M’,’Male’,’F’,’Female’,null,’?’) |
|
|
Validations on FORM vs. Validations on PROCEDURE
|
Validations on forms do not use up much bandwidth as they provide filtering before a query is ran...can not run query until validations approve
*Problem with this is that you must consistently update validations and sync with page *Validations on procedure translate down, and provide an error if the values don’t work, allows for single edit and will automatically reflect the change |
|
|
What is a SYNONYM? How do we create them? Why are they helpful?
|
*Aliases for tables or views that are easier to work with
*Can HIDE the "ownership" and “location” of an object, providing another level of abstraction for the database, as a security measure * “Create synonym Budgets for bc_budget |
|
|
What does a Remote DB Link allow us to do? How is it different than replication (Materialized Views)?
|
*Remote DB Links create a remote link to another database in order to access information that you don’t have stored on your own database
*Query goes over to the alternate database, launches an account, runs the query, and returns the answer set to the original database over the link *If you can access the link, you have access to all the tables, commands and views involved with the link *Replication (Materialized Views) are tables that exist on a local database that are copies of a Master Table in another database *can be updated by sending out a redo Log *Materialized Views can be strong performance enhancers, particularly on "Read-Only" files |
|
|
How do we transform data from Operational to Analytical?
|
E.T.L.
1. Extract - Take the Data that CHANGES from day to day 2. Transform - expand the changed data, DENORMALIZE the records 3. Load - add info into the Analytical DB, typically just tacking the information onto the end of the available records |
|
|
What are the measures that Data Warehouse use to classify their measures of predictability in data mining?
|
1. Accuracy - how good of a predictor is it?
2. Coverage - how big of a sampling is it? 3. Interest - Difference between accuracy of entire population and that of a specific subset. |
|
|
What are the methods of predicting market trends?
|
1. Predictive - use history to see what we are looking for and then use this information to predict the future
2. Clustering - if you know characteristics about a subject then find a subject that ACTS SIMILAR and has SIMILAR attributes to make predictions 3. Association - What two things happen together i.e. Walmart and Beer example |
|
|
How does OBJECT ORIENTED DESIGN do?
|
* Further refines normalization by only allowing attributes to be in ONE location, even if they serve to classify the noun
*They do this by saying that the attribute must exist BECAUSE of the thing it’s describing *PERSON TABLE branches down into STUDENT TABLE and EMPLOYEE TABLE ***This lets us make an update in one location, which is transferred down the line of tables |
