Flashcards
ITEC 670 Final
Cards in this deck (45)
- 1Data Definition Language (DDL)Commands that define a database, including creating, altering, and dropping tables and establishing constraints
- 2Data Manipulation Language (DML)Commands that maintain and query a database
- 3Data Control Language (DCL)Commands that control a database, including administering privileges and committing data
- 4Which players are over 30 years old or are less than 22 years old and have a player number less than 2000?SELECT *
FROM PLAYERS
WHERE AGE>30
OR (AGE<22 AND PLAYNUM<2000); - 5Which players are between 25 and 27 years old?SELECT PLAYNUM, PLAYNAME
FROM PLAYERS
WHERE AGE BETWEEN 25 AND 27; - 6DISTINCT- If you would like to retrieve just the unique records in specified columns, you can use the "DISTINCT" keyword.
- DISTINCT will discard the duplicate records for the columns you specified after the "SELECT" statement
- Example: SELECT DISTINCT ____ - 7LIKE Comparison OperatorCompares partial strings
Two wildcard characters are used:
'%' replaces an arbitrary number of characters '_' replaces a single arbitrary character - 8Example of LIKESELECT *
FROM Products
WHERE ProductName LIKE '%Ware%'
- STRINGS MUST BE IN SINGLE QUOTES - 9Find all of the players whose last names begin with "S". (use of wildcard)SELECT PLAYNUM, PLAYNAME
FROM PLAYERS
WHERE PLAYNAME LIKE 'S%'; - 10HAVINGThe HAVING clause should follow the GROUP BY clause if you are going to use it.
EXAMPLE:
SELECT dept, avg(salary)
FROM employee
GROUP BY dept
HAVING avg(salary) > 20000; - 11ORDER BY- Ascending order is by default
- If descending it would be, ORDER BY Age DESC; - 12INSERTThe insert statement is used to insert or add a row of data into the table
EXAMPLE:
Insert into UniversityEmployee
(UniversityID, first, last, age, Street, city, state)
values ('1234', 'Luke', 'Duke', 45, '2130 Boars Nest', 'Hazard Co', 'Georgia'); - 13UPDATE EXAMPLEUpdate "Student"
Set GPA = 3.4
Where "LastName" = 'Lenu' - 14DELETE EXAMPLEDELETE FROM table_name
delete from employee
where lastname = 'May'; - 15The four V's of Big Data- Volume
- Velocity
- Veracity
- Variety - 16Hadoop- Hadoop is an open-source framework that allows to store and process big data in a distributed environment across clusters of computers using simple programming models
- Designed to scale up from a single server to thousands of machines, with a very high degree of fault tolerance - 17Hadoop ComponentsTwo major layers:
Processing/computation (MapReduce)
Storage layer (Hadoop Distributed File System) - 18Core Hadoop Concepts- Applications are written in high-level code
- Nodes talk to each other as little as possible
- Data is spread among machines in advance - 19Moore's LawRoughly stated, processing power doubles every two years
- 20Where is data for a distributed system stored?SAN (Storage Area Network)
- 21Key Concepts Hadoop ContinuedHDFS: A distributed, fault tolerance file system
MapReduce: A parallel data processing framework
Hive : A query framework (like SQL)
PIG : A query scripting tool
HBase: realtime read/write access to your Big Data - 22HDFS, the Hadoop Distributed File SystemResponsible for storing data on the cluster
- 23MapReduce- The system used to process data in the Hadoop cluster
- Consists of two phases: Map, and then Reduce - 24Spark- Spark runs on top of existing Hadoop Distributed File System (HDFS)
- Provides support for deploying Spark applications in an existing Hadoop cluster
- Spark is an alternative to MapReduce rather than a replacement to Hadoop. - 25NoSQL- NoSQL is a class of database management system identified by its non-adherence to the widely used relational database management system (RDBMS) model with its structured query language (SQL).
- NOSQL has evolved to mean "Not Only" SQL - 26Why NoSQL databases?Two trends:
- The exponential growth of the volume of data generated by users and systems
- The increasing interdependency and complexity of data, accelerated by the Internet, Web 2.0, social networks
- NoSQL databases are useful when working with a huge quantity of data and the data's nature does not require a relational model for the data structure - 27Advantages of NoSQL Databases- Massive write performance.
- Fast key value look ups.
- Flexible schema and data types.
- No single point of failure.
- Fast prototyping and development.
- Out of the box scalability (particularly Horizontal scalability). - 28Vertical Scalability - NoSQL- Also called scaling up
- Increase the capacity of existing hardware or software by adding resources - 29Horizontal Scalability - NoSQLMeans that you scale by adding more machines into your pool of resources
- 30Basic Information about NoSQL- NoSQL involves more Programming and Less Database Design
- Does not use SQL as its query language
- May not give full ACID (Atomicity, consistency, isolation, durability) (what SQL databases follow) guarantees
- Distributed, fault-tolerant architecture - 31BASE..- An alternative to ACID
- Basically Available
- Soft state
- Eventual consistency
- What NoSQL databases follow - 32CAP TheoremStates that it is impossible for a distributed computer system to simultaneously provide all three of the following guarantees:
Consistency
All nodes see the same data at the same time
Availability
A guarantee that every request receives a response about whether it was successful or failed
Partition tolerance
The system continues to operate despite arbitrary message loss or failure of part of the system - 33Document v Key Value Databases- A document database is, at its core, a key/value store with one major exception.
- Instead of just storing any blob in it, a document db requires that the data will be store in a format that the database can understand (i.e. JSON, XML etc).
- In most doc DBs, that means that we can now allow queries on the document data - 34MongoDBData are organized in collections. A collection stores a set of documents.
- 35CouchDB- IN CouchDB, no schema is enforced, so new document types with new meaning can be added alongside the old.
- A CouchDB document is an object that consists of named fields. Field values may be:
strings, numbers, dates etc. - 36Columnar Databases- In Relational databases data is stored in the disk row by row.
- Where in Columnar databases data is stored in the disk column by column - 37Column-oriented DatabaseData is stored in column order
- 38Graph DatabasesA graph database is a database that uses graph structures for semantic queries with nodes, edges and properties to represent and store data.
- 39Cloud Computing- Is Internet-based computing, whereby shared resources, software and information are provided to computers and other devices on-demand, like electricity.
Massive scale.
- On-demand access: Pay-as-you-go, no upfront commitment.
- Data-intensive Nature: What was MBs has now become TBs, PBs and XBs.
- Supports new development and application Paradigms: Hadoop/MapReduce, NoSQL and others. - 40Cloud Computing Service ModelsInfrastructure as a Service
Platform as a Service
Software as a Service - 41Infrastructure as a Service- Provides various infrastructure components such as hardware, storage, and other fundamental computing resources.
- The consumer does not manage or control the underlying infrastructure but controls and configures operating systems, storage, deployed applications, and possibly, selected networking components (e.g., host firewalls). - 42Platform as a ServiceProvides a service that runs over an underlying infrastructure
- 43Software as a ServiceRuns on an underlying platform and infrastructure managed by the vendor and provides a self-contained operating environment used to deliver a complete application
- 44Types of Cloud- Private cloud is operated solely for an organization and the cloud may be on or off the premises.
- Community cloud is shared by several organizations and supports a specific community of customers that have similar information technology requirements.
- Public cloud has an infrastructure that is made available to the general public or large industry group.
- Hybrid cloud has an infrastructure that is composed of two or more clouds that remain unique entities but are bound together by standardized or proprietary technology. - 45SQL v. NoSQLSql = relational database, pre-defined schema
NoSQL = unstructured, non-relational database