Informatics Practices · Ch 6 — Database Concepts
Summary
Summary
- A file in a file system is a container used to store data in a computer; data files can be created, searched, modified and deleted, but each file stands on its own.
- The file system approach suffers from data redundancy (the same data repeated in several files), data inconsistency (copies of the same data disagreeing after an update), data isolation (data scattered across files that cannot easily be combined), data dependence (programs breaking when a file's structure changes) and poor controlled data sharing.
- A Database Management System (DBMS) is software used to create and manage databases; a database is a collection of related tables kept as a single repository at a centralised location, usable by multiple users at the same time.
- The database schema is the design or skeleton of a database: the table names, their columns, the type of data each column holds, the constraints on that data and the relationships among the tables. It is also called the visual or logical architecture of the database.
- A constraint is a restriction on the data that can be inserted into a column — for example NOT NULL and UNIQUE on a roll-number column. Constraints ensure the accuracy and reliability of stored data.
- The schema together with its constraints is stored by the DBMS in the database catalog (data dictionary) as meta-data — data about the data.
- A database instance is the state or snapshot of the database at a given moment. The schema stays fixed while the data changes, so one schema can have many instances over time.
- A query is a request to the database for information, framed in a query language (SQL). Data manipulation consists of three operations: insertion, deletion and update. The database engine is the underlying component of a DBMS that creates the database and handles queries for retrieval and manipulation.
- A DBMS has costs too: increased complexity (maintaining security, consistency, sharing and integrity) and increased data vulnerability, since centrally stored data means a hardware or software failure can halt operations for every user. Shifting from a file system also incurs high costs for hardware, software, user training and regular backup and recovery.
- In the relational data model, data is stored in relations (tables). A column is an attribute (field); a row is a tuple (record); the domain of an attribute is the set of values it may take, usually given by a data type.
- The degree of a relation is its number of attributes; the cardinality is its number of tuples. A relation is a flat file: every cell holds a single value and every record has the same number of columns.
- A candidate key is any attribute (or minimal combination) that can uniquely identify the tuples of a relation. The candidate key chosen for identification is the primary key; a candidate key not chosen as primary is an alternate key. A primary key made of more than one attribute is a composite primary key. …