View, Sequence & Synonym in RDBMS

VIEW, SEQUENCE & SYNONYMS in RDBMS

View

A view is a logical entity also called virtual table which is created from the base table or main table.

The main objective of creating a view is to provide secure access over the main table. In some cases when admin want to show some column from the specific table, admin or database programmer can do this by creating separate table i.e. virtual table or VIEW with the same data for each user as per the requirement but Data Redundancy is the limitation of this approach.

Read-only view:

A view by which user can only view records but unable to insert, update or delete records is called read only view.

Updatable view:

A view by which user can perform DML statements like select, insert, update or delete operation is called updatable view.

A view can be read only or updatable, that can be determined by its definition.

If a view definition having complete primary key field as well as Not Null key fields then view is updatable otherwise read only.

Example-

Let us take example of each view one by one by following given steps-

1. Create a table student by given command for which you want to create a VIEW.

SQL> CREATE TABLE Student

            (

                Enroll_No int PRIMARY KEY,

                Student_Name varchar(20),

                Course varchar(10),

                Fees numeric(10,2)

            );

2. Create a Read Only VIEW named  VWR_Student by given command-

SQL>CREATE OR REPLACE VIEW  VWR_Student AS Select Student_Name, Course, Fees FROM Student;

3. Create Updatable VIEW named VWU_Student by given command-

SQL>CREATE OR REPLACE VIEW VWU_Student AS Select * FROM Student;

4. Now we can check the difference between these two views one by one by given commands-

    A. We can not perform insert operation by Read Only View because it does not have primary key. If we perform below insert command without primary key data than there will be an error.

        SQL>INSERT INTO VWR_Student VALUES('ABC', 'BCA', 12000);

        error: can not insert NULL into System.Student.Enroll_No.

    B. We can perform below insert command by Updatable View because it has primary key.

        SQL>INSERT INTO VWU_Student VALUES(1, 'ABC', 'BCA', 12000); 

 OUTPUT WINDOW of VIEW

Practical Demonstration of VIEW in Oracle

SEQUENCE

Sequence is an order set of values like 1, 3, 5, … that are generated by SQL Program in Oracle and supported by some database systems to produce unique values on requirement.

A sequence is nothing but a user-defined schema or object that automatic generates an ordered value of numeric type.

Sequences are regularly used in database programming skills now a days because of the requirement of a unique value in each row inside a table in many applications and sequences provides an easy way to generate it.

The order of numeric values are automatically generated in an ascending or descending order according to definition of Sequence with defined intervals and can be restarted when exceeds maximum value.

Example-

Let us understand Sequence in Oracle with following Steps.

1. Execute below command to create a table student for which you want to create a SEQUENCE.

SQL> CREATE TABLE Student

            (

                Enroll_No int PRIMARY KEY,

                Student_Name varchar(20),

                Course varchar(10),

                Fees numeric(10,2)

            );

2. Create a SEQUENCE named  SEQ_Student by given command-

SQL>CREATE SEQUENCE SEQ_Student

          start with 1

          increment by 1

          minvalue 0

          maxvalue 100

         cycle;

3. Insert the sequential value by nextval predefined function given below-

SQL>INSERT INTO Student VALUES(SEQ_Student.nextval, 'ABC', 'BCA', 12000); 

SQL>INSERT INTO Student VALUES(SEQ_Student.nextval, 'ABCD', 'BCOM', 14000);

OUTPUT WINDOW of Sequence

Practical Demonstration of Sequence in Oracle

SYNONYMS

The CREATE SYNONYM is a statement which allows you to create an alternative name called synonym for a database object such as a table, sequence, view, procedure and  stored function.

Example-

Let us take an Example to understand Synonyms by following steps-

1. Execute below command to create a student table.

SQL> CREATE TABLE Student

            (

                Enroll_No int PRIMARY KEY,

                Student_Name varchar(20),

                Course varchar(10),

                Fees numeric(10,2)

            );

2. Execute below command to insert a record in student table.

SQL>INSERT INTO Student VALUES(1,'abc', 'BCA', 10000);

  

3. Execute below command to create an alternate name or synonym of student table (ex. Stud).

SQL> CREATE OR REPLACE SYNONYM Stud FOR Student;

4. Execute any one of the below commands to select record from student table.

SQL>SELECT * FROM STUDENT;

                        

                        OR

SQL>SELECT * FROM STUD;

OUTPUT WINDOW of SYNONYM

Practical Demonstration of Synonym in Oracle

BCA RDBMS PRACTICAL NUMBER_7

PRATICAL NUMBER-7 

➤ Perform Different types of joins on any two tables.

Types of joins-

A. Equi Join

B. Theta Join or Non-Equi Join

C. Self Join 

D. Natural Join

E. Inner join

F. Outer Join

    1. Left Outer Join

    2. Right Outer Join

    3. Full Outer Join

G. Cross Join 

We can perform these joins by given below steps-

1. Create Student and Course table to perform join operations by given below commands-

    SQL>CREATE TABLE Student

            (

                Enroll_No int,

                Student_Name varchar(20),

                Course_ID int

            );

            Table created.

    SQL>CREATE TABLE Course

            (

                Course_ID int,

                Course_Name varchar(20),

                Course_Duration varchar(20)

            );

            Table created.

2. Insert records in above table by given below commands-

    SQL> INSERT INTO Student VALUES(101, 'Shreya', 102);

               1 row created.

    SQL> INSERT INTO Student VALUES(102, 'Tanu', 103);

               1 row created.

    SQL> INSERT INTO Student VALUES(103, 'Roshni', 100);

               1 row created.

    SQL> INSERT INTO Student VALUES(104, 'Manisha', 101);

               1 row created.

    SQL> INSERT INTO Course VALUES(101, 'BCA', 'Three Years');

               1 row created.

    SQL> INSERT INTO Course VALUES(103, 'BEd', 'Two Years');

               1 row created.

    SQL> INSERT INTO Course VALUES(108, 'BALLB', 'Five Years');

               1 row created.

3. Now check the record by below select Command-

    SQL>SELECT * FROM Student;

Enroll_No	Student_Name	Course_ID
101	Shreya	102
102	Tanu	103
103	Roshni	100
104	Manisha	101

    SQL> SELECT * FROM Course;

Course_ID	Course_Name	Course_Duration
101	BCA	Three Years
103	BEd	Two Years
108	BALLB	Five Years

4. Now we can start to perform the join operations by below commands-

    A. Equi Join-It is used to join two tables by equal to operator(=).

    SQL> SELECT Enroll_No, Student_Name, Course_Name, Course_Duration FROM Student S, Course C WHERE S.Course_ID = C.Course_ID; 

Enroll_No	Student_Name	Course_Name	Course_Duration
102	Tanu	BEd	Two Years
104	Manisha	BCA	Three Years

    B. Non-Equi Join-It is used to join two tables by Relational Operator except equal to operator.

    SQL> SELECT Enroll_No, Student_Name, Course_Name, Course_Duration FROM Student S, Course C WHERE S.Course_ID > C.Course_ID;

Enroll_No	Student_Name	Course_Name	Course_Duration
101	Shreya	BCA	Two Years
102	Tanu	BCA	Two Years

    C. Self Join- It is used to join table itself by its aliases.

    SQL> SELECT S1. Enroll_No, S2. Course_ID, S1.Student_Name FROM Student S1, Student S2 WHERE S1.Enroll_No=S2.Course_ID;

Enroll_No	Course_ID	Student_Name
102	102	Tanu
103	103	Roshni
101	101	Shreya

    D. Natural Join- It is used to join tables by common Attribute.

    SQL> SELECT * FROM Student NATURAL JOIN Course;

Course_ID	Enroll_No	Student_Name	Course_Name	Course_Duration
103	102	Tanu	BEd	Two Years
101	104	Manisha	BCA	Three Years

    E. Inner Join- It is used to join tables by common values of conditional result.

    SQL> SELECT * FROM Student INNER JOIN Course ON Student.Course_ID=Course.Course_ID;

Enroll_No	Student_Name	Course_ID	Course_ID	Course_Name	Course_Duration
102	Tanu	103	103	BEd	Two Years
104	Manisha	101	101	BCA	Three Years

    F. Outer Join- It is used to join tables by common Attribute as well as table remaining attributes based on different outer joins. Let us Discuss all three Outer Joins by an example of each. 

        1. LEFT OUTER JOIN- It is used to fetch the common result of both tables as well as complete record of Left Table and null values from Right table.

SQL> SELECT * FROM Student LEFT OUTER JOIN Course ON Student.Course_ID = Course.Course_ID;

Enroll_No	Student_Name	Course_ID	Course_ID	Course_Name	Course_Duration
104	Manisha	101	101	BCA	Three Years
102	Tanu	103	103	BEd	Two Years
103	Roshni	100
101	Shreya	102

           2. RIGHT OUTER JOIN- It is used to fetch the common result of both tables as well as complete record of Right Table and null values from left table.

SQL> SELECT * FROM Student RIGHT OUTER JOIN Course ON Student.Course_ID = Course.Course_ID;

Enroll_No	Student_Name	Course_ID	Course_ID	Course_Name	Course_Duration
102	Tanu	103	103	BEd	Two Years
104	Manisha	101	101	BCA	Three Years
			108	BALLB	Five Years

        3. FULL OUTER JOIN- It is used to fetch the common result of both tables as well as complete record of Left Table and right table with null values from both tables.

SQL> SELECT * FROM Student FULL OUTER JOIN Course ON Student.Course_ID = Course.Course_ID;

Enroll_No	Student_Name	Course_ID	Course_ID	Course_Name	Course_Duration
104	Manisha	101	101	BCA	Three Years
102	Tanu	103	103	BEd	Two Years
103	Roshni	100
101	Shreya	102
			108	BALLB	Five Years

    G. Cross Join- It is used to join tables by mapping of each row of first table to each row of second table.

    SQL> SELECT * FROM Student CROSS JOIN Course;

Enroll_No	Student_Name	Course_ID	Course_ID	Course_Name	Course_Duration
101	Shreya	102	101	BCA	Three Years
102	Tanu	103	101	BCA	Three Years
103	Roshni	100	101	BCA	Three Years
104	Manisha	101	101	BCA	Three Years
101	Shreya	102	103	BEd	Two Years
102	Tanu	103	103	BEd	Two Years
103	Roshni	100	103	BEd	Two Years
104	Manisha	101	103	BEd	Two Years
101	Shreya	102	108	BALLB	Five Years
102	Tanu	103	108	BALLB	Five Years
103	Roshni	100	108	BALLB	Five Years
104	Manisha	101	108	BALLB	Five Years

 OUTPUT WINDOWS

TABLE CREATION FOR JOIN OPERATIONS

Equi, Non-Equi, Self & Natural Join Operations

Inner Join, Outer Join (Left, Right & Full Outer Join) Operations

Cross Join Operation

BCA RDBMS PRACTICAL NUMBER_4

PRACTICAL NUMBER-4

➤ Create a Database Implementing Primary & Foreign Key.

We are working here on a Oracle 10g setup which uses system Database by default in which we are trying to implement Primary and Foreign key concept practically. Let us start step by step process given below-

1. Create a Student Registration Table in which we will use Rgst_No as a Primary Key of Integer Data Type by using given command-

CREATE TABLE Student_Rgst

(

    Rgst_No INT PRIMARY KEY,

    Student_Name VARCHAR(20) NOT NULL,

    Student_Course CHAR(20) NOT NULL,

    Mobile NUMBER(10) UNIQUE,

    Student_Fees numeric(10,2)

);

After pressing enter the table created message will appeared. The column used in above table described below-

Rgst_No --> Hold Integer values and used as a Primary key means value can not be NULL and must be                      Unique.

Student_Name --> Hold variable character values of size 20 and can not be NULL due to NOT NULL                                  Constraint.

Student_Course --> Hold character values of size 20 and can not be NULL due to NOT NULL Constraint.

Mobile --> Hold Numbers of size 10 and must be unique due to UNIQUE Constraint.

Student_Fees --> Hold Floating point values of Max size 10 in which 2 are precision values.

2. Create a Student Result Table in which we will use Rgst_No of Student_Rgst table as a reference so that Referential Integrity will be achieved by given command-

CREATE TABLE Student_Result

(

    Rgst_No INT REFERENCES Student_Rgst,

    Marks INT DEFAULT 0,

);

After pressing enter the table created message will appeared. The column used in above table described below-

Rgst_No --> It references the Rgst_No of Student_Rgst table. It can not be NULL or DUPLICATE.

Marks --> It accept the integer values. If not enter marks than it will take default value 0 de to DEFAULT Constraint.

3. Insert two records of Students in Student_Rgst table by given command-

INSERT INTO Student_Rgst VALUES(1,'SHREYA','BCA',1234567890,15000.25);

INSERT INTO Student_Rgst VALUES(2,'ROSHNI','BCOM',1234567892,16000.25);

After each statement we press Enter key that will create a row. Now we can see our table by select command-

SELECT * FROM Student_Rgst;

Our table look like-

Table-1. Student_Rgst

4. Insert One record of Students Marks in Student_Result table by given command-

INSERT INTO Student_Result VALUES(1,45);

After statement we press Enter key that will create a row. Now we can see our table by select command-

SELECT * FROM Student_Result;

Our table look like-

Table-2. Student_Result

5. We can not Perform the Following Operations-

    👉can not insert marks of student who is not registered i.e. below code will generate an error-

        INSERT INTO Student_Result VALUES(3,45);

    👉can not delete student record from Student_Rgst table if result of that student is available. i.e.

        DELETE Student_Rgst where Rgst_No=1; 

➤Complete Practical Output of the above queries given below in Picture format-

Implementing the Primary Key and Foreign Key

➤Complete Practical Output of the above queries with errors-

Errors after Primary and Foreign Key Implementation 

Three Layer Architecture of Database System

This Database Architecture is called three level architecture because of three layer implementation in this:

1. External Level (also called External Schema or View Level)

2. Conceptual Level (also called Logical Schema or Logical Level)

3. Internal Level (also called Internal Schema or Physical Level)

Three Layer Architecture of DBMS

Let us discuss each layer one by one-

1. External Level (also called External Schema or View Level)

This layer is topmost layer of DBMS system where view of logical level will be shared or displayed. The user or viewer desired result is represented as a view at this layer which is generated by fetching from Database or Logical Level. There are number of viewer can be exist for single conceptual level. The mapping between the View Level and Conceptual Level helps as a channel to get a result in view form from logical level database. This Layer is also called abstraction layer because it only display required view and hide the remaining information from user. The user do not need to know about from where data he gets or what the data structure (or schema) is used to store data. 

2. Conceptual Level (also called Logical Schema or Logical Level)

This Layer is Middle layer of DBMS Architecture where the structure of Database is logically defined. It also ensure the Data Structure used to store the Database, the index definition, Constraint implementation, Security etc. are the role responsibility of this Layer. The DBA (Database Administrator) is responsible for all these responsibilities at this layer.

3. Internal Level (also called Internal Schema or Physical Level)

This Layer is Bottom Layer or Lowest Level of DBMS Architecture that show how the data actually stored in Hard Disk. This Layer is responsible to allocate memory to data and its storage location. 

Data Independence in DBMS

Database architecture is generally designed in different layers so that each layer can work separately and if changes required at any level or at any layer than no problem would be encountered due to separation between the Layers. This separation provides the Data Independence. As we Learnt the Architecture of DBMS where three layers are used View level, Logical Level and Physical Level.

Data Independence means, if changes required at any level or at any schema than no other layers or schema will be infected by changes. The separation or isolation is required from application layer because no need to show the structure of database or its storage.   

Data Independence

---

Types of Data Independence

1. Logical Data Independence- It provide the ability to DBMS system that if any changes required at the Logical Schema or Logical Level than do no need to change the application program or view level. It means the External level or application program do not need to modify itself if changes done at logical level. In other words we can say the changes done at logical level will not generate any problem at external level due to the logical data independence. Example changes done in table will not required to modify the view for viewer at view level or External Level.

Logical Data Independence

2. Physical Data Independence- It provide the ability to DBMS system that if any changes required at the Physical Level or Physical Schema than do no need to change the Logical Schema or Logical Level. It means Conceptual Level or Logical Level do not need to change itself if changes done at Physical Level or Physical schema. In other words e can say that the changes done at physical level like storage structure modification, index modification, changes in accessing methods, changes in data structures etc. will not generate any problem at Logical Level de to the Physical Data Independence.   

Physical Data Independence

File System v/s Database System

File System

This system adopted after the the se of punch cards for storing the information in 1960's. In this system files are managed by software and the operation performed like insert, update, delete etc. are done manually. The advantages of this kind of system is easy data handling and having good security. Easy data handling is possible because the the work done by human manually. But in file system there are number of disadvantages like data redundancy (Data Duplication) is high, data inconsistency is high, unable to maintain data integrity, only application for small business like local shop etc. Le s understand these points one by one that completely express file system- 

1. File System adopted in 1960's for easy data handling manually.

2. The files are stored in Hard Disk Drives and managed by software and provides security.

3. All the operations like storing, searching or retrieving information is done manually and not highly technical skills were required.

4. Only one record is maintained in one file and hence many files were used to store many records.

5. Data Consistency is not achieved in this system because manual checking of data is not easy.

6. Data Redundancy is also not achieved in this system due to the possibility of having one record in more than one files and its manual checking is time consuming.

---

Database System

To overcome the issues created in File System, database system is in use. In the Database systems software manages the databases rather than files. Database system can not be used by normal human, for using this system human must be skilled in database technology. The Database System is very useful because it provides less redundant and consistent data as compared with file system redundant and inconsistent data by using Normalization process. The integrity is also achieved by database system but there is a problem i.e. the SQL Language knowledge is must for user. This process is semi manually process because you have to use any SQL command that can be performed by SQL Engine on Database to access, insert, update or delete operations. Let us understand Database System point by point-

1. Database System Come in Market after the file systems.

2. Database system provides the Data Consistency, less Data Redundancy and High Data Integrity.

3. Database System are used in large scale organization where the need of huge amount of data to be stored. 

4. Database System also supports other operations like TCL (Transaction Control Language) and DCL (Data Control Language) etc.

5. The Database management is a complex process.       

       

---

Difference between File System v/s Database System
Topic	File System	Database System
Definition	A system that manages the data into files inside the HDD and perform the file related operations.	A system that organize the collected data into a structure inside the database and perform the database related operation using SQL query Language.
Data Sharing	files are located in local system hence data sharing is not done.	Databases are stored in centralized system hence sharing is allowed in Database System.
Data Security	Files are stored in local system so it is secured if no other person use local system otherwise it is not secured. Majorly we call File System is insecure if files stored on Network.	Data stored in Database which is located in centralized system and only authorized user (using username and password) can access it. Hence it is more secure.
Data Consistency	Data consistency is very poor in the file system. For an example, If one person information is stored in more than one file than we have to change the information in all the files of particular person if required otherwise one person information is different in different files.	Data consistency is managed in database system. Because we can store data into centralized location.
Data Integrity	Data integrity is not maintained in file system.	Data integrity is maintained by DBMS system.
Data Redundancy	Data Redundancy (Data Duplication) is a major problem in file system.	Less Data Redundancy is exist in Database System.
Normalization Process	Normalization process is not used in File System.	Normalization process is required to maintain Remove the Data Redundancy and to Achieve the Data Consistency.
Application	It is used in small scale organization like local shop or locality information.	It is used to store at large scale organization like college, university etc.
Backup & Recovery	In File System, Backup & data recovery process is not good. If data is removed than there is less chances to recover complete data.	In this system Data Backup & Recovery is possible. In case of system failure backup is used to restore or recover the lost data.
Structure	Simple Structure is used to store data i.e. file.	Database structure is complex and normal human can not understand it.
Query Language	No any query language is required.	Database system required a Query Language known as SQL (Structure Query Language).
Data Abstraction	Data abstraction is not provided in File System.	Data Abstraction is possible in Database system. Only user required information is accessible other information is hidden to provide data abstraction functionality.
User Interface	Single User Interface is provided in File System hence called Isolated system.	Multiuser interface is provided by Database System so that multi user can access at the same time for same database.
Concurrency Control	Concurrent Access is not achieved in File System	Concurrent access is possible in Database System.
Transaction Control	Transaction Control Not possible in File System.	Transaction Control is achieved in Database System using TCL commands.
Data Control	Data Control not possible in File System.	Data Control is achieved in Database System using DCL commands.
Examples	NTFS, FAT & FAT32 etc.	MS-SQL, Oracle & MySQL etc.

Strategic Database Planning

We know that all the organization have their database resources through which they can store and manage their organizational data and maintain it. But these resource creation is purely work on needs of organization. These needs gives a result called Database. But to fulfil the needs we have to follow some basic rules or strategy so that needs of organization will be easily achieved.

When we are working on database creation for any client requirement than we have to follow some steps in sequential manner so that our database provide all the functionality according to client requirement with efficient and accurate result. 

Let us discuss such kind of strategies or sequential steps planning that create a Good Database-

1. Business Plan

We know that everything will be created after the plan. If client wants a database than client must have a good plan according to that plan client needs a Database. For an example if client want to open an organization like college than client have a plan like financial plan, property plan etc.

2. Information Needs

To work on this plan client must have useful information or required useful information so that its plan work successful. In our case client needed useful information for his organization (college) like how many courses is required which course have good scope, how many universities provide that courses, ho many staff members required, departments required etc.

3. Database Plan

Now the client have done his duty after showing information needs for his organization and after that the Database developer will a play role. Database Developer will understand all the plan and information needs and start to create his plan for making good database. This plan can be called as database blueprint. This plan contain the description about how many tables are required to fulfil client needs, mapping between tables is required or not, default values are required to store or not, how many users are required to access this database like manager, employee, admin etc.

4. Project Implementation or Database Implementation

Till this step database developer has full knowledge about the client requirement for his plan and on that requirement developer created his database plan. Now this database plan is implemented by developer as a project so that client can use it for his organization. In this implementation, Developer must have the complete database knowledge so that he can create such structures like for department table creation developer must ensure the primary key for Department ID, Not Null Constraint for Department Name etc. Similarly in Employee table, Employee ID must be primary attribute, Employee Name must contain not null constraint etc. After complete this database development, database will be  used to store and maintain the organization records according to the client plan.

Strategic Database Planning