Better Schema - Database Management Systems - Lecture Slides, Slides of Introduction to Database Management Systems

Download Better Schema - Database Management Systems - Lecture Slides and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database Management Systems Design

Better Schema

• Students (sid:string, sname:string, saddr:string, sage:integer, sgpa: float);
• Courses (cname:string,ccreds:integer,clevel:string, cdesc:string);
• Offering (cname:string,csection:integer,cterm:stri ng, croom:string, ccapacity:integer);
• Professor (pid:string, pname:string, pdept:string, psalary:float, prank:string);
• Taking (sid:string, cname:string, csection:integer, cterm:string, cgrade:string);
• Teaching (pid:string, cname:string, csection:integer, cterm:string);

Structured Query Language (SQL)

• Developed by IBM as part of System-R project
• Became widely used by early DBMS prototypes
• De-facto standard, then became ANSI Standard
  • SQL-86, SQL-89, SQL-92, SQL-
• SQL is based on Relational Algebra
• SQL specifies the query to be sent to DBMS
  • Declarative language
• Modes of use:
  • String send from Command prompt
  • String send from API in a Programming Language
  • Embedded within a Programming Language

Basic Structure

• SQL queries have the following form: SELECT A1, A2, …,An FROM R1, R2, …, Rk WHERE P
  • Projection List: A1, A2, …,An
  • Table List: R1, R2, …, Rk
  • Selection condition: Predicate P
• The result is not really a relation because duplicates are permitted
• If we remove duplicates, the expression is: π A 1, A 2,..., An (σ (^) P ( R 1 × R 2 ×... Rk ))

SELECT Clause and duplicates

• SQL does not remove duplicates by default SELECT sname FROM Students
  • You need to tell it to do
• Use the keyword distinct for this purpose: SELECT distinct sname FROM Students
  • This will only return one of each name in the database

FROM Clause

• FROM is used to specify the tables that are accessed in the query SELECT sid,sname,sgpa FROM Students
• Unless equi-join condition is given in where clause, FROM clause forms a Cartesian product: SELECT sid, sname, sgpa, cname, csection FROM Students, Taking - This query forms a Cartesian product of the relations Students and Taking

Examples of SQL Queries

• Get the name and id for all students under 21 with a gpa of at least 3. SELECT sname, sid FROM Students WHERE sage < 21 AND sgpa >= 3.
• Get all the information about courses taught during the Spring of 2002 SELECT * FROM Offering WHERE cterm = ‘Spring 2002’

Examples of SQL Queries

• Get the distinct names of courses being taught in room S- SELECT distinct cnames FROM Offering WHERE croom = ‘S113’
• Get the name, and id of professors making less than 30000 SELECT pid, pname FROM Professors WHERE psalary < 30000

Example of between clause

• Get all the information about students whose gpa is between 3.70 and 4. SELECT * FROM Students WHERE sgpa BETWEEN 3.70 and 4.
• Get the names of all professors whose salary is a between 40000 and 70000 SELECT pname FROM Professors WHERE psalary BETWEEN 40000 AND 70000

Rename Operator

• The rename operator in SQL allow you to
  • Rename a Table
  • Rename individual attributes
  • General Form: old-name AS new-name
• Examples: SELECT sname AS Name, sage AS Age FROM Students

SELECT R.pid, R.pname, R.psalary FROM Professors as R WHERE R.psalary >= 80000 AND R.name <> ‘Joe Smith’ Docsity.com

Joins

• In SQL Joins come in two flavors:
  • Those done with the SELECT clause
  • Those done with the JOIN clause.
• SELECT clause is often used for equi-join
  • You need to project what you want “by hand”
• JOIN clause behaves like the join operation in relational algebra
• There is also an OUTER JOIN clause.
• It is recommended to use the various JOIN clause since the query looks more clean.

Joins with the SELECT clause

• Simply write an Equi-join in the WHERE clause
• Example: Get the name and id of all students taking ICOM 4035 during the Fall of 2002

SELECT S.sid, S.name FROM Students as S, Taking as T WHERE S.sid = T.sid AND T.cname = “ICOM 4035” AND T.cterm = “Fall 2002”

Joins with JOIN Clause

• Join Clause comes in three flavors:
  • Equi-join
  • Natural join
  • Theta join
• The key word is : inner join
• General form for Equijoin
  • R1 inner join R2 using (A1,A2,A3) : equi-join on A1,A2,A
• General form for Natural Join
  • R1 natural inner join R2 : natural join between R1Docsity.com

Equijoin

• Get the name, id and gpa of all students that have taken ICOM 6005 SELECT R.sname, R.sid, R,sgpa FROM (Students inner join Taking using (sid)) AS R WHERE R.cname = “ICOM 6005”
• Get the name and id of all professors making more than $50,000 that teach MATE 4040 SELECT R.pname, R.pid FROM (Professor inner join Teaching using (pid)) AS R WHERE R.psalary > 50, AND R.cname = “MATE 4040”