Database Concepts
A database is a computer based record keeping system. A database may be
defined as a collection of interrelated data stored together to serve multiple
applications.
Limitations of the File-based approach
1. Difficulty in access:
2. Data redundancy-storing same data mutiple times.
3. Data inconsistency- Multiple copies of same data when do not match, it is
called data inconsistency.
4. 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.
5. Data Dependence- The close relationship between data stored in files and
the software programs that update and maintain those files is called data
dependence.
6. Data sharing security/Control Issues-There is no provision to have control
over data access. All the data is available to all types of users.
Database Systems
A database is an organised collection of structured information, or data,
typically stored electronically in a computer system at a central location.
Database Management System(DBMS)
A
DBMS is a software system designed to maintain a database and provide
data management services.
Advantages of databases
1. Databases reduce the data redundancy to a large extent
2. Daabases can control data inconsistency to a large extent.
3. Databases facilitate sharing of data-Data may be shared among several
different users.
4. Databases enforce standards-All the data follows the standardised
structure and format.
5. Databases can ensure data security- Carrying out authorization checks
whenever access to sensitive data is attempted.
6. Databases ensure Data independence-Application programs are
unaffected from the changes in the data structures.
Relational database model
, 2
Relational data model is based on a collection of tables. A row in a table
represents a relationship among a set of values. Since a table is a collection
of such relationships, it is called relation.
Components of a table
1. Byte - A byte is a group of eight bits and is used to store a character.
2. Data Item - A data item is the smallest unit of named data.
3. Record - A record is a named collection of data items which represents a
complete unit of information.
4. Table- A table is a named collection of all occurrences of a given type of
logical record.
Relational Model Terminology
The relational model was propounded by E.F. Codd.
Relation
Properties
1. In a given column of a table, all items are of the same kind whereas items
in different columns may not be of the same kind.
2. For a row, each column must have an atomic value.
3. Allrows of a relation are distinct.
4. There is no order maintained for rows inside a relation.
5. The columns of a relation are assigned distinct names and the ordering is
immaterial.
Domain
A domain is a pool of values from which the actual values appearing in a
given column are drawn.
Tuple
The rows of tablesare called tuples.
Attributes
The columns of tables are referred to as attributes.
Degree
The number of attributes in a relation determine the degreeof a relation.
Cardinality
The number of rows in a relation is called cardinality of the relation.
Keys in a database
1. Primary key
, 3
uniquely
A primary key is a set of one or more attributes that can
identify tuples within the relation.
a
In some tables, combination of more than one attribute provides
unique value for each row. In such cases, the primary key consists of
more than one attribute, it is called composite-primary-key.
Primary key doesnot contain duplicate or null values.
non
The non-primary-key attributes of a table can be referred to as
key attributes.
2. Candidate key
All attribute combinations inside a relation that can serve as primary
key are candidate keys as they are candidates for the primary key
position.
3. Alternate key
A candidate key that is not the primary key is called an Alternate key.
4. Foreign key
A foreign key is used to represent the relationship between two
relations. A foreign key is an attribute whose value is derived from the
primary key of another relation. It can have null value or duplicate
values. The referencing relation is called Foreign relation. The relation
in which the referenced primary key is defined is called primary
relation or master relation.
Structured Query Language(SQL)
SQL, Structured Query Language, was developed in 1970s.
MySQL elements
1) Literals
Literals refer to a fixed data value. Literals may be character type or numeric
type. Character literals are enclosed in single quotation marks or double
quotation marks. Numeric literals can either be integer literals or real literals.
2) Data Types
Data types are means to identify the type of data and associated operations for
handling it.
Datatypes divided into three categories.
1. Numeric data types
int
float
A database is a computer based record keeping system. A database may be
defined as a collection of interrelated data stored together to serve multiple
applications.
Limitations of the File-based approach
1. Difficulty in access:
2. Data redundancy-storing same data mutiple times.
3. Data inconsistency- Multiple copies of same data when do not match, it is
called data inconsistency.
4. 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.
5. Data Dependence- The close relationship between data stored in files and
the software programs that update and maintain those files is called data
dependence.
6. Data sharing security/Control Issues-There is no provision to have control
over data access. All the data is available to all types of users.
Database Systems
A database is an organised collection of structured information, or data,
typically stored electronically in a computer system at a central location.
Database Management System(DBMS)
A
DBMS is a software system designed to maintain a database and provide
data management services.
Advantages of databases
1. Databases reduce the data redundancy to a large extent
2. Daabases can control data inconsistency to a large extent.
3. Databases facilitate sharing of data-Data may be shared among several
different users.
4. Databases enforce standards-All the data follows the standardised
structure and format.
5. Databases can ensure data security- Carrying out authorization checks
whenever access to sensitive data is attempted.
6. Databases ensure Data independence-Application programs are
unaffected from the changes in the data structures.
Relational database model
, 2
Relational data model is based on a collection of tables. A row in a table
represents a relationship among a set of values. Since a table is a collection
of such relationships, it is called relation.
Components of a table
1. Byte - A byte is a group of eight bits and is used to store a character.
2. Data Item - A data item is the smallest unit of named data.
3. Record - A record is a named collection of data items which represents a
complete unit of information.
4. Table- A table is a named collection of all occurrences of a given type of
logical record.
Relational Model Terminology
The relational model was propounded by E.F. Codd.
Relation
Properties
1. In a given column of a table, all items are of the same kind whereas items
in different columns may not be of the same kind.
2. For a row, each column must have an atomic value.
3. Allrows of a relation are distinct.
4. There is no order maintained for rows inside a relation.
5. The columns of a relation are assigned distinct names and the ordering is
immaterial.
Domain
A domain is a pool of values from which the actual values appearing in a
given column are drawn.
Tuple
The rows of tablesare called tuples.
Attributes
The columns of tables are referred to as attributes.
Degree
The number of attributes in a relation determine the degreeof a relation.
Cardinality
The number of rows in a relation is called cardinality of the relation.
Keys in a database
1. Primary key
, 3
uniquely
A primary key is a set of one or more attributes that can
identify tuples within the relation.
a
In some tables, combination of more than one attribute provides
unique value for each row. In such cases, the primary key consists of
more than one attribute, it is called composite-primary-key.
Primary key doesnot contain duplicate or null values.
non
The non-primary-key attributes of a table can be referred to as
key attributes.
2. Candidate key
All attribute combinations inside a relation that can serve as primary
key are candidate keys as they are candidates for the primary key
position.
3. Alternate key
A candidate key that is not the primary key is called an Alternate key.
4. Foreign key
A foreign key is used to represent the relationship between two
relations. A foreign key is an attribute whose value is derived from the
primary key of another relation. It can have null value or duplicate
values. The referencing relation is called Foreign relation. The relation
in which the referenced primary key is defined is called primary
relation or master relation.
Structured Query Language(SQL)
SQL, Structured Query Language, was developed in 1970s.
MySQL elements
1) Literals
Literals refer to a fixed data value. Literals may be character type or numeric
type. Character literals are enclosed in single quotation marks or double
quotation marks. Numeric literals can either be integer literals or real literals.
2) Data Types
Data types are means to identify the type of data and associated operations for
handling it.
Datatypes divided into three categories.
1. Numeric data types
int
float
