TAILIEUCHUNG - 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 .

• Cơ sở dữ liệu
• Fundamentals of Database Systems
• Database Systems
• Basic constraints
• Schema definition
• Create schema
• Retrieval queries in SQL
• Database system concepts
• The relational data model
• Database design
• Database programming techniques
• Relational database constraints
• Database architecture
• Data models
• Hierarchical model
• Relational database design algorithms
• Designing a set of relations
• Relational decompositions
• Relational database schema
• Database users
• Database applications
• Typical DBMS functionality
• Database approach
• Databases and database users
• Relational databases
• Relational calculus
• Physical database design
• Concurrency control techniques
• Database recovery techniques
• Database security
• Distributed databases
• Database management systems
• Fundamentals of database management systems
• Logical database design
• Object oriented database management
• Data administration
• Relational model concepts
• Relational model constraints
• Characteristics of relations
• Relational database design
• EERR to Relational mapping
• Mapping EER model constructs
• ER to Relational mapping algorithm
• Database design process
• ER model concepts
• ER diagrams
• Database application
• Relational database schemas
• Multivalued dependencies
• Database design theory
• File structures
• Query processing
• Database tuning
• Transaction processing
• Concurrency control
• Database System
• Lecture Database Systems
• Enhanced Entity Relationship
• UML modeling
• Enhanced ER model
• Fundamentals of data modeling
• Data modeling
• Entity Relationship
• Relational algebra
• Relational model
• Programming techniques
• Database programming
• SQL Triggers
• Views in SQL
• Data independence
• Database system utilities
• Classification of DBMSs
• Embedded SQL
• Functions calls
• Stored procedures
• Relational data retrieval
• Relational database model