In: Computer Science
1. Discuss the elements of a database system. Like
2. Discuss the key components of a database management system architecture and how they collaborate.
3. Discuss why a database management system needs a good query optimizer.
1.
Database schema - It is the visual representation of a database, a blueprint of the database. It is the set of rules that govern a database(like the relationships between objects and information in a database) or all the objects belonging to a particular user. You can think of it as a box that contains the tables, views, stored procedures and the related data assets.
Schema objects - It is a collection of database objects that describes how a database is stored. Information on tables, indexes, views, clusters and functions is a part of schema objects. It is owned by the database user.
Indexes - Indexing is used to efficiently obtain records from database files based on some specific attributes. Such attributes on which indexing is done are the indexes. It improves speed of data retrieval but has a cost of additional space used for creating the index structure. It is similar to the kind of indexing present in books.
Tables - It is a datasheet which holds data. It has records and fields. It is the primary unit of physical storage of data in a database system.
Fields and columns - A column/field is the category of information in a table. It is an attribute for the subject on which data is stored in the table. Each column has a data type which tells what type of values can be written in that column.
Records and rows - A row/record is the collection of single data entry for all the columns of the table. A record is composed of various fields and it has all data for a particular subject of the database.
Visualization for Fields/Columns and Records/Rows - A column is a vertical structure in a table and it contains values of a specific attribute for each row of data.
Keys - It is either an attribute(column) or a set of attributes(columns) which help us identify a row in a table. They can be used to define a relation between two tables. They can help in uniquely obtaining rows in a table depending on a combination of one or more columns in that table.
Relationships - When one table has a foreign key that references the primary key of the other table, then it is called a relationship between the two tables. It allows linking of multiple tables in a database system and provide the option to create dependencies.
Data types - It determines the type of data that should be stored for a particular column of the table. Some common data types are Numeric, Alphanumeric, date/time. For example, if a column stores age of people, then the data type for that column should be numeric.
2. The major components of a database management system architecture are -
Query Processor - The query processor changes user queries into a series of low level instructions. It is used to interpret the query and convert it into a series of operations to be sent to the run time data manager for execution.
Database manager - It is the central software component of the DBMS. It is the interface between the programs and the queries. It handles all the database access. It accepts queries and examines the external and conceptual schemas to determine what conceptual records are required to satisfy the user’s request. It enforces constraints to maintain the consistency and integrity of the data, as well as its security. It also performs backing and recovery operations. It also optimizes the user queries to perform query execution in the most optimal manner.
Data manager - The data manager is responsible for the actual handling of data in the database. It provides recovery to the system which that system should be able to recover the data after some failure. It includes Recovery manager and Buffer manager. The buffer manager is responsible for the transfer of data between the main memory and secondary storage (such as disk or tape).
Collaboration of these components - The following steps are followed to execute user's queries:
3. Database structures are complex and for all complex queries, the required data for a query can be collected from the database by accessing it in many different ways, through different data-structures, and in different orders. Each way requires different processing time which can vary a lot. Processing times of the same query usually have a large variance, from a fraction of a second to hours, depending on the way selected. This makes it very important for the database management systems to have a very good query optimizer. The large possible variance in query execution times makes it very important to have a good query optimizer.