Chapter 8 DATABASE CONCEPT
1. What is a database system ? What is its need?
SOLUTION. A database is a collection of inter related data and a database system is basically a computer based recordkeeping system. A typical file processing system suffers from some major limitations like data redundancy (duplication of data), data inconsistency, unsharable data, unstandardized data, insecure data, incorrect data etc. On the other hand, a database system overcomes all these limitations and ensures continuous efficiency. The
advantages provided by a database system are:
(i) Reduced data redundancy (ii) Controlled data inconsistency
(iii) Shared data (v) Secured data
(iv) Standardized data (i) Integrated data.
Therefore, to have the systems with increased performance and efficiency, the database systems are preferred.
2. Can you think of disadvantages of using a database Can system? What are these?
Solution :-
With the complex tasks to be performed by database systems, certain things may crop up that may be termed as disadvantage you
tages is of using database system. These are:
1. Security may be compromised without good controls.
2. Integrity may be compromised without good controls,
3. Extra hardware may be required.
4. Performance overhead may be significant.
5. System is likely to be complex.
3. List three significant differences between a file processing system and a DBMS.
SOLUTION :-
1. A DBMS reduces the amount of data redundancy/duplication whereas a file based system has many of the data duplicated as data is stored separately for each application.
2. A DBMS implements standardised formats as data is stored at a central location whereas a file based system has same data stored at multiple locations in different formats.
3. A database management system is designed to coordinate multiple users accessing the same data at the same time. A file processing system is usually designed to allow one or more programs to access different data files at the same time.
4.What is data inconsistency? How is it related data redundancy?
SOLUTION :- Data inconsistency refers to a situation where multiple copies of same data exist but they do not match either in respect of data or in respect of their format. Data redundancy occurs when the same piece of data exists in multiple places. Data redundancy can cause data inconsistency.
5.What is Data Isolation? Give an example.
SOLUTION. Data isolation refers to a situation where data of one file cannot be mapped to other related file in the absence of links or mappings or common formats. For example, to prepare a consolidated report about a bank's performance, data from all department files i would be needed. But in file based systems, there is no way to link or map data in different files (no data mapping supported).
6. What is database schema ?
SOLUTION :- The logical structure and design of a database is called the database schema. It is the blueprint or skeleton structure of a database.
7.What is database instance ?
Solution :-
The data stored in database at a particular moment of time (i.e., the state of the database at a particular instance of time) is called instance of database. Database schema defines the variable declarations in tables that belong to a particular database; the value of these variables at a moment of time is called the instance of that database. For example, in a table namely student in a database has 100 records today, so we can say that today the database-instance has 100 records.
8.What is metadata and data dictionary?
Solution :-
SOLUTION. Metadata is data about data. A data dictionary is a centralized repository of metadata. A data dictionary contains information such as what is in the database (tables, indexes etc.), its properties (table structures, data types, formats, constraints etc.), its users etc.
9. What is database engine ?
Solution :-
SOLUTION. A database engine is the underlying software component (a set of programs) that a DBMS uses to create, read, update and delete data from a database. The database engine has two major components: the storage engine and the query processor. The storage engine writes data to and retrieves data from stable media (e.g., disks). The query processor accepts, parses, and executes SQL commands.
10.Explain the property of a relation: Values stored must be atomic.
Solution :-
This property of a relation implies that columns and rows in a relational table cannot contain groups or arrays of values e.g., arow cannot contain multiple values for a phone number column. Likewise, a column in a table must have exactly one value for a row
11.Explain the property of a relation: A Column's Values must be from same domain.
Solution :-
This property of a relation implies that all values in a column come are of the same kind, i.e., from the same domain, e.g., a Monthly Salary column contains only specific monthly salaries. It never contains other information such as comments, status flags, or even weekly salary.
12.Define the terms: (i) Relation, (ii) Tuple, is (iii) attribute, (iv) degree, (v) cardinality.
SOLUTION :-
(i) Relation. A relation is a table of a relational database, which has unordered rows and columns and it contains non-duplicate rows and columns having atomic values.
(ii) Tuple. The rows in a relation are called tuples.
(iii) Attribute. The columns in a relation are called attributes.
(iv) Degree. It refers to the number of columns or attributes in a relation (table).
(v) Cardinality. It refers to the number of rows or tuples in a relation (table).
13.Define the terms: (i) Primary Key, (ii) Candidate Key, (iii) Alternate Key, (iv) Foreign Key, (v) Composite Key.
SOLUTION :-
(1) Primary Key. Primary Key is a column or group of columns in a table that uniquely identify every row in that table.
(ii) Candidate Key. It is a set of attributes that uniquely identifies tuples in a table. The Primary key should be selected from the
candidate keys.
(iii) Alternate key. It is a column or group of columns in a table that uniquely identifies every row in that table. All the keys from the candidate keys, which are not primary key are called an Alternate Key.
(iv) Foreign Key. Foreign keys are the columns of the table which refer to the primary key of another table. A Foreign key creates a relationship between two tables.
(v)Composite Key. When a primary key is made of two or more columns, then such a key is called composite primary key or simply the composite key.
14.What is the purpose of using MySQL ?
SOLUTION :-
MySQL is a free, open source: Relational Database Management System (RDBMS) that uses Structured Query Language: (SQL). In a MySQL database, information is: stored in Tables. A single MySQL database can contain many tables at once and store thousands of individual records.
15. What do you understand by MySQL server instance?
Solution :-
MYSQL server instance is created from background processes and applications. It stays in memory and listens for client requests coming in over the network and accesses database contents according to those requests and provides that to the clients.
16.What do you understand by MySQL client?
SOLUTION. MYSQL Clients are programs that connect to the MySQL server and issue queries in a pre-specified format. MySQL is compatible with the standards based SQL (Structured Query Language). The client program may contact the manually. server programmatically or manually.
17. MySQL provides some command line programs and GUI programs. Name some of these.
Solution :- Command line programms : mysqldump and mysqladmin
18. What is SQL? What are different categories of commands available in SQL?
SOLUTION. In order to access data within the Oracle database, all programs and users must use, Structured Query Language (SQL). SQL is the set of commands that is recognised by nearly all RDBMSS.
SQL commands can be divided into following. categories:
1. Data Definition Language (DDL) Commands.
2. Data Manipulation Language (DML) Commands.
3. Transaction ControlLanguage Commands
4. Session Control Commands.
5. System Control Commands.
19.Differentiate between DDL and DML commands.
SOLUTION:- The Data Definition Language (DDL) commands, as the name suggests, allow you to perform tasks related to data definition. That is, through these commands, you can perform tasks like, create, alter and drop schema objects, grant and revoke privileges etc.
The Data Manipulation Language (DML) commands, as the name suggests, are used to manipulate data. That is, DML commands query and manipulate data in existing schema objects.
