1
1
Data
Data
: Data is collection of records.
: Data is collection of records.
Database
Database
: A database is a collection of data that represents important objects in a user's business.
: A database is a collection of data that represents important objects in a user's business.
Usage of database
Usage of database
: Database is being used in almost all possible aspects of business and life.
: Database is being used in almost all possible aspects of business and life.
1) S
1) S
ingle User
ingle User
: 2)
: 2)
Multiple Users
Multiple Users
: 3)
: 3)
Very Large Application
Very Large Application
: 4)

PROGRAM

2
2
1.
1.
It is self-describing
It is self-describing2.
2.
It is a collection of integrated records (Users Data, Meta Data, Indexes, Application
It is a collection of integrated records (Users Data, Meta Data, Indexes, Application
Metadata)
Metadata)
3. A Database is Model of Model
3. A Database is Model of Model
Therefore based on the characteristics of the Database we can formulate the definition as: A
Therefore based on the characteristics of the Database we can formulate the definition as: A
database is self-describing collection of integrated records.
database is self-describing collection of integrated records.
GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 1
Characteristics of Database Management System
Characteristics of Database

Relational Database: databases in which data is organized into tables.
Relational Database: databases in which data is organized into tables.GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 1
E.F.Codd formulated rules for Relational Database Manangement Systems which can be tablulated as below:
Information Representation All data must be represented logically in tables
Guaranteed logical accessibility All data is accessible logically using a combination of table name, key name, key value
and column
NULL support A Null value represents missing information and are not to be confused with empty, blank
or zero filled data. They also are not necessarily equal
Dynamic online catalog The definition of data is represented in the same manner as data so that it can
be relationally accessed. Example every database has catalog tables
Comprehensive data sub-language (HLL Like SQL ) A unique language which is supported along with
several other programming languages to achieve data definition, view and manipulation
Updatable views All views that can theoretically exists can be updated
High level insert, updates and delete Both base and derived relations can be handled as singular
requests and apply to retrieval, insert, update and deletion of data
Physical data independence Program and terminal activities are preserved when changes are made
in storage representation or access methods
Logical data independence Program and terminal activities are preserved when changes are made to base tables
Lintegrity independence Integrity constraints must be definable in the unique sub-language and stored in the catalog
Distribution independence e The unique sub-language must support database distribution while preserving program
and terminal activities
Nonsubersion A relational system which perform low-level processing of records cannot supplant the integrity rules in effect
in high level, record processing by a relational language
Relational Table database Defined as a rectangular array of columns and rows where columns are homogenous,
non-grouped, uniquely named data items( attributes ) and rows are not duplicated. It is also important to impose the property

ColdFusion,
XML
Single Database Application

5
5
GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 1
Components of Database
1. User Data:
StudentID Student Name StudentPhone InstructorName InstructorPhone
100 Adam 111-222-0987 Gary 111-222-3333
101 John 111-221-3457 Gary 111-222-3333
102 Jenny 111-220-9822 Tim 111-219-3267
103 Smantha 111-220-7644 Tim 111-219-3267
104 Stacy 111-222-3478 Gary 111-222-3333
Table StudentIns
StudentID Student Name StudentPhone InstructorName
100 Adam 111-222-0987 Gary
101 John 111-221-3457 Gary
102 Jenny 111-220-9822 Tim
103 Smantha 111-220-7644 Tim
104 Stacy 111-222-3478 Gary
Table Student
InstructorName InstructorPhone
Gary 111-222-3333 7
7
GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 1
Features of DBMS subsystem
OPERATING
SYSTEM
DBMS
ENGINE
DESIGN TOOLS:
•TABLE CREATION
•QUERY CREATION
•
REPORT CREATION
•
PROCEDURAL LANGUAGE
RUN-TIME FACILITIES:
•FORM PROCESSOR
•QUERY PROCESSOR
•
REPORT WRITE
•
PROCEDURAL RUN TIME
•Maximum cardinality of the relationship is inside the diamond
•
The name of the relationship is shown near the diamond
•
To show Minimum cardinality one can place a hash mark (|) across the relationship line to indicate that the entity
must exist in the relationship and place an oval across the relationship line to indicate that there may or may not be
an entity in the relationship

9
9
GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 2
Important Utilities of SQL Server
7.0
Creating database in SQL Server
The SQL Server Enterprise Manager
•The Create Database statement
•
The database creation Wizard
Create database Dbase_name
[ ON [PRIMARY]
( [ NAME = logical_file_name, ]
FILENAME = 'os_file_name'
[, SIZE = size]
[, MAXSIZE = { max_size | UNLIMITED } ]
[, FILEGROWTH = growth_increment] ) [, n]]

Total AS marks1+marks2
)
SQL Server 7.0

10
10
GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 2
SQL Server Data Types
I. Strings (char (n), varchar)- String contain character data made up of letters, numbers and symbols. In case of char (n),
data will always contain n bytes of information. If its less then n, it will be padded with spaces and if more then n, data will be
truncated.
Varchar (n) is used when one expects a wide variation in data size. Varchar option is good from memory standpoint but SQL
Server has to do overhead in determining the variable-length field.
II. Binary Data (binary (n), varbinary) - Binary data is stored as a series of 0s and 1s, which are represented on input and
output as a hexadecimal pairs. These hexadecimal pairs are made up of characters 0 through 9 and A through F
III. Integers (tinyint, smallint, int)- Of these three types of integer data type difference is in the amount of storage space
they require and range of values they can store
Data Type Length Range
TinyInt 1 0-255
SmallInt 2 +32,767
Int 4 +2,147,483,647
IV. Approximate and Exact Numeric Data Types
Approximate Numeric data types (float (n), real)- Floats and Reals are useful for scientific and statstical data where
absolute accuracy is not required, but where value reaches from extremely small to extremely large numbers. Floats can have
a precision from 1 to 38 digits.

Money +922,337,203,685,447.5808
Smallmoney +214,748,3647
VI. User- Defined Data Types- One can create user-defined database for specific database or one can place same in a
Model Database.
To create user-defined data Type, one must use system-provided data types.
For defining use-defined data type one needs to use sp_addtype system stored procedure
EXEC sp_addtype ssn, 'VARCHAR (11)', 'NOT NULL'
To drop user defined data type use:sp_droptype sid
User-Defined data types can be created and dropped using EM too.

12
12
GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 3
Normalization
Normalization is a process for converting a relation that has certain problems to two or more relations that do not have
problem. We can say that Normalization is used to check the desirability and correctness of relations. In Relational Model
Tables are termed as relations.
A relation is a 2-D table with each row holding data that pertain to some thing or a portion of some thing. Each column has
a data regarding an attribute. The term relational comes from the mathematical field of relational algebra. So, Relational models
are composed of relations, or two-dimensional tables, which follow the operations described in relational algebra.
For a table to be in a relation, it must follow these rules
•Cells of the table must be of single value
•
All entries of the column must be of same type
•

100 IT203 2220
101 1T161 1000
102 IT107 2000
103 IT121 1000
101 IT107 2000
103 IT203 2220
104 IT331 200
B
Relation: Courses1
StudentId CourseName
100 IT203
101 1T161
102 IT107
103 IT121
101 IT107
103 IT203
104 IT331
Relation: Student - Courses
CourseName Fee
IT203 2220
1T161 1000
IT107 2000
IT121 1000
IT107 2000
IT203 2220
IT331 200

Relation: Courses - Fee
Modification Anomalies:
delete Studen 104 from A or B, or insert another course IT176 to A or B

104 IT331 200
A
Relation: Courses

15
15
GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 3
Second Normal Form
Consider once again Relation Course1, now this relation also suffers from modification anomalies as discussed before. In this
relation one student can register for one or more courses. That means that StudentId itself only cannot be key. So we consider
(
StudentId, CourseName
), (
StudentId, Fee
) and (C
ourseName, Fee
). But since two courses can have same fee we can not
have (
CourseName, Fee
) and also (
StudentId, Fee
) can not determine uniqueness in row as student 100, for example, could
engage in two different activities both having same fee. Thus only (
StudentId, CourseName
) can uniquely determine each row.

102 CityHall 1200
103 CalmPlace 1500
104 UrbaneColony 1200
105 CityHall 1200
106 CityWoodsl 1000
C
Relation: HOUSING
In above relation StudentId is a key, and the functional dependencies
are:
StudentId BldgName
and BldgName Rent
StudentId BldgName
100 CityHall
101 CityWoods
102 CityHall
103 CalmPlace
104 UrbaneColony
105 CityHall
106 CityWoodsl
C1
Relation: Student-Bldg
BldgName Rent
CityHall 1200
CityWoods 1000
CityHall 1200
CalmPlace 1500
UrbaneColony 1200
CityHall 1200
CityWoodsl 1000
C2

FundID, InvestmentType
as the Primary Key
•
1NF for sure as relation is as per definition of 1NF
•
2NF because all of the non-key attributes (Manager) is dependant on all of the key
• 3NF because there are no transitive dependencies

18
18
GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 3
Consider what happens if we delete the tuple with FundID 22. We loose the fact that Brown manages the InvestmentType
"Growth Stocks." The following are steps to normalize a relation into BCNF:
•
List all of the determinants
•
See if each determinant can act as a key (candidate keys)
• For any determinant that is not a candidate key, create a new relation from the functional dependency. Retain
thedeterminant in the original relation
For our example:
Rorig (FundID, InvestmentType, Manager)
The determinants are:
•FundID, InvestmentType
•
FundID, Manager

19
GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 3
Fourth Normal Form
Id -> -> Major
Id -> ->Minor
This attribute dependency is called multi-value dependency.
•
Student can enroll in several different majors and several different minors
•
Major and Minor are independent of each other
• Only possible key is (id, major, minor)
Above relation is:
•
1NF for sure
• 2NF because there is no non-key attributes
•
3NF because there are no transitive dependencies
•
BCNF as it does not have any functional dependency, so this relation is in BCNF
Above relation also suffers from anomalies as:
• If we want to signup student 10 for minor Chemistry, then we can add row (10, Physics, Chemistry) but this will mean that
student can take minor Chemistry with Major Physics only and not with Math, which is wrong. So we will have to add row
(10, Math, Chemistry) too. This is an update anomaly as too much information needs to be updated for simple change in
the data
•
Also if a student drops a major, all the entries associated with major need to be dropped, this causes deletion anomaly
To make this relation 4NF we need to:
Create 2 relations each storing data only for one multi multi-value attribute.

2. Data Manipulation language (DML) - Allows one to retrieve or update data within a database. It is used for query, insertion,
and deletion and updating of information stored in databases. E.g.: Select, Insert, Update, Delete.
E.g.
1.Select * From Student where Student_nm = 'Dave'
2.Delete Student where Student_nm = 'James'
3.Update Student Set Address = '146, Fauntleroy Way Sw, Seattle- 98116' where Student_nm = 'Jim'
3. Data control language (DCL) -In RDBMS one of the main advantage is the security for the data in the database. You
can allow some user to do a specific operation or all operations on certain objects. Examples for DCL statements are
Grant, Revoke statements.
While Grant is used to grant permission to user, Revoke is used to revoke permission to user.
4. Indexes are referred as Physical key too. Physical Key signifies a column that has a special data structure defined to it in
order to improve performance. Whereas in Databases we often use term key for Logical key which identifies the unique column/s
in a database. One can choose any column or columns of the table to act as Physical key (index). A Logical key need not be a
Physical key, and a Physical key need not be a logical key.

21
21
GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 3
Defining the database structure to the DBMS:
There are various ways in which the structure of database can be described to the database management system.
Using a text file to describe a data structure as the one we define in Query Analyzer of the SQL Server which use DDL
Using a GUI based interface for defining the structure of database as in SQL Server Enterprise Manager.
DML Interface to the DBMS
There are multiple ways in which user can interface to the database. They can use form and report capability supplied by the
DBMS or can use query language or process application using application interface. All broad categories are discussed below:

Attributes from the corresponding columns must come from same domain
•
Duplicate rows must be eliminated

23
23
GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 4
Example:
Student (Sid, Name, City)
Sid Name City
1001 Dan Seattle
1002 Adam Redmond
1003 John Kirkland
3003 Jack Seattle
Relation: Student(Sid, Name, City)
Attribute Domain Format
Sid Identifier Integer (4)
Name PersonName Char (14)
City Residencecity Char (10)
Iid Name City
5001 Steve Seattle
5002 Neena Redmond
3003 Jack Seattle

Relation: Instructor(Iid, Name, City)

GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 4
Sid or Iid Name City
1001 Dan Seattle
1002 Adam Redmond
1003 John Kirkland
3003 Jack Seattle

A-B
Sid or Iid Name City
3003 Jack Seattle

Intersection

25
25
GIÁO TRÌNH CƠ SỞ DỮ LIỆU
Nguyễn Quang Tấn
Bài 4
5. Product
Product of two relations is the joining
of each row of one relation with each
row of another relation. The Product (AXB) of
Relation A having m rows with a Relation B
having n rows would be
m x n

A x B