1 / 21

Database Design Lecture 3_1 Data definition in SQL

Database Design Lecture 3_1 Data definition in SQL. SQL Overview. SQL (Structured Query Language) has 3 major components: A DDL for defining database structure. A DML for retrieving and updating data. A DCL for data control, granting of rights etc. Official pronunciation is ‘S-Q-L'.

bettyharmon
Download Presentation

Database Design Lecture 3_1 Data definition in SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Design Lecture 3_1 Data definition in SQL Database Design lecture 3_11

  2. SQL Overview • SQL (Structured Query Language) has 3 major components: • A DDL for defining database structure. • A DML for retrieving and updating data. • A DCL for data control, granting of rights etc. • Official pronunciation is ‘S-Q-L'. • Can be used by range of users including DBAs, management, application developers, and other types of end users. • Easy to learn: • Consists of standard English words, case insensitive Database Design lecture 3_12

  3. SQL • An ISO standard now exists for SQL, making it both the formal and de facto standard language for relational databases (Latest version SQL-99). • Supported by many vendors • There are vendor-specific variations • Data types vary from vendor to vendor • Standard SQL data types: see text 8.1 • Different conformity to SQL standards • SQL terminology  table, row, column • SQL uses bag-semantics-- a table may contain duplicate rows • We will use Oracle 10g SQL, but omitting most of the complex Oracle-specific details. Database Design lecture 3_13

  4. Oracle Data Types • Basic built-in data types include • Numbers • Number • Character Strings • Char • varchar2 • Date and time • Date • large strings and BLOBs (binary large objects) Database Design lecture 3_14

  5. Number • Syntax for specifying numbers • Number (precision, scale) • Precision is the maximum digits of numbers • Scale specifies the position of decimal point. • Eg • Number(5) 5 digit integer, 12345 • Number(6,2) 6 digit (not including decimal point) decimal number with 2 digits after the decimal point , 1234.56 • Can store 1—38 digits precision Database Design lecture 3_15

  6. String • To store strings, you can choose from • Char • stores fixed-length character strings of up to 2000 characters. Eg. char(10) • should use it to store short strings or strings with ‘neat’ lengths • Varchar2 • Stores variable-length strings of up to 4000 characters long. Eg. Varchar2(50) • Preferred for most string types • String values are quoted with single quotes • Eg ‘12234’, ‘abcd’, ‘a12’ Database Design lecture 3_16

  7. Date and time • Oracle uses the datedata type to store both date and time • Always uses 7 bytes for date-time data. • Oracle date has rich formats, you need to specify it SS second 0-59 MI Minute 0-59 HH Hour 1-12 HH24 Military hour 1-24 DD day of month 1-31 (depends on month) DAY day of the week Sunday-Saturday D day of the week 1-7 MM month number 1-12 MON month abbreviated Jan—Dec Month Month spelled out January-December YY last 2 digits of year eg, 98 YYYY full year value eg, 1998 Database Design lecture 3_17

  8. Date and time • Example of date format: • ‘dd-mon-yyyy’ 01-dec-2001 • ‘dd/mm/yyyy’ 01/12/2001 • ‘mm-dd-yy hh:mi:ss’ 12-01-01 12:30:59 • Default format: ‘dd-mon-yyyy’ • Current date and time: • Sysdate • Oracle has built-in functions that converts between data types: eg, date to string, string to date, string to number and number to string. Database Design lecture 3_18

  9. Data Definition • Creating a table create table table-name ( column-name1 datatype [not null], …….. column-nameN datatype [not null]); Table name • can not exceed 30 characters long, • Must begin with an alphabetic character • May contain letters, numbers, $, # and _ • Should not be an oracle reserved word • Should be descriptive Database Design lecture 3_19

  10. Identifying Primary Key create table table-name ( column-name1 datatype [not null], …….. column-nameN datatype [not null], [constraint constraint-name] Primary key (clolumn-nameA,…,column-nameX) );or create table table-name ( column-name1 datatype [not null] [constraint constraint-name] primary key, …….. column-nameN datatype [not null]) Database Design lecture 3_110

  11. Example create table Department ( Dept_no char(4)PRIMARY KEY, Dept_name varchar2(25)); or create table Department ( dept_no char(4), Dept_name varchar2(25), CONSTRAINT dept_PK PRIMARY KEY(dept_no)); Database Design lecture 3_111

  12. Constraints • Primary key is an example of constraints. Other constraints exist, eg. foreign key, check constraints, null-value constraints. • If you don’t give the constraint name, the system will generate a name automatically, but the name is hard for human understanding. • As seen, primary key constraints (in fact other constraints too) can be specified as row constraints and table constraints. Tables constraints can, for example, identify several columns as the primary key. • Viewing constraints • User constraints are stored in the table user_constraints; • Can use select command to view the constraints. Database Design lecture 3_112

  13. Identifying Foreign Keys create table table-name ( column-name1 datatype [not null] [unique], …….. column-nameN datatype [not null] [unique], [constraint constraint-name1] Primary key (clolumnA,…,columnX), [constraint constraint-name2] Foreign key (Column_list) references referenced_table (column_list_in_referenced_table) ); Create table Staff( staff_no char(3), staff_name varchar2(20), dept_no char(4), Constraint staff_fk foreign key (dept_no) references department (dept_no)); Database Design lecture 3_113

  14. More about Foreign Keys • When you delete or update a value of the columns referenced by other tables, the referential integrity constraints may be violated. • You can specify what action to take in such cases • No action or restrict: reject the delete or update (default) • Cascade: cascading the deletion • Set null • Set default Database Design lecture 3_114

  15. Check Constraints • You can use check constraints to limit the values of a column or some columns • Example: Create table staff( Staff_no char(3), Staff_name varchar2(20) not null, Staff_gender char(1) check (staff_gender in (‘M’, ‘F’)), Staff_salary number(8,2) not null, Dept_no char(4), Constraint staff_pk Primary key (staff_no), Constraint staff_fk Foreign key (dept_no) references department (dept_no), Constraint staff_sal check (staff_salary >10000.00)); Database Design lecture 3_115

  16. Unique Constraints and Default Values Create table staff( Staff_no char(3), Staff_name varchar2(20) not null, DateofBirth date, Staff_nationality char(10) default ‘Australia’, Staff_salary number(8,2) not null, Dept_no char(4), Constraint staff_pk Primary key (staff_no), Constraint staff_fk Foreign key (dept_no) references department (dept_no) on delete set null, Constraint staff_sal check (staff_salary >10000.00), UNIQUE(staff_name, DateofBirth)); The table can not have two rows having the same values for staff_name and DateofBirth Unlike primary key columns, unique columns are not automatically NOT NULL Database Design lecture 3_116

  17. Modifying Table Definitions Alter table table_name add (column_specification | constraint,..., column_specification| constraint); Alter table table_name modify (column_specification | constraint,..., Column_specification | constraint); Alter table table_name drop column column_name | drop (column_list); Alter table table_name drop primary key; Alter table table_name drop constraint constraint_name; • Compare with standard SQL in text Database Design lecture 3_117

  18. Examples alter table orders add (quantity number (3) not null); alter table orders modify (quantity number(5)); alter table orders drop (quantity); • Be careful if the table contains data. Database Design lecture 3_118

  19. Dropping Tables • Drop table table_name [Cascade Constraints]; • Pay attention to referential integrity constraints when dropping tables. • If Cascade Constraints is used, the constraints will be dropped first. • example Drop table Staff; Drop table Department; Drop table Department cascade constraints; Database Design lecture 3_119

  20. Viewing/enabling/disabling/dropping Constraints • To view the constraints defined for table Department, type SELECT constraint_name, constraint_type, search_condition FROM user_constraints WHERE table_name ='DEPARTMENT'; • To disable/enable a constraint, use ALTER TABLE Table_name DISABLE CONSTRAINT constraint_name; ALTER TABLE Table_name ENABLE CONSTRAINT constraint_name; • To drop a constraint, use ALTER TABLE Table_name DROP PRIMARY KEY| UNIQUE (column_name) | CONSTRAINT constraint_name; Database Design lecture 3_120

  21. Viewing Tables and Table Structures • To see what tables are there in SQL*plus, type select * from cat; • To see the structure of a table, type describe table_name; or desc table _name; Database Design lecture 3_121

More Related