Lab 1

From bd_en
Jump to: navigation, search

Objectives:

  • Understanding the relational model for databases
  • An overview of the SQL language


The relational model

  • is a simple model with efficient implementations and with a simple and expressive query language (SQL);
  • proposed in '70s by Edgar Frank Codd, it is used by the most popular DBMSs: http://db-engines.com/en/ranking_trend.

Features:

  • The database consists of a set of tables/relations;
  • Every table/relation has a unique name in the database it belongs to and contains a set of columns/attributes/fields;
  • Every attribute has a unique name in the table it belongs to and can take values to a specific domain it is associated to;
  • Every line/record/tuple in a table has values for each attribute of the table (there are no missing values, when a value does not exist the NULL value is filled in);
  • The order of the lines/tuples does not matter.

The schema of the database is actually its structural description: table names and table definitions (attributes and associated types).

A database instance represents the content of the database at a given time.

NULL – is a special value used when we do not know an attribute value for a given record.

Candidate key – an attribute which takes unique values for every row in the table - or a set of attributes whose combination of values are unique (uniquely identifies each record in the table), minimal with regard to this property (by excluding any attribute from this set we cannot uniquely identify each tuple); by adding any other attribute to a candidate key we obtain a super-key.

Primary key – one of the candidate keys, designated to act as the main key for identifying the records; none of its attributes can be NULL.

Alternate key – any candidate key, not designated as primary.

Foreign key – an attribute or a set of attributes in a relation that references a candidate key in other table (or even in the current table, case which corresponds to a recursive foreign key).

Information is extracted from the database by using queries wirtten in SQL. The result of a query is also structured as a table.

Relational algebra

  • A formal language
  • Basic operators: selection, projection, cartesian product, intersection, difference, renaming
  • Based on sets and not bags (duplicates are eliminated)

The SQL language

SQL - Structured Query Language

  • the language used to manipulate relational databases
  • a declarative language: expresses WHAT one wants and NOT HOW one obtains it
  • a language that implements the operators of the relational algebra (its semantics is defined by the relational algebra) but based on multi-sets/bags (in a relation there may exist duplicates)
  • although considered a non-procedural language, most RDBMSs offer procedural extensions.

The SQL language was standardized for the first time by ISO in 1987. Since then, the language was subject to many changes/improvements (called revisions), the latest stable version being SQL:2016.

Although standardized at international level, every DBMS provider deviated from and extended the standardized version to offer some advantages. Because of its large size, some providers did not implement all the features present in the standard. On the contrary, there might be the case that the standard has not defined some behaviors, the decisions being left to developers. In these situations there exist today SQL dialects. This phenomenon leads to incompatibilities between various relational DBMSs. Some incompatibilities may be preserved on purpose in order to retain gained customers.

The Oracle's conformance claims can be accessed at [[https://docs.oracle.com/database/121/SQLRF/ap_standard_sql003.htm#SQLRF55516]

In the following we list the most popular SQL commands, using Backus-Naur syntax where:

  • <> corresponds to a substitution variable
  • | signifies disjunction
  • […] contains optional items
  • {…|…|…} forces the user to choose exactly one item from the list

DDL (Data Definition Language)

Creating tables

CREATE [{GLOBAL | LOCAL} TEMPORARY] TABLE
       <table_name>
        (<column_name1> {data_type | domain_name} [constraints] 
        [,<column_name2> {data_type | domain_name} [constraints]]
        […]
        )     
       [ ON COMMIT { DELETE | PRESERVE } ROWS ]


The TEMPORARY option creates a temporary table, which stores records only during a transaction (until a COMMIT command is triggered), or during a session (in case ON COMMIT PRESERVE ROWS option is used). In Oracle we have only GLOBAL temporary tables: the definition of the temporary table is available from all the sessions but the access to data is private (the records inserted in one session can be accessed only from that session).


Deleting tables

DROP TABLE <table_name>  [RESTRICT | CASCADE]

CASCADE forces dropping a table which is referenced by a view or by other foreign keys, propagating the drop command on these objects.

In Oracle a table is dropped using:

DROP TABLE <table_name>  [CASCADE CONSTRAINTS] [PURGE]

In Oracle the tables are recycled, unless the PURGE option is specified. They can be restored by using:

FLASHBACK TABLE <table_name>   TO 
     { { SCN | TIMESTAMP } expr 
     [ { ENABLE | DISABLE } TRIGGERS ] | 
     BEFORE DROP [ RENAME TO <nume_nou> ]} 

DML (Data Manipulation Language)

Inserting records

INSERT INTO <table_name> 
            [ (columns_list) ]
            VALUES (values_list)

Updating records

UPDATE <table_name>
       SET <column_name1> = value1
           [, <column_name2> = valoare2...]
       [WHERE condition]

Deleting records

 
DELETE FROM <table_name> 
      [WHERE condition]


Querying the database

SELECT [DISTINCT | ALL] {* | [column_expresion [AS <new_name>]] [,...] }
       FROM <table_name> [alias] [, ...]
       [WHERE condition]
       [GROUP BY columns_list] [HAVING condition]
       [ORDER BY exp1 [ASC/DESC] [,exp2...]] 

Datatypes in Oracle

Type Properties
CHAR(n) Strings of fixed length n (1 character is stored on 1 byte). If the string inserted by the user is shorter, the system inserts blanks at the end of the string; if the string to be inserted is longer, an error is triggered (VALUE_ERROR)
VARCHAR2(n) Strings of variable length, n is the maximum length. It should be used when the values to be inserted differ largely with regard to their length, because it allocates a number of bytes equal to the length of the inserted string.
NUMBER Stores numbers in range (-10126, 10126), infinity (positive şi negative).
NUMBER(n1, n2) Numbers with a total of n1 significant digits, n2 specifying the number of digits from the decimal point to the least significant digit. Ex. for NUMBER(5,-2), introducing 12345 generates 12300 while for 12399 generates 12400.
DATE A moment in time, storing the year, month, day, hours, minutes and seconds.
LOB (large object):
BLOB, CLOB, NCLOB, BFILE
Allows storing unstructured data like text, images, videos...

For compatibility with other systems, Oracle offers support for other types as well: decimal, integer, smallint, real; however, their internal representation is defined based on the number type.

A complete list can be accessed at [| http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm]

NULL is a special value indicating missing information. Both tests NULL=NULL and NULL<>NULL return FALSE. Testing for the NULL value must be done with IS [NOT] NULL.

The database schema - a case study

Propose a schema for a database that stores information about students (ID, first name, last name, year of study, group, scholarship, date of birth), teachers (id, first name, last name, academic title), courses (ID, title, year, semester, credits) and students' grades. Write SQL commands to create the tables, insert a few records make some updates. Query the database.