What is the Data?Data is raw facts and figures which does not convey any meaning or idea because it is not organized.
What is the Database?A database is a well organized collection of inter-related that ensures safety, security and integrity of data. It works like a container which contains the various object like Tables, Queries, Reports etc. in organized way. Most of the databases stores data in the form of Relations (also called Tables). Such Database are known as Relational Database.
What is the Database Management System (DBMS)?A DBMS refers to a software that is responsible for storing, maintaining and utilizing database in an efficient way. A Database along with DBMS software is called Database System. Example of DBMS software are Oracle, MS SQL Server, MS Access, Paradox, DB2 and MySQL etc. MySQL is open source and freeware DBMS.
Why Database System is used? (Advantages)Databases reduces Redundancy It removes duplication of data because data are kept at one place and all the application refers to the centrally maintained database. Database controls Inconsistency When two copies of the same data do not agree to each other, then it is called Inconsistency. By controlling redundancy, the inconsistency is also controlled. Database facilitate Sharing of Data Data stored in the database can be shared among several users. Database ensures Security Data are protected against accidental or intentional disclosure to unauthorized person or unauthorized modification. Database maintains Integrity It enforces certain integrity rules to insure the validity or correctness of data. For ex. A date can’t be like 25/25/2000.
Types of Databases models
Data model is a model or presentation which shows How data is organized ? or stored in the database. A data is modeled by one of the following given-
Relational Data Model
In this model data is organized into Relations or Tables (i.e. Rows and Columns). A row in a table represents a relationship of data to each other and also called a Tuple or Record. A column is called Attribute or Field.
Network Data Model
In this model, data is represented by collection of records and relationship among data is shown by Links.
Hierarchical Data Model
In this model, Records are organized as Trees. Records at top level is called Root record and this may contains multiple directly linked children records.
Object Oriented Data Model
In this model, records are represented as a objects. The collection of similar types of object is called class.
Basics of the Relational Data Model
Relational Model was developed by E.F.Codd of the IBM and used widely in the
most of the DBMS. The following key-terms are used in the Relational Model-Relational Databases stores data or information in tables. A table or Relation
is a two dimensional representation of data using rows and columns.
A Relation or Table is Matrix like structure arranged in Rows and Columns. It has the following properties-
Atomicity : Each column assigned a unique name and must have atomic (indivisible) value i.e. a value that can not be further subdivided.
No duplicity: No two rows of relation will be identical i.e. in any two rows value in at least one column must be different.
All items in a column are homogeneous i.e. same data type.
Ordering of rows and column is immaterial.
It is collection of values from which the value is derived for a column. A domain is a collection of all possible values from which the values for a given column or an attribute is drawn. A domain is said to be atomic if elements are considered to be indivisible units.
Tuple / Entity / Record :
Rows of a table is called Tuple or Record.
Column of a table is called Attribute or Field.
Number of columns (attributes) in a table.
Number of rows (Records) in a table.
Concept of Keys
As discussed earlier, In a Relation each record must be unique i.e. no two identical records are allowed in the Database.
A key attribute identifies the record and must have unique value.
A set of one or more attribute (column) that can identify a record uniquely in the relation is called Primary Key.
A primary key is a column in a table that is unique for each record. Every table in a database should have a column or a group of columns designated as the primary key. The value this key holds should be unique for each record in the table.
Some common examples of Primary key are:
- Admission Number in student table of School,
- Accession Number of a Book in the Book table,
- Employee ID of an employee in the Employee Table,
- Item Code of an item in the Stock table etc.
In a table, those columns who can become primary key are called Candidate Key.
Some time a table may have more than one column that uniquely identifies a record in a table. All such columns are called Candidate Keys because they all are having candidature to work as key.
A Candidate key also uniquely identifies a record. A Primary Key is one of the candidate keys. A table may have more than one candidate keys but definitely has one and only one primary key.
All candidate keys that not chosen as primary key are called alternate keys. A Candidate Key that is not a Primary key is called Alternate key.
A non-key attribute whose values are derived from the primary key of some other table is called Foreign key.
Foreign Key is used to implement Referential Integrity in the Database. It establishes relationship among two tables.
It concerns the concept of a foreign key. The referential integrity rules states that any foreign key value can only be in one of two states. The usual state of affairs is that the foreign key value refers
to a primary key value of some table in the database
Consider the following Student Table. The RollNo and Admission_no both may be used to uniquely identify each record in the Table, so both are candidate keys.
So, Candidate Keys are – RollNo and Admission_no Pramary Key – Admission_No and Alternate Key – RollNo or vice-versa.
CSV files Multiple Choice Questions1. DBMS stands for (a) Database Microsoft System (b) Database Migration System (c) Database Management System (d) None of the above
Answer:Ans. (c) DBMS is the short form of Database ManagementSystem. It refers to a category of softwares that store and manage bulk volumes of data.
Answer:Ans. (a) A table can have only a single primary key to identify the records.
Answer:Ans. (a) MySQL , Oracle and Microsoft SQL Server are all DBMS but MS-Word is a documentation software.
Answer:Ans. (b) The term degree refers to the total number of columns in a table.
Answer:Ans. (d) A foreign key is a common field found in two tables andit links the two tables.
Answer:Ans. (d) The total number of rows in a table is called cardinality.
Answer:Ans. (d) Degree is the total number of attributes/columns in a table , so if a column is deleted the degree decreases.
Answer:Ans. (c) Two columns cannot be primary key. But a combination of the columns can be primary key.
Answer:Ans. (c) A Relational Database Management System (RDBMS) is a software that can be used to maintain , manipulate and create large volumes of data in relations/tables and relationships between them.
Answer:Ans. (d) The tables stores the individual domains of data of a database system and thus stores the data and organises it.
Answer:Ans. (c) The complex structure of tables , the relationships between them , other database objects and their management is what makes database systems complex and specialised software and people to manage. Database systems are complex, difficult and time-consuming to design.
Answer:Ans. (a) The permanent or secondary storage device is the hard disk or any kind of disk storage that resides in the hardware unit.
Answer:Ans. (d) A primary key carries unique values and hence is used to identify the records uniquely.
Case Based MCQs on CSV file
Anita has created a table “Players” to store the details of players who play in her sports academy .
Table : Players
Answer the following questions, which based on the given information.
(i) Which column can she make the primary key?
(a) PlayerId (b) PlayerName (c) Game (d) Type
(ii) Which column(s) can act as candidate key?
(a) Only PlayerId (b) Only AadharNo (c) Both (a) and (b) (d) Type
(iii) Which column is the foreign key in the table?
(a) PlayerName (b) Type (c) Game (d) None of these
(iv) What is the degree of the table?
(a) 1 (b) 2 (c) 3 (d) 5
(v) What will be the cardinality of the table, if two columns are added to the table?
(a) 7 (b) 6 (c) 4 (d) None of these
Mr. Sharma is a new user of database systems . He has created a table storing the details of staff in his office. He is confused about some of the terms related to tables and databases. Help him solving his confusions.
Table : Staff
(i) The vertical set storing the departments under the heading “Dept” is called
(a) field (b) attribute (c) column (d) All of these
(ii) What is the cardinality of the table?
(a) 2 (b) 3 (c) 4 (d) 1
(iii) Can “StaffName” column serve as primary key?
(a) No (b) Yes, only if it stores non-blank and distinct names
(c) Yes, only if it stores only distinct names (d) Yes
(iv) A tuple carries
(a) a single value (b) double values
(c) a row of multiple values as a record (d) None of the above
(v) An attribute which can uniquely identify tuples of the table but is not defined as primary key of the
table is called
(a) primary key (b) alternate key (c) forign key (d) None of these
Short Answer Type Questions on database basics
- What do you understand by the term database?
Ans. A database is a huge collection of data accumulating in a particular system. It comprises of historical data, operational and transactional data. The database grows everyday with the transactions dealing with it. A database has the following properties (i) It is a collection of data elements representing real-world information. (ii) It is logical, coherent and internally consistent.
- What is a DBMS? Expand and explain in short.
Ans. A Database Management System is a software system that enables users to define, create and maintain the database and provides controlled access to this database. The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient. Data in a database can be added, deleted, changed, sorted or searched, all using a DBMS.
- What is a table? Also, write the other name of table.
Ans. Table is also called a relation, it is a diagrammatically a matrix of rows and columns that store the data of a particular system. A table is just like a sheet in Excel, that stores data in some columns and rows.The data is arranged under some fields, where each field stores similar kind of data.
- What do you mean by fields of a table? Give examples.
Ans. A field of a table is simply a vertical column of the table. A field is also called an attribute. It stores similar kind of data. e.g. Name,Class ,Marks etc., can be fields of student table, EmpId, Empname,Dept can be fields of Employee table. Each field derives its values from a pool of data which is called as the domain. All the values in a single field will be of same data type.
- What are records? Also, write the other name of record.
Ans. A record is a horizontal row of a table storing complete data of one entity. It is also called a tuple. e.g. 2 Mr. Das Sales 45000 The above record of Mr. Das carries all the information about him. Similarly other records of the table carry data about other employees. All the records together make up the data of the table.
- What do you understand by the term degree of a table? Can it change?
Ans. The term degree refers to the total number of columns in a table. Yes the degree changes with addition or deletion of columns. e.g. If a table “Product” stores the data in columns “PNo, PName,Qty,Price’’, there are 4 columns , hence the degree will be 4.
- What do you understand by the term cardinality of a table? How can it be modified?
Ans. The total number of rows of a table is called the cardinality. It gets modified by the addition or deletion of rows. If rows are added to the table the cardinality increases . If rows are deleted the cardinality decreases.
- What is a primary key? How many primary keys can be there in a table?
Ans. It is a combination of one or more fields in a table that can uniquely identify a record. There can be only one primary key in a table. It plays an important role in identifying the records, because it is the primary key who carries unique values. The criteria for a field to become primary key is : It must be carrying unique and NOT NULL values.
- What is candidate key?
Ans. All the field combinations that can serve as primary key for unique identification of records in a table are called candidate keys. For example, If a student table carries “RollNo., Name, Class, AadharNo., AdmissionNo” columns, then columns RollNo., AadharNo. and AdmissionNo can become the candidate keys since all carry unique values.
- Can we have multiple candidate keys in a table? Give example.
Ans. Yes, we can have multiple candidate primary keys. e.g. In an Employee table, ENo and AadharNo both can serve as primary key, hence both are candidate keys. Only primary key in a table will be a single field , candidate keys can be multiple.
- Which fields are regarded as alternate keys?
Ans. All the candidate key fields that are not primary key are alternate keys. e.g. If a table Employee carries columns “ENo,EName,PFNo,VoterId”, then “ENo” is set as primary key and the other candidate keys “PFNo” and “VoterId” will be the alternate keys.
- Why foreign keys are allowed to have NULL values? (NCERT)
Ans. A foreign key is a field that links two tables . A table may have links to multiple tables . Each link is supported by a value that is common in the two tables . If there is a missing foreign key value for a record , it means the link is missing and no matching values are present. This is perfectly a valid situation, not an error .
- How many foreign keys can be there in a table?
Ans. A table can have multiple foreign keys depending on the number of tables to which the mother table has links.
- Write names of few softwares used as DBMS.
Ans. MySQL, Oracle , DB/2, Ingres softwares obey certain common rules of relational algebra. Like they all support most of the codd’s rules and support SQL . Some of these softwares like MySQL are free and some like Oracle is proprietary, that it has to be bought.
- What do you understand by the term domain?
Ans. Domain refers to the pool or set of values from which a field of a table derives its values. e.g. The RollNo field derives its values from the set of integers from 1-100 (approx.). The “Dept” field derives its values from the domain of possible departments and the “Marks” field derives its values from the range of marks in an examination.
- Give suitable example of a table with sample data and illustrate primary and candidate keys in it.
Ans. Candidate Key It is a set of all attributes that uniquely identifies records in a table. Each table may have one or more candidate keys.
- List some commonly used DBMS software packages.
Ans. Some commonly used DBMS software packages are (i) MySQL (ii) Oracle (iii) Postgre (iv) DB2 (v) MS-SQL (vi) Sybase
- Differentiate between an attribute and a tuple with an example.
Ans. The columns of a table are referred to as attributes. It is also known as field which is reserved for a specific piece of data. The rows of a table are referred to as tuples.
- What is the difference between degree and cardinality of a table? What is the degree and cardinality of the following table?
Ans. Degree The number of attributes or columns in a table is called the degree of the table. The degree of the given table is 3. Cardinality The number of rows or records in a table is called the cardinality of the table. The cardinality of the given table is 2.
- Mention atleast three limitations of DBMS.
Ans. Some limitations of DBMS are given below
- High Cost DBMS requires various software, hardware and highly intelligent people for operating and maintaining the database system. It increases its cost.
- Database Failure If database is corrupted due to power failure or any other reason, our valuable data may be lost or whole system stops.
- Data Quality With increased number of users accessing data directly. There are enormous opportunities for users to damage data. So, it is not easy to provide a strategy to support multiple users to update data simultaneously
Long Answer Type Questions on Database concepts
A table “Sports” exists with 3 columns and 5 rows. What is its degree and cardinality? 2 rows are added to the table and 1 column deleted. What will be the degree and cardinality now?
Ans. The term degree refers to the total number of columns in a table. The term cardinality refers to the total number of rows in a table.
Initially, Sports table has 3 columns and 5 rows, so
Degree : 3
Cardinality : 5
After operations, 2 rows are added to the table and 1 column
Now, degree : 2 cardinality : 7.
Differentiate the terms primary key and candidate key.
Ans. Differences between primary key and candidate key are
A primary key is a single field in a table that is used to identify the records uniquely.
Only one field among the candidate keys is selected as primary key.
Primary key of a table is used in linking the data of the table to another table.
A candidate key is a set of columns who are eligible for unique identification of records.
A table can have multiple candidate keys.
Candidate keys do not have such role.
Explain by an example how foreign key is useful for bringing data from multiple tables?
Ans. Consider the two tables given below
Table : Student
Table : Address
Referring to the above tables , if we look for Place to which “Priya” belongs” , we can link the tables by the foreign key “AddressID” of the Student table to get place as “Barnala” by the AddressID “A3”. So, a foreign key helps in bringing data from multiple tables