Chapter 6: Databases Flashcards
Database
A structured collection of related data
A database is structured in a way that allows you to… (3)
- Search (find data)
- Sort (order the data)
- Add, update and remove (change the data)
Popular ways of sorting data include… (3)
- Alphabetical OR reverse alphabetical
- Numerical OR reverse numerical
- Chronological OR reverse chronological
What are the two main types of databases?
- Flat-file
2. Relational
Flat-file database
Stores all of its data in a single table
Pros and Cons of a Flat-file database (3 | 3)
Pros
- Simple and easy to implement
- Easy to access using many different applications
- Easy to understand - all information stored in one place
Cons
- Easy to extract information - less secure
- Data duplication makes data inconsistency more likely
- Searching process is time-consuming with larger databases
Relational database
Organises its data into related tables of records
Pros and Cons of a Relational database (4 | 2)
Pros
- Information only has to be written or updated in one area - less data duplication
- More efficient storage
- Flexible and well-established
- Standard data access language in SQL
Cons
- Harder to design and maintain
- Has a lot of overhead and complexity
Data duplication OR Data redundancy
Where the same data is stored more than once, unnecessarily
Data duplication leads to…
Data inconsistency
Data inconsistency
Where different and conflicting versions of the same data appear in different places, thus compromising data integrity
Entity
A single person, place or thing about which data can be stored
Table
A collection of data about a certain topic organised into records and fields
Record
Data stored about a particular entity within a table
Field
One specific data item being stored about a particular entity
Fill in the blanks:
Records form the _____ of a table. Fields form the table’s _____.
- Rows
2. Columns
Primary key
The field in a table that uniquely identifies a record
How many times can a primary key occur in a table?
Once
Foreign key
A field in one table that is the primary key in another, used to create a relationship between the two
Relationship
A connection between two tables
How do you form a relationship?
By including the primary key of one table as a foreign key in another
What are the three different kinds of relationships between tables?
- One-to-one
- One-to-many OR many-to-one
- Many-to-many
Index
A data structure defined along the columns of a database table, used to significantly speed up queries
How does an Index work?
It sorts the data by the most commonly searched columns
Pros and Cons of using an Index (4 | 2)
Pros
- Usually leads to much better performance
- Makes it possible to quickly retrieve data
- Can be used for sorting
- Unique indexes guarantee uniquely identifiable records in the database
Cons
- They decrease performance on inserts, updates and deletes
- They take up disk space
Primary index
The primary key field
SQL
Structured Query Language: a high level command language, used to search through and manipulate databases
Query
A request for information from a database based on specified criteria
Format of a Query that selects data from one or more Tables based on a certain condition in a specified order of one or more fields
SELECT [field(s)]
FROM [Table(s)]
WHERE [condition(s)]
ORDER BY [field(s)] [optional order keyword]
SELECT [field(s)]
Lists the Fields you want to display
FROM [Table(s)]
Lists the Table or Tables where the data will come from
WHERE [condition(s)]
Lists the search criteria
Logical operator
NOT, AND or OR. Used in complex criteria in queries
ORDER BY [field(s)] [optional order keyword]
Lists the Field or Fields by which the data is sorted in a certain order
What two keywords can be used with the ORDER BY clause?
- ASC: ascending
2. DESC: descending
What is the default order using ORDER BY?
Ascending, although the ASC may still be included for clarity
Format of a Query that inserts a Record into a Table (column names not specified)
INSERT INTO Table
VALUES ([values])
Format of a Query that inserts a Record into a Table (column names specified)
INSERT INTO Table ([columns])
VALUES ([respective values])
Do all of the Fields within a Record have to be filled?
No. Any unfilled Fields will have the NULL value
Format of a Query that changes the value of a Field when certain criteria is met
UPDATE Table
SET field = value
WHERE [condition(s)]
Format of a Query that deletes one or several Fields from a Record
DELETE [field(s)]
FROM Table
WHERE Primary_key = value
Format of a Query that deletes a Record
DELETE *
FROM Table
WHERE Primary_key = value
Format of a Query to delete a Table
DROP TABLE Table
Why should you be careful when using DELETE and DROP TABLE statements?
SQL doesn’t warn you before executing commands, so you could accidentally delete important information
Format of a Query to create a Table
CREATE TABLE Table ( column data_type [...] )
How do you show which Table a particular Field comes from when the Query involves more than one Table’s contents?
Dot notation
How would you refer to the title attribute of the Books Table?
Books.title
What does the * wildcard character mean?
All
What does the following Query do?
SELECT id, title
FROM Movies
WHERE duration >= 86 AND genre = ‘Horror’
ORDER BY duration ASC;
Lists the ID and title of any horror movies with a duration of 86 minutes or longer from the Movies table, in ascending order of duration
