SQL Data Definition 2 ISO SQL Data Types

SQL: Data Definition

2 ISO SQL Data Types

3 Data Definition • SQL DDL allows database objects such as schemas, domains, tables, views, and indexes to be created and destroyed. • Main SQL DDL statements are: CREATE SCHEMA CREATE/ALTER DOMAIN CREATE/ALTER TABLE CREATE VIEW DROP SCHEMA DROP DOMAIN DROP TABLE DROP VIEW • Many DBMSs also provide: CREATE INDEX DROP INDEX

4 CREATE SCHEMA [Name | AUTHORIZATION Creator. Id ] DROP SCHEMA Name [RESTRICT CASCADE ] | • With RESTRICT (default), schema must be empty or operation fails. • With CASCADE, operation cascades to drop all objects associated with schema in order defined above. If any of these operations fail, DROP SCHEMA fails.

5 CREATE TABLE Table. Name {(col. Name data. Type [NOT NULL] [UNIQUE] [DEFAULT default. Option] [CHECK search. Condition] [, . . . ]} [PRIMARY KEY (list. Of. Columns), ] {[UNIQUE (list. Of. Columns), ] […, ]} {[FOREIGN KEY (list. Of. FKColumns) REFERENCES Parent. Table. Name [(list. Of. CKColumns)], [ON UPDATE referential. Action] [ON DELETE referential. Action ]] [, …]} {[CHECK (search. Condition)] [, …] })

6 CREATE TABLE • Creates a table with one or more columns of the specified data. Type. • With NOT NULL, system rejects any attempt to insert a null in the column. • Can specify a DEFAULT value for the column. • Primary keys should always be specified as NOT NULL. • FOREIGN KEY clause specifies FK along with the referential action.

7 Example 6. 1 - CREATE TABLE CREATE DOMAIN Owner. Number AS VARCHAR(5) CHECK (VALUE IN (SELECT owner. No FROM Private. Owner)); CREATE DOMAIN Staff. Number AS VARCHAR(5) CHECK (VALUE IN (SELECT staff. No FROM Staff)); CREATE DOMAIN PNumber AS VARCHAR(5); CREATE DOMAIN PRooms AS SMALLINT; CHECK(VALUE BETWEEN 1 AND 15); CREATE DOMAIN PRent AS DECIMAL(6, 2) CHECK(VALUE BETWEEN 0 AND 9999. 99);

8 Example 6. 1 - CREATE TABLE Property. For. Rent ( property. No. PNumber NOT NULL, …. rooms PRooms NOT NULL DEFAULT 4, rent PRent NOT NULL, DEFAULT 600, owner. No Owner. Number NOT NULL, staff. No Staff. Number Constraint Staff. Not. Handling. Too. Much …. branch. No Branch. Number NOT NULL, PRIMARY KEY (property. No), FOREIGN KEY (staff. No) REFERENCES Staff ON DELETE SET NULL ON UPDATE CASCADE …. );

9 ALTER TABLE • • • Add a new column to a table. Drop a column from a table. Add a new table constraint. Drop a table constraint. Set a default for a column. Drop a default for a column.

10 Example 6. 2(a) - ALTER TABLE Change Staff table by removing default of ‘Assistant’ for position column and setting default for sex column to female (‘F’). ALTER TABLE Staff ALTER position DROP DEFAULT; ALTER TABLE Staff ALTER sex SET DEFAULT ‘F’;

11 Example 6. 2(b) - ALTER TABLE Remove constraint from Property. For. Rent that staff are not allowed to handle more than 100 properties at a time. Add new column to Client table. ALTER TABLE Property. For. Rent DROP CONSTRAINT Staff. Not. Handling. Too. Much; ALTER TABLE Client ADD pref. No. Rooms PRooms;

12 DROP TABLE Table. Name [RESTRICT | CASCADE] e. g. DROP TABLE Property. For. Rent; • Removes named table and all rows within it. • With RESTRICT, if any other objects depend for their existence on continued existence of this table, SQL does not allow request. • With CASCADE, SQL drops all dependent objects (and objects dependent on these objects).

13 Exercise • The following tables form part of a database held in a relational DBMS. Hotel (hotel. No, name, address) Room (room. No, hotel. No, type, price) Booking (hotel. No, guest. No, date. From, date. To, room. No) Guest (guest. No, name, address) where • Hotel contains hotel details and hotel. No is the primary key. • Room contains room details for each hotel and room. No, hotel. No forms the primary key. • Booking contains details of the bookings and the primary key comprises hotel. No, guest. No and date. From. • Guest contains guest details and guest. No is the primary key.

14 I. Create the Hotel table II. Create the Room and Booking tables using the following constraints: ▫ ▫ Type must be one of single, double or family Price must be between $10 and $100 room. No must be between 1 and 100 date. From and date. To must be greater than today’s date