SQL Data Definition

1. SQL Data Definition • Spread throughout chapter 7

2. A Data Dictionary for the CH06 Database Table 6.3

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

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

5. Data Definition Commands • Creating Table Structures CREATE TABLE <table name>(<attribute1 name and attribute1 characteristics,attribute2 name and attribute2 characteristics,attribute3 name and attribute3 characteristics,primary key designation,foreign key designation and foreign key requirements>);

6. DDL - Create Table 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) )

7. DDL • Foreign Keys 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 ) • Domains CREATE DOMAIN ZIP_CODE_TYP AS CHAR(9);

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

9. Data Definition Commands • SQL Integrity Constraints • Entity Integrity • PRIMARY KEY • NOT NULL and UNIQUE • Referential Integrity • FOREIGN KEY • ON DELETE • ON UPDATE

10. Domains • Domain is a set of permissible values for an attribute. • Defining a domain requires: • Name • Data type • Default value • Domain constraint or condition CREATE DOMAIN depttyp AS CHAR(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 • Syntax: CREATE DOMAIN <domain name> AS <data type> DEFAULT <value> CHECK <condition>

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

12. SQL Indexes CREATE INDEX P_CODEXON PRODUCT(P_CODE); CREATE UNIQUE INDEX P_CODEXON PRODUCT(P_CODE); • Composite index • Index based on two or more attributes CREATE INDEX meetingX ON SECTION ( Sem, Year, Time, Room); • Often used to prevent data duplication

13. DDL • DROP SCHEMA • DROP TABLE • ALTER TABLE

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

15. Advanced Data Management Commands • Changing Table Structures ALTER TABLE <table name>MODIFY (<column name> <new column characteristics>); ALTER TABLE <table name>ADD (<column name> <new column characteristics>);

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

17. Changing a Column’s Data Characteristics • Use ALTER to change data characteristics • 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 ALTER TABLE PRODUCTMODIFY (P_PRICE DECIMAL(9,2));

18. Adding or Dropping a Column • Use ALTER to add a column • Do not include the NOT NULL clause for new column ALTER TABLE PRODUCTADD (P_SALECODE CHAR(1)); • Use ALTER to drop a column • Some RDBMSs impose restrictions on the deletion of an attribute ALTER TABLE StudentsDROP COLUMN Year;

19. Advanced Data Management Commands • Primary and Foreign Key Designation ALTER TABLE PRODUCTADD PRIMARY KEY (P_CODE); ALTER TABLE PRODUCTADD FOREIGN KEY (V_CODE) REFERENCES VENDOR; ALTER TABLE PRODUCT ADD PRIMARY KEY (P_CODE) ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR;

20. Advanced Data Management Commands • Deleting a Table from the Database • DROP TABLE <table name>; DROP TABLE PART;

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

22. Example Views • CREATE VIEW CS_STUD AS SELECT * FROM STUDENT WHERE MAJOR = ‘CS’; • CREATE VIEW PRIV_STUD AS SELECT SSN,FNAME,LNAME,YEAR,MAJOR FROM STUDENT • 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

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

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

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

26. End SQL DDL