Database

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

- Data stored in descrete file and on computer
- It can be altered, accessed and modify by the user

- There is 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 as all users have equal access to it
- Difficult to perform complex searches as a new program must be written each time

- Multiple tables are linked together which improves data integrity and 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

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
Ex: 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 niow optimize the query

- a language 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 the data stored in a relational database
- written in a script that is 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.
An 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