Week 5 - Database
Describe pattern matching on strings
The most commonly used operation on strings is pattern matching using the operator like. Patterns are described using two special characters: percent (%) and underscore (_) Percent is for multiple characters and underscore is for a single character. Patterns are case sensitive. Given the relation Book(bookNumber, bookTitle, author), to find the book-number and author of the books whose title begins with the sub-string "Com", the query expression is as follows: SELECT bookNumber, authorFROM BookWHERE bookTitle like "Com%" SQL allows user to search for mismatches instead of matches by using the comparison operator not like.
Describe SQL schema definition for deleting tuples.
To delete all tuples in a relation r, the DELETE FROM command is used. DELETE FROM r
Describe SQL schema definition for deleting a table.
To remove a relation r from SQL database, the DROP TABLE command is used. DROP TABLE r
Describe SQL schema definition for creating a table.
An SQL relation is defined using CREATE TABLE command: CREATE TABLE r (A1 D1, A2 D2, ..., An Dn,<integrity-contraint-1>,...,<integrity-contraint-k>) where r is the name of the relation, each Ai is the name of the attribute in the schema of relation r, and Di is the domain type of the values in the domain of attribute Ai. The allowed integrity constraints include primary key (Aj1, Aj2, ..., Ajm) check(P) where P is a predicate. For example: to create a physical table in the database for the relational schema Student(studentName, studentNumber, program, cgpa), the SQL DDL statement can be written as: CREATE TABLE Student (studentName char(40) not null, studentNumber char(10), program char(4) not null,cgpa real, primary key (studentNumber), check (cgpa >= 0.00 and cgpa <= 7.00)) SQL allows the domain declaration of an attribute to include the specification not null, and thus prohibits the insertion of a null value for this attribute. A newly created relation is initially empty. The INSERT command can be used to load data into the relation.
Example of SELECT clause
Consider the relation Student(studentNumber, studentName, program, cgpa) To find the name of all students in the student relation, the query is as: SELECT studentNameFROM Student To force the elimination of duplicates, the keyword distinct is used. SELECT distinct studentNameFROM Student To specify explicitly that duplicates are not removed, the keyword all is used. SELECT all studentNameFROM Student The asterisk "*" can be used to denote "all attributes". A SELECT clause of the form SELECT * indicates that all attributes of all relations appearing in the FROM clause must be selected. To list all information of the students in the relation, the query is as: SELECT *FROM Student The SELECT clause can also contain arithmetic expressions involving the operators, +, -, * and / operating on constants or attributes of tuples. Considering the relation: User(userID, userName, address, age), we can write SELECT userName, age * 365FROM User
What is DDL?
DDL stands for Data Definition Language. Database management systems provide a facility in the form of a language known as DDL, which can be used to define the conceptual schema and also give some details about how to implement this schema in the physical devices used to store the data. This definition includes all the entity types and their associated attributes as well as the relationships among the entity types. This definition also includes constraints on the values assigned to different attributes in the same or different records.
What is DML?
DML refers to Data Manipulation Language. A DML is a language that enables users to access or manipulate data as organised by the appropriate data model. There are basically two types. Procedural DMLs: Procedural DMLs require a user to specify what data are needed and how to get those data. Nonprocedural DMLs: Nonprocedural DMLs require a user to specify what data are needed without specifying how to get those data. Nonprocedural DMLs are usually easier to learn and use than are procedural DMLs. Since a user does not have to specify how to get the data, nonprocedural DMLs may generate code that is not as efficient as the procedure by procedural languages.
What is SQL?
SQL (pronounced 'S-Q-L', or sometimes 'See-Quel') stands for Structured Query Language. It is now both the formal and de facto standard language for relational DBMSs. Besides a query language, SQL language includes features for: (a) Defining the structure of the data, (b) Modifying data in the database, and (c) Specifying security constraints.
Example of WHERE clause
SQL allows user to use the comparison operators <, <=, >, >=, = and <> to compare strings and arithmetic expressions, as well as special types, such as date types in the WHERE clause. Consider the relation Student (studentNumber, studentName, program, birthDate, cgpa) To find the name of the students from IT program, the query is as follows: SELECT studentNameFROM StudentWHERE program = 'IT' Note that the value of text (using char or varchar) type attribute is given in quotation marks. Although SQL expressions are not case-sensitive, the text used in quotation marks is case-sensitive. To find the names of the students who obtained CGPA greater than 5.50, the query is as follows: SELECT studentNameFROM StudentWHERE cgpa > 5.50 Note that numeric values (using number, integer, single, double) are written directly in SQL expressions. To find the names of the students who were born on 26 January 2007, the query is as follows: SELECT studentNameFROM StudentWHERE birthDate = '2007/01/26'
Describe tuple display ordering.
SQL offers the ORDER BY clause that causes the tuples in the result of a query to appear in sorted order. By default, the ORDER BY clause lists items in ascending order. To specify the sort order, desc is used for descending order or asc is used for ascending order. Consider the relation: Book(bookNumber, bookTitle, author) To find the book title and author of all books in descending order of book number. SELECT bookTitle, authorFROM BookORDER BY bookNumber desc Since sorting a large number of tuples may be costly; it is desirable to sort only when necessary.
Describe the aliasing operation.
SQL provides a mechanism for temporarily giving names both relations and attributes. It uses the as clause, taking the form: old-name as new-name The as clause can appear in both the SELECT and FROM clause. Consider the following relations: User(userID, userName, address, age),Book(bookNumber, bookTitle, author) andTransaction(userID, bookNumber, issueDate, returnDate) SELECT userName, age * 365 as ageInDaysFROM User The as clause is particularly useful in defining the notion of tuple variables. SELECT B.bookTitle, B.authorFROM Book as B, Transaction as TWHERE B.bookNumber = T.bookNumber and T.issueDate = '2019/11/09'
List the functions of SQL DDL statements.
The SQL DDL allows the specification of The schema for each relation, The domain of values associated with each attribute, The integrity constraints, The set of indices to be maintained for each relation, The security and authorisation information for each relation, The physical storage structure of each relation on disk.
Describe the SQL domain types.
The SQL-92 standard supports a variety of built-in domain types, including the following char(n): Fixed-length character string, with user-specified length n. The full form character can be used instead. varchar(n): Variable-length character string, with user-specified maximum length n. The full form character varying is equivalent. int: Integer. The full form integer is equivalent. smallint: Small Integer. numeric(p, d): Fixed-point number, with user-specified precision. The number consists of p digits (plus a sign), and d of p digits are to the right of the decimal point. real, double precision: Floating-point and double-precision floating-point numbers, with machine-dependent precision. float(n): Floating-point number with user-specified precision of at least n digits. date: Calendar date, containing a (four digit) year, month, and day of the month. time: Time of day, in hours, minutes and seconds. SQL-92 allows user to define domains using a CREATE DOMAIN clause. CREATE DOMAIN dom-person-name char(40)
What is the basic structure of an SQL DML expression?
The basic structure of an SQL DML expression consists of three clauses: SELECT, FROM, and WHERE. The SELECT clause is used to list the attributes desired in the result of a query. It corresponds to the projection operation of the relational algebra. The FROM clause lists the relations to be scanned in the evaluation of the expression. It corresponds to the Cartesian-product operation of the relational algebra. The WHERE clause consists of a predicate involving attributes of the relations that appear in the FROM clause. It corresponds to the selection operation of the relational algebra. A typical SQL query has the form: SELECT A1, A2, ..., An FROM r1, r2, ..., rm WHERE P Each Ai represents an attribute, each ri is a relation and P a predicate. If the WHERE clause is omitted, the predicate P is true. The result of an SQL query is a relation.
Describe SQL schema definition for changing table scheme.
To modify the schema of an existing relation/table by adding or removing attributes, the ALTER TABLE command is used. To add an attribute to an existing relation, we write ALTER TABLE r ADD A D where A is the name of attribute and D is the domain of the attribute A to be added to relation r. To remove an attribute from an existing relation, we write ALTER TABLE r DROP B where B is the name of attribute to be deleted from relation r.