Class 12 Date: 8.1.
26, Thursday
7. Relational Database and SQL
1. Database:
a. It is place where related information are stored
b. Organized collection of data (Information) about a particular thing
c. It can be integrated and shared
d. Various operations can be formed on it
e. It is the highest unit of file organization
2. Data/character/Information
3. Field
4. Record
5. File/Table/Relation
6. DBMS: Data Base Management System
a. It is a system software
b. Components
i. Users
1. Database Administrators
2. Application developers
3. End users
ii. Database creation: Create a database and store data
iii. Database Maintenance: Edit, manipulate and control a database
iv. Database Processing: Query the database
v. Database Application: Personal, Departmental, Enterprise and Internal
vi. DBMS: MySQL, Oracle
vii. Database: Collection of Tables
c. It is used to
i. Create a database
ii. Store data
iii. Query the database
iv. Manipulation: Edit, manipulate
v. Validate the data
vi. Check for inconsistencies
vii. Consolidated reports: Either on screen or print outs
7. Advantages of DBMS
a. Elimination of Data Redundancy: Avoid duplication of data
b. Data consistency: Changes made in one place, reflects in all other places
c. Sharing of Data: Both existing and new applications can share the data
d. Reduced programming effort: Query
e. Database Enforces Standards: DBA- Database Administrator ensures the following
i. Standardization
ii. Format
iii. Documentation standards
iv. Conventions
f. Improved Data Integrity: Ensures that correct information has been entered by the
user. It consists of various constraints
g. Privacy and Security: Protection of data against loss or access by the unauthorized
persons
h. Economical: Combining more than one organization data in one database
i. Improved Backup and Recovery System: Facility for recovery from hardware and
software failures
j. Meeting Enterprise Requirements than Individual Requirements: Technically strong
knowledge users can use a database. Hence it should it provide variety of user
interface. Interface means – Communication mode or interaction mode
i. GUI: Graphical User Interface
ii. CLI: Command Line Interface
iii. Forms-Based Interface: Library Management System, Online bank account
application
iv. API: Application Programming Interface – Java / Java Database Connectivity
(JDBC)
v. Natural Language and Speech Interface: Smart device
vi. Menu-based Interface: Web based application
vii. Interface for Parametric users: ATM Machine
Database Keys:
i. It is used to identify a table on the basis of attributes
ii. It is used to identify the relation among two or more tables
iii. Uniquely identifies a record
Types
a. Primary key: Uniquely identifies a record
b. Candidate key: It is capable of becoming a primary key but not a primary key
c. Alternate key: A candidate key which is not set as a primary key
d. Foreign key: A non key attribute of one table which has been set as primary key in another
table
Example 1:
Table Name: Emp_V
E_No E_Id E_Name Designation
E1 S1 Felix Supervisor
E2 S2 Sharon Clerk
Note:
i. E_No is set as the Primary key
ii. Candidate keys in Emp_V: E_No and E_id
iii. Alternate key in Emp_V: since E_No is set as primary key, E_id is the alternate key
Example 2:
Table Name: Emp_P
E_Id E_OT E_Salary
S1 500 9000
S2 200 6000
Parent Table
Table Name: Emp_V
E_No E_Id E_Name Designation
E1 S1 Felix Supervisor
E2 S2 Sharon Clerk
E3 S3 Ram HOD
Child Table
Note: Not Permitted
i. E_Id in table Emp_P is set as the primary key
ii. E_No in table Emp_V is set as the primary key
iii. Parent Table: Emp_P is the parent table (has the primary key Emp_Id being referenced).
iv. Child Table: Emp_V is the child table (has the foreign key Emp_Id referencing Emp_P).
v. Emp_Id in Emp_V Table is the foreign key.
vi. In the child table, insertion of third record is not permitted since an attribute, E_Id
(Foreign key) is set as primary key in parent table. After inserting S3 data in E_id of
Parent table, we can insert record related to S3 (E_ID) of child table.
vii. Deletion of records from parent table is not permitted if the corresponding records
already exist in the child table. To do this, we have to delete the corresponding records
from child table and then delete it from the parent table
viii. Reason for point vi and vii: To ensure Referential integrity. It is a constraint in the
database that enforces the relationship between the tables.
Example 2:
Table Name: Emp_P
E_Id E_OT E_Salary
S1 500 9000
S2 200 6000
Parent Table
Table Name: Emp_V
E_No E_Id E_Name Designation
E1 S1 Felix Supervisor
E2 S2 Sharon Clerk
Child Table
Note:
a. To delete record related to an attribute S1 from parent table, first we have delete a record
related to an attribute S1 from child table.
Data Types
1. Numeric
a. Integer
b. Float
c. Numeric(x,y)
d. Decimal(x,y)
2. Non-Numeric or String
a. Char or char(x)
b. Varchar(x)
3. Date
a. Format: YYYY/MM/DD or YYYY-MM-DD
4. Time
a. Format: HH:MM:SS
Integer
1. Syntax:
integer or INTEGER or int or INT
2. It stores positive or negative whole numbers
3. 4 bytes
Float
1. Syntax:
float or FLOAT
2. It stores positive or negative decimal numbers
3. 4 bytes
Numeric
1. Syntax:
Numeric(x,y)
2. Example:
Numeric(4,2)
3. x–y=4–2 = 2 (1 for integer part, 1 for decimal point)
4. y=2 (after decimal point 2 places)
Decimal
1. Syntax:
Decimal(x,y)
2. Example:
Decimal(4,2)
3. x–y=4–2 = 2 (2 for integer part, excluding decimal point)
4. y=2 (after decimal point 2 places)
Character: Fixed Length
1. Syntax:
char or char(x)
or
CHAR or CHAR(x)
2. Each character: 1 byte
3. Enclosed in single or double quotes
4. Stores 0 to 255 characters
5. Example:
Name char(10);
Name=’Ram’
6. Name field occupies 10 bytes, with the first three bytes with values and the rest with blank
data
Character: Variable Length
1. Syntax:
varchar(x)
or
VARCHAR(x)
2. Each character: 1 byte
3. Enclosed in single or double quotes
4. Stores up to 65,535
5. Example:
Name varchar(10);
Name=’Ram’
6. Name field occupies 5 bytes, with the first three bytes with values and the next 2 bytes for
variable length information
Date
1. Syntax:
YYYY/MM/DD
or
YYYY-MM-DD
2. Enclosed in single quotes
Time
1. Syntax:
HH:MM:SS
Constraints in SQL
1. Primary key: Uniquely identifies a record
2. NOT NULL: Data must be entered. The field should not left blank or NULL
3. UNIQUE: It ensures that the filed does not contain duplicate values
e. Foreign key: A non key attribute of one table which has been set as primary key in another
table
SQL Commands
1. Keyword: It has specific meaning in SQL
Example: Select, From, Update
2. Clause: It is a logical part of an SQL statement
Example: Where, Group by, Having
3. Command: It is a combination of keywords or/and clauses
Example:
SELECT * FROM Employee;
SELECT e_no, e_name FROM Employee WHERE salary>=45000;
4. DDL Commands – Data Definition Language
a. It provides the storage structure and access methods
b. It defines the relevant data types
c. Create
i. It is used to create a Database and Table
d. Use
i. To open an existing database
e. Show
i. To show all the existing database
ii. To show all the existing tables in a database
f. Alter: Modifies the structure of the table
i. To add column/constraint
ii. To modify
iii. To drop column/constraint
iv. To change
g. Drop
i. Deletes a Database and Table
5.