Database Management System

1. Database Management System Module 3: Relational Languages

2. Structure Query Language (SQL) • Structure query languages (SQL) is the most widely used commercial relational database language. • Developed at IBM and now most DBMS vendors include their products with SQL, hence it has become a de facto standard.

3. SQL - Data Definition Language (DDL) • It is a subset of SQL that supports the creation, deletion and modification of definitions for tables and views. • The DDL also provides commands for specifying access rights or privileges to tables and views

4. Creating Table • DDL involves facilities for maintaining schemas and tables. • To form a table using SQL the user needs to specify four components: • Name of the table • Name of each of the columns in the table. • Data type of each column • Maximum length of each column.

5. Creating Table • These four items are formulated together in a CREATE TABLE command with the following format: CREATE TABLE <table name> (<column name><data type>(<length>), <column name><data type>(<length>), .. .. .. )

6. Creating Table • Creating table using sql we use CREATE TABLE statement. • Example: CREATE TABLE Student ( sid CHAR (20), name CHAR(20), login CHAR (20), age INTEGER, gpa REAL )

7. Creating Table with Constraints (Primary Key) • CREATE TABLE Students ( sid CHAR(20), name CHAR(30), login CHAR (20), age INTEGER, UNIQUE (name, age), CONSTRAINT StudentKey PRIMARY KEY (sid) )

8. Creating Table with Constraints: Foreign Key • Suppose you have the following schema; Enrolled (sid: string, cid: string, grade: string) • The following define foreign key constraints;

9. Creating Table with Constraints: Foreign Key • CREATE TABLE Enrolled ( sid CHAR(20), cid CHAR(20), grade CHAR(10), PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES Students)

10. Modifying Tables • This operation is allowed to Database Administrator and it involves: • Adding an extra column to a table • Drop a column from a table • Modify a maximum length of an exiting column • Add new table constraints • Drop a table constraint

11. Modifying Tables • Each operation is specified using ALTER TABLE command. Example: ALTER TABLE Lectures ADD COLUMN roomNo SMALLINT

12. CASE STUDY: Creating Tables Suppose we are an insurance com[pany. Our business primarily revolves around insurance policies – effectively a financial product assuring risk between the holder of the policy and some events such as death, building damage or car accident. A basic part of the database of the insurance company is likely to be organised as follows:

13. CASE STUDY: Creating Tables • Policies (policyNo, holderNo, startDate, premium, renewalDate, policyType) • PolicyHolders (holderNo, holderName, holderAddress, holderTelno) Question: Create two tables that will be storing above information. Include in your definition primary and foreign key constraints.

14. Delete Tables • If we no longer need a base table and want to destroy it use DROP TABLE command. • For example DROP TABLE Students <RESTRICT> destroys the Students table. • Replacing word RESTRICT with CASCADE will drop the table even if there is referencing views or integrity constraints.

15. Views • A view is a table whose rows are not explicitly stored in the database but are computed as needed from a view definition. • Consider the Students and Enrolled relations. Suppose that we are often interested in finding the names and student identifiers of students who got grade B in some courses, together with cid for the course.

16. Views Using SQL-92 we can define a view for this purpose. CREATE VIEW B-Students (name, sid, course) AS SELECT S.name, S.sid, E.cid FROM Students S, Enrolled E WHERE S.id = E.sid AND E.grade = “B”.

17. Views • The view B-Students has three fields called name, sid, and course with the same domains as the fields sname and sid in Enrolled. • View can be used just like a base table in defining new queries or view.

18. Views • It can be queried in the same manner as a base table. • For instance: SELECT * FROM B-Students WHERE course = “maths”

19. Queries in SQL (DML) • Data Manipulation Language (DML) concerns the CRUD activities associated with a database: Create – inserting rows into table Retrieval – accessing data within tables Update – updating data within existing rows of tables Delete – deleting rows from tables

20. A Sample Table • Consider a relational database which contains a table below.

21. SQL - INSERT • SQL INSERT is used to add new data to a table. • Example:INSERT INTO products (product_code, description, unit_price) VALUES (‘127-275’, ‘Computer’, ‘3456’)

22. SQL - SELECT • Querying the database to access (retrieving) information that is in the table • For example: SELECT * FROM products • Another example: SELECT product_code, unit_price FROM products WHERE unit_price >=100 ORDER DESC BY unit_price

23. SQL- UPDATE • SQL UPDATE is used to change the data within rows in a table. • Example: UPDATE product SET description=’Refrigerator’, unit_price=’870’ WHERE product_code=‘198-741’

24. SQL- Delete • Used to delete rows data from the table. • Use delete command with where clause DELETE FROM products WHERE Unit_Price = 24.95

25. More SQL retrieval examples

26. Example • Find all sailors with a rating above 7 SELECT S.sid, S.sname,S.rating,S.age FROM Sailors AS S WHERE S.rating > 7

27. Example • Find the sid of the sailors who have reserved a red boat SELECT R.sid FROM Boats B, Reserves R WHERE B.bid = R.bid AD B.color = “red”

28. Union, Intersect, and Except

29. Union, Intersect, and Except • These three operators implement the union, intersection and difference operators of the relational algebra.

30. Union • It combines the results of two compatible queries. • Example: The query below produces a result combining information about computer studies degree modules (courseCode = “CSD”) with those in the electrical engineering department (courseCode = “EED”)

31. UNION SELECT moduleName, level FROM Modules WHERE courseCode = “CSD” UNION SELECT moduleName, level FROM Modules WHERE courseCode = “EED”

32. Intersect • It finds those rows which are common to the results from two queries. • Example: The query below produces a result detailing those modules that are run both under the computer studies programme and under the electrical engineering programme:

33. Intersect SELECT moduleName, level FROM Modules WHERE courseCode = “CSD” INTERSECT SELECT moduleName, level FROM Modules WHERE courseCode = “EED”

34. Except • It produces results of all those rows in one result tat are not preent in the other result. • Example: The query below produces a result detailing those modules that are run both under the computer studies programme and not under the electrical engineering programme:

35. Except SELECT moduleName, level FROM Modules WHERE courseCode = “CSD” DIFFERENCE SELECT moduleName, level FROM Modules WHERE courseCode = “EED”

36. JOIN • Join operator can be used in a FROM clause: SELECT moduleName, staffName FROM Lectures NATURAL JOIN Modules • This will join two tables on the primary_foreign key references stored in the table definitions.

37. Nested Queries • A nested query is a query that has another query embedded within it; the embedded query is called subquery. • A subquery typically appear within the WHERE clause of query.

38. Example • Find the names of sailors who have reserved boat 103. SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid = 103)