RECORD THE LECTURE
RELATIONAL MODEL AND SQL
COMP2420/COMP6420 INTRODUCTION TO DATA MANAGEMENT, ANALYSIS AND SECURITY
Copyright By PowCoder代写 加微信 powcoder
WEEK 7 – LECTURE 1 Tuesday 22 April 2022
of Computing
College of Engineering and Computer Science
Credit: (previous course convenor)
HOUSEKEEPING
Midsemester Exam
• Thursday 21 April at 1pm (Canberra time)
• 15 mins reading time and 90 minutes writing time
• Screen capture set-up required (test it well in advance e.g. on sample exam)
• Check sample exam on course site
• Save , commit, push regularly
• The last PUSH time before the deadline is the version that will be considered
Decision Tree Lecture recording
• Over long weekend, a recording of the Decision Tree lecture (week 5) was uploaded to Echo360.
• Slides also updated (errors corrected in slides 22 and 24).
Upcoming public holidays
• Easter Monday 18 April
• ANZAC Day Monday 25 will be no lectures. A make-up session will be arranged on the corresponding Tuesday at 2pm instead to record a lecture, we will run it live and students are welcomed to join in. Your tutors will contact you about make-up labs. Contact them if you have not heard from them yet.
Describe the relational model as applied to storing data in databases.
Explain the relational algebra that applies to relational databases.
03 04 05 06
Outcomes you should be able to:
01 By the end of this seminar,
Discuss what SQL is and its relevant features.
Distinguish between different SQL queries.
Apply SQL queries in practice to perform various relational algebraic operations on data.
Reflect on the data access abilities that SQL provide.
Different types of database models
• AscoveredinWeek6 • Whatarethey?
Different types of database models
• Hiarchical
• Relational
• Object-oriented • Graph
Database Management System (DBMS)
• Consistsofinterrelateddataand software for analysing the data.
• Enablesthedefinition,creation, updating, querying and administration of databases
• Allowsforsecuredataaccess
• MySQL,Postgres,EnterpriseDB, MongoDB, Microsoft SQL Server, Oracle, SQLite and IBM DB2
THE RELATIONAL MODEL
Inventor – relational model (1969)
Attribution:
Characteristics
• Arelationisexpressedasasetof tuples.
• Eachtupleisalistofcomponents.
• Eachrelationhasafixedarity.
• Informationisstoredintables.Arow is a tuple and the columns are attributes or components of the tuple.
Example – Relational database
Primary Key? Foreign Key?
Attribution:Relational
SQL Engine
Attribution:SQL Engine
RELATIONAL ALGEBRA
Basic operations
Proposed by Codd as a basis for database query languages.
1. Union (∪)
2. Intersection (∩) 3. Difference (−) 4. Selection (𝜎)
5. Projection (𝛱) 6. Join (⋈)
Attribution:Definitions from Caltech course CS121
𝑅 = { 𝑎!,𝑏!,𝑐! , 𝑎”,𝑏”,𝑐” ,…, 𝑎#,𝑏#,𝑐# } 𝑆𝑐h𝑒𝑚𝑒 = {𝑎, 𝑏, 𝑐}
• Result contains all tuples from R and S
– Each tuple is unique, even if it is both in R and S • R and S must have compatible schemas
– R and S must have the same arity
– Each corresponding attribute in R and S must have the same domain
Union example
Intersection
• R-S=therowsinRbutnotinS
• R-(R-S)=therowsinbothRandS • Schemas must be compatible
Intersection example
Difference • R−S
• Result contains tuples that are only in R, but not in S
• Schemas must be compatible
Difference example
• P is the predicate for selection
• P can refer to attributes in R, as well as literal values
• Can use comparison operators: =, ≠, <, ≤, >, ≥
• Can combine using multiple predicates: ∧,∨, ≠
• Result relation contains all tuples in R for which P is true
• Result schema is identical to schema for R
Selection example
Projection
𝛱%,’,… 𝑅
• ResultcontainsonlythespecifiedattributesofR –Specified attributes must actually be in schema of R –Result’s schema only contains the specified attributes –Domains are same as source attributes’ domains
• ResultmayhavefewerrowsthanR.Why?
Projection example
Natural Join
• Result is the set of all combinations of tuples in R and S that are equal on their common attribute names.
• If there is no common attribute constraint, then this becomes the Cartesian product.
• Result is projected so that common attributes only appear once.
Natural join example
A query is used to retrieve information from a database. It is specified using a query language. The most widely used query language is SQL.
What type of language is SQL – Procedural or Declarative?
Data- manipulation language
• Used to access/manipulate data. Types of access are:
• Retrieve, Insert, Delete and Modify
Two basic types:
• Procedural
Need to specify what data is required and how to get that data
• Declarative
Just specify what data is needed. Not how to get that data
Introduction
• Original Language was SEQUEL
– IBM’s System R Project (early 70’s)
– Structured English Query Language
• Simple, but powerful, declarative language for querying and manipulating data
• Many standards available (ANSI/ISO)
– ANSI SQL, SQL 92, SQL 99, …
– Vendors loosely follow standards. Portability issues.
• Data Definition Language (DDL)
– Specify relational schemas
– Create/alter/delete tables and their attributes
• Data Manipulation Language (DML)
– Generally based on relational algebra
– Select/Insert/update/modify/delete data in tables
– Query tables
• Can also define views, transactions, etc
Will look at the DML part of SQL first.
SQL Engine
Attribution:SQL Engine
SQL Data Types
• Atomic types
– Characters: CHAR(20),
VARCHAR(50)
– Numbers: INT, BIGINT, SMALLINT, FLOAT, NUMERIC
– Others: MONEY, DATETIME, TIMESTAMP, BLOB, CLOB, TEXT
• Every attribute must have an atomic type. Hence tables are flat.
SQL queries
• UsetheSELECTstatement
• VerycentralpartofallSQLlanguage • Basicform
–SELECT
–FROM
SQL Query 1
SQL query 1
SQL Query 2
SQL query 2
SQL Query 2a
SQL query 2a
SQL query 3
SQL Query 4
SQL query 4
SQL Query 5
SQL query 5
SQL Query 6
SQL query 6
Query 7 – anything wrong in the result?
SQL query 7
Query 7 – issue
SQL query 7 issue
Query 7 – corrected
SQL query 7 corrected
Query 8 – (INNER) JOIN
SQL query 8
Query 8a – LEFT JOIN
SQL query 8a
Query 8b – RIGHT JOIN
SQL query 8b
Query 8c – OUTER JOIN
SQL query 8c
Query 9 – SELF JOIN & ALIASES
SQL query 9
Attribution:SQL JOINS
SQL query 10
SQL query 10a
SQL query 10b
SQL query 10c
SQL query 10d
SQL query 10e
SQL query 11
SQL query 12
Query 13 – HAVING (like WHERE, for aggregates)
Difference between WHERE and HAVING
SQL query 13
Query 14 – ORDER BY (sorting)
SQL query 14
Query 15 – LIMIT
SQL query 15
• Relational model and various relational algebraic operations were presented.
• The use of SQL to write queries that implement relational algebraic operations.
• Practice to develop understanding.
Live coding
Mindika will do the live coding session next.
Additional resources
• Guru99(2013)WhatisDatabaseand SQL?
• Socratica(2019)SQLforbeginners
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com