MGS 8020 Business Intelligence Relational Algebra and Structured

MGS 8020 Business Intelligence Relational Algebra and Structured Query Language Jul 1, 2017 Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 1

Agenda SQL Georgia State University - Confidential Queries Join MGS 8020_09. ppt/Jul 1, 2017/Page 2

Set Operations Unary Restriction Projection Binary Union Intersection ? Difference ? UNION Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 3

SQL Building Blocks • CREATE, ALTER, DROP • INSERT, DELETE, UPDATE • SELECT • UNION, INTERSECT, MINUS • JOIN • INDEX • VIEWS • Utilities (introduced throughout the examples). • Transaction Management Features • Additional Features Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 4

Horizontal Slices • Restriction – Specifying Conditions Unconditional List all students Algebra: selection or restriction (R) t select from * STUDENT; (Student) Conditional List all student with GPA > 3. 0 select * from STUDENT where GPA > 3. 0; t GPA > 3. 0 Georgia State University - Confidential (Student) MGS 8020_09. ppt/Jul 1, 2017/Page 5

Pattern Matching ‘%’ ‘_’ x any string with n characters, n>=0 any single character. exact sequence of string x. List all CIS courses. select * from COURSE where course# like CIS%’; List all CIS 3200 levelcourses. select * from COURSE where course# like ? ; Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 6

Specifying Conditions List all students in. . . select * from STUDENT where city in (‘Boston’, ’Atlanta’); List all students in. . . select * from STUDENT where zip not between 60115 and 60123; Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 7

Vertical Slices • Projection – Specifying Elements Algebra: projection <A 1, A 2, . . . Am> No Specification List all information about Students t select from * STUDENT; (Student) Georgia State University - Confidential (R) Conditional List IDs, names, and addresses of all students t select from ID, name, address STUDENT; ID, name, address (Student) MGS 8020_09. ppt/Jul 1, 2017/Page 8

Does SQL treat Relations as ‘Sets’ What are the different salaries we pay to our employees? select salary from EMPLOYEE; OR is the following better? select DISTINCT salary from EMPLOYEE; Is the following necessary? Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 9

Horizontal and Vertical Query: Lista all student ID, names and addresses who have GPA > 3. 0 and age >20. select from where order by ID, Name, Address STUDENT GPA > 3. 0 and DOB < ‘ 1 -Jan-6’ Name DESC; Algebra: ID, name, address ( GPA > 3. 0 and DOB < ‘ 1 -Jan-74’ (STUDENT) Order by sorts result in descending (DESC) order. Note: The defauld order is ascending (ASC) as in: order by Name; Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 10

Agenda SQL Georgia State University - Confidential Queries Join MGS 8020_09. ppt/Jul 1, 2017/Page 11

Relational Database A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. The relational database was invented by E. F. Codd at IBM in 1970. The standard user and application program interface to a relational database is the structured query language (SQL). SQL statements are used both for interactive queries for information from a relational database and for gathering data for reports. A relational database is a set of tables containing data fitted into predefined categories. Each table (which is sometimes called a relation) contains one or more data categories in columns. Each row contains a unique instance of data for the categories defined by the columns. For example, a typical business order entry database would include a table that described a customer with columns for name, address, phone number, and so forth. Another table would describe an order: product, customer, date, sales price, and so forth. A user of the database could obtain a view of the database that fitted the user's needs. For example, a branch office manager might like a view or report on all customers that had bought products after a certain date. A financial services manager in the same company could, from the same tables, obtain a report on accounts that needed to be paid. Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 12

Relational Database When creating a relational database, you can define the domain of possible values in a data column and further constraints that may apply to that data value. For example, a domain of possible customers could allow up to ten possible customer names but be constrained in one table to allowing only three of these customer names to be specifiable. The definition of a relational database results in a table of metadata or formal descriptions of the tables, columns, domains, and constraints. Meta is a prefix that in most information technology usages means "an underlying definition or description. " Thus, metadata is a definition or description of data and metalanguage is a definition or description of language. A database is a collection of data that is organized so that its contents can easily be accessed, managed, and updated. The most prevalent type of database is the relational database, a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. A distributed database is one that can be dispersed or replicated among different points in a network. An object-oriented programming database is one that is congruent with the data defined in object classes and subclasses. SQL (Structured Query Language) is a standard interactive and programming language for getting information from and updating a database. Although SQL is both an ANSI and an ISO standard, many database products support SQL with proprietary extensions to the standard language. Queries take the form of a command language that lets you select, insert, update, find out the location of data, and so forth. Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 13

Nesting Queries SELECT attribute(s) FROM relation(S) WHERE attr [not] {in | comparison operator | exists } ( query statement(s) ); List names of students who are taking “BA 201” select Name from Student where ID in ( select ID from REGISTRATION where course#=‘BA 201’); Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 14

Sub Queries List all students enrolled in CIS courses select name from STUDENT where studentnum in (select Student. Id from REGISTRATION where cno like ‘CIS%’); List all courses taken by Student (Id 1011) select cname from COURSE where cnum _ any (select cno from REGISTRATION where Student. Id = 1011); Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 15

Sub Queries Who received the highies grade in CIS 814 select Student. Id from GRADE where cnum = ‘CIS 814’ and grade >=all (select grade from GRADE where cno = ‘CIS 814’); List all students enrolled in CIS courses. select name from STUDENT S where exists (select * from REGISTRATION where Student. Id = S. Studentnum and cno like “CIS%’); Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 16

Recursive Queries List all employees who earn more than their immediate supervisor. select from where E. Emp#, E. title, E. salary EMPLOYEE, EMPLOYEE M E. salary > M. salary and E. Manager. Emp# = M. Wmp#; Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 17

Summaries and Aggregates Calculate the average GPA select from avg. (GPA) STUDENT, Find the lowest GPA select from min (GPA) as min. GPA STUDENT, How many CIS majors? select from where count (Student. Id) STUDENT major=‘CIS’; Discarding duplicates select from where avg (distinct GPA) STUDENT major=‘CIS’ Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 18

Aggregate Functions COUNT (attr) SUM (attr) AVG (attr) MAX (attr) MIN (attr) - a simple count of values in attr sum of values in attr average of values in attr macimum value in attr minimum value in attr t. Take effect after data are retrieved from the database t. Applied to either the entire resulting relation or groups t. Can’t be involved in any query qualifications (where clause) n Would the following query be permitted? select Student. Id from STUDENT where GPA = max (GPA); Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 19

Missing or Incomplete Information • List all students whose address or telephone number is missing: select from where Truth Table * STUDENT Address is null or GPA is null; T T T F T U F T F F F U U T U F U U ~a a&b a or b Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 20

Groupin Results Obtained Show all students enrolled in each course. select cno, stno from REGISTRATION group by cno; Is this grouping OK? Calculate the average GPA of students by county. select county, avg (GPA) as County GPA from STUDENT group by county; Calculate the average GPA of each class. select cno, term, year, count (stno) as enrol from REGISTRATION group by cno, year, term; Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 21

Selections on Groups n Show all CIS courses that are full. select cno, count (stno) from REGISTRATION group by cno having count (stno) > 29; Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 22

Union List students who live in Atlanta or GPA > 3. 0 select ID, Name, DOB, Address from STUDENT where Address = ‘Atlanta’ union select ID, Name, DOB, Address from STUDENT where GPA > 3. 0; Can we perform a Union on any two Relations ? Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 23

Union Compatibility Two relations, A and B, are union-compatible if 1) A and B contain a same number of attributes, and 2) The corresponding attributes of the two have the same domains Examples CIS=Student (ID: Did; Name: Dname; Address: Daddr; Grade: Dgrade); Senior-Student (SName: Dname; S#: Did; Home: Daddr; Grade: Dgrade); Course (C#: Dnumber; Title: Dstr; Credits: Dnumber) Are CIS-Student and Senior-Student union compatible? Are CIS-Student and Couse union compatible? What happens if we have duplicate tuples? What will be the column names in the resulting Relation? Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 24

Union, Intersect, Minus select CUSTNAME, ZIP from CUSTOMER where STATE = ‘MA’ UNION select SUPNAME, ZIP from SUPPLIER where STATE = ‘MA’ ORDER BY 2; B A select CUSTNAME, ZIP from CUSTOMER where STATE = ‘MA’ MINUS select SUPNAME, ZIP from SUPPLIER where STATE = ‘MA’ ORDER BY 2; Georgia State University - Confidential select CUSTNAME, ZIP from CUSTOMER where STATE = ‘MA’ INTERSECT select SUPNAME, ZIP from SUPPLIER where STATE = ‘MA’ ORDER BY 2; B A MGS 8020_09. ppt/Jul 1, 2017/Page 25

Union, Intersect, Minus Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 26

Agenda SQL Georgia State University - Confidential Queries Join MGS 8020_09. ppt/Jul 1, 2017/Page 27

Connecting/Linking Relations t. List information about all students and the classes they are taking Student Class What can we use to connect/link Relations? Join: Connecting relations so that relevant tuples can be retrieved. Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 28

Join Cartesian Product R 1 R 2 Student: 30 tuples Class: 4 tuples Total Number of Tuples in the Cartesian Product. ? (match each tuple of student to every tuple of class) Select tuples having identical Student Ids. Expected number of such Tuples: Join Selectivity Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 29

R 1 Join Forms • • • General Join Forms – Equijoin – Operator Dependent Natural Join Outer Join – Left – Right – Full select from where s. *. c. * STUDENT s, CLASS c s. ID = c. ID; R 1 select from where Georgia State University - Confidential R 2 s. *. c. * STUDENT s, CLASS c s. ID = c. ID (+); R 2 = x > y <>. . . MGS 8020_09. ppt/Jul 1, 2017/Page 30

Grouping Results after Join Calculate the average GPA of each class select from where group by course#, avg (GPA) STUDENT S, CLASS C S. ID = C. ID course#, Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 31

Index u There is no order among tuples u Indexes speed up data retrieval – Find all students who live in Atlanta. How many tuples would you have to search for this query? What if the table was ‘indexed’? Index Table Georgia State University - Confidential Student MGS 8020_09. ppt/Jul 1, 2017/Page 32

Creating and Deleting Indices CREATE [UNIQUE] INDEX index name ON base-relation-name ( attr-name [order], attr-name[order]. . . ) [CLUSTER]; create unique index student-id on STUDENT ( ID ASC ); create index Address-index on Student (Address); create unique index Name-Age-Index on STUDENT ( Name DESC, Age ); • What are the advantages & disadvantages of Indexing? Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 33

Relational Views • • • Relations derived from other relations. Views have no stored tuples. Are useful to provide multiple user views. View 1 View 2 Base Relation 1 Georgia State University - Confidential View N Base Relation 2 MGS 8020_09. ppt/Jul 1, 2017/Page 34

View Creation Create View view-name [ ( attr [ , attr ]. . . ) ] AS subquery [ with check option ] ; DROP VIEW view-name; – Create a view containing the student ID, Name, Age and GPA for those who are qualified to take 300 level courses, i. e. , GPA >=2. 0. Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 35

View Options • • With Check Option enforces the query condition for insertion or update To enforce the GPA >=2. 0 condition on all new student tuples inserted into the view • • A view may be derived from multiple base relations Create a view that includes student IDs, student names and their instructors’ names for all CIS 300 students. Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 36

View Retrieval Queries on views are the same as that on base relations. Queries on views are expanded into queries on their base relations. select Name, Instructor-Name from CIS 300 -Student where Name = Instructor-Name; Georgia State University - Confidential ? MGS 8020_09. ppt/Jul 1, 2017/Page 37

View: Update on a view actually changes its base relation(s)! update Qualified-Student set GPA = GPA-0. 1 where SID = ‘s 3’; insert into Qualified-Student values ( ‘s 9’, ‘Lisa’, 4. 0 ) insert into Qualified-Student values ( ‘s 10’, ‘Peter’, 1. 7 ) Why are some views not updateable? What type of views are updateable? Georgia State University - Confidential MGS 8020_09. ppt/Jul 1, 2017/Page 38