DATABASE CONCEPTS

1

NAVODAYA VIDYALAYA SAMITI, NOIDA

E-CONTENT PREPARATION FOR

CLASS XI

CHAPTER- 7

​

PREPARED BY:

ASHOK KUMAR SHARMA, PGT CS

JNV SHIMOGA, KARNATAKA

DATABASE

• A database is a collection of interrelated data.
• It can serve multiple applications.
• It is computer based recordkeeping system.
• It not only allows to store but also allows us modification of data as per requirements.

​

​

2

FILE SYSTEM

​

• A file can be understood as a container to store data in a computer. Files can be stored on the storage device of a computer system. Contents of a file can be texts, computer program code, comma separated values (CSV), etc. Likewise, pictures, audios/videos, web pages are also files.
• Files stored on a computer can be accessed directly and searched for desired data.
• But one has to write computer programs for efficient access of data from files.

3

DBMS

​

• DBMS refers to Database Management System
• It is a software that can be used for creating, storing, manipulating, maintaining and retrieving data from database by users or application programs.
• The DBMS serves as an interface between the database and end users or application programs.
• Some examples of open source and commercial DBMS include MySQL, Oracle, PostgreSQL, SQL Server, Microsoft Access, MongoDB.

4

NEED OF DBMS

File system becomes difficult to handle when number of files increases and volume of data also grows. Following are some of the advantages of database:

• Database reduces redundancy

Redundancy means same data are duplicated in different places (files). It removes redundancy as the data are stored at one place and all the application refers to the centrally maintained database.

• Database controls inconsistency

Data inconsistency occurs when same data maintained in different places do not match. By controlling redundancy, the inconsistency is also controlled.

• Database facilitates sharing of data

Data stored in the database can be shared among several users.

​

​

​

5

NEED OF DBMS

• 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 ensure the validity or correctness of data.

• Database enforces standard

Database is maintained in a standard format which helps data interchange or migration of data between two systems.

6

KEY CONCEPTS IN DBMS

• Database Schema
• Database Schema is the design of a database.
• Database schema is also called the visual or logical architecture as it tells us how the data are organised in a database.
• Data Constraint
• It is certain restrictions or limitations on data to ensure its validity and consistency.
• Meta-data or Data Dictionary
• The database schema along with various constraints on the data is stored by DBMS in a database catalogue or dictionary, called meta-data.
• A meta-data is data about the data.

7

KEY CONCEPTS IN DBMS

• Database Instance

The state or snapshot of the database at any given time is the database instance

• Database Query

A query is a request to a database for obtaining information in a desired way.

• Data Manipulation
• Modification of database
• It consists of three operations viz. Insertion, Deletion or Updating.
• Database Engine
• Database engine is the underlying component or set of programs used by a DBMS

8

RELATIONAL DATA MODEL

​

• In a relational database model, data is organized into tables (i.e. rows and columns).
• These tables are also known as relations.
• A row in a table represents relationship among a set of values.
• A column represents the field/attributes related to relation under which information will be stored.
• For example: If we want to store the details of students then Roll_No, Class, Section etc. will be the column/attributes and the collection of all the column data will become a Row/Attribute.

​

​

9

COMPONENTS COMPRISING A TABLE

​

• Data Item: smallest unit of named data. It represent one type of information and often referred to as a field or column information

​

• Record: collection of data items which represent a complete unit of information

​

• Table: collection of all Rows and Columns.

​

10

�commonly used terminologies in relational data model

The Relational Model was developed by E. F. Codd of the IBM in 1970 and is the most widely used Database Model.

​

• Relation / Table

A Relation/Table is a tabular structure arranged in rows and columns. It has the following properties:

• All items in a column are homogeneous i.e. they are of the same data type
• Each column is assigned a unique name and has an atomic(indivisible) value.

​

11

commonly used terminologies in relational data model

​

• No attribute can have many data values in one tuple .
• All rows of a relation are distinct i.e. no two identical rows are present in a relation.
• Ordering of rows or columns are immaterial.
• A special value “NULL” is used to represent values that are unknown or non-applicable to certain attributes.

​

12

commonly used terminologies in relational data model

• Domain

It is a set of values from which an attribute can take a value in each row. Usually, a data type is used to specify domain for an attribute.

• Tuple / Entity / Record

Each row of data in a relation (table) is called a tuple.

• Attribute / Field

The columns of a relation are the attributes which are also referred as fields.

• Degree

The number of attributes in a relation is called the Degree of the relation

• Cardinality

The number of tuples in a relation is called the Degree of the relation

​

​

13

DATABASE TABLE/RELATION

14

STUDENT

Rows / Tuples

Table / Relation

Data Item

Records

Table/Relation Name

Columns / Attributes

�Keys in a Relational Database

The tuples within a relation must be distinct. It means no two tuples in a table should have same value for all attributes. That is, there should be at least one attribute in which data are distinct (unique) and not NULL. That way, we can uniquely distinguish each tuple of a relation. So, relational data model imposes some restrictions or constraints on the values of the attributes and how the contents of one relation be referred through another relation. These restrictions are specified through different types of keys.

15

Keys in a Relational Database

• Primary Key
• A set of one or more attribute(s) that can uniquely identify a record in the relation is called Primary Key.
• There can be only one primary key in a table.
• A primary key accepts only distinct (non-duplicate) values and cannot be left blank.
• E.g. Student ID No, Student Admission No, Aadhar Card No etc. qualifies as Primary Key as the values of all these are unique.

​

16

Keys in a Relational Database

• Candidate Key
• A table can have one or more attributes that takes distinct values. Any of these attributes can be used to uniquely identify the tuples in the relation. Such attributes are called candidate keys.
• 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.

17

Keys in a Relational Database

• Candidate Key

​

STUDENT

​

​

​

​

• In the above given table Stud_ID, Roll_No, Email_ID can uniquely identify a row in the table and hence they are candidate keys.

18

Candidate Keys

Keys in a Relational Database

• Composite Primary Key
• If no single attribute in a relation is able to uniquely distinguish the tuples, then more than one attribute are taken together as primary key. Such primary key consisting of more than one attribute is called Composite Primary key.

​

​

​

​

​

• Here a composite Primary Key (CLASS,SECTION) can be formed together as either of these column in isolation cannot be used to uniquely identify each row in the table.

19

Keys in a Relational Database

• Alternate Keys
• Only one of the Candidate keys is selected as the primary key of a table. All other candidate keys are called Alternate keys.
• A Candidate Key which is not a primary key is an Alternate Key.
• Candidate Keys – Primary Key = Alternate Key(s).
• E.g. In the table STUDENT mentioned in Page No. 18, if Stud_ID is selected as the Primary Key then Roll_No and Email_ID will become the Alternate Keys.

​

​

​

20

Keys in a Relational Database

• Foreign Key
• A foreign key is used to represent the relationship between two tables.
• It is a non-key attribute whose value is derived from the Primary key of another table.
• Foreign key column will check for the presence of value in Primary key of another table, if present then only entry will be allowed otherwise data will be rejected.

21

Keys in a Relational Database

• In some cases, foreign key can take NULL value if it is not the part of primary key of the foreign table.
• The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

22

Keys in a Relational Database

ORDERS

​

​

​

​

CUSTOMER

​

​

​

​

• From the above given tables we can observe that the CUST_ID column of ORDERS table is deriving its value from CUST_ID of CUSTOMER table. So we can say that the CUST_ID of ORDERS table is a foreign key whose value is dependent upon the Primary key column CUST_ID of table CUSTOMER.

23

Child Table

Parent Table

REFERENTIAL INTEGRITY

• The property of a relational database which ensures that no entry in a foreign key column of a table can be made unless it matches a primary key value in the corresponding column of the related table is called referential integrity.
• It also ensures that the user don’t accidentally delete or change the related data.
• Referential integrity can be applied when:
• The master table’s column is a Primary Key or has a unique index.
• The related fields have the same data type.
• Both tables must belong to the same database.

​

24

REFERENTIAL INTEGRITY

• The following rules must be followed if the referential integrity is enforced using Foreign Key:

​

• We cannot enter a value in Child Table which is not present in Master Table’s Primary key column. However, NULL values can be entered in foreign key.
• We cannot delete a record from Master Table if matching record exists in related table/Child Table.
• We cannot modify or change the Primary Key value in Master table if its matching record is present in related table/Child Table.

25

​

​

​

​

​

THANK YOU

26