Databases Flashcards
What is a database?
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
Benefits of databases?
- Make it fast to: access data, update data, search for data and present data in a suitable way
What are the layers of a Database?
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
What is data security?
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
What are files?
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
What are serial data files?
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
What are sequential data files?
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
What is indexing?
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
What is an indexed sequential data file?
Used for large Sequential Data Files
- Stored in order of key field AND index
What is a random access data file?
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
What are fixed length fields?
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
What are variable length fields?
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
What is a flat file database?
Simple databases with one table
A single spreadsheet is an example
used for simple data-handling requirements
What is a relational database?
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)
What is a hierarchal database?
Multiple tables
Tree like structure
Each child node linked to parent node
Structure of relational databases? (Including primary, secondary and foreign keys)
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
What are entity-relationship diagrams? Draw a one-to-many relationship, many-many relationship and one-one relationship
- 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
What is hashing?
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
What is data redundancy?
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
What is referential integrity?
ensures data integrity.
Referential Integrity refers to the accuracy and consistency of data within a database.
REMEMBER ####
Makes sure all foreign keys link to an existing/valid primary key.
Also makes sure if a primary key is deleted/updated, foreign keys are no longer valid
What is a DBMS? What does it create and what does it provide?
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
What is transaction processing?
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
