Relational Set Operators

Relational algebra :

Defines theoretical way of manipulating table contents using relational operators

Use of relational algebra operators on existing relations produces new relations

• UNION INTERSECT
• DIFFERENCE PRODUCT
• SELECT PROJECT JOIN DIVIDE

• UNION: combines all rows from two tables, keeping out duplicate rows.
• INTERECT: yields only the rows that appear in both tables.
• DIFFERENCE: yields all rows in one table that are not found in other table.
• PRODUCT: yields all possible pairs of rows from two tables.
• SELECT (RESTRICT): yields values for all rows found in a table that satisfy a given condition.
• PROJECT: yields all value for selected attributes.
• JOIN: allows information to be combined from two or more tables.
• Natural join: links tables by selecting only the rows with common values in their common attributes.
• Left outer join: yields all of the rows in a table, including rows with now matching values from other table.
• DIVIDE: uses one single column as the divisor and one 2 column as the dividend.

Written by: Mohammed Alaslani

Some important Key Terms for attribute and entity

A required attribute: is an attribute that must have a value.
An optional attribute: is an attribute that does not require a value.
Composite attribute: is an attribute that can be subdivided to yield additional attributes.
Simple attribute: is an attribute that cannot be subdivided.
Single-valued-attribute: is an attribute that can only have single value.
multivalued attribute : an attribute can have many values.
A derived attribute: is an attribute whose value is calculated from other attributes.
The entities that participate in a relationship are also known as participants.
Cardinality: expresses the minimum and maximum number of entity occurrences associated with one occurrence of related entity.
An entity is said to be existence-dependant if it can exist in the database when it is associated with another related entity occurrence.
If an entity can exist a part from one or more related entities it is said to be existence-independent.
A weak relationship (non-identifying-relationship): exists if the PK of related entity does not contain a PK component of parent entity.
A strong relationship (identifying-relationship): exists if the PK of related entity contains a PK component of parent entity.
Optional participation: means that one entity occurrence does not require a corresponding entity occurrence in a particular relationship.
Mandatory participation: means that one entity occurrence requires a corresponding entity occurrence in a particular relationship.

Written by : Mohammed Alaslani

Type of database

• Single-user database supports only one user at a time
• Multiuser database supports multiple users at the same time
• Workgroup database supports a small number
• Enterprise database supports a large number
• Centralized database: data located at a single site
• Distributed database: data distributed across several different sites
• Operational database: supports a company’s day-to-day operations(Transactional or production database )
• Data warehouse: stores data used for tactical or strategic decisions
• Unstructured data exist in their original state
• Semistructured data have been processed to some extent
• Extensible Markup Language (XML) represents data elements in textual format
• XML database supports semistructur

Databases can be classified according to:

–Number of users

–Database location(s)

–Expected type and extent of use

Written by : Mohammed Alaslani

How to create and insert values into table(SQL)

In this blog i will show how user an create table and insert data into the table he want.

First of all we must use this syntax :

CREATE TABLE (TABLE_NAME)

(

Then after the (

You an declare any attribute you want

So for example lets create the student table:

CREATE TABLE STUDENT

(

STUDENT_NAME VARHAR2(25),

STUDENT_NUMBER NUMBER(10);

)

This the result we will have:

STUDENT_NAME

STUDENT_NUMBER

Now let say we want to add value for that table

so we must use this syntax

INSERT INTO STUDENT VALUES (‘Mohammed Alaslani’ , 12982831);

This the final result will be for the student table

STUDENT_NAME	STUDENT_NUMBER
Mohammed Alaslani	12982831

* The values are example from me you an use whatever you want but the same type you have delared.

Written by: Mohammed Alaslani

Normalization

A normalization i s a process for evaluating and correcting table structure to minimize data redundancy.Where as denormalization is to get lower normal form. iwill be considring on this post 3 types of normalization only which are:

1NF

2NF

3NF

and the others are :

BCNF AND 4NF

second normal form is better than first normal form , and third normal form is better than second normal form.

So if you have bigger normal form it is better than less normal form.

i have created a table using Microsoft word , shows how to get 1NF, 2NF , 3NF:

Written by: Mohammed Alaslani

Cursors in PL/SQL

A Cursors is Pointer to memory location on database server , and DBMS uses to process a SQL query.

An example of cursor

DECLARE

CURSOR student_cur IS

SELECT student_name, student_id FROM student;

– declare record variable that represents a row fetched from the studentt table

student_rec student_cur%ROWTYPE;

BEGIN

– open the explicit cursor and use it to fetch data into student_rec

OPEN student_cur;

LOOP

FETCH student_cur INTO student_rec;

EXIT WHEN student_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(student_rec.student_name || ‘ , ‘ ||   student_rec.student_id);

END LOOP;

END;

Written by: Mohammed Alaslani

Anonymous Block PL SQL

In this blog i will give an example of creating Anonymous Block in SQL :

First of all you have to use the server output on so it can show you the result of your Anonymous Block .

Secondly you can declare any variable you want to use , and then begin work on it , you can use loops if want to , it is up to you what do you want with this Anonymous Block .

And finally you have to close everythig by using the keyword END;

This a simple example of Anonymous Block PL SQL that will print (Hello World).

SET SERVEROUTPUT ON

DECLARE

TXT VARCHAR2(20) = ‘Hello World’;

BEGIN

DBMS_OUTPUT.PUT_LINE(TXT);

END;

The result will be = Hello World

Written by : Mohammed Alaslani

How data base design fits into the overall systems development process

Database Design :
Is the method of producing a detailed data representation of a database.

The database design process is comprised of the next six steps:

Requirements collection and analysis:

The first step in designing a database is to choose what type of data require to be stored in the database, and what applications must be built on top of it, and what operations are most common and subject to presentation requirements.

Conceptual database design:

The aim is to create a simple explanation of the data that very much matches how users and developers imagine of the data.

Choosing a DBMS :

The Purpose of this step is to establish which the best framework for implementing the produced schema is. For example, type of DBMS (relational, network, deductive …). Or, what types of query languages.

Data model mapping(data model mapping) :

That is to alter the generic, DBMS independent conceptual schema in the data model of the selected DBMS.

Physical database design :

To decide the specific storage structures and access paths for the database files.

Database system implementation :

Database system implementation, consists of inflowing data and maintaining the DBMS

In order to have a good database design , usually those six steps must be considered on the design.

Written by: Mohammed Alaslani