EXAM 3 (Mods 6 - 8) Flashcards
List the 4 SQL Set Operators
Union, Union All, Intersect, Except
What do the Union and Union All operators do?
Union: Combine rows, with no duplicates, from separate queries.
Union All: Combine rows, allowing duplicates, from separate queries.
What does the Intersect operator do?
Returns all rows common to both queries
What is the result of the Except operator?
Returns rows that appear in the first query but not in the second query (analogous to subtraction).
What are some of the problems that arise when data is stored in one big long table (excel and flat files)?
- Potential for lots of redundant data
- Many updates needed when a single value is changed
- many deletes needed to keep data clean
- Easy for data to become out of sync or outdated
- Structure can be hard to analyze
Benefits of normalizing data into RDMS
Save disc space (not really a concern…CPU-Disc trade off)
Increased efficiency when managing data
Can use SQL -> Remove, insert, update, and delete anomalies
Increase data consistency
Explain the process of normalization
A process applied to the entities of an ER-Diagram to ensure they do not contain hidden repetitive entities that can lead to redundant data. If hidden entities are found, new tables must be made and their relationships defined.
What is the first normal form (1NF)?
Data should be atomic
- Each table cell should contain a single value.
- Each record needs to be unique.
A table with the following attribute value structure for its address column is an example of breaking which normal form?
- 61 William Nock cove, Winnipeg, MB, Canada R2M5S2
1NF
Describe the second normal form (2NF)
Remove functional dependencies on parts of a composite key.
If the value of an attribute changes only with the one part of a key changes and not the combination of the parts you have a 2NF inconsistency and you need to split the entities into separate table
*Only applies if you have a composite key. If table(s) primary key is based on a single attribute then 2NF does not apply!
What is the third normal form (3NF)?
Removes functional dependencies on non-key attributes.
- If value of an attribute is changing based on values of another non-key attribute data is not in 3NF and must split the table to resolve the hidden entity.
Special case of 3NF
Every column is a candidate key. Examples: periodic cable, almost every 2 column table
Four additional considerations for cleaning up ERDs
One-to-One
Chasm
Fan-trap
Redundant
Where to one-to-one relationships make sense/are appropriate?
Subtypes
When does a chasm occur?
When the participation in the relationship is not mandatory…results in no rows coming back when you query.
What are fan-traps?
Occur when you have one-many-many-one. Can cause too many unrelated rows to be returned. Take relationships ambiguous. Better to do many-one then one to many.
How to identify redundant relationships in ERD
- look for a loop in your ERD
- Each pathway needs to describe a different relationship, if they do then loop is valid and may have to stay.
6 key reasons to use DB forms
- Simplify the UI
- Streamline data entry process
- Add functionality to the DB
- Automate common tasks
- Make the end-user more efficient when they work w/ the DB
-Protect the base-data from corruption
What are the two views in LibreOffice for forms?
Design and Form
What is a subform?
A form within a form. the forms are linked based on fields
Why use reports?
- People love ‘paper’
- Can apply branding
- Standardize presentation of data
What are parameters, why are they used, PostGres syntax?
Substitute a value for a criterion.
They allow for some flexibility in your queries, reports and forms.
:<some_message></some_message>
What is a View?
A dynamic table. Essentially a an SQL SELECT statement (query) stored in the DB vs as a .sql file
general syntax for creating a view…?
CREATE OR REPLACE VIEW <name> AS SELECT...</name>
What are some advantages of VIEWs
-Presents “One-Big-Table” to the end user
-good for Reports in Base (forms like queries, reports like tables)
- Generate summary tables for an IS or GIS
- Can do spatial processing within a view query
-handy for bug workarounds
Which tag(s) encloses metadata and info in an html document?
<head>
<meta></meta>
</head>
how many html headings tags are there?
six
<h1> to <h6>
</h6></h1>
List the 8 common html form tags
<input></input>, <textarea>, <select>, <option>, <button>, <optgroup>, <fielset>, and <label></label></fielset></button></option></select></textarea>
what is the role of the <input></input> form tag?
Specifies an input field where the user can enter data. must specify a type attribute within the input tag.
What does the <textarea> tag define in an html doc?</textarea>
A multi-line text input control.
what html tag(s) is used to create a drop down list?
<select> is used to create a drop-down list structure
<option> is used within the select tag to define choices within the drop-down</option></select>
What is the <optgroup> tag used for?
groups related options in a <select> element (drop down list)</select>
what is the use case for the <fieldset> tag do in an html form?
group related elements in form and draws a box around them on the webpage.
List 10 input type attributes for html forms
button, checkbox, file, hidden, image, password, radio, reset, submit, and text
What are the 4 main elements, and their role, that contribute to an HTML 5 webpage?
1) H5 tags
2) CSS- for styling and structure
3) JavaScript- for DOM (Document Object Model) and BOM (Brower Object Model) manipulation, RIA (Rich Internet Application)
4) JSON- for data
Explain the different functionality of =, ==, and === in JavaScript
= assignment operator
== equality
=== equal value and equal type
Syntax for the three JavaScript logical operators
&&, ||, !
Where are {} used in JavaScript?
objects, loops, and functions
what are the 5 JavaScript loop types and their general syntax?
For (start; end ;increment){some block of code}
while (condition) {some block of code}
do {some block of code} while (condition);
for/in
-loops through the properties of an object
for/of
-loops through the values of an iterable object
-this is the most analogous to the python for loop
what’s the general syntax of a function declaration on JavaScript?
function someFunctionName() {some code}
what does the JSON.parse() function do?
Turns text into JS objects
What are the 6 popular JS Frameworks?
JQuery, DoJo, Angular, Polymer, React, and Vue
What are the three main JS mapping libraries?
Leaflet, OpenLayers3, and ESRI JS API
