Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

DBMS > aws > Flashcards

aws Flashcards

1
Q

DataBase

A

It’s an organized collection of structured data that can be stored, accessed and modified electronically

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

DBMS

A

The software which is used to manage database is called Database Management System (DBMS). * It is the software that interacts with end users, applications, and the database itself to capture and analyze the data. * DBMS provides an interface to perform various operations like database creation, storing data in it, updating data, creating a table in the database etc. * MySQL, Oracle etc. are popular commercial DBMS used in different applications.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

CHARACTERISTICS OF DBMS

A

Stores any kind of data

Concurrent use of the database

Supports ACID properties

Back up and recovery

Data integrity

Multiple Views

Security

Represents Complex Relationship Between Data

Query Language

Cost

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

DATABASE USERS

A

Database users are those people whose jobs require access to the database. They are categorized
based up on their interaction with the database.

Casual Users / Temporary Users :Casual Users are the users who occasionally use/access the database, but each time when they access the data base they require new information

Naive / Parametric End Users: Parametric End Users are unsophisticated who don’t have any DBMS knowledge but they frequently use the data base applications in their daily life to get the desired results.

Sophisticated Users : Sophisticated users can be engineers, scientists, business analyst, who are familiar with the database

Database Administrator (DBA): * Database Administrator is a person/team who defines the schema and also controls the 3 levels of database.
* DBA will then create a new account id and password for the user if he/she need to access the data base.
* DBA is also responsible for providing security to the data base and he allows only the authorized users to access/modify the data base.

Data Base Designers: Data Base Designers are the users who design the structure of data base which includes
tables, indexes, views, constraints, triggers, stored procedures.

.Application Program :Application Program are the back end programmers who writes the code for the application
programs.

System Analyst :* System Analyst is a user who analyzes the requirements of parametric end users.
* They check whether all the requirements of end users are satisfied.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

ADVANTAGES OF DBMS

A

Better Data Transferring:
Database management creates a place where users have an advantage of more and better managed
data. Thus making it possible for end-users to have a quick look and to respond fast to any changes
made in their environment

2.Better Data Security
As number of users increases data transferring or data sharing rate also increases thus increasing the risk of data security. DBMS provide a better platform for data privacy and security policies thus, improves Data Security.

  1. Better data integration:
    Due to DBMS, we have an access to well managed and synchronized form of data, thus it makes data handling very easy and gives integrated view of how a particular organization is working and also helps to keep a track on how one segment of the company affects other segment
  2. Minimized Data Inconsistency:
    Data inconsistency occurs between files when different versions of the same data appear
    in different places.
    So if a database is properly designed, then Data inconsistency can be greatly reduced
    hence minimizing data inconsistency.
  3. Faster data Access:
    DBMS helps to produce quick answers to database queries thus making data accessing faster and
    more accurate.
  4. Better decision making:
    Due to DBMS now we have Better managed data and Improved data accessing because of which we can generate better quality information. Hence, on this basis, better decisions can be made. Better Data quality improves accuracy, validity and time it takes to read data.\
  5. Increased end-user productivity:
    The data which is available with the help of combination of tools which transform data into useful information, helps end user to make quick, informative and better decisions that can make difference between success and failure in the global economy.
  6. Simple:
    DBMS gives simple and clear logical view of data. Many operations like insertion, deletion or creation of file or data are easy to implement
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

DATA MODELS

A

Data models in Database Management Systems (DBMS) are conceptual tools that help summarize the description of the database. They provide a transparent picture of the data, making it easier to create an actual database. Data models show how the data is designed and implemented, from the conceptual level to the physical level.

There are 3 types of data models

Conceptual Data Model:
The conceptual data model describes the database at a very high level and is useful to understand the needs or requirements of the database. It is this model, that is used in the requirement-gathering process eg: ER model

Logical Data Model
This type of data model is used to represent only the logical part of the database and does not represent the physical structure of the database. The representational data model allows us to focus primarily, on the design part of the database.

Physical Data Model
The physical Data Model is used to practically implement Relational Data Model. Ultimately, all data in a database is stored physically on a secondary storage device such as discs and tapes. This is stored in the form of files, records, and certain other data structures. It has all the information on the format in which the files are present and the structure of the databases,

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

DATABASE SCHEMA

A

DB Schema is the overall description of the DB.It is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data

It contains a descriptive detail of the database, which can be depicted by means of schema diagrams.

  • Physical Database Schema − It describes the database designed at physical level.This schema focusses on the actual storage of data and its form of storage like files, indices, etc.
    It defines how the data will be stored in a secondary storage.
  • Logical Database Schema − It describes the database designed at logical level. This schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints.
  • View schema – Defines the design of database at view level
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

DATABASE INSTANCE

A

The content of database at a point of time is called instance.The instances can be changed by certain
operations as like addition, deletion ,updation of data. It may be noted that any search query will not
make any kind of changes in the instances.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

DATABASE LANGUAGE

A
  • A DBMS has appropriate languages and interfaces to express database queries and updates.
  • Database languages can be used to read, store and update the data in the database.

a). DDL (Data Definition Language) : Deals with schemas and descriptions, of how the data
should reside in the database.

b).DML (Data Manipulation Language): Deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc. It is used to store, modify, retrieve, delete and update data in a database

c).DCL (Data control Language) : used to control access to data stored in a database
* GRANT: It is used to give user access privileges to a database.
* REVOKE: It is used to take back permissions from the user.

d).TCL (Transaction Control Language) :TCL is used to run the changes made by the DML statement.
* COMMIT: It is used to save the transaction on the database.
* ROLLBACK: It is used to restore the database to original since the last Commit.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

DATABASE STATES :

A

ONLINE OFFLINE RESTORING RECOVERING RECOVERY PENDING SUSPECT EMERGENCY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

THREE SCHEMA ARCHITECTURE

A

The three schema architecture is also called ANSI/SPARC architecture or three-level architecture.
* This framework is used to describe the structure of a specific database system.
* The three schema architecture is also used to separate the user applications and physical database.
* The three schema architecture contains three-levels. It breaks the database system down into three different categories.
Internal Schema
Conceptual Schema
External Schema

  • Mapping is used to transform the request and response between various database levels of
    architecture.
  1. Internal Level
    * The internal level has an internal schema which describes the physical storage structure of the database.
    * The internal schema is also known as a physical schema.
    * It uses the physical data model. It is used to define that how the data will be stored in a block.
  2. Conceptual Level
    The conceptual schema describes the design of a database at the conceptual level. Conceptual level
    is also known as logical level.
    * The conceptual schema describes the structure of the whole database.
    * The conceptual level describes what data are to be stored in the database and also describes
    what relationship exists among those data
  3. External Level
    At the external level, a database contains several schemas that sometimes called as subschema. The
    subschema is used to describe the different view of the database.
    * An external schema is also known as view schema.
    * Each view schema describes the database part that a particular user group is interested and
    hides the remaining database from that user group.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Objectives of Three schema Architecture :

A

The main objective of three level architecture is to enable multiple users to access the same data
with a personalized view while storing the underlying data only once
Thus it separates the user’s view from the physical structure of the database.
This separation is desirable for the following
reasons:

  • Different users need different views of the same data.
  • The approach in which a particular user needs to see the data may change over time
  • All users should be able to access the same data according to their requirements.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Mapping between Views(Three tier schema)

A

There must be mapping between the three levels . DBMS is responsible for the mapping.
There are basically two types of mapping in the database architecture:
1). Conceptual/ Internal Mapping :
The Conceptual/ Internal Mapping lies between the conceptual level and the internal level. Its role
is to define the correspondence between the records and fields of the conceptual level and files and
data structures of the internal level.

2). External/ Conceptual Mapping :
The external/Conceptual Mapping lies between the external level and the Conceptual level. Its role
is to define the correspondence between a particular external and the conceptual view.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Data Independence(3 tier schema)

A

1). Physical data independence :
It is the capacity to change the internal schema without having to change the conceptual schema. Hence, the external schemas need not be changed as well. Changes to the internal schema may be needed because some physical files were reorganized
2).Logical data independence :
It is the capacity to change the conceptual schema without having to change external schemas or application programs. We may change the conceptual schema to expand the database by adding a record type or data item), to change constraints, or to reduce the database .

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

DATABASE INTERFACES

A

Menu-Based Interfaces for Web Clients or Browsing – Forms-Based Interfaces –. Graphical User Interface –Natural language Interfaces –Speech Input and Output –
Interfaces for DBA –

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

ER MODEL(ENTITY RELATIONSHIP MODEL)

A
  • It is a high-level data model that is used to define the data elements and relationship for a specified system.
  • It develops a conceptual design for the database. It also develops a very simple and easy to design view of data.
  • In ER modeling, the database structure is portrayed as a diagram called an entity relationship diagram.
  1. Entity:An entity may be any object, class, person or place. In the ER diagram, an entity can be represented
    as rectangles
  2. Attribute: The attribute is used to describe the property of an entity. Eclipse is used to represent an attribute.
    a. Key Attribute
    The key attribute is used to represent the main characteristics of an entity. It represents a primary
    key. The key attribute is represented by an ellipse with the text underlined.

b. Composite Attribute
An attribute that composed of many other attributes is known as a composite attribute. The
composite attribute is represented by an ellipse, and those ellipses are connected with an ellipse

c. Multivalued Attribute
An attribute can have more than one value. These attributes are known as a multivalued attribute.
The double oval is used to represent multivalued attribute.

d. Derived Attribute
An attribute that can be derived from other attribute is known as a derived attribute. It can be
represented by a dashed ellipse.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Weak entity

A

An entity that depends on another entity called a weak entity. The weak entity doesn’t
contain any key attribute of its own. The weak entity is represented by a double rectangle

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Relationship

A

A relationship is used to describe the relation between entities. Diamond or rhombus is used to
represent the relationship.

CARDINALITY : It is expressed as the number of entities to which another entity can be
associated via a relationship set.

a. One-to-One Relationship
b. One-to-many relationship
c). Many-to-one relationship
d). Many-to-many relationship

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

DEGREE OF A RELATIONSHIP

A

The number of entity types which take part in the entity relationship is called the degree of relationships.

1).Unary relationship
It is the relationship between the instances of a single entity type. It is also called a recursive relationship.

2).Binary relationship It is the relationship between the instances of two different entity types.

3).Ternary relationship : A simultaneous relationship between the instances of three entity types with unique attributes

4).N-ary relationship

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

PARTICIPATION CONSTRAINTS

A

Participation of an entity can be either total or partial.

a).Total Participation − Each entity is involved in the relationship. Total participation is
represented by double lines.
b). Partial participation − Not all entities are involved in the relationship. Partial participation is
represented by single lines..

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

TYPES OF KEYS

A
  1. Primary Key :

2.Candidate key :The candidate keys refer to those attributes that identify rows uniquely in a table. In a table, we select the primary key from a candidate key.

  1. Alternate Key: keys that did not mkae it to be primary keys
  2. Super Key
    A super key refers to the set of all those keys that help us uniquely identify all the rows present in a
    table
  3. Foreign Key
    We use a foreign key to establish relationships between two available tables. Foreign keys are the
    column of a table used to point to the primary key of another table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

NAMING CONVENTIONS

A
  • Use an easily understandable and readable name.
  • Pick comprehension over length.short names are not always better.
  • Choose singular names for entity types, rather than plural ones, because the entity type name
    applies to each individual entity belonging to that entity type.
  • Entity type and relationship type names are uppercase letters.
  • Attribute names have their initial letter capitalized.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Relational Model(table)

A

Relational Model represents how data is stored in Relational Databases. A relational database stores
data in the form of relations (tables).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Degree(table)

A

no of attributes

25
Q

Cardinality(tables)

A

no of tuples

26
Q

CONSTRAINTS IN RELATIONAL MODEL

A

conditions which must hold for data present in
database called Constraints. These constraints are checked before performing any operation
(insertion, deletion and updation) in database. If there is a violation in any of constraints, operation
will fail

A). Domain Constraints:(where )
An attribute can only take values which lie inside the domain range. eg: If a constraint AGE>0 is applied on STUDENT relation, inserting negative value of AGE
will result in failure

B). Key Integrity:(primary key)
Every relation in the database should have atleast one set of attributes which defines a tuple
uniquely. Those set of attributes is called key. e.g: ROLL_NO in STUDENT is a key. No two
students can have same roll number. So a key has two properties:
* It should be unique for all tuples.
* It can’t have NULL values.

C). Referential Integrity:
When one attribute of a relation can only take values from other attribute of same relation or any
other relation, it is called referential integrity. Let us suppose we have 2 relations.

27
Q

INTRODUCTION OF RELATIONAL ALGEBRA IN DBMS

A

Relational Algebra is procedural query language, which takes Relation as input and generate relation as output. Relational algebra mainly provides theoretical foundation for relational databases and SQL.

28
Q

Operators in Relational Algebra

A

1). Projection (π)
Projection is used to project required column data from a relation.

2). Selection (σ)
Selection is used to select required tuples of the relations

σ (c>3)R
π (σ (c>3)R ) w

3). Union (U)(no dupes)
Union operation in relational algebra is same as union operation in set theory
Both relation must have same set of Attributes.
R u S

∏ CUSTOMER_NAME(BORROW) ∪ ∏ CUSTOMER_NAME(DEPOSITOR)

4).Intersection (∩)(dupes)
Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in
both R & S.

5). Rename (ρ)
Rename is a unary operation used for renaming attributes of a relation.
ρ (a/b)R will rename the attribute ‘b’ of relation by ‘a’.

6). Cross Product ( X ) ( each row in a with each row in b)
Cross product between two relations , lets say A and B, so cross product, A X B will results all the
attributes of A followed by each attribute of B. Each record of A will pairs with every record of B.

7).Natural Join ( ) ⋈(coomon attribute)
Natural join is a binary operator. Natural join between two or more relations will result set of all
combination of tuples where they have equal common attribute.

8).Conditional Join
Conditional join works similar to natural join. In natural join, by default condition is ‘equal’
between common attribute. While in conditional join, we can specify any condition such as greater
than, less than, not equal

9).Set Difference (-)
Set Difference in relational algebra is same set difference operation as in set theory.
Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in R
but not in S.

10). Divide ( ÷)
Division operator A÷B can be applied if and only if:
* Attributes of B is proper subset of Attributes of A.
* The relation returned by division operator will have attributes = (All attributes of A – All
Attributes of B)
* The relation returned by division operator will return those tuples from relation A which are
associated to every B’s tuple

29
Q

STRUCTURED QUERY LANGUAGE

A

SQL is a standard language for storing, manipulating and retrieving data in databases. SQL is used
to communicate with a database.

30
Q

SQL QUERIES

A

1).To create a database
CREATE DATABASE Database_name ;

2).To list the existing databases
SHOW DATABASES;

3).To use an already existing database
USE Database_name;
eg: USE COLLEGE;

4).To create a table
CREATE TABLE Table_name(column1 datatype, column2 datatype…., columnn datatype);
eg:CREATE TABLE Student(Name VARCHAR(20), Rollno INT, Dob DATE, Department VARCHAR(20));

5).To insert values to a table
INSERT INTO Table_name VALUES(Value1,Value2….Valuen);
eg:INSERT INTO Student VALUES(‘Asha’,1, ‘1995-07-01’, ‘CS’);

6).SELECT
a).To select certain columns of all the entries from table
SELECT Column_a, Column_b FROM Table_name;
eg: SELECT Rollno,Name FROM Student;

b).To select all the rows
SELECT * FROM Table_name

7).To select distinct elements of a column
SELECT DISTINCT Column_a FROM Table_name;
eg: SELECT DISTINCT Name FROM Student;

8).OR
To display the record of those students whose rollno is 1 or 2,
eg: SELECT *FROM Student WHERE Rollno=1 OR Rollno=2

9).AND
To display the record of those students whose rollno is 1 and name is Asha,
eg: SELECT * FROM Student WHERE Rollno=1 AND Name=’Asha’;

10).NOT
To display the records of those students whose name is not ‘akhil’,
eg: SELECT * FROM Student WHERE NOT Name=’Akhil’;

11).ALTER
a). To add a new column in a table
ALTER TABLE Table_name ADD Column_name datatype;
eg: ALTER TABLE Student ADD Email VARCHAR(20);
b).To delete a column from a table
ALTER TABLE Table_name DROP COLUMN Column_name;
eg: ALTER TABLE Student DROP COLUMN Email;
c).To change the datatype of a column in a table
ALTER TABLE Table_name ALTER COLUMN Column_name datatype;
eg: ALTER TABLE Student ALTER COLUMN Rollno VARCHAR(20);

12).DROP
To drop an existing table from db.This will result in loss of complete info stored in table and
schema.
DROP TABLE Table_name;
eg:DROP TABLE Student;

13).DELETE
To delete tuples from a table
a). DELETE FROM Table_name WHERE Condition;
eg: DELETE FROM Student WHERE Rollno=5;
b).To Delete entire records in table
DELETE FROM Table_name;

14). UPDATE
To modify the existing records in a table
UPDATE Table_name SET Column1=value1….Columnn=valuen WHERE Condition;
Eg: UPDATE Student SET Name=’Anjali’ WHERE Rollno=1;

15). IN
Same as OR
SELECT Column_names FROM Table_name WHERE Column_name IN
(Value1,Value2..);
eg: SELECT *FROM Student WHERE Rollno IN (1,3,5);

16). ORDER BY
To sort the result in asc/desc order. Ascending by default.
SELECT Column1,Column2,.. FROM Table_name ORDER BY
Column1,Column2…ASC/DESC;
eg: To sort the entire table according to the marks obtained in DBMS subject,
SELECT * FROM Student ORDER BY dbms_Marks;

22). LIKE
To search for a specific pattern in a column
SELECT Column_names FROM Table_name WHERE Columnn LIKE Pattern;
a). find any value starting with ‘a’
SELECT Rollno,Name FROM Student WHERE Name LIKE ‘a%’;
b). find any value that have ‘a’ in 2nd position
SELECT Rollno,Name FROM Student WHERE Name LIKE ‘_a%’;

23). BETWEEN :
To select values within a range(begin and end values are included).
SELECT Column_names FROM Table_names WHERE Column_name BETWEEN Value1
AND Value2;
eg : SELECT Name FROM Student WHERE Rollno BETWEEN 1 AND 5;

24). GROUP BY :
To group the rows that have same values into summary rows. It is often used along with aggregate
functions.
SELECT Columnnames FROM Table_name WHERE Condition GROUP BY
Columnnames ORDER BY Columnnames;
eg1: To find the number of customers in each country,
SELECT COUNT(CustomerId), Country FROM Customers GROUP BY Country;
eg2: To sort the country names according to the number of customers in each country ,
SELECT COUNT(CustomerId), Country FROM Customers GROUP BY Country ORDER
BY COUNT(CustomerId);

25). HAVING :
The HAVING clause was added to SQL because the WHERE keyword cannot be used with
aggregate functions.
SELECT column_name(s) FROM table_name WHERE condition
GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

31
Q

Aggregate functions

A

17). COUNT
To get the count of values in a column
a).SELECT COUNT(Columnn) FROM Table_name;
eg: SELECT COUNT(Rollno) FROM Student;
b).To get the number of rows that matches a condition
SELECT COUNT(Columnn) FROM Table_name WHERE Condition;

18). MIN()
To get the smallest value in a column
SELECT MIN(Column_name) FROM Table_name;

19).MAX() :
To get the largest value in a column
SELECT MAX(Column_name) FROM Table_name;

20). SUM() :
Returns the total of a numeric column
SELECT SUM(Column_name) FROM Table_name;

21). AVG( )
Returns the average of a numeric column.
SELECT SUM(Column_name) FROM Table_name;

32
Q

JOIN

A

Used to comblne rows from two or more tables based on a related column between them

a).INNER JOIN
SELECT column_name(s) FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;

b).LEFT JOIN
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records
from the right table (table2).
Syntax :
SELECT column_name(s) FROM table1 LEFT JOIN table2
ON table1.column_name = table2.column_name;

c).RIGHT JOIN
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching
records from the left table (table1). The result is 0 records from the left side, if there is no match.
Syntax :
SELECT column_name(s) FROM table1 RIGHT JOIN table2
ON table1.column_name = table2.column_name;

d)FULL JOIN
The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or
right (table2) table records.
Syntax:
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2
ON table1.column_name = table2.column_name WHERE condition;

33
Q

NESTED QUERIES

A

In nested queries, a query is written inside a query. The result of inner query is used in execution of
outer query.

SELECT column_names FROM tablenames
WHERE column_name OPERATOR
(SELECT column_name FROM tablename WHERE condition)

34
Q

VIEWS

A
  • In SQL, a view is a virtual table, based on the result-set of an SQL statement

A view contains rows and columns, just like a real table. The fields in a view are fields from
one or more real tables in the database.ie,We can create a view by selecting fields from one
or more tables present in the database. A View can either have all the rows of a table or
specific rows based on certain condition..A View can be created from a single table or
multiple tables.

single table
CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;

multiple tables
CREATE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS,
StudentMarks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

Drop the view :
We can delete or drop a View using the DROP statement.
Syntax:
DROP VIEW view_name;
* For example, if we want to delete the View MarksView, we can do this as:
DROP VIEW MarksView;

35
Q

Uses of a View

A
  1. Restricting data access –
    Views provide an additional level of table security by restricting access to a predetermined
    set of rows and columns of a table.
  2. Hiding data complexity –
    A view can hide the complexity that exists in a multiple table join.
  3. Simplify commands for the user –
    Views allows the user to select information from multiple tables without requiring the users
    to actually know how to perform a join.
  4. Store complex queries –
    Views can be used to store complex queries.
  5. Rename Columns –
    Views can also be used to rename the columns without affecting the base tables provided the
    number of columns in view must match the number of columns specified in select statement.
    Thus, renaming helps to to hide the names of the columns of the base tables.
  6. Multiple view facility –
    Different views can be created on the same table for different users.
36
Q

STORED PROCEDURE

A

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and
over again. So if you have an SQL query that you write over and over again, save it as a stored
procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so
that the stored procedure can act based on the parameter value(s) that is passed.

  • Instead of inserting them in the code of each application, they may be put in a stored
    procedure and reused.

Syntax
DELIMITER $$
CREATE PROCEDURE Procedure_name( Parameter_list )
BEGIN
Statements;
END $$
* To invoke the procedure
CALL Procedure_name(argument_list);
* To drop a procedure
DROP PROCEDURE Procedure_name;

eg

DELIMITER $$
CREATE PROCEDURE GetCustomers()
BEGIN
SELECT customerName,country
FROM customers
ORDER BY customerName;
END $$

CALL GetCustomers();

  • The first time you invoke a stored procedure, MySQL looks up for the name in the database
    catalog, compiles the stored procedure’s code, place it in a memory area known as a cache,
    and execute the stored procedure.

STORED PROCEDURES WITH SINGLE PARAMETER PASSING
To Create a procedure that find the number of students who comes from a particular place,
DELIMITER $$
CREATE PROCEDURE Findcount( IN Place VARCHAR(20))
BEGIN
SELECT COUNT(*) FROM STUDENT WHERE City=Place;
END $$
To invoke the procedure
CALL Findcount(‘kannur’);
$$

STORED PROCEDURES WITH MULTIPLE PARAMETER PASSING
To Create a procedure that find the number of students of a particular gender who comes from a
particular place,
DELIMITER $$
CREATE PROCEDURE Gettotal( IN Place VARCHAR(20), IN Gend VARCHAR(10))
BEGIN
SELECT COUNT(*) FROM STUDENT WHERE City=Place AND Gender=Gend;
END $$
To invoke the procedure,
CALL Gettotal(‘Kannur’,’female’);
$$

STORED PROCEDURES WITH IF-THEN-ELSE STATEMENT

DELIMITER $$
CREATE PROCEDURE Getstatus( IN Registerno INT, OUT Status VARCHAR(20))
BEGIN
DECLARE mark INT DEFAULT 0;
SELECT dbms INTO mark FROM STUDENT WHERE Regno=Registerno;
IF mark >20 THEN SET Status=’pass’;
else SET Status=’fail’;
END IF;
END $$

To invoke the procedure,
CALL Getstatus(9,@Status);
SELECT @Status;
$$

37
Q

Benefits of using a Stored Procedure in SQL

A

Stored procedures provide some crucial benefits, which are:
* Reusable: Multiple users and applications can easily use and reuse stored procedures by
merely calling it.
* Easy to modify: You can quickly change the statements in a stored procedure as and when
you want to, with the help of the ALTER TABLE command.
* Security: Stored procedures allow you to enhance the security of an application or a
database by restricting the users from direct access to the table.
* Low network traffic: The server only passes the procedure name instead of the whole query,
reducing network traffic.
* Increases performance: Upon the first use, a plan for the stored procedure is created and
stored in the buffer pool for quick execution for the next time.

38
Q

TRIGGER

A

A trigger is a stored procedure in database which automatically invokes whenever a special event in
the database occurs. For example, a trigger can be invoked when a row is inserted into a specified
table or when certain table columns are being updated.

Syntax:
create trigger [trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
[for each row]
[trigger_body]

create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
1. [before | after]: This specifies when the trigger will be executed.
2. {insert | update | delete}: This specifies the event (DML operation).
3. on [table_name]: This specifies the name of the table associated with the trigger.
4. [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for each
row being affected.
5. [trigger_body]: This provides the operation to be performed as trigger is fired.

BEFORE and AFTER of Trigger:
BEFORE triggers run the trigger action before the triggering statement is run.
AFTER triggers run the trigger action after the triggering statement is run.

example
create trigger stud_marks
before INSERT
on
Student
for each row
set new.total =new.subj1 + new.subj2 + new.subj3, new.average = new.total /3 ;

39
Q

EXCEPTIONS IN SQL

A
  • An exception is an error which disrupts the normal flow of program instructions.
  • An Exception is an error situation, which arises during program execution.
  • PL/SQL provides us the exception block which raises the exception thus helping the
    programmer to find out the fault and resolve it.
  • When an error occurs & exception is raised, normal execution is stopped and control
    transfers to exception handling part.

ACCESS_INTO_NULL It is raised when a NULL object is automatically assigned a value.
CASE_NOT_FOUND It is raised when none of the choices in the “WHEN” clauses of a
CASE statement is selected, and there is no else clause.
COLLECTION_IS_NULL It is raised when a program attempts to apply collection methods other
than exists to an uninitialized nested table or varray, or the program
attempts to assign values to the elements of an uninitialized nested
table or array.
DUP_VAL_ON_INDEX It is raised when duplicate values are attempted to be stored in a
column with unique index.
INVALID_CURSOR It is raised when attempts are made to make a cursor operation that is
not allowed, such as closing an unopened cursor.
INVALID_NUMBER It is raised when the conversion of a character string into a number
fails because the string does not represent a valid number.
LOGIN_DENIED It is raised when s program attempts to log on to the database with an
invalid username or password.
NO_DATA_FOUND It is raised when a select into statement returns no rows.
NOT_LOGGED_ON It is raised when a database call is issued without being connected to
the database.
PROGRAM_ERROR It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH It is raised when a cursor fetches value in a variable having
incompatible data type.
STORAGE_ERROR It is raised when PL/SQL ran out of memory or memory was
corrupted.
TOO_MANY_ROWS It is raised when a SELECT INTO statement returns more than one
row.
VALUE_ERROR It is raised when an arithmetic, conversion, truncation, or sizeconstraint error occurs.
ZERO_DIVIDE - It is raised when an attempt is made to divide a number by zero.

40
Q

Handling exeception

A

Eg 1: To handle an exception ‘Table not found’:
delimiter $$
create procedure test()
begin
declare exit handler for 1146
select ‘please create the table first’ as message;
select *from abc;
end $$

41
Q

ANOMALIES IN DATABASE DESIGN

A

There are different types of anomalies which can occur in referencing and referenced relation which can be discussed as:

a). Insertion anomaly: :If a tuple is inserted in referencing relation and referencing attribute
value is not present in referenced attribute

b). Deletion and Updation anomaly: If a tuple is deleted or updated from referenced relation and
referenced attribute value is used by referencing attribute in referencing relation, it will not allow
deleting the tuple from referenced relation.

42
Q

FUNCTIONAL DEPENDENCY

A

A functional dependency A->B in a relation holds if two tuples having same value of attribute A
also have same value for attribute B.

43
Q

Normalization

A

Redundancy in relation may cause insertion, deletion, and update anomalies. Normalization is the
process of minimizing redundancy from a relation or set of relations. Normal forms are used to
eliminate or reduce redundancy in database tables.

44
Q

Armstrongs axioms (F+)

A

Armstrong’s Axiom is a mathematical notation used to find the functional dependencies in a database.
Conceived by William W. Armstrong
It is a list of axioms or inference rules that can be implemented on any relational database.
It is denoted by the symbol F+

primary rules
axioms
Axiom of Reflexivity: If A is a set of attributes and B is a subset of A, then A holds B. If B⊆A then A→B. This property is trivial property.

Axiom of Augmentation: If A→B holds and Y is the attribute set, then AY→BY also holds. That is adding attributes to dependencies, does not change the basic dependencies. If A→B, then AC→BC for any C.

Axiom of Transitivity: Same as the transitive rule in algebra, if A→B holds and B→C holds, then A→C also holds. A→B is called A functionally which determines B. If X→Y and Y→Z, then X→Z.

SEcondary
Union: If A→B holds and A→C holds, then A→BC holds. If X→Y and X→Z then X→YZ.

Composition: If A→B and X→Y hold, then AX→BY holds.

Decomposition: If A→BC holds then A→B and A→C hold. If X→YZ then X→Y and X→Z.

Pseudo Transitivity: If A→B holds and BC→D holds, then AC→D holds. If X→Y and YZ→W then XZ→W.

Self Determination: It is similar to the Axiom of Reflexivity, i.e. A→A for any A.

Extensivity: Extensivity is a case of augmentation. If AC→A, and A→B, then AC→B. Similarly, AC→ABC and ABC→BC. This leads to AC→BC.

45
Q

CLOSURE OF FUNCTIONAL DEPENDENCY

A

The Closure Of Functional Dependency means the complete set of all possible attributes that can be functionally derived from given functional dependency
● If “F” is a functional dependency then closure of functional dependency can be denoted using “{F}+ ”.
● There are three steps to calculate closure of functional dependency

Step-1 : Add the attributes which are present on Left Hand Side in the original functional dependency.
Step-2 : Now, add the attributes present on the Right Hand Side of the functional dependency.
Step-3 : With the help of attributes present on Right Hand Side, check the other attributes that can be derived from the other given functional dependencies. Repeat this process until all the possible attributes which can be derived are added in the closure

46
Q
  1. First Normal Form –
A

A relation is in first normal form if every attribute in that relation is singled valued attribute

1 A c1, c2
2 E c3
3 M C2, c3
In the above table Course is a multi-valued attribute so it is not in 1NF.
Below Table is in 1NF as there is no multi-valued attribute
ID Name Course
——————
1 A c1
1 A c2
2 E c3
3 M c2
3 M c3

47
Q
  1. Second Normal Form –
A

To be in second normal form, a relation must be in first normal form and relation must not contain
any partial dependency. A relation is in 2NF if it has No Partial Dependency, i.e., no non-prime
attribute (attributes which are not part of any candidate key) is dependent on any proper subset of
any candidate key of the table.

Prime attribute : Attributes which are a part of candidate key.
* Non prime attribute :Attributes which are not a part of candidate key.
* Partial Dependency : If the proper subset of candidate key determines non-prime attribute, it
is called partial dependency.

48
Q

3.THIRD NORMAL FORM

A

Definition 1: A relational schema R is said to be in 3NF, First, it should be in 2NF and, no nonprime attribute should be transitively dependent on the Key of the table
If X → Y and Y → Z exist then X → Z also exists which is a transitive dependency, and it should
not hold.
Definition 2: First it should be in 2NF and if there exists a non-trivial dependency between two sets
of attributes X and Y such that X → Y (i.e., Y is not a subset of X) then
either X is Super Key OR Y is a prime attribute.

49
Q

4.BCNF(Boyce Codd Normal Form)

A
  • BCNF is the advanced version of 3NF.
  • A table is in BCNF if every functional dependency X->Y, X is the super key of the table.
  • ie, for BCNF, the table should be in 3NF, and for every FD, LHS is super key.
50
Q

LOSSLESS JOIN DECOMPOSITION

A

Lossless-join decomposition is a process in which a relation is decomposed into two
or more relations. This property guarantees that the extra or less tuple generation
problem does not occur and no information is lost from the original relation during
the decomposition. It is also known as non-additive join decomposition.
* When the sub relations combine again then the new relation must be the same as the
original relation was before decomposition.

51
Q

DEPENDENCY PRESERVING DECOMPOSITION

A
  • If a relation R is decomposed into relation R1 and R2, then the dependencies of R,
    either must be a part of R1 or R2 or must be derivable from the combination of
    functional dependencies of R1 and R2.
  • Ie, (F1 F2 … F ∪ ∪ ∪ n)+ = F+
  • Eg: Suppose there is a relation R (A, B, C, D) with functional dependency set (A->BC).
    The relational R is decomposed into R1(ABC) and R2(AD) which is dependency preserving
    because FD A->BC is a part of relation R1(ABC).
52
Q

Transactions in DBMS

A

 Transactions are a set of operations used to perform a logical set of work.
 A transaction is an action or series of actions.
 It is performed by a single user to perform operations for accessing the contents of the
database

53
Q

Operations of Transaction:

A

Transactions access data using read and write operations.
Read(X): Read operation is used to read the value of X from the database and stores it in a buffer in
main memory.
Write(X): Write operation is used to write the value back to the database from the buffer

it may be possible that because of the failure of hardware, software or power, etc. that
transaction may fail before finished all the operations in the set.

To solve this problem, we have two important operations:
1.Commit: It is used to save the work done permanently.ie. After all instructions of a transaction
are successfully executed,the changes made by transaction are made permanent in the database.
2.Rollback: It is used to undo the work done.ie, if a transaction is not able to execute all operations
successfully, all changes made by that transaction is undone.

54
Q

ACID Properties of a transaction

A

In order to maintain consistency in a database, before and after the transaction, certain
properties are followed. These are called ACID properties.

1). Atomicity :By this, we mean that either the entire transaction takes place at once or doesn’t happen at all. There Is no midway i.e. transactions do not occur partially. Each transaction is considered as one unit and either runs to completion or is not executed at all
It involves the following two operations
(a) Abort : If a transaction aborts, changes made to the database are not visible.
(b) Commit : If a transaction commits, changes made are visible.

2). Consistency:
This means that integrity constraints must be maintained so that the database is consistent before
and after the transaction. It refers to the correctness of a database.

3). Isolation:
This property ensures that multiple transactions can occur concurrently without leading to the
inconsistency of the database state. Transactions occur independently without interference. Changes
occurring in a particular transaction will not be visible to any other transaction until that particular
change in that transaction is written to memory or has been committed.

4). Durability:
This property ensures that once the transaction has completed execution, the updates and
modifications to the database are stored in and written to disk and they persist even if a system
failure occurs. These updates now become permanent and are stored in non-volatile memory. The
effects of the transaction, thus, are never lost.

Property Responsibility for maintaining
properties
Atomicity - Transaction Manager
Consistency - Application programmer
Isolation - Concurrency Control Manager
Durability - Recovery Manager

The ACID properties provide a mechanism to ensure the correctness and consistency of a database
in a way such that each transaction is a group of operations that acts as a single unit, produces
consistent results, acts in isolation from other operations, and updates that it makes are durably
stored.
***

55
Q

Transaction States in DBMS

A

States through which transaction goes during its lifetime. These are the states which tell about the
current state of the Transaction and also tell how we will further do the processing in the
transactions

These are different types of Transaction States :
1. Active State–
When the instructions of the transaction are running then the transaction is in active state. If
all the ‘read and write’ operations are performed without any error then it goes to the
“partially committed state”; if any instruction fails, it goes to the “failed state”.

  1. Partially Committed–
    After completion of all the read and write operation the changes are made in main memory
    or local buffer. If the the changes are made permanent on the Data Base then the state will
    change to “committed state” and in case of failure it will go to the “failed state”.
  2. Failed State –
    When any instruction of the transaction fails, it goes to the “failed state” or if failure occurs
    in making a permanent change of data on Data Base.
  3. Aborted State –
    After having any type of failure the transaction goes from “failed state” to “aborted state”
    and since in previous states, the changes are only made to local buffer or main memory and
    hence these changes are deleted or rolled-back.
  4. Committed State –
    It is the state when the changes are made permanent on the Data Base and the transaction is
    complete and therefore terminated in the “terminated state”.
  5. Terminated State –
    If there isn’t any roll-back or the transaction comes from the “committed state”, then the
    system is consistent and ready for new transaction and the old transaction is terminated.
55
Q

Types of Schedules based on Recoverability

A

1). Recoverable Schedules:
Schedules in which transactions commit only after all transactions whose changes they read commit
are called recoverable schedules. In other words, if some transaction Tj is reading value updated or
written by some other transaction Ti, then the commit of Tj must occur after the commit of Ti.

a). Cascading Schedule
A cascading schedule is classified as a recoverable schedule. A cascading rollback is a type of
rollback in which if one transaction fails, then it will cause rollback of other dependent transactions.
The main disadvantage of cascading rollback is that it can cause CPU time wastage.

b). Cascadeless schedule
When a transaction is not allowed to read data until the last transaction which has written it is
committed or aborted, these types of schedules are called cascadeless schedules

c). Strict schedule

2). Non Recoverable Schedule

If a transaction does a dirty read operation from an uncommitted transaction and commits before the
transaction from where it has read the value, then such a schedule is called an irrecoverable
schedule

56
Q

NO SQL DATABASES

A
  • A NoSQL (originally referring to “non-SQL” or “non-relational”) provides a mechanism for
    storage and retrieval of data that is modeled in means other than the tabular relations used in
    relational databases.
  • NoSQL databases are increasingly used in big data and real time web applications.
  • Sometimes the data structures used by NoSQL databases are also viewed as “more flexible”
    than relational database tables.

The data structures used by NoSQL databases are different from those used by default in relational databases, making some
operations faster in NoSQL.

57
Q

NoSQL database features

A

Flexible schemas
* Stores unstructured, semi structured or structured data
* Horizontal scaling
* Handles large volume of data at high speed.
* Fast queries due to the data model
* Developer friendly as it is easy to update schema and fields
* Applicable in real time streaming

58
Q

TYPES OF NOSQL

A

graph docs key value wide column

. D ocument database
* A document database stores data in JSON, BSON , or XML documents (not Word
documents or Google docs). In a document database, documents can be nested. Particular
elements can be indexed for faster querying.
* Documents can be stored and retrieved in a form that is much closer to the data objects used
in applications, which means less translation is required to use the data in an application.

Graph database
* A graph database focuses on the relationship between data elements. Each element is stored
as a node (such as a person in a social media graph). The connections between elements are
called links or relationships.
* A graph database is optimized to capture and search the connections between data elements,
overcoming the overhead associated with JOINing multiple tables in SQL

Key-Value Stores
* The simplest type of NoSQL database is a key-value store .
* Every data element in the database is stored as a key value pair consisting of an attribute
name (or “key”) and a value.

Wide column store

  • While a relational database stores data in rows and reads data row by row, a column store is
    organized as a set of columns.
  • This means that when you want to run analytics on a small number of columns, you can read
    those columns directly without consuming memory with the unwanted data.

DBMS (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions