Data Definition Language
Website: mars.mu Session Name: BTLG5M
1
Relational model theories
Oracle syntax
User requirements
Drawing conceptual ERD
Business rules
ERD notations
Conceptu al ERD
Drawing logical-le vel ERD
Logical- level ERD
Write database schema
Database schema
normalisation
SQL general syntax
• A single statement is ended with SEMICOLON.
• Predefined KEYWORDs represent clauses
(components) of a statement.
• Keywords are NOT case sensitive.
• Examples:
CREATE TABLE unit (
unit_code CHAR(7) NOT NULL,
unit_name VARCHAR(50) NOT NULL CONSTRAINT uq_unit_name UNIQUE, CONSTRAINT pk_unit PRIMARY KEY (unit_code)
);
SELECT * FROM student;
3
SQL Statements • Creating database structure.
– CREATE TABLE, ALTER TABLE, DROP TABLE
• Adding and Manipulating database contents
(rows).
– INSERT, UPDATE, DELETE
• Retrieving data from database – SELECT
4
CREATING TABLES
5
CREATE TABLE STUDENT (
);
stu_nbr
stud_lname
stud_fname
stu_dob
CONSTRAINT STUDENT_PK PRIMARY KEY (stu_nbr)
NUMBER VARCHAR2(50) VARCHAR2(50)
NOT NULL, NOT NULL, NOT NULL, NOT NULL,
DATE
What relational model component(s) is/are defined in the above create table statement?
a. Relation, Attribute, Domain
b. Primary Key
c. Foreign Key
d. Referential Integrity constraint
e. All of the options in a-d are correct.
f. Some of the options in a-d are correct.
6
Column VS Table Level Constraints
CREATE TABLE STUDENT (
stu_nbr
stud_lname
stud_fname
stu_dob
CONSTRAINT STUDENT_PK PRIMARY KEY (stu_nbr)
NUMBER VARCHAR2(50) VARCHAR2(50)
NOT NULL, NOT NULL, NOT NULL, NOT NULL,
column constraints
table constraint
DATE
);
7
CREATE
TABLE student (
stu_nbr NUMBER(8) NOT NULL,
stu_lname VARCHAR(50) NOT NULL,
stu_fname VARCHAR(50) NOT NULL, stu_dob DATE NOT NULL, CONSTRAINT pk_student PRIMARY KEY (stu_nbr)
); CREATE
TABLE unit (
unit_code CHAR(7) NOT NULL,
unit_name VARCHAR(50) NOT NULL CONSTRAINT uq_unit_name UNIQUE, CONSTRAINT pk_unit PRIMARY KEY (unit_code)
);
8
How many foreign key (FK) will be in the database when the three table are created?
a. 1. b. 2. c. 3. d. 4.
During discussion, name the attribute(s) that will be assigned as FK and what table(s) would it “link”?
9
What would be the order of the CREATE TABLE statements in the schema script to successfully creating a database based on the above diagram? (assuming that we will define the FK as part of the create table statement)
a. UNIT, ENROLMENT, STUDENT
b. ENROLMENT, STUDENT, UNIT
c. STUDENT, UNIT, ENROLMENT
d. UNIT, STUDENT, ENROLMENT
e. More than one option is correct.
10
CREATE
TABLE enrolment (
stu_nbr
unit_code
enrol_year
enrol_semester
enrol_mark
enrol_grade
CONSTRAINT pk_enrolment PRIMARY KEY (stu_nbr, unit_code, enrol_year, enrol_semester), CONSTRAINT fk_enrolment_student FOREIGN KEY (stu_nbr) REFERENCES student (
stu_nbr),
CONSTRAINT fk_enrolment_unit FOREIGN KEY (unit_code) REFERENCES unit ( unit_code)
);
NUMBER(8) NOT NULL, CHAR(7) NOT NULL, NUMBER(4) NOT NULL, CHAR(1) NOT NULL, NUMBER(3) ,
CHAR(3),
11
Alternative method of defining FKs
CREATE
TABLE enrolment (
stu_nbr unit_code enrol_year enrol_semester mark
);
ALTER TABLE enrolment ADD
( CONSTRAINT fk_enrolment_student FOREIGN KEY (stu_nbr)
REFERENCES student ( stu_nbr),
CONSTRAINT fk_enrolment_unit FOREIGN KEY (unit_code) REFERENCES unit ( unit_code)
);
NUMBER(8) NOT NULL, CHAR(7) NOT NULL, NUMBER(4) NOT NULL, CHAR(1) NOT NULL, NUMBER(3),
CHAR(3),
CONSTRAINT pk_enrolment PRIMARY KEY (stu_nbr, unit_code, enrol_year, enrol_semester)
grade
12
ALTER TABLE
It is used to change table structure. For example:
• Adding column(s).
• Removing column(s).
• Adding constraint(s).
• Removing constraint(s)
ALTER TABLE student
ADD (stu_address varchar(200),
status char(10) constraint status_chk
);
DEFAULT
CHECK ( status in (‘graduated’,’current’))
‘current’,
13
DELETING TABLE • Use the DROP statement.
• Examples:
– DROP TABLE enrolment PURGE;
– DROP TABLE student CASCADE CONSTRAINTS PURGE;
14
● ●
Notes on Creating Database Schema in Oracle
Put all the create table statements into a single SQL script file.
Create another SQL script file containing the DROP table statements.
15
ADDING TUPLES/ROWS TO TABLE
16
INSERT
• Adding data to a table in database.
• SYNTAX:
INSERT INTO table [(column [, column…])] VALUES (value [, value…]);
INSERT INTO unit VALUES (‘FIT1004’,’Database’);
INSERT INTO student VALUES (112233,’Indrawan-Santiago’,’Maria’,’01-Jan-1995 ’)
17
Assume the tables have been created and there is no data currently in the tables. In what order should we populate the table?
a. UNIT- > STUDENT -> ENROLMENT
b. ENROLMENT -> UNIT -> STUDENT
c. STUDENT -> UNIT -> ENROLMENT
d. More than one option is correct.
18
COMMIT and ROLLBACK
INSERT INTO enrolment VALUES (112233, ‘FIT1004’,1,2012,45,’N’); INSERT INTO enrolment VALUES (112233, ‘FIT1001’,1,2012,80,’HD’); COMMIT;
COMMIT makes the changes to the database permanent. ROLLBACK will undo the changes.
19
Using a SEQUENCE
• Oracle supports auto-increment of a numeric PRIMARY KEY.
– SEQUENCE.
• Steps to use:
– Create sequence
CREATE SEQUENCE sno_seq INCREMENT BY 1;
– Access the sequence using two built-in variables (pseudocolumns):
• NEXTVAL and CURRVAL
– INSERT INTO student
VALUES(sno_seq.nextval,’Bond’,’James’,’01-Jan-1994’);
– INSERT INTO enrolment VALUES(sno_seq.currval,’FIT1004’,…’);
20
Two new students and their enrolment details need to be added, James Bond wants to enrol in FIT1004 and FIT1001, Bruce Lee only wants to enrol in FIT1004. The sequence for sno is called sno_seq.
— Add two students
INSERT INTO student VALUES (sno_seq.nextval,’Bond’,’James’,’01-Jan-1994’); INSERT INTO student VALUES (sno_seq.nextval,’Lee’,’Bruce’,’01-Feb-1994’); — Add the enrolments
INSERT INTO enrolment VALUES (sno_seq.currval,1,2012,’FIT1004’,0,’NA’); INSERT INTO enrolment VALUES (sno_seq.currval,1,2012,’FIT1001’,0,’NA’); INSERT INTO enrolment VALUES (sno_seq.currval,1,2012,’FIT1004’,0,’NA’); COMMIT;
What is/are the problem(s) with the use of SEQUENCE in the above SQL script?
a. There will be an error message. It states that a violation of primary key constraint in the ENROLMENT has occurred.
b. Bruce Lee will be enrolled in FIT1001.
c. There will be NO enrolment record for James Bond.
d. All of the options a-c are problems that will be caused by the script.
e. Some of the options in a-c are problems that will be caused by the
script.
f. There will be no problem caused by the script.
21
Summary • CREATE TABLE statements
– PK, FK, data type and constraints • INSERT statements.
– Sequence
22