[SOLVED] CS SQL database MONASH

MONASH
INFORMATION TECHNOLOGY
Creating & Populating the Database Data Definition Language
Workshop Q&A

User requirements
Relational model theories
Oracle syntax
Draw Conceptual conceptual ERD
ERD
Draw logical level Model
Logical level Model
Write database schema
Database schema
Business rules
ERD notations
Normalisation
2
2

SQL general syntax
A single statement is ended with SEMICOLON.
Predefined KEYWORDs represent clauses (components) of a statement.
Keywords are NOT case sensitive. Examples:
3
3

SQL Statements
Data Definition Language (DDL) Creating database structure
CREATE TABLE, ALTER TABLE, DROP TABLE Data Manipulation Language (DML)
Adding and Manipulating database contents (rows) INSERT, UPDATE, DELETE
Retrieving data from database SELECT
Data Control Language (DCL) Set permissions on objects
GRANT
4
4

CREATE A TABLE (DDL)
5

Common ORACLE data types
Text: CHAR(size), VARCHAR2(size)
e.g., CHAR(10), VARCHAR2(10)
CHAR(10) apple = apple
VARCHAR2(10) apple != apple
Numbers: NUMBER(precision, scale)
Weight NUMBER(7) or NUMBER(7,0) Weight = 7456124 Weight NUMBER(9,2) Weight = 7456123.89
Weight NUMBER(8,1) Weight = 7456123.9
Data/Time: DATE, TIMESTAMP
DATE can store a date and time (time to seconds), stored as Julian date
TIMESTAMP can store a date and a time (up to fractions of a second)
TIMESTAMP WITH TIME ZONE
6
6

Column VS Table Level Constraints
CREATE TABLE training (
train_code
train_desc
train_hrs
train_active_months NUMBER(2) NOT NULL, CONSTRAINT training_pk PRIMARY KEY (train_code)
);
CHAR(5) NOT NULL, VARCHAR2(100) NOT NULL,
column constraints
table constraint
NUMBER(2) NOT NULL,
7
7

CREATE TABLE training (
train_code
train_desc
train_hrs
train_active_months NUMBER(2) NOT NULL, CONSTRAINT training_pk PRIMARY KEY ( train_code )
);
CREATE TABLE trainer (
CHAR(5) NOT NULL,
VARCHAR2(100) NOT NULL, NUMBER(2) NOT NULL,
trainer_id
trainer_rego
trainer_fname
trainer_lname
trainer_category CHAR(1) NOT NULL, CONSTRAINT trainer_pk PRIMARY KEY ( trainer_id )
);
NUMBER(3) NOT NULL, CHAR(12) NOT NULL,
VARCHAR2(25) NOT NULL, VARCHAR2(25) NOT NULL,
8
8

CREATE TABLE training_course (
train_code CHAR(5) NOT NULL,
traincourse_date DATE NOT NULL,
trainer_id NUMBER(3) NOT NULL,
CONSTRAINT training_course_pk PRIMARY KEY ( train_code, traincourse_date ), CONSTRAINT trainer_trainingcourse FOREIGN KEY ( trainer_id )
REFERENCES trainer ( trainer_id ), CONSTRAINT training_trainingcourse FOREIGN KEY ( train_code )
);
REFERENCES training ( train_code )
9
9

Problems here?
10 10

CREATE TABLE training_course (
train_code
traincourse_date
trainer_id
CONSTRAINT training_course_pk PRIMARY KEY ( train_code, traincourse_date )
);
ALTER TABLE training_course ADD
( CONSTRAINT trainer_trainingcourse FOREIGN KEY ( trainer_id ) REFERENCES trainer ( trainer_id ),
CONSTRAINT training_trainingcourse FOREIGN KEY ( train_code ) REFERENCES training ( train_code ));
CHAR(5) NOT NULL, DATE NOT NULL,
NUMBER(3) NOT NULL,
11 11

Referential Integrity
To ensure referential integrity, SQL defines three possible actions for FKs in relations when a deletion of a primary key occurs:
RESTRICT (Oracle No Action basically equivalent)
Deletion of tuples is NOT ALLOWED for those tuples in the table referred by the FK (the table containing PK) if there is corresponding tuple in the table containing the FK.
CASCADE
A deletion of a tuple in the table referred by the FK (the table containing PK) will result in the deletion of the corresponding tuples in the table containing the FK.
NULLIFY
A deletion of a tuple in the table referred by the FK (the table containing PK) will result in the update of the corresponding tuples in the table containing the FK to NULL.
12 12

Referential Constraints SQL Data Modeller
13 13

What Referential Integrity Constraint to implement?
Use the model to decide on what referential integrity constraint to implement.
Mandatory vs Optional participation.
The constraints must be decided at the design phase.
14 14

Used to change a tables structure.
For example:
Adding column(s).
Removing column(s).
Adding constraint(s).
Removing constraint(s)
15 15

Referential Integrity Definition Example
ALTER TABLE training_course
DROP CONSTRAINT training_trainingcourse;
ALTER TABLE training_course ADD
( CONSTRAINT training_trainingcourse FOREIGN KEY ( train_code ) REFERENCES training ( train_code ) ON DELETE CASCADE);
16 16

DELETING A TABLE
Use the DROP statement. Examples:

17 17

ADDING TUPLES/ROWS TO A TABLE (DML)
18

INSERT
19 19

COMMIT and ROLLBACK
How to determine
INSERT INTO manufacturer VALUES (12, DJI);
INSERT INTO drone_type VALUES(DJIT, DJI Trello, 5, C0001, 12);
20 20

DJIT, DJI Trello, 5, C0001,
21 21

PUTTING THIS TO WORK
22

23 23

24 24

25 25

26 26

27 27