Introduction to SQL #1

IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory. It renamed as SQL short for Structured Query Language. But still, it can be called as Sequel. Commercial systems offer most, if not all, SQL-92 features, plus varying feature sets from later standards and special proprietary features. So not all examples here may work on your particular system. FYI, I will use SQLite from now on. :)

The SQL data-definition language(DDL) allows the specification of information about relations, including the schema for each relation, the domain of values associated with each attribute, integrity constraints and other information such as the set of indices to be maintained for each relation, security and authorization information for each relation and the physical storage structure of each relation on disk.


Domain Types in SQL

• char(n): Fixed-length character string, with user-specified length n.
• varchar(n): Variable length character strings, with user-specified maximum length n.
• int: Integer - a finite subset of the integers that is machine-dependent -
• smallest: Small integer - a machine dependent subset of the integer domain type -
• numeric( p, d): Fixed point number. with the user-specified precision of p digits, with d or the p digits to the right of decimal point.
• ex) numeric( 3, 1): 44.5 to be stored exactly, but, neither 444.5 nor 0.32 can be stored exactly
• real, double precision: Floating point and double-precision floating point numbers, with machine-dependent precision.
• float(n): floating point number, with a user-specified precision of at least n digits.
• ...

Create Table Construct

An SQL relation is defined using the create table command: 

ex) create table instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8, 2))

If you want to insert the data into the table using this command:

ex) insert into instructor values ( '10211', 'Smith', 'Biology', 66000)

And * means all, so the command - select * from 'table name' - will show us the whole table. I ran the insert row twice so there are 2 Davids in the table.

If you want to delete the table, use 'drop' command.

ex) drop table instructor

Integrity Constraints in Create Table

Command [ not null ] guarantee the variable cannot be the null value.

Also, there are commands about primary key and foreign key. For example, declare dept_name as the primary key for 'department'. Primary key declaration on an attribute automatically ensures not null. In case of the foreign key, the values of attributes (Am, ..., An) for any tuple in the relation must correspond to values of the primary key attribute of some tuple in relation r. For example, declare dept_name as the foreign key for 'instructor'. For each 'instructor' tuple, the department name specified in the tuple must exist in the primary key attribute (dept_name) of the department relation.

Relational Model #2

Relational Query Languages

• Selection of tuples



Relation r



after selection




• If I want to select tuples with A=B and D>5 in relation r,
• σA=B and D>5 (r)

• Selection of Attributes

• Projection
• Π A, C (r)

after projection

• Cartesian Product - Cross product!

relations r and s

•  Cartesian product returns every possible pair in the result.

r ╳ s

• Union of two relations

relations r and s

• r ∪ s =

Union of r and s

• Set difference between two relations

relations r and s

• r - s = 

differences

• Set intersection between two relations

relations r and s

• r ∩ s =

intersection

• Natural Join
• Let r and s be relations on schemas R and S respectively. Then, the 'natural join' of relations R and S is a relation on schema R∪ S obtain as follows:
1. Consider each pair of tuples tr from r and ts form s.
2. If tr and ts  have the same value on each of the attributes in R∩ S, add a tuple t to the result, where
• t has the same value as tr on r
• t has the same value as ts on s

relations r and s

•  r ⋈ s

Natural join r and s

Relational Model #1

Attribute Types

• The set of allowed values for each attribute is called the domain of the attribute.
• Attribute values are (normally) required to be atomic; indivisible.
• The important issue is not what the domain itself is, but rather how we use domain elements in our database.
• ex) Phone number
• not splitting the value into a country code, an area code, and a local number,
• treating it as a nonatomic value
• The special value null is a member of every domain
• The null value causes complications in the definition of many operations.

Relation Schema and Instance

• If A1, A2, ..., An are attributes, R=(A1, A2, ..., An) is a relation schema.
• ex) Instructor = (ID, name, dept_name, salary)

Formally, given sets D1, D2, ..., Dn a relation r is a subset of D1*D2*D3...*Dn. Thus, a relation is a set of n-tuples (a1, a2, a3, ..., an) where ai∈Di

• The current values (relation instance) of a relation are specified by a table
• An element t of r is a tuple. represented by a row in a table

Database

• A database consists of multiple relations
• Information about an enterprise is broken up into parts - instructor, student, advisor
• Bad design: univ(instructor-ID, name, dept_name, salary, student_ID, ...) results in
• repetition of information
• the need for null values
• Good schemas? => Normalization theory in Chapter 7!

Keys

• Let K⊆R
• K is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R)
• ex) {ID} and {ID, name} are both superkeys of instructor.
• Superkey K is a candidate key if K is minimal
• ex) {ID} is a candidate ket for the relation named Instructor
• One of the candidate keys is selected to be the primary key.
• determined by the database administrator
• Foreign key
• A relation r1 may include among its attributes the primary key of another relation r2. This attributes the primary key of another relation r2. This attribute is called a foreign key from r1, referencing r2.
• ex) If there are 2 relations Instructor( ID, name, dept_name, salary) and Department( dept_name, building, budget), dept_name is a foreign key from Instructor, referencing department, since dept_name is the primary key of Department.
• In the schema diagram, foreign key dependencies appear as arrows from the foreign key attributes of the referencing relation to the primary key of the referenced relation.

Schema Diagram for University Data

• classroom( building, room number, capacity)
• department(dept_name, building, budget)
• course(course_ID, title, dept_name, credits)
• instructor(ID, name, dept_name, credits)
• section(course_id, sec_id, semester, year, building, room_number, time_slot_id)
• teaches(ID, course_id, sec_id, semester, year, grade)
• student(ID, name, dept_name, tot_cred)
• takes(ID, name, dept_name, semester, year, grade)
• advisors(s_ID, i_ID)
• time_slot(time_slot_id, day, start_time, end_time)
• prereq(course_id, prereq_id)