tailieunhanh - Lecture Fundamentals of database systems: Chapter 8 - Emasri, Navathe

Chapter 8 describes more advanced features of the SQL language standard for relational databases. In this chapter present more complex features of SQL retrieval queries, such as nested queries, joined tables, outer joins, aggregate functions, and grouping. Inviting you to refer for more information. | Used to CREATE DROP and ALTER the descriptions of the tables relations of a database Specifies a new base relation by giving it a name and specifying each of its attributes and their data types INTEGER FLOAT DECIMAL ij CHAR n VARCHAR n A constraint NOT NULL may be specified on an attribute CREATE TABLE DEPARTMENT DNAME VARCHAR 10 DNUMBER INTEGER MGRSSN CHAR 9 MGRS TARTDATE CHAR 9 NOT NULL NOT NULL Copyright 2007 Ramez Elmasri and Shamkant B. Navathe lide 8- 3 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe lide 8-4 In SQL2 can use the CREATE TABLE command for specifying the primary key attributes secondary keys and referential integrity constraints foreign keys . Key attributes can be specified via the PRIMARY KEY and UNIQUE phrases CREATE TABlE dept DNAME VARCHAR 10 NOT NULL DNUMBER MGRSSN MGRSTARTDATE PRIMARY KEY INTEGER CHAR 9 CHAR 9 DNUMBER NOT NULL UNIQUE DNAME FOREIGN KEY MGRSSN REFERENCES EMP Used to remove a relation base table and its definition The relation can no longer be used in queries updates or any other commands since its description no longer exists Example DROP TABLE DEPENDENT Copyright 2007 Ramez Elmasri and Shamkant B. Navathe lide 8 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Slide 8- 6 1 Used to add an attribute to one of the base relations The new attribute will have NULLs in all the tuples of the relation right after the command is executed hence the nOt null constraint is not allowed for such an attribute Example ALTER TABLE EMPLOYEE ADD JOB VARCHAR 12 The database users must still enter a value for the new attribute JOB for each EMPLOYEE tuple. This can be done using the UPDATE command. Copyright 2007 Ramez Elmasri and Shamkant B. Navathe lide 8 Specifies a new database schema by giving it a name Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Slide 8- 9 We can specify RESTRICT CASCADE SET NULL or SET DEFAULT on referential integrity constraints foreign keys CREATE TABLE DEPT DNAME VARCHAR 10 NOT NULL DNUMBER INTEGER .