1. What is an entity supertype and when it is used?2. What is a subtype discriminator? Give an example of its use.3. In what circumstances the composite primary key is appropriate?4. Design the EERD for a media store that have a PRODUCT supertype containing attributes, Prod_Title, Prod_ReleaseDate, Prod_Price, Prod_Type. The subtypes can be BOOK, CD or MOVIE. The book subtype has specific attributes such as Book_CoverType, Book_PageCount, CD has specific attributes as CD_Genre, CD_Artist and Movie has specific attributes like Movie_Rating and Movie_Director.
1 (Database Systems – ICT 201) Semester 2 2021 ICT201 DATABASE SYSTEM 1. What is an entity supertype and when it is used? 2. What is a subtype discriminator? Give an example of its use. 3. In what circumstances the composite primary key is appropriate? 4. Design the EERD for a media store that have a PRODUCT supertype containing attributes, Prod_Title, Prod_ReleaseDate, Prod_Price, Prod_Type. The subtypes can be BOOK, CD or MOVIE. The book subtype has specific attributes such as Book_CoverType, Book_PageCount, CD has specific attributes as CD_Genre, CD_Artist and Movie has specific attributes like Movie_Rating and Movie_Director. Week 8 Tutorial ICT201 DATABASE SYSTEM PowerPoint Presentation DATABASE SYSTEM LECTURE 7 Introduction to Structured Query Language (SQL) Text Book and References Coronel, C. and Morris, S. (2019). Database Systems: Design, Implementation, and Management, 13th ed. USA: Cengage Learning. 2 Learning Objectives After completing this chapter, you will be able to: Retrieve specified columns of data from a database Join multiple tables in a single SQL query Restrict data retrievals to rows that match complex criteria Aggregate data across groups of rows Create subqueries to preprocess data for inclusion in other queries Identify and use a variety of SQL functions for string, numeric, and date manipulation Explain the key principles in crafting a SELECT query 3 Introduction to SQL (1 of 4) Categories of SQL functions Data definition language (DDL) Data manipulation language (DML) Transaction control language (TCL) Data control language (DCL) SQL is relatively easy to learn Nonprocedural language with basic command vocabulary set of less than 100 words Differences in SQL dialects are minor 4 Introduction to SQL (2 of 4) Table 7.2SQL Data Definition Commands Command or OptionDescriptionCovered CREATE SCHEMA AUTHORIZATIONCreates a database schemaChapter 8 CREATE TABLECreates a new table in the user’s database schemaChapter 8 NOT NULLEnsures that a column will not have null valuesChapter 8 UNIQUEEnsures that a column will not have duplicate valuesChapter 8 PRIMARY KEYDefines a primary key for a tableChapter 8 FOREIGN KEYDefines a foreign key for a tableChapter 8 DEFAULTDefines a default value for a column (when no value is given)Chapter 8 CHECKValidates data in an attributeChapter 8 CREATE INDEXCreates an index for a tableChapter 8 CREATE VIEWCreates a dynamic subset of rows and columns from one or more tablesChapter 8 ALTER TABLEModifies a table’s definition (adds, modifies, or deletes attributes or constraints)Chapter 8 CREATE TABLE ASCreates a new table based on a query in the user’s database schemaChapter 8 DROP TABLEPermanently deletes a table (and its data)Chapter 8 DROP INDEXPermanently deletes an indexChapter 8 DROP VIEWPermanently deletes a viewChapter 8 5 Introduction to SQL (3 of 4) Table 7.3Other SQL Commands Command or OptionDescriptionCovered Transaction Control Language COMMITPermanently saves data changesChapter 8 ROLLBACKRestores data to its original valuesChapter 8 Data Control Language GRANTGives a user permission to take a system action or access a data objectChapter 16 REVOKERemoves a previously granted permission from a userChapter 16 6 Introduction to SQL (4 of 4) Data type: specification about the kinds of data that can be stored in an attribute Influence queries that retrieve data Fundamental types of data Character data Numeric data Date data At the heart of SQL is the query Covers both questions and actions 7 The Database Model 8 Basic SELECT Queries Each clause in a SELECT query performs a specific function SELECT: specifies the attributes to be returned by the query FROM: specifies the table(s) from which the data will be retrieved WHERE: filters the rows of data based on provided criteria GROUP BY: groups the rows of data into collections based on sharing the same values in one or more attributes HAVING: filters the groups formed in the GROUP BY clause based on provided criteria ORDER BY: sorts the final query result rows in ascending or descending order based on the values of one or more attributes SQL commands can be grouped together on a single line Complex command sequences are best shown on separate lines, with space between the SQL command and the command’s components 9 SELECT Statement Options (1 of 7) The SELECT query specifies the columns to be retrieved as a column list Syntax: SELECT columnlist FROM tablelist; The columnlist represents one or more attributes, separated by commas A wildcard character is a symbol that can be used as a general substitute for other characters or commands Using column aliases Alternative name for a column or table in a SQL statement Using computed columns Computed column (also called a calculated column) represents a derived attribute Arithmetic operators: the rule of precedence Rules that establish the order in which computations are completed 10 SELECT Statement Options (2 of 7) 11 SELECT Statement Options (3 of 7) 12 SELECT Statement Options (4 of 7) 13 SELECT Statement Options (5 of 7) Table 7.4: The Arithmetic Operators OperatorDescription +Add -Subtract *Multiply /Divide ^Raise to the power of (some applications use ** instead of ^) 14 SELECT Statement Options (6 of 7) Date arithmetic Values are stored as a number of days; it is possible to perform date arithmetic in a query Listing unique values SQL’s DISTINCT clause produces a list of only those values that are different from one another Command example: SELECT DISTINCT V_CODE FROM PRODUCT; 15 SELECT Statement Options (7 of 7) 16 FROM Clause Options (1 of 6) FROM clause of the query specifies the table or tables from which the data is to be retrieved Inner joins return only rows from the tables that match on a common value Outer joins return the same matched rows as the inner join, plus unmatched rows from one table or the other Natural join returns all rows with matching values in the matching columns and eliminates duplicate columns Determines the common attribute(s) by looking for attributes with identical names and compatible data types Selects only the rows with common values in the common attribute(s) If there are no common attributes, returns the relational product of the two tables Syntax: SELECT column-list FROM table1 NATURAL JOIN table2 17 FROM Clause Options (2 of 6) Table 7.5 Creating Links through Foreign Keys TableAttributes To Be ShownLinking Attribute PRODUCTP_DESCRIPT, P_PRICEV_CODE VENDORV_NAME, V_CONTACT, V_AREACODE, V_PHONEV_CODE 18 FROM Clause Options (3 of 6) JOIN USING syntax Returns only the rows with matching values in the column indicated in the USING clause—and that column must exist in both tables Syntax: SELECT column-list FROM table1 JOIN table2 USING (common-column) JOIN ON syntax Express a join when the tables have no common attribute names Query returns only the rows that meet the indicated join condition Syntax: SELECT column-list FROM table1 JOIN table2 ON join-condition Common attribute names Most common cause of duplicate column names is the existence of a foreign key 19 FROM Clause Options (4 of 6) 20 FROM Clause Options (5 of 6) Outer joins Returns not only the rows matching the join condition (rows with matching values in the common columns) and returns the rows with unmatched values ANSI standard defines three types of outer joins: left, right, and full Cross join Performs a relational product (also known as the Cartesian product) of two tables Joining tables with an alias An alias may be used to identify the source table from which the data is taken The ability to specify a table alias is very useful Using a table alias allows the database programmer to improve the maintainability of the code by using a table alias that is descriptive of what data the table is providing within the query Recursive joins Recursive query: joins a table to itself 21 FROM Clause Options (6 of 6) 22 ORDER BY Clause Options (1 of 2) ORDER BY clause is especially useful when the listing order is important Syntax: SELECT columnlist FROM tablelist [ORDER BY columnlist [ASC|DESC] ]; Cascading order sequence 1. ORDER BY last name 2. Within matching last names, ORDER BY first name 3. Within matching first and last names, ORDER BY middle initial 23 ORDER BY Clause Options (2 of 2) 24 WHERE Clause Options (1 of 4) Selecting rows with conditional restrictions WHERE clause is used to add conditional restrictions to the SELECT statement that limit the rows returned by the query Syntax: SELECT columnlist FROM tablelist [WHERE conditionlist ] [ORDER BY columnlist [ASC | DESC] ]; Using comparison operators on character attributes May be used to place restrictions on character-based attributes Using comparison operators on dates Date procedures are often more software-specific than other SQL procedures 25 WHERE Clause Options (2 of 4) Table 7.6 Comparison Operators SymbolMeaning =Equal to< less="" than=""><= less="" than="" or="" equal="" to="">Greater than >=Greater than or equal to<> or !=Not equal to 26 WHERE Clause Options (3 of 4) Selecting rows with conditional restrictions WHERE clause is used to add conditional restrictions to the SELECT statement that limit the rows returned by the query Syntax: SELECT columnlist FROM tablelist [WHERE conditionlist ] [ORDER BY columnlist [ASC | DESC] ]; Using comparison operators on character attributes May be used to place restrictions on character-based attributes Using comparison operators on dates Date procedures are often more software-specific than other SQL procedures 27 WHERE Clause Options (4 of 4) Logical operators: AND, OR, and NOT SQL allows you to include multiple conditions in a query through the use of these logical operators Boolean algebra is dedicated to the use of logical operators Old-style joins Generally not recommended Make complex queries more difficult to maintain Susceptible to undetected errors Special operators BETWEEN IN LIKE IS NULL NOT 28 Aggregate Processing (1 of 3) Takes a collection of rows and reduces it to a single row SQL provides useful aggregate functions that count, find minimum and maximum values, calculate averages, etc. Aggregate functions Count MIN and MAX SUM and AVG Grouping data GROUP BY clause syntax: SELECT columnlist FROM tablelist [WHERE conditionlist ] [GROUP BY columnlist ] [ORDER BY columnlist [ASC | DESC] ]; 29 Aggregate Processing (2 of 3) Table 7.7 Some Basic SQL Aggregate Functions FunctionOutput COUNTThe number of rows containing non-null values MINThe minimum attribute value encountered in a given column MAXThe maximum attribute value encountered in a given column SUMThe sum of all values for a given column AVGThe arithmetic mean (average) for a specified column 30 Aggregate Processing (3 of 3) HAVING clause Operates very much like the WHERE clause in the SELECT statement HAVING clause is applied to the output of a GROUP BY operation Syntax: SELECT columnlist FROM tablelist [WHERE conditionlist ] [GROUP BY columnlist ] [HAVING conditionlist ] [ORDER BY columnlist [ASC | DESC] ]; 31 Subqueries (1 of 3) Key characteristics A subquery is a query (SELECT statement) inside another query A subquery is normally expressed inside parentheses The first query in the SQL statement is known as the outer query The query inside the SQL statement is known as the inner query The inner query is executed first The output of an inner query is used as the input for the outer query The entire SQL statement is sometimes referred to as a nested query Subquery can return one or more values One single value (one column and one row) A list of values (one column and multiple rows) A virtual table (multicolumn,