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