Preparation for the workshop – ready, set ……
▪ connect to Flux – flux.qa and be ready to answer questions
▪ test SQL Developer and your Oracle connection to ensure you can login
to the database (local install or via MoVE)
Copyright By PowCoder代写 加微信 powcoder
▪ on campus students nominate someone as your table leader for this
INFORMATION TECHNOLOGY
Creating & Populating the Database
Workshop 2022 S1
Note for this Workshop we are using examples and syntax particular to Oracle
Relational model theories
Oracle syntax
User requirements
Draw Conceptual
Draw logical level Model
Logical level Model
Write database schema
Database schema
conceptual ERD ERD
Business rules
ERD notations
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 VARCHAR2(50)CONSTRAINT uq_unit_name UNIQUE NOT NULL, CONSTRAINT pk_unit PRIMARY KEY (unit_code)
SELECT * FROM unit;
Flux Task coming up ….
Q1. The SQL Language is made up of the following components (multiple answers can be selected):
A. Data Control Language (DCL)
B. Data Query Language (DQL)
C. Data Definition Language (DDL)
D. Data Manipulation Language (DML)
E. Data Structured Query Language (DSQL)
F. Data Update Language (DUL)
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
Flux Tasks coming up ….
CREATE A TABLE (DDL)
Q2. An attribute is to be used to store Australian postcodes eg. 3001, 2000 – the data type should be:
A. VARCHAR2(4) B. CHAR(4)
C. NUMBER(4) D. STRING(4)
Q3. An attributes is to be used to store a customers outstanding balance – the permitted range is $0 to $2000.00 – the data type should be:
A. VARCHAR2(8) B. NUMBER(4,2) C. NUMBER(6,2) D. NUMBER
Q4. An attribute is to be used to store the year an event occurred – the data types which could be used are (multiple answers can be selected):
B. DATE(4)
C. NUMBER(4) D. CHAR(4)
Q5. An attribute is to be used to store the finish time of athletes entered in a 800m running event in minutes and seconds eg. 3 min 25 sec – the data type which should be used is:
A. DATETIME
D. NUMBER(3,2)
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
▪ Date/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
Column VS Table Level Constraints
CREATE TABLE training (
train_code
train_desc
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,
CREATE TABLE training (
train_code
train_desc
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,
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 )
Group Task coming up ….
Q6. The foreign keys in this model can be declared by (multiple answers can be selected if required). Discuss as a group and arrive at an answer – be prepared to justify your answer:
A. Column Constraints
B. Table Constraints
C. Use of the ALTER command
D. None of these
Problems here?
Alternative (BETTER) method of defining FKs
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,
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.
• 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.
• 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.
Referential Constraints SQL Data Modeller
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.
Group Tasks coming up ….
Q7. TRAINERs have a high turnover (they are employed and leave the company frequently), what could we select for the referential constraint involving TRAINING_COURSE.trainer_id FK (multiple answers can be selected):
A. RESTRICT
B. SET NULL
C. CASCADE
D. None of these
Q8. What could we select for the referential constraint involving TRAINING.train_code FK (multiple answers can be selected):
A. RESTRICT
B. SET NULL
C. CASCADE
D. None of these
ALTER TABLE
▪ Used to change a tables structure.
▪ For example:
– Adding column(s).
– Removing column(s).
– Adding constraint(s) – used previously for FK’s, but can be any constraint
– Removing constraint(s)
ALTER TABLE TRAINER
ADD (CONSTRAINT chk_trainercategory CHECK
(trainer_category IN ( ‘C’, ‘F’ )),
trainer_nocourses number(3) DEFAULT 0 NOT NULL);
Manipulate Constraints
● Turn constraint ON or OFF to temporarily disable
○ ALTER TABLE training_course
DISABLE CONSTRAINT training_trainingcourse;
○ ALTER TABLE training_course
ENABLE CONSTRAINT training_trainingcourse;
● Remove/re add constraint to modify constraint
○ ALTER TABLE training_course
DROP CONSTRAINT training_trainingcourse;
○ ALTER TABLE training_course
( CONSTRAINT training_trainingcourse FOREIGN KEY ( train_code )
REFERENCES training ( train_code ) ON DELETE CASCADE);
DELETING A TABLE
▪ Use the DROP statement. ▪ Examples:
– DROP TABLE training_course PURGE;
– DROP TABLE trainer CASCADE CONSTRAINTS PURGE;
ADDING TUPLES/ROWS TO A TABLE (DML)
• Adding data to a table in a database. • SYNTAX:
INSERT INTO table [(column [, column…])] VALUES (value [, value…]);
INSERT INTO training VALUES (‘C0001′,’Starter Drone Training 1′,8,24);;
INSERT INTO trainer (trainer_id, trainer_rego, trainer_fname, trainer_lname,
trainer_category) VALUES (312,’DR523412-314′,’Thomas’,’Price’,’F’);
INSERT INTO training_course VALUES (‘C0001′,’20-Oct-2020′,312); Role of: to_date and to_char
Inserting DATES into a table
▪ 06 Apr 2022
– this is a character string, not a date – we understand by agreement that it represents a
date but it is NOT a date datatype
– what date is this: 06/04/2022 – 6th April 2022 or the 4th June 2022
• it depends on your country
▪ SQL Developer does its own conversion of strings to dates using a preset
National Language Support (NLS) rules, however we must not depend on this
▪ TO_DATE is a special Oracle function which converts a string to a date based on a user supplied pattern
– convert a date
• to_date(’06 Apr 2022′,’dd Mon yyyy’)
– convert a date and time
• to_date(’06/04/2022 17:00′,’dd/mm/yyyy hh:mi’)
– convert a time
• to_date(’17:00′,’hh:mi’)
Inserting DATES into a table continued
▪ Insert a rental into the RENTAL table:
insert into RENTAL (rent_no, rent_bond, rent_out, rent_in,
drone_id, ct_id) values (123, 250,
to_date(’06 Apr 2022 10:00′,’dd Mon yyyy hh:mi’), null,
234, 2345);
COMMIT and ROLLBACK
How to determine
INSERT INTO manufacturer VALUES (12, ‘DJI’);
INSERT INTO drone_type VALUES(‘DJIT’, ‘DJI Trello’, 5, ‘C0001′, 12);
COMMIT makes the changes to the database permanent. ROLLBACK will undo the changes.
COMMIT/ROLLBACK only applicable to INSERT/UPDATE and DELETE
Using a SEQUENCE
• Oracle supports auto-increment of a numeric PRIMARY KEY. – SEQUENCE.
• Steps to use:
– Create sequence
CREATE SEQUENCE manuf_seq INCREMENT BY 1;
– Access the sequence using two built-in variables (pseudocolumns): • NEXTVAL and CURRVAL
– INSERT INTO manufacturer VALUES(manuf_seq.nextval,’DJI’);
– INSERT INTO drone_type VALUES(‘DJIT’, ‘DJI Trello’, 5, ‘C0001’, manuf_seq.currval);
– Note sequence value CANNOT be relied on after a COMMIT/ROLLBACK
Group Task coming up ….
PUTTING THIS TO WORK
Assume purple relations (tables) have been created
Q9. Code the three white relations using SQL Developer (omit column comments).
As a group discuss the approach and constraints needed before beginning
CREATE TABLE customer (
cust_id NUMBER(4) NOT NULL,
cust_fname VARCHAR2(25) NOT NULL,
cust_lname VARCHAR2(25) NOT NULL,
cust_phone CHAR(12) NOT NULL
CREATE TABLE rental (
rent_no NUMBER(8) NOT NULL,
rent_bond NUMBER(6, 2) NOT NULL,
rent_out DATE NOT NULL,
rent_in DATE,
drone_id NUMBER(5) NOT NULL,
ct_id NUMBER(7) NOT NULL
CREATE TABLE cust_train (
train_code
traincourse_date DATE NOT NULL,
ct_exam_date DATE,
ct_date_expire DATE
NUMBER(7) NOT NULL,
NUMBER(4) NOT NULL,
CHAR(5) NOT NULL,
ALTER TABLE rental ADD CONSTRAINT rental_pk PRIMARY KEY ( rent_no );
ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY ( cust_id );
ALTER TABLE cust_train ADD CONSTRAINT cust_train_pk PRIMARY KEY ( ct_id );
ALTER TABLE cust_train
ADD CONSTRAINT customer_custtrain FOREIGN KEY ( cust_id )
REFERENCES customer ( cust_id );
ALTER TABLE cust_train
ADD CONSTRAINT traincourse_custtrain FOREIGN KEY ( train_code,
REFERENCES training_course ( train_code,
ALTER TABLE rental
ADD CONSTRAINT drone_rental FOREIGN KEY ( drone_id )
REFERENCES drone ( drone_id );
ALTER TABLE rental
ADD CONSTRAINT custtrain_rental FOREIGN KEY ( ct_id )
REFERENCES cust_train ( ct_id );
traincourse_date )
traincourse_date );
— Other Constraints
ALTER TABLE cust_train
ADD CONSTRAINT cust_train_uq UNIQUE ( cust_id,
train_code,
traincourse_date );
During this workshop column comments were omitted to speed up
the coding
**HOWEVER** column comments MUST always be provided in the
final schema
Q10. Code the SQL create table statements for the following segment of the Monash Software model (column comments are required):
Answer available 5 PM Sunday
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com