Lecture 3: Building Database PDF

Lecture 3 Building Database Database 2 Dr. Mohamed Saied Amer Outline SQL Data Definition. Attribute Data Types and Domains in SQL Specifying Constraints in SQL. Data Definition Language (DDL) Commands in SQL. SQL Example SQL Data Definition. SQL schema Identified by a sch...

Lecture 3 Building Database Database 2 Dr. Mohamed Saied Amer Outline SQL Data Definition. Attribute Data Types and Domains in SQL Specifying Constraints in SQL. Data Definition Language (DDL) Commands in SQL. SQL Example SQL Data Definition. SQL schema Identified by a schema name Includes an authorization identifier and descriptors for each element. Schema elements include Tables, constraints, views, and other constructs. Each statement in SQL ends with a semicolon Attribute Data Types and Domains in SQL Numeric Integer numbers: INTEGER, INT, and SMALLINT Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISION Character-string Fixed length: CHAR(n), CHARACTER(n) Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n) Attribute Data Types and Domains in SQL Bit-string Fixed length: BIT(n) Varying length: BIT VARYING(n) BLOB (Binary Large Object), BLOB(10G) Boolean Values of TRUE or FALSE or NULL Attribute Data Types and Domains in SQL DATE Ten positions Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD Multiple mapping functions available in RDBMSs to change date formats Timestamp Includes the DATE and TIME fields Plus a minimum of six positions for decimal fractions of seconds Optional WITH TIME ZONE qualifier DATE, TIME, Timestamp, INTERVAL data types can be cast or converted to string formats for comparison. Specifying Constraints in SQL Relational Model has 3 basic constraint types that are supported in SQL: Key constraint: A primary key value cannot be duplicated Entity Integrity constraint: A primary key value cannot be null Referential integrity constraints : The “foreign key “ must have a value that is already present as a primary key, or may be null. Specifying Key and Referential Integrity Constraints PRIMARY KEY clause Specifies one or more attributes that make up the primary key of a relation Dnumber INT PRIMARY KEY; UNIQUE clause Specifies alternate (secondary) keys (called CANDIDATE keys in the relational model). Dname VARCHAR(15) UNIQUE; FOREIGN KEY clause Default operation: reject update on violation DDL Commands in SQL. Following are the five DDL commands in SQL: CREATE Command DROP Command ALTER Command TRUNCATE Command RENAME Command CREATE Command CREATE is used to create databases, tables, triggers, views and other database objects. Syntax : CREATE Database Database_Name; CREATE TABLE Student ( CREATE TABLE Persons ( Roll_No. Int , ID int NOT NULL PRIMARY KEY, First_Name Varchar (20) , LastName varchar(255) NOT NULL, Last_Name Varchar (20) , FirstName varchar(255), Age Int , Age int Marks Int , ); ); CREATE Command The CREATE INDEX command is used to create indexes in tables (allows duplicate values). Example: CREATE INDEX idx_lastname ON Persons (LastName); Example Example (cont.) ALTER Command ALTER is used to changes or modifies the existing structure of the database, and it also changes the schema of database objects. Syntax : ALTER TABLE Student ADD Father's_Name Varchar(60); ALTER TABLE Student DROP Age, Marks; ALTER TABLE Student MODIFY ( Last_Name varchar(25));  ALTER Command ID LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger ALTER TABLE Persons ADD DateOfBirth date; ID LastName FirstName Address City DateOfBirth 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger DROP Command DROP is used to delete/remove the database objects from the SQL database. We can easily remove the entire table, view, or index from the database using this DDL command. Examples: DROP DATABASE Database_Name; DROP TABLE Table_Name; DROP INDEX Index_Name; TRUNCATE Command TRUNCATE is used to deletes or removes all the records from the table and keep the table structure as it is. Syntax : TRUNCATE TABLE students; RENAME Command RENAME is used to change the name of the database tables. Syntax: RENAME TABLE Student TO Student_Detail;  Thanks Any Questions?