CS代考 COMP2420/COMP6420 INTRODUCTION TO DATA MANAGEMENT, ANALYSIS AND SECURITY

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 – WHERE

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