CLASS XII CS
Created by Archana Jain
DATABASE CONCEPTS
UNIT 4
Created by Archana Jain
What is data?
• Attendance Record - record attendance for future reference
• Search histories - keep a track of customer preferences
• Order details – to keep a record
• Marks of a student -- performance
• Covid related data -- Analysis and take decisions
• Bank maintain data about customers - record /
It a information stored for future references ,it is basically to describe ,an entity , it is
combination of characters, digits , special characters.
Created by Archana Jain
Use of data
• Taking decisions
• Machine learning
• Defining strategies
• Record Maintenance
Created by Archana Jain
Database Non database applications
• Bank Calculator
• Reservations system
• School management system
. Inventory management
Sales monitoring system
Shares management
LMS
Created by Archana Jain
Limitations of manual databases
• Lot of efforts ,human resources , space to keep those files , time to enter data
• Sharing of data was a tedious task.
• Difficult to update data.
• Difficulty in accessing
• Possibility of human errors
• Data redundancy
• Data isolation
• Data inconsistency
Created by Archana Jain
Uses of electronic databases
• Saving space , store large amount of data
• Conserving resources
• Easy editing /input of data/ Calculation of data is also very easy.
• Data sharing
• Data accessibility
• Reduction in data redundancy , data inconsistency
• Less errors
• Improved Security
• Backup services / recovery services
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Database management system :
A Database Management System allows a person to organize, store, and retrieve data from a
computer. It is a way of communicating with a computer’s “stored memory.
In 1960, Charles W. Bachman designed the Integrated Database System, the “first” DBMS. IBM,
not wanting to be left out, created a database system of their own, known as IMS. Both database
systems are described as the forerunners of navigational databases.
Some examples of open source and commercial DBMS include MySQL, Oracle, PostgreSQL, SQL
Server, Microsoft Access, MongoDB.
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Types of database models
There are many kinds of data models. Some of the most common ones
include:
• Hierarchical database model
• Network model
• Relational model
• Object-oriented database model
• NoSQL model
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Relational model of DBMS
The term "relational database" was invented by E. F. Codd at IBM in 1970. RDBMS products
typically implement some but not all of Codd's 12 rules. Relational Model (RM) represents the
database as a collection of relations.
RDBMS
• Present the data to the user as relations (a presentation in tabular form, i.e. as
a collection of tables with each table consisting of a set of rows and columns);
• Provide relational operators to manipulate the data in tabular form.
• Some popular Relational Database management systems are:
• DB2 and Informix Dynamic Server - IBM
• Oracle and RDB – Oracle
• SQL Server and Access - Microsoft
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
RDBMS
• This model organizes data into one or more tables (or "relations") of columns and rows, with a
unique key identifying each row.
• Rows are also called records or tuples.
• Columns are also called attributes.
• Generally, each table/relation represents one "entity type" (such as customer or product).
• The rows represent instances of that type of entity (such as “Anika" or "chair") and the columns
representing values attributed to that instance (such as address or price).
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Relation / Table
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
RELATIONAL DATABASE MODEL CONCEPTS
• Attribute: Each column in a Table. Attributes are the properties which define a relation. e.g.,
Student_Rollno, NAME,etc.
• Tables – In the Relational model the, relations are saved in the table format. It is stored along
with its entities. A table has two properties rows and columns. Rows represent records and
columns represent attributes.
• Tuple – It is nothing but a single row of a table, which contains a single record.
• Relation Schema: A relation schema represents the name of the relation with its attributes.
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
RELATIONAL DATABASE MODEL CONCEPTS
• Degree: The total number of attributes which in the relation is called the degree of the relation.
• Cardinality: Total number of rows present in the Table.
• Column: The column represents the set of values for a specific attribute.
• Relation instance – Relation instance is a finite set of tuples in the RDBMS system. Relation
instances never have duplicate tuples.
• Relation key - Every row has one, two or multiple attributes, which is called relation key.
• Attribute domain – Every attribute has some pre-defined value and scope which is known as
attribute domain
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Type of keys in a Relation
• Primary Key : A column or set of columns that uniquely
identifies a row within a table is called primary key. Every
table can have only one primary key.
• Primary key can never be NULL
• Primary Key can never be duplicated .
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Type of keys in a Relation
• 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.
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Type of keys in a Relation
• Candidate Key : Candidate keys are set of fields (columns
with unique values) in the relation that are eligible to act as
a primary key.
• Relation can have multiple candidate keys.
• Candidate Keys can never be duplicated .
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Type of keys in a Relation
• Alternate Keys: A candidate key that is not the primary key
is called alternate key.
• Foreign Keys: It is a field whose values are derived from
primary key of another table. Foreign key can be used to
establish a link between two tables.
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Type of keys in a Relation
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
REFERENTIAL INTEGRITY –
A referential integrity is a system of rules that a DBMS uses to
ensure that relationships between records in related tables are
valid, and that users don’t accidentally delete or change related
data. This integrity is ensured by foreign key.
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Practice
Question: 1
Identify the
following :
• Candidate keys
• Primary Key
• Alternate Key
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Practice
Question: 2
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Practice
Question: 3
Identify the
following :
• Candidate keys
• Primary Key
• Degree
• cardinality
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Practice
Question: 4
Consider the following table STUDENT {SID, FNAME, LNAME,
COURSEID}
Find the following :
• Candidate keys
• Primary Key
• Foreign key
• Degree of the table
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Practice
Question:
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Practice
Question:
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Union:
It allows manipulation of result returned by two or more queries . During such operations, we take
two or more results from SELECT statements and create a new table with the collected data by
merging the result of each query into one single set. Union will always return unique values from
the both tables.
Rules :
Both the tables should be union compatible means :
• It should have same number of columns.
• Domain of ith column in a relation should be same with the ith column in another relation .
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Union:
It allows manipulation of result returned by two or more queries . During such operations, we take
two or more results from SELECT statements and create a new table with the collected data by
merging the result of each query into one single set. Union will always return unique values from
the both tables.
Rules :
Both the tables should be union compatible means :
• It should have same number of columns.
• Domain of ith column in a relation should be same with the ith column in another relation .
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Intersection :
It allows manipulation of result returned by two or more queries . During such operations, we take
two or more results from SELECT statements and create a new table containing common data of
both the tables. Intersection will always return common values from the both tables.
Rules :
Both the tables should be union compatible means :
• It should have same number of columns.
• Domain of ith column in a relation should be same with the ith column in another relation .
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Minus:
It is used to select values which is present in one table but not in another.
Rules :
Both the tables should be union compatible means :
• It should have same number of columns.
• Domain of ith column in a relation should be same with the ith column in another relation .
Created by Archana Jain
During such operations, we take two or more results from SELECT statements and create a new table with the collected data.
Cartesian Product
It produces all the possible concatenations of the table.
R1 X R2
Number of rows in resultant set = No. of rows in relation R1 * No. of rows in Relation R2
Number of cols in resultant set = No. of cols in relation R1 + No. of cols in Relation R2
Created by Archana Jain
The MINUS, UNION and INTERSECT
operators will always
sort the returned results;
It is not possible to use ORDER BY in the
individual queries that make a
compound query. An ORDER BY
clause can be appended to the end of a
compound query.
Created by Archana Jain
