Data Structures (Database Fundamentals)

Question 1

What is a database?

Answer 1

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.

Question 2

Give two examples of flat files.

Answer 2

Spreadsheet and CSC (Comma Separated Value).

Question 3

What does CSV stand for?

Answer 3

Comma Separated Values.

Question 4

What is a CSV file?

Answer 4

A CSV file uses commas to identify the end of a column and a line feed for each row.

Question 5

What does RDBMS stand for?

Answer 5

Relational Database Management System.

Question 6

What does SQL stand for?

Answer 6

Structured Query Language.

Question 7

Give four examples of RDBMS.

Answer 7

1. Microsoft SQL Server
2. Oracle Database
3. MySQL
4. Microsoft Office Access.

Question 8

What is a “schema”?

Answer 8

The structure of the database in terms of the fields defined in each table and the relationship between primary and foreign keys.

Question 9

What is a primary key?

Answer 9

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.

Question 10

What is a “foreign key”?

Answer 10

When a primary key in one table is referenced in another table.

Question 11

What does GIGO stand for?

Answer 11

Garbage In, Garbage Out

Question 12

What is a constraint?

Answer 12

Rules or conditions applied to database data, ensuring data integrity, consistency, and adherence to business rules.

Question 13

What does parse mean?

Answer 13

Interpret

Question 14

Tell me 2 things about Unstructured Data.

Answer 14

1. Much easier to create.
2. Supports a much larger variety of data types.

Question 15

Two things about Semi-Structured data.

Answer 15

1. A middle ground between structured and unstructured data.
2. Data is identifiable via metadata.

Question 16

Give two examples of semi-structured.

Answer 16

1. Email data.
2. XML.

Question 17

What is metadata?

Answer 17

Associated information that helps identify data.

Question 18

Two things about NoSQL.

Answer 18

1. Database engines dealing with a mixture of structured, unstructured, and semi-structured data.
2. Also called Not Only SQL.

Question 19

Two things about a Document Database.

Answer 19

1. Is an example of a semi-structured database.
2. They can use the same or different structure types.

Question 20

What is a key/value database?

Answer 20

A means of storing the properties of objectives without predetermining the fields used to define an object.

Question 21

What are ‘database interfaces’ used for?

Answer 21

They are used to add or update information and to extract (or view) information from the database.

Question 22

What are the two types of relational methods?

Answer 22

Those that define the database structure and those that manipulate the information in the database.

Question 23

What does DDL stand for?

Answer 23

Data Definition Language.

Question 24

What is Data Definition Language?

Answer 24

DDL refers to SQL commands that add to or modify the structure of the database.

Question 25

Give four examples of DDL commands.

Answer 25

1. CREATE 2. ALTER TABLE 3. DROP 4. CREATE INDEX

Question 26

What does the DDL command CREATE do? There are two things.

Answer 26

1. It can create a new database on the RDBMS server. 2. It can add a new table within an existing database.

Question 27

What DDL command will create a database?

Answer 27

CREATE DATABASE

Question 28

What DDL command will add a table to an existing database?

Answer 28

CREATE TABLE

Question 29

What does the DDL command ALTER TABLE do? Name three things.

Answer 29

1. It allows you to add, remove, and modify table columns. 2. Change a primary or foreign key. 3. Configure existing constraints.

Question 30

What does the DDL command ALTER DATABASE do?

Answer 30

It can modify the properties of a whole database, such as its character sets.

Question 31

What are the two DDL DROP commands?

Answer 31

1. DROP TABLE 2. DROP DATABASE

Question 32

What does DDL command CREATE INDEX do?

Answer 32

It creates an index on a specific column(s) within a table, which essentially acts as a lookup table to quickly locate data.

Question 33

What DDL command drops an index?

Answer 33

DROP INDEX

Question 34

What does DML stand for?

Answer 34

Data Manipulation Language

Question 35

What does Data Manipulation Language DML commands do?

Answer 35

It allows you to insert or update records and extracts information from records for viewing.

Question 36

In DML an UPDATE command can be used with or without a WHERE statement. What happens in each case?

Answer 36

- 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.

Question 37

In DML a DELETE FROM command can be used with or without a WHERE statement. What happens in each case.

Answer 37

- 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.

Question 38

Which DML statement enables you to define a query to retrieve data from a database?

Answer 38

SELECT

Question 39

Using DML how would I select all data in the specified "Customers" table?

Answer 39

SELECT * FROM Customers ;

Question 40

Using DML how would I retrieve the values in the Name and Town fields for all records in the Customers table?

Answer 40

SELECT Name, Town FROM Customers ;

Question 41

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'?

Answer 41

SELECT * FROM Customers WHERE Town = 'Slough' ORDER BY Name ;

Question 42

What are SQL Permissions

Answer 42

A control system where specific user accounts can be granted rights over different objects in the database and the database itself.

Question 43

What are database objects?

Answer 43

Tables, columns, views.

Question 44

What happens when an account creates an SQL Permission?

Answer 44

That account becomes the owner of that object and has complete control over it? The owner cannot be denied permission.

Question 45

How can a SQL Permissions owner be changed? Meaning what command?

Answer 45

ALTER AUTHORIZATION

Question 46

How can I grant a specific rights to another user using SQL commands?

Answer 46

GRANT (permission) TO (user)

Question 47

Using the SELECT statement grant specific rights to "james".

Answer 47

GRANT SELECT ON Customers TO james Note: don't worry to much about this one.

Question 48

How do I deny/take away rights from someone using SQL.

Answer 48

DENY (permission) TO (user)

Question 49

Which is stronger DENY or GRANT?

Answer 49

DENY overrides GRANT, but cannot affect the owner.

Question 50

What is Query/Report Builder?

Answer 50

A GUI for those who don't know SQL syntax.

Question 51

What is Microsoft SQL Server?

Answer 51

A RDBMS

Question 52

What is used to maintain and query data?

Answer 52

SQL

Question 53

What type of database is a SQL database?

Answer 53

A Relational Database.

Question 54

What is a widely used key/value format?

Answer 54

JSON

Question 55

What are used to summarize and correlate data points?

Answer 55

Searches/Queries.

Question 56

What type of database is a Key/Value Pair Database?

Answer 56

Non-relational Database.

Question 57

What do you call data that has no rigid formatting?

Answer 57

Unstructured.

Question 58

What data lacks formal structure, but has associated metadata?

Answer 58

Semi-structured.

Question 59

What type of data is stored in a relational database?

Answer 59

Structured.

Question 60

Which DML command deletes records from a table?

Answer 60

DELETE FROM (table name)

Question 61

Which DML command is used to change the value to one or more table columns?

Answer 61

UPDATE (Table name)

Question 62

Which DML command adds a new row in a table of database?

Answer 62

INSERT INTO (table name)