SQL and Relational Model: Understanding Data Definition and Relationships using SQL, Slides of Database Management Systems (DBMS)

Download SQL and Relational Model: Understanding Data Definition and Relationships using SQL and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

SQL Data Definition

SQL

• Originally ‘Sequel’ - Structured English query Language, part of an IBM project in the 70’s
• Sequel was already taken, so it became SQL - Structured Query Language - ANSI Standards - SQL- - SQL-92 (SQL2) - SQL-99 (SQL3) - Most modern DBMS use a variety of SQL - Most based on SQL2, increasingly SQL - Few (if any) are true to the standard

Notes

• SQL is (usually) not case-sensitive, but we’ll write SQL keywords in upper case for emphasis
• SQL statements will be written in BOLD COURIER FONT - Strings in SQL are surrounded by single quotes: 'I AM A STRING' - Single quotes within a string are doubled: 'I''M A STRING' - The empty string:''

Non-Procedural Programming

• SQL is a declarative (non-procedural) language - Procedural - say exactly what the computer has to do - Non-procedural – describe the required result (not the way to compute it) - Example: Given a database with tables - Student with attributes ID, Name, Address - Module with attributes Code, Title - Enrolment with attributes ID, Code - Get a list of students who take the module ‘Database Systems’

Non-Procedural (SQL)

SELECT Name FROM Student, Enrolment

WHERE

(Student.ID = Enrolment.ID)

AND

(Enrolment.Code =

(SELECT Code FROM Module WHERE

Title = ‘Database Systems’))

SQL, the Relational Model,

and E/R Design

• SQL is based on the relational model - It has many of the same ideas - Databases that support SQL are often described as relational databases - It is not always true to the model - E/R designs can be implemented in SQL - Entities, attributes, and relationships can all be expressed in terms of SQL - Many-to-many relationships are a problem, so should be removed

Implementing E/R Designs

• Given an E/R design
  • The entities become SQL tables
  • Attributes of an entity become columns in the corresponding table
  • Relationships may be represented by foreign keys

Enrolment

Student

Module

In

Has

ID

Code

Title

Name

Address

Year

Exam Assignment

Assignment

Exam

Credits

Entities and Attributes

• Each entity becomes a table in the database - The name of the table is often the name of the entity - The attributes become columns of the table with the same name - A table called Student - With columns for ID, Name, Address, and Year

Student

ID

Name

Address

Year

Column Definitions

<col-name>

[NULL|NOT NULL] [DEFAULT ] [constraint-1 [, constraint-2[, ...]]]

• Each column has a name and a type
• Common types
  • INT
  • REAL
  • CHAR(n)
  • VARCHAR(n)
  • DATE

Column Definitions

• Columns can be specified as NULL or NOT NULL
• NOT NULL columns cannot have missing values
• If neither is given then columns are assumed NULL - Columns can be given a default value - You just use the keyword DEFAULT followed by the value, eg:

num INT DEFAULT 0

Constraints

CONSTRAINT

• Common s
  • PRIMARY KEY
  • UNIQUE
  • FOREIGN KEY
  • INDEX
    • Each constraint is given a name - Access requires a name, but some others don’t
    • Constraints which refer to single columns can be included in their definition

Primary Keys

• Primary Keys are defined through constraints
• A PRIMARY KEY constraint also includes a UNIQUE constraint and makes the columns involved NOT NULL - The
  for a primary key is a list of columns which make up the key

CONSTRAINT PRIMARY KEY (col1, col2, …)

Example

CREATE TABLE Student (

stuID INT NOT NULL, stuName VARCHAR(50) NOT NULL, stuAddress VARCHAR(50), stuYear INT DEFAULT 1, CONSTRAINT pkStudent PRIMARY KEY (stuID))

Relationships

• Depends on the type
  • 1:1 are usually not used, or can be treated as a special case of M:
  • M:1 are represented as a foreign key from the M-side to the 1
  • M:M are split into two M:1 relationships

Enrolment

Student

Module

In

Has

ID

Code

Title

Name

Address

Year

Exam Assignment

Assignment

Exam

Credits