Databases Flashcards
1
Q
What is a database?
A
- A structured persistent store of data. Stored on the secondary storage devices of a computer system
- Provide security, auto backups and users with controlled access to data they need
2
Q
Benefits of databases?
A
-Make it fast to: access data, update data, search for data and present data in a suitable way
2
Q
What are the layers of a Database?
A
- External level: What the user sees. Acts like a filter on data in the database Designed to be useful for a particular job. User friendly UI. Hides unnecessary/restricted data. Allows user to interact with DB
- Conceptual view: Describes structure of DB, entities, attributes, data types, relationships, validation, tables. Hides details of physical storage structures
- Physical: How the data is actually stored in the DB, works with OS and DBMS to store and retrieve data from storage devices
3
Q
What is data security?
A
- Keeping data safe from accidental or deliberate loss and malicious attacks.
- Controls Who has access to the database and what they are permitted to do
4
Q
What are files?
A
- Data stores on a computers secondary storage medium that have names and are stores of binary data
- Can be a program, word processed document, image or music
5
Q
What are serial data files?
A
- Records organised one after another
- Only possible way to storage files on mediums such as tape
- Each data file is as large as the data to be stored
- No empty records = efficient
- Slow as it has to read through each data record until relevant record is found
- If data is modified then the complete altered file has to be re-written back to storage medium. Temp file created and replaces original until process is complete
6
Q
What are sequential data files?
A
- Same as serial but records are stored in order of a designated key field (Eg: alphabetical)
- Records can be found easier
- Data modification still raises same issues as serial
- Not great for data that has no particular order as data has to be sorted often
7
Q
What is indexing?
A
- Speed up searches
- Software searches for the start of the right section (like a book chapter), then directs the search to the right part of the main data store. After that a sequential search can look for the item required
- Records match length of data to be stored
- No wasted space
- Deleting a record will only delete index number, data still remains
- New records are added to end of files and index number is increased
- Eventually DB will have large amounts of wasteful data that is not indexed. Can be solved by rewriting the file and therefore removing any un-indexed data and re-indexing the rest
8
Q
What is an indexed sequential data file?
A
- Used for large Sequential Data Files
- Stored in order of key field AND index
9
Q
What is a random access data file?
A
- Each record is ID with a record number in sequence and each record occupies same space
- Quick access to data as it can calculate position based on record number
- Wasteful of storage as EVERYTHING is stored with same amount of memory
- Each time new file is added it is added to the end and the RN is Incremented
- When records are removed only the data is deleted, leaving an empty record. Overtime there can be many blank records
10
Q
What are fixed length fields?
A
- Suitable amount of space In a file is reserved for data
- Any unused bytes are packed with a character such as a space
- Less space efficient however processing is more straightforward because bytes can be counted to reach desired point. (After set ‘n’ number of bytes you will move onto next field)
- Quick to search
11
Q
What are variable length fields?
A
- Only store as much data as is needed. Eg “Fred’ takes 4 bytes and ‘Jennifer’ takes 8 bytes
- Space efficient
- Processing is harder as finding a particular record requires checking all the preceding entries sequentially
12
Q
What is a flat file database?
A
- Simple databases with one table
- A single spreadsheet is an example
- used for simple data-handling requirements
13
Q
What is a relational database?
A
- Multiple related tables
- Each table represents a single entity (real world object or person)
- Required for the storage of more complex data
- Tables are linked via common attributes to create relationships (Primary keys —> foreign keys)
- Reduces amount of duplicated data
- Entity is anything about which we store data. They have attributes (characteristics eg: student name, ID, subject level)
- Tables consist of Tuples (records)
14
Q
What is a hierarchal database?
A
- Multiple tables
- Tree like structure
- Each child node linked to parent node
15
Q
Structure of relational databases? (Including primary, secondary and foreign keys)
A
- Use fixed length fields
- Each column contains 1 data type
- No rule about order of rows in a table. When order is required indexes are added
- No rule about order of columns
- No two rows can be identical
- One column must be primary key therefore making each row unique
- Primary key is used to link a table to the foreign keys of other tables. Relationships link them together and are produced by having repeated fields across tables (foreign key)
- Secondary keys are not necessarily unique but can be useful for quick searching
16
Q
What are entity-relationship diagrams? Draw a one-to-many relationship, many-many relationship and one-one relationship
A
-A data modelling technique used to define a relational database
RELATIONSHIP DIAGRAMS CAN BE FOUND IN PHOTOS
-In relational databases we want tables to be related by one-to-many relationships
-A one-to-one relationship suggests that 2 entities should be in the same table
-A many-to-many relationship suggests the need for at least one more table to separate entities
18
Q
What is hashing?
A
- Method of transforming a string of characters in a record into a shortened form that can be used as a disk address
- This shortened form can be used to access a record from a database more easily than by using the complete original string
- Typically multiple records can produce the same hash values. In this case the data is located in the next available space on the storage medium, so some serial searching may be necessary
21
Q
What is data redundancy?
A
- Unnecessary duplication of data in database
- Repeated fields etc. We need some repeated fields to make links but to many leads to errors and wastes storage space
22
Q
What is referential integrity?
A
- one aspect of data integrity
- You cannot delete a record if it is linked to a record in another table
- a state the DB where inconsistent transactions are not possible
- eg; cannot enter a student for subject that doesn’t exist or not being able to delete a subject which a student is connected to
24
Q
What is a DBMS? What does it create and what does it provide?
A
- Database management system that creates and maintains a database. Eg: Microsoft Access
- Creates and uses: the structure, queries, views, individual tables, interfaces and outputs
- Provides: security, backups, index updating, enforcement of referential integrity and facilities to update and interrogate the DB
25
Q
What is transaction processing?
A
- attempts to provide a response to the user within a short time frame.
- not as time critical as real time
- features limited range of operations planned in advance, such as a bank account balance enquiry or withdrawal
26
Q
What is CRUD?
A
- relational databases must have certain basic functionality to be useful
- Create, Read, Update, Delete
- match to; INSERT, SELECT, UPDATE, DELETE. SQL commands
- 3 of the commands result in transaction taking place
27
Q
What is data integrity?
A
- transaction must not allow DB to be damaged else DB will be inconsistent
- DBMS ensures that DB changes from one consistent state to another
28
What is data corruption?
- opposite of data integrity
- caused by; hardware/software failures or electrical glitches
- can also be operator error or malpractice
29
What is ACID?
-protects integrity of a DB
30
What does the A in ACID stand for?
-Atomicity = a change is either completely
| performed or not at all
31
What does the C in ACID stand for?
-Consistency = a transaction must take the whole DB from one consistent state to another (bank transaction; total money in system must remain the same)
32
What does the I in ACID stand for?
- Isolation = a transaction must be performed in isolation so that the other processes/users cannot access that data until it is in a consistent sate
- while an operation is being performed on a record, the record is locked
33
What does the D in ACID stand for?
-Durability = once committed, a change must not be lost due to any subsequent system failures
34
What is the aim of DB normalisation?
- makes a DB more efficient process by which a collection of data is gradually organised into tables through a series of steps
- cumulative and the stages have to be worked through in succession
- makes sure the data in each table is all properly and completely related together
- attributes in a relation must not themselves be sets, multiple values for one tuple are not allowed
35
What is the First Normal Form (1NF)?
1. Eliminate duplicated columns from same table
2. Create separate tables for each group of related data
3. ID a primary key
- still not perfect as there are multiple repeating fields
36
What is the Second Normal Form (2NF)?
1. Check data is in 1NF
2. Remove any data sets that occur in multiple rows and move them to new tables
6. Create relationships between these new tables and previous tables by means of foreign keys (primary key from other tables)
37
What is Third Normal Form (3NF)?
1. check that the data is in 2NF
2. Remove any columns that are not dependent on the primary key
- "every non-key attribute in a table must depend on the key, the whole key and nothing but the key"
38
what are queries?
- main use of DB's
- used to isolate and display a subset of the data in a database. -They can take related data from multiple tables and present them in an easy-to-understand way
- used as the basis for a screen form of a printed report so that filtered data can be presented in some clear or standard way
39
What is the main way queries are performed?
Query By example (QBE) comes with most off the shelf DBMSs. User is presented with GUI into which can be dropped the fields required as well as setting up conditions to filter the results
40
What is SQL?
- the code behind queries that allow for more flexible ones to performed
- Fields required are separated by commas . SELECT is used to extract the required data from the data set and conditions can be applied using the WHERE clause
- Conditions can be tailored exactly to meet the operational requirements
41
What happens if multiple tables are being queried in one SQL statement?
- dot notation is used
- eg; if "customername" and "tableorder" are both being queried the fields will be written as "customername.name" and "tableorder.order"
42
What are wild card operations?
- '*' means everything
- '%' means one or many characters
- '_' means just one character
43
What else can SQL do?
- has features that allow the creation and modification of DB's
- rich set of commands and operators that can perform any data processing required on a relational DB
44
What are some other SQL commands?
- CREATE = allows creation of tables
- INSERT = allows data to be entered in DB's
- DROP = allows SQL program to remove indexes, tables, fields and whole databases (structural removal)
- DELETE = allows the removal of data from a table (data removal)
- JOIN = combines data from two or more tables using a duplicated field such as a customer number in both the customer table and the order table
45
Practice SQL
Do it
