Databases. What is database. Database Terms and Concepts.
A Database is a structure that can store information about multiple types of things (or entities), the characteristics (or attributes) of those entities, and the relationships among the entities. It also contains datatypes for attributes and indexes.A Database Management Systems (DBMS) is a software product (a program or collection of programs) through which the actual manipulation of the underlying database is handled. The user can access the data by going directly to the DBMS or can access the data through another program written with C++, VB, etc. that goes to the DBMS. Note: Both ways must go through the DBMS! (The user should not access the data by going through the "back-door", i.e. going around the DBMS directly to the DB.)
Database Processing is the manipulation of the data inside the database by the DBMS.
A Database Application is a set of one or more programs that serves as an intermediary between the user and the DBMS. It produces forms, queries, reports; it sends and receives data to and from user; it transforms user actions into requests for DBMS.
An entity is a person, place, thing or event that we want to store information about. (It is like a noun.) In a relational database structure each entity has its own table, i.e. a structure of rows and columns.
An attribute is a characteristic of an entity that we need to store. An attribute is represented as a column in the table for the entity. It is also called a field. An attribute has a specific datatype. It might be indexed. (Don’t worry about this now; we’ll do more about datatypes and indexing later.)
A record is a collection of fields with data about one thing or entity, such as a student. One of the fields usually identifies the entity. (Such as a student number.)
A domain is a description of the legitimate values for an attribute. It is the set of all possible values for that attribute.
An entity class is the general description of an entity. It is the design of the table for that entity, showing all of the attributes of that entity. It describes all possible entities that could ever be stored. (The domain for the entity.)
An entity instance aka entity occurrence is a specific entity. The information (i.e. all of the attributes) on one specific entity is stored in a record. (It is a row in a table in the datasheet view.)
A tuple is a collection of attributes about a single entity instance. It is the design of a record. It usually doesn’t have a name but it is a specific collection of fields that describes one entity instance. The entity instance may be identified by one of the fields in that record, such as the Student ID field.
An entity set is a collection of entity occurrences. The collection of entity occurrences is stored in a table. (It is the datasheet view of a table.)
A relation is a table that meets specific criteria. If the database is a relational database, then the tables must meet these criteria and hence be relations. So a relational database is a collection of relations and relationships.
A relational database is a database that is built on a certain model. In this model, the tables must be relations and they are related to each other through established relationships.
Vocabulary Hints:
- table (in design view) = entity class = relation (for a relational database) = (data) file
- table (in datasheet view) = entity set
- attribute = column = field
- row (in a table in datasheet view) = entity occurrence (or entity instance) = record
- row (design) = tuple
There are three types of relationships:
- one-to-many (most common)
- one-to-one
- many-to-many
A Parent Table is the table on the one side of a one-to-many relationship.
A Child Table is the table on the many side of a one-to-many relationship.
A Primary Key is an attribute (or a collection of attributes) that uniquely identifies each row in the table. (Each entity instance in a table must be unique and so this is a way of insuring that.)
A Composite Key is a key that is made up of more than one attribute.
A Surrogate Key is a key that is not from naturally occurring values.
A Foreign Key is an attribute in a child table that matches the primary key values in the parent table.
Schema refers to the logical model of the database.
A Relationship Table (aka Junction Table or Bridge Table) is a table that exists only for the purpose of establishing a many-to-many relationship between two tables, which cannot be done directly. It may be "all key" or also have some fields that are attributes of the relationship.
A Base Table is a table that store data about the entities.
A Query/View is a question about the data that you want answered. For example, "Who are the students at City College that have GPA higher than 2.5 and live near the Airport?"
A Virtual Table is a table that is returned by a query or a view.
A Form is an onscreen way of viewing, entering or editing data in the database. It is usually not intended to be printed out.
A Report is a print out of the data in a helpful way to the user. Special formatting and summary calculations are usually important.
Database Design is the process of determining an appropriate database structure, i.e. deciding on the appropriate tables, attributes and relationships, to satisfy a given set of requirements. This is a two-part process: 1) Information-level design and 2) physical-level design. The set of requirements are split into user views, i.e. the requirements for a particular user.
Data Model – consists of two components: structure and operations.
The structure refers to the way the system structures data (or at least how the user thinks it is structured.)
The operations are the facilities given to the users of the DBMS to manipulate data within the database.
These operations can:
- create tables and queries (DDL)
- add data, sort and calculate the data (DML)
- retrieve the data (DQL)
- change and look at the data (DCL)
Comments: