CMPT 258 Database SystemsThe Relationship Model(Chapter 3)

Relational Database: Definitions•Relational database: a set of relations

with distinct relation names▫Database: a set of tables with distinct

table names

Relational Database: Definitions•Relation (table): made up of 2 parts:

▫Schema : specifies name of relation (table), plus name and type of each column. E.G. Students(sid: string, name: string, login:

string, age: integer, gpa: real).▫Instance : a table, with rows and columns.

Can think of a relation as a set of rows or tuples (i.e., all rows are distinct).#Rows = cardinality, #fields = degree

Example Instance of Students Relation

sid name login age gpa

53666 Jones jones@cs 18 3.4

53688 Smith smith@eecs 18 3.2 53650 Smith smith@math 19 3.8

Cardinality = 3, degree = 5, all rows distinct Do all columns in a relation instance have to

be distinct?

TUPLES(RECORDS, ROWS)

FIELDS (ATTRIBUTES, COLUMNS)

Relational Query Languages

•A major strength of the relational model▫supports simple, powerful querying of data.

•Structured query language (SQL)•Developed by IBM in the 1970s•SQL Standard (ANSI) since it is used by

many vendors

6

The SQL Query Language• DDL (Data Definition Language)

▫A subset of SQL that supports the creation, deletion, and modification of tables

▫Commands CREATE, DROP, and ALTER are used for data definition

• DML (Data Manipulate Language)▫The SELECT-FROM-WHERE structure of SQL

queriesSELECT <attribute list>FROM <table list>WHERE <condition>

DDL

•Create database•Create table•Alter table•Drop table

7

Creating Databases

•Create database db_name;▫A new database will be created if not

exists.▫e.g. Create database db_university;

•Use db_name;▫e.g. Use db_university;

Creating tables

Creating Tables in SQL

• Creates the Students relation. ▫Observe that the type

(domain) of each field is specified, and enforced by the DBMS whenever tuples are added or modified.

• As another example, the Enrolled table holds information about courses that students take.

CREATE TABLE Students(sid CHAR(20), name CHAR(20), login CHAR(10), age INTEGER, gpa REAL)

CREATE TABLE Enrolled(sid CHAR(20), cid CHAR(20), grade CHAR(2))

Data Types and Domains

•Basic data types▫Numeric data types

Integer numbers: INTEGER or INT, and SMALLINT

Floating-point (real) numbers: FLOAT and REAL or DOUBLE PRECISION

▫Character-string data types Fixed length: CHAR(n)or CHARACTER(n) Varying length: VARCHAR(n)or CHAR VARYING(n), CHARACTER VARYING(n)

Data Types and Domains▫Bit-string data types

Fixed length: BIT(n) Varying length: BIT VARYING(n)

▫Boolean data type Values of TRUE or FALSE or NULL (the Boolean data is UNKNOWN)

▫DATE data type Ten positions Components are YEAR, MONTH, and DAY in the

form YYYY-MM-DD

DML: Adding Tuples•Can insert a single tuple using:

▫INSERT INTO TABLE_NAME [ (col1, col2, col3,...colN)] VALUES (value1, value2,value3,...valueN);

• INSERT INTO Students (sid, name, login, age, gpa) VALUES (53688, ‘Smith’, ‘smith@ee’, 18, 3.2)

• We can optionally omit the list of column name in the INTO clause and list the values in the appropriate order.

DML: Adding Tuples• INSERT INTO Students (sid, name, login, age,

gpa) VALUES (53688, ‘Smith’, ‘smith@ee’, 18, 3.2)

• INSERT INTO Students VALUES (53688, ‘Smith’, ‘smith@ee’, 18, 3.2)

Altering Tables• ALTER TABLE tbl_name• ADD COLUMN col_name domain• DROP COLUMN col_name

• e.g., ALTER TABLE Students • ADD COLUMN firstYear integer• The schema of Students is altered by adding

a new field; every tuple in the current instance is extended with a null value in the new field.

DML: updating data within a table•Modify attribute values of one or more

selected tuples•Additional SET clause in the UPDATE

command ▫Specifies attributes to be modified and new

values

• UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... [WHERE condition]

The WHERE Clause

•Logical comparison operators▫=, <, <=, >, >=, and <>

•Projection attributes▫Attributes whose values are to be retrieved

•Selection condition▫Boolean condition that must be true for any

retrieved tuple

DML: updating data within a table• UPDATE table_name

SET column_name1 = value1, column_name2 = value2, ... [WHERE condition]

DML: updating data within a table

DML: updating data within a table

Exercises

•Use database university•Create another table called Enrolled.

▫Enrolled(sid, cname)•Add two tuples each for Joe and Paul•Add another column called grade in

Enrolled with a default value of ‘B’•Change Joe’s databases grade to ‘A-’•Change Paul’s data structures grade to ‘A’

• Use AND to combine two or more conditions in the WHERE clause

• Is it case sensitive in MySQL when comparing strings?

• What if we want everyone in the databases class to get an ‘A’?

• What happens when we omit the WHERE clause?

• Joe dropped Data Structures and he took Algorithms instead and he got a D :(

• What is S.sid?

DML: Deleting Tuples• Removes tuples from a relation

▫Includes a WHERE clause to select the tuples to be deleted

• DELETE FROM table_name [WHERE condition];

•What happens when we omit the where clause?

DML: Deleting Tuples• Can delete all tuples satisfying some condition

(e.g., name = Smith):▫DELETE FROM Students

WHERE name = ‘Smith’

What if nothing satisfies the condition?

Destroying Tables

•DROP TABLE tbl_name;▫e.g., DROP TABLE Students;▫e.g., DROP TABLE Enrolled;

•Destroys the relation Students. The schema information and the tuples are deleted.

Basic SQL Queries

•SELECT statement▫One basic statement for retrieving

information from a database

Basic SQL Queries

•To find all 18 year old students, we can write:

SELECT *FROM Students WHERE age=18

sid name login age gpa

53666 Jones jones@cs 18 3.4

53688 Smith smith@ee 18 3.2

Specify an asterisk (*)Retrieve all the attribute values of the selected tuples

Basic SQL Queries

SELECT *FROM Students

Missing WHERE clause Indicates no condition on tuple selection

Basic SQL Queries

•To find the names and logins of all 18 year old students :

•Find all the ids of students and courses in which they got an A.

SELECT name, loginFROM Students WHERE age=18

Querying Multiple Relations

• Find all the names of students and courses in which they got an A.

SELECT S.name, E.cidFROM Students S, Enrolled EWHERE S.sid=E.sid AND E.grade=‘A’

S.name E.cid

Smith Topology112

sid cid grade53831 Carnatic101 C53831 Reggae203 B53650 Topology112 A53666 History105 B

Given the following instances of Enrolled and Students:

we get:sid name login age gpa

53666 Jones jones@cs 18 3.453688 Smith smith@eecs 18 3.253650 Smith smith@math 19 3.8

Ambiguous Attribute Names

•Same name can be used for two (or more) attributes ▫As long as the attributes are in different

relations▫Must qualify the attribute name with the

relation name to prevent ambiguitySELECT S.name, E.cidFROM Students S, Enrolled EWHERE S.sid=E.sid AND E.grade=‘A’

•CROSS PRODUCT▫All possible tuple combinations

SQL

•Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:▫CREATE - to create objects in the

database▫ALTER - alters the structure of the

database▫DROP - delete objects from the database

33

SQL

•Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:▫SELECT - retrieve data from the a

database▫INSERT - insert data into a table▫UPDATE - updates existing data within a

table▫DELETE - deletes all records from a

table, the space for the records remain

34

Readings

•Chapter 3