Relational Database Management system

what is Cardinality or binary relationship? explain in details with example and diagram

2020-05-22T20:06:00.001+05:30

Cardinality/Binary relationship

It defines the numerical attributes of the relationship between two entites or entity sets.

One to one relationship : -

When a single instance of an entity is associated with a single instance of another entity then it is called one to one relationship.

For example : - a person has only one passport and a passport is given to one person.

One to many relationship : -

When a single instance of an entity is associated with more than one instance of another entity then it is called one to many relationship.

For example: -

One customer can placed many orders but an order can’t be placed by many customers.

Many to One relationship: -

When more than one instance of an entity is associated with single instance of another entity then it is called many to one relationship.

For example: -

Many students can study in a single college but a student in many colleges at same time.

Many to many relationship: -

When more than one instance is associated with more than instance of another entity then it is called many to many relationship.

For example: -

More than one employee assigned to many project and many project can be assigned to many employee.

What is FILES of records in DBMS? explain in details. Also explain operation on files

2020-05-18T21:12:00.001+05:30

FILES OF RECORDS

l A file is a sequence of records, where each record is a collection of data values (or data items).

l A file descriptor (or file header ) includes information that describes the file, such as the field names and their data types, and the addresses of the file blocks on disk.

l Records are stored on disk blocks. The blocking factor bfr for a file is the (average) number of file records stored in a disk block.

l A file can have fixed-length records or variable-length records.

Operation on Files

Typical file operations include:

l OPEN: Readies the file for access, and associates a pointer that will refer to a current file record at each point in time.

l FIND: Searches for the first file record that satisfies a certain condition, and makes it the current file record.

l FINDNEXT: Searches for the next file record (from the current record) that satisfies a certain condition, and makes it the current file record.

l READ: Reads the current file record into a program variable.

l INSERT: Inserts a new record into the file, and makes it the current file record.

l DELETE: Removes the current file record from the file, usually by marking the record to indicate that it is no longer valid.

l MODIFY: Changes the values of some fields of the current file record.

l CLOSE: Terminates access to the file.

l REORGANIZE: Reorganizes the file records. For example, the records marked deleted are physically removed from the file or a new organization of the file records is created.

l READ_ORDERED: Read the file blocks in order of a specific field of the file.

Unordered Files

l Also called a heap or a pile file.

l New records are inserted at the end of the file.

l To search for a record, a linear search through the file records is necessary. This requires reading and searching half the file blocks on the average, and is hence quite expensive.

l Record insertion is quite efficient.

l Reading the records in order of a particular field requires sorting the file records.

Ordered Files

l Also called a sequential file.

l File records are kept sorted by the values of an ordering field.

l Insertion is expensive: records must be inserted in the correct order. It is common to keep a separate unordered overflow (or transaction ) file for new records to improve insertion efficiency; this is periodically merged with the main ordered file.

l A binary search can be used to search for a record on its ordering field value. This requires reading and searching log₂ of the file blocks on the average, an improvement over linear search.

l Reading the records in order of the ordering field is quite efficient.

Figure 1 Ordered Files

Hashed files Hashing for disk files is called External Hashing. To continue read about HASHING or HASHED FILES visit my blogger site https://myrdbmsnotes.blogspot.com/2020/05/what-is-hashed-files-in-dbms-explain.html

What is Hashed files in DBMS? explain with proper diagram

2020-05-18T20:59:00.000+05:30

Hashed Files

l Hashing for disk files is called External Hashing

l The file blocks are divided into M equal-sized buckets, numbered bucket₀, bucket₁, ...,

bucket _M-1. Typically, a bucket corresponds to one (or a fixed number of) disk block.

l One of the file fields is designated to be the hash key of the file.

l The record with hash key value K is stored in bucket i, where i=h(K), and h is the hashing function.

l Search is very efficient on the hash key.

l Collisions occur when a new record hashes to a bucket that is already full. An overflow file is kept for storing such records. Overflow records that hash to each bucket can be linked together.

There are numerous methods for collision resolution, including the following:

l Open addressing: Proceeding from the occupied position specified by the hash address, the program checks the subsequent positions in order until an unused (empty) position is found.

l Chaining: For this method, various overflow locations are kept, usually by extending the array with a number of overflow positions. In addition, a pointer field is added to each record location. A collision is resolved by placing the new record in an unused overflow location and setting the pointer of the occupied hash address location to the address of that overflow location.

l Multiple hashing: The program applies a second hash function if the first results in a collision. If another collision results, the program uses open addressing or applies a third hash function and then uses open addressing if necessary.

Figure 1 Hashed Files

l To reduce overflow records, a hash file is typically kept 70-80% full.

l The hash function h should distribute the records uniformly among the buckets; otherwise, search time will be increased because many overflow records will exist.

l Main disadvantages of static external hashing:

- Fixed number of buckets M is a problem if the number of records in the file grows or shrinks.

- Ordered access on the hash key is quite inefficient (requires sorting the records).

Figure 2 Hashed Files - Overflow handling

Dynamic And Extendible Hashed Files

Dynamic and Extendible Hashing Techniques

l Hashing techniques are adapted to allow the dynamic growth and shrinking of the number of file records.

l These techniques include the following: dynamic hashing , extendible hashing , and linear hashing .

l Both dynamic and extendible hashing use the binary representation of the hash value h(K) in order to access a directory. In dynamic hashing the directory is a binary tree. In extendible hashing the directory is an array of size 2^d where d is called the global depth.

l The directories can be stored on disk, and they expand or shrink dynamically. Directory entries point to the disk blocks that contain the stored records.

l An insertion in a disk block that is full causes the block to split into two blocks and the records are redistributed among the two blocks. The directory is updated appropriately.

l Dynamic and extendible hashing do not require an overflow area.

l Linear hashing does require an overflow area but does not use a directory. Blocks are split in linear order as the file expands.

Figure 3 Extendible Hashing

In DBMS-What is Timestamp based concurrency control algorithm? explain Multiversion technique using timestamp ordering

2020-05-14T19:29:00.000+05:30

Timestamp based concurrency control algorithm

Timestamp : - A monotonically increasing variable (integer) indicating the age of an operation or a transaction. A larger timestamp value indicates a more recent event or operation.

Timestamp based algorithm uses timestamp to serialize the execution of concurrent transactions

Basic Timestamp Ordering

1. Transaction T issues a write_item(X) operation:

If read_TS(X) > TS(T) or if write_TS(X) > TS(T), then an younger transaction has already read the data item so abort and roll-back T and reject the operation.
If the condition in part (a) does not exist, then execute write_item(X) of T and set write_TS(X) to TS(T).

2. Transaction T issues a read_item(X) operation:

If write_TS(X) > TS(T), then an younger transaction has already written to the data item so abort and roll-back T and reject the operation.

If write_TS(X) £ TS(T), then execute read_item(X) of T and set read_TS(X) to the larger of TS(T) and the current read_TS(X

Strict Timestamp Ordering

1. Transaction T issues a write_item(X) operation:

a. If TS(T) > read_TS(X), then delay T until the transaction T’ that wrote or read X has terminated (committed or aborted).

2. Transaction T issues a read_item(X) operation:

a. If TS(T) > write_TS(X), then delay T until the transaction T’ that wrote or read X has terminated (committed or aborted).

Thomas’s Write Rule

1.If read_TS(X) > TS(T) then abort and roll-back T and reject the operation.

2.If write_TS(X) > TS(T), then just ignore the write operation and continue execution. This is because the most recent writes counts in case of two consecutive writes.

3.If the conditions given in 1 and 2 above do not occur, then execute write_item(X) of T and set write_TS(X) to TS(T).

Multiversion concurrency control techniques

Concept

This approach maintains a number of versions of a data item and allocates the right version to a read operation of a transaction. Thus unlike other mechanisms a read operation in this mechanism is never rejected.

Side effect: Significantly more storage (RAM and disk) is required to maintain multiple versions. To check unlimited growth of versions, a garbage collection is run when some criteria is satisfied.

Multiversion technique based on timestamp ordering

Side effects: Significantly more storage (RAM and disk) is required to maintain multiple versions. To check unlimited growth of versions, a garbage collection is run when some criteria is satisfied.

Explanation

Assume X1, X2, …, Xn are the version of a data item X created by a write operation of transactions. With each Xi a read_TS (read timestamp) and a write_TS (write timestamp) are associated.

read_TS(Xi): The read timestamp of Xi is the largest of all the timestamps of transactions that have successfully read version Xi.

write_TS(Xi): The write timestamp of Xi that wrote the value of version Xi.

A new version of Xi is created only by a write operation.

To ensure serializability, the following two rules are used.

If transaction T issues write_item (X) and version i of X has the highest write_TS(Xi) of all versions of X that is also less than or equal to TS(T), and read _TS(Xi) > TS(T), then abort and roll-back T; otherwise create a new version Xi and read_TS(X) = write_TS(Xj) = TS(T).

If transaction T issues read_item (X), find the version i of X that has the highest write_TS(Xi) of all versions of X that is also less than or equal to TS(T), then return the value of Xi to T, and set the value of read _TS(Xi) to the largest of TS(T) and the current read_TS(Xi).

What is Normalization in DBMS & its form? explain in details with example

2020-05-11T21:08:00.001+05:30

Normalization

Normalization is a process that “improves” a database design by generating relations that are of higher normal forms.

The objective of normalization: “to create relations where every dependency is on the key, the whole key, and nothing but the key”.

We discuss four normal forms: first, second, third, and Boyce-Codd normal forms

1NF, 2NF, 3NF, and BCNF

There is a sequence to normal forms:

1NF is considered the weakest,

2NF is stronger than 1NF,

3NF is stronger than 2NF, and

BCNF is considered the strongest

Also,

any relation that is in BCNF, is in 3NF;

any relation in 3NF is in 2NF; and

any relation in 2NF is in 1NF.

a relation in BCNF, is also in 3NF

a relation in 3NF is also in 2NF

a relation in 2NF is also in 1NF

We consider a relation in BCNF to be fully normalized.

The benefit of higher normal forms is that update semantics for the affected data are simplified.

This means that applications required to maintain the database are simpler.

A design that has a lower normal form than another design has more redundancy. Uncontrolled redundancy can lead to data integrity problems.

First we introduce the concept of functional dependency

Functional Dependencies

We say an attribute, B, has a functional dependency on another attribute, A, if for any two records, which have

the same value for A, then the values for B in these two records must be the same. We illustrate this as:

A à B

Example: Suppose we keep track of employee email addresses, and we only track one email address for each employee. Suppose each employee is identified by their unique employee number. We say there is a functional dependency of email address on employee number:

employee number à email address

If EmpNum is the PK then the FDs:

EmpNum à EmpEmail

EmpNum à EmpFname

EmpNum à EmpLname

must exist.

EmpNum à EmpEmail

EmpNum à EmpFname

EmpNum à EmpLname

First Normal Form

We say a relation is in 1NF if all values stored in the relation are single-valued and atomic.

1NF places restrictions on the structure of relations. Values must be simple.

The following in not in 1NF

EmpNum	Empphone	EmpDegrees
123	250-7853
233	250-1325	BA,BSC,PhD
976	250-2311	BSc,MSc

EmpDegrees is a multi-valued field:

employee 976has two degrees: BSc and MSc

employee 233 has three degrees: BA, BSc, PhD

EmpNum	EmpPhone	EmpDegrees
123	250-7853
233	250-1325	BA,BSC,PhD
976	250-2311	BSc,MSc

To obtain 1NF relations we must, without loss of information, replace the above with two relations are given below

Boyce-Codd Normal Form

BCNF is defined very simply:

a relation is in BCNF if it is in 1NF and if every determinant is a candidate key.

If our database will be used for OLTP (on line transaction processing), then BCNF is our target. Usually, we meet this objective. However, we might denormalize (3NF, 2NF, or 1NF) for performance reasons.

Second Normal Form

A relation is in 2NF if it is in 1NF, and every non-key attribute is fully dependent on each candidate key. (That is, we don’t have any partial functional dependency.)

• 2NF (and 3NF) both involve the concepts of key and non-key attributes.

• A key attribute is any attribute that is part of a key; any attribute that is not a key attribute,

is a non-key attribute.

• Relations that are not in BCNF have data redundancies

• A relation in 2NF will not have any partial dependencies

Consider this InvLine table (in 1NF):

InvNum

LineNum

ProdNum

Qty

InvDate

There are two

candidate keys.

Qty is the only non-key

attribute, and it is

dependent on InvNum

InvNum → InvDate

Since there is a determinant that is not a candidate key, InvLine is not BCNF

InvLine is not 2NF since there is a partial dependency of InvDate on InvNum

InvLine

InvNum

LineNum

ProdNum

Qty

InvDate

The above relation has redundancies: the invoice date is repeated on each invoice line.

We can improve the database by decomposing the relation into two relations:

Third Normal Form

• A relation is in 3NF if the relation is in 1NF and all determinants of non-key attributes are candidate keys That is, for any functional dependency: X ® Y, where Y is a non-key attribute (or a set of non-key attributes), X is a candidate key.

• This definition of 3NF differs from BCNF only in the specification of non-key attributes - 3NF is weaker than BCNF. (BCNF requires all determinants to be candidate keys.)

• A relation in 3NF will not have any transitive dependencies of non-key attribute on a candidate key through another non-key attribute.

Consider this Employee relation

EmpName, DeptNum, and DeptName are non-key attributes.

DeptNum determines DeptName, a non-key attribute, and DeptNum is not a candidate key.

Is the relation in 3NF? … no

Is the relation in 2NF? … yes

Is the relation in BCNF? … no

We correct the situation by decomposing the original relation into two 3NF relations. Note the decomposition is lossless.

Verify these two relations are in 3NF.

In DBMS describe Two-Phase Locking Techniques with Algorithm

2020-05-09T21:02:00.001+05:30

Two-Phase Locking Techniques

Locking is an operation which secures (a) permission to Read or (b) permission to write a data item for a transaction. Example: Lock (X). Data item X is locked in behalf of the requesting transaction.

Unlocking is an operation which removes these permissions from the data item. Example: Unlock (X). Data item X is made available to all other transactions. Lock and Unlock are Atomic operations

Two-Phase Locking Techniques: Essential components

1) Two locks modes (a) shared (read) and (b) exclusive (write).

Shared mode: shared lock (X). More than one transaction can apply share lock on X for reading its value but no write lock can be applied on X by any other transaction.

Exclusive mode: Write lock (X). Only one write lock on X can exist at any time and no shared lock can be applied by any other transaction on X.

Conflict matrix

2) Lock Manager: Managing locks on data items Lock table: Lock manager uses it to store the identify of transaction locking a data item, the data item, lock mode and pointer to the next data item locked. One simple way to implement a lock table is through linked list.

3) Database requires that all transactions should be well- formed. A transaction is well-formed if:

• It must lock the data item before it reads or writes to it.

• It must not lock an already locked data items and it must not try to unlock a free data item

4) The following code performs the lock operation:

B: if LOCK (X) = 0 (*item is unlocked*)

then LOCK (X) ¬ 1 (*lock the item*)

else begin

wait (until lock (X) = 0) and

the lock manager wakes up the transaction);

goto B

end;

The following code performs the unlock operation:

LOCK (X) ¬ 0 (*unlock the item*)

if any transactions are waiting then wake up one of the waiting the transaction.

5) The following code performs the read operation:

B: if LOCK (X) = “unlocked” then

begin LOCK (X) ¬ “read-locked”;

no_of_reads (X) ¬ 1;

end

else if LOCK (X) ¬ “read-locked” then

no_of_reads (X) ¬ no_of_reads (X) +1

else begin wait (until LOCK (X) = “unlocked” and

the lock manager wakes up the transaction);

go to B

end;

6) The following code performs the write lock operation:

B: if LOCK (X) = “unlocked” then

begin LOCK (X) ¬ “read-locked”;

no_of_reads (X) ¬ 1;

end

else if LOCK (X) ¬ “read-locked” then

no_of_reads (X) ¬ no_of_reads (X) +1

else begin wait (until LOCK (X) = “unlocked” and

the lock manager wakes up the transaction);

go to B

end;

7) The following code performs the unlock operation:

if LOCK (X) = “write-locked” then

begin LOCK (X) ¬ “unlocked”;

wakes up one of the transactions, if any

end

else if LOCK (X) ¬ “read-locked” then

begin

no_of_reads (X) ¬ no_of_reads (X) -1

if no_of_reads (X) = 0 then

begin

LOCK (X) = “unlocked”;

wake up one of the transactions, if any

end

Lock conversion

Lock upgrade: existing read lock to write lock

if Ti has a read-lock (X) and Tj has no read-lock (X) (i ¹ j) then

convert read-lock (X) to write-lock (X)

else

force Ti to wait until Tj unlocks X

Lock downgrade: existing write lock to read lock

Ti has a write-lock (X) (*no transaction can have any lock on X*)

convert write-lock (X) to read-lock (X)

Two-Phase Locking Techniques: The algorithm

Two Phases: (a) Locking (Growing) (b) Unlocking (Shrinking).

Locking (Growing) Phase: A transaction applies locks (read or write) on desired data items one at a time.

Unlocking (Shrinking) Phase: A transaction unlocks its locked data items one at a time.

Requirement: For a transaction these two phases must be mutually exclusively, that is, during locking phase unlocking phase must not start and during unlocking phase locking phase must not begin.

Two-Phase Locking Techniques: The algorithm

T1 T2 Result

read_lock (Y); read_lock (X); Initial values: X=20; Y=30

read_item (Y); read_item (X); Result of serial execution

unlock (Y); unlock (X); T1 followed by T2

write_lock (X); Write_lock (Y); X=50, Y=80.

read_item (X); read_item (Y); Result of serial execution

X:=X+Y; Y:=X+Y; T2 followed by T1

write_item (X); write_item (Y); X=70, Y=50

unlock (X); unlock (Y);

Two-Phase Locking Techniques: The algorithm

T’1 T’2

read_lock (Y); read_lock (X); T1 and T2 follow twophase

read_item (Y); read_item (X); policy but they are subject to

write_lock (X); Write_lock (Y); deadlock, which must be

unlock (Y); unlock (X); deals with.

read_item (X); read_item (Y);

X:=X+Y; Y:=X+Y;

write_item (X); write_item (Y);

unlock (X); unlock (Y);

Two-Phase Locking Techniques: The algorithm

Two-phase policy generates two locking algorithms (a) Basic and (b) Conservative.

Conservative: Prevents deadlock by locking all desired data items before transaction begins execution.

Basic: Transaction locks data items incrementally. This may cause deadlock which is dealt with.

Strict: A more stricter version of Basic algorithm where unlocking is performed after a transaction terminates (commits or aborts and rolled- back).This is the most commonly used two-phase locking algorithm.

What is external sorting in DBMS? Explain the algorithm for external sorting by using short merge strategy

2020-05-06T20:03:00.000+05:30

Algorithms for External Sorting

External sorting: refers to sorting algorithms that are suitable for large files of records stored on disk that do not fit entirely in main memory, such as most database files.

Sort-Merge strategy: starts by sorting small subfiles (runs) of the main file and then merges the sorted runs, creating larger sorted subfiles that are merged in turn.

–Sorting phase: nR = ⌐(b/nB)¬

–Merging phase: dM = Min (nB-1, nR); nP = ⌐(logdM(nR))¬

nR: number of initial runs; b: number of file blocks;

nB: available buffer space; dM: degree of merging;

nP: number of passes.

Outline of the short merge algorithm for external sorting

How to execute raw SQL query by using DBcontext

2020-05-04T21:02:00.000+05:30

Execute raw SQL query by using DBcontext

executing raw SQL query by using command below

DbSet.SqlQuery()

DbContext.Database.SqlQuery()

DbContext.Database.ExecuteSqlCommand()

DbSet.SqlQuery()

Use the DbSet.SqlQuery() method to write raw SQL queries which return entity instances. The resulted entities will be tracked by the context, as if they were returned by the LINQ query.

using (var ctx = new companyEntities())

{

    var studentList = ctx.employee

                        **.SqlQuery("Select * from employee")

                        .ToList<employee>();**

}

The above query executes select * from employee SQL in the database to get all students and will be converted into a list of employee entities. The column names in the SQL query must match with the properties of an entity type, otherwise, it will throw an exception.

You can specify the parameters using the object of SqlParameter, as shown below.

using (var ctx = new companyEntities())

{

    var student = ctx.employee

                    **.SqlQuery("Select * from employee where employeeId=@id", new SqlParameter("@id", 1))

                    .FirstOrDefault();

}

If you change the column name in SQL query, then it will throw an exception because it must match column names. The following example will throw an exception.

using (var ctx = new companyEntities())

{

    //this will throw an exception

  var employeeName = ctx.employee.SqlQuery("Select employeeid as id, employeename as name

            from employee where employeename='Souvik'").ToList();

}

The DbSet<TEntity>.SqlQuery() executes the SQL query only for the table which is mapped with the specified entity (e.g. DbSet<Student>.SqlQuery() only returns the result from the corresponding students table and not from any other table). The following will throw an exception.

using (var ctx = new companyEntities())

{

    //this will throw an exception

  var employeeName = ctx.employee.SqlQuery("Select * from Courses").ToList();

}

Database.SqlQuery()

The Database class represents the underlying database and provides various methods to deal with the database. The methodDatabase.SqlQuery() returns a value of any type.

using (var ctx = new CompanyDBEntities())

{

    //Get employee name of string type

    string employeeName = ctx.Database.SqlQuery<string>("Select employeename from employee where employeeid=1")

                            .FirstOrDefault();

    //or

    string employeeName = ctx.Database.SqlQuery<string>("Select employeename from employee where employeeid=@id", new SqlParameter("@id", 1))

                            .FirstOrDefault();

}

Database.ExecuteSqlCommnad()

The Database.ExecuteSqlCommnad() method is useful in executing database commands, such as the Insert, Update and Delete command.

using (var ctx = new CompanyDBEntities())

{



using (var ctx = new companyEntities())

{

    int noOfRowUpdated = ctx.Database.ExecuteSqlCommand("Update employee

            set employeename ='changed student by command' where employeeid=1");

    int noOfRowInserted = ctx.Database.ExecuteSqlCommand("insert into student(employeename)

            values('New employee')");

    int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from employee

            where employeeid=1");**

}

Explain RAID (Redundant Array of Independent Disks )technology & it's uses in details with diagram in DBMS

2020-05-04T15:41:00.002+05:30

RAID (Redundant Array of Independent Disks)
Technology

l A natural solution is a large array of small independent disks acting as a single higher-performance logical disk. A concept called data striping is used, which utilizes parallelism to improve disk performance.

l Data striping distributes data transparently over multiple disks to make them appear as a single large, fast disk.

Different raid organizations were defined based on different

combinations of the two factors of granularity of data

interleaving (striping) and pattern used to compute redundant

information.

l Raid level 0 has no redundant data and hence has the best

write performance.

l Raid level 1 uses mirrored disks.

l Raid level 2 uses memory-style redundancy by using

Hamming codes, which contain parity bits for distinct

overlapping subsets of components. Level 2 includes both

error detection and correction.

l Raid level 3 uses a single parity disk relying on the disk

controller to figure out which disk has failed.

l Raid Levels 4 and 5 use block-level data striping, with

level 5 distributing data and parity information across all

disks.

l Raid level 6 applies the so-called P + Q redundancy

scheme using Reed-Soloman codes to protect against up

to two disk failures by using just two redundant disks.

Use of RAID Technology

Different raid organizations are being used under

different situations

l Raid level 1 (mirrored disks)is the easiest for rebuild of a disk from other disks– It is used for critical applications like l

Raid level 2 uses memory-style redundancy by using Hamming codes, which contain parity bits for distinct overlapping subsets of components. Level 2 includes both error detection and correction.

l Raid level 3 ( single parity disks relying on the disk controller to figure out which disk has failed) and level 5 (block-level data striping) are preferred for Large volume storage, with level 3 giving higher transfer rates.

l Most popular uses of the RAID technology currently are: Level 0 (with striping), Level 1 (with mirroring) and Level 5 with an extra drive for parity.

l Design Decisions for RAID include – level of RAID, number of disks, choice of parity schemes, and grouping of disks for block-level striping.

What is the type Functions in SQL?Explain user defined function with syntax

2020-05-03T19:35:00.000+05:30

What is the type Functions in SQL?Explain user defined function with syntax

Types of Functions in SQL Server

There are two types of functions in SQL Server:

System Functions: All the built-in functions supported by the Server called System functions in SQL Server. We don’t have to bother about the logic inside them because they cannot be modified. For example, Mathematical Functions, Ranking Functions, String Functions, etc.

2. User Defined Functions: The User Defined Functions in SQL Server are like functions in any other programming language that accepts the parameters, performing complex calculations, and returning the result value.

SQL Server allows us to create our functions called as user defined functions in SQL Server. For example, if we want to perform some complex calculations, then we can place them in a separate function, and store it in the database. Whenever we need the calculation, we can call it. There are two types of SQL user defined functions:

Scalar Function: It is a function that returns a single value. Generally, we have to define the function body between BEGIN … END block, but for inline scalar function, you can omit them. We can use any SQL data typeas the return type except text, image, ntext, cursor, and timestamp.
Table Valued Functions: It is a user defined function in SQL Server that returns a table.
- - Inline Table valued Functions: This function returns a table data type based on a single SELECT Statement
- Multi Statement Table valued Functions: This function also returns the tabular result set. But, unlike the inline table valued function, we can use multiple select statements inside the function body.

Advantages of User Defined Functions in SQL Server

User defined functions in SQL Server prevent us from writing the same logic multiple times.
Within the Database, you can create the function once, and call it n number of times.
SQL user defined functions reduce the compilation time of query by catching the execution plan and reusing them.
This user defined functions can help us to separate the complex calculations from the regular query so that we can understand and debug the query quicker and better.
It reduces the network traffic because of its cache plan
SQL Server Functions also used in WHERE Clause as well. By this, we can limit the number of rows sent to the client.

Syntax of User Defined Functions in SQL Server

The syntax the SQL server User defined functions or UDF is

CREATE FUNCTION Function_Name(@Parameter_Name Data_type, 
                                 .... @Parameter_Name Data_type
                             )
RETURNS Data_Type
AS
   BEGIN
      -- Function Body
      
      RETURN Data 
   END

Return_Type:
1. Data Type: Please specify the data type of return value. For example, VARCHAR2, INT, FLOAT, etc.
2. Data: Please specify the return value, and it should match the Data Type. It can be a single value or Table
Function_Name: You can specify any name you wish to give other than the system reserved keywords. Please try to use meaningful names so that you can identify them easily.
@Parameter_Name: Every function accepts zero or more parameters; it completely depends upon the user requirements. While declaring the parameters don’t forget the appropriate data type. For example (@name VARCHAR2(50), @number INT)
Function Body: Any query, or any complex mathematical calculations you want to implement in this particular function.

What is Disk storage Devices in DBMS ? with proper diagram

2020-05-01T19:04:00.001+05:30

Disk Storage Devices

l Preferred secondary storage device for high storage capacity

and low cost.

l Data stored as magnetized areas on magnetic disk surfaces.

l A disk pack contains several magnetic disks connected

a rotating spindle.

l Disks are divided into concentric circular tracks on each

disk surface. Track capacities vary typically from 4 to 50

Kbytes. Because a track usually contains a large amount of

information, it is divided into smaller blocks or sectors.

l The division of a track into sectors is hard-coded on the disk

surface and cannot be changed. One type of sector organization

calls a portion of a track that subtends a fixed angle at the

center as a sector.

l A track is divided into blocks. The block size B is fixed for each

system. Typical block sizes range from B=512 bytes to B=4096

bytes. Whole blocks are transferred between disk and main

memory for processing.

l A read-write head moves to the track that contains the block to

be transferred. Disk rotation moves the block under the read-

write head for reading or writing.

l A physical disk block (hardware) address consists of a cylinder

number (imaginary collection of tracks of same radius from all

recorded surfaces), the track number or surface number (within

the cylinder), and block number (within track).

l Reading or writing a disk block is time consuming because of

the seek time s and rotational delay (latency) rd.

l Double buffering can be used to speed up the transfer of

contiguous disk blocks.

Diagrams of how to work Disk storage device

Example of typical disk parameter below

What is database schema & database independence? explain in details with diagram

2020-04-30T13:54:00.001+05:30

Database Schema & Data Independence

Database Schema: - A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how relation among them are associated. It formulates all the constrains that are to be applied on the data.

A database schema defines its entities and the relationship among them. It contains descriptive details of the database which can be described by means of schema diagram. A database schema can be divided into two categories.

I. Physical database schema: - this schema contain to 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.

II. Logical database schema: - this schema defines all the logical constrains that need to be applied on the data stored. It defines tables, views and integrality constrains.

Data Independence: - Data Independence can be explained using 3three schema architecture. Data independence refers characteristic of being able to modified schema at one level of the database without altering the schema at next higher level.

There are two types of data independence.

I. Logical data independence: - It refers characteristic being able to change conceptual schema without having to change the external schema. Logical data independence is used to separate the external level from conceptual view. Logical data independence occurs at user inference level.

II. Physical data independence: - Physical data independence can be defined as capacity to change internal schema without having to change conceptual schema. Physical data independence used to separate conceptual level from internal level. Physical data independence occurs at the logical interference level.

what is an-entity & it's type? explain with proper Diagram In DBMS.

2020-04-29T18:26:00.003+05:30

Entity set & it's types

1.What is entity?
Ans/- An entity can be a real word object either animated or inanimate that can be easily identifiable.

Entity set: - An entity set is a group of similar kind of entities. It may contain entities with attributes sharing similar values. Entities are represents by their properties which also called attributes.

Type of entity: -

Weak entity: - A weak entity is a type of entity which doesn’t have its key attribute. It can be identified uniquely by considering by consisting primary key of another entity. It represented by double rectangular box. Weak relation represent by double diamond box.

Strong entity: - A strong entity is a type of entity which has its key attribute for that strong entity said need not to have participation.

Difference between strong and weak entity: -

Strong entity	Weak entity
1. Strong entity set always has a primary key.	1. It doesn’t have enough attributes to build a primary key.
2. It is represented by a rectangular symbol.	2. It is represented by double rectangular symbol.
3. Member of a strong entity set is called as dominate entity set.	3. Member of a weak entity set is called as a subordinate entity set.
4. In the ER diagram the relationship between two strong entities set shown by a diamond symbol.	4. The relationship between one strong and weak entity set shown by double diamond symbol.

To know about Cardinality & its type in details visit my blog site

https://myrdbmsnotes.blogspot.com/2020/05/what-is-cardinality-or-binary.html

Relation model concept and it's advantage & disadvantage

2020-04-28T19:35:00.000+05:30

Relation model concept and it's advantage & disadvantage

1. What is relational model?

Ø Relation model represent database as a collection of relations. A relation is nothing but a table of values. Every row in the table represents a collection of related data values. This row in the table denotes a real world entity or relationship.

Relational model concept: -

· Attribute: - Each column in a table, Attributes is the properties which define a relation. Example: - DNO, DNAME.

· Table: - In the relational model relations are saved in the table format. A table has two properties rows, column. Rows represent records and column represents attributes.

· Degree: - The total number of attribute which in the relation which called the degree of relation.

· Cardinality: - Total number of rows present in the table.

Attribute domain: - Every attribute has some predefined values and scope which is known as attribute domain.

Advantage of relational model: -

1) Simplicity: - A relational data model is simpler than hierarchical model and network model.

2) Easy to use: - The relational model is easy as table consisting of rows and column is quite natural and easy to understand.

3) Data independence: - the structure of a database can be changed without having to change any application.

4) Query capability: - It make possible for a high level query language like SQL to avoid complex data navigation.

Disadvantage of using relational model: -

· Few relational databases have limits on field length which cannot be exceeded

· Relational databases can sometime become complex as the amount of data grows and the relation between pieces of data become more complicated.

· Complex relational database may lead isolated databases where information cannot be shared from one system to another.

Explain Database model & it's types in DBMS with diagarm

2020-04-24T15:46:00.000+05:30

Database models & it's definition

Database model: - Database model defines the logical design and structure of database and defines how data will be stored and updated in a database management. Data Models are fundamental entities to introduce abstraction in a DBMS.

1) Hierarchical Model: - This database model organizes data into a tree like structure with a single root to which all the other data is linked. The hierarchy starts from root data and expands like tree, adding child nodes to the parent nodes. In this model a child node will only have a single parent node. This model efficiently describes many real world relationship like index of a book, recipes. In hierarchical model, data is organized into tree like structure with one, one to many relationship between two different types of data.

2) Network Model: - This is an extension of the hierarchical model. In this model data is organized more like a graph and are allowed to have more than one parent node. In this database model data is more related as more relationship established in this model. As the data is related, hence accessing the data is also easier and fast. This database model was used to map many to many data relationship.

3) Entity relationship model (ER model): - In this model relationship are created by dividing objects of interest into entity and its characteristic into attributes. ER model are define to represent the relationship into pictorial form to make it easier for different situation to understand. This model is good to design a database which can be turn into tables in relational mode.

I. 4) Relational model: - In this model, data is organized into two dimensional tables and relationship is maintain by storing a common filed. Basic structure of data in the relational model is tables. All the information related to a particular type is stored in rows of that table.

What is DBMS languages & its commands?

2020-04-23T19:54:00.002+05:30

DBMS languages

Database languages are used to read, update and store data in database. These database languages that can be used for this purpose one of them SQL. There are Five types of database languages
Types Database languages are

DDL :- Data Definition Language

DML :- Data Manipulation Language

DCL :- Data Control Language

SQL :- Structure Querying Language

TCL :- Transaction Control Language

I. 1)DDL(Data Definition Language):- Data Definition Language used for specifying the data base ‘schema’. It is used for creating tables, schemas etc. in database.
Commands of Data Definition Language(DDL) are given below

CREATE: - to create database instances.

DROP: - to drop database instances.

COMMENT: - to comment.

ALTER: -to alter structure of database

2)DML(Data Manipulation Language): - Data Manipulation Language used for accessing and manipulating data in database.
Commands of Data Manipulation Language (DML) are

INSERT: - to insert record(s) into the table.

UPDATE: - to update data into table.

DELETE: - to delete record(s) into the table.

SELECT: -to read/retrieved record(s) from table.

III. 3)DCL( Data Control Language): - Data Control Language used for granting and revoking user access on a database.
Commands of Data Control Language(DCL) are given below

GRANT: - to grant access to user.

REVOKE: - to revoke access to user.

4)TCL( Transaction Control Language): - Transaction Control Languages are used to changes in the database that we made by using DML commands either performance or role backed by using TCL.
Commands of Transaction Control Language(TCL) are given below

COMMIT: - to persist the changes made by DML command in database.

ROLL BACK: - to roll back the changes made to the database.

What is file system and advantage of DBMS over file system

2020-04-20T19:22:00.000+05:30

Definition of File system & it's advantage of DBMS over file system

What is file system?

Ans/- A file system is a database system that allows us access to single file at a time. In a file system data is directly stored in set of file. It contains flat file that have no relation to other file.

Advantage of DBMS over file system:-

1) Data redundancy: - Data redundancy refers to the duplication of data that means each data may have a single copy. A file system can’t control redundancy of data. In DBMS data redundancy removed by using of data normalization.

2) Data inconsistency: - Data redundancy leads to data inconsistency. In DBMS data redundancy is taken care by data normalization and data in consistency also been taken care of as part of it.

3) Data isolation: - In file system data stored are in various file and files may be different formats. So it is very difficult to retrieved appropriate data. But DBMS data stored in single file so retrieved appropriate data more easy.

4) Atomicity issue: - Atomicity of an instruction refers to all or nothing which means either all operation in a transaction executes or none. It is difficult to achieve atomicity in file system not in DBMS.

5) Data dependency: - In file system changing in data is depended on application program but DBMS changing in data independent.

6) Easy recovery: - Since database system keeps the backup of data. It is easier to do a full recovery of data in case of a failure.

7) Flexible: - Database system is more flexible than file processing system

Explains the client server architecture /three tier architecture of DBMS in details

2020-04-19T19:43:00.000+05:30

Client server architecture (explains the client server architecture of DBMS/explains three tier architecture of DBMS): -

Client server architecture is an architectural deployment style that describes separation of functionality into layers with each segment being tier that can be located on a physically separate computer. A client server architecture separate its tier from each other based on the complexity of user and how they used the data present in the database. It is the most widely used architecture to design a DBMS.

A. Database tier: - At this tier database resides along with its query processing languages. We also have the relation that define the data and their constrains at this level.

B. Application (middle) tier: - At this tier reside the application server and program that access database. For a user this application tier presents and abstracted view of database. Hence application tier/layer sits in the middle and acts as mediator between the end user and database.

C. Presentations (user) tier: - End users operate on this tier and they know nothing about any existence of database beyond this layer. At this layer multiple views of the database can be provided by application. All views are generated by application that resides in application tier.

What is Hierarchical database model?

2020-04-18T22:00:00.000+05:30

I. Hierarchical Model: - A hierarchical model represents the data in a tree-like structure in which there is a single parent for each record. To maintain order there is a sort field which keeps sibling nodes into a recorded manner. These types of models are designed basically for the early mainframe database management systems, like the Information Management System (IMS) by IBM. This database model organizes data into a tree like structure with a single root to which all the other data is linked. The hierarchy starts from root data and expands like tree, adding child nodes to the parent nodes. In this model a child node will only have a single parent node. This model efficiently describes many real world relationship like index of a book, recipes. In hierarchical model, data is organized into tree like structure with one, one to many relationship between two different types of data. Here the example of this model: -

You can easily understand the hierarchical model .
to know other DATABASE MODELS visit this site https://myrdbmsnotes.blogspot.com/2020/04/database-models-its-definition-database.html