Section 4 SQL：Data Definition.pdfVIP

SQL: Data Definition 1 Prerequisites for This Section Readings: Required: Connolly and Begg, sections 5.1 and 5.2 Required: Connolly and Begg, sections 6.1, 6.2 and 6.3 Assessments: Multiple-Choice Quiz 3 2 Section Objectives In this section you will learn: ① Purpose and importance of SQL. ② Data types supported by SQL standard. ③ How to create and drop a database. ④ How to create, modify and drop a table. ⑤ How to create and drop an index. 3 DreamHome Rental Database The relational schema for part of DreamHome case study is: Branch (branchNo, street, city, postcode) Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo) PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) Client (clientNo, fName, lName, telNo, prefType, maxRent) PrivateOwner (ownerNo, fName, lName, address, telNo) Viewing (clientNo, propertyNo, viewDate, comment) Registration (clientNo, branchNo, staffNo, dateJoined) 4 Agenda 1. Introduction to SQL 2. Writing SQL Commands 3. Creating and Dropping a Database 4. SQL Data Types 5. Creating a Domain 6. Creating, Modifying, and Dropping a Table 7. Creating and Removing an Index 5 SQL is an Ideal Database Language An ISO standard now exists for SQL SQL is a language with four major components: DDL (Data Define Language) for defining database structure. DML (Data Manipulation Language) for populating the tables. DQL (Data Quer