SQL

Page 1

DATA VS DATABASE

Data - anything that can be stored digitally

Database - stores data. A place that stores an organised collection of data. It is an electronic system that allows us to store data, grants us easy access to data, manipulate the data or retrieve it efficiently.

- consists of storage, memory (RAM), processor (CPU) and software.

- a database is a software that you install on your computer that organises our data systematically.

Page 2

DATABASE MANAGEMENT SYSTEMS

Collection of programs that enable users to access the database and manipulate and retrieve data for the users.

4 types of DBMS:

Heirarchical DBMS: rarely used, uses parent-child relationships

Network DBMS: uses many-to-many relationships, results in complex database structures, also not really used

Relational DBMS: most used, (Oracle, MySQL, MSQL) does not support many-to-many relationships, has entities and we establish relationships between these entities, has predefined types and you can store only these types

Object-Relational DBMS: supports storing new data types, you can have your own objects and each object has a type in it, objects have attributes (Postgre SQL)

Page 3

WHAT IS A TABLE?

Data in Oracle is stored in tables.

Horizontal - called records or rows, includes all information of a specific element, can have any type of data

Vertical - called columns, can only have one type of data, eg string values for names

Collection of all values is called a table.

Table stores data in tabular form.

Database has many different types of objects, but main object is tables

Page 4

Page 5

RELATIONAL DBMS

Main table consists of many columns, eg Department. You can create a new table using IDs. So Department column in main table has now been broken up into a new table consisting of for example 4 new columns with data related to Department, eg Dept ID, Dept Name, Location ID, etc. This creates a relationship between these 2 tables. Group data into different tables and establish some relationships between these tables by parent-child relationships.

To retrieve data from multiple tables, join the tables via parent-child relationships (often using ID) and retrieve as one result.

In main table, only Department ID is stored. The actual data about the department is stored in Departments Table.

Page 6

VARIOUS

Entity - smallest unit containing a meaningful set of data is called an entity (rows, columns, tables). Entities have attributes.

Pluggable Database - before pluggable database was introduced by oracle in recent update, each database had to be stored on a separate server. Oracle updated its architecture and now there are pluggable databases in one container database.

Page 7

OBJECTS

Oracle database has many objects in it. Can be catergorised into 2 subjects: Schema and Non-Schema.

TYPES OF OBJECTS:

Table: base unit of the database to store data, formatted with columns and rows.

Constraints: rules for restricting invalid data entry into tables.

Index: used for improving speed of data retrieval from tables, works automatically.

Sequence: database objects that generate unique integers, can be used by multiple users/tables.

Synonym: alternate name (alias) for database objects. References to original object.

View:

virtual table that provides access to a subset of columns or some restricted rows from one or more tables.

Viws are just a subscript of a table with a name like a table, and when you query from a view, the view executes that query and retrieves the data returning from that query. Views act like a real table, but do not physically use any space in database for returning the data. There is no data it in and it is just a stored SQL query.

Schema Objects:

Collection of logical structures of data or objects. There are many schema objects. Schema objects are logical structures created by the users, and there can be many.

A database user has only one schema with the same name as its username. Eg user HR as schema name HR and username HR.

Non-Schema Objects: