Database Terminology

Relationship

A link between one table and another table based on common data fields.

Entity

A person, place, object, event, or concept in the user environment about which the organization wishes to maintain data.

Primary Key?

A primary key is unique. A key value can not occur twice in one table. With a key, you can find at most one row. The field selected as a unique identifier for the database.

ERD

(Entity Relationship Diagram) is a snapshot of data structures. ERDs show entities in a database and relationships between tables within that database. It is essential to have one of these if you want to create a good database design. The patterns help focus on how the database actually works with all of the interactions and data flows

What is a Database?

1. A database is a collection of tables, with related data. 2. A collection of data organized in a manner that allows access, retrieval, and use of that data.

In DBs what is a Table?

1. A table is a matrix with data. A table in a database looks like a simple spreadsheet. 2. A collection of records that share the same fields.

Compound Key?

A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique. Eg if there are two key fields, they are known as candidate keys. Often one will be the primary key and the other will be the secondary key.

What is a database view?

A database view is a virtual table or logical table which is defined as a SQL SELECT query with joins. Because a database view is similar to a database table, which consists of rows and columns, so you can query data against it. Most database management systems, including MySQL, allows you to update data in the underlying tables through the database view with some prerequisites. A database view is dynamic because it is not related to the physical schema. The database system stores database views as a SQL SELECT statement with joins. When the data of the tables changes, the view reflects that changes as well.

Foreign Key?

A foreign key is the linking pin between two tables. A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables

What does Relational Database Management System (RDBMS) mean?

A relational database management system (RDBMS) is a database engine/system based on the relational model specified by Edgar F. Codd--the father of modern relational database design--in 1970. Most modern commercial and open-source database applications are relational in nature. The most important relational database features include an ability to use tables for data storage while maintaining and enforcing certain data relationships.

Relationship sets?

A relationship is an association between several entities. A relationship set is a set of relationships of the same type.

In DBs what is a Row?

A row (= tuple, entry or record) is a group of related data, for example the data of one subscription.

what is an attribute?

An attribute refers to a database component, such a table. It also may refer to a database field. Attributes describe the instances in the row of a database. Think of a table in a relational database as being analogous to a spreadsheet. An attribute is simply one non-null cell in the spreadsheet, or the conjunction of a column and row. It stores only one piece of data about the object represented by the table in which the attribute belongs. For example, the attributes in an invoice might be price, number, date or paid/unpaid, etc.

what is an Entity?

An entity is an object that exists and is distinguishable from other objects. For instance, John Harris with S.I.N. 890-12-3456 is an entity, as he can be uniquely identified as one particular person in the universe. An entity may be concrete (a person or a book, for example) or abstract (like a holiday or a concept).

what is an Entity Set?

An entity set is a set of entities of the same type (e.g., all persons having an account at a bank).

Index

An index in a database resembles an index at the back of a book. A predefined sort on a particular field. Having a field indexed means that database searches and sorts based on that field will be faster.

What does Boyce-Codd Normal Form (BCNF) mean?

Boyce-Codd Normal Form (BCNF) is one of the forms of database normalization. A database table is in BCNF if and only if there are no non-trivial functional dependencies of attributes on anything other than a superset of a candidate key. BCNF is also sometimes referred to as 3.5NF, or 3.5 Normal Form.

What is cardinality?

Cardinality expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity. In the ERD (Entity Relationship diagram), cardinality is indicated by placing the appropriate numbers beside the entities, using the format (x,y). The first value represents the minimum number of associated entities, while the second value represents the maximum number of associated entities

What is data administration?

Data administration is the process by which data is monitored, maintained and managed by a data administrator and/or an organization. Data administration allows an organization to control its data assets, as well as their processing and interactions with different applications and business processes. Data administration ensures that the entire lifecycle of data use and processing is on par with the enterprise's objective. Data administration typically involves the logical management of data in which the flow of data is analyzed, data models are created and the relationships among them are defined. Data administration also defines the security and access control elements of data where executive level data might be limited to some people and processes.

Difference between DA and DBA?

Data management differs from database administration in that the former defines the processes used to manage and maintain data as an organizational asset, whereas the latter deals with the technicalities involved with managing and distributing data.

Redundancy

Data redundancy is a condition created within a database or data storage technology in which the same piece of data is held in two separate places. This can mean two different fields within a single database, or two different spots in multiple software environments or platforms. Whenever data is repeated, this basically constitutes data redundancy. This can occur by accident, but is also done deliberately for backup and recovery purposes.

What is database administration?

Database administration refers to the whole set of activities performed by a database administrator to ensure that a database is always available as needed. Other closely related tasks and roles are database security, database monitoring and troubleshooting, and planning for future growth. Database administration is an important function in any organization that is dependent on one or more databases. The database administrator (DBA) is usually a dedicated role in the IT department for large organizations. However, many smaller companies that cannot afford a full-time DBA usually outsource or contract the role to a specialized vendor, or merge the role with another in the ICT department so that both are performed by one person.

What does First Normal Form (1NF) mean?

First normal form (1NF) sets the fundamental rules for database normalization and relates to a single table within a relational database system. Normalization follows three basic steps, each building on the last. The first of these is the first normal form. The first normal form states that: - Every column in the table must be unique - Separate tables must be created for each set of related data - Each table must be identified with a unique column or concatenated columns called the primary key - No rows may be duplicated - No columns may be duplicated - No row/column intersections contain a null value - No row/column intersections contain multivalued fields

Functional Dependency?

Functional dependency is a relationship that exists when one attribute uniquely determines another attribute. If R is a relation with attributes X and Y, a functional dependency between the attributes is represented as X->Y, which specifies Y is functionally dependent on X. Here X is termed as a determinant set and Y as a dependant attribute. Each value of X is associated precisely with one Y value. Functional dependency in a database serves as a constraint between two sets of attributes. Defining functional dependency is an important part of relational database design and contributes to aspect normalization.

Trigger

MySQL triggers are stored programs that are executed automatically to respond to specific events associated with table e.g., insert, update or delete a record. An action on one data table that causes other actions to happen on other tables

What does Normalization mean?

Normalization is the process of reorganizing data in a database so that it meets two basic requirements: (1) There is no redundancy of data (all data is stored in only one place), and (2) data dependencies are logical (all related data items are stored together). Normalization is important for many reasons, but chiefly because it allows databases to take up as little disk space as possible, resulting in increased performance. The three main types of normalization are listed below. 1NF 2NF 3NF The following three NFs exist but are rarely used: BCNF 4NF 5NF

In DBs what is a Column?

One column (data element) contains data of one and the same kind, for example the column postcode.

What is Referential integrity?

Referential integrity is a relational database concept, which states that table relationships must always be consistent. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key. Thus, any primary key field changes must be applied to all foreign keys, or not at all. The same restriction also applies to foreign keys in that any updates (but not necessarily deletions) must be propagated to the primary parent key. Referential Integrity makes sure that a foreign key value always points to an existing row. A property of a relationship between two tables. When Referential Integrity is on, the child table cannot contain a foreign key value that does not have a corresponding value in the primary key of the parent table.

Relation

Relation is sometimes used to refer to a table in a relational database but is more commonly used to describe the relationships that can be created between those tables in a relational database. In relational databases, a relationship exists between two tables when one of them has a foreign key that references the primary key of the other table. This single fact allows relational databases to split and store data in different tables, yet still link the disparate data items together. It is one of the features that makes relational databases such powerful and efficient stores of information.

What does Second Normal Form (2NF) mean?

Second normal form (2NF) is the second step in normalizing a database. 2NF builds on the first normal form (1NF). Normalization is the process of organizing data in a database so that it meets two basic requirements: - There is no redundancy of data (all data is stored in only one place). - Data dependencies are logical (all related data items are stored together). A 1NF table is in 2NF form if and only if all of its non-prime attributes are functionally dependent on the whole of every candidate key.

What is SQL?

Structured Query Language (SQL) is a standard computer language for relational database management and data manipulation. SQL is used to query, insert, update and modify data. Most relational databases support SQL, which is an added benefit for database administrators (DBAs), as they are often required to support databases across several different platforms. First developed in the early 1970s at IBM by Raymond Boyce and Donald Chamberlin, SQL was commercially released by Relational Software Inc. (now known as Oracle Corporation) in 1979. The current standard SQL version is voluntary, vendor-compliant and monitored by the American National Standards Institute (ANSI). Most major vendors also have proprietary versions that are incorporated and built on ANSI SQL, e.g., SQL*Plus (Oracle), and Transact-SQL (T-SQL) (Microsoft).

DBA's roles are?

The primary role of database administration is to ensure maximum up time for the database so that it is always available when needed. This will typically involve proactive periodic monitoring and troubleshooting. This in turn entails some technical skills on the part of the DBA. In addition to in-depth knowledge of the database in question, the DBA will also need knowledge and perhaps training in the platform (database engine and operating system) on which the database runs. A DBA is typically also responsible for other secondary, but still critically important, tasks and roles. Some of these include: - Database Security: Ensuring that only authorized users have access to the database and fortifying it against any external, unauthorized access. - Database Tuning: Tweaking any of several parameters to optimize performance, such as server memory allocation, file fragmentation and disk usage. - Backup and Recovery: It is a DBA's role to ensure that the database has adequate backup and recovery procedures in place to recover from any accidental or deliberate loss of data. - Producing Reports from Queries: DBAs are frequently called upon to generate reports by writing queries, which are then run against the database.

What does Third Normal Form (3NF) mean?

Third normal form (3NF) is the third step in normalizing a database and it builds on the first and second normal forms, 1NF and 2NF. 3NF states that all column reference in referenced data that are not dependent on the primary key should be removed. Another way of putting this is that only foreign key columns should be used to reference another table, and no other columns from the parent table should exist in the referenced table.