A database can be defined as a set of related information that is organized in a way such that a computer analyst can promptly access a required piece of information. Commonly used databases are organized by records, fields, and files. A database contains data that can be graphical, textual, or numerical. The data can be searched, retrieved or manipulated, as per requirements. We can broadly classify all of the database operations into four categories, which are:
- Inserting data or Create
- Retrieving existing data or Read
- Modifying existing data or Update
- Deleting existing data or Delete
The above given related operations are collectively known as CRUD operations.
A database schema gives the logical description of a database and is drawn based on the types of data which is used. It is made up of related objects having relationships with each other. A database scheme is not the representation of a person rather it is a framework where the values of the fields exist and can be changed from time to time. We can associate 3 models or views with a database schema and they are:
- Conceptual – Also known as the logical model and it gives us a user’s view describing what data has actually been stored. It is a basic database model dealing with organizational structures.
- Internal – Also known as the physical model it addresses the physical storing of data and accessing it by using indexes.
- External – Also known as the application interface model, it addresses how the data will look and the methods used by users to access the schema.
A database consists of one or more tables comprising of information that is organized for a specific function. It can contain queries, forms, reports, etc. A tuple is a row in a table that stores data about a single topic and is sometimes also referred to as a record. An attribute is a field or column of a table and contains a value for describing the row it is enclosed in. Thus, a table is also known as a relation or an entity about which we want to store information or data and it has a conceptual or physical existence. While a column consists of a characteristic or an attribute of the entity, a row represents the columns that are associated with an item of the table. An entity set consists of a collection of entities having the same type and they share the same attributes or properties. A domain specifies a group of values that are used by the specific attributes of particular relation to form their actual value.
The information that is stored in a database should maintain certain integrity which is controlled by a number of keys. A database can have the following types of keys:
- Super key – It is a collection of one or more attributes that when used, collectively, uniquely identifies an entity from an entity set.
- Candidate key – When a table has multiple possibilities for a primary key, then each of those keys is candidate keys. Also, any subset of a super key automatically becomes a super key. But this should not be the case.
- Primary key – It is a field containing unique values that can be used for identifying each record of a table. It is the primary means of locating the entities from an entity set and thus, contains those attributes which are rarely or never changed. Sometimes, primary keys denote a candidate key.
- Composite key – Composite keys are also a type of primary key and can be formed by combining two or more columns that can uniquely identify a row of a table. If a single field does not satisfy the qualifications for a primary key, then we have a multifield composite key. It is also known as a concatenated or compound key and it too does not allow duplicate values.
- The data type of a foreign key should match that of the related primary key field. However, unlike a primary key, foreign keys can have duplicate values.
Since databases consist of a number of tables, most of them are related to each other and their relationships can be established by using primary keys and foreign keys. Dividing a large amount of data into smaller tables and creating a relation between them eliminates data redundancy and inconsistency. Four types of table relationships are possible in a database. They are:
- One to one – Here each record of a table is associated only with one record of another table and vice versa.
- Many to many – Here each record of a table can be associated with a number of records of the second table and vice versa.