Study notes

Database System Concepts

Definition
• End users
• DBMS
• Database
• Data dictionary
• Application Programmer
• Database Administrator
Database
Database is a shared collection of inter-related data designed to meet the
various information needs of an organization. Databases are systems for
information management.
For example, most of the healthcare, educational, and social welfare
organizations easily handles there daily transactions with the help of databases.

1. End Users
• The database is designed, created and maintained to serve the
information needs of end users, who use the data and information to
perform their jobs.
• Regardless of the elegance of the database design or the sophistication of
the hardware and software used. If the database does not provide
adequate information to users, it is failure. Users can be categorized
according to the way they access data.

2. DBMS
• The DBMS is a software system that receives and satisfies all requests for
data. Normally the DBMS provides concurrent access to multiple database
users.
• The commonly used DBMS are My SQL, MS Access, MS SQL Server, Oracle
etc.

3. Database
The “database more specifically in database system environment” is the
physical repository of all user’s data.
For example, student information like registration, exam, finance and library are
stored within a university database.

4. Data Dictionary / Dictionary

• Data about data is stored in data dictionary
• The Data Dictionary is a repository of all definitions of data used by the
organization.
 • For example, all data item names, lengths and representations are stored
over here.

5. Application programmer
• This group includes programmers who write application programs for the
users.
• Their application programs may be written in a variety of hosts
programming languages such as C#, VB and JAVA.
• Each Application Program to access the database contents; calls the
database management system (DBMS) to perform the operations over the
database. For example, update or retrieval.

6-Database Administrator (DBA)

• The DBA is the person or group responsible for designing, creating and
maintaining the database. The DBA interacts with the database as a “super
user” who not only accesses data but who controls and accesses
information about the user.
Overview
• Data
• Information
• Real World Data
• Meta Data
• Existence Data
• Database vs Database Management System
• The Range of Database Applications
• Drawbacks of Traditional File Processing Systems
Data
• The term data referred to facts about objects and events that could be
recorded and stored on computer media
• For example
• In a salesperson’s database the data will be facts such as:
• Customer name, address and contact number.
• This type of data is called structured data.
• The salesperson database might include some more:
• For example Product Picture, image of the customer, sound recording or
video clip of the product. This type of data is called as unstructured data or
as multimedia data.

Data
• Structured data?
• Unstructured data or as multimedia data?
• Information
• For Example.
• Consider the following facts:

Ahmad Ali 12589

Shafiq Khan 36985

Saleem Khan 41563

Ashraf 74125

Rashid 45689

Khalid 78541

• These facts satisfy our definition of Data, but data are useless in its
present form.
Information(cont..)
• By adding few additional data items and providing some structure, we
recognize a class BS 3rd semester for a particular course. Now this is some
useful information to some users like course instructor or Examination
Department.

Class BS 5th Semester

Semester: Fall 2010 Section: B

Name Id Major GPA

Ahmad Ali 12589 MGT 2.9

Shafiq Khan 36985 MKT 3.4

Saleem Khan 41563 PRM 3.7

Ashraf 74125 MGT 2.8

Rashid 45689 IS 2.9

Khalid 78541 ACCT 3.3

Information(cont..)
• Another way to convert the data into information is to summarize it or
otherwise process and present it for human interpretation (to understand
it easily).
 GPA
4
3.5
3
2.5
2 GPA
1.5
1
0.5
0
MGT
MKT
PRM
MGT
IS
ACCT

Data VS Information
DATA INFORMATION

Always in Raw form Always in Arranged Form

Can’t used directly for decision making Can be used for decision making di

No Information system in data collection Information system i.e Computer s

and arrangement involved in this process

Input to Computer System Output of Computer System

Levels of Data
• Real World Data
• Meta Data
• Existence Data

Real World Data

• The real world level of data means that level of data at which entities or
objects exist in reality
 • It means that any object existing in reality have a name and other
well known attributes through which we can understand that specific
object or entity.
For Example
• Student
• Teacher
• Doctor

Metadata
• Data that describes the properties of data.
• Some of the properties of the data are data names, definitions, length or
size, and domain values.
• Also known as data about data.

Data Items Values

Name Type Length Min Max Source
Course Alphanumeric 30 Academic Unit
Section Integer 1 1 9 Academic Unit
Semester Alphanumeric 10 Exam Unit
Name Alphanumeric 30 Student
Id Integer 9 Student
Major Alphanumeric 4 Student
GPA Decimal 3 0.0 4.0 Academic Unit
Levels of Data

Difference between Database and Database

Management System
• The database is the collection of data about anything, could be anything.
• Like cricket teams, students, busses, movies, personalities, buildings,
furniture, lab equipment, hotels, countries, and many more anything
about which you want to store data.
• What is Data???

Database Management System?

• A Database Management system (DBMS) is basically a database
management software or a record-keeping system; i.e, it is a computerized
system whose overall purpose is to help users in storing their data and to
allow users to retrieve and update it on demand
• DBMS creates manage and keep the data secure for future use.

The Range of Database Applications

• Although there is some overlap, we divide database applications into five
categories:
• Personal databases
• Workgroup databases,
 • Departmental/Divisional databases,
• Enterprise databases
• Web-enabled databases.

Traditional File Processing Systems

• Traditional file processing system or simple file processing system means
the first computer-based approach of handling the commercial or business
data.
• That is why it is also called a replacement of the manual file system.
• Before the use of computers, the data in the offices or business was
maintained in the files.
• Obviously, it was laborious, time consuming, inefficient, especially in case
of large organizations

Drawbacks of Traditional File Processing Systems

• Traditional File Processing Systems temporarily solved the problem of
Manual work but soon it was realized that Traditional File Processing
System has lot of drawbacks
• A major drawback in the traditional file system environment is the non-
sharing of data.
• It means if different systems of an organization are using some common
data rather than storing it once for further reuse. In traditional system
each sections stores data in separate files.
• This creates the problem of redundancy or wastage of storage.

Inconsistency
• One other important problem is inconsistency of data.
• The change in the record in one system is not reflected in the same record
stored in other system.
• So different systems in organization; store different facts about same
thing. This is inconsistency as is shown in figure below
Inconsistency(cont…)
 Advantages of Database Approach(cont…)

Redundancy
Data Independence
Better Data Integrity

Advantages of Database Approach(cont…)

• Data Sharing
• The figure shows different subsystem or applications in an educational institution,
like library system, examination system, and registration system.
• These are separate, different application programs for every application or
subsystem.
• However, the data for all applications is stored at the same place in the database and
all application programs, relevant data and users are being managed by the DBMS.
Advantages of Database Approach(cont…)
• For example
• Data like registration number, name, address, father name that is common among
different applications is being stored repeatedly in the file processing system
environment
• where as it is being stored just once in database system environment and is being
shared by all applications.

Advantages of Database Approach(cont…)

b) Data Independence
• Data and programs are independent of each other, so change in one has no or
minimum effect on other.
• Data and its structure are stored in the database where as application programs
manipulating this data are stored separately, the change in one does not
unnecessarily affect other
Both application programmer and database admin can work on their units
independently.

Advantages of Database Approach(cont

c) Controlled redundancy
• It means that we do not need to duplicate data unnecessarily; we do duplicate data
in the databases, however, this duplication is controlled and for relating the tables.

Advantages of Database Approach(cont…)

d) Better Data Integrity
• It is very important feature and it means the validity points of the data being stored
in the database.
• Integrity (accuracy and true reference) of data is very important, since all the
processing and the information produced in return are based on the data.
• Now if the data entered is not valid, how can we be sure that the processing in the
database is correct and the results or the information produced is valid?
Advantages of Database Approach(cont…)
e) Data Consistency
• Data consistency means that the changes made to different set of data should be
controlled and managed in such a way that all the set have same value for any
specific data item.
• In database approach it is controlled because data is shared and consistency is
controlled and maintained

Advantages of Database Approach(cont…)

f) Better Data Security
• All application programs access data through DBMS, So DBMS can very efficiently
check that which user is performing what sort of action and accessing which part of
data.
• So a DBMS most effectively control and maintain security of Data stored in a
database.

Advantages of Database Approach(cont…)

I) Better Concurrency Control
• Concurrency means the access of database form number of points
simultaneously.
• Concurrency control means to access the database in such a way that all the data
accesses are completed correctly.
• One example of controlled concurrency is the use of ATM Machine for withdrawal of
money (cash).
• All ATM machines of a bank are connected to a central database system
worldwide, so that a multiple user can access the accounts from different
locations and can get cash from any ATM terminal.

Advantages of Database Approach(cont…)

i) Better Concurrency Control
• As there may be thousands of ATM terminal across the region for a specific bank so as a
result thousand of user process and access the bank’s database.
• All these processes are managed concurrently using the database systems and is done in
such an efficient manner that no two user face any problem in the processing of their
requests.
 E- R DATA MODEL
Overview

ER Data Model

Types of Entity type

Weak Entity Types

Strong Entity Type

Naming Entity Types

Symbols

Attribute

Types of Attributes

Symbols for Attributes

Primary Key

Foreign Key

Relationships

Symbol for Relationships

Relationships types

1. Unary Relationship

2. Binary Relationship

Attributes of the Relationship

3. Ternary Relationships
ER Data Model (or E-R Model)
The ER model defines the conceptual view of a database. It works around real-world
entities and the associations among them.
At view and engineering level, the ER model is considered as a good option for designing
databases.

ER Data Model (or E-R Model)

An entity-relationship model (e-r model) is a detailed, logical representation of the data for an organization
or for a business area.
The E-R model is expressed in terms of entities in the business environment, the relationships (or
associations) among those entities, and the attributes (or properties) of both the entities and their
relationships.
An E-R model is normally expressed as an entity-relationship diagram (E-R diagram, or ERD), which is a
graphical representation of an E-R model.

E-R Model vs. E-R diagram

E-R Model:
Logical representation of the data for an organization or for a business area, using entities for categories of data and
relationships for associations between entities.

E-R Diagram:
A graphical representation of an entity-relationship model.

Major Components of ERD

 Entities
 Attributes
 Relationships

Entity
 Term used to mean three different meanings
Entity type
Entity instance
Entity set

Entity Type
 A name/label assigned to items/objects that exist in an environment and
that have similar properties
  It could be person, place, event or even concept

Entity Type
 Distinguishable from other entity types on the basis of properties
 Identified through abstraction (thinking closer) process
 Different from External Entity (Not from un-related terminology)

Entity Instance & Set

 A particular object belonging to a particular entity type
 Entity Type: Employee
 Entity Instance: M. Sharif
 Entity Set: All employees

Types of Entity type

Entity types can be classified into
1. Regular/Strong/Independent ETs
2. Weak/Dependent ETs

Weak Entity Types

An entity type whose instances cannot exist without being linked with
.instances of some other entity type, i.e
.They cannot exist independently

Strong Entity Type

 A strong/regular entity type is the one whose instances can exist
independently, i.e., without being linked to other instances
 Strong ETs have their own identity
Naming Entity Types
 Singular noun recommended
 Organization specific names
 Write in capitals
 Abbreviations can be used.
E.G student enrollment
STD_ENROL

Symbols
Reg Entity Type NAME

Weak Entity Type NAME

BOOK BOOK_COPY
Attribute
An attribute of an entity type is a defining property or quality of the instances of that entity type. Entity
instances of same entity type have the same attributes. (e.g. Student Identification, Student Name)
 Every attribute has a domain
-Set of possible values for an attribute

1. Simple
Attributes-

2. Composite
f
3. Single Valued
Attributes-

4. Multi Valued
Attributes-
5. Derived

6. Key
Attributes-
Symbols for
 Each represented as an oval, linked
with an ET symbol
 Types of Attributes
 Simple vs. composite
 Single valued vs. multi-valued
 Stored vs. derived

Symbols for Attributes

Simple
Composite
Multi-valued
Derived
Examp
le
Experienc empId empName
e

EMPLOY address
EE

dateHired Emp Qual street houseNo

Table
Customers

Field (Column)
Customers

a field
Record (Row)
Customers

a record
 Primary
Customers

primary key
field
y key is a unique identifier of records in a table.
y key values may be generated manually or automatically.

Primary
Key Roles (Performances)

Composite primary key

A primary key can consist of more than one field
called composite PK.
 Foreign
primary key
field parent
table
Director
s

relationshi child table

p Movies

foreign key
field

Relationships
 Relationships are the connections and interactions between the entities
instances, e.g., Program and Student ETs are linked
 Naming Relationships
• Up to you
• If there is no proper name of the association in the system then
participants’ names of abbreviations are used
 STUDENT and CLASS have ENROLL relationship
  However, it can also be named as STD_CLS

Symbol for
Relationships
DEPT EMP

STD BOOK

Relationships types
 Relationship type can be identified like an entity type
 Entities involved in a relationship are called its participants
 Types of the relationships can be established on the basis of participant
ETs
 One-to-One Relationships
 One-to-Many Relationships
 Many to One Relationships
 Many-to-Many Relationships
1.One-to-one:
 One instance from entity set X can be associated with at most one
instance of entity set Y and vice versa.
 Example: One student can register for numerous courses. However, all
those courses have a single line back to that one student. But one at a
time.

One to one
Deptt HoD

2.One-to-many:
 One instance from entity set X can be associated with multiple instances of
entity set Y, but an entity from entity set Y can be associated with at least
one entity.
 For example, one class is consisting of multiple students.

3. Many to One
 More than one instances from entity set X can be associated with at most
one instance of entity set Y. However, an instance from entity set Y may or
may not be associated with more than one instance from entity set X.
 For example, many students belong to the same class.
4. Many to Many:
 One instance from Entity X can be associated with more than one
Instances from Y and vice versa.
 For example, Students as a group are associated with multiple faculty
members, and faculty members can be associated with multiple students.

Sample ERD
 Relationship
 A meaningful association among entity types.
 Relationship is the association among the instances of one or more
entities.
 Relationship is the glue that holds together the various component of E-R
model.
 Example
 STUDENT Completes COURSE

Relationship
 Graphical Representation
 Denoted by Line labeled with the name of relationship

Completes
STUDENT COURSE

Relationship
 Associative Entity
 An entity that associates (links) the instances of one or more entities
and contains attributes that belongs to the relationship of
participating entities.

Relationship
 Associative Entity
 When should a relationship with attributes can be an associative entity?
 Associative entity exist only when the participating entity type has
many to many relationship.
 Associative entity may have its own identifier and other attributes
 Associative entity get primary key of participating entities with other
attributes belongs to the relationship.
Relationship
 Associative Entity
 Graphical Representation
 Represented by diamond symbol enclosed in a rectangle

Date_Complete
Certificate_No d

Roll_No Title
Name Course_ID

EMPLOYEE CERTIFICATE COURSES

Relationship
Degree of Relationship
 The number of entity types that participate in a relationship is called
degree of the relationship.
 The most commonly used relationship in E-R Model are Unary, Binary and
Ternary relationship.

Relationship
Unary Relationship
 A relationship among the instances of single entity type
 Unary relationship are also called recursive relationship
 Relationship

Unary Relationship

Manages

EMPLOYEE

Relationship
Binary Relationship
 A relationship among the instances of two entity types

EMPLOYEE PARKING SPACE

STUDENT DEPARTMENT

STUDENT COURSE
Relationship
Ternary Relationship
 A relationship among the instances of three entity types

a relationship can have attributes of its own

 Database System Concepts
Relational Database Design Concepts
Overview
Relational Data Model
 Data structure
 Data Manipulation
 Data integrity

Relational Keys
 Candidate Key
 Primary Key
 Composite Primary Key
 Alternate Key
 Secondary Key
 Foreign Key
Alternative terminologies
Introduction
 The relational data model represents data in the form of tables.
 The relational data model consists of the following three components:
 Data structure
 Data Manipulation
 Data integrity

Introduction
 Data structure
 Data are organized in the form of tables with rows and columns.
 Data Manipulation
 Powerful operations (using the SQL language) are used to manipulate
(control and operate on) data stored in the relations.
 Data integrity
 Facilities are included to specify business rules that maintain the integrity
of data when they are manipulated.

Relational Keys
We must be able to store in and retrieve data from a table, based on some well-
known attribute’s values.
for achieving this goal relations (tables) contains some keys.

Keys
 The set of attributes that can be used to identify or access records of a
table. The concept of key is very important.
 Why and how?
 A table may have many instances (records), from a few to several
thousands and even more.
  Now out of many instances, when we want to retrieve a particular record,
and many times we do need it, then key is the only solution.

Keys
 For example
 Think of whole population of Afghanistan, the data of all citizen stored at
one place, let say with NIC (National Identity Card) Department.
 Now if at some time we need to identify a particular person out of all
stored data, how can we do that?
 Can we use name for that, well, think of any name, like Muhammad
Aslam, now we may find many people with this name in Afghanistan.
 Another option is the combination of name and father name, then again,
Muhammad Shoaib s/o Muhammad Karim, there could be so many such
pairs. There could be many such examples. Another point for this is that
the key comes lengthy.

Keys
 For example
 However, if you think about National ID Card number, then no matter
whatever is the population of Afghanistan, you will be able always to pick
a single person. That is the key.
 While defining an entity type we also generally define the key of that
entity type.
 How do we select the key?
 From the study of the real-world system; key attribute(s) already exist
there, sometimes if they don’t then the designer has to define one, like
sear number as auto number.
 A key can be simple, that is, consisting of single attribute, or it could be
composite which consists of two or more attributes.
Types of Keys
 Following are the major types of keys:
 Candidate Key
 Primary Key
 Composite Primary Key or Composite Key
 Alternate Key
 Secondary Key
 Foreign Key

Candidate key
 One or more keys that are candidate for becoming the primary key are
called Candidate Keys

Candidate Key
Candidate Key
Candidate Key
STUDENT
Primary key
 A candidate key chosen by the database designer to act as unique key is
the primary key. It is an attribute (or combination of attributes) that
uniquely identifies each record in a relation.
 An entity type may have more than one candidate keys; in that case the
database designer has to designate one of them as primary key.
 Since there is always only a single primary key in a table. If there is just one
candidate key, then obviously the same will be declared as primary key.
 The primary key can also be defined as the successful candidate key.

Primary key

Both are candidate keys

Primary key
 Now in this situation we have two candidate keys.
 The one that we choose will be declared as primary key
 Other will be the alternate key.
 Any of the candidate keys can be selected as primary key, it is mainly
depends on the database designer and the organization requirements.

Primary key
Primary Key Alternate
Key

Both both are candidate keys

Primary key
 There are certain things that are generally considered while making this
decision that which candidate key should be primary key
 The key that is shorter, easy to remember, to type and more meaningful to
select as a primary key.
Primary key
 A major characteristic of the PK is that it cannot have the NULL value.
 If PK is a composite, then none of the attributes included in the PK can
have the NULL

Composite Primary Key or Composite key

 It is a primary key that consists of more than one attribute.
 For example
 To Identify Subject Marks for as student we need the ID and Subject name
both to be a part of the primary key.

Alternate Keys
 Candidate keys which are not chosen as the primary key are known as
alternate keys.
 For example
 We have two candidate keys of table STUDENT in figure of a previous
slide, Reg_No and NIC_Number,
 If we select Reg_No as PK then the NIC_Number will be alternate key.

Secondary Key
 Many times we need to access certain instances i.e. group of records from
an entity type using the attribute’s value other than the PK.
The difference in accessing instances using the value of a key and non-key
attribute is that the search on the value of PK will always return a single
record (if it exists). While we can get more records in case of non-key
attribute.

Secondary Key
 For example
 We want to see how many of our students belong to Kabul
 In that case we will access those instances of the STUDENT entity that
contain “Kabul” as their address.
  so address is called secondary key, since we are accessing records on the
basis of address.

Foreign key
 It is an attribute in a relation of a database that serves as the primary key
of another relation in the same database.
 For example
 Consider the relations EMPLOYEE1 and DEPARTMENT:
 EMPLOYEE (Emp ID, Name, DeptNo, Salary)
 DEPARTMENT(DeptNo, Dept Name, Location)
 The attribute DeptNo is a foreign key in EMPLOYEE.
 It allows a user to associate any employee with the department assigned.
 An attribute can be presented as foreign key by a dashed underline.
( DeptNo )

Alternative terminologies for the relational model terms

Overview

Properties of Relation
Integrity Constraints
Domain Constraints
Entity Integrity
Referential integrity
Database System Concepts
Relation Properties and Constraints
Properties of Relation
 We have defined relations as two-dimensional tables
of data.
 However, not all tables are relations (Tables of
database while relationship means link or association).
 Relations have several properties that distinguish them
from non-relational tables.
 We summarize these properties on coming slides.
Properties of Relation
1. Each relation (or table) in a database has a unique name
 It means that in one relational schema there should be
no other table having the same name.
Properties of Relation
2. An entry at the intersection of each row and column is atomic (or single
value).
 A cell is the intersection of a row and a column, so it represents a value of
an attribute in a particular row.
 The property means that the value stored in a single cell is considered as a
single value.

Properties of Relation

Properties of Relation
3. Each row is unique; no two rows in a relation are identical.
 Two rows of a table cannot be same. The value of at least a single attribute
has to be different that makes the entire row distinct.

Properties of Relation
4. Each attribute (or column) within a table has a unique name.
 It has to be unique, a table cannot have duplicated column/attribute
names.
  In the table figure we seen, the bold items in the first row represent the
column/attribute names.

Properties of Relation
5. The sequence of columns (left to right) is not necessary. The columns of a
relation can be interchanged without changing the meaning or use of the
relation.
6. The sequence of rows (top to bottom) is not necessary. As with columns,
the rows of a relation may be inter changed or stored in any sequence.

Properties of Relation
7. The values of the attributes come from the same domain
 Each attribute is assigned a domain when it is defined.
 The domain represents the set of possible values that an attribute can
have.
 Domain type may be numeric or text or some other valid data types.

Integrity Constraints
 The relational data model includes several types of constraints, or
business rules. The main purpose is to maintain the accuracy and integrity
of data in the database.
 The major types of integrity constraints are
 Domain Constraints
 Entity Integrity
 Referential integrity

Domain Constraints
 All the values that appear in a column of a relation must be taken from the
same domain.
 A domain is the set of values that may be assigned to an attribute.
 A domain definition usually consists of the following components:
  Domain name
 Data type
 Size (or length).
 Allowable values.

Entity Integrity
 The entity integrity rule is designed to ensure that every relation has a
primary key (highly recommended), and that values for that primary key
are all valid.
 It guarantees that every primary key attribute is non-null.
 It guarantees that primary key value must not be duplicated.

Entity Integrity
 A null is a value that may be assigned to an attribute when no other value
applies or when the value is unknown.
 In reality, a null is not a value but rather the absence of a value.

Referential Integrity
 Referential integrity constraint is a rule that maintains consistency among
the rows of two relations.
 The rule states that if there is a Foreign Key in one relation, then either
each foreign key value must match a primary key value in another
relation, or the foreign key value will be null.

Normalization
• Main objective in developing a logical data model for relational database
systems is to create an accurate representation of the data, its
relationships, and constraints.
• To achieve this objective, we must identify a suitable set of relations.
Normalization
• Four most commonly used normal forms are first (1NF), second (2NF) and
third (3NF) normal forms, and Boyce–Codd normal form (BCNF).
• Based on functional dependencies among the attributes of a
relation/Table.
• A relation can be normalized to a specific form to prevent possible
occurrence of update anomalies.

Normalization
• Normalization is the process for assigning attributes to entities
• Reduces data redundancies
• Helps eliminate data anomalies
• Produces controlled redundancies to link tables
• Normalization stages
• 1NF - First normal form
• 2NF - Second normal form
• 3NF - Third normal form
• 4NF - Fourth normal form

Data Redundancy
• Major aim of relational database design is to group attributes into
relations to minimize data redundancy and reduce file storage space
required by base relations.
• Problems associated with data redundancy are illustrated by comparing
the following Staff and Branch relations with the StaffBranch relation.
 Data Redundancy

Data Redundancy

Data Redundancy
• StaffBranch relation has redundant data: details of a branch are repeated
for every member of staff.
• In contrast, branch information appears only once for each branch in
Branch relation and only branchNo is repeated in Staff relation, to
represent where each member of staff works.
Update Anomalies
• Relations that contain redundant information may potentially suffer from
update anomalies.
• Types of update anomalies include:
• Insertion,
• Deletion,
• Modification.

Insertion anomalies
 At the time of record insertion in to a table which is not suitably
normalized. It is often become time consuming, because some part of
stored record needs to be re-typed.
 And Some spelling mistake may lead to data inconsistency.
 While no mistake also leads to data redundancy.
 For example entering new employee in the same branch using the
StaffBranch relation.

Deletion anomalies
 At the time of record Deletion from a table which is not suitably
normalized. It is often become lost of necessary information, because
some part of stored record needs to maintained.
 As the record can’t be partially deleted means it is atomic.
 For example deletion of a staff member may completely delete a branch
from the system based on StaffBranch relation.

Modification anomalies
 At the time of record Modification in a table which is not suitably
normalized. It is often leads the database to inconsistent state, because
some part of stored record needs to maintained coherent all the times.
 As on a record referential integrity can’t be implement unless and until it is
not in primary and foreign key relationship .
For example modifying the staff member’s name or a branch’s address may left
the record set inconsistent or the modification may become quite hard in
consideration of StaffBranch relation, as the same modification may be required
in multiple records.

Dependency Diagram (1NF)

The Process of Normalization

 Formal technique for analyzing a relation based on its primary key and
functional dependencies between its attributes.
 Often executed as a series of steps. Each step corresponds to a specific
normal form, which has known properties.
 As normalization proceeds, relations become progressively more restricted
with removal of anomalies.

First Normal Form (1NF)

 A relation in which intersection of each row and column contains one and
only one value.

UNF to 1NF
 Nominate an attribute or group of attributes to act as the key for the table.
 All key attributes defined
 No repeating rows in table
 All attributes dependent on primary key
Second Normal Form (2NF)
 Based on concept of full functional dependency:
 A and B are attributes of a relation,
 B is fully dependent on A if B is functionally dependent on A but not on
any proper subset of A.
 2NF - A relation that is in 1NF and every non-primary-key attribute is fully
functionally dependent on the primary key (no partial dependency)
 1NF to 2NF
 Identify primary key for the 1NF relation.
 Identify functional dependencies in the relation.
 If partial dependencies exist on the primary key remove them by placing
them in a new relation along with copy of their determinant.

 Dependency Diagram (1NF)…

 2NF Conversion
Results

Third Normal Form (3NF)

 Based on concept of transitive dependency:
 A, B and C are attributes of a relation such that if A  B and B  C,
 then C is transitively dependent on A through B.

 3NF - A relation that is in 1NF

and 2NF and in which no non-primary-key attribute is transitively
dependent on the primary key.

2NF to 3NF
 Identify the primary key in the 2NF relation.
 Identify functional dependencies in the relation.

• If transitive dependencies exist on the primary key remove them by

placing them in a new relation along with copy of their determinant.
3NF Conversion Results
• Prevent referential integrity violation by adding a JOB_CODE

PROJECT (PROJ_NUM, PROJ_NAME)

ASSIGN (PROJ_NUM, EMP_NUM, HOURS)
EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS)
JOB (JOB_CLASS, CHG_HOUR)

Boyce–Codd Normal Form (BCNF)

 Difference between 3NF and BCNF is that for a functional dependency A 
B, 3NF allows this dependency in a relation if B is a primary-key attribute

and A is not a candidate key.

 Whereas, BCNF insists that for this dependency to remain in a relation, A

must be a candidate key.
 Every relation in BCNF is also in 3NF. However, relation in 3NF may not be
in BCNF.

3NF Table Not in

BCNF

Figure 4.7
Decomposition of
Table
Structure to Meet
 Database System Concepts
SQL
Structured Query Language
Overview
SQL
DML-(Data Manipulation Language)
-SELECT (show data)
-INSERT (save data)
-UPDATE (change data)
-DELETE (remove data)
DDL-(Data Definition Language)
-CREATE
-ALTER
-DROP
SQL is a Standard
SQL is an ANSI (American National Standards Institute) standard computer
language for accessing and manipulating database systems. SQL statements are
used to retrieve and update data in a database. SQL works with database
programs like MS Access, MS SQL Server, Oracle, etc.
There are many different versions of the SQL language, but to be in compliance
with the ANSI standard, they must support the same major keywords in a
similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and so on).
Note: Most of the SQL database programs also have their own proprietary
extensions in addition to the SQL standard!
A database most often contains one or more tables. Each table is
SQL Database
by a name (e.g. "Customers" or "Orders"). Tables contain records
with data.
Tables
Below is an example of a table called "Persons":
A database most often contains one or more tables. Each
LastName FirstName Address City
table
Hansen is identified by
Ola a name (e.g. "Customers"
Timoteivn 10 or "Orders").
Sandnes
Tables
Svendson
contain records
Tove
(rows) with data.
Borgvn 23 Sandnes
Below
Pettersen
is an example
Kari
of a table called "Persons":
Storgt 20 Stavanger

LastName FirstName Address City

Hansen Ola Timoteivn 10 Sandnes

Svendson Tove Borgvn 23 Sandnes

Pettersen Kari Storgt 20 Stavanger

SQL Data Manipulation

Language (DML)
SQL (Structured Query Language) is a syntax for
executing queries.
But the SQL language also includes a syntax to update,
insert, and delete records.
These query and update commands together form the
Data Manipulation Language (DML) part of SQL:
• SELECT - extracts data from a database table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• INSERT INTO - inserts new data into a
database table

SQL Data Definition

Language (DDL)
The Data Definition Language (DDL) part of SQL permits database tables to be
created or deleted. We can also define indexes (keys), specify links between
tables, and impose constraints between database tables.
The most important DDL statements in SQL are:
CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database table

SQL Queries
With SQL, we can query a database and have a result set returned.
A query like this:
SELECT LastName FROM Persons
Gives a result set like this:
LastName
Hansen
Svendson
Pettersen
 SQL The SELECT Statement
The SELECT statement is used to select data from a table. The tabular result is
stored in a result table (called the result-set).
Syntax
SELECT column_name(s)
FROM table_name
To select the columns named "LastName" and "FirstName", use a SELECT
statement like this:
SELECT LastName, FirstName FROM Persons
Persons

LastName FirstName Address City

Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger

Result Set

LastName FirstName

Hansen Ola

Svendson Tove

Pettersen Kari

Select All Columns

To select all columns from the "Persons" table, use a * symbol instead of
column names, like this:
SELECT * FROM Persons

LastName FirstName Address City

Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger
The SELECT DISTINCT Statement
The DISTINCT keyword is used to return only distinct (different) values.
The SELECT statement returns information from table columns. But what if we
only want to select distinct elements?
With SQL, all we need to do is to add a DISTINCT keyword to the SELECT
statement:
Syntax
SELECT DISTINCT column_name(s)
FROM table_name

Using the DISTINCT keyword

To select ALL values from the column named "Company" we use a SELECT
statement like this:
SELECT Company FROM Orders
Orders Company
Sega
Company
W3Schools
Company OrderNumber Sega
Trio
Sega 3412 W3Schools
W3Schools
W3Schools 2312 Trio
Trio 4678 W3Schools
W3Schools 6798

Note that "W3Schools" is listed twice in the result-set.

To select only DIFFERENT values from the column named "Company" we use a
SELECT DISTINCT statement like this:
SELECT DISTINCT Company FROM Orders

Company
Sega
W3Schools
Orders

Company OrderNumber Company

Sega 3412 Sega

W3Schools 2312 W3Schools

Trio 4678 Trio

W3Schools 6798

Select Specified Columns

The WHERE clause is used to specify a selection criterion.
The WHERE Clause
To conditionally select data from a table, a WHERE clause can be added

Syntax
SELECT column FROM table
WHERE column operator value
 With the WHERE clause, the following operators can be used:
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern

Note: In some versions of SQL the <> operator may be written as !=

Using the WHERE Clause

To select only the persons living in the city "Sandnes", we add a WHERE clause
to the SELECT statement:
SELECT * FROM Persons

LastName FirstName Address City Year

Hansen Ola Timoteivn 10 Sandnes 1951
Svendson Tove Borgvn 23 Sandnes 1978
Svendson Stale Kaivn 18 Sandnes 1980
Pettersen Kari Storgt 20 Stavanger 1960

WHERE City='Sandnes'
LastName FirstName Address City Year
Hansen Ola Timoteivn 10 Sandnes 1951
Svendson Tove Borgvn 23 Sandnes 1978
Svendson Stale Kaivn 18 Sandnes 1980
Using Quotes
Note that we have used single quotes around the conditional values in the
examples.
SQL uses single quotes around text values (most database systems will also
accept double quotes). Numeric values should not be enclosed in quotes.
This is correct:
SELECT * FROM Persons WHERE FirstName='Tove'
This is wrong:
SELECT * FROM Persons WHERE FirstName=Tove

The LIKE Condition

The LIKE condition is used to specify a search for a pattern in a column.
Syntax
SELECT column FROM table
WHERE column LIKE pattern
A "%" sign can be used to define wildcards (missing letters in the pattern) both
before and after the pattern.

Using LIKE
The following SQL statement will return persons with first names that start with
an 'O':
SELECT * FROM Persons
WHERE FirstName LIKE 'O%'
The following SQL statement will return persons with first names that end with
an 'a':
SELECT * FROM Persons
WHERE FirstName LIKE '%a'
Using LIKE
The following SQL statement will return persons with first names that contain
the pattern 'la':
SELECT * FROM Persons
WHERE FirstName LIKE '%la%'
 Inserting data in to a table
 Syntax:

insert into table name

Values(col1_val,col2_val,…..coln_val)
E.g.
insert into batch_detail
values( 01, ‘Testing_01’,’Rajiv Arora’,3)
*to insert multiple rows use this query for any no of times.

Retrieving the information

 Syntax:
SELECT <column_list>
FROM <table_name
[WHERE <condition>]
[GROUP BY <column_name(s)>]
[HAVING<condition>]
[ORDER BY <expression>]
select stdAddress,
SUM(stdMarks)as Total from
TBL_STUDENTS
where stdMarks >70
group by STDADDRESS
having SUM(stdMarks)<200
order by STDADDRESS desc;

WHERE Clause
 WHERE Clause is used to specify the condition.
 Operators are used to specify the condition.
1)Relational Operators: =, >,<,<=,>=,<>,!=
2)Logical Operators: AND,OR, NOT
3)Special Operators: IN, BETWEEN, LIKE,IS
 Syntax:
select * from table_name;
select col1_name,.. Coln_name from table_name;
WHERE <condition>
 e.g.
select * from batch_detail
WHERE Trainer =‘Rajiv Arora’ ;
 Result:
1 testing_01 Rajiv Arora 3

ORDER BY Clause
 ORDER BY clause is used to impose an order on the result of a query.
 It is used with SELECT stmt.
 One or more column can be specified in ORDER BY clause.
 Syntax:
SELECT <column_list>
FROM <table>
WHERE <condition>
ORDER BY<columns> [ASC|DESC] ;
GROUP BY Clause
 GROUP BY clause is used to divide the rows in a table into smaller groups.
 SQL groups the query after it retrieves the rows from a table.
 Conditional Retrieval of rows from a grouped result is possible with the
HAVING clause

Updating Table Rows

 UPDATE action query syntax
UPDATE tablename
SET column1 = new_value1, column2 = new_value2, …
WHERE search condition;

Deleting Table Rows

 The DELETE action query used removes specific or all rows
 Syntax:
DELETE FROM tablename
WHERE search condition;

DDL Queries
CREATE TABLE
tblCust
(
cid NUMERIC(10,0) PRIMARY KEY,
cname VARCHAR(10),
 email VARCHAR(20) UNIQUE);

TRUNCATE
 The TRUNCATE action query removes all rows and reset the identity
column.
 TRUNCATE TABLE tablename;

Drop Table
DROP TABLE tblName;
ALTER TABLE table_name ALTER COLUMN column_name column_type;

Sample queries
SELECT DEPT,COUNT(*) AS TOTAL_STD FROM TBL_Std
GROUP BY DEPT
SELECT * FROM TBL_STD ORDER BY SID DESC;
INSERT INTO TBL_Std(SNAME,AvgMarks) VALUES('Naveed',91);
UPDATE TBL_Std SET SPHONE='0738877878',DEPT='BCE'
WHERE SID=10
DELETE FROM TBL_Std WHERE DEPT IS NULL
CREATE TABLE TBL1
(
CID NUMERIC(10,0) PRIMARY KEY,
CNAME VARCHAR(20)
)
ALTER TABLE TBL1 ADD CPHONE VARCHAR(13)
DROP TABLE TBL1