Chapter 8: Databases

A collection of data that can be accessed by different application programs

Data stored in discrete files;
- Stored on computer;
- Can be accessed, altered or removed by the user

- More data redundancy because the same data is being stored in multiple different places
- Worse data integrity because duplicate data might be stored differently in certain places
- Lack of privacy; all users have equal access to it
- Difficult to perform complex searches; new program must be written each time

- Multiple tables are linked together; improves data integrity; reduces data redundancy
- Complex queries can easily be used to find very specific data
- User permissions can be setup to improve security and data privacy by preventing them from seeing data they should no

- Reduced data redundancy // less repeated data because each item of data is only stored once
- Maintains data consistency // improves data integrity changes in one table will automatically update in another linked data cannot be entered differently in two tables
- Program-data independence changes to the data do not require programs to be re-written
- Complex queries are easier to run
- Can provide different views so users can only see specific aspects of the database

Serves as the interface between the user and the database
- Developer Interface to create interactive features and generate reports
- Query processor to design new SQL queries
- Password and biometrics to protect against unauthorized access
- User accounts to restrict what a user can do (read/write)
- Automatic backups in case of data loss
- Encryption to make data incomprehensible to people

Data Redundancy Issue
- solved by storing data in separate linked tables, which reduces the duplication of data as most items of data are only stored once
- Items of data used to link tables by the use of foreign keys are stored more than once
- DBMS will flag any possible errors when any attempt is made to accidentally delete this type of item

Data Inconsistency Issue
- solved by storing most items of data only once, allowing updated items to be seen by all applications
- As data is not inconsistent, the integrity of the data stored is improved
- Consistent data is easier to maintain as an item of data will only be changed once, not multiple times, by different applications

Data Dependency Issue
- Data is independent of the applications using the database, so changes made to the structure of the data will be managed by DBMS and have little or no effect on the application using the database

Data management
- Uses a more structured approach to the management, organization, and maintenance of data in a database
- An already defined data structured can be used to set up and create the database

Data Dictionary
- store the metadata, including the definition of tables, attributes, relationship between tables and any indexing
-Can also define the physical storage of the data
- Improve the integrity of data stored, helping to ensure that it is accurate, complete and consistent

Data Modeling
To show the data structure of a database
e.g: E-R diagram

Logical Scheme
- A data model for a specific database that is independent of the DBMS used to build the database

Data security
- To prevent unwanted alteration, corruption, deletion, sharing data with others that has no access
- Using usernames, passwords to prevent unauthorized access
- Encryption of the data stored
- Using access rights — different level of access

Developer Interface
- Allows a developer to write queries in Structured Query Language (SQL)
- These queries are then processed and executed by the Query Processor
- Allow the construction of more complex queries to interrogate the database

Query Processor
- Takes a query written in SQL and processes it
- Includes a DDL interpreter, DML compiler, and a Query Evaluation Engine
- Any DDL Statements is interpreted and recorded in the database’s data dictionary
- They are then compiled into low level instructions that are executed by the query evaluation engine
- DML compiler will now optimize the query

- A language used to create, modify, and remove the data structures that form a database
- Written as scripts that uses syntax similar to a computer program
- To work on relational databases structure

- A language used to add, modify, delete, and retrieve data stored in a relational database
- Written in a script that similar to a computer program
- To work with the data stored in the relational database

A list of SQL commands that perform a given task; often stored in a file for reuse

Figuring out key fields and their dependencies from the flat file to create multiple tables to avoid data repetition. Contains no data repetition.

Creating relationships between tables while avoiding partial dependencies and many-to-many relationships, an extra table might be needed. Contains no partial dependencies

Figuring out non key fields and their dependencies to create an extra table and avoid data repetition. Contains no transitive dependencies

The file which keeps data of all entities during a transactional period which may change during the next transactional period

When a field only depends on one part of the composite primary key

The file that keeps the data of all entities

The file which holds the basic data of an entity that does not need to be repeated

A field that is unique for every record, it cannot be repeated or null like an ID

When multiple fields are used to make a primary key

A candidate key but not a primary key; alternative field to the primary key when primary key is unknown

A field which is common between two tables and hence is used to link them

A field which has the potential to be a primary key but is not as only one primary key is needed.

Stores metadata about the database like relationships, primary and foreign keys

Combination of interrelated fields

The headers of the table

A single entry

Formed when one table in a database has a foreign key that refers to a primary key in another table in the database

- Used to construct a relational database that has integrity and in which data redundancy is reduced
- Table that has not been normalized appear larger

Creating a secondary key on an attribute to provide fast access when searching on that attribute; indexing data must be updated when table data changes

Prevents users or applications from entering inconsistent data

It is an entity relationship between two primary key

It is an entity relationship between the primary key and the non primary key

It is an entity relationship between two non primary keys

Base 16, from 0-9 to A-F

- In MAC and IP Addresses
- Represent Colours in HTML
- In error messages and memory dumps

Kibi (Ki) = 1024 bytes
Mebi (Mi) = 1024 KiB
Gibi (Gi) = 1024 MiB
Tebi (Ti) = 1024 GiB

Kilo (k) = 1000 bytes
Mega (M) = 1000 Kb
Giga (G) = 1000 Mb
Tera (T) = 1000 Gb

Number system that uses 4 bits to represent each denary digit.
e.g:
- (19 in BCD): - 00011001
- 0001 (= 1) 1001 (= 9)
- 19

- Used to represent numbers in calculators and counters
- Allows you to accurately measure fractions
- Electronically code decimal numbers

ASCII, Extended ASCII, Unicode

American Standard Code for Information Interchange
- 128 characters
- Only from the English language
- 7 bits

- 256 characters
- Most European langauges including English
- 8 bits

- 10,000 characters
- variety of languages
- current standard
- 32 bits

The smallest unit of area in an image representing a single colour

Stores data about the image,
- Size
- Resolution
- Colour depth
- Number of colours

Number of pixels wide by the number of pixels high

Number of bits used to represent the colours in a pixel

Number of bits used to represent the smallest unit in an image file (or sound file);
- The larger the bit depth, the better the quality of the sound or colour image.

File size = (Width x Height x Bit depth (in bytes)) + Header bytes

- Reducing bit depth, fewer bits used to store each pixel in an image
- Reducing colour palette, less colours and therefore less bits per pixel
- Reduced image resolution, fewer pixels per unit area so less space is needed

Using Run Length Encoding (RLE), - Looks for a series of consecutive pixels that store the same colour,
-- Indexes the colour and the number of times it occurs.

- Reduce amplitude to only the range used, limited amplitudes mean fewer bits per sample
- Run-length-encoding, Where consecutive sounds are the same record the binary value of the sound and number of times it repeats
- Record the changes instead of the actual sounds

Images using 2D points to describe lines and curves and their properties; grouped to form geometric shapes

- Can be scaled without losing quality as it gets recalculated.
- Relatively small file size, stores equations not pixels.

The number of bits used to store each sample.
- Larger values mean more accurate reconstruction of sound but higher file size (more bits used to store the sound)

The number of samples taken per unit time

Sound size = sampling rate x sampling resolution x time

Continous real-world data

Discrete electrical data

The reduction of file size

- Reduces transfer time
- Saves bandwidth
- Saves storage space

Lossy and Lossless

File size compression where original data is lost and cannot be recovered

File size compression where original data is not lost and can be recovered

- Maintains quality
- Useful when all original data needs to be recovered

Pros:
- Creates a much smaller file size than lossless
-- Useful when significant size reduction is needed
Cons:
- Significant loss in quality