Module Nine Flashcards
What is a database?
A database is an organized collection of information consisting of tables of information organized into columns and rows. Each row represents a separate record in the database, while each column represents a single field within a record.
Give two examples of flat files.
Spreadsheet and CSC (Comma Separated Value).
What does CSV stand for?
Comma Separated Values.
What is a CSV file?
A CSV file uses commas to identify the end of a column and a line feed for each row.
What does RDBMS stand for?
Relational Database Management System.
What does SQL stand for?
Structured Query Language.
Give four examples of RDBMS.
- Microsoft SQL Server
- Oracle Database
- MySQL
- Microsoft Office Access.
What is a “schema”?
The structure of the database in terms of the fields defined in each table and the relationship between primary and foreign keys.
What is a primary key?
A primary key is used to define the relationship between one table and another table in a database. For instance one customer may have more than one table, a primary key field/column would be the way they were linked.
What is a “foreign key”?
When a primary key in one table is referenced in another table.
What does GIGO stand for?
Garbage In, Garbage Out
What is a constraint?
Rules or conditions applied to database data, ensuring data integrity, consistency, and adherence to business rules.
What does parse mean?
Interpret
Three things about Unstructured Data.
- Much easier to create.
- Supports a much larger variety of data types.
- Examples would be (Image and text files, Word documents, and PowerPoints).
Two things about Semi-Structured data.
- A middle ground between structured and unstructured data.
- Data is identifiable via metadata.
Give two examples of semi-structured.
- Email data.
- XML.
What is metadata?
Associated information that helps identify data.
Two things about NoSQL.
- Database engines dealing with a mixture of structured, unstructured, and semi-structured data.
- Also called Not Only SQL.
Two things about a Document Database.
- Is an example of a semi-structured database.
- They can use the same or different structure types.
What is a key/value database?
A means of storing the properties of objectives without predetermining the fields used to define an object.
What are ‘database interfaces’ used for?
They are used to add or update information and to extract (or view) information from the database.
What are the two types of relational methods?
Those that define the database structure and those that manipulate the information in the database.
What does DDL stand for?
Data Definition Language.
What is Data Definition Language?
DDL refers to SQL commands that add to or modify the structure of the database.
Give four examples of DDL commands.
- CREATE
- ALTER TABLE
- DROP
- CREATE INDEX
What does the DDL command CREATE do? There are two things.
- It can create a new database on the RDBMS server.
- It can add a new table within an existing database.
What DDL command will create a database?
CREATE DATABASE
What DDL command will add a table to an existing database?
CREATE TABLE
What does the DDL command ALTER TABLE do? Name three things.
- It allows you to add, remove, and modify table columns.
- Change a primary or foreign key.
- Configure existing constraints.
What does the DDL command ALTER DATABASE do?
It can modify the properties of a whole database, such as its character sets.
What are the two DDL DROP commands?
- DROP TABLE
- DROP DATABASE
What does DDL command CREATE INDEX do?
It creates an index on a specific column(s) within a table, which essentially acts as a lookup table to quickly locate data.
What DDL command drops an index?
DROP INDEX
What does DML stand for?
Data Manipulation Language
What does Data Manipulation Language DML commands do?
It allows you to insert or update records and extracts information from records for viewing.
Which DML command adds a new row in a table of database?
INSERT INTO (table name)
Which DML command is used to change the value to one or more table columns?
UPDATE (Table name)
Which DML command deletes records from a table?
DELETE FROM (table name)
In DML an UPDATE command can be used with or without a WHERE statement. What happens in each case?
- If used with a WHERE statement it will filter the records to be updated.
- If no WHERE statement is used the command applies to all records in the table.
In DML a DELETE FROM command can be used with or without a WHERE statement. What happens in each case.
- If a WHERE statement is used it will filter through the records in the table.
- If a WHERE statement isn’t used, all records in the table will be deleted.
Which DML statement enables you to define a query to retrieve data from a database?
SELECT
Using DML how would I select all data in the specified “Customers” table?
SELECT * FROM Customers ;
Using DML how would I retrieve the values in the Name and Town fields for all records in the Customers table?
SELECT Name, Town FROM Customers ;
Using DML how would I, in alphabetical order, retrieve all records from the Customer table where the value in the Town field is equal to “Slough’?
SELECT * FROM Customers
WHERE Town = ‘Slough’
ORDER BY Name ;
What are SQL Permissions
A control system where specific user accounts can be granted rights over different objects in the database and the database itself.
What are database objects?
Tables, columns, views.
What happens when an account creates an SQL Permission?
That account becomes the owner of that object and has complete control over it? The owner cannot be denied permission.
How can a SQL Permissions owner be changed? Meaning what command?
ALTER AUTHORIZATION
How can I grant a specific rights to another user using SQL commands?
GRANT (permission) TO (user)
Using the SELECT statement grant specific rights to “james”.
GRANT SELECT ON Customers TO james
Note: don’t worry to much about this one.
How do I deny/take away rights from someone using SQL.
DENY (permission) TO (user)
Which is stronger DENY or GRANT?
DENY overrides GRANT, but cannot affect the owner.
Query/Report Builder
A GUI for those who don’t know SQL syntax.
What is Microsoft SQL Server?
A RDBMS
What is used to maintain and query data?
SQL
What type of database is a SQL database?
A Relational Database.
What is a widely used key/value format?
JSON
What are used to summarize and correlate data points?
Searches/Queries.
What type of database is a Key/Value Pair Database?
Non-relational Database.
What do you call data that has no rigid formatting?
Unstructured.
What data lacks formal structure, but has associated metadata?
Semi-structured.
What type of data is stored in a relational database?
Structured.
