SQL Data Definition 4 Spread throughout chapter 7

SQL Data Definition 4 Spread throughout chapter 7 7

A Data Dictionary for the CH 06 Database 7 Table 6. 3

Data Types 4 Each attribute will be defined to have a particular data type 7 4 Data type selection is usually dictated by the nature of the data and by the intended use 4 Pay close attention to the expected use of attributes for sorting and data retrieval purposes

Some Common SQL Data Types Data Type 7 Format Numeric NUMBER(L, D) INTEGER SMALLINT DECIMAL(L, D) Character CHAR(L) VARCHAR(L) Date DATE

Data Definition Commands 4 7 Creating Table Structures CREATE TABLE

( );

DDL - Create Table 7 CREATE TABLE STUDENT ( SSN CHAR(9) NOT NULL, FNAME VARCHAR(15) NOT NULL, MIDINIT CHAR, LNAME VARCHAR(15) NOT NULL, YEAR CHAR(2) NOT NULL DEFAULT ‘Fr’, MAJOR CHAR(4) DEFAULT ‘Und’, CREDIT INT DEFAULT 0, GPA DECIMAL(4, 2), HOMETOWN VARCHAR(15), BALANCE DECIMAL(7, 2) DEFAULT 0, CONSTRAINT STDPK PRIMARY KEY (SSN) )

4 Foreign Keys 7 DDL CREATE TABLE enrollments (classindex INT NOT NULL, stdssn CHAR(9) NOT NULL, CONSTRAINT ENRLPK PRIMARY KEY(CLASSINDEX, STDSSN), CONSTRAINT ENRLSECT FOREIGN KEY (CLASSINDEX) REFERENCES SECTION(INDEX) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT ENRLSTUD FOREIGN KEY (STDSSN) REFERENCES STUDENT(SSN) ON DELETE CASCADE ON UPDATE CASCADE ) 4 Domains CREATE DOMAIN ZIP_CODE_TYP AS CHAR(9);

Other SQL Constraints 4 NOT NULL constraint u 7 Ensures that a column does not accept nulls 4 UNIQUE constraint u Ensures that all values in a column are unique 4 DEFAULT constraint u Assigns a value to an attribute when a new row is added to a table 4 CHECK constraint u Validates data when an attribute value is entered

Data Definition Commands 4 SQL Integrity Constraints 7 u Entity Integrity l l u PRIMARY KEY NOT NULL and UNIQUE Referential Integrity l l l FOREIGN KEY ON DELETE ON UPDATE

Domains 7 4 Domain is a set of permissible values for an attribute. 4 Defining a domain requires: Name u Data type u Default value u Domain constraint or condition CREATE DOMAIN depttyp AS CHAR(4); u 4 Then can use in defining any attributes of that type – one benefit, we can ensure that we define the PK and FK the same way 4 Syntax: CREATE DOMAIN AS DEFAULT CHECK

SQL Indexes 7 4 When a primary key is declared, DBMS automatically creates a unique index 4 Often need additional indexes 4 Using the CREATE INDEX command, SQL indexes can be created on the basis of any selected attribute

SQL Indexes 7 CREATE INDEX P_CODEX ON PRODUCT(P_CODE); CREATE UNIQUE INDEX P_CODEX ON PRODUCT(P_CODE); 4 Composite index u Index based on two or more attributes CREATE INDEX meeting. X ON SECTION ( Sem, Year, Time, Room); u Often used to prevent data duplication

DDL 7 4 DROP SCHEMA 4 DROP TABLE 4 ALTER TABLE

Advanced Data Definition Commands 4 All changes in the table structure are made by using the ALTER command 7 u Followed by a keyword that produces specific change u Three options are available l ADD l MODIFY l DROP

Advanced Data Management Commands 7 4 Changing Table Structures ALTER TABLE

MODIFY ( ); ALTER TABLE

ADD ( );

Changing a Column’s Data Type 4 ALTER can be used to change data type 7 4 Some RDBMSs (such as Oracle) do not permit changes to data types unless the column to be changed is empty ALTER TABLE PRODUCT MODIFY (V_CODE CHAR(5));

Changing a Column’s Data Characteristics 4 Use ALTER to change data characteristics 7 4 If the column to be changed already contains data, changes in the column’s characteristics are permitted if those changes do not alter the data type 4 ALTER TABLE PRODUCT MODIFY (P_PRICE DECIMAL(9, 2));

Adding or Dropping a Column 4 Use ALTER to add a column 7 u Do not include the NOT NULL clause for new column ALTER TABLE PRODUCT ADD (P_SALECODE CHAR(1)); 4 Use ALTER to drop a column Some RDBMSs impose restrictions on the deletion of an attribute ALTER TABLE Students DROP COLUMN Year; u

Advanced Data Management Commands 7 4 Primary and Foreign Key Designation ALTER TABLE PRODUCT ADD PRIMARY KEY (P_CODE); ALTER TABLE PRODUCT ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR; ALTER TABLE PRODUCT ADD PRIMARY KEY (P_CODE) ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR;

Advanced Data Management Commands 7 4 Deleting a Table from the Database u DROP TABLE

; DROP TABLE PART;

Views 7 4 A view is a virtual table - looks like it exists but doesn’t 4 View has a name (like a table) and you can do many of the same things to views as tables 4 Content of a view is derived from the contents of some real table(s). (“base tables”) u View is defined via a SELECT statement … see upcoming 4 A user may see the view and think it is the actual DB

Example Views 7 4 CREATE VIEW CS_STUD AS SELECT * FROM STUDENT WHERE MAJOR = ‘CS’; 4 CREATE VIEW PRIV_STUD AS SELECT SSN, FNAME, LNAME, YEAR, MAJOR FROM STUDENT 4 CREATE VIEW ENROLL_STATS (DEPT, LARGE, SMALL, AVE, TOTAL) AS SELECT DEPT, MAX(ENROLL), MIN(ENROLL), AVG(ENROLL), SUM(ENROLL) FROM SECTIONS GROUP BY DEPT

Example Views 7 4 CREATE VIEW STUD_ENROLL_INFO AS SELECT ENROLLMENTS. INDEX, STUDENT. SSN, FNAME, LNAME, YEAR, MAJOR, GPA FROM ENROLLMENTS, STUDENT WHERE ENROLLMENTS. STUDENT = STUDENT. SSN;

Updates involving Views 7 4 NOT ALL views are updatable (e. g. ENROLL_STATS, STUD_ENROLL_INFO) 4 If a given update is ambiguous as to what the user would want, it is hard for the DBMS to decide for them 4 Updatable: u a view based on a single table is updatable if the view includes the PK 4 Not Updateable: views involving joins u view involving grouping and aggregate functions u

Advantages of Views 7 4 (Some) Logical Independence 4 Show user only the info they need to see 4 Views can simplify the user’s interaction with DB

End SQL DDL 7